CODEWITHSUNDEEP
c#sqldatabasevisual-studiovisual-studio-2015
Tim Hellegers
Tim Hellegers

Reputation: 97

Why is my SQL code in C# not working?

I wrote a SQL command to save some items in my database. But when I run it, it gives an error message:

enter image description here

And here is my code:

public void Opslaan(string titel, string rVoornaam, string rAchternaam, decimal beoordeling, string a1Voornaam, string a1Achternaam, string a2Voornaam, string a2Achternaam, string a3Voornaam, string a3Achternaam)
    {
        if (beoordelingBest < beoordeling)
        {
            titelBest = titel;
            beoordelingBest = beoordeling;
        }
        string queryString = "INSERT INTO Films (titel, beoordeling) VALUES('" + titel + "', " + beoordeling + ");" +
                             "INSERT INTO Acteurs (voornaam, achternaam, FilmID) VALUES('" + a1Voornaam + "' , '" + a1Achternaam + "', (SELECT FilmID from Films where titel = '" + titel + "'));" +
                             "INSERT INTO Acteurs (voornaam, achternaam, FilmID) VALUES('" + a2Voornaam + "' , '" + a2Achternaam + "', (SELECT FilmID from Films where titel = '" + titel + "'));" +
                             "INSERT INTO Acteurs (voornaam, achternaam, FilmID) VALUES('" + a3Voornaam + "' , '" + a3Achternaam + "', (SELECT FilmID from Films where titel = '" + titel + "'));" +
                             "INSERT INTO Regisseurs (voornaam, achternaam, FilmID) VALUES('" + rVoornaam + "' , '" + rAchternaam + "', (SELECT FilmID from Films where titel = '" + titel + "'));";
        command = new SqlCommand(queryString, con);

Can someone please help me with this? I can't figure it out.

Upvotes: 1

Views: 162

Answers (3)

Igor
Igor

Reputation: 62213

  1. Use parametererized queries and do not use string concatination. This is to prevent sql injection attacks but also errors with the values like forgetting to make sure strins are escaped (if a string contains a ' for example).
  2. If you have multiple queries each unique parameter value should have its own parameter name/value
  3. Wrap your ado.net database types (SqlConnection, SqlCommand, etc) in using blocks if they are disposable
  4. Never reuse connections as global objects, create, use, and destroy them when needed.

Here is the updated code with 1 statement, you can append additional statements to this and add more parameters as necessary.

var query = "INSERT INTO Acteurs (voornaam, achternaam, FilmID) SELECT @a1Voornaam, @a1Achternaam, FilmID from Films WHERE titel = @title";

using(var con = new SqlConnection("connection string here"))
using(var command = new SqlCommand(queryString, con))
{
  command.Parameters.Add(new SqlParameter("@a1Voornaam", SqlDbType.VarChar){Value = a1Voornaam});
  command.Parameters.Add(new SqlParameter("@achternaam", SqlDbType.VarChar){Value = achternaam});
  command.Parameters.Add(new SqlParameter("@title", SqlDbType.VarChar){Value = title});

  con.Open();
  command.ExecuteNonQuery();
}

Upvotes: 3

ASH
ASH

Reputation: 20302

Are you using TextBoxes? I can't tell for sure. Try something like this, and change to suit your specific needs.

using System.Data.SqlClient;

protected void Button1_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection(System.Configuration.
ConfigurationManager.ConnectionStrings["con"].ToString());
    try
    {
        string query = "insert into UserDetail(Name,Address) 
        values('" + txtName.Text + "','" + txtAddress.Text + "');";
        SqlDataAdapter da = new SqlDataAdapter(query, con);
        con.Open();
        da.SelectCommand.ExecuteNonQuery();
        con.Close();
        lblmessage.Text = "Data saved successfully.";
    }
    catch
    {
        con.Close();
        lblmessage.Text = "Error while saving data.";
    }

}

Upvotes: 0

user1023602
user1023602

Reputation:

Perhaps one of your values is ');

That would terminate the INSERT statement early, and cause the error.

                                                   |
                                                   V
  INSERT INTO Films (titel, beoordeling) VALUES('');,'anything');

You should use SqlParameters instead of string concatenation.

Upvotes: 0

Related Questions