locked
Update database in MS SQL, C#

    Question

  • Dear Guru

     

    I have to following code under a button click event to update my database:

    string myquery;
          
          mycon.Open();
          myquery = "Update Album set Alb_name = '" + txtb_album.Text + "' , Alb_release = '" + date_txtb.Text +
            "' where Alb_name = '" + txtb_album.Text + 
            '" Update Artist set A_name = '" + Artist_txtb.Text + 
            "' where A_name = '" + Artist_txtb.Text +
            '" Update Genre set Gen_type = '" + genre_txtb.Text + 
            "' where Gen_type = '" + genre_txtb.Text +
            '" Update Ratings set R_rate = '" + rating_cbox.Text + 
            "' where R_rate = '" + rating_cbox.Text +
            '" Update Songs set S_name = '" + track_txtb.Text + 
            "' where S_name = '" + track_txtb.Text + "' ";
          SqlCommand updatecom = new SqlCommand(myquery, mycon);
          updatecom.ExecuteNonQuery();
          mycon.Close();
    

    It is suppose to update several table at once, I'm not sure if you can do it this way, hence the error.

    Can someone correct this for me or show me a better way of coding this?

    Friday, July 08, 2011 6:48 PM

Answers

  • Put a semicolon between the individual statements.

    ...Where Alb_name = '" + txtb_album.Text + "'; Update Genre...


    Bob - www.crowcoder.com
    • Marked as answer by lenr0c Friday, July 08, 2011 7:10 PM
    Friday, July 08, 2011 6:57 PM

