Query inside of Access Database

I want to programmatically call a query, which is inside the Access Database.
Either in C# or VB.NET. I dont' watn to write the query in the code. Rather
would call a query inside the access db.

Thanks

It is called a stored procedure and yes, you can.

--
Miha Markic [MVP C#]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

"pradeep" <pradeep@discussions.microsoft.com> wrote in message
news:3CE0C564-9DF8-4200-913F-E2F6199EF35D@microsoft.com...
>I want to programmatically call a query, which is inside the Access
>Database.
> Either in C# or VB.NET. I dont' watn to write the query in the code.
> Rather
> would call a query inside the access db.
>
> Thanks
>

On Tue, 7 Feb 2006 12:15:28 -0800, "pradeep" <pradeep@discussions.microsoft.com> wrote:

¤ I want to programmatically call a query, which is inside the Access Database.
¤ Either in C# or VB.NET. I dont' watn to write the query in the code. Rather
¤ would call a query inside the access db.

I'll assume you want to return data. Relatively simple method below:

Dim AccessConn As System.Data.OleDb.OleDbConnection

AccessConn = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=e:\My Documents\db1.mdb")

AccessConn.Open()

Dim AccessCommand As New System.Data.OleDb.OleDbCommand("qryTable1", AccessConn)
AccessCommand.CommandType = CommandType.StoredProcedure

Dim da As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter

With da
.SelectCommand = AccessCommand
End With

Dim ds As New DataSet("AccessTables")
da.Fill(ds, "Table1")

DataGrid1.SetDataBinding(ds, "Table1")
DataGrid1.Refresh()

AccessConn.Close()


Paul
~~~~
Microsoft MVP (Visual Basic)

Paul,

I am stuck with this. I am using your code as an example an it never returns
any rows. The query works ok in Access and I get no errors, just an empty
dataset.

My query - procSearchFirst
PARAMETERS @strFirst Text ( 255 );
SELECT *
FROM ticket
WHERE (((UCase([First])) Like UCase(strFirst+"*")));

My Code snippet:
Dim AccessConn As System.Data.OleDb.OleDbConnection
AccessConn = New System.Data.OleDb.OleDbConnection(ConnString)
AccessConn.Open()
Dim AccessCommand As New
System.Data.OleDb.OleDbCommand("procSearchFirst", AccessConn)
AccessCommand.CommandType = CommandType.StoredProcedure
AccessCommand.Parameters.Add("@strFirst",
System.Data.OleDb.OleDbType.VarChar).Value = txtSrchFirst.Text

MsgBox(AccessCommand.Parameters("@strFirst").Value)


Dim da As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter

With da
.SelectCommand = AccessCommand
End With

Dim ds As New DataSet()
da.Fill(ds, "Peeps")
MsgBox(ds.Tables("Peeps").Rows.Count())

Count is always zero. Any Suggestions please??


"Paul Clement" wrote:

> On Tue, 7 Feb 2006 12:15:28 -0800, "pradeep" <pradeep@discussions.microsoft.com> wrote:
>
> ¤ I want to programmatically call a query, which is inside the Access Database.
> ¤ Either in C# or VB.NET. I dont' watn to write the query in the code. Rather
> ¤ would call a query inside the access db.
>
> I'll assume you want to return data. Relatively simple method below:
>
> Dim AccessConn As System.Data.OleDb.OleDbConnection
>
> AccessConn = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
> "Data Source=e:\My Documents\db1.mdb")
>
> AccessConn.Open()
>
> Dim AccessCommand As New System.Data.OleDb.OleDbCommand("qryTable1", AccessConn)
> AccessCommand.CommandType = CommandType.StoredProcedure
>
> Dim da As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter
>
> With da
> .SelectCommand = AccessCommand
> End With
>
> Dim ds As New DataSet("AccessTables")
> da.Fill(ds, "Table1")
>
> DataGrid1.SetDataBinding(ds, "Table1")
> DataGrid1.Refresh()
>
> AccessConn.Close()
>
>
> Paul
> ~~~~
> Microsoft MVP (Visual Basic)
>

On Wed, 1 Mar 2006 09:20:33 -0800, "GBAS" <gavinator@nospam.nospam> wrote:

¤ Paul,
¤
¤ I am stuck with this. I am using your code as an example an it never returns
¤ any rows. The query works ok in Access and I get no errors, just an empty
¤ dataset.
¤
¤ My query - procSearchFirst
¤ PARAMETERS @strFirst Text ( 255 );
¤ SELECT *
¤ FROM ticket
¤ WHERE (((UCase([First])) Like UCase(strFirst+"*")));

