473,320 Members | 1,732 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Batch commands & Ms-Access

Hi I am using batch commands against Ms-Access and getting an error
Message "Characters found after end of SQL statement." String

Here is my sql Dim str_screens As String = "Select * from Screens Order by
ScreenName;Select * from Functions"

How can I fix this problem. Any help is greatly appreciated.

Selva

Dec 12 '05 #1
12 5082
"Selva Chinnasamy" <se*****************@online.nospam> schrieb
Hi I am using batch commands against Ms-Access and getting an error
Message "Characters found after end of SQL statement." String

Here is my sql Dim str_screens As String = "Select * from Screens
Order by ScreenName;Select * from Functions"

How can I fix this problem.


Remove the characters after the end of SQL statement. The end is the ";"
BTW, this is the group for database related questions:
micrsoft.public.dotnet.framework.adonet
Armin

Dec 12 '05 #2
Hey Selva,

I've also found someone else encountered this problem. Actually it is
caused by the Access db query engine dosn't support mulitple query
statement batch executing. We may need to use two SqlCommands to execute
the two sqlstatements through the same sqlconnection .... You can also
found the discussion on this in the following thread:

http://forums.asp.net/936743/showpost.aspx

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: "Selva Chinnasamy" <se*****************@online.nospam>
| Subject: Batch commands & Ms-Access
| Date: Mon, 12 Dec 2005 13:33:51 -0500
| Lines: 12
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1506
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1506
| Message-ID: <u2**************@TK2MSFTNGP15.phx.gbl>
| Newsgroups: microsoft.public.dotnet.languages.vb
| NNTP-Posting-Host: w27fw1.pb.com 199.231.49.128
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGP15.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.dotnet.languages.vb:308263
| X-Tomcat-NG: microsoft.public.dotnet.languages.vb
|
| Hi I am using batch commands against Ms-Access and getting an error
| Message "Characters found after end of SQL statement." String
|
| Here is my sql Dim str_screens As String = "Select * from Screens Order by
| ScreenName;Select * from Functions"
|
| How can I fix this problem. Any help is greatly appreciated.
|
| Selva
|
|
|
|

Dec 13 '05 #3
Stevan, Thanks for your reply. But I couldn't find an answer for the problem.
Any help to resolve the problem is greatly appreciated.

Selva

"Steven Cheng[MSFT]" wrote:
Hey Selva,

I've also found someone else encountered this problem. Actually it is
caused by the Access db query engine dosn't support mulitple query
statement batch executing. We may need to use two SqlCommands to execute
the two sqlstatements through the same sqlconnection .... You can also
found the discussion on this in the following thread:

http://forums.asp.net/936743/showpost.aspx

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: "Selva Chinnasamy" <se*****************@online.nospam>
| Subject: Batch commands & Ms-Access
| Date: Mon, 12 Dec 2005 13:33:51 -0500
| Lines: 12
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1506
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1506
| Message-ID: <u2**************@TK2MSFTNGP15.phx.gbl>
| Newsgroups: microsoft.public.dotnet.languages.vb
| NNTP-Posting-Host: w27fw1.pb.com 199.231.49.128
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGP15.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.dotnet.languages.vb:308263
| X-Tomcat-NG: microsoft.public.dotnet.languages.vb
|
| Hi I am using batch commands against Ms-Access and getting an error
| Message "Characters found after end of SQL statement." String
|
| Here is my sql Dim str_screens As String = "Select * from Screens Order by
| ScreenName;Select * from Functions"
|
| How can I fix this problem. Any help is greatly appreciated.
|
| Selva
|
|
|
|

Dec 13 '05 #4
Steven,

It is not only Access, you cannot use Batch SQL commands in ADONET.

What is it that you want to achieve because even if it did exist than that
Select alone does nothing?

Cor
"Selva" <Se***@discussions.microsoft.com> schreef in bericht
news:77**********************************@microsof t.com...
Stevan, Thanks for your reply. But I couldn't find an answer for the
problem.
Any help to resolve the problem is greatly appreciated.

Selva

"Steven Cheng[MSFT]" wrote:
Hey Selva,

I've also found someone else encountered this problem. Actually it is
caused by the Access db query engine dosn't support mulitple query
statement batch executing. We may need to use two SqlCommands to execute
the two sqlstatements through the same sqlconnection .... You can also
found the discussion on this in the following thread:

