How to check db null

[andrewsmd]

I have a stored procedure that has a if statement in it. This if statement checks a count select. If it's greater than 0, it selects a set of data, if it's 0 then it selects a different set of data. The problem I have is in the vb code, I have been trying to check a value to see if it's there. If it is, then I add the value to a couple labels, otherwise I don't Here is the vb I have commented it so you can see what I mean.

Try
Dim lrd As SqlDataReader = selectCommand.ExecuteReader()
While lrd.Read()

'Response.Write(lrd("dealershipName") & " " & lrd("UserStreet") & " " & lrd("UserCity") & " " & _
' lrd("UserState") & " " & lrd("UserZip") & " " & lrd("CustName") & " " & _
' lrd("CustName") & " " & lrd("CustCompany") & " " & lrd("CustStreet") & " " & _
' lrd("CustCity") & " " & lrd("CustState") & " " & lrd("CustZip") & " " & _
' lrd("CustPhone") & " " & lrd("OrderId") & " " & lrd("DealerKey") & " " & _
' lrd("PurchName") & " " & lrd("PurchNum"))

Me.DealerNameLabel.Text = myProfile.FirstName & " " & myProfile.LastName

'I don't know what to do here
'the select statement in the sp doesn't always return
'dealership name and dealerkey I basically need to say
'if lrd(dealershipName) is returned then set those two
'labels. I tried <> "" and that throws the exception
'
'System.IndexOutOfRangeException:
'dealershipName at System.Data.ProviderBase.FieldNameLookup.GetOrdinal(String fieldName)
'at System.Data.SqlClient.SqlDataReader.GetOrdinal(String name) at
'System.Data.SqlClient.SqlDataReader.get_Item(String name) at devOrthman.invoice.loadOrderInfo()
If (lrd("dealershipName")) Then
Me.DealershipNameLabel.Text = lrd("dealershipName")
Me.DealerKeyLabel.Text = lrd("DealerKey")

End If

Me.DealershipAddressLabel.Text = lrd("UserStreet")
Me.DealershipCitySTZipLabel.Text = lrd("UserCity") & ", " & lrd("UserState") & " " & lrd("UserZip")
Me.DealerPhoneLabel.Text = myProfile.Phone
Me.CustomerNameLabel.Text = lrd("CustName")
Me.CustomerCompanyLabel.Text = lrd("CustCompany")
Me.CustomerAddressLabel.Text = lrd("CustStreet")
Me.CustomerCitySTZipLabel.Text = lrd("CustCity") & ", " & lrd("CustState") & " " & lrd("CustZip")
Me.CustomerPhoneLabel.Text = lrd("CustPhone")
Me.orderIDLabel.Text = lrd("OrderId")
Me.poName.Text = lrd("PurchName")
Me.poNum.Text = lrd("PurchNum")

End While 'while lrd.read

Catch ex As Exception
Response.Write(ex.ToString)
Finally
conn.Close()
End Try

Thanks

[Ocean10000]

' Call GetOrdinal and assign value to variable.
Dim dealershipNameOrdinal As Integer = lrd.GetOrdinal("dealershipName")

'Checks if the value is DBNULL value.
If (lrd.IsDBNull(dealershipNameOrdinal) = false) Then
Me.DealershipNameLabel.Text = lrd("dealershipName")
Me.DealerKeyLabel.Text = lrd("DealerKey")
End If

Reference
System.Data.SqlClient.IsDBNull [msdn.microsoft.com]
System.Data.SqlClient.GetOrdinal [msdn.microsoft.com]

[andrewsmd]

So I tried this

If (lrd.IsDBNull(lrd.GetOrdinal("dealershipName")) = False) Then
Me.DealershipNameLabel.Text = lrd("dealershipName")
Me.DealerKeyLabel.Text = lrd("DealerKey")
End If

And still get this error. Am I doing it right
System.IndexOutOfRangeException: dealershipName at System.Data.ProviderBase.FieldNameLookup.GetOrdinal(String fieldName) at System.Data.SqlClient.SqlDataReader.GetOrdinal(String name) at devOrthman.invoice.loadOrderInfo()

Thanks,

[Ocean10000]

What does the sql statement look like that is feeding this reader. I am thinking "dealershipName" is not in there or somehow spelled differently.

Reference:

GetOrdinal performs a case-sensitive lookup first. If it fails, a second, case-insensitive search occurs. The method throws an IndexOutOfRange exception if the zero-based column ordinal is not found.

Because ordinal-based lookups are more efficient than named lookups, it is inefficient to call GetOrdinal within a loop. Save time by calling GetOrdinal once and assigning the results to an integer variable for use within the loop.

[andrewsmd]

Here is the sp
IF (SELECT COUNT(1) FROM dbo.orders INNER JOIN dbo.Dealers ON orders.UserId = dealers.dealerId where orderid = @OrderId) > 0

SELECT R.*, S.Street as UserStreet, S.City as UserCity, T.StateName as UserState, S.ZipCode as UserZip, U.dealershipName, dealers.DealerKey, V.UserName as DealerUserName, W.StatusName as StatusOrder
FROM dbo.Orders as R
INNER JOIN dbo.Dealers ON R.UserId = dealers.dealerId
INNER JOIN dbo.Dealerships as U ON dealers.dealershipId = U.dealershipID
INNER JOIN dbo.aspnet_Users as V ON R.UserId = V.UserId
INNER JOIN dbo.Addresses as S ON U.Address = S.addressId
INNER JOIN dbo.States as T ON S.StateId = T.StateId
INNER JOIN dbo.Status as W ON R.OrderStatus = W.StatusId
WHERE R.OrderId = @OrderId
ELSE

select R.*, S.Street as UserStreet, S.City as UserCity, T.StateName as UserState,
S.ZipCode as UserZip, V.UserName as DealerUserName, W.StatusName as StatusOrder
from orders as R
INNER JOIN dbo.aspnet_Users as V ON R.UserId = V.UserId
INNER JOIN dbo.Addresses as S ON V.UserId = S.UserId
INNER JOIN dbo.States as T ON S.StateId = T.StateId
INNER JOIN dbo.Status as W ON R.OrderStatus = W.StatusId
WHERE R.OrderId = @OrderId

RETURN

I am having issues with the else part. Whenever that is returned dealerkey and dealershipname are not selected because they don't exist. Thanks,

[Ocean10000]

' Call GetOrdinal and assign value to variable.
Dim dealershipNameOrdinal As Integer = lrd.GetOrdinal("dealershipName")

'Makes sure there is a column with the name we are looking for.
If (dealershipNameOrdinal >-1) Then
'Checks if the value is DBNULL value.
If (lrd.IsDBNull(dealershipNameOrdinal) = false) Then
Me.DealershipNameLabel.Text = lrd("dealershipName")
Me.DealerKeyLabel.Text = lrd("DealerKey")
End If
End If

[andrewsmd]

I tried your code as is (dealershipName is correct) and it still gave me the same error. In the order of getting this working I ended up editing the second select statement to this
select R.*, S.Street as UserStreet, S.City as UserCity, T.StateName as UserState,
S.ZipCode as UserZip, V.UserName as DealerUserName, W.StatusName as StatusOrder, dealershipName = '',
dealerKey = ''

More of a band aid than a fix, but I'm out of options. If someone has a true fix for this, I'm still open for other suggestions. Thanks,

[carguy84]

In your SQL Else statement, you're not returning "dealershipName" or "DealerKey" with your SELECT statement.