reading values from database

[simon]

What is the best way to read the values from the datatbase.

I have sql="SELECT userName, userCountry, userVisit from users where userID=2"

Create a command object?

Dim oCmd As SqlCommand
oCmd = New SqlCommand(sql, Connection)
object = oCmd.ExecuteScalar()
oCmd.Connection.Close()

How can I read the values from this?

userName=object.fields(0).Value ' or something like that
userCountry=...
userVisit=....

Thank you,
Simon

 

[Robin Tucker]

I do something like this. Modify CommandType and CommandText for your query
(I use stored procedures here).

' Our data reader

Dim DataReader As SqlDataReader

' Result

Dim Result As Boolean = False

' Do sommit

Try

Dim Command As New SqlCommand

' Setup the command object

Command.Connection = m_Connection
Command.CommandText = "proc_SomeStoredProcedure_or_select_text"
Command.CommandType = CommandType.StoredProcedure

' Execute the reader

DataReader = Command.ExecuteReader()

' Now iterate the result set

If DataReader.HasRows Then

While DataReader.Read() ' ie. for each record returned

' Get a value...

Dim aValue, aNotherValue as integer

aValue = DataReader.GetInt32(0)
aNotherValue = DataReader.GetInt32(1)

End While

End If

' Success

Result = True

Catch Ex As Exception

' Failed

Result = False

Finally

' Close reader

If Not DataReader Is Nothing Then

DataReader.Close()

End If

End Try

Return Result

What is the best way to read the values from the datatbase.

I have sql="SELECT userName, userCountry, userVisit from users where
userID=2"

Create a command object?

Dim oCmd As SqlCommand
oCmd = New SqlCommand(sql, Connection)
object = oCmd.ExecuteScalar()
oCmd.Connection.Close()
How can I read the values from this?

userName=object.fields(0).Value ' or something like that
userCountry=...
userVisit=....

Thank you,
Simon

 

[W.G. Ryan eMVP]

If you need multiple values, then ExecuteScalar isn't the way to go. First off though, I'd cast the return value instead of using object - so if you were expecting an Integer value, I'd use

Dim returnValue as System.Int32= CType(cmd.ExecuteScalar, System.Int32)

For this one though you can use a DataReader ie Dim dr as SqlDataReader

dr = cmd.ExecuteReader

'Assuming you know that you will only have one row back you can use the SingleResult enumeration http://msdn.microsoft.com/library/d.../frlrfsystemdatacommandbehaviorclasstopic.asp

If dr.Read Then
Dim firstValue as System.String = dr.GetString(0) 'username
Dim secondValue as System.Int32 = dr.String(1)'userCountry

etc ( you can also use GetValue which is a little cleaner. ALso, make sure to check for DBNull values or you'll get a null reference exception.

End If


Two other things: 1) ALWAYS use a Try /Catch Finally on when using SqlConnection. Angel Saenz-Badillos describes why this is so critical here http://weblogs.asp.net/angelsb/
2) Don't use concatenated SQL Like That - use Parameterized queries instead http://www.knowdotnet.com/articles/dynamisql.html
http://www.knowdotnet.com/articles/storedprocsvb.html

It works the same whether you are using Stored Procedures or not so use them instead - Microsoft's Data Access Application Block is also a wonderful resource for stuff like this http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/daab-rm.asp

--
W.G. Ryan, MVP

www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com
What is the best way to read the values from the datatbase.

I have sql="SELECT userName, userCountry, userVisit from users where userID=2"

Create a command object?

Dim oCmd As SqlCommand
oCmd = New SqlCommand(sql, Connection)
object = oCmd.ExecuteScalar()
oCmd.Connection.Close()

How can I read the values from this?

userName=object.fields(0).Value ' or something like that
userCountry=...
userVisit=....

Thank you,
Simon

 

[Cor Ligthert]

Simon,

There are not much "best" ways in VBNet and certainly not for such a wide question as yours.

It depends why you want to read something, do you by instance want to read only one value, than the executescalar is a way to go however not with the select as you use.

http://msdn.microsoft.com/library/d...aoledboledbcommandclassexecutescalartopic.asp

I think a good thing to do for you is reading some books, mostly advices in the dotNet newsgroups are the books for Adonet from Davis Sceppa or William(Bill) Vaughn

And when you tell us something more what you want to archive, than maybe we can help you in the right direction.

I hope this helps a anyway?

Cor

 

[W.G. Ryan eMVP]

Robin:

Just to add a quick tidbit - closing the Connection is ostensibly one of the
more important issues you need to address. You can specify CloseCOnnection
in the CommandBehavior Enumeration of ExecuteReader which will handle this
for you whenever you close the reader but it's a must unless you want to
risk some serious performance hits. I'd also avoid trapping
System.Exception here. A StackOVerflow isn't the same things as a
Connection Timeout so I'd trap Connection.Open() individually as well as
executeReader and wrap them in SqlException, OleDbException etc

 

[Robin Tucker]

Ahh yes. My connection gets closed a little later, ie. like this (not made
clear in my original post)

Dim theObject as MyDataBaseClass()

Try

' Create it

theDBObject = new MyDataBaseClass ( s_ConnectionString )

' Execute a method...

Result = theDBObject.FetchMyImagesFromDataBase()
If Not Result Then
Throw New Exception ( "Something screwed up" )
End If

' and another one

Result = theDBObject.DoAnotherThingBeforeClosing()

...
...

Catch Ex as Exception

Finally

If not theDBObject is Nothing Then
theDBObject.Close()
End If

End Try

 

[Robin Tucker]

Hmmmm. You have a point about trapping Timeout. This is important in
applications (unlike mine) where you could indeed get a long response time
from the server. In my application (which is single user), I assume a
timeout means that SQL server is broken and so treat it as a "failure" like
every other exception.

 

[W.G. Ryan eMVP]

I figured as much. Forgetting to close connections or have them in a using
block or finally statement is the source of a lot of drama so I figured I'd
mention it.