http://forums.asp.net/936743/showpost.aspx

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: "Selva Chinnasamy" <se*****************@online.nospam>
| Subject: Batch commands & Ms-Access
| Date: Mon, 12 Dec 2005 13:33:51 -0500
| Lines: 12
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1506
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1506
| Message-ID: <u2**************@TK2MSFTNGP15.phx.gbl>
| Newsgroups: microsoft.public.dotnet.languages.vb
| NNTP-Posting-Host: w27fw1.pb.com 199.231.49.128
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGP15.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.dotnet.languages.vb:308263
| X-Tomcat-NG: microsoft.public.dotnet.languages.vb
|
| Hi I am using batch commands against Ms-Access and getting an error
| Message "Characters found after end of SQL statement." String
|
| Here is my sql Dim str_screens As String = "Select * from Screens Order
by
| ScreenName;Select * from Functions"
|
| How can I fix this problem. Any help is greatly appreciated.
|
| Selva
|
|
|
|

Dec 13 '05 #5
Cor,
The reason I wanted to use Batch Command is to retrive more than one table
using an adapter and make a relationship between the tables.

Also I wanted to read data from multiple DB's and make a relationship
between them. Can you please give me an alternate for doing this?

Thanks In Adavnce
Selva

"Cor Ligthert [MVP]" wrote:
Steven,

It is not only Access, you cannot use Batch SQL commands in ADONET.

What is it that you want to achieve because even if it did exist than that
Select alone does nothing?

Cor
"Selva" <Se***@discussions.microsoft.com> schreef in bericht
news:77**********************************@microsof t.com...
Stevan, Thanks for your reply. But I couldn't find an answer for the
problem.
Any help to resolve the problem is greatly appreciated.

Selva

"Steven Cheng[MSFT]" wrote:
Hey Selva,

I've also found someone else encountered this problem. Actually it is
caused by the Access db query engine dosn't support mulitple query
statement batch executing. We may need to use two SqlCommands to execute
the two sqlstatements through the same sqlconnection .... You can also
found the discussion on this in the following thread:

http://forums.asp.net/936743/showpost.aspx

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: "Selva Chinnasamy" <se*****************@online.nospam>
| Subject: Batch commands & Ms-Access
| Date: Mon, 12 Dec 2005 13:33:51 -0500
| Lines: 12
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1506
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1506
| Message-ID: <u2**************@TK2MSFTNGP15.phx.gbl>
| Newsgroups: microsoft.public.dotnet.languages.vb
| NNTP-Posting-Host: w27fw1.pb.com 199.231.49.128
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGP15.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.dotnet.languages.vb:308263
| X-Tomcat-NG: microsoft.public.dotnet.languages.vb
|
| Hi I am using batch commands against Ms-Access and getting an error
| Message "Characters found after end of SQL statement." String
|
| Here is my sql Dim str_screens As String = "Select * from Screens Order
by
| ScreenName;Select * from Functions"
|
| How can I fix this problem. Any help is greatly appreciated.
|
| Selva
|
|
|
|


Dec 13 '05 #6
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?...b-8b8620d93761

I hope this helps,

Cor
Dec 13 '05 #7
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\winf orm\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]" <no************@planet.nl>
| References: <u2**************@TK2MSFTNGP15.phx.gbl>
<VA**************@TK2MSFTNGXA02.phx.gbl>
<77**********************************@microsoft.co m>
<Oe**************@TK2MSFTNGP15.phx.gbl>
<17**********************************@microsoft.co m>
| 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: <uG**************@TK2MSFTNGP12.phx.gbl>
| Newsgroups: microsoft.public.dotnet.languages.vb
| NNTP-Posting-Host: ip3e830773.speed.planet.nl 62.131.7.115
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGP12.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?...b-8b8620d93761
|
| I hope this helps,
|
| Cor
|
|
|

Dec 14 '05 #8
Steven,

You are right, my information was wrong, I never tested it (what I did now),
I assumed therefore it was right as I had read it (I will not tell from who
I had read it)

:-)

Thanks.

Cor

