insert and update database exception please help

I'm acctualy new to csharp and ado.net. i use Odbc.net I really confused about the eception. I'm using mysql server . I have table member and
i use the procedure below procedure to insert rewcord to table member.

// insert exception
When i inserted for the first time it works but then i got exception "[07002] SQLBindingParameter not used for all parameter". when inserting other record.

// this is the insert command
OdbcDataAdapter temp=Login.myDb.getdataAdapter();

temp.InsertCommand.CommandText="INSERT INTO member "+
"(nama,tempat_lahir, tgl_lahir,telp,ym,alamat, alamat_libur,skill,nim,password,divisi_id) "+
"VALUES (?,?,?,?,?,?,?,?,?,?,?)";
temp.InsertCommand.Parameters.Add("@nama", OdbcType.VarChar,30,"nama");.
.
.
.[cut]
. temp.InsertCommand.Parameters.Add("@divisi_id", OdbcType.Int,2,"divisi_id");

private void insertProfile_Click(object sender, System.EventArgs e)
{
DataRow newRow=Login.myDb.getDataTable("member").NewRow();
newRow["nama"]=this.namaIn.Text;
newRow["tempat_lahir"]=this.tempatLahirIn.Text;
newRow["tgl_lahir"]=this.tgllahirIn.Value.ToShortDateString();
newRow["telp"]=this.telpIn.Text;
newRow["ym"]=this.ymIn.Text;
newRow["alamat"]=this.alamatIn.Text;
newRow["alamat_libur"]=this.alamatLiburIn.Text;
newRow["skill"]=this.skillDetail.Text;
newRow["nim"]=this.nimIn.Text;
newRow["password"]=this.passwdIn.Text;
newRow["divisi_id"]=1;
Login.myDb.addRow(newRow,"member");
try
{
dataAdapter.Update(dataSet,"member");
dataSet.AcceptChanges();
this.statusProfil.Text="success";
}
catch(Exception ex){
this.statusProfil.Text="failed"+"/n"+ ex.Message;
Login.myDb.updateFail();
}

}

//update exception
when i want to update password one of the member table field i got exception concurrency violation 0 rows affected

//this is the update command
temp.UpdateCommand.CommandText="UPDATE member "+
"SET password=? WHERE nim=?";

temp.UpdateCommand.Parameters.Add("@password", OdbcType.VarChar,314,"password"); temp.UpdateCommand.Parameters.Add("@nim", OdbcType.VarChar,14,"nim");

DataRow[] Rows=(Login.myDb.getDataTable("member")).Select("nim="+Login.userNim);
DataRow targetRow=Rows[0];
if(targetRow!=null)
{
targetRow.BeginEdit();
targetRow["password"]=this.newpassword.Text;
targetRow.EndEdit();
DataSet dataSetChanged = (Login.myDb.getDataSet()).GetChanges(DataRowState.Modified);
if(dataSetChanged.HasErrors)
{
this.statusProfil.Text="failed";
}
else
{
try{
dataAdapter.Update(dataSetChanged,"member");
dataSet.AcceptChanges();

this.statusProfil.Text="success";
}
catch(Exception ex)
{
this.statusProfil.Text=ex.Message;
Login.myDb.updateFail();
}

}
}

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Andrzej Wegierski21y ago
Insert error: You must set parametrs before use - before insert any row. Update error: there's an error in primary key field value - may be spaces after the value or improper row status - may be extra AcceptChanges() call.