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

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

.NET Inside SQL Server (2005)

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

Author

Topic

azinyama
Starting Member

7 Posts

Posted - 2012-11-04 : 05:51:18

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...

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-04 : 11:52:02

I am familiar with VB.Net only in passing - so what I am saying below is based on my experience with C#.

Public Shared Sub user_login1(ByVal User_UserName As SqlString, 
	ByVal User_Password As SqlString, ByRef Station As SqlString, 
	<Out()> ByRef Users_RowID As SqlInt32, 
	<Out()> ByRef Users_Name As SqlString, 
	<Out()> ByRef Success As SqlBoolean, 
	<Out()> ByRef Default_Message As SqlString)

First, you will need to pass an output parameter as by ref and with the <Out()> attribute.

Second, To return the value to the caller, if I am not mistaken, you will need to assign it after the ExecuteNonQuery. When you have a statement like "sqlparam(3).Value = Users_RowID" with VB, I don't know if it is making a copy or just copying the reference, so I may be wrong in this.

Third, at least in C#, you have to explicitly convert the data to the type you want when going from SqlDbType to SqlInt32 using Convert function (or VB.Net's CType(?) function).


        LoginCommand.ExecuteNonQuery()
	Users_RowID = Convert.ToInt32(sqlparam(3).Value)

These are just my observations by reading your code - not really tested it.

azinyama
Starting Member

7 Posts

Posted - 2012-11-04 : 15:22:30

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.

[code="vb"]
<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
[/code]

Heeeellllpppp

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-05 : 07:32:27

You have not changed the ByVal's to ByRefs. Also, you will need use Convert in statements such as "sqlparam(3).Value = Users_RowID"

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-11-05 : 07:54:22

erm. You posted another question where I asked you why you want to use this example for the CLR.

Your code is doing nothing that requires CLR calls. You are only making things more complicated for no gain.

What's your end goal in this? To work out how the CLR works?

If that's the case then you would be better off writing a string splitter or other piece of code as an example.

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources