Determine if record exists

[Guest]

What would be a more efficient way for me to determine if a record in an SQL
DB table exists? Right now, I have a try/catch like this:

try
{
if(checkcom.ExecuteScalar().ToString()==tbBillNumber.Text)
{
....
}
}
catch
{
...
}

Is there any way I can do this without using a try/catch?
All I need to do is determine if a record exists or not.
Thanks

 

[Mark Fitzpatrick]

You could have a query that returns a field that only has the count of the
number of matching rows such as:

SELECT count(*) as TotalCount FROM myTable WHERE id = @someid

How you get to the count depends on how you like to do things. You could use
the return parameter or an output parameter in a stored procedure to return
the count after you set it to a local variable than just grab the parameter.

Hope this helps,
Mark Fitzpatrick
Microsoft MVP - FrontPage

 

[Guest]

Thanks for the quick reply.
I'm relatively unskilled with SQL, though. What, exactly, is that statement
doing?
I know the count function, but what is the @someid thing at the end?
Right now, my query looks like this:

checkcom.CommandText = "SELECT * FROM table WHERE bill_Number = '" +
tbBillNumber.Text+"'";

I'm trying to determing if the record exists, and then go on to use the data
in that record, if it exists. Am I just going to have to use two queries?

 

[Ryan Trudelle-Schwarz]

Thanks for the quick reply.

I'm relatively unskilled with SQL, though. What, exactly, is that
statement
doing?
I know the count function, but what is the @someid thing at the end?
Right now, my query looks like this:
checkcom.CommandText = "SELECT * FROM table WHERE bill_Number = '" +
tbBillNumber.Text+"'";

I'm trying to determing if the record exists, and then go on to use
the data in that record, if it exists. Am I just going to have to use
two queries?

You'll probably want to do something like so:

Dim dataReader as SqlDataReader
dataReader = connection.ExecuteReader(...)

if dataReader.Read() then
' Data was found, use it.
end if

dataReader.close()

 

[Guest]

That's exactly what I needed (its C# equivalent, anyway). Thanks

 

[Guest]

That's exactly what I needed (its C# equivalent, anyway). Thanks

 

[Joe Fallon]

You need to validate the contents of tbBillNumber.Text *before* using it in
your query.
If it is a valid value then go ahead and use it.

But what if an evil user decides to inject some SQL into that exposed
textbox?
They could end up doing serious damage to your DB.
They could also end up taking over the server and getting in to your
network.

Look up SQL Injection attacks and take steps to validate all user input.

By using a parameterized query (the one with the @ symbol in it earlier) you
can help avoid SQL Injection attacks.