Re: Batch commands & Ms-Access

Tech-Archive recommends: Fix windows errors by optimizing your registry

Hey Cor,

It is not only Access, you cannot use Batch SQL commands in ADONET.
====================
I don't quite think so, for SQLServer, we can execute two sql query through
single statement in a SqlCommand instance.... And we can let the DataReader
return two ResultSet or let the DataAdpater fill two DataTable into a
DataSet..... e.g:

SqlConnection conn = new
SqlConnection("server=(local);database=ncs;uid=ncs;pwd=ncs;");
SqlCommand cmd = new SqlCommand("select * from ncsuser;select * from
ncshistory", conn);

I've ever done this through both Sql managed provider or OleDbProvider.....
(And for Access ,we can not even execute such mutpile select query through
its query wizard....., so this is DataBase specific, nothing to do with
ADO.NET.....)


Hi Selva,

For your scenario, that you need to query two tables from Access Db and
store them into a single DataSet, we need to execute two select statements
separately and fill them into the same DataSet (specify the table name...),
then we can add the relation for the two DataTables in the DataSet...

e.g:

==============================
Private Sub btnLoad_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnLoad.Click

Try

Dim conn As OleDbConnection
Dim adapter As OleDbDataAdapter
Dim adapter1 As OleDbDataAdapter

conn = New OleDbConnection("Jet OLEDB:Database Locking
Mode=1;Data
Source=""F:\users\stcheng\workspace\dotnet\CS\winform\WindowsApplication2\bi
n\Debug\Northwind.mdb"";Mode=Share Deny None;Jet OLEDB:Engine
Type=5;Provider=""Microsoft.Jet.OLEDB.4.0"";")
adapter = New OleDbDataAdapter("select * from Orders", conn)


adapter.Fill(ds, "Orders")

Me.DataGrid1.DataSource = ds
Me.DataGrid1.DataMember = "Orders"


adapter.SelectCommand = New OleDbCommand("select * from [Order
Details]", conn)
adapter.Fill(ds, "Order Details")

Me.DataGrid2.DataSource = ds



Catch ex As Exception
MessageBox.Show(ex.ToString())
End Try

End Sub
==========================

Thanks,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)



--------------------
| From: "Cor Ligthert [MVP]" <notmyfirstname@xxxxxxxxx>
| References: <u2CQaq0$FHA.3136@xxxxxxxxxxxxxxxxxxxx>
<VAgJbc7$FHA.3764@xxxxxxxxxxxxxxxxxxxxx>
<77E1B6D5-2922-47DE-8D4E-D4646F7A5A12@xxxxxxxxxxxxx>
<OeJdIa$$FHA.1256@xxxxxxxxxxxxxxxxxxxx>
<1774E3ED-A302-47E4-9311-B5ECA88C0849@xxxxxxxxxxxxx>
| Subject: Re: Batch commands & Ms-Access
| Date: Tue, 13 Dec 2005 16:33:07 +0100
| Lines: 19
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
| X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
| X-RFC2646: Format=Flowed; Original
| Message-ID: <uGpdyp$$FHA.3296@xxxxxxxxxxxxxxxxxxxx>
| Newsgroups: microsoft.public.dotnet.languages.vb
| NNTP-Posting-Host: ip3e830773.speed.planet.nl 62.131.7.115
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.dotnet.languages.vb:308475
| X-Tomcat-NG: microsoft.public.dotnet.languages.vb
|
| Selva,
|
| If you use Net 1.x than you need two selects for it.
|
| Net 2.0 has a new feature however that I did not try yet. If you use that
| than, just use the create datasource in the Data tab from Visual Studio.
|
| In net 1.x you need just two seperate Fills and add the relation.
|
| See this page for how to create a relation (The tables are here build on
| the fly in the sample)
|
|
http://www.vb-tips.com/default.aspx?ID=d667d78f-4b08-42ef-ba3b-8b8620d93761
|
| I hope this helps,
|
| Cor
|
|
|

.

Relevant Pages

  • Re: creating and executing queries in VBA.
    ... Execute it into what? ... You can't just "execute" a select query. ... When you execute sql it has to go into a report, or a form or a record set. ...
    (microsoft.public.access.modulesdaovba)
  • Re: freetexttable query never completes nor times out?
    ... ORDER BY Rank DESC ... Looking for a SQL Server replication book? ... Microsoft Analysis Services Client Tools 2005.090.2047.00 ... I have a simple freetexttable query that seems to run forever without ...
    (microsoft.public.sqlserver.fulltext)
  • Re: UPDATE query in Access 2003 raising error
    ... Runtime error 3066 Query must have at least one destination field. ... The SQL works fine if I use it in the QBF Design mode. ... Set qdfTemp = db.CreateQueryDef ... Elsewhere in the code I use the same technique to execute an SQL ...
    (microsoft.public.access.formscoding)
  • Re: [Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT I
    ... This statement causes ADO to create a recordset to receive the results ... of the query being executed and assign that recordset to your qryAddProj ... You should use the ExecuteOptions argument of the Execute ... string containing a sql statement to be executed. ...
    (microsoft.public.data.ado)
  • BUG: freetexttable query never completes nor times out?
    ... Microsoft SQL Server Management Studio 9.00.2047.00 ... Microsoft Analysis Services Client Tools 2005.090.2047.00 ... I have a simple freetexttable query that seems to run forever without timing ... Only this query stalls...the SQL Server is still responding to other queries ...
    (microsoft.public.sqlserver.fulltext)