All replies

  • Put a semicolon between the individual statements.

    ...Where Alb_name = '" + txtb_album.Text + "'; Update Genre...


    Bob - www.crowcoder.com
    • Marked as answer by lenr0c Friday, July 08, 2011 7:10 PM
    Friday, July 08, 2011 6:57 PM
  • Also, using the Text property directly opens your code up to sql injection attacks and errors if someone enters something that contains an apostrophe, for instance.

    You can parameterize the query to guard against sql injection like:

    string sqlQry = "Update Album set Alb_name = @albname, Alb_release = @albrelease... etc";
    SqlCommand updatecom = new SqlCommand(sqlQry, mycon);
    updatecom.Parameters.AddWithValue("@albname", txt_album.Text); //Still should sanitize the text...
    updatecom.Parameters.AddWithValue("@albrelease", date_txtb_Text);
    etc...
    updatecom.ExecuteNonQuery();
    ...
    



    Bob - www.crowcoder.com
    Friday, July 08, 2011 7:04 PM
  • Thanks for the tip about the sql injection. I will keep it in mind.

    So, i inserted the semicolon between the seperate statements, but its still not working

          string myquery;
          
          mycon.Open();
          myquery = "Update Album set Alb_name = '" + txtb_album.Text + "' , Alb_release = '" + date_txtb.Text +
            "' where Alb_name = '" + txtb_album.Text + 
            '"; Update Artist set A_name = '" + Artist_txtb.Text + 
            "' where A_name = '" + Artist_txtb.Text +
            '"; Update Genre set Gen_type = '" + genre_txtb.Text + 
            "' where Gen_type = '" + genre_txtb.Text +
            '"; Update Ratings set R_rate = '" + rating_cbox.Text + 
            "' where R_rate = '" + rating_cbox.Text +
            '"; Update Songs set S_name = '" + track_txtb.Text + 
            "' where S_name = '" + track_txtb.Text + "' ";
          SqlCommand updatecom = new SqlCommand(myquery, mycon);
          updatecom.ExecuteNonQuery();
          mycon.Close();
    

    What am i still doing wrong?

    Friday, July 08, 2011 7:22 PM
  • Not quite right, notice the location of the quotes:

     myquery = "Update Album set Alb_name = '" + txtb_album.Text + "' , Alb_release = '" + date_txtb.Text +
        "' where Alb_name = '" + txtb_album.Text + 
        "'; Update Artist set A_name = '" + Artist_txtb.Text + 
        "' where A_name = '" + Artist_txtb.Text +
        "'; Update Genre set Gen_type = '" + genre_txtb.Text + 
        "' where Gen_type = '" + genre_txtb.Text +
        "'; Update Ratings set R_rate = '" + rating_cbox.Text + 
        "' where R_rate = '" + rating_cbox.Text +
        "'; Update Songs set S_name = '" + track_txtb.Text + 
        "' where S_name = '" + track_txtb.Text + "';"
    



    Bob - www.crowcoder.com
    Friday, July 08, 2011 7:27 PM
  • ok so i have used the other example you showed me

    mycon.Open();
          string sqlQry = "Update Album set Alb_name = @albname, Alb_release = @albrelease ; Update Artist set A_name = @Aname ; Update Genre set Gen_type = @gentype ; Update Ratings set R_rate = @rrate ; Update Songs set S_name = @sname ";
    
          SqlCommand updatecom = new SqlCommand(sqlQry, mycon);
          updatecom.Parameters.AddWithValue("@albname", txtb_album.Text); //Still should sanitize the text...
          updatecom.Parameters.AddWithValue("@albrelease", date_txtb.Text);
          updatecom.Parameters.AddWithValue("@Aname", Artist_txtb.Text);
          updatecom.Parameters.AddWithValue("@gentype", genre_txtb.Text);
          updatecom.Parameters.AddWithValue("@rrate", rating_cbox.Text);
          updatecom.Parameters.AddWithValue("@sname", track_txtb.Text);
    
          updatecom.ExecuteNonQuery();
          mycon.Close();
    

    but now it changes all the data in each column to the same thing?

    Friday, July 08, 2011 7:39 PM
  • You left out the Where clauses, let me write out the whole thing:

    "UPDATE [Album] SET [Alb_name] = @albname , [Alb_release] = @albrelease WHERE [Alb_name] = @albname ;" +
    " UPDATE [Artist] SET [A_name] = @artistname WHERE [A_name] = @artistname ;" +
    " UPDATE [Genre] SET [Gen_type] = @genretype WHER [Gen_type] = @genretype ;" +
    " UPDATE [Ratings] SET [R_rate] = @rating WHERE [R_rate] = @rating ;" +
    " UPDATE [Songs] SET [S_name] = @track WHERE [S_name] = @track ;";
    


    But only the first update clause is going to change data. All the others update a field to what its value is already. 

    Are you sure you don't want to, for instance, " UPDATE Artist SET A_name = @artistname WHERE A_id = @artistid" ?

     


    Bob - www.crowcoder.com
    Friday, July 08, 2011 7:51 PM
  • That's is actually what i am trying to do, but I'm not sure how.

    Sorry I'm really new to this and I'm still very unsure in what I have to do.

    After this I still have to do a Search and a delete

    Friday, July 08, 2011 8:01 PM
  • For each table you will need to know the primary key field(s) and the value(s). The WHERE clause of each statement will be like ... WHERE [key_field] = @keyvalue

    Of course I don't know if this is what you're working with, but I assume your form has these text boxes populated from the database and you want to edit the values and save the changes. When the textboxes are populated you should have access to that data. The data should have the primary key value that you can use in your WHERE clauses.


    Bob - www.crowcoder.com
    Friday, July 08, 2011 8:18 PM
  • That's is actually what i am trying to do, but I'm not sure how.

    Sorry I'm really new to this and I'm still very unsure in what I have to do.

    After this I still have to do a Search and a delete

    If you aren't sure, you can always split up the statements.

    Then execute them one by one, there is no harm in doing

    //statement1

    //addwithvalue1

    //execute1

    //statement2

    //addwithvalue2

    //execute2

    ...

    It's always easier to work with smaller fragments than the whole picture. It works the same, too.

    • Edited by ImmortalSmoke Tuesday, July 12, 2011 2:02 AM additions
    Tuesday, July 12, 2011 2:00 AM