check exist value in SQL 2008 DB using foreach loop DataGridviewin c# Windows Application

Question

0.00/5 (No votes)

See more:

, +

hi iam using this code for row storage while check data is exist or not in DB sql server 2008

cmd.Parameters["@pname"].Value = row.Cells["gpname"].Value;
                            cmd.Parameters["@cstock"].Value = row.Cells["gcstock"].Value;
                            cmd.Parameters["@ondate"].Value = DateTime.Now;
                            check();

after to debug in comes in check function first time hasrow function is true value check and get display as value exist then loop 2nd time comes sqldatareader comes directly out of function and row function is false

using (Conn = new SqlConnection(constr))
            {
                var name = cmd.Parameters["@pname"].Value;
                var date = cmd.Parameters["@ondate"].Value;
                string dd = String.Format("{0:yyyy/MM/dd}", date);
                string query = "select pname from stock1 where pname='"+name+"' and ondate='"+dd+"'";
                cmd1 = new SqlCommand(query, Conn);
                cmd1.CommandType = CommandType.Text;
                try
                {
                    Conn.Open();
                SqlDataReader sdr = cmd1.ExecuteReader();
               
                    if(sdr.HasRows)
                    {


                        while (sdr.Read())
                        {
                            string s1 = sdr.GetString(0);
                            if (s1 == name.ToString())
                            {
                                MessageBox.Show("value already exist");

                            }
                            else
                            {
                                cmd.ExecuteNonQuery();
                            }

                        }
                    }
                }

how to set hasrow get always true execute reader in the program

Posted 8-Nov-14 0:00am

Soundararajan O M

Add a Solution

Comments

Tomas Takac 8-Nov-14 6:13am

I don't understand. You are calling check twice? Where? Or you mean looping trough while (sdr.Read()) two times? Setting aside the fact you should be doing this differently.

1 solution

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)

DamithSL · Answer 1 · 2014-11-08T00:39:00

I would do as below

C#

using(SqlConnection conn = new SqlConnection(constr))
using(SqlCommand comm = new SqlCommand("SELECT COUNT(*) FROM stock1 WHERE pname =@pname and ondate =@ondate", conn))
{
    comm.Parameters.AddWithValue("@pname", name);
    comm.Parameters.AddWithValue("@ondate", date);
    conn.Open();
    int count = (int) comm .ExecuteScalar();
    if(count>0)
        MessageBox.Show("value already exist");
}

You have Check method, update the method to pass name and ondate as method parameters.
nested database queries makes difficult to find the issues and while using readers and commands with same sql connection. You better refactor your code and try to apply below best practices.

-Don't share the connection objects, create one and use it, after you can properly dispose the connection. (wrap the connection, command etc with using blocks, it will automatically dispose the object)
- use parameters, it is safe and you are not open for sql injection attacks
- you can use ExecuteScalar method in this case to check whether row exist with given values. ( learn about methods available with which methods most suitable for your requirements)