One SqlDataAdapter, One DataSet with multiple tables. possible?

[Wael]

Hi,

I have a data adapter and trying to connect it to multiple tables. I am
not sure if it is possible and if so, I can't find what I am doing
wrong. I am using HTML controls because the original page was created
with another software, but I changed all the controls to "Run as Server
Control". The loading works fine, but the updates don't. How can I
attach two SQL statements to one data adapter? Or do I have to use two
data adapters?


Option Explicit On
Option Strict On

Imports System.Data.SqlClient

Public Class _62031
Inherits System.Web.UI.Page


' Variables to hold local copies of the global data objects
Dim lv_PageDataAdapter As SqlDataAdapter
Dim lv_PageConnection As SqlConnection
Dim lv_PageDataSet As DataSet

Dim lv_TableName1 As String = "INCLUS"
Dim lv_TableName2 As String = "EXCLUS"
Dim lv_id1 As Integer
Dim lv_id2 As Integer
Dim Radio(1) As HtmlControls.HtmlInputRadioButton ' A holder for
radio buttons used to get indexes

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
' Make local copies of the data objects to make it easier to
manipulate
lv_id1 = CType(Session.Contents("gv_id1"), Integer)
lv_id2 = CType(Session.Contents("gv_id2"), Integer)
lv_PageConnection = CType(Session.Contents("gv_Connection"),
SqlConnection)
lv_PageDataAdapter = CType(Session.Contents("gv_DataAdapter"),
SqlDataAdapter)
lv_PageDataSet = CType(Session.Contents("gv_DataSet"), DataSet)

If Not Page.IsPostBack Then
' Make sure we have an open connection
If lv_PageConnection.State <> ConnectionState.Open Then
lv_PageConnection.Open()
lv_PageDataAdapter.SelectCommand = _
New SqlCommand("SELECT id1, id2, i1 FROM " & _
lv_TableName1 & " WHERE id1=" & lv_id1 & " AND
id2=" & lv_id2, _
lv_PageConnection)

lv_PageDataAdapter.Fill(lv_PageDataSet.Tables.Add(lv_TableName1))

ID1.Value =
lv_PageDataSet.Tables(lv_TableName1).Rows(0).Item("id1").ToString
ID2.Value =
lv_PageDataSet.Tables(lv_TableName1).Rows(0).Item("id2").ToString

i1.Value =
lv_PageDataSet.Tables(lv_TableName1).Rows(0).Item("i1").ToString)

' Exclusions
lv_TableName2 = "EXCLUS"
lv_PageDataAdapter.SelectCommand = _
New SqlCommand("SELECT id1, id2, e1 FROM " & _
lv_TableName2 & " WHERE id1=" & lv_id1 & " AND
id2=" & lv_id2, _
lv_PageConnection)

lv_PageDataAdapter.Fill(lv_PageDataSet.Tables.Add(lv_TableName2))

e1.Value =
lv_PageDataSet.Tables(lv_TableName1).Rows(0).Item("e1").ToString)
End If
End Sub

