Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# SQL-Server
 UGIcon.Open();
            cmd = new SqlCommand("select * from purchase", UGIcon);
            SqlDataReader s = cmd.ExecuteReader();
            if (s.Read())
            {
                if (toolStripTextBox1.Text != s["cm"].ToString())
                {
                    MessageBox.Show("Please check the name");
                }
                else
                {
                    if (textBox2.Text == "")
                    {
                        errorProvider1.SetError(textBox2, "Provide Owner name");
                    }
                    else if (textBox3.Text == "")
                    {
                        errorProvider1.SetError(textBox3, "Provide Address");
                    }
                 
                    
 
                    else
                    {
 

                        int a = 0, b = 0, c = 0;
 
                        if (!int.TryParse(textBox5.Text,
                                            System.Globalization.NumberStyles.Integer,
                                            System.Globalization.CultureInfo.CurrentUICulture,
                                            out a))
                        {
                            MessageBox.Show("u need to enter value");
                        }
 
                        else if (!int.TryParse(textBox9.Text,
                                            System.Globalization.NumberStyles.Integer,
                                            System.Globalization.CultureInfo.CurrentUICulture,
                                            out b))
                        {
                            MessageBox.Show("u entered greater value");
                        }
                        else if (b > a)
                        {
                            MessageBox.Show("ur amount greater then total");
                           
                        }
                        else
                        {
                            c = a - b;
                            textBox10.Text = c.ToString(
                                System.Globalization.CultureInfo.CurrentUICulture);
                            long status = Convert.ToInt64(textBox10.Text);
                            if (status == 0)
                            {
                                textBox4.Text = "paid";
                            }
                            else
                            {
                                textBox4.Text = "pending";
                            }
                            // UGIcon.Open();
                            cmd = new SqlCommand("update purchase set om='" + textBox2.Text + "', address='" + textBox3.Text + "', phone='" + maskedTextBox1.Text.ToString() + "',baled='" + numericUpDown2.Value + "',paid='" + textBox9.Text + "',status='" + textBox4.Text + "',balance='" + textBox10.Text + "' where cm='" + toolStripTextBox1.Text + "'", UGIcon);
                            SqlDataReader dr;
                            dr = cmd.ExecuteReader();
                           
                            MessageBox.Show("Details has been updated sucessfully", "Update Window", MessageBoxButtons.OK, MessageBoxIcon.Information);
                        
                        }
                        
                       
                    }
 
                }
               
                UGIcon.Close();
 

            }
 
if i update if the presence of one record its working... if i added another record and update then "please check the name" messagebox displaying... not updating
Posted 24-Jan-13 3:02am
Edited 24-Jan-13 3:39am
v6
Comments
sjelen at 24-Jan-13 9:13am
   
You did not explain what your problem is or what is the error you get.
There are so many wrong things in this code, I wouldn't know where to begin.
selva_1990 at 24-Jan-13 9:18am
   
if i update if the presence of one record its working... if i added another record and update then "please check the name" messagebox displaying... not updating
selva_1990 at 24-Jan-13 9:26am
   
that is my error can u help me
Mike Meinz at 24-Jan-13 9:35am
   
What does the Visual Studio Debugger show for the values of toolStripTextbox1.Text and s("cm").ToString as you iterate through each of the rows?
 
You should use a Where clause on the Select rather than reading all of the rows in the table to find just one.
selva_1990 at 24-Jan-13 9:44am
   
simply message box is showing as please check the name... and i have used where clause also
CHill60 at 24-Jan-13 9:39am
   
What is toolStripTextBox1.Text set to when you debug this and what is s["cm"] at the same time. But I think your problems is that you are using cmd.ExecuteReader() when you're issuing an update command (have a look at ExecuteNonQuery instead)
selva_1990 at 24-Jan-13 9:52am
   
in s["cm"] have records like 1) aaa 2) bbb 3) ccc.... i have used cmd.executeReader() to check whether record is present or not... if record not there it ll display messagebox as please check the name otherwise it need to update
CHill60 at 24-Jan-13 10:00am
   
Firstly,
cmd = new SqlCommand("update ...., UGIcon);
SqlDataReader dr;
dr = cmd.ExecuteReader();
Is unlikely to work.
2) In s["cm"] you can not have multiple records at the point of the error - only one. What is that value
3) What have you put in toolStripTextBox1.Text - we cannot see your screen. What I do know is that it is not the same as the s["cm"] value
selva_1990 at 24-Jan-13 10:09am
   
