Selecting a row from SQL table

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
RySk8er30Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeff CertainCommented:
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
RySk8er30Author Commented:
I am using a SQL database.  Can I use a sqlReader?
0
Jeff CertainCommented:
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
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

RySk8er30Author Commented:
Could you please explain how to do this?
0
Jeff CertainCommented:
' 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bob LearnedCommented:
(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
RySk8er30Author Commented:
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
Bob LearnedCommented:
salesmanName = myReader("SalesmanName")
divisionId=myReader("DivisionID")
managerId=myReader("ManagerID")

Bob
0
Jeff CertainCommented:
Implicit casts... thanks Bob. That's what I get for stealing MicroSlop's sample code...
0
RySk8er30Author Commented:
I did this...

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

And it worked.  Is there a better way?

Ryan
0
Bob LearnedCommented:
I hate positional code, that uses ordinal indices.

Bob
0
RySk8er30Author Commented:
Thanks guys.
0
Jeff CertainCommented:
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
Jeff CertainCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.