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 ProductDescript ion, ProductID
FROM Products
WHERE (ProductIDNumbe r = ?)

How do I assign the...say, ProductDescript ion 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********@NOS PAMcomcast.nets chreef in bericht
news:Ok******** ******@TK2MSFTN GP03.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 ProductDescript ion, ProductID
FROM Products
WHERE (ProductIDNumbe r = ?)

How do I assign the...say, ProductDescript ion 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 ProductDescript ion 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 ProductDescript ion, 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********@NOS PAMcomcast.netw rote in message
news:Ok******** ******@TK2MSFTN GP03.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 ProductDescript ion, ProductID
FROM Products
WHERE (ProductIDNumbe r = ?)

How do I assign the...say, ProductDescript ion 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 ProductDescript ion 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******** *****@TK2MSFTNG P06.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********@NOS PAMcomcast.nets chreef in bericht
news:Ok******** ******@TK2MSFTN GP03.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 ProductDescript ion, ProductID
FROM Products
WHERE (ProductIDNumbe r = ?)

How do I assign the...say, ProductDescript ion 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********@NOS PAMcomcast.nets chreef in bericht
news:ej******** ******@TK2MSFTN GP02.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 ProductDescript ion 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******** *****@TK2MSFTNG P06.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********@NOS PAMcomcast.nets chreef in bericht
news:Ok******* *******@TK2MSFT NGP03.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 ProductDescript ion, ProductID
FROM Products
WHERE (ProductIDNumbe r = ?)

How do I assign the...say, ProductDescript ion 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********@NOS PAMcomcast.nets chreef in bericht
news:ej******** ******@TK2MSFTN GP02.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 ProductDescript ion 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******* ******@TK2MSFTN GP06.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********@NOS PAMcomcast.nets chreef in bericht
news:Ok****** ********@TK2MSF TNGP03.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 ProductDescript ion, ProductID
FROM Products
WHERE (ProductIDNumbe r = ?)

How do I assign the...say, ProductDescript ion 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.Ole Db
Public Class Form1

Private Sub Form1_Load(ByVa l sender As System.Object, ByVal e As
System.EventArg s) 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.Da taReaderConnect ionString) 'I set this up
using the wizard by adding a new DataSource. (Access DB)

cnnInvMan.Open( )
strSQL = "SELECT * FROM EMPLOYEES"
cmmInvMan = New OleDbCommand(st rSQL, cnnInvMan)
drdTest = cmmInvMan.Execu teReader
Do While drdTest.Read
field1 = drdTest.Item("F irstName") '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.Ole Db
Public Class Form1

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

cnnInvMan = New
OleDbConnection (My.Settings.Da taReaderConnect ionString)

cnnInvMan.Open( )
strSQL = "SELECT FirstName FROM EMPLOYEES"
cmmInvMan = New OleDbCommand(st rSQL, cnnInvMan)
field1 = cmmInvMan.Execu teScalar().ToSt ring
MessageBox.Show (field1)
End Sub
End Class

Thanks again to everyone!!

Tony K

"Göran Andersson" <gu***@guffa.co mwrote in message
news:ey******** *****@TK2MSFTNG P06.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********@NOS PAMcomcast.nets chreef in bericht
news:ej******* *******@TK2MSFT NGP02.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 ProductDescript ion 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****** *******@TK2MSFT NGP06.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********@NOS PAMcomcast.nets chreef in bericht
news:Ok***** *********@TK2MS FTNGP03.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 ProductDescript ion, ProductID
FROM Products
WHERE (ProductIDNumbe r = ?)
>
How do I assign the...say, ProductDescript ion 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("Sel ect 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 ApplicationExce ption("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(M y.Settings.Nort hwindConnection String)
cn.Open()
Dim cmd As New SqlCommand(ss, cn)

Dim pUnitPrice, pInStock, pProductName As SqlParameter
pUnitPrice = cmd.Parameters. Add("@UnitPrice ", SqlDbType.Money )
pUnitPrice.Dire ction = ParameterDirect ion.Output
pInStock = cmd.Parameters. Add("@UnitsInSt ock", _
SqlDbType.NVarC har, 20)
pInStock.Direct ion = ParameterDirect ion.Output

pProductName = cmd.Parameters. Add("ProductNam e", _
SqlDbType.NVarC har, 40)
pProductName.Va lue = "Chai"

cmd.ExecuteNonQ uery()

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

cn.Close()
Good luck.
Robin S.
----------------------------------------------
"Tony K" <ki********@NOS PAMcomcast.netw rote in message
news:Ok******** ******@TK2MSFTN GP03.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 ProductDescript ion, ProductID
FROM Products
WHERE (ProductIDNumbe r = ?)

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

Thanks,
Tony

[Tom Leylan]

"Tony K" <ki********@NOS PAMcomcast.netw rote...

THIS IS USING THE EXECUTESCALAR

cnnInvMan.Open( )
strSQL = "SELECT FirstName FROM EMPLOYEES"
cmmInvMan = New OleDbCommand(st rSQL, cnnInvMan)
field1 = cmmInvMan.Execu teScalar().ToSt ring
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******** *******@TK2MSFT NGP04.phx.gbl.. .

"Tony K" <ki********@NOS PAMcomcast.netw rote...

>THIS IS USING THE EXECUTESCALAR

> cnnInvMan.Open( )
strSQL = "SELECT FirstName FROM EMPLOYEES"
cmmInvMan = New OleDbCommand(st rSQL, cnnInvMan)
field1 = cmmInvMan.Execu teScalar().ToSt ring
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.