Access queryDef as stored procedure

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?

Mary
Tue Jan 03 15:29:12 CST 2006

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

On Tue, 3 Jan 2006 11:07:03 -0800, "jonefer"
<jonefer@discussions.microsoft.com> wrote:

>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?
>

Robbe
Tue Jan 03 17:40:40 CST 2006

I'm a little confused by your question. That said, this
ADO.NET code generator will write the object oriented
data access layers needed to call all of your stored
queries/database statements in Microsoft Access.

It may help you...

http://www.eggheadcafe.com/articles/microsoftaccess_source_code_generator.asp

--
Robbe Morris - 2004/2005 Microsoft MVP C#
http://www.eggheadcafe.com/forums/merit.asp





"jonefer" <jonefer@discussions.microsoft.com> wrote in message
news:9B7663CC-EB1E-40A6-AB81-6F8831F726E9@microsoft.com...
> 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?
>
>

Paul
Wed Jan 04 09:51:56 CST 2006

On Tue, 3 Jan 2006 11:07:03 -0800, "jonefer" <jonefer@discussions.microsoft.com> wrote:

¤ 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)