I cannot insert anything but "Id" values into tables

Question

I am currently building my first web app from scratch and trying to figure out the communication of C# and SQLDatabases, I've been trying to enter custom data into a table.

This code for some reason works perfectly fine, and it successfully adds "Id = 3" in a new row:

sql = " INSERT INTO dbo.AspNetUsers (Id) VALUES (3)";

    SqlCommand command = new SqlCommand(sql, cnn);
    SqlDataAdapter adapter = new SqlDataAdapter();

    adapter.InsertCommand = new SqlCommand(sql, cnn);
    adapter.InsertCommand.ExecuteNonQuery();

But this one does not, and the only difference is that it adds another item in a different column, as opposed to the previous one which it only adds the "Id":

sql = " INSERT INTO dbo.AspNetUsers (Id, UserName) VALUES (3, testName)";

    SqlCommand command = new SqlCommand(sql, cnn);
    SqlDataAdapter adapter = new SqlDataAdapter();

    adapter.InsertCommand = new SqlCommand(sql, cnn);
    adapter.InsertCommand.ExecuteNonQuery();

Lastly, another problem I have, I am unable to use either ExecuteReader() and ExecuteScalar() to read data from tables created by me as opposed the ones set up by the Framework auth system.

Thanks in advance.

`testName` should be surrounded by quotes. like any string value normally would. That said, you should really use paramtized queries. — Rafael Herscovici, Jun 28 '19 at 11:33

score 0 · Answer 1 · edited Jun 28 '19 at 18:14

0

This code addresses both issues:

the value of UserName is passed as a SQL parameter, which is recommended for string values to avoid SQL injection and other possible problems,
the SQL command is executed with ExecuteNonQuery, the correct way of calling SQL commands that do not return any result

    SqlCommand cmd = new SqlCommand();
    string sql = " INSERT INTO dbo.AspNetUsers (Id, UserName) VALUES (3, @testName)";
    cmd.Parameters.AddWithValue("@testName", "testName");
    cmd.Connection = con;
    cmd.CommandText = sql;
    con.Open();
    cmd.ExecuteNonQuery();

edited Jun 28 '19 at 18:14

Martin Staufcik

8,295
4
44
63

answered Jun 28 '19 at 11:56

S D

11
4

Use This to insert any string value or any values to avoid sql injection – S D Jun 28 '19 at 11:57
2

What does that do? How does it answer the question? Don't just blurt out code. Explain yourself! https://stackoverflow.com/help/how-to-answer – Rob Jun 28 '19 at 12:05

I cannot insert anything but "Id" values into tables

1 Answers1