Execute Parameterized SQL StoredProcedure via ODBC

Question

From within a C# WinForms app I must execute a parameterized Stored Procedure on a MS SQL Express Server. The Database Connection works, the Procedure works either, but I get an Error Message:

42000: Missing Parameter '@KundenEmail'

although I'm sure I added the parameter correctly. Maybe some of you could have a look - I don't know what to search for any more...

OdbcConnection ODBCConnection = new OdbcConnection();
try
{
    ODBCConnection.ConnectionString = ODBCConnectionString;
    ODBCConnection.Open();
}
catch (Exception DatabaseConnectionEx)
{
    if (ODBCConnection != null)
        ODBCConnection.Dispose();
    // Error Message
    return null;
}
OdbcParameter ODBCParameter = new OdbcParameter("@KundenEmail", OdbcType.NChar, 50);
ODBCParameter.Value = KundenEmail;
OdbcCommand ODBCCommand = new OdbcCommand("getDetailsFromEmail", ODBCConnection);
ODBCCommand.CommandType = CommandType.StoredProcedure;
ODBCCommand.Parameters.Add(ODBCParameter);
DataTable DataTable = new DataTable();
OdbcDataAdapter ODBCDatadapter = new OdbcDataAdapter(ODBCCommand);
ODBCDatadapter.Fill(DataTable);
ODBCDatadapter.Dispose();
ODBCConnection.Close();
ODBCConnection.Dispose();

This is the error message I get:

ERROR [4200][Microsoft][ODBC SQL Server]The Procedure or method 'getDetailsFromEmail' expects the '@KundenEmail'-parameter, which was not supplied.

Ah, I missed the connection string

private static String ODBCConnectionString = "Driver={SQL Server};Server=TESTSRV\\SQLEXPRESS;Database=TestDatabase;";

Any ideas? Thanks in advance.

Solution 1

Well - I now managed to solve the problem on my own, with some help from the MSDN-documentation.

The correct statement to execute a stored procedure via ODBC is as follows:

OdbcCommand ODBCCommand = new OdbcCommand("{call getDetailsFromEmail (?)}", ODBCConnection);
ODBCCommand.CommandType = CommandType.StoredProcedure;
ODBCCommand.Parameters.AddWithValue("@KundenEmail", KundenEmail);

Nevertheless - thanks for your help Thorsten.

Solution 2

How To Execute SQL Parameterized Stored Procedures by Using the ODBC .NET Provider and Visual C# .NET

While executing a parameterized stored procedure using the ODBC .NET Provider is little different from executing the same procedure using the SQL or the OLE DB Provider, there is one important difference: the stored procedure must be called using the ODBC CALL syntax rather than the name of the stored procedure.

Call Syntax Examples

Here is an example of the call syntax for an stored procedure that expects one input parameter:

{CALL CustOrderHist (?)}

Here is an example of the call syntax for a stored procedure that expects one input parameter and returns one output parameter and a return value. The first placeholder represents the return value:

{? = CALL Procedure1 (?, ?)

Sample Code

    public static void SheduleDocuments(int siteid, int docid)
    {
        DataTable objDt = new DataTable();
        OdbcConnection odbccon = new OdbcConnection();
        try
        {
            odbccon.ConnectionString =
             "Dsn=Dsn;" +
             "Uid=databaseuserid;" +
             "Pwd=databasepassword;";
            odbccon.Open();
            OdbcCommand cmd = new OdbcCommand("{call usp_GetEmpDetailsByIDanddepid(?,?)", odbccon);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@siteid", siteid);
            cmd.Parameters.AddWithValue("@DocumentIDs", docid);
            cmd.ExecuteNonQuery();
        }
        catch (OdbcException objEx)
        {
            string str = objEx.Message;
        }
        finally { odbccon.Close(); }
    }

Solution 3

Anyway it's better your code would look like this:

using (OdbcConnection connection = new OdbcConnection(connectionString) )
using (OdbcCommand command = connection.CreateCommand())
{
    command.CommandText = commandText;
    command.CommandType = CommandType.StoredProcedure;
    command.Parameters.Add("@KundenEmail", OdbcType.NChar, 50).Value = KundenEmail
    DataTable dataTable = new DataTable();
    connection.Open();
    using (OdbcDataAdapter adapter = new OdbcDataAdapter(command))
    {
        adapter.Fill(dataTable);
    }
}

But rather better to use SqlConnection/SqlCommand/SqlDataAdapter instead of ODBC types. Syntax will be still the same.

Comments

[dhh]

Hello Thorsten, thanks for your reply. I added the OdbcConnectionStringBuilder - I'm not able to use the direct SQL connection because I am told to use the ODBC Connection. For testing purpose I used the Sql-things before (because I already used it) and then switched it to ODBC. Somehow I'm not able to make it work...

[Thorsten Dittmar]

How about trying ODBCCommand.Parameters.AddWithValue(...)? Does that exist for OdbcCommands? The requirement of using the ODBC connection doesn't make sense if you connect to the SQL Server directly. Just out of curiousity: does it work when you use the respective Sql... classes?

[dhh]

Yes, indeed it works when I use Sql...-Classes. Well, the ODBC Connection is to be used, because it is not sure which database system will be used.

[abatishchev]

So don't forget to accept one of the answer - your one or somebody else's

[dhh]

Thanks for the hint - I already tried to accept my own answer but as I'm merely new to this site, I'm only allowed to accept my own answer in two days - "You can accept your own answer in 2 days". Nevertheless - thanks for all your help & ideas.

[Nick Vaccaro]

@dhh Just came across the same issue, but had a nullable parameter, so I didn't get an error code. You're answer just saved the day. Thank you much.