SQL result assigned to a variable?

  • Thread starter Thread starter Tony K
  • Start date Start date
T

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
 
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
 
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/
 
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
 
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
 
Cor said:
Tony,

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

Not only. Output parameters can also be used.
 
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.DataReaderConnectionString) '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.DataReaderConnectionString)

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
 
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.NorthwindConnectionString)
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 said:
THIS IS USING THE EXECUTESCALAR
Click to expand...
cnnInvMan.Open()
strSQL = "SELECT FirstName FROM EMPLOYEES"
cmmInvMan = New OleDbCommand(strSQL, cnnInvMan)
field1 = cmmInvMan.ExecuteScalar().ToString
MessageBox.Show(field1)
Click to expand...

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).
 
Tom Leylan said:
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).
Click to expand...

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

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

Robin S.
 
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/
 
Back
Top