SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


T-SQL and CLR types for parameter "" do not match


T-SQL and CLR types for parameter "" do not match

Author
Message
azinyama
azinyama
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 10
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].[user] 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.[user] " &
"WHERE User_UserName = @User_UserName AND User_Password = @User_Password" &
"SELECT @Users_Name = (User_SName + ', ' + User_FName + '. ' + Title_Descr) " &
"FROM dbo.[user] " &
"INNER JOIN dbo.title ON (dbo.[user].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...
lnardozi 61862
lnardozi 61862
SSC-Enthusiastic
SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)

Group: General Forum Members
Points: 166 Visits: 615
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
azinyama
azinyama
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 10
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].[user] 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.[user] " &
"WHERE User_UserName = @User_UserName AND User_Password = @User_Password" &
"SELECT @Users_Name = (User_SName + ', ' + User_FName + '. ' + Title_Descr) " &
"FROM dbo.[user] " &
"INNER JOIN dbo.title ON (dbo.[user].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
lnardozi 61862
lnardozi 61862
SSC-Enthusiastic
SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)

Group: General Forum Members
Points: 166 Visits: 615
.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



azinyama
azinyama
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 10
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()


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search