473,695 Members | 3,385 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

reading values from database

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.ExecuteSca lar()
oCmd.Connection .Close()

How can I read the values from this?

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

Thank you,
Simon
Nov 21 '05 #1
7 1690
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.Connect ion = m_Connection
Command.Command Text = "proc_SomeStore dProcedure_or_s elect_text"
Command.Command Type = CommandType.Sto redProcedure

' Execute the reader

DataReader = Command.Execute Reader()

' Now iterate the result set

If DataReader.HasR ows Then

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

' Get a value...

Dim aValue, aNotherValue as integer

aValue = DataReader.GetI nt32(0)
aNotherValue = DataReader.GetI nt32(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.Clos e()

End If

End Try

Return Result

"simon" <si*********@st ud-moderna.si> wrote in message
news:uP******** ******@TK2MSFTN GP14.phx.gbl...
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.ExecuteSca lar()
oCmd.Connection .Close()
How can I read the values from this?

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

Thank you,
Simon
Nov 21 '05 #2
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.Execu teScalar, System.Int32)

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

dr = cmd.ExecuteRead er

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

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

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/de...ml/daab-rm.asp

--
W.G. Ryan, MVP

www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com
"simon" <si*********@st ud-moderna.si> wrote in message news:uP******** ******@TK2MSFTN GP14.phx.gbl...
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.ExecuteSca lar()
oCmd.Connection .Close()

How can I read the values from this?

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

Thank you,
Simon
Nov 21 '05 #3
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/de...calartopic.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
Nov 21 '05 #4
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.Exceptio n 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

--
W.G. Ryan, MVP

www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com
"Robin Tucker" <id************ *************@r eallyidont.com> wrote in
message news:cj******** **********@news .demon.co.uk...
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.Connect ion = m_Connection
Command.Command Text = "proc_SomeStore dProcedure_or_s elect_text"
Command.Command Type = CommandType.Sto redProcedure

' Execute the reader

DataReader = Command.Execute Reader()

' Now iterate the result set

If DataReader.HasR ows Then

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

' Get a value...

Dim aValue, aNotherValue as integer

aValue = DataReader.GetI nt32(0)
aNotherValue = DataReader.GetI nt32(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.Clos e()

End If

End Try

Return Result

"simon" <si*********@st ud-moderna.si> wrote in message
news:uP******** ******@TK2MSFTN GP14.phx.gbl...
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.ExecuteSca lar()
oCmd.Connection .Close()
How can I read the values from this?

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

Thank you,
Simon

Nov 21 '05 #5
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_ConnectionStr ing )

' Execute a method...

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

' and another one

Result = theDBObject.DoA notherThingBefo reClosing()

...
...

Catch Ex as Exception

Finally

If not theDBObject is Nothing Then
theDBObject.Clo se()
End If

End Try

"W.G. Ryan eMVP" <Wi*********@gm ail.com> wrote in message
news:%2******** *******@TK2MSFT NGP11.phx.gbl.. .
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.Exceptio n 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

--
W.G. Ryan, MVP

www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com
"Robin Tucker" <id************ *************@r eallyidont.com> wrote in
message news:cj******** **********@news .demon.co.uk...
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.Connect ion = m_Connection
Command.Command Text = "proc_SomeStore dProcedure_or_s elect_text"
Command.Command Type = CommandType.Sto redProcedure

' Execute the reader

DataReader = Command.Execute Reader()

' Now iterate the result set

If DataReader.HasR ows Then

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

' Get a value...

Dim aValue, aNotherValue as integer

