What to do with a Null Value that is returned from a Select. ??

  • Thread starter Thread starter Harry
  • Start date Start date
H

Harry

Hi,

Can anyone help with this please?

I have the below code, which I want tp check if an email address
exsists in my SQL database. It works fine if the email address is
found, but if the email address does not exsist, it chucks out the
following error:-
"System.NullReferenceException: Object reference not set to an
instance of an object"

No I understand this is because Select query is coming back with a
'null' value, but I dont seem to be able catch it properly.
(Ive read lots about DBNull.Value, but i dont seem to be able to use
it without getting errors!)

Any help apriciated. (Also, is the below method an 'efficient' way of
carrying out this type of function?)

Many Thanks in advance
H


void Send_Email(object sender, System.EventArgs e)
{
SqlConnection myConnection = new SqlConnection
System.Configuration.ConfigurationSettings.AppSettings["pdSQL"]);
myConnection.Open();
//Run Query to compare the Email Address
SqlCommand myCommand1 = new SqlCommand("SELECT * FROM dbo.Email_List
WHERE Email = '" + email.Text + "'" , myConnection);
int userCount = (int)myCommand1.ExecuteScalar();

if (userCount = 1 )
{
Response.Write("Email Matched" );
}
else {
Response.Write("Email NOT Matched");
}

myConnection.Close();
}



HTML
The HTML is simple and just contains a text box.
<asp:textbox CssClass="ENEWStextBox" ID="email" runat="server"
TextMode="SingleLine" />
 
SELECT * FROM dbo.Email_List WHERE Email='(e-mail address removed)'

UserID Email
------- -------
1099 (e-mail address removed)
2928 (e-mail address removed)

With your logic, it would return 1099 or NULL. This is not a DBNull issue,
but a misunderstanding of how ExecuteScalar works. If you are simply looking
for a number try this for your SQL:

SqlCommand myCommand1 = new SqlCommand("SELECT COUNT(*) FROM dbo.Email_List
WHERE Email = '" + email.Text + "'" , myConnection);
int userCount = (int)myCommand1.ExecuteScalar();

Execute Scalar returns all from the table and then only pulls the first
value.

It will then return 0 for no records or N (where N is the number of records)
when records are found.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

**********************************************************************
Think Outside the Box!
**********************************************************************
 
Harry:

First, do yourself a favor and get rid of the dynamic SQL. Trust me on
this. (It's not the cause of your problem, but if you have a web enabled
DataBase, you are Begging for trouble if you do this. I just attended
DevDays in Atlanta on ASP.NET Security, and there are plenty of other good
reasons not to use Dynamic SQL, but they beat this point into the ground-
and use Stored Procs if at all possible)

SqlCommand myCommand1 = new SqlCommand("SELECT * FROM dbo.Email_List
WHERE Email = @Email , myConnection);

myCommand1.Parameters.Add["@Email", SqlDbType.VarChar).Value = email.txt;

Now, in your other block where you call executeScalar:
try{
int userCount = (int)myCommand1.ExecuteScalar();
Response.Write("Email Matched" );
}
catch(NullReferenceException ex){
Response.Write("Email NOT Matched");
}

A even better way is to use Count(*) so you now you'll get an int value,
then you can do away with the exceptions which are costly when you get a
null value.

The best way is to use an Output parameter and check for it. Fast,
efficient, effective...let me know if you want me to walk you through that.

HTH,

Bill
 
Cowboy - Thanks for the code. That worked great. (Oh so simple when you
know how!!)

Bill - Thanks for your info as well.
I will read up about the not using Dynamic SQL. I am just starting to
get my head round the security side things, but for now I will use your
example.

I am now using COUNT. I should of really thought of this from the start,
but for some reason I was thinking along the c# side of things rather
than amending the SQL statment.

I am intrested in your suggestion for using an "Output parameter" so any
info you can provide would be appriciated.
(Any help is appriciated, as I travel along this long learning road that
is .net!! )


Thanks Guys.
H
 
Cowboy - Thanks for the code. That worked great. (Oh so simple when you
know how!!)

Bill - Thanks for your info as well.
I will read up about the not using Dynamic SQL. I am just starting to
get my head round the security side things, but for now I will use your
example.

I am now using COUNT. I should of really thought of this from the start,
but for some reason I was thinking along the c# side of things rather
than amending the SQL statment.

I am intrested in your suggestion for using an "Output parameter" so any
info you can provide would be appriciated.
(Any help is appriciated, as I travel along this long learning road that
is .net!! )


Thanks Guys.
H
Click to expand...

In my opinion you would be better off sticking with the Count(*)
method unless you need to examine the return value.

However if the email addresses are indexed, selecting on the email
address may be more efficient. I think the concern for efficiency may
be moot unless you are expecting to have many thousands of email
addresses in the table.

Otis Mukinfus
http://www.otismukinfus.com
 
Back
Top