SQL Insert Query Using C

Question

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

User · Answer

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

User · Answer

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

User · Answer

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  lt  0                      MessageBox Show  Error                     MessageBox Show  Record        Message   MessageBoxButtons OK  MessageBoxIcon Information                   con Close                    loader                                    catch  Exception ex                        MessageBox Show ex Message               con Close

User · Answer

Try String query    quot INSERT INTO dbo SMS PW  id username password email  VALUES   id  username   password   email  quot   using SqlConnection connection   new SqlConnection connectionString   using SqlCommand command   new SqlCommand query  connection           a shorter syntax to adding parameters     command Parameters Add  quot  id quot   SqlDbType NChar  Value    quot abc quot        command Parameters Add  quot  username quot   SqlDbType NChar  Value    quot abc quot          a longer syntax for adding parameters     command Parameters Add  quot  password quot   SqlDbType NChar  Value    quot abc quot        command Parameters Add  quot  email quot   SqlDbType NChar  Value    quot abc quot          make sure you open and close after executing  the connection     connection Open        command ExecuteNonQuery

User · Answer

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 lt SqlParameter gt  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

User · Answer

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

User · Answer

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

User · Answer

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  lt  0              Console WriteLine  Error inserting data into Database

[c#] SQL Insert Query Using C#

Examples related to c#

Examples related to sql

Examples related to sql-server-express