'The parameter data type of UInt32 is invalid. (MS SQL Server)

In general we are supposed to pass integer values to our stored procedures and to do so we usually do it with this method

command.Parameters.AddWithValue("@param1", paramValue);

But, I found it very strange that if we need to pass uint datatype parameter to the stored procedure using above method, it gives a strange exception. Although its not when the code hits the ExecuteNonQuery method, but its after that. I am not sure why this is happening. If anyone have anything to share please...

Here's the stack trace:

   at System.Data.SqlClient.MetaType.GetMetaTypeFromValue(Type dataType, Object value, Boolean inferLen, Boolean streamAllowed)
   at System.Data.SqlClient.SqlParameter.GetMetaTypeOnly()
   at System.Data.SqlClient.SqlParameter.Validate(Int32 index, Boolean isCommandProc)
   at System.Data.SqlClient.SqlCommand.SetUpRPCParameters(_SqlRPC rpc, Int32 startCount, Boolean inSchema, SqlParameterCollection parameters)
   at System.Data.SqlClient.SqlCommand.BuildRPC(Boolean inSchema, SqlParameterCollection parameters, _SqlRPC& rpc)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
c++sharpsqlsql-server


Solution 1:[1]

According to the reference provided UInt32 is not supported in Sql.

Inferring a SqlDbType from UInt32 is not supported.

So it is good to pass parameter as;

command.Parameters.Add("@param1", SqlDbType.Int).Value = Convert.ToInt32(paramValue);

Solution 2:[2]

As you can try this:

cmd.Parameters.Add(new SqlParameter("@param", SqlDbType.Int));

You can check this Link for more.

Solution 3:[3]

UInt16 is not supported in ADO.NET. Use Int64 instead of UInt16. For more information visit: https://msdn.microsoft.com/library/yy6y35y8(v=vs.100).aspx

Solution 4:[4]

You can pass it like string. Use UInt32.ToString(). It's work perfectly.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Irshad
Solution 2 Suvro
Solution 3 Irshad
Solution 4 Irshad

Related Questions