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.
68 Answers
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!"); }
}Try
String query = "INSERT INTO dbo.SMS_PW (id,username,password,email) VALUES (@id,@username, @password, @email)";
using(SqlConnection connection = new SqlConnection(connectionString))
using(SqlCommand command = new SqlCommand(query, connection))
{ //a shorter syntax to adding parameters command.Parameters.Add("@id", SqlDbType.NChar).Value = "abc"; command.Parameters.Add("@username", SqlDbType.NChar).Value = "abc"; //a longer syntax for adding parameters command.Parameters.Add("@password", SqlDbType.NChar).Value = "abc"; command.Parameters.Add("@email", SqlDbType.NChar).Value = "abc"; //make sure you open and close(after executing) the connection connection.Open(); command.ExecuteNonQuery();
}The most common mistake (especially when using express) to the "my insert didn't happen" is : looking in the wrong file.
If you are using file-based express (rather than strongly attached), then the file in your project folder (say, c:\dev\myproject\mydb.mbd) is not the file that is used in your program. When you build, that file is copied - for example to c:\dev\myproject\bin\debug\mydb.mbd; your program executes in the context of c:\dev\myproject\bin\debug\, and so it is here that you need to look to see if the edit actually happened. To check for sure: query for the data inside the application (after inserting it).
static SqlConnection myConnection; public Form1() { InitializeComponent(); } private void button1_Click(object sender, EventArgs e) { myConnection = new SqlConnection("server=localhost;" + "Trusted_Connection=true;" + "database=zxc; " + "connection timeout=30"); try { myConnection.Open(); label1.Text = "connect successful"; } catch (SqlException ex) { label1.Text = "connect fail"; MessageBox.Show(ex.Message); } } private void Form1_Load(object sender, EventArgs e) { } private void button2_Click(object sender, EventArgs e) { String st = "INSERT INTO supplier(supplier_id, supplier_name)VALUES(" + textBox1.Text + ", " + textBox2.Text + ")"; SqlCommand sqlcom = new SqlCommand(st, myConnection); try { sqlcom.ExecuteNonQuery(); MessageBox.Show("insert successful"); } catch (SqlException ex) { MessageBox.Show(ex.Message); } }private void button1_Click(object sender, EventArgs e) { String query = "INSERT INTO product (productid, productname,productdesc,productqty) VALUES (@txtitemid,@txtitemname,@txtitemdesc,@txtitemqty)"; try { using (SqlCommand command = new SqlCommand(query, con)) { command.Parameters.AddWithValue("@txtitemid", txtitemid.Text); command.Parameters.AddWithValue("@txtitemname", txtitemname.Text); command.Parameters.AddWithValue("@txtitemdesc", txtitemdesc.Text); command.Parameters.AddWithValue("@txtitemqty", txtitemqty.Text); con.Open(); int result = command.ExecuteNonQuery(); // Check Error if (result < 0) MessageBox.Show("Error"); MessageBox.Show("Record...!", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information); con.Close(); loader(); } } catch (Exception ex) { MessageBox.Show(ex.Message); con.Close(); } }public static string textDataSource = "Data Source=localhost;Initial
Catalog=TEST_C;User ID=sa;Password=P@ssw0rd";
public static bool ExtSql(string sql) { SqlConnection cnn; SqlCommand cmd; cnn = new SqlConnection(textDataSource); cmd = new SqlCommand(sql, cnn); try { cnn.Open(); cmd.ExecuteNonQuery(); cnn.Close(); return true; } catch (Exception) { return false; } finally { cmd.Dispose(); cnn = null; cmd = null; }
}I have just wrote a reusable method for that, there is no answer here with reusable method so why not to share...
here is the code from my current project:
public static int ParametersCommand(string query,List<SqlParameter> parameters)
{ SqlConnection connection = new SqlConnection(ConnectionString); try { using (SqlCommand cmd = new SqlCommand(query, connection)) { // for cases where no parameters needed if (parameters != null) { cmd.Parameters.AddRange(parameters.ToArray()); } connection.Open(); int result = cmd.ExecuteNonQuery(); return result; } } catch (Exception ex) { AddEventToEventLogTable("ERROR in DAL.DataBase.ParametersCommand() method: " + ex.Message, 1); return 0; throw; } finally { CloseConnection(ref connection); }
}
private static void CloseConnection(ref SqlConnection conn)
{ if (conn.State != ConnectionState.Closed) { conn.Close(); conn.Dispose(); }
}class Program
{ static void Main(string[] args) { string connetionString = null; SqlConnection connection; SqlCommand command; string sql = null; connetionString = "Data Source=Server Name;Initial Catalog=DataBaseName;User ID=UserID;Password=Password"; sql = "INSERT INTO LoanRequest(idLoanRequest,RequestDate,Pickupdate,ReturnDate,EventDescription,LocationOfEvent,ApprovalComments,Quantity,Approved,EquipmentAvailable,ModifyRequest,Equipment,Requester)VALUES('5','2016-1-1','2016-2-2','2016-3-3','DescP','Loca1','Appcoment','2','true','true','true','4','5')"; connection = new SqlConnection(connetionString); try { connection.Open(); Console.WriteLine(" Connection Opened "); command = new SqlCommand(sql, connection); SqlDataReader dr1 = command.ExecuteReader(); connection.Close(); } catch (Exception ex) { Console.WriteLine("Can not open connection ! "); } }
}
