ASP.Net VB Database Question - Simple but frustrating

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to get the value from a simple stored procedure - the Stored
Procedure returns 1 record with one field - it is basically "Select Max(po)
as MaxPo from PODB"

I want to store that value into a variable - in old asp i could simply say
MaxPO=rs("MaxPo")
and i would be done. Now i have tried using Datareaders and dataadapters
w/o any success. all the examples i am finding are for datagrids and
dropdown boxes. All I want to do is grab this one value? what is the easiest
way??
 
Hey,

Take a look at the SqlCommand.ExecuteScalar() method (or that of
OleDbCommand if that's what you're using). You should be able to do
something like...

SqlCommand MyCmd = MyConn.CreateCommand();
MyCmd.CommandType = CommandType.Text;
MyCmd.CommandText = "Select Max(po) as MaxPo from PODB";
int MaxPO = MyCmd.ExecuteScalar();

Honestly, I may be "paraphrasing" slightly with the code, but that should be
pretty close..

HTH,

John
 
Thank You - That is exactly what i was looking for - is there any way to
check that value to make sure it doesnt return a null value - otherwise it
errors out when it is null

Thanx!
 
For anyone interested, I fixed my problem with a little error trapping i
found. Here is the example:

Sql = "execute spGetMaxPo " & ddOrderedBy.SelectedValue

Dim myConnection As SqlConnection = New
SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))

Dim myCommand As SqlCommand = New SqlCommand(Sql, myConnection)


myCommand.CommandType = CommandType.Text

Try
myConnection.Open()
Dim count As String = myCommand.ExecuteScalar()
Dim strID As String = CStr(count)
txtExtra.Text = strID
Catch ex As Exception
Response.Write(ex.Message)
Response.Write("We will set the txtExtra to equal 1")
txtExtra.Text = 1
'''Response.End()
Finally
If myConnection.State = ConnectionState.Open Then

myConnection.Close()
End If
End Try
 
Hey again,

In C#, I think you'd just check against null, e.g.:
if (Count==null)...

In VB, I think null handling goes something more like:
If Not IsDbNull Count Then

I'm not completely certain, but maybe look a bit more closely at IsDbNull
and DbNull.

Also, it looks like you've got a viable solution. You'll get different
schools of thought, but generally error-trapping is less efficient than
testing for a likely error. Rather than using a try...catch to handle
errors, you may want to consider checking explicitly for a null rather than
trapping for the exception.

HTH,

John
 
Thank You for your input - the error trapping seems to do the trick. I wasnt
able to get the synatx right for checking for null - i guess i could put it
in the stored procedure and it wouldnt be an issue. Thanx for your help!
 
Back
Top