UPDATE STATEMENT in asp.net using c#

Question

i am using asp.net with C# as code behind


   OleDbConnection cn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Documents and Settings\CJP\My Documents\Visual Studio 2005\WebSites\NewElligibleSoft\elligiblity.mdb;Persist Security Info=False");
        cn.Open();
        string sql = "UPDATE main SET s_name='"+TextBox1.Text+"',inst_code='"+DropDownList1.SelectedItem+"',ms_oms='"+Label7.Text+"',elligiblity='"+Label12.Text+"',Board='"+DropDownList5.SelectedItem+"',percentage='"+TextBox4.Text+"' WHERE elg_id = '"+DropDownList4.SelectedItem+"'";
        OleDbCommand cmd = new OleDbCommand(sql, cn);
        cmd.ExecuteNonQuery();
        cmd.Dispose();
        cn.Close();
        Response.Write("alert('DATA UPDATED')");

i am getting error on

cmd.ExecuteNonQuery();

that Data type mismatch in criteria expression.

Zo Has · Accepted Answer · 2014-09-08 09:08:16Z

4

Don't code like

string connection_string="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Documents and Settings\CJP\My Documents\Visual Studio 2005\WebSites\NewElligibleSoft\elligiblity.mdb;Persist Security Info=False";
using(OleDbConnection cn = new OleDbConnection(connection_string))
{
     cn.Open();
     string sql = "UPDATE main SET s_name=?,inst_code=?,ms_oms=?,elligiblity=?,Board=?,percentage=?,amount=? WHERE elg_id =?";

     using(OleDbCommand cmd = new OleDbCommand(sql, cn))
     {
          cmd.Parameters.Add(new OleDbParameter("s_name",TextBox1.Text.Trim()));
          cmd.Parameters.Add(new OleDbParameter("inst_code",DropDownList1.SelectedItem.Value.ToString()));
          cmd.Parameters.Add(new OleDbParameter("ms_oms",Label7.Text.ToString()));
          cmd.Parameters.Add(new OleDbParameter("elligiblity",Label12.Text));
          cmd.Parameters.Add(new OleDbParameter("Board",DropDownList5.SelectedItem.Value.ToString()));
          cmd.Parameters.Add(new  OleDbParameter("percentage",DropDownList5.SelectedItem.Value.ToString()));
          cmd.Parameters.Add(new OleDbParameter(amount",DropDownList5.SelectedItem.Value.ToString()));
          cmd.Parameters.Add(new OleDbParameter("elg_id",DropDownList5.SelectedItem.Value.ToString()));
          cmd.ExecuteNonQuery();
          cn.Close();
     }
}        
 Response.Write("alert('DATA UPDATED')");

edited Sep 8, 2014 at 9:08

answered Jul 10, 2010 at 6:21

Zo Has

13.1k24 gold badges92 silver badges150 bronze badges

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

2 Comments

Zo Has Over a year ago

hey, you are right that code 'does' work but its not recommended. Cheers.

Zo Has Over a year ago

Yeah, sure depending on your database you just need to change the binding parameters. In Sql Server we use '@parameterName' instead of '?'.Cheers.

Fyodor Soikin · Accepted Answer · 2010-07-10 04:17:02Z

1

Remove single quotes around DropDownList4.SelectedItem. I bet your elg_id column is of type integer or something, and you're giving it a string.

Having that said, you would be really better off if you provided text of error, database table structure and maybe some other information so that people wouldn't have to read your mind.

answered Jul 10, 2010 at 4:17

Fyodor Soikin

81.3k10 gold badges134 silver badges179 bronze badges

1 Comment

Fyodor Soikin Over a year ago

You're welcome. If my answer helped you, would you please consider accepting it? (just click that hollow checkmark to the left)

Chase Florell · Accepted Answer · 2010-07-10 04:26:46Z

1

Can you try DropDownList1.SelectedItem.Text or DropDownList1.SelectedItem.Value

This should be the same for all DropDownLists.

Also you might have to convert TextBox4 to the appropriate datatype for "percentage".

Assuming that the percentage is a Double, you'd need something like

Double.Parse(Textbox4.Text)

Lastly, if you're not sending a "string" to the query, you would be really good to remove the single quotes from those fields. That way you're not parsing the data but still sending string information.

edited Jul 10, 2010 at 4:26

answered Jul 10, 2010 at 4:09

Chase Florell

47.6k59 gold badges192 silver badges382 bronze badges

1 Comment

Chase Florell Over a year ago

again, you basically need to convert (parse) all of your inputs (textboxes and dropdownlists) into the appropriate data types before submitting the query.

user383664 · Accepted Answer · 2010-07-10 04:25:32Z

this is de correct code


OleDbConnection cn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Documents and Settings\CJP\My Documents\Visual Studio 2005\WebSites\NewElligibleSoft\elligiblity.mdb;Persist Security Info=False");
        cn.Open();
        string sql = "UPDATE main SET s_name='" + TextBox1.Text + "',inst_code='" + DropDownList1.SelectedItem.Value.ToString() + "',ms_oms='" + Label7.Text + "',elligiblity='" + Label12.Text + "',Board='" + DropDownList5.SelectedItem.Value.ToString() + "',percentage='" + float.Parse(TextBox4.Text) + "',amount='" + Label10.Text + "' WHERE elg_id = " + DropDownList4.SelectedItem.Value + "";
        OleDbCommand cmd = new OleDbCommand(sql, cn);
        cmd.ExecuteNonQuery();
        cmd.Dispose();
        cn.Close();
        Response.Write("alert('DATA UPDATED')");

thanxx

j0k · Accepted Answer · 2012-08-28 13:15:35Z

0

OleDbConnection cn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Documents and Settings\CJP\My Documents\Visual Studio 2005\WebSites\NewElligibleSoft\elligiblity.mdb;Persist Security Info=False");
        cn.Open();
        string sql = "UPDATE main SET s_name='" + TextBox1.Text + "',inst_code='" + DropDownList1.SelectedItem.Value.ToString() + "',ms_oms='" + Label7.Text + "',elligiblity='" + Label12.Text + "',Board='" + DropDownList5.SelectedItem.Value.ToString() + "',percentage='" + float.Parse(TextBox4.Text) + "',amount='" + Label10.Text + "' WHERE elg_id = " + DropDownList4.SelectedItem.Value + "";
        OleDbCommand cmd = new OleDbCommand(sql, cn);
        cmd.ExecuteNonQuery();
        cmd.Dispose();
        cn.Close();
        Response.Write("alert('DATA UPDATED')");

edited Aug 28, 2012 at 13:15

j0k

22.8k28 gold badges81 silver badges90 bronze badges

answered Aug 28, 2012 at 12:59

Aakashi

411 bronze badge

1 Comment

Spontifixus Over a year ago

Welcome on SO! Please write a small statement in your answer on where the error was and how you fixed it. That makes your answer better understandable for other readers.

Collectives™ on Stack Overflow

UPDATE STATEMENT in asp.net using c#

5 Answers 5

2 Comments

1 Comment

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

1 Comment

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related