How to update data in DataGridView based on condition in sql

Ajit N

Hello, i want to update record ( based on condtion in sql query ) from DataGridView. i used the update method and i tried some code but it shows me below error on update method

OleDbCommand.Prepare method requires all parameters to have an explicitly set type.

OleDbDataAdapter da;
DataSet ds = null;
BindingSource bsource = new BindingSource();
ublic void BindData()
{
OleDbCommand cmd = new OleDbCommand("select srno,AccNumber,Admission_Fee,Family_fund,MonthlyCollection,MonthlyDeposit,Fund,SimpleLoanBal,SimpleInstallment,SimpleInt,UrgentLoanBal,UrgentInstallment,UrgentInt,EducationLoanBal,EducationInstallment,EducationInt,GuarantorInstallment,GuarantorInt,OtherLoanBal,OtherInstallment,OtherInt,BankLoanBal,BankInstallment,BankInt,TotalInstallment,OtherLoanCaption from transDemand where IssueDate=@IssueDate order by AccNumber Desc", con);
cmd.Parameters.AddWithValue("@IssueDate", datesearch.Value.ToString("dd-MM-yyyy"));
da = new OleDbDataAdapter(cmd);
ds = new DataSet();
OleDbCommandBuilder cmdbuild = new OleDbCommandBuilder(da);
da.Fill(ds, "transDemand");
bsource.DataSource = ds.Tables["transDemand"];
dataGridView1.DataSource = bsource;
private void btnupdate_Click(object sender, EventArgs e)
{
DataTable dt = ds.Tables["transDemand"];
this.dataGridView1.BindingContext[dt].EndCurrentEdit();
this.da.Update(dt);
BindData();
}

5 Replies

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

Vincent Maverick Durano8y ago
Hmmm i didnt know that you were using access database, anyway instead of using @ sign for parameters, try to use ? sign. For details, check this article: https://www.mikesdotnetting.com/article/26/parameter-queries-in-asp-net-with-ms-access
Ajit N8y ago
I tried your code but showing me this warning...

'System.Data.Oledb.OledbParameterCollection.Add(string,object)' is obsolete: 'Add(String parameterName,ObjectValue) has been Deprecated.Use AddWithValue(String parameterName,Object value)'.

and also same error on "this.da.Update(dt);"

i'm using ms access database and for IssueDate Column i set Date/Time Datatype.
Vincent Maverick Durano8y ago
Can you please post your updated code here? Also what is the sql datatype for IssueDate?
Ajit N8y ago
Hey Vincent Maverick Durano, i tried your code but still got same error..
Vincent Maverick Durano8y ago
Instead of doing this:
cmd.Parameters.AddWithValue("@IssueDate", datesearch.Value.ToString("dd-MM-yyyy"));
Try :
cmd.Parameters.Add("@IssueDate", SqlDbType.VarChar).Value = datesearch.Value.ToString("dd-MM-yyyy");
You need to be able to somehow determine what datatype your parameters will have to be.