CODEWITHSUNDEEP
c#sqlsql-server
NewbieProgrammer
NewbieProgrammer

Reputation: 864

How to check if a row is present in SQL Server table or not?

I am trying to check if there is a row present in a SQL Server table or not.

If the row exists (on a particular TicketID), it should show a messagebox that you can't continue further as there is already an entry in database. But if there isn't, it should insert some records (on that particular TicketID).

I tried try and catch but wasn't able to do it :

Here is the code of query: (hardcoded ticketID for example)

bool no;

try
{
    SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ST"].ConnectionString.ToString());
    con.Open();
    cmd.CommandText = "SELECT EngineerVisited from tblTicketTechnical where TicketID=1";
    cmd.Connection = con;
    rdr = cmd.ExecuteReader();

    while (rdr.Read())
    {
        bool = rdr.GetBoolean(0);
    }

    con.Close();
}
catch
{
    MessageBox.Show("Cannot continue");
}

I would really appreciate if someone could suggest a function that will return true if row is found and return false, if it isn't.

Upvotes: 2

Views: 2993

Answers (5)

Developerzzz
Developerzzz

Reputation: 1126

Create procedure and code like this

IF NOT EXISTS (SELECT 1 FROM youtable WHERE id= @id)
   BEGIN
      RAISERROR ('Record Exists', 16, 2)
   END
ELSE
   Begin
      INSERT INTO YOURTABEL(COLUM1,COLUM2) VALUES(VALUE1, VALUE2)
   END

and then by try catch you can show message to user

Upvotes: 1

Nagaraj S
Nagaraj S

Reputation: 13484

You can use DataTableReader.HasRows Property

The HasRows property returns information about the current result set

Upvotes: 0

Abbas
Abbas

Reputation: 14432

You should follow the same logic in code as the logic you state in English: if there's already a ticket show a message and if not, insert some data.

var checkQuery = "SELECT COUNT(*) FROM tblTicketTechnical where TicketID=1";
var command = new OleDbCommand(checkQuery, con);

con.Open();
int count = (int)command.ExecuteScalar();

if(count > 0)
{
    //Already exists, show message
}
else
{
    var insertQuery = "INSERT INTO tblTicketTechnical(col1, col2) VALUES('val1', val2')";
    con = new OleDbCommand(insertQuery, con);
    con.ExecuteNonQuery();
}

Please mind that this is written out of my head and not tested. Nor have I implemented exception handling. This is just to show the logic how you can perform what you want to achieve.

Upvotes: 3

NaveenBhat
NaveenBhat

Reputation: 3328

You can use HasRows property of SQLDataReader.

Upvotes: 2

Crono
Crono

Reputation: 10478

A catch block will only be executed if your code throws an exception. Here it is simply not happening.

Instead of Try/Catch use if statements and checks on your query results.

Upvotes: 1

Related Questions