C#

[Hamza_13]

My tables are PURCHASE and SALES...

In PURCHASE table, fields are CODE(primary key), PRICE, DATE, TOTAL, QTY...

when I'm entering datas in form and when i click submit, the datas stored...

but when i enter same DATA(CODE), it is showing error...

i don't want to show the DEFAULT ERROR message...

so, i want to search whether the CODE is already in database or not...

i.e When i click SUBMIT button, 1st it has to search whether the CODE is already PRESENT in table or not. if it is not there, DATAS have to be stored. otherwise, it has to DISPLAY MESSAGE BOX.

[JOSheaIV]

Hello Hamza_13,

Can you please post the code you have written. It's kind of hard to follow exactly what your code is doing just by your original post. It would be also nice to see that you have attempted to do the work, as DaniWeb isn't a place to ask people to write you code for you (this includes school projects)

[Hamza_13]

when click on button if UserName is same in database table then show me messagebox otherwise insert ..

[JOSheaIV]

I see the problem, it's actually two pieces. First of all while you assign the parameter, with the the cmd.Parameters.AddWithValue("@UserName", this.txtUsername.Text)', you never actually call the code to query the database in the first place. You know something like cmd.ExecuteQuery();

Also your comparison probably isn't going to work.

You have if ("UserName" != this.txtUsername.Text), which is saying "if the username entered is not equal to "UserName" inject it into the table (you are comparing the TextBox value to a hardcoded string). I assume your intent was to query the table, and see if any records exist for the name, and if not, insert.

What you should look on doing is querying the table, and seeing if any records were returned. You could do this for instance with a "COUNT" sql command, and use it in relation to an ExecuteScalar call. You could also just query the table, and populate a DataTable with the results, and if the row count on it is greater then 0, then there were matches found (the 2nd is a little more work, but it's nice if you want to build a wrapper class that you can call to run queries and store the results).

Check out this link, it might help you with what you want
http://stackoverflow.com/questions/2807282/getting-mysql-record-count-with-c-sharp

[Hamza_13]

Brother, I'm getting this error please help.
Additional information: Specified cast is not valid.
Also getting error on ExecuteScalar();.

string fullname = txtFullname.Text;
                    string uname = txtUsername.Text;
                    string pass = txtPassword.Text;
                    string gender = cbGender.Text;
                    string utype = cbUsertype.SelectedValue.ToString();
                    string address = rtxtAddress.Text;
                    string number = txtContactnumber.Text;


                    string CheckString = @"(SELECT COUNT(*) FROM user WHERE UserName = '" + txtUsername.Text + " ')";
                    string qry = @"(INSERT INTO user (FullName,UserName,Password,Gender,idUserType,Address,ContactNumber) VALUES('" +
                        fullname + "','" + uname + "','" + pass + "','" + gender + "','" + utype + "','" + address + "','" + number + "')";

                   MySqlCommand commandAdd = new MySqlCommand(qry, con);
                    MySqlCommand commandCheck = new MySqlCommand(CheckString, con);
                    con.Open();
                    int Count = (int)commandCheck.ExecuteScalar();

                    if (Count > 0)
                    {
                        MessageBox.Show("Existing record.");
                    }
                    else
                    {
                        commandAdd.ExecuteNonQuery();
                        MessageBox.Show("Record added");
                    }

[JOSheaIV]

Well first of all your count query has an issue. You have a space after the Username. Notice the last string

string CheckString = @"(SELECT COUNT(*) FROM user WHERE UserName = '" + txtUsername.Text + "')";

Depending the SQL that may or may not matter (I know SQL Server can sometimes disregard trailing spaces). But that's just one.

As for your other error, if you look at the error, it's a casting error. ExecuteScalar by default returns a type object. So the code probably is having some issues trying to type cast it. Try this instead

int Count = Convert.ToInt32(commandCheck.ExecuteScalar());

[Hamza_13]

thanks working But another error.
first time I click the button it goes to if block and showing existing record for a username then I change the username and click the button Error is Connection.open()error Connection is already open something like that.

[Hamza_13]

now there's no connection already open error but still there is an error about the query I check the query it looks fine but still there's an error. also, run that query in MySQL on the database it works fine but when I run in C# then there's an error.

Error on commandAdd.ExecuteNonQuery();

string fullname = txtFullname.Text;
                    string uname = txtUsername.Text;
                    string pass = txtPassword.Text;
                    string gender = cbGender.Text;
                    string utype = cbUsertype.SelectedValue.ToString();
                    string address = rtxtAddress.Text;
                    string number = txtContactnumber.Text;


                    string CheckString = @"(SELECT COUNT(*) FROM user WHERE UserName = '" + txtUsername.Text + "')";
                    string qry = @"(INSERT INTO user (FullName,UserName,Password,Gender,idUserType,Address,ContactNumber) VALUES('" + fullname + "','" + uname + "','" + pass + "','" + gender + "','" + utype + "','" + address + "','" + number + "')";
                    MySqlCommand commandAdd = new MySqlCommand(qry, con);
                    MySqlCommand commandCheck = new MySqlCommand(CheckString, con);
                    con.Open();
                    int Count = Convert.ToInt32(commandCheck.ExecuteScalar());

                    if (Count > 0)
                    {
                        MessageBox.Show("Existing record.");
                    }
                    else
                    {
                        commandAdd.ExecuteNonQuery();
                        MessageBox.Show("Record added");
                    }
                    con.Close();

[Hamza_13]

i got it. Thanks Brother problem solved . just missing close bracket in string qry.

[JOSheaIV]

Ahh perfect. Also I see you added a Close to the code.

I should point out that I have read a few articles about closing your connection to a database. They all say it's better to open the connection while you need it, then close it when you are done. Even if this involves a single query.