Stored Query and DATASETs don't get along ?

Discussion in 'ASP .Net Datagrid Control' started by Lorenzo, Jul 6, 2005.

  1. Lorenzo

    Lorenzo Guest

    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
    Lorenzo, Jul 6, 2005
    #1
    1. Advertising

  2. 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:...
    > 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, Jul 12, 2005
    #2
    1. Advertising

  3. Lorenzo

    Lorenzo Guest

    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.


    "Fiaz Ali Saleemi" <> ha scritto nel messaggio
    news:%...
    > 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:...
    >> 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
    >>
    >>

    >
    >
    >
    Lorenzo, Jul 14, 2005
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. news.microsoft.com
    Replies:
    0
    Views:
    430
    news.microsoft.com
    Apr 12, 2006
  2. Francisco Garcia
    Replies:
    2
    Views:
    465
    Fran Garcia
    Apr 13, 2006
  3. Steven D'Aprano

    Doctests and decorated methods don't get along

    Steven D'Aprano, Feb 6, 2010, in forum: Python
    Replies:
    7
    Views:
    361
    Diez B. Roggisch
    Feb 9, 2010
  4. news.microsoft.com
    Replies:
    0
    Views:
    184
    news.microsoft.com
    Apr 12, 2006
  5. Francisco Garcia
    Replies:
    3
    Views:
    247
    vincent
    Apr 13, 2006
Loading...

Share This Page