how to do this

[virusisfound]

I am using C# as front end and sql as back end.

EX. table name employee

feilds = empid,nm,add

I want to search code 003 if it present in database it show me message that this data is present and search for last empid+1 as empid. other wise if code 003 is not present in database then it accept that code as empid.

How can i solve this problem.....

thanks in advance

[ShahanDev]

Hi!

See this code(Didn't tested yet, SQL not present now), but can give you a start:

System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection("Data Source=.\\sqlexpress;Initial Catalog=myDataBase; Integrated Security=SSPI");
System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand("Select * from employee where empID=" + txtempid.Text);
cmd.Connection = con;
object obj = cmd.ExecuteScalar();
con.Close();

if (obj == null) //Means record not found, assign (MaxID + 1)
{
	con.Open();
	cmd = new System.Data.OleDb.OleDbCommand("Select MAX(empID) from employee where empID=" + txtempid.Text);
	cmd.Connection = con;
	System.Data.OleDb.OleDbDataReader rdr = cmd.ExecuteReader();
	txtempid.Text = (Convert.ToInt32(rdr["empID"]) + 1).ToString();
	// Get value, increase, and assign it to empid Textbox.
	con.Close();
}

[virusisfound]

I use this code to compare data but in this code the problem is that it compare text box2 data with each data in database and display message every time. if a database having large number of data then it display every time message that data is present and data is not present.

i want to compare with data base but if it found the data then it show message and stop at that point.

other wise it search in database and display that data is not present.

Please correct this code.......

SqlConnection cn = new SqlConnection();
            cn.ConnectionString = "path of database";
            string sSql = "select a1 from abc";
            SqlCommand cmd = new SqlCommand(sSql ,cn );
            SqlDataReader dr = new SqlDataReader();
            while (dr.Read())
            {
                textBox1.Text = dr[0].ToString();

                if (dr[0].ToString() == textBox2.Text)
                {
                    MessageBox.Show("data is present");

                }
                else
                {
                    MessageBox.Show("data not present");
                }




            }

Please....... Please....... Please....... Please.......

[kvprajapati]

string code = "";

            for (int i = 1; i <= 10; i++)
            {
                int no;
                int.TryParse(code, out no);
                no++;
                code = new string('0', 3 - no.ToString().Length) + no;
                Console.WriteLine(code);
            }

[AhmedSaud]

bool flag=false;// 
            SqlConnection cn = new SqlConnection();
            cn.ConnectionString = "path of database";
            string sSql = "select a1 from abc";
            SqlCommand cmd = new SqlCommand(sSql ,cn );
            SqlDataReader dr = new SqlDataReader();
            while (dr.Read())
            {
                textBox1.Text = dr[0].ToString();
 
                if (dr[0].ToString() == textBox2.Text)
                {
                    MessageBox.Show("data is present");
                    flag=true;//means data is present
                    break;//
 
                }              
            }
if(!flag)
 MessageBox.Show("data not present");

[lolafuertes]

Maybe you'll need, first, change the database in order the EmpId will be an Integer instead of string. Then create an SQL Sentence like "SELECT CASE WHEN NOT EXISTS( SELECT EmpId FROM Employee WHERE EmpId = " + txtEmpId.Text + ") THEN " + txtEmpId.Text + " ELSE MAX(EmpId)+1 END As EmpId FROM Employee;" that, hopefully, can solve the problem.

The query result will always give the right value for you

Lets explain a little what this SQL sentence does: NOT EXISTS( SELECT EmpId FROM Employee WHERE Empid = '" & txtEmpId.text & ") veryfies in the table Employee if there is any record with the searched value (txtEmpId.text), returning a true (if not) , or false (if yes), condition.

Then the SELECT CASE WHEN 'True' THEN TruePart ELSE FalsePart does the tric.

The true part (means not exists) returns the searched value.
The false part (means already exists) will get the greatest code existing (MAX(EmpId)) and add one to it to return the next empty value.

Then, using a datareader (lets call it dr as in the previous examples), you will always get one record, and the

dr.Read();
var NewEmpId = dr.GetInt32(0);
dr.Close;

will give you the answer.

If you need to present the message about found or not, then show it using an structure like

If (Ctype(txtEmpId.Text, Int) == NewEmpId)
{
' Not Found
}
Else
{
' Found
}

This approach lets the database do the work for you.

Hope this helps.
------------------------------------------------------------------------------------------------------------------------------
If you found this post helpful press the UP arrow of the post to Increase Feedback.

[ShahanDev]

Hi!

You can check this: (No need to declare any additional variable)

SqlConnection cn = new SqlConnection();
            cn.ConnectionString = "path of database"
            string sSql = "select a1 from abc";
            SqlCommand cmd = new SqlCommand(sSql, cn);
            cn.Open();
            SqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                textBox1.Text = dr[0].ToString();

                if (dr[0].ToString() == textBox2.Text)
                {
                    MessageBox.Show("data is present");
                    dr.Close();  // here record found so close the stream
                    break; // here data found so quit from the loop
                }
            }
       if (!dr.IsClosed) // when stream  not closed here this mean data not found
            { MessageBox.Show("data is not present");
                 dr.Close();  // Since stream not closed so close it.
            } 
       cn.Close();