Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2004

    Unanswered: Please help with VB and SQL Problem

    I am tring to call a stored procedure and return a value from VB.
    When my VB code gets to the execute statement i get a Type Name Invalid error. Could someone please take a look at it and see what may be causing the error. I have incuded the VB code and SQL stored procedure.


    Option Explicit

    Private Sub cmdOk_Click()

    'Define the recordset & Return Parameter
    Dim cn As New ADODB.Connection
    Dim cmd As New ADODB.Command
    Dim rs As New ADODB.Recordset
    Dim parReturn As Parameter
    Dim varReturn As Integer
    'Dim strConn As Variant
    Dim varUserID As Variant
    Dim Param1 As Parameter
    Dim Param2 As Parameter
    Dim varPassword As Variant

    'Connet using the SQLOLEDB & Connection Parameters
    cn.Provider = "sqloledb"
    cn.Properties("Data Source") = "DARRYL"
    cn.Properties("Initial Catalog") = "Accounting"
    cn.Properties("user ID") = "sa"
    cn.Properties("password") = ""

    'Open the connection

    'Setup a command object for the stored procedure
    Set cmd.ActiveConnection = cn
    cmd.CommandText = "Check_Uid_Password"
    cmd.CommandType = adCmdStoredProc

    'Setup a return parameter
    Set parReturn = cmd.CreateParameter("Return", adInteger, adParamReturnValue)
    cmd.Parameters.Append parReturn

    'Setup User ID input parameter
    Set Param1 = cmd.CreateParameter("Input", adVariant, adParamInput)
    cmd.Parameters.Append Param1
    'Set cmd.Parameters(0).Size = 5
    varUserID = txtUid
    Param1.Value = varUserID

    'Setup Password Input Parameter
    Set Param2 = cmd.CreateParameter("Input", adVariant, adParamInput)
    cmd.Parameters.Append Param2
    varPassword = txtPassword
    Param2.Value = varPassword

    'Execute command
    Set rs = cmd.Execute

    End Sub

    CREATE PROCEDURE Check_Uid_Password

    --Declare Input and output parameters
    @varUserID varchar(50),
    @varPassword varchar(50),
    @UidOut varchar(50),
    @ReturnOut INT Output


    Select @UidOut = Employee_ID from Security where Employee_ID = @varUserID
    And Password = @varPassword

    If (@UidOut > 0)
    return 0
    return 99

  2. #2
    Join Date
    Nov 2004

    keep it simple

    have you tried the SP in the QA?

    if it works then you can call the SP from VB just the same way, something like
    set rs = conn.execute "Check_Uid_Password '" _
         & txtUid & "', '" & txtPassword & "'"
    also read this article about sql injection
    to err is human ; to really mess things up requires a computer

  3. #3
    Join Date
    Nov 2004
    SQL said that the procedure was ok but when I commented out the variables and the IF Statement and changed the Select Statement to "select * from security" the the call worked. I also commented out the sections in the VB code that Setup the Return, varUserID, varPassword input variables. So I added the "@UidOut" variable back and uncommented the IF Statement and Sql says that the SP is ok but when I run the VB code I get an error that says the SP is expecting the "@UidOut" to be passed as an Input to the SP. In the SP I tried explicitly declareing it with the Declare Statement but SQL says that that it is not the right syntax. but I used the same syntax as the Books Online shows.

    Could someone tell me how to declare a variable that is not a Input or Output in a SP
    Last edited by DDEtter; 12-01-04 at 12:19.

  4. #4
    Join Date
    Nov 2004

    Unhappy I'm not sure

    maybe you need an additional variable so the SP can deposit the value of the out parameter
    to err is human ; to really mess things up requires a computer

Posting Permissions

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