SQL result assigned to a variable?

[Tony K]

How would I assign the result of a SQL query to a variable. The result of
the following statement will ALWAYS result in 1 row returned.

SELECT ProductDescription, ProductID
FROM Products
WHERE (ProductIDNumber = ?)

How do I assign the...say, ProductDescription to a variable called prodDesc?

Thanks,
Tony

[Cor Ligthert [MVP]]

tony,

I am not sure of what your question is,

Do you want the first row of this exression than add Top1
If you want to make one row of this expression than add Distinct

If you only want one row to return than use the datareader,

Cor
"Tony K" <ki********@NOSPAMcomcast.netschreef in bericht
news:Ok**************@TK2MSFTNGP03.phx.gbl...

How would I assign the result of a SQL query to a variable. The result of
the following statement will ALWAYS result in 1 row returned.

SELECT ProductDescription, ProductID
FROM Products
WHERE (ProductIDNumber = ?)

How do I assign the...say, ProductDescription to a variable called
prodDesc?

Thanks,
Tony

There are several ways of doing this. I am assuming that you mean assign the
value of say the ProductDescription column from the SQL query to a variable
in your code that calls the query, right? If you´re only after one return
value, such as ProductDescription, you can use the ExecuteScalar method of
the Command class. If you´re after the single row as is currently returned,
but want to store one or more of the returned values in your code, then look
at using parameterized queries with output parameters.

Here are some samples, http://support.microsoft.com/kb/308049/

--
Carsten Thomsen
Senior .NET Solutions Architect / Developer / Author
MCAD/MCSD/MCSE/MCTS
"Tony K" <ki********@NOSPAMcomcast.netwrote in message
news:Ok**************@TK2MSFTNGP03.phx.gbl...

How would I assign the result of a SQL query to a variable. The result of
the following statement will ALWAYS result in 1 row returned.

SELECT ProductDescription, ProductID
FROM Products
WHERE (ProductIDNumber = ?)

How do I assign the...say, ProductDescription to a variable called
prodDesc?

Thanks,
Tony

[Tony K]

Cor,
The query is not bound to say... a text box. I want the result to be
assigned to a variable so I can use it in another place in my program. The
query will always return only 1 row because the ProductIDNumber is set to
unique in the database.

How do I get the value for ProductDescription to be assigned to the variable
strProdDesc or assign the ProductID to intProdID?

Thanks,
Tony

"Cor Ligthert [MVP]" <no************@planet.nlwrote in message
news:eD*************@TK2MSFTNGP06.phx.gbl...

tony,

I am not sure of what your question is,

Do you want the first row of this exression than add Top1
If you want to make one row of this expression than add Distinct

If you only want one row to return than use the datareader,

Cor
"Tony K" <ki********@NOSPAMcomcast.netschreef in bericht
news:Ok**************@TK2MSFTNGP03.phx.gbl...

>How would I assign the result of a SQL query to a variable. The result
of the following statement will ALWAYS result in 1 row returned.

SELECT ProductDescription, ProductID
FROM Products
WHERE (ProductIDNumber = ?)

How do I assign the...say, ProductDescription to a variable called
prodDesc?

Thanks,
Tony

[Cor Ligthert [MVP]]

Tony,

If it is more than one field, then you only have the choise between a
datareader or a datatable.

It is just personal preference which you take, if you have to update them
latter however, than probably the datatable will fit the best.

Cor

"Tony K" <ki********@NOSPAMcomcast.netschreef in bericht
news:ej**************@TK2MSFTNGP02.phx.gbl...

Cor,
The query is not bound to say... a text box. I want the result to be
assigned to a variable so I can use it in another place in my program.
The query will always return only 1 row because the ProductIDNumber is set
to unique in the database.

How do I get the value for ProductDescription to be assigned to the
variable strProdDesc or assign the ProductID to intProdID?

Thanks,
Tony

"Cor Ligthert [MVP]" <no************@planet.nlwrote in message
news:eD*************@TK2MSFTNGP06.phx.gbl...

>tony,

I am not sure of what your question is,

Do you want the first row of this exression than add Top1
If you want to make one row of this expression than add Distinct

If you only want one row to return than use the datareader,

Cor
"Tony K" <ki********@NOSPAMcomcast.netschreef in bericht
news:Ok**************@TK2MSFTNGP03.phx.gbl...

>>How would I assign the result of a SQL query to a variable. The result
of the following statement will ALWAYS result in 1 row returned.

SELECT ProductDescription, ProductID
FROM Products
WHERE (ProductIDNumber = ?)

How do I assign the...say, ProductDescription to a variable called
prodDesc?

Thanks,
Tony

[=?ISO-8859-1?Q?G=F6ran_Andersson?=]

Cor Ligthert [MVP] wrote:

Tony,

If it is more than one field, then you only have the choise between a
datareader or a datatable.

Not only. Output parameters can also be used.

It is just personal preference which you take, if you have to update them
latter however, than probably the datatable will fit the best.

