Data base field type stored in two times

Question

0.00/5 (No votes)

See more:

C#

using (SqlConnection connect = new SqlConnection(db.Connectionstring()))
           {
               using (SqlCommand command = new SqlCommand())
               {
                   string pro = "";
                   int qty;
                   int totqty;

                   int i=0;

                        foreach (DataGridViewRow rows in dataGridView1.Rows)
                        {

                            SqlConnection con1 = new SqlConnection(db.Connectionstring());
                            con1.Open();
                            SqlCommand cmd1 = new SqlCommand("select * from sales where empnames='" + comboBox1.Text + "' ", con1);
                            SqlDataReader dr = cmd1.ExecuteReader();

                            if (dr.HasRows)
                            {
                                //while (dr.Read())
                                //{
                                    SqlConnection con2 = new SqlConnection(db.Connectionstring());
                                    con2.Open();

                                    SqlCommand cmd2 = new SqlCommand("update sales set empnames=@empnames+'" + comboBox1.Text + "',  categories=@categories, weight=@weight,per=@per,wastage=@wastage,customer=@customer,party=@party  where date=@date ", con2);

                                   cmd2.Parameters.AddWithValue("@empnames", Convert.ToString((comboBox1.Text)));
                                    cmd2.Parameters.AddWithValue("@date", Convert.ToDouble(rows.Cells[0].Value));
                                    cmd2.Parameters.AddWithValue("@categories", Convert.ToString(rows.Cells[1].Value));
                                    cmd2.Parameters.AddWithValue("@weight", Convert.ToString((rows.Cells[2].Value)));
                                    cmd2.Parameters.AddWithValue("@per", Convert.ToDouble(rows.Cells[3].Value));
                                    cmd2.Parameters.AddWithValue("@wastage", Convert.ToString((rows.Cells[4].Value)));
                                    cmd2.Parameters.AddWithValue("@customer", Convert.ToDouble(rows.Cells[5].Value));
                                    cmd2.Parameters.AddWithValue("@party", Convert.ToString((rows.Cells[6].Value)));




                                    cmd2.ExecuteNonQuery();
                                    con2.Close();


                                    SqlConnection con4 = new SqlConnection(db.Connectionstring());
                                    con4.Open();
                                    SqlCommand cmd4 = new SqlCommand("update sales set amtcst='" + txt_netamount.Text + "', amtparty='" + txtparty.Text + "', amtfinal='" + txtfinal.Text + "' where empnames='" + comboBox1.SelectedText + "'", con4);
                                    cmd4.ExecuteNonQuery();
                                    con4.Close();

                          // }
                            }


                            else
                            {
                                SqlConnection con3 = new SqlConnection(db.Connectionstring());
                                con3.Open();
                                SqlCommand cmd3 = new SqlCommand("insert into sales(empnames,date,categories,weight,per,wastage,customer,party,amtcst,amtparty,amtfinal)values('" + comboBox1.Text + "', '" + rows.Cells[0].Value + "','" + rows.Cells[1].Value + "','" + rows.Cells[2].Value + "','" + rows.Cells[3].Value + "','" + rows.Cells[4].Value + "','" + rows.Cells[5].Value + "','" + rows.Cells[6].Value + "','" + txt_netamount.Text + "','" + txtparty.Text + "','" + txtfinal.Text + "')", con3);
                                cmd3.ExecuteNonQuery();
                                con3.Close();


                            }
                        }

                   }
               }

this is my insert and updation script for my c# 2010 win forms, i have problem in empnames insert in two times to data base.

Any one give me ideas

What I have tried:

C#

data base field type stored in two times

Posted 27-Nov-16 3:04am

Boopalslm

Updated 27-Nov-16 4:13am

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

P_Z · Answer 1 · 2016-11-27T03:19:00

>> empnames=@empnames+'" ~~+ comboBox1.Text~~
>> cmd2.Parameters.AddWithValue("@empnames", Convert.ToString((comboBox1.Text)));

You're already passing @empnames as parameter (which is good) so you don't need to re add it with query. As is empname will be saved as: "sometext""sometext"

But from the 1st line think of sql injection queries...as you're passing any text to query instead of just parameter.

Also on another point is that there is too much functionality being done by 1 method.
Opening many connections, can re use the same but clear parameters each time
select
update
update
insert

No validation of data on server side...so some queries may fail ex insert a string instead of int, etc

Wendelius · Answer 2 · 2016-11-27T03:45:00

