Friday, March 30, 2012

Parameter passing to a stored procedure

Hey huys, I got this stored procedure. First of all: could this work?

--start :)
CREATE PROCEDURE USP_MactchUser

@.domainUserID NVARCHAR(50) ,

@.EmployeeID NVARCHAR(50) ,

@.loginType bit = '0'
AS

INSERT INTO T_Login

(employeeID, loginType, domainUserID)

Values
(@.EmployeeID, @.loginType, @.domainUserID)
GO
--end :)

then I got this VB.Net code in my ASP.net page...

--begin :)
Private Sub matchUser()
Dim insertMatchedUser As SqlClient.SqlCommand
Dim daMatchedUser As SqlClient.SqlDataAdapter
SqlConnection1.Open()
'conn openned
daMatchedUser = New SqlClient.SqlDataAdapter("USP_MatchUser", SqlConnection1)
daMatchedUser.SelectCommand.CommandType = CommandType.StoredProcedure
daMatchedUser.SelectCommand.Parameters.Add(New SqlClient.SqlParameter("@.EmployeeID", SqlDbType.NVarChar, 50))
daMatchedUser.SelectCommand.Parameters.Add(New SqlClient.SqlParameter("@.domainUserID", SqlDbType.NVarChar, 50))
daMatchedUser.SelectCommand.Parameters("@.EmployeeID").Value = Trim(lblEmployeeID.Text)
daMatchedUser.SelectCommand.Parameters("@.domainUserID").Value = Trim(lblDomainuserID.Text)
daMatchedUser.SelectCommand.Parameters("@.EmployeeID").Direction = ParameterDirection.Output
daMatchedUser.SelectCommand.Parameters("@.domainUserID").Direction = ParameterDirection.Output
SqlConnection1.Close()
'conn closed

End Sub
--

If I try this it doesn't work (maybe that's normal :) ) Am I doing it wrong. The thing is, in both label.text properties a values is stored that i want to as a parameter to my stored procedure. What am I doing wrong?no.

Private Sub matchUser()
Dim insertMatchedUser As SqlClient.SqlCommand

insertMatchedUser = new SqlCommand;
insertMatchedUser.CommandText = "USP_MactchUser"
insertMatchedUser.CommandType = CommandType.StoredProcedure
insertMatchedUser.Parameters.Add(New SqlClient.SqlParameter("@.EmployeeID", SqlDbType.NVarChar, 50))
insertMatchedUser.Parameters.Add(New SqlClient.SqlParameter("@.domainUserID", SqlDbType.NVarChar, 50))
insertMatchedUser.Parameters("@.EmployeeID").Value = lblEmployeeID.Text.Trim()
insertMatchedUser.Parameters("@.domainUserID").Value = lblDomainuserID.Text.Trim()

insertMatchedUser.Connection = SqlConnection1;
SqlConnection1.Open()
'conn openned
insertMatcheduser.ExecuteNonQuery()

insertMatchedUser.Dispose()
SqlConnection1.Dispose()
End Sub|||Also:

CREATE PROCEDURE USP_MactchUser
@.domainUserID NVARCHAR(50) ,
@.EmployeeID NVARCHAR(50) ,
@.loginType bit = 0
AS

the bit data type literal should not have single quotes.sql

No comments:

Post a Comment