Any difference if you remove the '@' character from your QueryDef parameter name?


Paul
~~~~
Microsoft MVP (Visual Basic)

UCase(strFirst+"*")) should be UCase(strFirst+"%"))

Jet Database engine used by OleDbConnection uses % instead of * for
wildcards.

"GBAS" <gavinator@nospam.nospam> wrote in message
news:44329014-788A-4DA1-8445-8F5AB3A96D87@microsoft.com...
> Paul,
>
> I am stuck with this. I am using your code as an example an it never
> returns
> any rows. The query works ok in Access and I get no errors, just an empty
> dataset.
>
> My query - procSearchFirst
> PARAMETERS @strFirst Text ( 255 );
> SELECT *
> FROM ticket
> WHERE (((UCase([First])) Like UCase(strFirst+"*")));
>
> My Code snippet:
> Dim AccessConn As System.Data.OleDb.OleDbConnection
> AccessConn = New System.Data.OleDb.OleDbConnection(ConnString)
> AccessConn.Open()
> Dim AccessCommand As New
> System.Data.OleDb.OleDbCommand("procSearchFirst", AccessConn)
> AccessCommand.CommandType = CommandType.StoredProcedure
> AccessCommand.Parameters.Add("@strFirst",
> System.Data.OleDb.OleDbType.VarChar).Value = txtSrchFirst.Text
>
> MsgBox(AccessCommand.Parameters("@strFirst").Value)
>
>
> Dim da As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter
>
> With da
> .SelectCommand = AccessCommand
> End With
>
> Dim ds As New DataSet()
> da.Fill(ds, "Peeps")
> MsgBox(ds.Tables("Peeps").Rows.Count())
>
> Count is always zero. Any Suggestions please??
>
>
> "Paul Clement" wrote:
>
>> On Tue, 7 Feb 2006 12:15:28 -0800, "pradeep"
>> <pradeep@discussions.microsoft.com> wrote:
>>
>> ¤ I want to programmatically call a query, which is inside the Access
>> Database.
>> ¤ Either in C# or VB.NET. I dont' watn to write the query in the code.
>> Rather
>> ¤ would call a query inside the access db.
>>
>> I'll assume you want to return data. Relatively simple method below:
>>
>> Dim AccessConn As System.Data.OleDb.OleDbConnection
>>
>> AccessConn = New
>> System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
>> "Data Source=e:\My Documents\db1.mdb")
>>
>> AccessConn.Open()
>>
>> Dim AccessCommand As New
>> System.Data.OleDb.OleDbCommand("qryTable1", AccessConn)
>> AccessCommand.CommandType = CommandType.StoredProcedure
>>
>> Dim da As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter
>>
>> With da
>> .SelectCommand = AccessCommand
>> End With
>>
>> Dim ds As New DataSet("AccessTables")
>> da.Fill(ds, "Table1")
>>
>> DataGrid1.SetDataBinding(ds, "Table1")
>> DataGrid1.Refresh()
>>
>> AccessConn.Close()
>>
>>
>> Paul
>> ~~~~
>> Microsoft MVP (Visual Basic)
>>

Thanks for the reply.

Given there are two instances of strFirst in the QueryDef I have tried
various combinations with the following results.

@ before both strFirst's 0 records
@ before first one only gives 0 records
@ before second one only gives 0 records
no @ on either gives 0 records

They all work fine in access! Prompt for a parameter and return expected
results.

Any other suggestions please?


"Paul Clement" wrote:

> On Wed, 1 Mar 2006 09:20:33 -0800, "GBAS" <gavinator@nospam.nospam> wrote:
>
> ¤ Paul,
> ¤
> ¤ I am stuck with this. I am using your code as an example an it never returns
> ¤ any rows. The query works ok in Access and I get no errors, just an empty
> ¤ dataset.
> ¤
> ¤ My query - procSearchFirst
> ¤ PARAMETERS @strFirst Text ( 255 );
> ¤ SELECT *
> ¤ FROM ticket
> ¤ WHERE (((UCase([First])) Like UCase(strFirst+"*")));
>
> Any difference if you remove the '@' character from your QueryDef parameter name?
>
>
> Paul
> ~~~~
> Microsoft MVP (Visual Basic)
>

OMG, that works!!! Thank you.

However, I find it a bit silly that to use a query via OLEDB means it isn't
usable in Access. Oh well.

Thanks again.

"Jim Hughes" wrote:

