SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 .NET Inside SQL Server (2005)
 T-SQL and CLR types for parameter "" do not match
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

azinyama
Starting Member

7 Posts

Posted - 11/04/2012 :  05:51:18  Show Profile  Reply with Quote

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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/04/2012 :  11:52:02  Show Profile  Reply with Quote
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.
Go to Top of Page

azinyama
Starting Member

7 Posts

Posted - 11/04/2012 :  15:22:30  Show Profile  Reply with Quote

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

Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/05/2012 :  07:32:27  Show Profile  Reply with Quote
You have not changed the ByVal's to ByRefs. Also, you will need use Convert in statements such as "sqlparam(3).Value = Users_RowID"
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 11/05/2012 :  07:54:22  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
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/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.25 seconds. Powered By: Snitz Forums 2000