aValue = DataReader.GetI nt32(0)
aNotherValue = DataReader.GetI nt32(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.Clos e()

End If

End Try

Return Result

"simon" <si*********@st ud-moderna.si> wrote in message
news:uP******** ******@TK2MSFTN GP14.phx.gbl...
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.ExecuteSca lar()
oCmd.Connection .Close()
How can I read the values from this?

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

Thank you,
Simon


Nov 21 '05 #6
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" <Wi*********@gm ail.com> wrote in message
news:%2******** *******@TK2MSFT NGP11.phx.gbl.. .
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.Exceptio n 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

--
W.G. Ryan, MVP

www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com
"Robin Tucker" <id************ *************@r eallyidont.com> wrote in
message news:cj******** **********@news .demon.co.uk...
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.Connect ion = m_Connection
Command.Command Text = "proc_SomeStore dProcedure_or_s elect_text"
Command.Command Type = CommandType.Sto redProcedure

' Execute the reader

DataReader = Command.Execute Reader()

' Now iterate the result set

If DataReader.HasR ows Then

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

' Get a value...

Dim aValue, aNotherValue as integer

aValue = DataReader.GetI nt32(0)
aNotherValue = DataReader.GetI nt32(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.Clos e()

End If

End Try

Return Result

"simon" <si*********@st ud-moderna.si> wrote in message
news:uP******** ******@TK2MSFTN GP14.phx.gbl...
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.ExecuteSca lar()
oCmd.Connection .Close()
How can I read the values from this?

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

Thank you,
Simon


Nov 21 '05 #7
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.

--
W.G. Ryan, MVP

www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com
"Robin Tucker" <id************ *************@r eallyidont.com> wrote in
message news:cj******** ***********@new s.demon.co.uk.. .
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_ConnectionStr ing )

' Execute a method...

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

' and another one

Result = theDBObject.DoA notherThingBefo reClosing()

...
...

Catch Ex as Exception

Finally

If not theDBObject is Nothing Then
theDBObject.Clo se()
End If

End Try

"W.G. Ryan eMVP" <Wi*********@gm ail.com> wrote in message
news:%2******** *******@TK2MSFT NGP11.phx.gbl.. .
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.Exceptio n 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

--
W.G. Ryan, MVP

www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com
"Robin Tucker" <id************ *************@r eallyidont.com> wrote in
message news:cj******** **********@news .demon.co.uk...
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.Connect ion = m_Connection
Command.Command Text = "proc_SomeStore dProcedure_or_s elect_text"
Command.Command Type = CommandType.Sto redProcedure

' Execute the reader

DataReader = Command.Execute Reader()

' Now iterate the result set

If DataReader.HasR ows Then

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

' Get a value...

Dim aValue, aNotherValue as integer

aValue = DataReader.GetI nt32(0)
aNotherValue = DataReader.GetI nt32(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.Clos e()

End If

End Try

Return Result

"simon" <si*********@st ud-moderna.si> wrote in message
news:uP******** ******@TK2MSFTN GP14.phx.gbl...
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.ExecuteSca lar()
oCmd.Connection .Close()
How can I read the values from this?

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

Thank you,
Simon



Nov 21 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
7095
by: John | last post by:
I have over 5000 thumbnail pictures of size 5kb each. I would like to able to load all 5000 pictures and view 50 per page using mysql_data_seek(). I would like to know what are the advantages and disadvantages of using a MySQL blob field rather than reading the images directly from the file? How does one insert an image into a blob field? Can it be done dynamically? Thank you John
2
3021
by: Dariusz | last post by:
Below is part of a code I have for a database. While the database table is created correctly (if it doesn't exist), and data is input correctly into the database when executed, I have a problem when reading out the data into the PHP variables / array. It should be displaying the information out in the following way, using the PHP array: n_date, n_headline, n_summarytext, n_fulltext
1
1805
by: Sunil Pathi | last post by:
Thanks in Advance I have a XML file which is read from a .aspx page. I need to read through all the elements, find the values of them and pass them on to the stored procedure which updates the sql database. Here the xml file which has to be read. <transactions> <smssent time="10:30" subsid="12" dest="07951998960"
6
18844
by: Paul | last post by:
I was wondering if anyone has had an issue where using vba code to read an excel file and import the data into an access table some records are not imported from the excel file. It seems looking at the data in the excel file that if the first character in the excel file cell is numeric it will read and write only numeric values only. If I sort the coloumn in the excel file and the first character in the cell read is alphanumeric then only...
4
7621
by: Andy | last post by:
Hello All: I have a field in the database that is an Image. I have no idea how the data is stored in here (Image, compressed, encrypted, plain text, etc). I am trying to write the contents to a text file, image file, etc so I can see if the data is stored in a way we can understand (we have been tasked to write an app and the app needs to read this field, but we don't know what it really contains). How would I go about reading the...
1
1080
by: Bijoy Naick | last post by:
I have a form with mutiple text fields, all of similar nature.. for example: userName1, location1 userName2, location2 .. .. .. userName10, location10
1
8768
by: vadarv | last post by:
Hia! I'm a total amateur to VBS but need help on a VBS script. This is used in a HMI system from Siemens called WinCC, used for process viewing and control. What I need to is to write to a table in Access, then read these values and then delete records (rows) in Access. By using help functions in WinCC I have this set up: I created an Access database with the WINCC_DATA table and columns (ID, TagValue) with the ID as the Auto Value. ...
2
2836
by: Derik | last post by:
I've got a XML file I read using a file_get_contents and turn into a simpleXML node every time index.php loads. I suspect this is causing a noticeable lag in my page-execution time. (Or the wireless where I'm working could just be ungodly slow-- which it is.) Is reading a file much more resource/processor intensive than, say, including a .php file? What about the act of creating a simpleXML object? What about the act of checking the...
6
4226
by: jcasique.torres | last post by:
Hi everyboy. I trying to create a C promang in an AIX System to read JPG files but when it read just the first 4 bytes when it found a DLE character (^P) doesn't read anymore. I using fread function. Here a few lines: char *sAnv; .... sprintf(file_a, "%s/anverso.jpg", strDir);
4
9038
by: ducttape | last post by:
Hi, I have been trying for several days to read XML files into my program. I have been following several good tutorials on the internet, but I am struggling because the particular XML files that I am reading have several nested fields: <?xml version="1.0" encoding="UTF-8" ?> <xml> <records> <record> <database name="test.enl">test</database> <contributors> <authors> <author>
0
8640
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9122
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9001
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
8832
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7672
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6498
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4348
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4587
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3018
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.