C#: characters found after end of sql statement

.NET Framework Data Access and Storage

Candela

Hello,

I need to fill multiple tables into a single dataset, so I wrote the following:

//1)

cmd = new OleDbCommand("SELECT * FROM TableOne; SELECT * FROM TableTwo", myConnection);

//2)

dataAdapter = new OleDbDataAdapter(cmd);

//3)

dataAdapter.SelectCommand = cmd;

//4)

dataAdapter.Fill(ds);

The error stops at the the Fill on line 4.

OleDbException was unhandled "Characters found after end of SQL statement".

I hope you can show me where is my problem


Top

Re: .NET Framework Data Access and Storage C#: characters found after end of sql statement

Cristian_t

As far as I know this works only with System.Data.SqlClient.SqlCommand objects. You could change the statement to something like SELECT Table1.*, Table2.* FROM Table1, Table2 but you end up with a cross join - which means that Fill would create only one table in the dataset.

With the SqlClient provider the code might look like this:

Visual Basic

Dim connectionString As String = "Data Source=.\SQLEXPRESS; Integrated Security=SSPI; Initial Catalog=NorthwindCS;"

Dim ds As New DataSet("Northwind")

Using adapter As New SqlDataAdapter("SELECT * FROM Products; SELECT * FROM Categories", connectionString)

adapter.TableMappings.Add("Table", "Products")

adapter.TableMappings.Add("Table1", "Categories")

adapter.Fill(ds)

End Using

Notice the use of TableMappings. Without adding them the Dataset would contain two tables with names like Table and Table1.





Top

Re: .NET Framework Data Access and Storage C#: characters found after end of sql statement

Hayder Marzouk

Hi,

I think your running multiple queries on a access database.

Access database does not support multiple queries. It's not related to the ADO .Net provider but to the database engine it self. You can try by queriing SQL Server via ODBC, OLEDB Or native SQL connections and you'll see that multiple queries work.

I think, the best way is to be simple by using two commands :

Code Snippet

OleDbDataAdapter dataAdapter1 = new OleDbDataAdapter ("Select * from tableOne", myConnection);

OleDbDataAdapter dataAdapter2 = new OleDbDataAdapter ("Select * from tableTwo", myConnection);

dataAdapter1.Fill(ds, "TableOne") ; // Fills the table TableOne of the dataset

dataAdapter2.Fill(ds, "TableTwo") ; // Fills the table TableTwo of the dataset

myConnection.Close();

HTH.

Hayder Marzouk


Top

Re: .NET Framework Data Access and Storage C#: characters found after end of sql statement

slimshim

Sorry to jump in the middle here. your response helped me with a similar problem .I do have a quistion with this approach.

If you later want to push changes on all the tables in the dataset can it be done with one adapter.update or you need multiple adapters to push chages

thanx in advance

slimshim


Top

Re: .NET Framework Data Access and Storage C#: characters found after end of sql statement

Baba urf Sivaji

Dim con As OleDbConnection

Dim ad As OleDbDataAdapter, ds As DataSet

Try

con = New OleDbConnection

con.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=SRIDB;Data Source=xyz"

con.Open()

MsgBox(con.ConnectionString)

ad = New OleDbDataAdapter

ad.SelectCommand = New OleDbCommand("select * from emp; select * from emp_rel", con)

ds = New DataSet

ad.Fill(ds)

DataGrid1.DataSource = ds

Catch ex As Exception

MsgBox(ex.Message)

con.Close()

End Try

100% it works

just give me reply that it worked or not

Baba


Top

Re: .NET Framework Data Access and Storage C#: characters found after end of sql statement

VMazur

I believe it is related to the underlying provider, which is OLEDB provider. If provider supports execution of the multiple statements, then it will work. .NET Managed Oledb Provider is just a wrapper to expose interface for different types of OLEDB providers.

As I know only SQL Server Managed provider and OLEDB Provider for SQL Server supports multiple SQL statements execution.





Top