Private Sub Submit1_ServerClick(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles Submit1.ServerClick

lv_PageDataSet.Tables(lv_TableName1).Rows(0).Item("i1") = i1
lv_PageDataSet.Tables(lv_TableName1).Rows(0).Item("e1") = e1
Try
sbUpdateData(lv_PageDataSet)
lv_PageDataSet.AcceptChanges()

Catch ex As SqlException

End Try
End Sub

Private Sub sbUpdateData(ByVal pardv As DataSet)
Dim lv_CommandBuilder As New
SqlCommandBuilder(lv_PageDataAdapter)

If lv_PageConnection.State <> ConnectionState.Open Then
lv_PageConnection.Open()
lv_PageDataAdapter.UpdateCommand =
lv_CommandBuilder.GetUpdateCommand
lv_PageDataAdapter.Update(pardv.Tables(lv_TableName1))
End Sub
End Class

 

[Cor Ligthert [MVP]]

Wael,

You can reset everytime everything in a dataadapter, however it does not
give you any advance, therefore if you are able to that, than is using two
much easier.

I hope this helps,

Cor

 

[Wael]

Hi Cor,

Thanks for your reply. I am not sure what you meant by reset.

I like the idea of having one object of each type because I am storing
those objects (connection, adapter, dataset, etc) in the global.asax
and make references to them when I refresh my asp page. If I can have
all the tables in one dataset and I take care of adding and removing
them from the page I think would be better than having 2, 3 or 4 data
adapters and datasets in the global.asax when I am not going to use
them. Of course it all depends on how complex it is to get my original
idea to work. I still don't know whether it is doable or not.

If it is not possible, what is the purpose of having the ability to
have more than one table in a dataset? The loading part of my asp page
works. It is the update that is giving me a hard time.

 

[Cor Ligthert [MVP]]

Wael,

You can use one dataset and more dataadapters, that is no problem at all.

I don't know if you use the desiger, however I start to assume that. In that
case than change the dataset in the adapterwizard everytime to the same
dataset.

(A problem is that there is in not any version of Net a command
'datatable.haschanges' and because of that this concept of using one dataset
is in my opinion hard to use because it is a kind of updating in the blind).

I hope this helps,

Cor

 

[Wael]

This improves things a little bit, but I still have to keep a few
dataadapters in my global.asax that I may or may not use.

I do everything programmatically.

 

[Cor Ligthert [MVP]]

Wael,

If you use the commandbuilder, than you can probably add everytime the right
Selectstring to the selectcommand.commandtext of the dataadapter and than
use that commandbuilder to generate the right syntaxes for the insert,
update and delete (I do this in another way however in my opinion should
this go as well).

I hope this helps,

Cor

 

[Wael]

Can you please give me some code as an example. Simple code just to get
an understanding because I find ado.net is not something that I can
figure out easily.

 

[Cor Ligthert [MVP]]

Wael,

Be aware that this is only the reading, however when you see this and my
previous message than it should be easy to do this.

http://www.windowsformsdatagridhelp.com/default.aspx?ID=3eafa9ea-a906-45ff-aece-e8335682ad3a

I hope this helps,

Cor

 

[Wael]

Dim cb1 As New SqlCommandBuilder(da1)
Dim cb2 As New SqlCommandBuilder(da2)

IF ds.HasChanges then
UpdateData(ds)
ds.AcceptChanges
End If

private sub UpdateData(ds as DataSet)
da1.UpdateCommand = cb1.GetUpdateCommand
da1.Update(ds.Tables(TableName1))

da2.UpdateCommand = cb2.GetUpdateCommand
da2.Update(ds.Tables(TableName2))
End Sub

The first one works, the second one failes at da2.GetUpdateCommand. The
first one updates the table successfully. Any ideas?

 

[Wael]

I forgot to mention that I get an error: The DataAdapter.SelectCommand
property needs to be initialized.

 

[Cor Ligthert [MVP]]

Wael,

Can you try this one roughly typed here

\\\
dim cmb as sqlcommandbuilder
dim da as new sqldataadapter(Select * from TableName1, connstring)
'right sqlstring of course
if ds.HasChanges then
cmb = sqlcommandbuilder(da)
da.Update(ds.Tables(TableName1))

da.Selectcommand.commandtext = "Select * TableName2"
cmb = sqlcommandbuilder(da)
da.Update(ds.Tables(TableName2))
etc.
End if
etc.

I told you I never did it this way, however would not know why this would
not work.

I hope this helps and reply than please.

Cor

 

[Wael]

This is exactly how i did it. I found the problem by accident. I placed
the code that creates a local copy of one of the dataadapters in the
global.asax before the Page.IsPostBack and the other one inside by
mistake, the one outside worked, the one inside didn't. Now everything
is fine. Thank you very much for your help. If you come across any
solution to use just one DataAdapter, please let me know.

Thanks again
Wael

 

[Wael]

This is exactly how i did it. I found the problem by accident. I placed
the code that creates a local copy of one of the dataadapters in the
global.asax before the Page.IsPostBack and the other one inside by
mistake, the one outside worked, the one inside didn't. Now everything
is fine. Thank you very much for your help. If you come across any
solution to use just one DataAdapter, please let me know.

Thanks again
Wael