"Steven Cheng[MSFT]" <st*****@online.microsoft.com> schreef in bericht
news:0G**************@TK2MSFTNGXA02.phx.gbl...
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\winf orm\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]" <no************@planet.nl>
| References: <u2**************@TK2MSFTNGP15.phx.gbl>
<VA**************@TK2MSFTNGXA02.phx.gbl>
<77**********************************@microsoft.co m>
<Oe**************@TK2MSFTNGP15.phx.gbl>
<17**********************************@microsoft.co m>
| 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: <uG**************@TK2MSFTNGP12.phx.gbl>
| Newsgroups: microsoft.public.dotnet.languages.vb
| NNTP-Posting-Host: ip3e830773.speed.planet.nl 62.131.7.115
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGP12.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?...b-8b8620d93761
|
| I hope this helps,
|
| Cor
|
|
|

Dec 14 '05 #9
even better
i combine hundred inserts at once in my sql import application

i also performed multiple DDL statements at once just seprate them with a
; and there you go
:-)

regards

Michel Posseth [MCP]

"Steven Cheng[MSFT]" <st*****@online.microsoft.com> wrote in message
news:0G**************@TK2MSFTNGXA02.phx.gbl...
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\winf orm\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]" <no************@planet.nl>
| References: <u2**************@TK2MSFTNGP15.phx.gbl>
<VA**************@TK2MSFTNGXA02.phx.gbl>
<77**********************************@microsoft.co m>
<Oe**************@TK2MSFTNGP15.phx.gbl>
<17**********************************@microsoft.co m>
| 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: <uG**************@TK2MSFTNGP12.phx.gbl>
| Newsgroups: microsoft.public.dotnet.languages.vb
| NNTP-Posting-Host: ip3e830773.speed.planet.nl 62.131.7.115
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGP12.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?...b-8b8620d93761
|
| I hope this helps,
|
| Cor
|
|
|

Dec 16 '05 #10
Michel,

Het is niet nodig meer zout in de wond te strooien het prikt al genoeg.
(Just a Dutch phrase not important)

I am glad that Steven has showed it to me, I missed this part, if I know one
solution, than I will use it or/and try it on more places.

I hate SQL.

Just kidding of course.

:-)

Cor

Dec 16 '05 #11
Michel- When I had couple Select statements seperated with ; I am getting an
error.
I am using OleDb Adapter against Ms-Access.

Is there any Connection setting that I need to aware of.

Thanks
Selva

"m.posseth" wrote:
even better
i combine hundred inserts at once in my sql import application

i also performed multiple DDL statements at once just seprate them with a
; and there you go
:-)

regards

Michel Posseth [MCP]

"Steven Cheng[MSFT]" <st*****@online.microsoft.com> wrote in message
news:0G**************@TK2MSFTNGXA02.phx.gbl...
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\winf orm\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]" <no************@planet.nl>
| References: <u2**************@TK2MSFTNGP15.phx.gbl>
<VA**************@TK2MSFTNGXA02.phx.gbl>
<77**********************************@microsoft.co m>
<Oe**************@TK2MSFTNGP15.phx.gbl>
<17**********************************@microsoft.co m>
| 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: <uG**************@TK2MSFTNGP12.phx.gbl>
| Newsgroups: microsoft.public.dotnet.languages.vb
| NNTP-Posting-Host: ip3e830773.speed.planet.nl 62.131.7.115
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGP12.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?...b-8b8620d93761
|
| I hope this helps,
|
| Cor
|
|
|


Dec 16 '05 #12
Hey Selva,

Your ADO.NET code is correct and nothing wrong here. The limitation here is
due to the Access DataBase's query enging which doesn't support multiple
querystatements ......

