Results 1 to 13 of 13

Thread: [RESOLVED] how to amend all record entries at once?

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Jun 2018
    Posts
    121

    Resolved [RESOLVED] how to amend all record entries at once?

    Hello,,,,
    I wrote the following code to amend all entries in any record in my sql table but it did not work when I try it
    It gives me an error
    Code:
    Incorrect syntext near ','
    Code:
    private void button2_Click(object sender, EventArgs e)
    {
        SqlConnection con = new SqlConnection
         (@"Data Source=(LocalDB)\MSSQLLocalDB;
        AttachDbFilename=D:\repos\WindowsFormsApp2\Database1.mdf;
        Integrated Security=True");
        try
        {
            
            DialogResult result = MessageBox.Show("Do you want to update record?", "Warning",
            MessageBoxButtons.YesNo, MessageBoxIcon.Warning);
            if (result == DialogResult.Yes)
            {
                con.Open();
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "update table1 set Product_name = '" + textBox4.Text + "' where Product_name= '" + textBox1.Text + "',set price = '" + textBox5.Text + "' where price='" + textBox2.Text + "'";
    
    
                cmd.ExecuteNonQuery();
                con.Close();
                MessageBox.Show("Record Has Been Inserted Successfully");
                FillDGV1();//Refresh DataGridView1 after adding data to table.
                          
            }
            else if (result == DialogResult.No)
            {
                return;
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
            con.Close();
        }
    }
    Last edited by Max45; Dec 5th, 2023 at 10:27 AM.

  2. #2
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,671

    Re: how to amend all record entries at once?

    Quote Originally Posted by Max45 View Post
    but it did not work when I try it
    Can you be a bit more specific in "did not work"? Did it throw an exception? Did it do nothing? Did the computer explode?
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  3. #3
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,290

    Re: how to amend all record entries at once?

    Spot the mistake...

    Code:
    update table1 
        set Product_name = '" + textBox4.Text + "' where Product_name= '" + textBox1.Text + "',
        set price = '" + textBox5.Text + "' where price='" + textBox2.Text + "'";

    i see 2 WHERE clauses.....
    ... and nevermind a String (note the Single Quotes) for a Field called "price"
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  4. #4

    Thread Starter
    Lively Member
    Join Date
    Jun 2018
    Posts
    121

    Re: how to amend all record entries at once?

    ok I am sorry
    it gives me an error
    Code:
    Incorrect syntext near ','

  5. #5
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,671

    Re: how to amend all record entries at once?

    So that is a SQL error and Zvoni correctly pointed out why. Can you layout in plain English what you're trying to do? My guess is that you want to make 2 separate update calls to table1 but I want you to confirm that first.
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  6. #6
    PowerPoster PlausiblyDamp's Avatar
    Join Date
    Dec 2016
    Location
    Pontypool, Wales
    Posts
    2,416

    Re: how to amend all record entries at once?

    Quote Originally Posted by Max45 View Post
    Hello,,,,
    I wrote the following code to amend all entries in any record in my sql table but it did not work when I try it
    It gives me an error
    Code:
    Incorrect syntext near ','
    Code:
    private void button2_Click(object sender, EventArgs e)
    {
        SqlConnection con = new SqlConnection
         (@"Data Source=(LocalDB)\MSSQLLocalDB;
        AttachDbFilename=D:\repos\WindowsFormsApp2\Database1.mdf;
        Integrated Security=True");
        try
        {
            
            DialogResult result = MessageBox.Show("Do you want to update record?", "Warning",
            MessageBoxButtons.YesNo, MessageBoxIcon.Warning);
            if (result == DialogResult.Yes)
            {
                con.Open();
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "update table1 set Product_name = '" + textBox4.Text + "' where Product_name= '" + textBox1.Text + "',set price = '" + textBox5.Text + "' where price='" + textBox2.Text + "'";
    
    
                cmd.ExecuteNonQuery();
                con.Close();
                MessageBox.Show("Record Has Been Inserted Successfully");
                FillDGV1();//Refresh DataGridView1 after adding data to table.
                          
            }
            else if (result == DialogResult.No)
            {
                return;
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
            con.Close();
        }
    }
    Are you sure you re using the correct textbox for each bit of the SQL string?

    Some suggestions...
    1. Give your controls decent names, nobody (including yourself in the future) will have a clue what TextBox1, TextBox4 etc. are supposed to be.
    2. Don't use string concatenation to build SQL strings, use parameters instead - there are plenty examples in this forum of how to do this.
    3. Check your datatypes, it looks as though are treating the price field in your DB as a string, is it really a string?

  7. #7
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,501

    Re: how to amend all record entries at once?

    Code:
    cmd.CommandText = "update table1 set Product_name = '" + textBox4.Text + "' where Product_name= '" + textBox1.Text + "',set price = '" + textBox5.Text + "' where price='" + textBox2.Text + "'";
    Produces an invalid SQL Command...
    Code:
    update table1 set Product_name = 'some value' where Product_name= 'orig value',set price = '12.123' where price='45.789'
    That's invalid ... there should be only one where clause, one set, and you should also be using an ID field to identify a row to update:
    Code:
    update table1 set Product_name = 'some value', price = '12.123' where ID = "ID Value"

    -tg

    edit - also if PRice it a numeric field, it doesn't need quotes:
    Code:
    update table1 set Product_name = 'some value', price = 12.123 where ID = "ID Value"
    Lastly... use parameters where you can.
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  8. #8
    Frenzied Member KGComputers's Avatar
    Join Date
    Dec 2005
    Location
    Cebu, PH
    Posts
    2,020

    Re: how to amend all record entries at once?

    Quote Originally Posted by Max45 View Post
    Hello,,,,
    I wrote the following code to amend all entries in any record in my sql table but it did not work when I try it
    It gives me an error
    Code:
    Incorrect syntext near ','
    Code:
    private void button2_Click(object sender, EventArgs e)
    {
        SqlConnection con = new SqlConnection
         (@"Data Source=(LocalDB)\MSSQLLocalDB;
        AttachDbFilename=D:\repos\WindowsFormsApp2\Database1.mdf;
        Integrated Security=True");
        try
        {
            
            DialogResult result = MessageBox.Show("Do you want to update record?", "Warning",
            MessageBoxButtons.YesNo, MessageBoxIcon.Warning);
            if (result == DialogResult.Yes)
            {
                con.Open();
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "update table1 set Product_name = '" + textBox4.Text + "' where Product_name= '" + textBox1.Text + "',set price = '" + textBox5.Text + "' where price='" + textBox2.Text + "'";
    
    
                cmd.ExecuteNonQuery();
                con.Close();
                MessageBox.Show("Record Has Been Inserted Successfully");
                FillDGV1();//Refresh DataGridView1 after adding data to table.
                          
            }
            else if (result == DialogResult.No)
            {
                return;
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
            con.Close();
        }
    }
    I'd rather revise the update query and use Parameters to avoid SQL Injection and other known issues. See post below as to why you should use Parameters.
    Database - Why should I use Parameters instead of putting values into my SQL string?
    MS Learn (documentation w/ examples)
    SQL Command Properties
    Configuring parameters and parameter data types
    CodeBank: VB.NET & C#.NET | ASP.NET
    Programming: C# | VB.NET
    Blogs: Personal | Programming
    Projects: GitHub | jsFiddle
    ___________________________________________________________________________________

    Rating someone's post is a way of saying Thanks...

  9. #9
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,501

    Re: how to amend all record entries at once?

    Quote Originally Posted by KGComputers View Post
    I'd rather revise the update query and use Parameters to avoid SQL Injection and other known issues. See post below as to why you should use Parameters.
    Database - Why should I use Parameters instead of putting values into my SQL string?
    MS Learn (documentation w/ examples)
    SQL Command Properties
    Configuring parameters and parameter data types
    Obligatory xkcd link ... https://xkcd.com/327/


    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  10. #10

    Thread Starter
    Lively Member
    Join Date
    Jun 2018
    Posts
    121

    Re: how to amend all record entries at once?

    Quote Originally Posted by dday9 View Post
    So that is a SQL error and Zvoni correctly pointed out why. Can you layout in plain English what you're trying to do? My guess is that you want to make 2 separate update calls to table1 but I want you to confirm that first.
    WHat I want tom do it is one update to all enteries in on record in table1

  11. #11

    Thread Starter
    Lively Member
    Join Date
    Jun 2018
    Posts
    121

    Re: how to amend all record entries at once?

    Quote Originally Posted by techgnome View Post
    Code:
    cmd.CommandText = "update table1 set Product_name = '" + textBox4.Text + "' where Product_name= '" + textBox1.Text + "',set price = '" + textBox5.Text + "' where price='" + textBox2.Text + "'";
    Produces an invalid SQL Command...
    Code:
    update table1 set Product_name = 'some value' where Product_name= 'orig value',set price = '12.123' where price='45.789'
    That's invalid ... there should be only one where clause, one set, and you should also be using an ID field to identify a row to update:
    Code:
    update table1 set Product_name = 'some value', price = '12.123' where ID = "ID Value"

    -tg

    edit - also if PRice it a numeric field, it doesn't need quotes:
    Code:
    update table1 set Product_name = 'some value', price = 12.123 where ID = "ID Value"
    Lastly... use parameters where you can.
    Thatnks it works
    Code:
    private void button2_Click(object sender, EventArgs e)
    {
        SqlConnection con = new SqlConnection
         (@"Data Source=(LocalDB)\MSSQLLocalDB;
        AttachDbFilename=D:\repos\WindowsFormsApp2\Database1.mdf;
        Integrated Security=True");
        try
        {
            
            DialogResult result = MessageBox.Show("Do you want to update record?", "Warning",
            MessageBoxButtons.YesNo, MessageBoxIcon.Warning);
            if (result == DialogResult.Yes)
            {
                con.Open();
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "update table1 set Product_name = '" + textBox4.Text + "', price = '" + textBox5.Text + "',  Quantity ='" + textBox6.Text + "' where id ='"+label6.Text+"'";
    
    
                cmd.ExecuteNonQuery();
                con.Close();
                MessageBox.Show("Record Has Been Inserted Successfully");
                FillDGV1();//Refresh DataGridView1 after adding data to table.
                          
            }
            else if (result == DialogResult.No)
            {
                return;
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
            con.Close();
        }

  12. #12
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,229

    Re: how to amend all record entries at once?

    This is purely a SQL question and really nothing to do with C#. As such, I have moved it to the Database Development forum.

    In future, you should get your SQL code working in the database itself first, then simply copy it over to your application code, making any replacements necessary.

    Also, you absolutely MUST stop using string concatenation to insert values into SQL code. Apart from the dangers of SQL injection, which would enable a malicious user to corrupt or even delete your entire database, it makes the code harder to read and that's one of the main reasons errors like this are made. If you can't clearly see what you've done, you're less likely to see what you've done wrong.

  13. #13
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,229

    Re: how to amend all record entries at once?

    Quote Originally Posted by Max45 View Post
    Thatnks it works
    Also, if your issue is resolved, please use the Thread Tools menu to mark the thread Resolved. That way, people can see that you need no more help without having to open your thread and read it. I have done so for you this time.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width