Query inside of Access Database

7 Feb 2006 8:15 PM

pradeep

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

7 Feb 2006 8:43 PM

Miha Markic [MVP C#]

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/

Show quote

"pradeep" <prad***@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
>

8 Feb 2006 3:56 PM

Paul Clement

On Tue, 7 Feb 2006 12:15:28 -0800, "pradeep" <prad***@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)

1 Mar 2006 5:20 PM

GBAS

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

Show quote

"Paul Clement" wrote:

> On Tue, 7 Feb 2006 12:15:28 -0800, "pradeep" <prad***@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)
>

1 Mar 2006 7:14 PM

Paul Clement

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)

2 Mar 2006 3:26 PM

GBAS

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?

Show quote

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

2 Mar 2006 5:37 AM

Jim Hughes

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

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

Show quote

"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"
>> <prad***@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)
>>

2 Mar 2006 3:44 PM

GBAS

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.

Show quote

"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"
> >> <prad***@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)
> >>
>
>
>

2 Mar 2006 4:45 PM

Paul Clement

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)

2 Mar 2006 3:47 PM

Paul Clement

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)