Access queryDef as stored procedure

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

in the 'Access Cookbook' by Getz, Litwin and Baron, it shows how to get a
qryAlternativeAlbums query as an OleDbDataReader:

'Construct an OleDbCommand to execute the query
Dim AltRock as OleDbCommand = _
New OleDbCommand("qryAlternativeAlbums", cnx)

'Odd as it may seem, you need to set the CommandType
'to CommandType.StoredProcedure.
cmdAltRock.CommandType = CommandType.StoredProcedure

'Run the query and place the rows in an OledbDataReader.
Dim drAltRock as OleDbDataReader
drAltRock = cmdAltRock.ExecuteReader

'Bind the OleDbDataReader to the DataGrid
dgrAltRock.DataSource = drAltRock
dgrAltRock.DataBind()


As an Access developer, this was exciting news, as I thought I presently had
no way to use joins to Union queries, etc...

Anyhow, I was wondering if I can use the DataAdapter in the same fashion?
Please help me get any kinks out if this is possible.
And if not, how can I parameterize a DataReader - is this possible?
 
Access SQL has always supported UNION and UNION ALL queries. You can
save them and execute them using the code you posted. There's no point
in using a DataAdapter with a UNION query because it is not
updateable. You can supply parameter values to your command object in
the usual way -- see the OleDbParameter topic in Help for more
information.

--Mary
 
¤ in the 'Access Cookbook' by Getz, Litwin and Baron, it shows how to get a
¤ qryAlternativeAlbums query as an OleDbDataReader:
¤
¤ 'Construct an OleDbCommand to execute the query
¤ Dim AltRock as OleDbCommand = _
¤ New OleDbCommand("qryAlternativeAlbums", cnx)
¤
¤ 'Odd as it may seem, you need to set the CommandType
¤ 'to CommandType.StoredProcedure.
¤ cmdAltRock.CommandType = CommandType.StoredProcedure
¤
¤ 'Run the query and place the rows in an OledbDataReader.
¤ Dim drAltRock as OleDbDataReader
¤ drAltRock = cmdAltRock.ExecuteReader
¤
¤ 'Bind the OleDbDataReader to the DataGrid
¤ dgrAltRock.DataSource = drAltRock
¤ dgrAltRock.DataBind()
¤
¤
¤ As an Access developer, this was exciting news, as I thought I presently had
¤ no way to use joins to Union queries, etc...
¤
¤ Anyhow, I was wondering if I can use the DataAdapter in the same fashion?
¤ Please help me get any kinks out if this is possible.
¤ And if not, how can I parameterize a DataReader - is this possible?
¤

Yes, you can run an Access QueryDef using a DataAdapter:

Dim AccessConn As System.Data.OleDb.OleDbConnection
Dim AccessCommand As System.Data.OleDb.OleDbCommand
Dim AccessReader As System.Data.OleDb.OleDbDataReader
Dim ConnectionString As String

ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=e:\My Documents\db1.mdb;" & _
"Jet OLEDB:System database=C:\Winnt\System32\System.MDW;" & _
"User ID=Admin;" & _
"Password="

AccessConn = New System.Data.OleDb.OleDbConnection(ConnectionString)

AccessConn.Open()
AccessCommand = New System.Data.OleDb.OleDbCommand("ValidateUser", AccessConn)
AccessCommand.CommandType = CommandType.StoredProcedure
Console.WriteLine(AccessCommand.CommandText)
AccessCommand.Parameters.Add("@pUserID", CType(System.Data.OleDb.OleDbType.VarWChar,
System.Data.OleDb.OleDbType), 15).Value = "username"
AccessCommand.Parameters.Add("@pPassword", CType(System.Data.OleDb.OleDbType.VarWChar,
System.Data.OleDb.OleDbType), 15).Value = "password"

Dim da As System.Data.OleDb.OleDbDataAdapter = New System.Data.OleDb.OleDbDataAdapter
With da
.SelectCommand = AccessCommand
End With

Dim ds As New DataSet
da.Fill(ds)

The following uses the DataReader w/the above Command:

AccessReader = AccessCommand.ExecuteReader


Paul
~~~~
Microsoft MVP (Visual Basic)
 
Back
Top