Syntax error ini UPDATE statement....

Question

0.00/5 (No votes)

See more:

C#

protected void Button1_Click(object sender, EventArgs e)
    {
        string a, b, c, d, f, g;
        a = DropDownList2.SelectedItem.Text;
        b = DropDownList3.SelectedItem.Text;
        c = DropDownList4.SelectedItem.Text;
        d = DropDownList5.SelectedItem.Text;
        f = DropDownList6.SelectedItem.Text;
        g = DropDownList7.SelectedItem.Text;


        try
        {
            OleDbConnection con2 = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\\db_attendance.accdb");
            con2.Open();
            string upd12 = "update tbl_assigntask set class='" + a + "',subject='" + b + "',teacher='" + c + "',time='" + d + "',days='" + f+ "',subtype='" + g + "',totalstud=" + TextBox2.Text + ",batchqty=" + TextBox3.Text + ", batchname='" + TextBox4.Text + "' where ID=" + TextBox1.Text + "";
            OleDbCommand cmd12 = new OleDbCommand(upd12, con2);
            cmd12.ExecuteNonQuery();
            string ff2 = "Record Updated Sucessfully";

            ClientScript.RegisterStartupScript(this.GetType(), "myalert", "alert('" + ff2 + "');", true);
            con2.Close();
        }

        catch (Exception ex)
        {
            MessageBox.Show(ex.Message.ToString());
        }
    }

Posted 28-Mar-14 2:58am

Member 9599723

Updated 28-Mar-14 3:02am

CHill60

v3

Add a Solution

Comments

Member 9599723 28-Mar-14 8:59am

Can anybody helpme.......

CHill60 28-Mar-14 9:05am

Can you step through your code to the point where upd12 has been assigned, and post what it's value is

[no name] 28-Mar-14 9:06am

First thing to do is to get rid of the SQL injection attack waiting to happen. Your problem might just go away all by itself then.

ravikhoda 28-Mar-14 9:06am

not sure but you did not put single quote for totalstud,batchqty,ID. please add single quote like other columns and check.

Member 9599723 28-Mar-14 9:22am

that also i have tried.

CHill60 28-Mar-14 9:35am

Please use the Improve question link to post the following information:
1) The Type of each of the columns you have listed in the Update command - i.e. subject, teach, time, days, subtype, totalstud, batchqty, batchname and ID
2) Give the contents of each of the variables you are trying to assign to them i.e. a, b,c,d,f,g, TextBox2, TextBox3, TextBox4 and TextBox1
3) The content of upd12 after the line that contains the "update tbl_assigntask" ... etc

Member 9599723 29-Mar-14 1:01am

data type for all columns is text.
all variable value is text

3 solutions

Solution 3

OP has finally confirmed

Quote:
data type for all columns is text.
all variable value is text

and the variable contains the following

update tbl_assigntask set class='First',subject='c',teacher='JN',time='2:30:00 PM',days='Monday',subtype='Practicle',totalstud=50,batchqty=15, batchname='Batch1, ' where ID=65

As all of the columns are "text" then all of the values passed should be surrounded by single quotes so the code needs to change to

string upd12 = "update tbl_assigntask set class='" + a + "',subject='" + b + "',teacher='" + c + "',time='" + d + "',days='" + f+ "',subtype='" + g + "',totalstud='" + TextBox2.Text + "',batchqty='" + TextBox3.Text + "', batchname='" + TextBox4.Text + "' where ID='" + TextBox1.Text + "'"

The change is subtle, so to be clear the values passed in for columns totalstud, batchqty and ID need to be surrounded by single quotes... which is what ravikhoda said in a comment yesterday!

This problem would not have arisen if you had used Parameterized Queries[^]. Not only do they help prevent SQL Injection (so amusingly demonstrated by woudwijk in Solution 1) but all of that business with single quotes, column types etc is taken care of for you - I strong advise you to read up on them

Posted 29-Mar-14 3:11am

CHill60

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

woudwijk · Accepted Answer · 2014-03-28T03:16:00

Solution 1

Could you please test youre code with the folowing in the TextBox2.text field?

'; Drop table tbl_assignTask;--'

I think that wil fix this problem you are having for now...

edit: and Ravichova is right, youre missing a few '

Posted 28-Mar-14 3:16am

woudwijk

Updated 28-Mar-14 3:19am

v3

Comments

CHill60 28-Mar-14 9:32am

Funny, and yet not. To any readers of this post - do NOT follow this suggestion but DO read this article SQL Injection Attacks and Some Tips on How to Prevent Them[^]

CHill60 28-Mar-14 9:37am

And the single quotes are only "missing" if those fields are char or varchar columns

Member 9599723 29-Mar-14 1:24am

CHill60 i have tried query on database directly. the error occured on the time column and its datatype is text plz help me
update tbl_assigntask set class='First',subject='c',teacher='JN',time='2:30:00 PM',days='Monday',subtype='Practicle',totalstud=50,batchqty=15, batchname='Batch1, ' where ID=65

CHill60 29-Mar-14 9:11am

I've posted a solution now that you've given us this extra detail

Abhinav S · Accepted Answer · 2014-03-28T03:21:00

string upd12 = "update tbl_assigntask set class='" + a + "',subject='" + b + "',teacher='" + c + "',time='" + d + "',days='" + f+ "',subtype='" + g + "',totalstud=" + TextBox2.Text + ",batchqty=" + TextBox3.Text + ", batchname='" + TextBox4.Text + "' where ID=" + TextBox1.Text + ""

;

Try running this query in server studio to check if it is working.
There could be some numbers where you are pushing strings or vice versa.