?
Solved

Selecting a row from SQL table

Posted on 2004-12-01
14
Medium Priority
?
208 Views
Last Modified: 2010-04-23
Hi,

I have a sql command which I want to run.  It would look similar to this...

"SELECT SalesmanName, DivisionID, ManagerID FROM Budget_Salesman WHERE SalesmanID ='" & Session("UserID") & "'"

This query will only return one row (which will have the three values).  Is there a way I can create an object array and put the data there?

Ryan
0
Comment
Question by:RySk8er30
  • 6
  • 5
  • 3
14 Comments
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 12720225
Well... your choices are to return a table or a scalar from any SQL string... but not three variables. You should just throw the data into a datatable and then retrieve the data from it...

Are you using SQL Server or an OLEDB (i.e. any other) database? Let me know, and I'll post the code to populate a datatable from the database.
0
 

Author Comment

by:RySk8er30
ID: 12720272
I am using a SQL database.  Can I use a sqlReader?
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 12720296
That's one way to go about it... probably the best way if you only need one record. Should I take that to mean that you're familiar with the datareader?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:RySk8er30
ID: 12720421
Could you please explain how to do this?
0
 
LVL 24

Accepted Solution

by:
Jeff Certain earned 100 total points
ID: 12720516
' Global variable declaration
private salesmanName as string
private managerId as integer
private divisionId as integer

Public Sub ReadMyData(myConnString As String)
    Dim mySelectQuery As String = "SELECT SalesmanName, DivisionID, ManagerID FROM Budget_Salesman WHERE SalesmanID ='" & Session("UserID") & "'"
    Dim myConnection As New SqlConnection(myConnString)
    Dim myCommand As New SqlCommand(mySelectQuery, myConnection)
    myConnection.Open()
    Dim myReader As SqlDataReader
    myReader = myCommand.ExecuteReader()
    ' Always call Read before accessing data.
    While myReader.Read()
' store your 3 items in the appropriate variables using myReader.GetInt32(i), myReader.GetString(i), etc; "i" is the position is the position of the data
salesmanName = myReader.GetString(0)
divisionId=myReader.GetInt32(1)
managerId=myReader.GetInt32(2)
    End While
    ' always call Close when done reading.
    myReader.Close()
    ' Close the connection when done with it.
    myConnection.Close()
End Sub 'ReadMyData
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 12720520
(1) Create SQLConnection with connection string.
(2) Create SQLCommand with query string and connection.
(3) Execute SQLCommand.ExecuteReader and store in an SQLReader object.

Bob
0
 

Author Comment

by:RySk8er30
ID: 12720637
Chaosian -

Everything is working except divisionID=myReader.GetInt32(1)

I get a message - Specified cast is invalid.  The type in the sql table is "numeric".

Ryan
0
 
LVL 96

Assisted Solution

by:Bob Learned
Bob Learned earned 100 total points
ID: 12720666
salesmanName = myReader("SalesmanName")
divisionId=myReader("DivisionID")
managerId=myReader("ManagerID")

Bob
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 12720681
Implicit casts... thanks Bob. That's what I get for stealing MicroSlop's sample code...
0
 

Author Comment

by:RySk8er30
ID: 12720688
I did this...

Session("DivisionID") = salesmanReader.GetValue(1).GetType.ToString

And it worked.  Is there a better way?

Ryan
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 12720697
I hate positional code, that uses ordinal indices.

Bob
0
 

Author Comment

by:RySk8er30
ID: 12720705
Thanks guys.
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 12720712
Session("DivisionID") = integer.parse(salesmanReader.GetValue(1)) would be better... what you're storing in the session variable is the string representation of the data type... i.e. "System.Integer"
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 12720719
Bob... yeah, I know. Confusing and hard to read... but slightly more efficient (if you care about such things) since it saves the lookup to get the ordinal based on the field name...
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question