no no data exist same as in DATABASE field "cm" only in toolstriptextbox...
1)using toolstriptextbox i just enter data as "aaa" and if click search...it will display all data in form...
2)now i need to update data related to "aaa"(now only that prob occurs if one record present its updating(if i had one more row as "bbb" at that time tat messagebox is displaying...
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

You did not explain what is the logic behind your code, what are you trying to achieve?
That message simply means that text in your textbox is different from text in 'cm' column in first row you get from database.
Why are you querying all records from 'purchase' and then using just first result?
 
I'm just guessing but try to change first query:
cmd = new SqlCommand(string.Format("select * from purchase where cm = '{0}'", toolStripTextBox1.Text), UGIcon);
 
Second query should be run with cmd.ExecuteNonQuery().
 
And most important, you should never write queries by concatenating strings.
Always use parametrized queries when possible, something like:
cmd = new SqlCommand("select * from purchase where cm = @cm", UGIcon);
cmd.Parameters.Add(new SqlParameter("@cm", toolStripTextBox1.Text);
 
You can find a lot of examples on this subject here on codeproject.
  Permalink  
Comments
CHill60 at 24-Jan-13 11:30am
   
My +5
sjelen at 24-Jan-13 12:05pm
   
Thanks.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Further to sjelen's answer and earlier comment
Quote:
There are so many wrong things in this code, I wouldn't know where to begin.
(which I happen to agree with) I thought I'd have a crack at practising my refactoring and peer review skills. The code I'm posting here may not be perfect but it demonstrates better and/or alternative ways of going about it. Look at the comments I've added to understand why I'm suggesting things.
 
// Do your validations first and flag ALL of the errors at the same time
// and be consistent in the way you are reporting errors to the user
// Personally I would put these into a separate function
bool errorsFound = false;  // or you could have int errorsFound = 0; and increment
if (textBox2.Text == "")
{
    errorProvider1.SetError(textBox2, "Provide Owner name");
    errorsFound = true;
}
if (textBox3.Text == "")
{
    errorProvider1.SetError(textBox3, "Provide Address");
    errorsFound = true;
}
int a = 0, b = 0, c = 0;
// using System.Globalization; means you can keep your code 
// less cluttered and easier to read
if (!int.TryParse(textBox5.Text, NumberStyles.Integer,
                    CultureInfo.CurrentUICulture, out a))
{
	errorProvider1.SetError(textBox5, "u need to enter value");
	errorsFound = true;
}
if (!int.TryParse(textBox9.Text, NumberStyles.Integer,
                    CultureInfo.CurrentUICulture, out b))
{
	errorProvider1.SetError(textBox9, "u need to enter value");
	errorsFound = true;
}
if (b > a)
{
	errorProvider1.SetError(textBox9, "ur amount greater then total");
	errorsFound = true;
}
// Check they've entered a name before searching the database!
if(toolStripTextBox1.Text.Trim() == "")
{
	errorProvider1.SetError(toolStripTextBox1, "enter a name");
	errorsFound = true;
}
if(errorsFound)
{
	MessageBox.Show("Please correct the errors indicated");
	return;  // get out of here if there are errors -- keeps indenting to a minimum
}
// End of Validations
// Get used to putting error handling in straight away
try
{
 
	UGIcon.Open();
	// See sjelen's solution - use a parameterised query
	// I also prefer to make it clear which commands are read and which are write
	SqlCommand cmdRead = new SqlCommand("select * from purchase where cm = @cm", UGIcon);
	cmdRead.Parameters.Add(new SqlParameter("@cm", toolStripTextBox1.Text));
	SqlDataReader s = cmd.ExecuteReader();
	bool readSuccess = s.Read();
	if(readSuccess && s.HasRows)
	{
		c = a - b;
		textBox10.Text = c.ToString(CultureInfo.CurrentUICulture);
		long status = Convert.ToInt64(textBox10.Text);
		
		// I happen to like the ternary operator so I would have done this ...
		textBox4.Text = (status == 0) ? "paid" : "pending";
 
		SqlCommand cmdWrite = new SqlCommand("update purchase set om='@p1',address=' @p2', phone='@p3',baled='@p4',paid='@p5',status='@p6',balance='@p7' where cm='@cm'", UGIcon);
		cmdWrite.Parameters.Add(new SqlParameter("@p1", textBox2.Text));
		cmdWrite.Parameters.Add(new SqlParameter("@p2", textBox3.Text));
		cmdWrite.Parameters.Add(new SqlParameter("@p3", maskedTextBox1.Text));
		cmdWrite.Parameters.Add(new SqlParameter("@p4", numericUpDown2.Value.ToString()));
		cmdWrite.Parameters.Add(new SqlParameter("@p5", textBox9.Text));
		cmdWrite.Parameters.Add(new SqlParameter("@p6", textBox4.Text));
		cmdWrite.Parameters.Add(new SqlParameter("@p7", textBox10.Text));
		cmdWrite.Parameters.Add(new SqlParameter("@cm", toolStripTextBox1.Text));
		
		// You're using the wrong method to update - do this instead
		int rowsUpdated = cmdWrite.ExecuteNonQuery();
 
		// Only 1 row should have been updated
		if(rowsUpdated == 1)
			MessageBox.Show("Details has been updated sucessfully", "Update Window", MessageBoxButtons.OK, MessageBoxIcon.Information);
		else if(rowsUpdated > 1)
			MessageBox.Show("Duplicate rows on database", "Update Window", MessageBoxButtons.OK, MessageBoxIcon.Information);
		else
			MessageBox.Show("Details have not been updated ", "Update Window", MessageBoxButtons.OK, MessageBoxIcon.Information);
	}
	else
	{
		MessageBox.Show("Name not found on database", "Update Window", MessageBoxButtons.OK, MessageBoxIcon.Information);
	}
	
	UGIcon.Close();
}
catch (Exception ex)
{
	// you can check for specific exception types here
	// and possibly show meaningful messages
	throw;
	// Never just do nothing in a catch block - it's a very 
	// bad habit to just "swallow" errors
}
  Permalink  
Comments
sjelen at 24-Jan-13 12:14pm
   
+5 for you effort :)
 
One thing you missed:
c = a - b;
textBox10.Text = c.ToString(CultureInfo.CurrentUICulture);
long status = Convert.ToInt64(textBox10.Text);
 
last line is unnecessary, 'status' will always be equal to 'c', no need for double conversion.
CHill60 at 24-Jan-13 16:47pm
   
d'oh ... missed that one ... good spot ... and good for selva_1990 to see these comments too ... looks like he's one that will learn from this :-)
selva_1990 at 24-Jan-13 13:06pm
   
i ll learn all this and inform u soon thanks for pointing my mistakes
selva_1990 at 24-Jan-13 13:10pm
   
thank you very much... now ly i knw we can do like this also... anyway after i improved my code and learned surly i'll reply

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



Advertise | Privacy | Mobile
Web02 | 2.8.140926.1 | Last Updated 24 Jan 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100