Thanks & Regards,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
--------------------
| Thread-Topic: Batch commands & Ms-Access
| thread-index: AcYCStP5u936hkiDTxq9tTJbVoupYw==
| X-WBNR-Posting-Host: 199.231.49.128
| From: "=?Utf-8?B?U2VsdmE=?=" <Se***@discussions.microsoft.com>
| References: <u2**************@TK2MSFTNGP15.phx.gbl>
<VA**************@TK2MSFTNGXA02.phx.gbl>
<77**********************************@microsoft.co m>
<Oe**************@TK2MSFTNGP15.phx.gbl>
<17**********************************@microsoft.co m>
<uG**************@TK2MSFTNGP12.phx.gbl>
<0G**************@TK2MSFTNGXA02.phx.gbl>
<uK**************@TK2MSFTNGP12.phx.gbl>
| Subject: Re: Batch commands & Ms-Access
| Date: Fri, 16 Dec 2005 06:13:03 -0800
| Lines: 160
| Message-ID: <0D**********************************@microsoft.co m>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.dotnet.languages.vb
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSF TNGXA03.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.dotnet.languages.vb:308928
| X-Tomcat-NG: microsoft.public.dotnet.languages.vb
|
| Michel- When I had couple Select statements seperated with ; I am getting
an
| error.
| I am using OleDb Adapter against Ms-Access.
|
| Is there any Connection setting that I need to aware of.
|
| Thanks
| Selva
|
| "m.posseth" wrote:
|
| > even better
| > i combine hundred inserts at once in my sql import application
| >
| > i also performed multiple DDL statements at once just seprate them
with a
| > ; and there you go
| >
| >
| > :-)
| >
| > regards
| >
| > Michel Posseth [MCP]
| >
| >
| >
| > "Steven Cheng[MSFT]" <st*****@online.microsoft.com> wrote in message
| > news:0G**************@TK2MSFTNGXA02.phx.gbl...
| > > 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\winf orm\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]" <no************@planet.nl>
| > > | References: <u2**************@TK2MSFTNGP15.phx.gbl>
| > > <VA**************@TK2MSFTNGXA02.phx.gbl>
| > > <77**********************************@microsoft.co m>
| > > <Oe**************@TK2MSFTNGP15.phx.gbl>
| > > <17**********************************@microsoft.co m>
| > > | 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: <uG**************@TK2MSFTNGP12.phx.gbl>
| > > | Newsgroups: microsoft.public.dotnet.languages.vb
| > > | NNTP-Posting-Host: ip3e830773.speed.planet.nl 62.131.7.115
| > > | Path:
TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGP12.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?...b-8b8620d93761
| > > |
| > > | I hope this helps,
| > > |
| > > | Cor
| > > |
| > > |
| > > |
| > >
| >
| >
| >
|

Dec 19 '05 #13

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

12
by: Moosebumps | last post by:
So, after reading some messages about os.system, and looking at the popen stuff and trying it a bit, I still have not found a way to keep a command window open for several commands (on Windows...
1
by: Bernhard Sollfrank | last post by:
Hello, i have to start a batch process from a dll to transfer files created by this dll to remote ftp server. I created a batch file: ftp -n -v -i -s:D:\batch\ftpcmds.txt ftp-server and a...
5
by: Tyler Hudson | last post by:
After reading Erland Sommarskog's most enlightening articles on SQL Server 2000's error handling capabilities (http://www.sommarskog.se/error-handling-I.html and...
8
by: Sir_Scriptor | last post by:
Hi, I am trying to understand Batch files for logging Text. I have a general understanding, but I am little lost on some of the command strings I keep seeing. I went out and Bought Microsoft Windows...
2
by: Paul Reddin | last post by:
Hi, (V8.1 Fp2) Our application uses JDBC batch to execute mutiple insert statements and we saw a strange thing this morning. There were 4 SQL Insert statements in the batch, and we know the...
2
by: Sinex | last post by:
Hi, I am triggering a batch file from my C# code. There are no echo commands in the batch file...yet the console window/command prompt window pops up every time the batch file is triggerred. Is...
1
by: matthias | last post by:
I have to execute fairly large batch files (200-300MB) with several million INSERT commands. When I do that from pgsql using the \i switch, I get the echo INSERT 0 1 for every single insert...
13
by: MLH | last post by:
I have a batch file named GetConf.bat. It contains a line like this: ipconfig /all >c:\MyAppDir\IPdata.txt I believe I could run the line with something like ShellWait by Terry Kreft. Any...
9
by: Jeff Groves | last post by:
I'm writing a launcher that should do the following: 1. Activate a .bat file to set environmental variables. 2. Start 3 programs, using said environmental variables as arguments. However, I...
1
by: GOPALA | last post by:
Hi , How To Execute A Psql Commands Through Batch File(.bat). My Aim Is , Usting Batch File , Connect The Database And Then Execute The Psql Statements.
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.