Datagrid

[Channa]

I have a datagrid that is bound to an MSDataSetGenerator
that is then bound to an access database though a
OleDbDataAdapter and an OleDbConnection. I am able to
open the form and its datagrid with no problem. My
question is no I want the underlaying sql statement to be
able to be based on a variable. In other words my sql
statement is: select * from tblCustomers where CustKey =
1. I would like to set up a interager variable intI and
have a statment like: select * from tblCustomers where
CustKey = intI.

Does anyone know how to do this - with examples please.

Thanks!

Channa

 

[Sean]

Hi Channa, if you have a variable named intI, you can do
this.

Select * from tblCustomers where CustKey = "& intI &"

That way your CustKey would be a variable.

Sean

 

[BB]

Channa,

Short answer is that you need a parameters collection,
which lets you stick with a single select statement, but
update the key value dynamically like in your example.
Check out *Using Parameters with a DataAdapter* in the on-
line help.

Here's an excerpt:

*****
Dim selectSQL As String = "SELECT CustomerID, CompanyName
FROM Customers WHERE Country = ? AND City = ?"

The parameterized query statements define which input and
output parameters will need to be created. To create a
parameter, use the Parameters.Add method or the Parameter
constructor to specify the column name, data type, and
size. For intrinsic data types, such as Integer, you do
not need to include the size, or you can specify the
default size.

The following code example creates the parameters for the
SQL statement from the preceding example and fills a
DataSet

Dim nwindConn As OleDbConnection = New OleDbConnection
("Provider=SQLOLEDB;Data Source=localhost;" & _
"In
tegrated Security=SSPI;Initial Catalog=northwind")
Dim custDA As OleDbDataAdapter = New OleDbDataAdapter

Dim selectCMD AS OleDbCommand = New OleDbCommand
(selectSQL, nwindConn)
custDA.SelectCommand = selectCMD

' Add parameters and set values.
selectCMD.Parameters.Add("@Country", OleDbType.VarChar,
15).Value = "UK"
selectCMD.Parameters.Add("@City", OleDbType.VarChar,
15).Value = "London"

Dim custDS As DataSet = New DataSet
custDA.Fill(custDS, "Customers")
*****

A couple comments...

1. This example is specific to Access (for SQL Server,
you'd use <@varname> instead of the question marks.

2. When you create the parameters collection, look at the
few different ways to construct them. At least for me at
the time, the .Add syntax on the .parameters was a little
confusing. In effect, you're constructing the .parameter
using <parameter name> and <type>, and then in the same
motion setting .value of that parameter to whatever you
want. Note that there's another construction of the
parameter that lets you provide the SourceColumn, which
in effect lets you map a datasource to provide the actual
value of the parameter, rather than having to set .value
explicitly in code. This second construction is the way
you'd do it if, say, you were iterating the customer
table and for each row wanted to run your select (or
insert, update, delete, etc.).

hth,

Bill