Solution 2

You're mixing parameters and literals and this is causing confusion for you. Use parameters and only parameters in your statements. Every time you start writing something like ..." + ... into an SQL statement you're most likely on the wrong track.

So the update should look something like

C#

SqlCommand cmd2 = new SqlCommand(
@"update sales 
  set empnames=@empnames,
      categories=@categories,      
      weight=@weight,
      per=@per,
      wastage=@wastage,
      customer=@customer,
      party=@party  
where date=@date ", con2);
cmd2.Parameters.AddWithValue("@empnames", comboBox1.Text);
cmd2.Parameters.AddWithValue("@categories", Convert.ToString(rows.Cells[1].Value));
cmd2.Parameters.AddWithValue("@weight", Convert.ToString((rows.Cells[2].Value)));
cmd2.Parameters.AddWithValue("@per", Convert.ToDouble(rows.Cells[3].Value));
cmd2.Parameters.AddWithValue("@wastage", Convert.ToString((rows.Cells[4].Value)));
cmd2.Parameters.AddWithValue("@customer", Convert.ToDouble(rows.Cells[5].Value));
cmd2.Parameters.AddWithValue("@party", Convert.ToString((rows.Cells[6].Value)));
cmd2.Parameters.AddWithValue("@date", Convert.ToDouble(rows.Cells[0].Value));

cmd2.ExecuteNonQuery();

But it doesn't stop there, you should fix all the statements to use parameters.

Also you should
- Open a connection only once inside a single method. Why repeatedly open it?
- Use using blocks to ensure that objects are disposed correctly.
- Use try..catch blocks to properly handle exceptions.

I suggest reading through Properly executing database operations[^]

Posted 27-Nov-16 3:45am

Wendelius

Comments

Boopalslm 27-Nov-16 9:51am

i am alter my code your procedure but cannot add to data base second row in same empnames, how to solve this error

Wendelius 27-Nov-16 10:58am

Not quite sure what you mean. If you get error message, please post it or some example data for the problem.

Boopalslm 27-Nov-16 11:02am

no error, i am bind my previous record, and enter second row but cannot stored second row in data base.

Wendelius 27-Nov-16 11:03am

What is the code you currently use?

Boopalslm 27-Nov-16 11:05am

above your code

Wendelius 27-Nov-16 11:10am

That updates a single row, how do you insert two rows? Do you still have the same foreach loop as in the code in the question?

Boopalslm 27-Nov-16 11:11am

no sir, how to use foreach loop in my update query.

Wendelius 27-Nov-16 11:17am

I don't mean that you should use a foreach in an update. Update commands updates an existing row. In the example you posted you had a foreach loop where you decided if you update or if you insert a row. The insert command was suffering from the same kinds of problems as the update command so have you fixed that already?

Also the decision whether to update or insert a row was made using a select statement. Have you debugged if the select statement works as expected?

Boopalslm 27-Nov-16 11:23am

sir what can i do sir.

Boopalslm 27-Nov-16 11:26am

sir if you don't mind totally alter my code sir. i will spend more and more time sir, but cannot get result.

Wendelius 27-Nov-16 13:01pm

I can't fix your code. I don't have the project nor the data nor the requirements and most importantly you wouldn't learn from it.

So you need to get the pieces together but people can help you as long as you try and have good questions.

Having that said, you currently seem to loop all the rows in the gridview. However, you make the decision whether to update or insert based on a selection of a combo box (comboBox1). That combo box has only one selection so could the reason be that even though you loop through several rows the decision whether to update is always the same. In other words is the logic incorrect.

Boopalslm 27-Nov-16 10:43am

sir give me some ideas.

Patrice T · Answer 3 · 2016-11-27T04:13:00

Solution 3

Never build an SQL query with concatenation like this:

C#

SqlCommand cmd1 = new SqlCommand("select * from sales where empnames='" + comboBox1.Text + "' ", con1);

If user input is exotic, the query will fail, if it is malicious,it opens door to SQL injection.
SQL Injection[^]
SQL injection - Wikipedia[^]

Posted 27-Nov-16 4:13am

Patrice T

Comments

Boopalslm 27-Nov-16 10:36am

so how alter my queries give me some ideas

Patrice T 27-Nov-16 10:46am

read second link

Boopalslm 27-Nov-16 10:52am

sir if you don't mind please above alter my command because i was already spend 4 days, till now i cannot get result.