Stored Query and DATASETs don't get along ?

[Lorenzo]

Hello there,
in my training in data access I am getting lost with somenthing maybe pretty
easy but I don't seem to find a way out.

I would like to bind some data that I get form a simple SELECT stored query
in access called sp_Leggi that looks like this

SELECT TOP 10 * FROM [Clienti];

All works well when I perfom the databind to a data reader with the
following:

Sub BindData()
'1. Create a connection
Dim myConnection as New
OleDbConnection(ConfigurationSettings.AppSettings("connectionString"))

'2. Create the command object, passing in the SQL string
Const strSQL as String = "sp_Leggi"
Dim myCommand as New OleDbCommand(strSQL, myConnection)
myCommand.CommandType = CommandType.StoredProcedure

'Set the datagrid's datasource to the datareader and databind
myConnection.Open()
DataGrid1.DataSource =
myCommand.ExecuteReader(CommandBehavior.CloseConnection)
DataGrid1.DataBind()
End Sub


IT DOES NOT WORK when I do the same binding through a dataset !!! I do the
follwoing:

Sub BindData()
'1. Create a connection
Dim myConnection as New
OleDbConnection(ConfigurationSettings.AppSettings("connectionString"))

Dim myDataSet as New DataSet()

Const strSQL as String = "sp_Leggi"

Dim myAdapter as New OleDbDataAdapter(strSQL, myConnection)

myAdapter.fill(myDataSet)

DataGrid1.DataSource = myDataSet
DataGrid1.DataBind()

End Sub

It is interesting that it works fine when I use the same code with a real
STORED PROC with SQL server no errors at all.

The error I get is:
[OleDbException (0x80040e14)not valid SQL



Any help greatly appreciated
Lorenzo

 

[Fiaz Ali Saleemi]

Hi Lorenzo

I think in datasource properties of controls it didn't accept Datareader
although DataSet, DataTable, DataView, Hashtable can be used.

Regards
Fiaz Ali Saleemi

"Lorenzo"
<CuTT-!thISoFfonlyLeAveMyNamelorenzoWithNoNuMBErS98776@LeaveMyCountry!!!ITAL
IANlodging.it.NOSPAM> wrote in message
news:[email protected]...

 

[Lorenzo]

Many thanks Ali for your reply. I will proceed my training if case access
using DataSets then.
I am still curios though maybe would be nice to implement some StoredQuery
support in the OleDbCommand class don't you think.

Maybe not since sqlexpress might take over any access support what a shame
!!!
What is your opinion?

L.

Hi Lorenzo

I think in datasource properties of controls it didn't accept Datareader
although DataSet, DataTable, DataView, Hashtable can be used.

Regards
Fiaz Ali Saleemi

"Lorenzo"
<CuTT-!thISoFfonlyLeAveMyNamelorenzoWithNoNuMBErS98776@LeaveMyCountry!!!ITAL
IANlodging.it.NOSPAM> wrote in message

Hello there,
in my training in data access I am getting lost with somenthing maybe pretty
easy but I don't seem to find a way out.

I would like to bind some data that I get form a simple SELECT stored query
in access called sp_Leggi that looks like this

SELECT TOP 10 * FROM [Clienti];

All works well when I perfom the databind to a data reader with the
following:

Sub BindData()
'1. Create a connection
Dim myConnection as New
OleDbConnection(ConfigurationSettings.AppSettings("connectionString"))

'2. Create the command object, passing in the SQL string
Const strSQL as String = "sp_Leggi"
Dim myCommand as New OleDbCommand(strSQL, myConnection)
myCommand.CommandType = CommandType.StoredProcedure

'Set the datagrid's datasource to the datareader and databind
myConnection.Open()
DataGrid1.DataSource =
myCommand.ExecuteReader(CommandBehavior.CloseConnection)
DataGrid1.DataBind()
End Sub


IT DOES NOT WORK when I do the same binding through a dataset !!! I do
the
follwoing:

Sub BindData()
'1. Create a connection
Dim myConnection as New
OleDbConnection(ConfigurationSettings.AppSettings("connectionString"))

Dim myDataSet as New DataSet()

Const strSQL as String = "sp_Leggi"

Dim myAdapter as New OleDbDataAdapter(strSQL, myConnection)

myAdapter.fill(myDataSet)

DataGrid1.DataSource = myDataSet
DataGrid1.DataBind()

End Sub

It is interesting that it works fine when I use the same code with a real
STORED PROC with SQL server no errors at all.

The error I get is:
[OleDbException (0x80040e14)not valid SQL



Any help greatly appreciated
Lorenzo