C# Prepared Statement and multiple queries

Question

Is it possible to execute multiple queries with the same prepared statement (same OdbcCommand object)?

Below is the code I have and is throwing the following exception: System.Data.Odbc.OdbcException was caught Message=ERROR [07006] [IBM][CLI Driver] CLI0102E Invalid conversion. SQLSTATE=07006

 ...
        odbcConnection = myConnection.getOdbcConnection();
        odbcConnection.Open();
        odbcCommand = odbcConnection.CreateCommand();
        odbcTrans = odbcConnection.BeginTransaction(IsolationLevel.ReadCommitted);
        odbcCommand.Transaction = odbcTrans;
try{
    odbcCommand.CommandText = queryStatement1();
            odbcCommand.Parameters.AddWithValue("?ID1", parameter1);
            odbcCommand.Parameters.AddWithValue("?ID2", parameter2);
    ...
    odbcCommand.Parameters.AddWithValue("?ID11", parameter3);
    odbcCommand.Prepare();
            odbcCommand.ExecuteNonQuery();


    odbcCommand.CommandText = queryStatement2();
    odbcCommand.Parameters.AddWithValue("?ID1", parameter4);
            odbcCommand.Parameters.AddWithValue("?ID2", parameter5);
    ...
    odbcCommand.Parameters.AddWithValue("?ID13", parameter6);
    odbcCommand.Prepare();
            odbcCommand.ExecuteNonQuery();
    odbcTrans.Commit();
} catch(Exception e){ ... }
...

Same OdbcCommand object, two different queries...

queryStatement1() returns the query string insert into TABLE (field1,field2...) values (?,?,...) — esausilva
– esausilva, Commented Nov 6, 2012 at 19:48
just for reference if you are using an IBM DB, you can possibly get their native .NET data components. Also addwithvalue can be evil especially with dates, if you know the type, specify it in the parameter constructor. — Paul Farry
– Paul Farry, Commented Nov 6, 2012 at 22:49
I'm working with IBM DB2. Also, I just changed the way I add the parameters to odbcCommand.Parameters.Add("?ID7", OdbcType.VarChar).Value = parameter7; — esausilva
– esausilva, Commented Nov 6, 2012 at 23:17

esausilva · Accepted Answer · 2012-11-06 19:47:13Z

2

Found the answer. I just needed to clear the command parameters right before assigning a different query string to the command

...
odbcCommand.ExecuteNonQuery();

            odbcCommand.Parameters.Clear();
            odbcCommand.CommandText = queryStatement2();
...

answered Nov 6, 2012 at 19:47

esausilva

2,1366 gold badges30 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

C# Prepared Statement and multiple queries

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related