Click here to Skip to main content
15,885,244 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
HERE IS MY INSERT BUTTON CODE
C#
private void btn_save_Click_1(object sender, EventArgs e)
       {
           string dt = Convert.ToDateTime(dateTimePicker1.Text.ToString()).ToShortDateString();
           int polfnum = 0;
           con.Close();
           try
           {
               string query = "SELECT COUNT(polf_number) FROM polf_details";
               con.Open();
               OleDbCommand cmd = new OleDbCommand(query, con);
               //OleDbDataReader oreader = cmd.ExecuteReader();

               //// Read the last value.
               //if (!oreader.Read())
               //{
               //       MessageBox.Show("Error reading from the table");
               //}
               //else
               //{
                     //polfnum = Convert.ToInt32(oreader.GetValue(1));
                     //polfnum = polfnum + 1;
                     // Display and Add it in the combobox for adding.
                     polfnum = (int)cmd.ExecuteScalar();
                     polfnum = polfnum - 1;
                     cb_polf_number.Items.Add(polfnum);
                     con.Close();
           //    }
           }
           catch (Exception ex)
           {
               MessageBox.Show("Error generating new number.\nError: " + ex.Message + "","ERROR");
               return;
           }
           string cust_name = cb_cust_name.Text.ToString();
           string circle = txt_circle.Text.ToString();
           int quantity = Convert.ToInt32(txt_qty.Text);
           string ponum = txt_po_num.Text.ToString();
           string artwrk = txt_artwork_name.Text.ToString();
           string moduletype = txt_module_type.Text.ToString();
           string product = txt_prod_name.Text.ToString();

           string SQLString = "INSERT INTO polf_details(polf_number, polf_date, customer_name, circle, quantity, po_number, artwork_no, module_type, product_type) VALUES('" + polfnum + "','" + dt + "','" + cust_name + "','" + circle + "', " + quantity + ",'" + ponum + "','" + artwrk + "','" + moduletype + "','" + product + "');";
           con.Open();
           //OleDbDataAdapter dAdapter = new OleDbDataAdapter(SQLString, connString);
           //create a command builder
           OleDbCommand cBuilder = new OleDbCommand(SQLString,con);

           int response = -1;
           try
           {
               response = cBuilder.ExecuteNonQuery();
           }
           catch (Exception ex)
           {
               MessageBox.Show(ex.Message);
           }
           if (response >= 1) MessageBox.Show("Entry is added to database", "Successful", MessageBoxButtons.OK, MessageBoxIcon.Information);
           txt_circle.Clear();
           txt_qty.Clear();
           txt_po_num.Clear();
           txt_artwork_name.Clear();
           txt_module_type.Clear();
           txt_prod_name.Clear();
           cb_cust_name.ResetText();
           cb_polf_number.ResetText();
           datasaved = true;
           btn_new_customer.Enabled = true;
           btn_new_polf.Enabled = true;
           btn_save.Enabled = false;
           con.Close();
       }
   }







AND HERE IS MY UPDATE EVENT CODE
C#
private void btn_update_Click(object sender, EventArgs e)
 {
     //sql query
     polf_details f1 = new polf_details();
     f1.con.Close();
     int polf_num=Convert.ToInt32( txt_polf_num_update.Text);
     string artwrk=txt_artwork_update.Text.ToString();
     string circle=txt_circle_update.Text.ToString();
     string module_type=txt_module_update.Text.ToString();
     string pono=txt_po_num_update.Text.ToString();
     string prod_type=txt_prod_name_update.Text.ToString();
     string cust_name=cb_customer_name_update.Text.ToString();
     int qty=Convert.ToInt32(txt_quantity_update.Text);
     string dt = Convert.ToDateTime(dateTimePicker_update.Text.ToString()).ToShortDateString();

     string SQLUpdateString = "UPDATE polf_details SET [polf_date] = @dt, [customer_name] = @cust_name, [circle]= @circle, [quantity]= @qty, [po_number]= @pono, [artwork_no]= @artwrk, [module_type]= @module_type, [product_type]=@prod_type WHERE [polf_number]= '"+ txt_po_num_update.Text+"'";

         OleDbCommand SQLCommand = new OleDbCommand();
         SQLCommand.CommandText = SQLUpdateString;
         SQLCommand.Parameters.AddWithValue("@dt", dt);
         SQLCommand.Parameters.AddWithValue("@cust_name", cust_name);
         SQLCommand.Parameters.AddWithValue("@circle", circle);
         SQLCommand.Parameters.AddWithValue("@quantity", qty);
         SQLCommand.Parameters.AddWithValue("@pono", pono);
         SQLCommand.Parameters.AddWithValue("@artwrk", artwrk);
         SQLCommand.Parameters.AddWithValue("@module_type", module_type);
         SQLCommand.Parameters.AddWithValue("@prod_type", prod_type);
         SQLCommand.Connection = f1.con;
         f1.con.Open();
         int response = SQLCommand.ExecuteNonQuery();
         if (response>=1) MessageBox.Show("Update successful!", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
         Close();
Posted
Updated 16-Dec-14 23:24pm
v2
Comments
Pheonyx 17-Dec-14 4:53am    
Have you tried running the queries manually and seeing if they throw an error or why they might not be working?

By Manually I mean by typing the code out in access.

Also, why do you use parameters for everything except the poif_number in your update statement and no parameters at all in your insert statement?
Member 11248655 17-Dec-14 22:52pm    
i wanted to check both approaches just to c which one was easier to understand.
and yes i will check the queries separately.
Sibasisjena 17-Dec-14 5:31am    
What is the exact error you are getting?
Member 11248655 17-Dec-14 22:47pm    
Sibasis(Muna) i am not getting any errors .. that's the main problem.
all i am getting is that the response= 0
Member 11248655 17-Dec-14 22:47pm    
Sibasis(Muna) i am not getting any errors .. that's the main problem.
all i am getting is that the response= 0

If you are getting a message box, then the message should explain the problem: if you aren't then the UPDATE request is likely to be caused by the WHERE condition not matching the actual data exactly: check for whitespace and that the value is exactly what you expect.

And for your own sake: Do not concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead - like you started to do in the UPDATE query and then tagged the really dangerous code onto the end!
 
Share this answer
 
Please, carefully, read solution1. There you'll find note about possible issues.

MS Access database does not know the meaning of @dt statement. To properly set named parameter, see these:
PARAMETERS Declaration (Microsoft Access SQL)[^]
Tips and Techniques for Queries in Access 2007[^]
Everything About Using Parameters from Code[^] - the old one, but very interesting!

EDIT:
A Beginner's Tutorial for Understanding ADO.NET[^]
Create a Business Logic Layer, Data Access Layer classes, and Stored Procedure scripts from a database table[^]
Social Club: Sample application using WinForms, C#.NET, ADO.NET and MS Access[^]
 
Share this answer
 
v2
Comments
Member 11248655 17-Dec-14 23:39pm    
to Maciej Los :
Hey. i am new to c#. but i did read Solution 1 ... and am testing the queries. But would really appreciate your help in the same matter as i am finding it a bit difficult to understand. please feel free to contact me on my personal mail id saurabhsahastrabudhe@gmail.com
Maciej Los 18-Dec-14 3:52am    
Sorry, but the only way we can contact each other is this board.
If you have any question or you want the answer to be improved by me, please, use "Reply" widget.
Member 11248655 22-Dec-14 0:45am    
Okay.. I read those links provided in above discussions. But still i am unable to figure out where i going wrong.. I am totally new to this programming please if you can guide me.
Maciej Los 22-Dec-14 1:53am    
See updated answer (new links after EDIT part).

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900