I'm having an issue at the moment which I am trying to fix. I just tried to access a database and insert some values with the help of C#
The things I tried (worked)
String query = "INSERT INTO dbo.SMS_PW (id,username,password,email) VALUES ('abc', 'abc', 'abc', 'abc')";
A new line was inserted and everything worked fine, now I tried to insert a row using variables:
String query = "INSERT INTO dbo.SMS_PW (id,username,password,email) VALUES (@id, @username, @password, @email)";
command.Parameters.AddWithValue("@id","abc")
command.Parameters.AddWithValue("@username","abc")
command.Parameters.AddWithValue("@password","abc")
command.Parameters.AddWithValue("@email","abc")
command.ExecuteNonQuery();
Didn't work, no values were inserted. I tried one more thing
command.Parameters.AddWithValue("@id", SqlDbType.NChar);
command.Parameters["@id"].Value = "abc";
command.Parameters.AddWithValue("@username", SqlDbType.NChar);
command.Parameters["@username"].Value = "abc";
command.Parameters.AddWithValue("@password", SqlDbType.NChar);
command.Parameters["@password"].Value = "abc";
command.Parameters.AddWithValue("@email", SqlDbType.NChar);
command.Parameters["@email"].Value = "abc";
command.ExecuteNonQuery();
May anyone tell me what I am doing wrong?
Kind regards
EDIT:
in one other line I was creating a new SQL-Command
var cmd = new SqlCommand(query, connection);
Still not working and I can't find anything wrong in the code above.
I assume you have a connection to your database and you can not do the insert parameters using c #.
You are not adding the parameters in your query. It should look like:
String query = "INSERT INTO dbo.SMS_PW (id,username,password,email) VALUES (@id,@username,@password, @email)";
SqlCommand command = new SqlCommand(query, db.Connection);
command.Parameters.Add("@id","abc");
command.Parameters.Add("@username","abc");
command.Parameters.Add("@password","abc");
command.Parameters.Add("@email","abc");
command.ExecuteNonQuery();
Updated:
using(SqlConnection connection = new SqlConnection(_connectionString))
{
String query = "INSERT INTO dbo.SMS_PW (id,username,password,email) VALUES (@id,@username,@password, @email)";
using(SqlCommand command = new SqlCommand(query, connection))
{
command.Parameters.AddWithValue("@id", "abc");
command.Parameters.AddWithValue("@username", "abc");
command.Parameters.AddWithValue("@password", "abc");
command.Parameters.AddWithValue("@email", "abc");
connection.Open();
int result = command.ExecuteNonQuery();
// Check Error
if(result < 0)
Console.WriteLine("Error inserting data into Database!");
}
}