Cor

"Tony K" <ki********@NOSPAMcomcast.netschreef in bericht
news:ej**************@TK2MSFTNGP02.phx.gbl...

>Cor,
The query is not bound to say... a text box. I want the result to be
assigned to a variable so I can use it in another place in my program.
The query will always return only 1 row because the ProductIDNumber is set
to unique in the database.

How do I get the value for ProductDescription to be assigned to the
variable strProdDesc or assign the ProductID to intProdID?

Thanks,
Tony

"Cor Ligthert [MVP]" <no************@planet.nlwrote in message
news:eD*************@TK2MSFTNGP06.phx.gbl...

>>tony,

I am not sure of what your question is,

Do you want the first row of this exression than add Top1
If you want to make one row of this expression than add Distinct

If you only want one row to return than use the datareader,

Cor
"Tony K" <ki********@NOSPAMcomcast.netschreef in bericht
news:Ok**************@TK2MSFTNGP03.phx.gbl...
How would I assign the result of a SQL query to a variable. The result
of the following statement will ALWAYS result in 1 row returned.

SELECT ProductDescription, ProductID
FROM Products
WHERE (ProductIDNumber = ?)

How do I assign the...say, ProductDescription to a variable called
prodDesc?

Thanks,
Tony

--
Göran Andersson
_____
http://www.guffa.com

[Tony K]

I have solved my problem with a deeply buried VB 2003 database book
(Database Programming with Visual Basic .NET by Carsten Thomsen) and Cor's
suggestion of using a datareader or datatable. Now...I wouldn't have known
what to look for if it wasn't for everyone that has responded to my initial
post so thanks to all. I created a new project for testing and came up with
this.

Absolutely NOTHING on the form...

THIS IS USING THE DATAREADER

Imports System.Data.OleDb
Public Class Form1

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
Dim cnnInvMan As OleDbConnection
Dim cmmInvMan As OleDbCommand
Dim strSQL As String
Dim drdTest As OleDbDataReader
Dim field1 As String

cnnInvMan = New
OleDbConnection(My.Settings.DataReaderConnectionSt ring) 'I set this up
using the wizard by adding a new DataSource. (Access DB)

cnnInvMan.Open()
strSQL = "SELECT * FROM EMPLOYEES"
cmmInvMan = New OleDbCommand(strSQL, cnnInvMan)
drdTest = cmmInvMan.ExecuteReader
Do While drdTest.Read
field1 = drdTest.Item("FirstName") 'test my problem by
assigning it to a variable before using it in an output...
MessageBox.Show(field1) 'this shows the first name of each
employee in the Access database.
Loop
End Sub
End Class

THIS IS USING THE EXECUTESCALAR

Imports System.Data.OleDb
Public Class Form1

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
Dim cnnInvMan As OleDbConnection
Dim cmmInvMan As OleDbCommand
Dim strSQL As String
Dim field1 As String

cnnInvMan = New
OleDbConnection(My.Settings.DataReaderConnectionSt ring)

cnnInvMan.Open()
strSQL = "SELECT FirstName FROM EMPLOYEES"
cmmInvMan = New OleDbCommand(strSQL, cnnInvMan)
field1 = cmmInvMan.ExecuteScalar().ToString
MessageBox.Show(field1)
End Sub
End Class

Thanks again to everyone!!

Tony K

"Göran Andersson" <gu***@guffa.comwrote in message
news:ey*************@TK2MSFTNGP06.phx.gbl...

Cor Ligthert [MVP] wrote:

>Tony,

If it is more than one field, then you only have the choise between a
datareader or a datatable.

Not only. Output parameters can also be used.

>It is just personal preference which you take, if you have to update them
latter however, than probably the datatable will fit the best.

Cor

"Tony K" <ki********@NOSPAMcomcast.netschreef in bericht
news:ej**************@TK2MSFTNGP02.phx.gbl...

>>Cor,
The query is not bound to say... a text box. I want the result to be
assigned to a variable so I can use it in another place in my program.
The query will always return only 1 row because the ProductIDNumber is
set to unique in the database.

How do I get the value for ProductDescription to be assigned to the
variable strProdDesc or assign the ProductID to intProdID?

Thanks,
Tony

"Cor Ligthert [MVP]" <no************@planet.nlwrote in message
news:eD*************@TK2MSFTNGP06.phx.gbl...
tony,

I am not sure of what your question is,

Do you want the first row of this exression than add Top1
If you want to make one row of this expression than add Distinct

If you only want one row to return than use the datareader,

Cor
"Tony K" <ki********@NOSPAMcomcast.netschreef in bericht
news:Ok**************@TK2MSFTNGP03.phx.gbl...
How would I assign the result of a SQL query to a variable. The
result of the following statement will ALWAYS result in 1 row
returned.
>
SELECT ProductDescription, ProductID
FROM Products
WHERE (ProductIDNumber = ?)
>
How do I assign the...say, ProductDescription to a variable called
prodDesc?
>
Thanks,
Tony

