ASP.Net VB Database Question - Simple but frustrating

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

PCL - 28 Oct 2004 15:29 GMT

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??

Reply to this Message

John Spiegel - 28 Oct 2004 16:31 GMT

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

> 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)
[quoted text clipped - 6 lines]
> dropdown boxes. All I want to do is grab this one value? what is the easiest
> way??

Reply to this Message

PCL - 28 Oct 2004 17:23 GMT

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!

> Hey,
>
[quoted text clipped - 26 lines]
> easiest
> > way??

Reply to this Message

PCL - 28 Oct 2004 18:29 GMT

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

> 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
[quoted text clipped - 32 lines]
> > easiest
> > > way??

Reply to this Message

John Spiegel - 28 Oct 2004 18:57 GMT

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

> For anyone interested, I fixed my problem with a little error trapping i
> found. Here is the example:
[quoted text clipped - 61 lines]
> > > easiest
> > > > way??

Reply to this Message

PCL - 28 Oct 2004 19:23 GMT

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!

> Hey again,
>
[quoted text clipped - 89 lines]
> > > > easiest
> > > > > way??

Reply to this Message

ASP.Net VB Database Question - Simple but frustrating

Free Magazines