Stored Procedure not accepting parameter value

1
votes

I am new to SQL, so I need some help. I have a simple stored procedure that counts the rows in the select statement and returns the number of rows. I create an ODBC command and add all the information to it. When I call the stored procedure I get the error. Procedure or function 'CountUsers' expects parameter '@cacLogin', which was not supplied. The stored procedure works fine when I run it SMS. I have no Idea what the problem is. Any help will be greatly appreciated.

The stored procedure is:

CREATE PROCEDURE [dbo].[CountUsers]
    (@cacLogin VARCHAR(100), 
     @rowcount INT OUTPUT)
AS
    SELECT @rowcount = COUNT(*) 
    FROM UserInfo
    WHERE strCACLogin = @cacLogin

    RETURN @rowcount
GO

The SQL statement is:

public void storedprocedure()
{
    int i;
    OdbcConnection conn = new OdbcConnection(ConfigurationManager.ConnectionStrings["dbConnect3"].ConnectionString);

    OdbcCommand dbComm = new OdbcCommand();
    dbComm.Connection = conn;
    dbComm.CommandType = CommandType.StoredProcedure;
    dbComm.CommandText = "CountUsers";

    dbComm.Parameters.Add("@cacLogin", OdbcType.VarChar, 100).Value = "MAULDIN.THOMAS.C.12345";

    dbComm.Connection.Open();
    i = dbComm.ExecuteNonQuery();
}
3
c#sqlsql-serverparameter-passing
any reason you're using a OdbcConnection versus a SqlConnection? - Cam Bruce
It would make more sense to me if it was complaining about @rowcount. What happens if you switch to SqlConnection, SqlCommand, etc., instead of ODBC? - Crowcoder
You have an output parameter defined but you effectively ignore it and return the value instead of using the parameter. But I agree with the previous assessment, use SqlConnection instead unless you have a compelling reason to continue using ODBC. - Sean Lange
I have to use ODBC because that is the connection the client uses. - Tom Mauldin

3 Answers

1
votes

I'm not super experienced with calling stored procedures through OCDB myself but this documentation (https://support.microsoft.com/en-us/help/310130/how-to-execute-sql-parameterized-stored-procedures-by-using-the-odbc-n) suggests the proper way to call the stored procedure for your example would be:

 new OdbcCommand("{call CountUsers(?)}", conn);
0
votes

Change your Stored Procedure to this:

CREATE PROCEDURE [dbo].[CountUsers]
     @cacLogin VARCHAR(100)
AS
BEGIN
     DECLARE @rowcount INT

     SELECT @rowcount = COUNT(*)
     FROM UserInfo
     WHERE strCACLogin = @cacLogin

     RETURN @rowcount
END

And your C# code to this:

OdbcConnection conn = new OdbcConnection(ConfigurationManager.ConnectionStrings["dbConnect3"].ConnectionString);

OdbcCommand dbComm = new OdbcCommand();
dbComm.Connection = conn;
dbComm.CommandType = CommandType.StoredProcedure;
dbComm.CommandText = "CountUsers";

dbComm.Parameters.AddWithValue("@cacLogin", "MAULDIN.THOMAS.C.12345");
var returnParameter = dbComm.Parameters.Add("@rowcount", OdbcType.Int);
returnParameter.Direction = ParameterDirection.ReturnValue;

conn.Open();
dbComm.ExecuteNonQuery();
var i = returnParameter.Value;

I hope this helps you.

0
votes

As per the comments, a direct SQL connection should work ok.

Using SqlConn As New SqlConnection(ConfigurationManager.ConnectionStrings("dbConnect3").ConnectionString)
    SqlConn.Open()
    Using cmd As New SqlCommand("CountUsers", SqlConn)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.AddWithValue("@cacLogin", "MAULDIN.THOMAS.C.12345")
        cmd.Parameters.Add("@rowcount", SqlDbType.Int).Direction = ParameterDirection.Output
        cmd.ExecuteNonQuery();
    End Using
End Using

Sorry for using VB but the DB calls are equivalent.