--
Göran Andersson
_____
http://www.guffa.com

[RobinS]

If you only want one value back, check the ExecuteScalar method of the
Command object. Here's an example, where this is the query:

Dim selectCmd as SqlCommand = new SqlCommand("Select customerID
From Customers where customerID = 'ALFKI'", conn)

And this is how to execute it using a command object called cmd:

Dim custID as String = DirectCast(cmd.ExecuteScalar, String)
If custID = Nothing Then
Throw New ApplicationException("Customer Not Found.")
Else
'do something
End If

If you want to return move than one value, you can use output parameters.
Here's an example that runs against Northwind.
Dim ss As String = "SELECT @UnitPrice = UnitPrice, " & _
" @UnitsInStock = UnitsInStock " & _
"FROM Products WHERE ProductName = @ProductName"

Dim cn As New SqlConnection(My.Settings.NorthwindConnectionStrin g)
cn.Open()
Dim cmd As New SqlCommand(ss, cn)

Dim pUnitPrice, pInStock, pProductName As SqlParameter
pUnitPrice = cmd.Parameters.Add("@UnitPrice", SqlDbType.Money)
pUnitPrice.Direction = ParameterDirection.Output
pInStock = cmd.Parameters.Add("@UnitsInStock", _
SqlDbType.NVarChar, 20)
pInStock.Direction = ParameterDirection.Output

pProductName = cmd.Parameters.Add("ProductName", _
SqlDbType.NVarChar, 40)
pProductName.Value = "Chai"

cmd.ExecuteNonQuery()

If pUnitPrice.Value Is DBNull.Value Then 'none were found
Console.WriteLine("No product found named {0}", _
pProductName.Value)
Else
Console.WriteLine("Unit price: {0}", pUnitPrice.Value)
Console.WriteLine("In Stock: {0}", pInStock.Value)
End If

cn.Close()
Good luck.
Robin S.
----------------------------------------------
"Tony K" <ki********@NOSPAMcomcast.netwrote in message
news:Ok**************@TK2MSFTNGP03.phx.gbl...

How would I assign the result of a SQL query to a variable. The result
of the following statement will ALWAYS result in 1 row returned.

SELECT ProductDescription, ProductID
FROM Products
WHERE (ProductIDNumber = ?)

How do I assign the...say, ProductDescription to a variable called
prodDesc?

Thanks,
Tony

[Tom Leylan]

"Tony K" <ki********@NOSPAMcomcast.netwrote...

THIS IS USING THE EXECUTESCALAR

cnnInvMan.Open()
strSQL = "SELECT FirstName FROM EMPLOYEES"
cmmInvMan = New OleDbCommand(strSQL, cnnInvMan)
field1 = cmmInvMan.ExecuteScalar().ToString
MessageBox.Show(field1)

Do note that you are using ExecuteScalar but you have not limited the return
set to a single item. You've gotten back "a firstname" but it doesn't have
to be the same one each time because you haven't identified it with an ID
(or some other primary key).

[RobinS]

"Tom Leylan" <tl*****@nospam.netwrote in message
news:%2***************@TK2MSFTNGP04.phx.gbl...

"Tony K" <ki********@NOSPAMcomcast.netwrote...

>THIS IS USING THE EXECUTESCALAR

> cnnInvMan.Open()
strSQL = "SELECT FirstName FROM EMPLOYEES"
cmmInvMan = New OleDbCommand(strSQL, cnnInvMan)
field1 = cmmInvMan.ExecuteScalar().ToString
MessageBox.Show(field1)

Do note that you are using ExecuteScalar but you have not limited the
return set to a single item. You've gotten back "a firstname" but it
doesn't have to be the same one each time because you haven't identified
it with an ID (or some other primary key).

Good point Tom. The sql string should be something more like

strSQL = "SELET FirstName FROM Employees WHERE EmployeeID = @EmployeeID"

Robin S.

[sloan]

As others have pointed out:
1 row, 1 value (or fields in the select statement)
.ExecuteScalar

1 row, multiple values
.ExecuteReader OR output parameters
(output parameters are ~slightly faster .. but require more work to get
at them)

MultipleRows , MultipleValues
.ExecuteReader
and
ExecuteReader can be substituted with LoadDataSet or equivalent.
and IDataReader ( which is the return of an ExecuteReader) is a firehose
method.
the others are "in memory, get the whole thing" loaders.


See a good demo at:
5/24/2006
Custom Objects/Collections and Tiered Development
http://sholliday.spaces.live.com/blog/




"Tony K" <ki********@NOSPAMcomcast.netwrote in message
news:Ok**************@TK2MSFTNGP03.phx.gbl...

How would I assign the result of a SQL query to a variable. The result of
the following statement will ALWAYS result in 1 row returned.

SELECT ProductDescription, ProductID
FROM Products
WHERE (ProductIDNumber = ?)

How do I assign the...say, ProductDescription to a variable called

prodDesc?

>
Thanks,
Tony