DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 14 of 14

Thread: ADO and stored proc parameters data type problem

  1. #1
    markus Guest

    ADO and stored proc parameters data type problem


    Hi -
    The following code works perfectly. As shown, as long as I pass a numerical
    parameter to a stored procedure I have no problem. However, I have had no
    luck passing character data to a stored procedure. I'm of course speaking
    of proper situations where the parameters used in VB and those expected by
    the stored procedure are in line with one another. The DBMS I'm going against
    is Sybase 11.9.2 but I doubt that has anything to do with it.
    DOES ANYONE HAVE A SUGGESTION REGARDING HOW TO PASS CHARACTER DATA AS PARAMETERS
    TO A STORED PROCEDURE?, OR WORKING EXAMPLES?, PLEASE HELP!!!
    Here's the code with my comments:
    Private Sub cmd_3_Click()
    Dim conn As New ADODB.Connection
    Dim cmd As New ADODB.Command
    Dim rs As New ADODB.Recordset
    ' Dim stringvalue As String
    Dim numvalue as Integer

    'open the connection
    conn.ConnectionString = "uid=myid;pwd=my_pw;DSN=my_app;DB=mydb"

    conn.CursorLocation = adUseClient

    conn.Open

    numvalue = txt_num.Text 'user input on form

    cmd.CommandText = "my_proc" 'stored proc name
    cmd.CommandType = adCmdStoredProc

    With cmd.Parameters ' set-up and append parameters in "with" block
    .Append cmd.CreateParameter("Return_value", adInteger, adParamReturnValue)
    ' ***** THIS NEXT LINE DOESN'T WORK if input parm is text '
    ' .Append cmd.CreateParameter("mytextin", adBSTR, adParamInput)
    ' ***** This results in parameter precision error, I've
    ' ***** tried adVariant, adChar, adVarChar, and even "adVarChar(10)"
    ' ***** which matches the sp parm on the DBMS but the "(10)" causes
    ' ***** a VB code error (not legitimate in VB I guess).
    ' *****
    ' ***** THIS NEXT LINE WORKS - again, as long as it is an integer
    ' ***** being passed, there is no problem.
    .Append cmd.CreateParameter("mynumin", adInteger, adParamInput)
    .Append cmd.CreateParameter("result_code", adInteger, adParamOutput)
    End With

    ' ***** HERE, IF THE VB Variable and SP Parameter WERE TEXT and
    ' ***** treated/handled as text, I would get a parameter precision
    ' ***** error... the code shown is for an INT, and works perfectly.
    ' cmd.Parameters("mytextin") = stringvalue
    cmd.Parameters("mynumin") = numvalue

    Set cmd.ActiveConnection = conn

    rs.Open cmd, , adOpenStatic, adLockBatchOptimistic

    MsgBox "that was it", vbInformation, "FYI..."

    ' << block of CASE ELSE logic on "result_code"
    ' which is used for success determination
    ' or error handling. This extensive block is
    ' omitted from this example code >>

    Set cmd.ActiveConnection = Nothing
    Set rs.ActiveConnection = Nothing
    conn.Close

    End Sub

    The stored procedure itself has one input parameter, one output parameter
    and a select error # (as additional output for generation and evaluation
    logic such as success or roll-back due to business rule violation).

    If nothing else, the code example can assist those of you who are having
    problems w/ ADO calls to stored procedures in general... again, the code
    approach is basically bullet proof. I don't think it's a Sybase issue but
    I can't say... Has anyone had succes at passing text parameter values to
    a stored procedure in Sybase?, or in Oracle or SQL Server?... any input would
    be GREATLY appreciated.

    Thanks in advance,
    - markus

  2. #2
    darcey Guest

    Re: ADO and stored proc parameters data type problem


    Hi Markus,

    I do not know if this works with sybase, but it does with SQL Server 7.0,
    ADO 2.5. My parameter in the stroed procedure is a varchar(10):

    Cmd.Parameters.Append Cmd.CreateParameter("@RemanNo", adVarChar, adParamInput,
    10, REMANNO)

    HTH,

    Darcey



    "markus" <mfaria100@hotmail.com> wrote:
    >
    >Hi -
    >The following code works perfectly. As shown, as long as I pass a numerical
    >parameter to a stored procedure I have no problem. However, I have had

    no
    >luck passing character data to a stored procedure. I'm of course speaking
    >of proper situations where the parameters used in VB and those expected

    by
    >the stored procedure are in line with one another. The DBMS I'm going against
    >is Sybase 11.9.2 but I doubt that has anything to do with it.
    >DOES ANYONE HAVE A SUGGESTION REGARDING HOW TO PASS CHARACTER DATA AS PARAMETERS
    >TO A STORED PROCEDURE?, OR WORKING EXAMPLES?, PLEASE HELP!!!
    >Here's the code with my comments:
    >Private Sub cmd_3_Click()
    > Dim conn As New ADODB.Connection
    > Dim cmd As New ADODB.Command
    > Dim rs As New ADODB.Recordset
    >' Dim stringvalue As String
    > Dim numvalue as Integer
    >
    > 'open the connection
    > conn.ConnectionString = "uid=myid;pwd=my_pw;DSN=my_app;DB=mydb"
    >
    > conn.CursorLocation = adUseClient
    >
    > conn.Open
    >
    > numvalue = txt_num.Text 'user input on form
    >
    > cmd.CommandText = "my_proc" 'stored proc name
    > cmd.CommandType = adCmdStoredProc
    >
    > With cmd.Parameters ' set-up and append parameters in "with" block
    > .Append cmd.CreateParameter("Return_value", adInteger, adParamReturnValue)
    >' ***** THIS NEXT LINE DOESN'T WORK if input parm is text '
    >' .Append cmd.CreateParameter("mytextin", adBSTR, adParamInput)
    >' ***** This results in parameter precision error, I've
    >' ***** tried adVariant, adChar, adVarChar, and even "adVarChar(10)"
    >' ***** which matches the sp parm on the DBMS but the "(10)" causes
    >' ***** a VB code error (not legitimate in VB I guess).
    >' *****
    >' ***** THIS NEXT LINE WORKS - again, as long as it is an integer
    >' ***** being passed, there is no problem.
    > .Append cmd.CreateParameter("mynumin", adInteger, adParamInput)
    > .Append cmd.CreateParameter("result_code", adInteger, adParamOutput)
    > End With
    >
    >' ***** HERE, IF THE VB Variable and SP Parameter WERE TEXT and
    >' ***** treated/handled as text, I would get a parameter precision
    >' ***** error... the code shown is for an INT, and works perfectly.
    >' cmd.Parameters("mytextin") = stringvalue
    > cmd.Parameters("mynumin") = numvalue
    >
    > Set cmd.ActiveConnection = conn
    >
    > rs.Open cmd, , adOpenStatic, adLockBatchOptimistic
    >
    > MsgBox "that was it", vbInformation, "FYI..."
    >
    >' << block of CASE ELSE logic on "result_code"
    >' which is used for success determination
    >' or error handling. This extensive block is
    >' omitted from this example code >>
    >
    > Set cmd.ActiveConnection = Nothing
    > Set rs.ActiveConnection = Nothing
    > conn.Close
    >
    >End Sub
    >
    >The stored procedure itself has one input parameter, one output parameter
    >and a select error # (as additional output for generation and evaluation
    >logic such as success or roll-back due to business rule violation).
    >
    >If nothing else, the code example can assist those of you who are having
    >problems w/ ADO calls to stored procedures in general... again, the code
    >approach is basically bullet proof. I don't think it's a Sybase issue but
    >I can't say... Has anyone had succes at passing text parameter values to
    >a stored procedure in Sybase?, or in Oracle or SQL Server?... any input

    would
    >be GREATLY appreciated.
    >
    >Thanks in advance,
    >- markus



  3. #3
    markus Guest

    Re: ADO and stored proc parameters data type problem


    Hi Darcey -
    Thanks a million... I was missing the "size" declaration of 10 in
    the parameter definition... your tip was all that was needed.
    Thank you very much! that simple syntax change was the exact
    thing needed... apparently it's a required definition for
    text parameters... PLUS it works with all types of text variables
    not just varchar... thanks again!!!
    - Markus

    "darcey" <dbrown@bayridgelbr.com> wrote:
    >
    >Hi Markus,
    >
    >I do not know if this works with sybase, but it does with SQL Server 7.0,
    >ADO 2.5. My parameter in the stroed procedure is a varchar(10):
    >
    >Cmd.Parameters.Append Cmd.CreateParameter("@RemanNo", adVarChar, adParamInput,
    >10, REMANNO)
    >
    >HTH,
    >
    >Darcey
    >
    >
    >
    >"markus" <mfaria100@hotmail.com> wrote:
    >>
    >>Hi -
    >>The following code works perfectly. As shown, as long as I pass a numerical
    >>parameter to a stored procedure I have no problem. However, I have had

    >no
    >>luck passing character data to a stored procedure. I'm of course speaking
    >>of proper situations where the parameters used in VB and those expected

    >by
    >>the stored procedure are in line with one another. The DBMS I'm going

    against
    >>is Sybase 11.9.2 but I doubt that has anything to do with it.
    >>DOES ANYONE HAVE A SUGGESTION REGARDING HOW TO PASS CHARACTER DATA AS PARAMETERS
    >>TO A STORED PROCEDURE?, OR WORKING EXAMPLES?, PLEASE HELP!!!
    >>Here's the code with my comments:
    >>Private Sub cmd_3_Click()
    >> Dim conn As New ADODB.Connection
    >> Dim cmd As New ADODB.Command
    >> Dim rs As New ADODB.Recordset
    >>' Dim stringvalue As String
    >> Dim numvalue as Integer
    >>
    >> 'open the connection
    >> conn.ConnectionString = "uid=myid;pwd=my_pw;DSN=my_app;DB=mydb"
    >>
    >> conn.CursorLocation = adUseClient
    >>
    >> conn.Open
    >>
    >> numvalue = txt_num.Text 'user input on form
    >>
    >> cmd.CommandText = "my_proc" 'stored proc name
    >> cmd.CommandType = adCmdStoredProc
    >>
    >> With cmd.Parameters ' set-up and append parameters in "with" block
    >> .Append cmd.CreateParameter("Return_value", adInteger, adParamReturnValue)
    >>' ***** THIS NEXT LINE DOESN'T WORK if input parm is text '
    >>' .Append cmd.CreateParameter("mytextin", adBSTR, adParamInput)
    >>' ***** This results in parameter precision error, I've
    >>' ***** tried adVariant, adChar, adVarChar, and even "adVarChar(10)"
    >>' ***** which matches the sp parm on the DBMS but the "(10)" causes
    >>' ***** a VB code error (not legitimate in VB I guess).
    >>' *****
    >>' ***** THIS NEXT LINE WORKS - again, as long as it is an integer
    >>' ***** being passed, there is no problem.
    >> .Append cmd.CreateParameter("mynumin", adInteger, adParamInput)
    >> .Append cmd.CreateParameter("result_code", adInteger, adParamOutput)
    >> End With
    >>
    >>' ***** HERE, IF THE VB Variable and SP Parameter WERE TEXT and
    >>' ***** treated/handled as text, I would get a parameter precision
    >>' ***** error... the code shown is for an INT, and works perfectly.
    >>' cmd.Parameters("mytextin") = stringvalue
    >> cmd.Parameters("mynumin") = numvalue
    >>
    >> Set cmd.ActiveConnection = conn
    >>
    >> rs.Open cmd, , adOpenStatic, adLockBatchOptimistic
    >>
    >> MsgBox "that was it", vbInformation, "FYI..."
    >>
    >>' << block of CASE ELSE logic on "result_code"
    >>' which is used for success determination
    >>' or error handling. This extensive block is
    >>' omitted from this example code >>
    >>
    >> Set cmd.ActiveConnection = Nothing
    >> Set rs.ActiveConnection = Nothing
    >> conn.Close
    >>
    >>End Sub
    >>
    >>The stored procedure itself has one input parameter, one output parameter
    >>and a select error # (as additional output for generation and evaluation
    >>logic such as success or roll-back due to business rule violation).
    >>
    >>If nothing else, the code example can assist those of you who are having
    >>problems w/ ADO calls to stored procedures in general... again, the code
    >>approach is basically bullet proof. I don't think it's a Sybase issue

    but
    >>I can't say... Has anyone had succes at passing text parameter values to
    >>a stored procedure in Sybase?, or in Oracle or SQL Server?... any input

    >would
    >>be GREATLY appreciated.
    >>
    >>Thanks in advance,
    >>- markus

    >



  4. #4
    darcey Guest

    Re: ADO and stored proc parameters data type problem


    Hi Markus,

    No problem! Glad I could help !!

    Darcey



    "markus" <mfaria100@hotmail.com> wrote:
    >
    >Hi Darcey -
    >Thanks a million... I was missing the "size" declaration of 10 in
    >the parameter definition... your tip was all that was needed.
    >Thank you very much! that simple syntax change was the exact
    >thing needed... apparently it's a required definition for
    >text parameters... PLUS it works with all types of text variables
    >not just varchar... thanks again!!!
    >- Markus
    >
    >"darcey" <dbrown@bayridgelbr.com> wrote:
    >>
    >>Hi Markus,
    >>
    >>I do not know if this works with sybase, but it does with SQL Server 7.0,
    >>ADO 2.5. My parameter in the stroed procedure is a varchar(10):
    >>
    >>Cmd.Parameters.Append Cmd.CreateParameter("@RemanNo", adVarChar, adParamInput,
    >>10, REMANNO)
    >>
    >>HTH,
    >>
    >>Darcey
    >>
    >>
    >>
    >>"markus" <mfaria100@hotmail.com> wrote:
    >>>
    >>>Hi -
    >>>The following code works perfectly. As shown, as long as I pass a numerical
    >>>parameter to a stored procedure I have no problem. However, I have had

    >>no
    >>>luck passing character data to a stored procedure. I'm of course speaking
    >>>of proper situations where the parameters used in VB and those expected

    >>by
    >>>the stored procedure are in line with one another. The DBMS I'm going

    >against
    >>>is Sybase 11.9.2 but I doubt that has anything to do with it.
    >>>DOES ANYONE HAVE A SUGGESTION REGARDING HOW TO PASS CHARACTER DATA AS

    PARAMETERS
    >>>TO A STORED PROCEDURE?, OR WORKING EXAMPLES?, PLEASE HELP!!!
    >>>Here's the code with my comments:
    >>>Private Sub cmd_3_Click()
    >>> Dim conn As New ADODB.Connection
    >>> Dim cmd As New ADODB.Command
    >>> Dim rs As New ADODB.Recordset
    >>>' Dim stringvalue As String
    >>> Dim numvalue as Integer
    >>>
    >>> 'open the connection
    >>> conn.ConnectionString = "uid=myid;pwd=my_pw;DSN=my_app;DB=mydb"
    >>>
    >>> conn.CursorLocation = adUseClient
    >>>
    >>> conn.Open
    >>>
    >>> numvalue = txt_num.Text 'user input on form
    >>>
    >>> cmd.CommandText = "my_proc" 'stored proc name
    >>> cmd.CommandType = adCmdStoredProc
    >>>
    >>> With cmd.Parameters ' set-up and append parameters in "with" block
    >>> .Append cmd.CreateParameter("Return_value", adInteger, adParamReturnValue)
    >>>' ***** THIS NEXT LINE DOESN'T WORK if input parm is text '
    >>>' .Append cmd.CreateParameter("mytextin", adBSTR, adParamInput)


    >>>' ***** This results in parameter precision error, I've
    >>>' ***** tried adVariant, adChar, adVarChar, and even "adVarChar(10)"
    >>>' ***** which matches the sp parm on the DBMS but the "(10)" causes
    >>>' ***** a VB code error (not legitimate in VB I guess).
    >>>' *****
    >>>' ***** THIS NEXT LINE WORKS - again, as long as it is an integer
    >>>' ***** being passed, there is no problem.
    >>> .Append cmd.CreateParameter("mynumin", adInteger, adParamInput)
    >>> .Append cmd.CreateParameter("result_code", adInteger, adParamOutput)
    >>> End With
    >>>
    >>>' ***** HERE, IF THE VB Variable and SP Parameter WERE TEXT and
    >>>' ***** treated/handled as text, I would get a parameter precision
    >>>' ***** error... the code shown is for an INT, and works perfectly.
    >>>' cmd.Parameters("mytextin") = stringvalue
    >>> cmd.Parameters("mynumin") = numvalue
    >>>
    >>> Set cmd.ActiveConnection = conn
    >>>
    >>> rs.Open cmd, , adOpenStatic, adLockBatchOptimistic
    >>>
    >>> MsgBox "that was it", vbInformation, "FYI..."
    >>>
    >>>' << block of CASE ELSE logic on "result_code"
    >>>' which is used for success determination
    >>>' or error handling. This extensive block is
    >>>' omitted from this example code >>
    >>>
    >>> Set cmd.ActiveConnection = Nothing
    >>> Set rs.ActiveConnection = Nothing
    >>> conn.Close
    >>>
    >>>End Sub
    >>>
    >>>The stored procedure itself has one input parameter, one output parameter
    >>>and a select error # (as additional output for generation and evaluation
    >>>logic such as success or roll-back due to business rule violation).
    >>>
    >>>If nothing else, the code example can assist those of you who are having
    >>>problems w/ ADO calls to stored procedures in general... again, the code
    >>>approach is basically bullet proof. I don't think it's a Sybase issue

    >but
    >>>I can't say... Has anyone had succes at passing text parameter values

    to
    >>>a stored procedure in Sybase?, or in Oracle or SQL Server?... any input

    >>would
    >>>be GREATLY appreciated.
    >>>
    >>>Thanks in advance,
    >>>- markus

    >>

    >



  5. #5
    Join Date
    Jan 2007
    Posts
    7

    Same Problem I Am Facing. Help Me.

    i am connecting the sybase 12.5 via VB6 using DSN. sybase is in HPUNIX.
    when i am trying to call the store procedure i am facing problem. if the input parameter is adinteger it is working fine . if input parameter is adVarchar, it shows the run time error " Method 'Excute of' object _Command failed ".plz any one help me. THanks in advance.


    my code is...

    Private Sub Command1_Click()
    Dim cmd As New ADODB.Command
    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim qry As String
    Dim input_param As string
    Dim output_param As Integer
    Dim inp1 As Integer

    input_param = "PARTCPNTCD"
    inp1 = 1
    Set cn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    Set cmd = New ADODB.Command
    rs.CursorLocation = adUseClient

    gDSN = "HPCUST"
    gDBId = "sa"
    gPWD = ""

    cn.ConnectionString = "DSN=" & gDSN & ";uid=" & gDBId & ";pwd=" & gPWD & ";"
    cn.Open
    MsgBox "connected"

    cn.CursorLocation = adUseClient
    cmd.ActiveConnection = cn
    cmd.CommandType = adCmdStoredProc 'when using SP with command object
    cmd.CommandText = "tempdb..testProc"

    ' The below line gives the error. if the datatype is adInteger and input parameter is integer then it is working.if tit is varchar it is not working.

    cmd.Parameters.Append cmd.CreateParameter("@i_ld", adVarChar, adParamInput, 20, input_param)

    cmd.Parameters.Append cmd.CreateParameter("@o_ErrDesc", adVarChar, adParamOutput, 200)

    Set rs = cmd.Execute

    MsgBox "Resultset prepared"
    MsgBox rs(0)
    rs.Close
    cn.Close
    End Sub

  6. #6
    Join Date
    Aug 2004
    Location
    Orange, California
    Posts
    1,263
    Have you tried putting quotes around your text parameters.
    input_param = "'PARTCPNTCD'"
    or
    input_param = """PARTCPNTCD"""

  7. #7
    Join Date
    Jan 2007
    Posts
    7
    HI ron,
    I tried what you said. but same error exists.
    thanks for your time man.

  8. #8
    Join Date
    Aug 2004
    Location
    Orange, California
    Posts
    1,263
    Did you notice that you have two periods in a row in this line of code?
    cmd.CommandText = "tempdb..testProc"

    Are you sure that is correct?

  9. #9
    Join Date
    Jan 2007
    Posts
    7
    Yes. that is correct. same code is working in Windows sybase. inHPUNIX syabse only it shows error.

  10. #10
    Join Date
    Jan 2007
    Posts
    7
    Friends, any updates..

  11. #11
    Join Date
    Jan 2007
    Posts
    6
    Well, the datatype of the input parameter of your stored procedure has to match with the datatype of the ADO Parameter. So when it works with Integer, most propably the stored procedure expects an integer, which means it cannot work with varchar.

    If that's not the problem, can you post the definition of the stored procedure? Maybe we'll see more there.

    Tom

  12. #12
    Join Date
    Jan 2007
    Posts
    7

    Smile

    No tomscholz.
    I made the datatype of i/p paramater as varchar in stored procedure and Vb code also.

  13. #13
    Join Date
    Jan 2007
    Posts
    6
    OK, then another idea: Did you make sure, that you passed the correct size of the actual string that you try to pass? You will receive an error when you set a size of 20 and pass a string of 40

    Sorry, if this sounds stupid, but that's the only idea I have.

  14. #14
    Join Date
    Jan 2007
    Posts
    7
    Yes man. Ichecked all those things. Same size same data type.Everything is same.Time being i am using Recordset like rs.open "Execute tempdb..test_proc" like that.it is working for Varchar.but i donot know how to handle Output variable in this. till now i am not able find Why command object Excute method is not working?
    THanks man.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center