Stuck in C# SQL

Question

Hello Every One I am new To C# and SQL can any one tell me where am I wrong. It is not inserting . And not giving any errors.

 SqlConnection myConnection = new SqlConnection("server=SHASHAK\\SQLEXPRESS;" +
                             "Trusted_Connection=yes;" +
                             "database=Abhishek; " +
                             "connection timeout=30");

private void btnShow_Click(object sender, EventArgs e)
{

    try
    {
        //myConnection.Open();
       //// SqlCommand cmd = new SqlCommand("select player_id, player_name , score from player", myConnection);
        SqlCommand cmd = new SqlCommand("INSERT INTO Abhishek (" + "  player_id, player_name " + ") VALUES (" + " @textBox1.Text, @textBox2.Text", myConnection);
       SqlDataAdapter adapter = new SqlDataAdapter(cmd);
       MessageBox.Show("Ok");

        //DataTable dt = new DataTable();
        //adapter.Fill(dt);
        //screen.DataSource = dt;



    }
    catch (SqlException ex)
    {
        MessageBox.Show("You failed!" + ex.Message);
    }
}

you aren't executing the command, and you aren't setting your parameter values. You commented out where you open the connection, which is pretty important — Jonesopolis, Apr 17 '14 at 18:13
And when you do execute the command you are missing a `)` however you really really should be using [Parametrized queries](http://stackoverflow.com/questions/5468425/how-do-parameterized-queries-help-against-sql-injection) — Scott Chamberlain, Apr 17 '14 at 18:15
[ExecuteNonQuery](http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery(v=vs.110).aspx) — mbeckish, Apr 17 '14 at 18:15
@AbhishekSharma because the c# code is syntactically correct but is generating invalid SQL. — D Stanley, Apr 17 '14 at 18:19

score 4 · Answer 1 · edited Apr 17 '14 at 18:53

4

Try This:

myConnection.Open();
SqlCommand cmd = new SqlCommand("INSERT INTO Abhishek (player_id, player_name)     
             VALUES(@playerid, @playername)", myConnection);
cmd.Parameters.AddWithValue("@playerid",textBox1.Text);
cmd.Parameters.AddWithValue("@playername",textBox2.Text);
int commandStatus = cmd.ExecuteNonQuery();

if(commandStatus > 0)
      MessageBox.Show("Row inserted Successfully!");
else
      MessageBox.Show("Row Insertion Failed!");

edited Apr 17 '14 at 18:53

Habib

219,104
29
407
436

answered Apr 17 '14 at 18:18

Sudhakar Tillapudi

25,935
5
37
67

its giving error "You failed " syntax error @player" – Abhishek Sharma Apr 17 '14 at 18:23
@AbhishekSharma: sorry i missed to specify the exact parameter names , please check my edit now – Sudhakar Tillapudi Apr 17 '14 at 18:24
giving error " must declare scaler variable @playerid" – Abhishek Sharma Apr 17 '14 at 18:27
again an error near "int commandStatus = cmd.ExecuteNonQuery();" – Abhishek Sharma Apr 17 '14 at 18:42
SQl unhandled "Incorrect syntax near '@playername'. – Abhishek Sharma Apr 17 '14 at 18:42

Habib · Accepted Answer · 2014-04-17T18:51:26.187

3

Few issues with your code.

You are trying to parameterized query but you are not passing any parameter to command
You need to use using statement with your connection and command object.
catch System.Exception after SqlException to catch any other exception.
You are not executing your command.

Your code could be on the following lines:

SqlConnection myConnection = new SqlConnection("server=SHASHAK\\SQLEXPRESS;" +
                   "Trusted_Connection=yes;" +
                   "database=Abhishek; " +
                   "connection timeout=30");
private void btnShow_Click(object sender, EventArgs e)
{
    try
    {
        using (myConnection)
        {
            using (SqlCommand cmd = new SqlCommand("INSERT INTO player (player_id, player_name) VALUES (@playerid, @playername)", myConnection))
            {
                cmd.Parameters.AddWithValue("@playerid", textBox1.Text);
                cmd.Parameters.AddWithValue("@playername", textBox2.Text);
                myConnection.Open();
                cmd.ExecuteNonQuery();
            }
        }
    }
    catch (SqlException ex)
    {
        MessageBox.Show("You failed!" + ex.Message);
    }
    catch (Exception ex)
    {
        //Show message / log
    }
}

Consider using names which reflect their values. e.g. txtPlayerID and txtPlayerName for textBox1 and textBox2 respectively.

edited Apr 17 '14 at 18:51

answered Apr 17 '14 at 18:19

Habib

219,104
29
407
436

`int.Parse` is not strictly nessesary, Sql will attempt to convert it for you when it performs the insert. Just be aware that it might not use your indexes in some special conditions depending on which variable (yours or the column you are doing something to) gets automatically cast. – Scott Chamberlain Apr 17 '14 at 18:33
@Habib Its giving me an error .. Invalid Object Abhishek – Abhishek Sharma Apr 17 '14 at 18:46
@AbhishekSharma, what is your table name ? is it same as your Database name ? – Habib Apr 17 '14 at 18:47
@AbhishekSharma, modified my code, your code in question had `INSERT INTO Abhishek`, it should be `INSERT INTO PLAYER`. Try the edited code in answer. – Habib Apr 17 '14 at 18:52
myConnection.Open(); SqlCommand cmd = new SqlCommand("select player_id, player_name , score from player", myConnection); SqlDataAdapter adapter = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); adapter.Fill(dt); screen.DataSource = dt; – Abhishek Sharma Apr 17 '14 at 18:54
@AbhishekSharma, what does that code has to do with the one in question ? – Habib Apr 17 '14 at 18:55
@AbhishekSharma, if it show data, then good enough. You should see [using statement](http://msdn.microsoft.com/en-us//library/yh598w02.aspx) and consider enclosing your connection and command object in `using` – Habib Apr 17 '14 at 19:07
Why it is showing connection property is not initialized – Abhishek Sharma Apr 17 '14 at 19:08
@AbhishekSharma, I can't really see what is going on. It should be a different question. You can ask a different question or search for the error first on Stackoverflow/google. If you can't figure out what is going on then post a new question. – Habib Apr 17 '14 at 19:10

Stuck in C# SQL

2 Answers2