wrong number of parameters sqlstate=07001

[snakay]

Hi,

I'm having CLI0100E Wrong number of parameters. SQLSTATE=07001 exception when I try to

private void PopulateTable()
        {
            bool IsConnecting = true;

            while (IsConnecting)
            {
                try
                {
                    cn = null;
                    cn = new OleDbConnection(strConn);
                    cn.Open();
                  sSqlCommand = "select a.sicil_no ,a.adi||\' \'||a.soyadi as isim,b.unvan_aciklama,a.ilkgir_tarihi,a.ayrilma_tarihi,a.sua,a.dogum_tarihi  "
                            + "from per015_ssk a,per003_ssk b";
                 da = new OleDbDataAdapter(cmd);
                    ds = new DataSet();
                    da.Fill(ds, "Izin");
                    tb = ds.Tables[0];
                    cn.Close();
 private void pbPost_Click(object sender, EventArgs e)
{              
if (ds.GetChanges(DataRowState.Modified) != null)
               
                {
                    DataSet dsChanges =ds.GetChanges(DataRowState.Modified);                    
                                try                                
                                {
                                    BuildCommands();
                                    // apply updates to the database
                                    da.Update(dsChanges,"Izin");
                                    ds.AcceptChanges();
                                }
                                catch (Exception exp)
                                {
                                    MessageBox.Show(exp.Message, "Error");
                                }
              }
}
private void BuildCommands()
         {
            
             OleDbConnection cn =
                 (OleDbConnection)da.SelectCommand.Connection;
          
              cn.Open();

             

             // Declare update command with parameters
             da.UpdateCommand = cn.CreateCommand();
             da.UpdateCommand.CommandText = "update  per010_ssk  set "
                                             + "   izin_tipi=?, "
                                             + "   hareket_tipi=?, "
                                             + "   izin_sure=? ,"
                                             + "   kullanim_sure=?, "
                                             + "   baslama_tarih=?, "
                                             + "   bitis_tarih=?, "
                                             + "   hakedis_tarih=?, "
                                             + "   sua=? "
                                    + " where sicil_no=?  and  donem=?  and hareket_tipi=? " 
                                    + " and baslama_tarih=?   and  bitis_tarih=? ";
             da.UpdateCommand.Parameters.Add("izin_tipi", OleDbType.Char, 0, "izin_tipi");
             da.UpdateCommand.Parameters.Add("hareket_tipi", OleDbType.Char, 0, "hareket_tipi");
             da.UpdateCommand.Parameters.Add("izin_sure", OleDbType.Integer, 0, "izin_sure");
             da.UpdateCommand.Parameters.Add("kullanim_sure", OleDbType.Integer, 0, "kullanim_sure");
             da.UpdateCommand.Parameters.Add("baslama_tarih", OleDbType.Date, 0, "baslama_tarih");
             da.UpdateCommand.Parameters.Add("bitis_tarih", OleDbType.Date, 0, "bitis_tarih");
             da.UpdateCommand.Parameters.Add("hakedis_tarih", OleDbType.Date, 0, "hakedis_tarih");
             da.UpdateCommand.Parameters.Add("sua", OleDbType.Integer, 0, "sua");
             da.UpdateCommand.Parameters.Add("donem", OleDbType.Char, 0, "donem");
             da.UpdateCommand.Parameters.Add("sicil_no", OleDbType.Char, 0, "sicil_no");

                             } // *** BuildCommands ***

Basically
-I select and populate dataset with joint tables
-Get the dataset for changes
-prepare aupdate for dataadapter
-and update.

It is an IBM DB2 9.5 environment. I do use datareader select and populate,insert,update and dataset fill OK with IBM Oledb driver.

I appreciate any help
thanks
snky

[sknake]

Count them:

    da.UpdateCommand.CommandText = "update  per010_ssk  set "
                                         + "   izin_tipi=?, " {1}
                                         + "   hareket_tipi=?, " {2}
                                         + "   izin_sure=? ," {3}
                                         + "   kullanim_sure=?, " {4}
                                         + "   baslama_tarih=?, " {5}
                                         + "   bitis_tarih=?, " {6}
                                         + "   hakedis_tarih=?, " {7}
                                         + "   sua=? " {8}
                                + " where sicil_no=? {9} and  donem=? {10}  and hareket_tipi=? {11}" 
                                + " and baslama_tarih=? {12}  and  bitis_tarih=? {13}";

Then:

         da.UpdateCommand.Parameters.Add("izin_tipi", OleDbType.Char, 0, "izin_tipi"); {1}
         da.UpdateCommand.Parameters.Add("hareket_tipi", OleDbType.Char, 0, "hareket_tipi"); {2}
         da.UpdateCommand.Parameters.Add("izin_sure", OleDbType.Integer, 0, "izin_sure"); {3}
         da.UpdateCommand.Parameters.Add("kullanim_sure", OleDbType.Integer, 0, "kullanim_sure"); {4}
         da.UpdateCommand.Parameters.Add("baslama_tarih", OleDbType.Date, 0, "baslama_tarih"); {5}
         da.UpdateCommand.Parameters.Add("bitis_tarih", OleDbType.Date, 0, "bitis_tarih"); {6}
         da.UpdateCommand.Parameters.Add("hakedis_tarih", OleDbType.Date, 0, "hakedis_tarih"); {7}
         da.UpdateCommand.Parameters.Add("sua", OleDbType.Integer, 0, "sua"); {8}
         da.UpdateCommand.Parameters.Add("donem", OleDbType.Char, 0, "donem"); {9}
         da.UpdateCommand.Parameters.Add("sicil_no", OleDbType.Char, 0, "sicil_no"); {10}

(13 != 10), thus you have the wrong number of parameters.

[snakay]

da.UpdateCommand.CommandText = "update per010_ssk set "
+ " izin_tipi=?, " {1}
+ " hareket_tipi=?, " {2}
+ " izin_sure=? ," {3}
+ " kullanim_sure=?, " {4}
+ " baslama_tarih=?, " {5}
+ " bitis_tarih=?, " {6}
+ " hakedis_tarih=?, " {7}
+ " sua=? " {8}
+ " where sicil_no=? {9} and donem=? {10} and hareket_tipi=? {11}"
+ " and baslama_tarih=? {12} and bitis_tarih=? {13}";

{2} and {11}-{5} and {12} , {6} and {13} are the same things, do I have to repeat them

[sknake]

When you're using "?" as the parameter then yes. Some drivers support named parameters and you don't have to but if you're using ?'s instead of parameter names then you need to specify the parameters in the exact orders the ?'s appear

[snakay]

after posting I did couple of tries yesterday and it worked with to columns. by "worked" I mean I did not get any message, that is in parallel with what you say. However nothing has been updated when I filled the grid again. In debug I see everything goes as planned ,that is it goes thru building commands, creating new dataset for modified data, update dataset and accept changes. Even though I modifies a single column on a single row, when I count the number of rows changed in dsChanges it count all the rows in the dataset, yet nothing changes even the row I worked on.
What do you think I'm missing.
snky

[snakay]

correction;
after posting I did couple of tries yesterday and it worked with to columns

after posting I did couple of tries yesterday and it worked with two columns
sorry

[snakay]

one other thing,
since the theory behind dataadapter updating is , it checks dataset and finds the rowstate changes and updates the datatable columns with the corresponding columns on the datasource based on what I provide when I prepare my update command, do I have to use where clause since dataadapter knows which columns on the dataset are modified
snky

[sknake]

hmm.. this sounds like you have a mess :) You should be able to turn on logging for the OleDb. I know you can force ODBC drivers to write to a text file in the ODBC setup but i'm not sure. You may know better than I since it is your driver.

Anyway... Why don't you use a typed dataset? I have never had the nerve to try updating data with a dynamically created dataset -- too many things could go wrong. The thing with typed datasets is they maintain the new value for every changed row+column as well as the old value. With your dataset if you update one of the fields included in the where clause it will fail to update the data. This is why knowing the old/new value is important, and could be your problem:

+ " where sicil_no=? {9} and donem=? {10} and hareket_tipi=? {11}"
+ " and baslama_tarih=? {12} and bitis_tarih=? {13}";

IF "baslama_tarih" is currently "1" and you change it to "5" then the update will fail because it is trying to locate it based on the new value. That is -- it will assemble:

Update Table
Set 
...
baslama_tarih = 5
Where baslama_tarih = 5

But it should be doing:

Update Table
Set
...
baslama_tarih = 5
Where baslama_tarih = 1 //The old value

That being said -- were you updating a field included in your where statement? Also you can call .AcceptChanges() on a row in a DataSet to mark it as "NotModified". So after you fill your dataset if the DataSet thinks they're all modified then call .AcceptChanges() on all of the rows. You may be able to call Acceptchanges() on the entire DataSet or DataTable.

I will wait to hear back on what fields you were modifying.

[sknake]

Here is an example for named parameters with the MSSQL Native driver. The OleDb SQL Provider also supported named parameters with the ":ParamName" convention so I know it can be implemented. Try this with your driver -- I think it should throw some sort of "ParamByName" exception if it doesn't implement the functionality.

private bool InsertRecord(string Loc, string Lab, string Tot)
    {
      const string connStr = @"Data Source=apex2006sql;Initial Catalog=Leather;Integrated Security=True;";
      List<string> Sql = new List<string>();
      Sql.Add("Insert Into Loc_Det (Loc, Lab, Tot)");
      Sql.Add("Select @Loc, @Lab, @Tot");
      Sql.Add("Where NOT EXISTS");
      Sql.Add("(");
      Sql.Add("  Select *");
      Sql.Add("  From Loc_Det (NOLOCK)");
      Sql.Add("  Where Loc_Det.Loc = @Loc and Loc_Det.Lab = @Lab and Loc_Det.Tot = @Tot");
      Sql.Add(")");
      Sql.Add("");
      Sql.Add("Select CAST(@@ROWCOUNT as int)");
      string query = GetText(Sql);
      using (SqlConnection conn = new SqlConnection(connStr))
      {
        conn.Open();
        using (SqlCommand cmd = new SqlCommand(query, conn))
        {
          cmd.Parameters.Add(new SqlParameter("@Loc", Loc));
          cmd.Parameters.Add(new SqlParameter("@Lab", Lab));
          cmd.Parameters.Add(new SqlParameter("@Tot", Tot));
          return (Convert.ToInt32(cmd.ExecuteScalar()) > 0);
        }
      }
    }

[sknake]

One last clarification ... In my Delphi I had to use the OleDb's SQL driver with parameters doing:

Select *
From Table
Where Column = :Param1

In that case the name of the parameter is Param1 . Only the SQL Server Native to my knowledge uses the @Param1 naming syntax so you likely shouldn't be using @'s.

[snakay]

for testing purposes I'm updating " kullanim_sure " which is not in where clause. But offcourse in real application I will be modifying fields which are within where statement.

I will try your other suggestions and let you know.

I have been using gupta team developer and parameter usage is exactly like delphi in db2 odbc environment. I will give it a try if it works in c# db2 combination with oledb. Life certainly can be much easier if it works that way.
snky

[snakay]

as you advised, when I place ds.AcceptChanges after populating , the problem of seeing the full dataset as modified, is resolved. Apparently when populating, I was also programmatically checking and filling columns, which probably meant modification on the rows and when counted the modified rows it counts them all. Now it sees only the row I work on as modified. That is good but it still does not update. I changed OleDb to IBM.Data.DB2 but no use, so still working on it
snky

[sknake]

Well at least you have one problem resolved :)

Let me know if you need any more help. Unfortunately since you're using DB2 this is a hard test environment to reproduce

[snakay]

I finally solved the problem, it is the Primary Key I missed in the populating dataset. While strugling, I have many questions raised. The reason I missed Pkey is that my current platform is not so much primary key bound. I don't want to argue about the virtues of it, since I noticed that .Net community is very sensitive about it, but I still cannot hold of my self from asking "there is got to be a way of updating without Pkey dependency." Since I made up a Pkey out of autonum column then I face another issue of synchronizing dataset autonum with datasource autonum, too much overhead and burocracy.

Another issue I have is;

Sometimes I create a new row for the datasource from a populated dataset where dataset row is not a new one, but by adding say a new data to a null cell I decide to create a new row for datasource. It seems dataadapter/dataset update method cannot handle a situation like this. If this is the case I would loop thru dataset and decide wich row is to be updated and which to be inserted as new for datasource.

Can you comment on this and maybe give a clue as to what to do.

Thanks
snky

[sknake]

>>since I noticed that .Net community is very sensitive about it
heh.

>>"there is got to be a way of updating without Pkey dependency."
there is. You can specify your "UpdateQuery" for the dataset. I usually do this in the designer but you're using a runtime created dataset, right? I'm sure there is a way to do it but I would have to look.

>>Sometimes I create a new row for the datasource from a populated dataset where dataset row is not a new one, but by adding say a new data to a null cell I decide to create a new row for datasource. It seems dataadapter/dataset update method cannot handle a situation like this. If this is the case I would loop thru dataset and decide wich row is to be updated and which to be inserted as new for datasource.

I have no idea what you're asking above. I'm thinking you might already have a row in the database with the PK value of "abc123" but rather than selecting it forth from the database, you're wanting to add the row manually have the dataset treat it as an existing row? Please confirm.

[snakay]

suppose you have 2 datasource tables; tblPersonnel as a and tblLeave as b and you populate dataset with both of them as joint.

select a.PersID,a.PersName,b.LeaveDate from tblPersonnel a,tblLeave b where a.PersID=b.PersID

So you have a row on dataset with;
a.PersID,a.PersName,b.LeaveDate and b.LeaveDate initially is null. You key-in a date for personnel to b.LeaveDate column as the date he will take a leave. So this is an update on the dataset row that is modified, whereas it will be a new record for tblLeave when posted. The question is is there a way in a dataset method to make an insert to tblLeave since you cannot make an update anyway because there is no record in tblLeave as such.

insert into tblLeave (LeaveDate) values ( "colLeaveDate" )

Remember I did not add a new row to dataset, I just entered a new data to a cell which was already populated and was null. I hope I made myself clear.
Thanks

[sknake]

I usually don't populate DataSets with join queries -- I create the relationship between two DataTables in the DataSet. However if you want to do a 1:1 join for display purposes or a "Left Join" then that wouldn't work. Unfortunately I haven't tried what you're attempting to do.

When I hit complex tables like you're dealing with I manually bring the data back in a DataTable and bind that to a grid and handle the CRUD operations myself.

[snakay]

Thanks Scott,

You have been great help

[nita_1]

I am getting this error:ERROR [07001] [IBM] CLI0100E Wrong number of parameters. SQLSTATE=07001
while executing below procedure
CREATE OR REPLACE PROCEDURE ECGLMDM.CUSTCOPY ( IN V_SRC_TBL_NAME VARGRAPHIC(100) DEFAULT 'CUSTOMER_ACCOUNT_LNDG_HISTORY', IN V_ARC_TBL_NAME VARGRAPHIC(100) DEFAULT 'CUSTOMER_ACCOUNT_LNDG_HISTORY_ARC', IN V_DAYS INTEGER DEFAULT 90 ) SPECIFIC SQL170810053210492 LANGUAGE SQL NOT DETERMINISTIC EXTERNAL ACTION MODIFIES SQL DATA CALLED ON NULL INPUT INHERIT SPECIAL REGISTERS OLD SAVEPOINT LEVELBEGINDECLARE STATMNT2 VARCHAR(1000); DECLARE STMT1 STATEMENT; SET STATMNT2 = 'insert into '||V_ARC_TBL_NAME||' select * from ' ||V_SRC_TBL_NAME|| ' where END_DATE >= CURRENT_TIMESTAMP-V_DAYS days';PREPARE STMT1 FROM STATMNT2;EXECUTE STMT1;END;
Please help to resolve

Thnaks

[Reverend Jim]

You'll increase the odds of getting an answer if you start a new thread rather than hijacking an old and already solved thread.