November 4, 2012 at 3:50 am
Good day all!!! I'm new to this forum. So please forgive me if I have posted in the wrong forum.
I'm create a CLR Stored Procedure using VS 2010, VB.Net using MS.SQL Server 2008. See code below
Partial Public Class StoredProcedures
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub user_login1(ByVal User_UserName As SqlString, ByVal User_Password As SqlString, ByRef Station As SqlString, <Out()> ByVal Users_RowID As SqlInt32, <Out()> ByVal Users_Name As SqlString, <Out()> ByVal Success As SqlBoolean, <Out()> ByVal Default_Message As SqlString)
Using conn As New SqlConnection("context connection=true")
Dim LoginCommand As New SqlCommand()
Dim sqlparam(6) As SqlParameter
sqlparam(0) = New SqlParameter("@User_UserName", SqlDbType.VarChar, 50)
sqlparam(1) = New SqlParameter("@User_Password", SqlDbType.VarChar, 50)
sqlparam(2) = New SqlParameter("@Station", SqlDbType.VarChar)
sqlparam(3) = New SqlParameter("@Users_RowID", SqlDbType.Int, 100, ParameterDirection.Output, False, 0, 0, String.Empty, DataRowVersion.Default, 0)
sqlparam(4) = New SqlParameter("@Users_Name", SqlDbType.VarChar, 100, ParameterDirection.Output, False, 0, 0, String.Empty, DataRowVersion.Default, String.Empty)
sqlparam(5) = New SqlParameter("@Success", SqlDbType.Bit, 1, ParameterDirection.Output, False, 0, 0, String.Empty, DataRowVersion.Default, 0)
sqlparam(6) = New SqlParameter("@Default_Message", SqlDbType.VarChar, 200, ParameterDirection.Output, False, 0, 0, String.Empty, DataRowVersion.Default, String.Empty)
sqlparam(0).Value = User_UserName
sqlparam(1).Value = User_Password
sqlparam(2).Value = Station
sqlparam(3).Value = Users_RowID
sqlparam(4).Value = Users_Name
sqlparam(5).Value = Success
sqlparam(6).Value = Default_Message
LoginCommand.Parameters.Add(sqlparam)
LoginCommand.CommandText =
"DECLARE @rowsaffected INT;" &
"DECLARE @User_Status_RowID INT;" &
"DECLARE @User_Cursor CURSOR" &
"SET @Success = 0" &
"SET @Users_RowID = 0" &
"SET @Users_Name = ''" &
"SET @User_Status_RowID = 0" &
"IF EXISTS (SELECT * FROM [dbo]. WHERE User_UserName = @User_UserName AND CONVERT(varbinary, User_Password) = CONVERT(varbinary, @User_Password))" &
"BEGIN " &
"SELECT @Users_RowID = User_RowID, @User_Status_RowID = User_Status_RowID " &
"FROM dbo. " &
"WHERE User_UserName = @User_UserName AND User_Password = @User_Password" &
"SELECT @Users_Name = (User_SName + ', ' + User_FName + '. ' + Title_Descr) " &
"FROM dbo. " &
"INNER JOIN dbo.title ON (dbo..Title_RowID = dbo.title.Title_RowID) " &
"WHERE User_UserName = @User_UserName AND User_Password = @User_Password" &
"IF ((LOWER(@User_UserName) <> 'administrator') AND (@User_Status_RowID = 1))" &
"BEGIN" &
"SET @Default_Message = 'User ''' + @Users_Name + ''' is already logged in.'" &
"SET @Success = 0" &
"RETURN" &
"END" &
"ELSE" &
"BEGIN" &
"EXEC [dbo].[user_status_update] @User_UserName, @Station, 1, @rowsaffected OUTPUT" &
"IF ((@rowsaffected = 2) AND (@@ERROR = 0))" &
"BEGIN" &
"SET @Default_Message = 'You have been logged in successfully'" &
"SET @Success = 1" &
"END" &
"ELSE" &
"BEGIN" &
"ROLLBACK TRANSACTION" &
"SET @Default_Message = 'An error occured while attempting to log you in. Please try again'" &
"SET @Success = 0" &
"RETURN" &
"END" &
"END" &
"END" &
"ELSE " &
"BEGIN" &
"ROLLBACK TRANSACTION" &
"SET @Default_Message = 'Invalid username and/or password. Try again'" &
"SET @Success = 0" &
"RETURN" &
"END"
LoginCommand.Connection = conn
conn.Open()
LoginCommand.ExecuteNonQuery()
conn.Close()
End Using
End Sub
End Class
When I try to run it I keep getting the same error:
Deploy error SQL01268: .Net SqlClient Data Provider: Msg 6580, Level 16, State 1, Procedure user_login1, Line 1 Declarations do not match for parameter 4. .NET Framework reference and T-SQL OUTPUT parameter declarations must match.
Deploy error SQL01268: .Net SqlClient Data Provider: Msg 6552, Level 16, State 3, Procedure user_login1, Line 1 CREATE PROCEDURE for "user_login1" failed because T-SQL and CLR types for parameter "@Users_RowID" do not match.
I have tried changing the type to: Int32, Integer, SqlInt32, SqlInteger, and so on and so on; with no luck...
Please help
Thanx in advance...
November 4, 2012 at 8:57 am
I'm not certain about VB but in C# the parameters are passed in by reference. Also, for SqlString you have to refer to them as follows:
sqlparam(0).Value = User_UserName.Value; // the value of the SqlString
sqlparam(1).Value = User_Password.Value;
sqlparam(2).Value = Station.Value; // the value of the SqlString
sqlparam(3).Value = Users_RowID
sqlparam(4).Value = Users_Name.Value; // the value of the SqlString
sqlparam(5).Value = Success
sqlparam(6).Value = Default_Message.Value; // the value of the SqlString
November 4, 2012 at 1:14 pm
Thanx for the reply...
This is what I have now, but it's still giving me the same error message:
Deploy error SQL01268: .Net SqlClient Data Provider: Msg 6580, Level 16, State 1, Procedure user_login1, Line 1 Declarations do not match for parameter 4. .NET Framework reference and T-SQL OUTPUT parameter declarations must match.
Deploy error SQL01268: .Net SqlClient Data Provider: Msg 6552, Level 16, State 3, Procedure user_login1, Line 1 CREATE PROCEDURE for "user_login1" failed because T-SQL and CLR types for parameter "@Users_RowID" do not match.
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub user_login1(ByVal User_UserName As String, ByVal User_Password As String, ByRef Station As String, <Out()> ByVal Users_RowID As Integer, <Out()> ByVal Users_Name As String, <Out()> ByVal Success As Boolean, <Out()> ByVal Default_Message As String)
Using conn As New SqlConnection("context connection=true")
Dim LoginCommand As New SqlCommand()
Dim sqlparam(6) As SqlParameter
sqlparam(0) = New SqlParameter("@User_UserName", SqlDbType.VarChar, 50)
sqlparam(1) = New SqlParameter("@User_Password", SqlDbType.VarChar, 50)
sqlparam(2) = New SqlParameter("@Station", SqlDbType.VarChar)
sqlparam(3) = New SqlParameter("@Users_RowID", SqlDbType.Int, 100, ParameterDirection.Output, False, 0, 0, String.Empty, DataRowVersion.Default, 0)
sqlparam(4) = New SqlParameter("@Users_Name", SqlDbType.VarChar, 100, ParameterDirection.Output, False, 0, 0, String.Empty, DataRowVersion.Default, String.Empty)
sqlparam(5) = New SqlParameter("@Success", SqlDbType.Bit, 1, ParameterDirection.Output, False, 0, 0, String.Empty, DataRowVersion.Default, 0)
sqlparam(6) = New SqlParameter("@Default_Message", SqlDbType.VarChar, 200, ParameterDirection.Output, False, 0, 0, String.Empty, DataRowVersion.Default, String.Empty)
sqlparam(0).Value = User_UserName
sqlparam(1).Value = User_Password
sqlparam(2).Value = Station
sqlparam(3).Value = Users_RowID
sqlparam(4).Value = Users_Name
sqlparam(5).Value = Success
sqlparam(6).Value = Default_Message
LoginCommand.Parameters.Add(sqlparam)
LoginCommand.CommandText =
"DECLARE @rowsaffected INT;" &
"DECLARE @User_Status_RowID INT;" &
"DECLARE @User_Cursor CURSOR" &
"SET @Success = 0" &
"SET @Users_RowID = 0" &
"SET @Users_Name = ''" &
"SET @User_Status_RowID = 0" &
"IF EXISTS (SELECT * FROM [dbo]. WHERE User_UserName = @User_UserName AND CONVERT(varbinary, User_Password) = CONVERT(varbinary, @User_Password))" &
"BEGIN " &
"SELECT @Users_RowID = User_RowID, @User_Status_RowID = User_Status_RowID " &
"FROM dbo. " &
"WHERE User_UserName = @User_UserName AND User_Password = @User_Password" &
"SELECT @Users_Name = (User_SName + ', ' + User_FName + '. ' + Title_Descr) " &
"FROM dbo. " &
"INNER JOIN dbo.title ON (dbo..Title_RowID = dbo.title.Title_RowID) " &
"WHERE User_UserName = @User_UserName AND User_Password = @User_Password" &
"IF ((LOWER(@User_UserName) <> 'administrator') AND (@User_Status_RowID = 1))" &
"BEGIN" &
"SET @Default_Message = 'User ''' + @Users_Name + ''' is already logged in.'" &
"SET @Success = 0" &
"RETURN" &
"END" &
"ELSE" &
"BEGIN" &
"EXEC [dbo].[user_status_update] @User_UserName, @Station, 1, @rowsaffected OUTPUT" &
"IF ((@rowsaffected = 2) AND (@@ERROR = 0))" &
"BEGIN" &
"SET @Default_Message = 'You have been logged in successfully'" &
"SET @Success = 1" &
"END" &
"ELSE" &
"BEGIN" &
"ROLLBACK TRANSACTION" &
"SET @Default_Message = 'An error occured while attempting to log you in. Please try again'" &
"SET @Success = 0" &
"RETURN" &
"END" &
"END" &
"END" &
"ELSE " &
"BEGIN" &
"ROLLBACK TRANSACTION" &
"SET @Default_Message = 'Invalid username and/or password. Try again'" &
"SET @Success = 0" &
"RETURN" &
"END"
LoginCommand.Connection = conn
conn.Open()
LoginCommand.ExecuteNonQuery()
Users_RowID = Convert.ToInt32(sqlparam(3).Value)
Users_Name = Convert.ToString(sqlparam(4).Value)
Success = Convert.ToBoolean(sqlparam(5).Value)
Default_Message = Convert.ToString(sqlparam(6).Value)
conn.Close()
End Using
End Sub
Heeeellllpppp
November 4, 2012 at 3:19 pm
.Value is nice to put there too, but the important place to put it is at the end of the SqlString variables you're passing in.
not
sqlparam(0).Value = User_UserName
sqlparam(1).Value = User_Password
but
sqlparam(0).Value = User_UserName.Value
sqlparam(1).Value = User_Password.Value
November 4, 2012 at 9:54 pm
Hi!!!
Thanx for the reply...
I have tried that but still getting the same error. The error type mismatch occurs with the parameter @Users_RowID:
"CREATE PROCEDURE for "user_login1" failed because T-SQL and CLR types for parameter "@Users_RowID" do not match."
Public Shared Sub user_login1(ByVal User_UserName As SqlString, ByVal User_Password As SqlString, ByRef Station As SqlString, <Out()> ByVal Users_RowID As SqlInt32, <Out()> ByVal Users_Name As SqlString, <Out()> ByVal Success As SqlBoolean, <Out()> ByVal Default_Message As SqlString)
...
...
...
sqlparam(0) = New SqlParameter("@User_UserName", SqlDbType.VarChar, 50)
sqlparam(1) = New SqlParameter("@User_Password", SqlDbType.VarChar, 50)
sqlparam(2) = New SqlParameter("@Station", SqlDbType.VarChar)
sqlparam(3) = New SqlParameter("@Users_RowID", SqlDbType.Int, 100, ParameterDirection.Output, False, 0, 0, String.Empty, DataRowVersion.Default, 0)
sqlparam(4) = New SqlParameter("@Users_Name", SqlDbType.VarChar, 100, ParameterDirection.Output, False, 0, 0, String.Empty, DataRowVersion.Default, String.Empty)
sqlparam(5) = New SqlParameter("@Success", SqlDbType.Bit, 1, ParameterDirection.Output, False, 0, 0, String.Empty, DataRowVersion.Default, 0)
sqlparam(6) = New SqlParameter("@Default_Message", SqlDbType.VarChar, 200, ParameterDirection.Output, False, 0, 0, String.Empty, DataRowVersion.Default, String.Empty)
sqlparam(0).Value = User_UserName.Value
sqlparam(1).Value = User_Password.Value
sqlparam(2).Value = Station.Value
sqlparam(3).Value = Users_RowID
sqlparam(4).Value = Users_Name.Value
sqlparam(5).Value = Success
sqlparam(6).Value = Default_Message.Value
...
...
...
LoginCommand.Connection = conn
conn.Open()
LoginCommand.ExecuteNonQuery()
Users_RowID = Convert.ToInt32(sqlparam(3).Value)
Users_Name = Convert.ToString(sqlparam(4).Value)
Success = Convert.ToBoolean(sqlparam(5).Value)
Default_Message = Convert.ToString(sqlparam(6).Value)
conn.Close()
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply