469,645 Members | 1,595 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,645 developers. It's quick & easy.

Determine if record exists

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()==tbBillNumb er.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
Nov 19 '05 #1
6 1820
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

"Opie" <Op**@discussions.microsoft.com> wrote in message
news:CA**********************************@microsof t.com...
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()==tbBillNumb er.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

Nov 19 '05 #2
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?

"Mark Fitzpatrick" wrote:
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


Nov 19 '05 #3
> 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()
Nov 19 '05 #4
That's exactly what I needed (its C# equivalent, anyway). Thanks :)

"Ryan Trudelle-Schwarz" wrote:

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()


Nov 19 '05 #5
That's exactly what I needed (its C# equivalent, anyway). Thanks :)

"Ryan Trudelle-Schwarz" wrote:
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()

Nov 19 '05 #6
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.
--
Joe Fallon


"Opie" <Op**@discussions.microsoft.com> wrote in message
news:34**********************************@microsof t.com...
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?

"Mark Fitzpatrick" wrote:
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

Nov 19 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

9 posts views Thread by Mark | last post: by
20 posts views Thread by Bryan | last post: by
7 posts views Thread by Stephen E. Weber | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.