> UCase(strFirst+"*")) should be UCase(strFirst+"%"))
>
> Jet Database engine used by OleDbConnection uses % instead of * for
> wildcards.
>
> "GBAS" <gavinator@nospam.nospam> wrote in message
> news:44329014-788A-4DA1-8445-8F5AB3A96D87@microsoft.com...
> > Paul,
> >
> > I am stuck with this. I am using your code as an example an it never
> > returns
> > any rows. The query works ok in Access and I get no errors, just an empty
> > dataset.
> >
> > My query - procSearchFirst
> > PARAMETERS @strFirst Text ( 255 );
> > SELECT *
> > FROM ticket
> > WHERE (((UCase([First])) Like UCase(strFirst+"*")));
> >
> > My Code snippet:
> > Dim AccessConn As System.Data.OleDb.OleDbConnection
> > AccessConn = New System.Data.OleDb.OleDbConnection(ConnString)
> > AccessConn.Open()
> > Dim AccessCommand As New
> > System.Data.OleDb.OleDbCommand("procSearchFirst", AccessConn)
> > AccessCommand.CommandType = CommandType.StoredProcedure
> > AccessCommand.Parameters.Add("@strFirst",
> > System.Data.OleDb.OleDbType.VarChar).Value = txtSrchFirst.Text
> >
> > MsgBox(AccessCommand.Parameters("@strFirst").Value)
> >
> >
> > Dim da As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter
> >
> > With da
> > .SelectCommand = AccessCommand
> > End With
> >
> > Dim ds As New DataSet()
> > da.Fill(ds, "Peeps")
> > MsgBox(ds.Tables("Peeps").Rows.Count())
> >
> > Count is always zero. Any Suggestions please??
> >
> >
> > "Paul Clement" wrote:
> >
> >> On Tue, 7 Feb 2006 12:15:28 -0800, "pradeep"
> >> <pradeep@discussions.microsoft.com> wrote:
> >>
> >> ¤ I want to programmatically call a query, which is inside the Access
> >> Database.
> >> ¤ Either in C# or VB.NET. I dont' watn to write the query in the code.
> >> Rather
> >> ¤ would call a query inside the access db.
> >>
> >> I'll assume you want to return data. Relatively simple method below:
> >>
> >> Dim AccessConn As System.Data.OleDb.OleDbConnection
> >>
> >> AccessConn = New
> >> System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
> >> "Data Source=e:\My Documents\db1.mdb")
> >>
> >> AccessConn.Open()
> >>
> >> Dim AccessCommand As New
> >> System.Data.OleDb.OleDbCommand("qryTable1", AccessConn)
> >> AccessCommand.CommandType = CommandType.StoredProcedure
> >>
> >> Dim da As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter
> >>
> >> With da
> >> .SelectCommand = AccessCommand
> >> End With
> >>
> >> Dim ds As New DataSet("AccessTables")
> >> da.Fill(ds, "Table1")
> >>
> >> DataGrid1.SetDataBinding(ds, "Table1")
> >> DataGrid1.Refresh()
> >>
> >> AccessConn.Close()
> >>
> >>
> >> Paul
> >> ~~~~
> >> Microsoft MVP (Visual Basic)
> >>
>
>
>

On Wed, 1 Mar 2006 21:37:10 -0800, "Jim Hughes" <NOSPAMJ3033@Hotmail.com> wrote:

¤ UCase(strFirst+"*")) should be UCase(strFirst+"%"))
¤
¤ Jet Database engine used by OleDbConnection uses % instead of * for
¤ wildcards.
¤

It would be quite interesting if it was the problem since he's calling a QueryDef and not executing
a SQL statement. It certainly wouldn't be what I would expect anyway.


Paul
~~~~
Microsoft MVP (Visual Basic)

On Thu, 2 Mar 2006 07:44:30 -0800, "GBAS" <gavinator@nospam.nospam> wrote:

¤ OMG, that works!!! Thank you.
¤
¤ However, I find it a bit silly that to use a query via OLEDB means it isn't
¤ usable in Access. Oh well.
¤

Yes and the problem is documented in the below MS KB article:

Wildcards and Stored Queries
If you have a stored QueryDef in an MDB file, created through Access or DAO, that uses wildcard
characters, it will not return any records if run under ADO. The OLEDB provider for Jet recompiles
the SQL and tells the query engine to use the ANSI wildcard characters (see table above).

http://support.microsoft.com/default.aspx/kb/225048


Paul
~~~~
Microsoft MVP (Visual Basic)