Query inside of Access Database

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

Guest

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
 
¤ 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,
¤
¤ 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 said:
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 said:
On Tue, 7 Feb 2006 12:15:28 -0800, "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.

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)
Click to expand...
Click to expand...
 
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?
 
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 said:
UCase(strFirst+"*")) should be UCase(strFirst+"%"))

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

GBAS said:
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 said:
On Tue, 7 Feb 2006 12:15:28 -0800, "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.

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)
Click to expand...
Click to expand...
Click to expand...
 
¤ 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)
 
¤ 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)
 
Back
Top