Stored Procedures with SQLCommandBuilder

Discussion in 'ASP .Net' started by microsoft.private.windows.netserver.setup, Mar 6, 2004.

  1. I have a very strange thing occurring in the program. I have a dataset
    retrieved from a stored procedure that just select * from a table. I then
    try to use the SQlCommandBuilder on the dataset, and fails. I try the same
    select statement directly and not using a stored procedure and use
    SQLCommandBuilder, the program works. This is a ASP.net page, and I am
    stumped. I would like to use the stored procedure rather than controlling
    it in the ASP.net page. Can anyone help? Thanks.
    microsoft.private.windows.netserver.setup, Mar 6, 2004
    #1
    1. Advertising

  2. For a start, though not the answer you're currently looking for I would
    separate out all your database activity into a separate assembly.

    Unfortunately I've not used SQLCommandBuilder yet, so this is all I can
    currently contiribute.

    FWIW
    Colin

    "microsoft.private.windows.netserver.setup" <> wrote in
    message news:...
    > I have a very strange thing occurring in the program. I have a dataset
    > retrieved from a stored procedure that just select * from a table. I then
    > try to use the SQlCommandBuilder on the dataset, and fails. I try the

    same
    > select statement directly and not using a stored procedure and use
    > SQLCommandBuilder, the program works. This is a ASP.net page, and I am
    > stumped. I would like to use the stored procedure rather than controlling
    > it in the ASP.net page. Can anyone help? Thanks.
    >
    >
    Colin Basterfield, Mar 6, 2004
    #2
    1. Advertising

  3. microsoft.private.windows.netserver.setup

    John Amick Guest

    To add to Colin's comment, the Data Access Application Block released by
    MSFT is a nice set of pre-written
    functions for DB access, and a good place to start.

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/daab-rm.asp

    -John

    "Colin Basterfield" <> wrote in message
    news:...
    > For a start, though not the answer you're currently looking for I would
    > separate out all your database activity into a separate assembly.
    >
    > Unfortunately I've not used SQLCommandBuilder yet, so this is all I can
    > currently contiribute.
    >
    > FWIW
    > Colin
    >
    > "microsoft.private.windows.netserver.setup" <> wrote

    in
    > message news:...
    > > I have a very strange thing occurring in the program. I have a dataset
    > > retrieved from a stored procedure that just select * from a table. I

    then
    > > try to use the SQlCommandBuilder on the dataset, and fails. I try the

    > same
    > > select statement directly and not using a stored procedure and use
    > > SQLCommandBuilder, the program works. This is a ASP.net page, and I am
    > > stumped. I would like to use the stored procedure rather than

    controlling
    > > it in the ASP.net page. Can anyone help? Thanks.
    > >
    > >

    >
    >
    John Amick, Mar 6, 2004
    #3
  4. It has to be doable. I am sure it is just the TableMappings of the stored
    procedure. The result set from the stored procedure is sp_WellProperties,
    but the SqlCommandBuilder is asking for WellProperties which is the actual
    name of the table. Even when I use sqlAdaptor.TableMappings.Add("Table",
    "WellProperties") it still doesn't work. I am quite sure I have
    WellProperties incorrect.in the TableMappings Add method. Is there a way to
    get the actual name of the dataset that was returned by the stored
    procedure? Thanks.

    "John Amick" <> wrote in message
    news:...
    > To add to Colin's comment, the Data Access Application Block released by
    > MSFT is a nice set of pre-written
    > functions for DB access, and a good place to start.
    >
    >

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/daab-rm.asp
    >
    > -John
    >
    > "Colin Basterfield" <> wrote in message
    > news:...
    > > For a start, though not the answer you're currently looking for I would
    > > separate out all your database activity into a separate assembly.
    > >
    > > Unfortunately I've not used SQLCommandBuilder yet, so this is all I can
    > > currently contiribute.
    > >
    > > FWIW
    > > Colin
    > >
    > > "microsoft.private.windows.netserver.setup" <>

    wrote
    > in
    > > message news:...
    > > > I have a very strange thing occurring in the program. I have a dataset
    > > > retrieved from a stored procedure that just select * from a table. I

    > then
    > > > try to use the SQlCommandBuilder on the dataset, and fails. I try the

    > > same
    > > > select statement directly and not using a stored procedure and use
    > > > SQLCommandBuilder, the program works. This is a ASP.net page, and I

    am
    > > > stumped. I would like to use the stored procedure rather than

    > controlling
    > > > it in the ASP.net page. Can anyone help? Thanks.
    > > >
    > > >

    > >
    > >

    >
    >
    microsoft.private.windows.netserver.setup, Mar 6, 2004
    #4
  5. microsoft.private.windows.netserver.setup

    John Amick Guest

    Could you post your code? That might help someone come up with an answer
    for you.

    -John

    "microsoft.private.windows.netserver.setup" <> wrote in
    message news:...
    > It has to be doable. I am sure it is just the TableMappings of the stored
    > procedure. The result set from the stored procedure is sp_WellProperties,
    > but the SqlCommandBuilder is asking for WellProperties which is the actual
    > name of the table. Even when I use sqlAdaptor.TableMappings.Add("Table",
    > "WellProperties") it still doesn't work. I am quite sure I have
    > WellProperties incorrect.in the TableMappings Add method. Is there a way

    to
    > get the actual name of the dataset that was returned by the stored
    > procedure? Thanks.
    >
    > "John Amick" <> wrote in message
    > news:...
    > > To add to Colin's comment, the Data Access Application Block released by
    > > MSFT is a nice set of pre-written
    > > functions for DB access, and a good place to start.
    > >
    > >

    >

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/daab-rm.asp
    > >
    > > -John
    > >
    > > "Colin Basterfield" <> wrote in message
    > > news:...
    > > > For a start, though not the answer you're currently looking for I

    would
    > > > separate out all your database activity into a separate assembly.
    > > >
    > > > Unfortunately I've not used SQLCommandBuilder yet, so this is all I

    can
    > > > currently contiribute.
    > > >
    > > > FWIW
    > > > Colin
    > > >
    > > > "microsoft.private.windows.netserver.setup" <>

    > wrote
    > > in
    > > > message news:...
    > > > > I have a very strange thing occurring in the program. I have a

    dataset
    > > > > retrieved from a stored procedure that just select * from a table.

    I
    > > then
    > > > > try to use the SQlCommandBuilder on the dataset, and fails. I try

    the
    > > > same
    > > > > select statement directly and not using a stored procedure and use
    > > > > SQLCommandBuilder, the program works. This is a ASP.net page, and I

    > am
    > > > > stumped. I would like to use the stored procedure rather than

    > > controlling
    > > > > it in the ASP.net page. Can anyone help? Thanks.
    > > > >
    > > > >
    > > >
    > > >

    > >
    > >

    >
    >
    John Amick, Mar 6, 2004
    #5
  6. I have pasted the code from the original file. I am sure that it is because
    it is trying to write back to the sp_WellPropertiesDatasets stored
    procedure. Of course this can't be done, becuase the stored proc just
    retrieves the table. I then tried a table mapping, but I think I have the
    wrong original table name, thus in SqlCommandBuilder it still thinks it is
    coming from a table named sp_WellPropertiesDatasets. Any help greatly
    appreciated, thanks.

    Code;
    'loads each section of the WellProperties table
    Sub Load_UserForm()
    sqlSQLCommand = New SqlCommand( strSQL2, sqlConnect )
    sqlDataAdaptor = New SqlDataAdapter( sqlSQLCommand )

    'add the table mappings (parameters is name of new table name, and
    original table name)
    sqlDataAdaptor.TableMappings.Add( "Table", "WellProperties" )

    sqlDataAdaptor.Fill( sqlDataSet1, "Table" )
    'Response.Write(sqlDataSet1.DataSetName)

    'bind to the datagrid, the key is ID field
    WellProperties.Datasource() = sqlDataset1
    WellProperties.DataBind()
    End Sub

    'save the changes back to the database
    Sub SaveDatasets(s As Object, e as EventArgs)
    'change a piece of data
    'sqlDataset1.Tables("WellProperties").Rows(0)(2) = "Test4"
    'original value is Heavy

    'create the sqlcommandbuilder
    Dim cbWellProperties As New SqlCommandBuilder( sqlDataAdaptor )

    'display the update statement of the records that were changed
    strDebug.Text = cbWellProperties.GetUpdateCommand().CommandText

    'push the updates to the database
    sqlDataAdaptor.Update( sqlDataSet1 )

    'bind to the datagrid, the key is ID field
    WellProperties.Datasource() = sqlDataset1
    WellProperties.DataBind()
    End Sub

    "John Amick" <> wrote in message
    news:...
    > Could you post your code? That might help someone come up with an answer
    > for you.
    >
    > -John
    >
    > "microsoft.private.windows.netserver.setup" <> wrote

    in
    > message news:...
    > > It has to be doable. I am sure it is just the TableMappings of the

    stored
    > > procedure. The result set from the stored procedure is

    sp_WellProperties,
    > > but the SqlCommandBuilder is asking for WellProperties which is the

    actual
    > > name of the table. Even when I use

    sqlAdaptor.TableMappings.Add("Table",
    > > "WellProperties") it still doesn't work. I am quite sure I have
    > > WellProperties incorrect.in the TableMappings Add method. Is there a

    way
    > to
    > > get the actual name of the dataset that was returned by the stored
    > > procedure? Thanks.
    > >
    > > "John Amick" <> wrote in message
    > > news:...
    > > > To add to Colin's comment, the Data Access Application Block released

    by
    > > > MSFT is a nice set of pre-written
    > > > functions for DB access, and a good place to start.
    > > >
    > > >

    > >

    >

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/daab-rm.asp
    > > >
    > > > -John
    > > >
    > > > "Colin Basterfield" <> wrote in message
    > > > news:...
    > > > > For a start, though not the answer you're currently looking for I

    > would
    > > > > separate out all your database activity into a separate assembly.
    > > > >
    > > > > Unfortunately I've not used SQLCommandBuilder yet, so this is all I

    > can
    > > > > currently contiribute.
    > > > >
    > > > > FWIW
    > > > > Colin
    > > > >
    > > > > "microsoft.private.windows.netserver.setup" <>

    > > wrote
    > > > in
    > > > > message news:...
    > > > > > I have a very strange thing occurring in the program. I have a

    > dataset
    > > > > > retrieved from a stored procedure that just select * from a table.

    > I
    > > > then
    > > > > > try to use the SQlCommandBuilder on the dataset, and fails. I try

    > the
    > > > > same
    > > > > > select statement directly and not using a stored procedure and use
    > > > > > SQLCommandBuilder, the program works. This is a ASP.net page, and

    I
    > > am
    > > > > > stumped. I would like to use the stored procedure rather than
    > > > controlling
    > > > > > it in the ASP.net page. Can anyone help? Thanks.
    > > > > >
    > > > > >
    > > > >
    > > > >
    > > >
    > > >

    > >
    > >

    >
    >
    microsoft.private.windows.netserver.setup, Mar 6, 2004
    #6
    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. Taras
    Replies:
    2
    Views:
    4,797
    Rick Spiewak
    Oct 5, 2003
  2. Rico Alexander
    Replies:
    1
    Views:
    348
    zPaul
    Oct 23, 2003
  3. =?Utf-8?B?SmltIFJleW5vbGRz?=

    SqlCommandBuilder

    =?Utf-8?B?SmltIFJleW5vbGRz?=, May 12, 2005, in forum: ASP .Net
    Replies:
    4
    Views:
    2,647
    =?Utf-8?B?SmltIFJleW5vbGRz?=
    May 16, 2005
  4. microsoft.private.windows.netserver.setup

    Datagrid and SqlCommandBuilder

    microsoft.private.windows.netserver.setup, Mar 6, 2004, in forum: ASP .Net Datagrid Control
    Replies:
    0
    Views:
    118
    microsoft.private.windows.netserver.setup
    Mar 6, 2004
  5. microsoft.private.windows.netserver.setup

    Stored Procedures with SQLCommandBuilder

    microsoft.private.windows.netserver.setup, Mar 6, 2004, in forum: ASP .Net Datagrid Control
    Replies:
    0
    Views:
    126
    microsoft.private.windows.netserver.setup
    Mar 6, 2004
Loading...

Share This Page