October 5, 2005 at 10:59 am
I define the following sp and it works.
CREATE PROCEDURE usp_check_login @username varchar(20), @password varchar(20)
AS
SET NOCOUNT ON
RETURN 1
GO
If I run the following code from VB, it fails while executing cmdLogin.Execute. The error description is: Procedure 'usp_check_login' expects parameter '@username', which was not supplied.
Any idea why cmdLogin.Execute fails and how it can be fixed?
Dim lResult As Long
Dim cmdLogin As ADODB.Command
Dim rsLogin As ADODB.Recordset
Dim sUsername As String
Dim sPassword As String
cmdLogin.CreateParameter "RETURN_VALUE", adInteger, adParamReturnValue, 4
sUsername = "john"
cmdLogin.CreateParameter "@username", adVarChar, adParamInput, 20, sUsername
sPassword = "smith"
cmdLogin.CreateParameter "@password", adVarChar, adParamInput, 20, sPassword
Set rsLogin = cmdLogin.Execute
lResult = cmdLogin.Parameters(0).value
Thank you,
Alex
October 5, 2005 at 11:11 am
Place your cursor in CreateParameter. Hit F1 to get MSDN help. This is what it says:
This member supports the .NET Framework infrastructure and is not intended to be used directly from your code.
[Visual Basic]Private Function CreateParameter() As IDbDataParameter Implements _ IDbCommand.CreateParameter
Use cmdLogin.Parameters.Add() instead.
October 5, 2005 at 11:21 am
cmdLogin.Parameters.Add() method is apparently NOT supported.
cmdLogin.Parameters.Append(Object) is available, but how do I create an object from the following parameters?
"@username", adVarChar, adParamInput, 20, sUsername
I recall seeing cmdCommunity.CreateParameter("@username", adVarChar, adParamInput, 20, sUsername) used to create an object, but cmdCommunity is NOT a known object in my ADODB environment.
October 5, 2005 at 11:32 am
This is a generated code. I know it can be written in a shorter fashion, but at least it'll get you started :
P.S. MyCn is a global connection to the application
Private Function exec_usp_check_login(ByVal username As String, ByVal password As String) As Int32
Dim MyCmd As New SqlClient.SqlCommand("dbo.usp_check_login", MyCn)
MyCmd.CommandType = CommandType.StoredProcedure
Dim MyParam As SqlClient.SqlParameter
MyParam = MyCmd.Parameters.Add("@username", username)
MyParam.Size = 20
MyParam.Direction = ParameterDirection.Input
MyParam.SqlDbType = SqlDbType.VarChar
MyParam = MyCmd.Parameters.Add("@password", password)
MyParam.Size = 20
MyParam.Direction = ParameterDirection.Input
MyParam.SqlDbType = SqlDbType.VarChar
MyParam = MyCmd.Parameters.Add("@Return", "0")
MyParam.Size = 4
MyParam.Direction = ParameterDirection.ReturnValue
MyParam.SqlDbType = SqlDbType.Int
Dim RowsAffected As Int32 = 0
Dim ReturnValue As Int32 = 0
Try
MyCn.Open()
RowsAffected = MyCmd.ExecuteNonQuery()
MyCn.Close()
ReturnValue = CType(MyCmd.Parameters("@Return").Value, Int32)
Catch MyEx As Exception
'ErrHandling goes here
MsgBox(MyEx.Message)
Finally
If MyCn.State ConnectionState.Closed Then
MyCn.Close()
End If
Dim Disp As IDisposable
If TypeOf MyCmd Is IDisposable Then
Disp = MyCmd
Disp.Dispose()
End If
End Try
Return ReturnValue 'or Return RowsAffected
End Function
October 5, 2005 at 12:24 pm
> Dim MyCmd As New SqlClient.SqlCommand("dbo.usp_check_login", MyCn)
SqlClient is not a known object in ActiveX Data Objects 2.7 (Library) ,which define ADODB objects. What library reference do I need to add to my project in order for my environment to recognize SqlClient? I appreciate your help
October 5, 2005 at 12:29 pm
All I can find is System.Data on google, but I have no way of testing it.
October 5, 2005 at 1:21 pm
I apologize - shoulda gulped some more caffeine before replying, because I assumed ADO.Net, when you are clearly referencing classic ADO.
The System.Data.SqlClient namespace is from the .Net Framework and is irrelevant to your scenario.
Check this sample code: http://www.freevbcode.com/ShowCode.asp?ID=3687
What you are doing wrong is failing to assign the result of CreateParameter to a variable, and then using Command.Parameters.Append to append the newly created parameter object to the command's collection.
October 5, 2005 at 1:23 pm
Now that would be a OOPS.
Need the same version in VB 6 now?
October 5, 2005 at 3:38 pm
Addict's sample code did the trick. I'll keep System.Data.SqlClient for when we migrate to .NET
I don't think that I would have been able to figure it out on my own regardless of how many times I would have banged my head against the wall. So, thank you very much all!
October 6, 2005 at 6:51 am
HTH.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply