Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Posts
    5

    Unanswered: sql server stored proc parameter passing from vb

    i have a problem with parameter passing from visual basic to sql server stored procedure. the store procedure is
    CREATE proc COVERW
    @coverno varchar(15),
    @coverhead int output
    AS
    DECLARE @APPID AS BIGINT
    SELECT @APPID=(SELECT APPLICANTID FROM APPLICANTMASTER WHERE COVERNO =@coverno AND COVERHEAD=1)

    IF(@appid IS NULL)
    set @coverhead=0
    else

    SET @coverhead=1

    and is working properly
    and when i try to pass variable from visualbasic getting a oposte result
    visualbasic code
    Private Sub Form_Load()
    Dim nCover As String
    Dim dbcmd As ADODB.Command
    Set dbcmd = New ADODB.Command
    Dim R As Integer
    CoverNo = "KL100-5"
    With dbcmd
    .CommandText = "COVERW"
    .CommandType = adCmdStoredProc
    .Parameters.Append dbcmd.CreateParameter(nCover, adVarChar, adParamInput, 15)
    .Parameters.Append dbcmd.CreateParameter(R, adInteger, adParamInputOutput, 40)
    dbcmd.ActiveConnection = Database
    dbcmd.Execute , , adAsyncExecute
    End With
    Text2.text = R
    end sub

  2. #2
    Join Date
    Feb 2004
    Posts
    199
    what does it mean?
    Code:
    .Parameters.Append dbcmd.CreateParameter(nCover, adVarChar, adParamInput, 15)
    .Parameters.Append dbcmd.CreateParameter(R, adInteger, adParamInputOutput, 40)
    first parameter is the stored procedure's parameter name, but you put there a varible with int value?!

    use this way

    Code:
    .Parameters.Append dbcmd.CreateParameter("coverno", adVarChar, adParamInput, 15)
    .Parameters.Append dbcmd.CreateParameter("coverhead", adInteger, adParamInputOutput, 40)
    and after execute

    Text2.text =dcmd.Parameters("coverhead")
    MDB, ADP <-> MS SQL + VBA, ADO & RDO, .NET, Oracle, Java/Jsp.

  3. #3
    Join Date
    Feb 2004
    Posts
    5

    i tried it but getting an error message can u write your own

    Originally posted by sreelal
    i have a problem with parameter passing from visual basic to sql server stored procedure. the store procedure is
    CREATE proc COVERW
    @coverno varchar(15),
    @coverhead int output
    AS
    DECLARE @APPID AS BIGINT
    SELECT @APPID=(SELECT APPLICANTID FROM APPLICANTMASTER WHERE COVERNO =@coverno AND COVERHEAD=1)

    IF(@appid IS NULL)
    set @coverhead=0
    else

    SET @coverhead=1

    and is working properly
    and when i try to pass variable from visualbasic getting a oposte result
    visualbasic code
    Private Sub Form_Load()
    Dim nCover As String
    Dim dbcmd As ADODB.Command
    Set dbcmd = New ADODB.Command
    Dim R As Integer
    CoverNo = "KL100-5"
    With dbcmd
    .CommandText = "COVERW"
    .CommandType = adCmdStoredProc
    .Parameters.Append dbcmd.CreateParameter(nCover, adVarChar, adParamInput, 15)
    .Parameters.Append dbcmd.CreateParameter(R, adInteger, adParamInputOutput, 40)
    dbcmd.ActiveConnection = Database
    dbcmd.Execute , , adAsyncExecute
    End With
    Text2.text = R
    end sub

  4. #4
    Join Date
    Feb 2004
    Posts
    199
    lazy people
    I think on this forum people should discus about general propblems and solution ways, people, why you don't read help?, it's more easyer to find there answers for your simple questions.

    Code:
    Private Sub Form_Load()
    Dim dbcmd As ADODB.Command
    Set dbcmd = New ADODB.Command
    With dbcmd
    .CommandText = "COVERW"
    .CommandType = adCmdStoredProc
    .Parameters.Append dbcmd.CreateParameter("coverno" , adVarChar, adParamInput, ""KL100-5"
    ")
    .Parameters.Append dbcmd.CreateParameter("coverhead", adInteger, adParamInputOutput, 0)
    dbcmd.ActiveConnection = Database
    dbcmd.Execute , , adAsyncExecute
    Text2.text = .Parameters("coverhead")
    End With
    end sub
    Last edited by Kakha; 03-05-04 at 04:41.
    MDB, ADP <-> MS SQL + VBA, ADO & RDO, .NET, Oracle, Java/Jsp.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •