Help with DataAdapter Fill and Binding

[pwh777]

I need help in understanding the DataAdapter Fill method and how it
relates to the binding to controls on a form. I have a table called
tbl_CID_XRef on SQL Server. I have written as a test the simple code
below:

----------------------------------------------------------------

Private cob As SqlCommandBuilder
Private con As SqlConnection
Private cur As CurrencyManager
Private dap As SqlDataAdapter
Private dst As New DataSet

Try
con = New SqlConnection("workstation id=KC...")
dap = New SqlDataAdapter("SELECT CLIENT_ID, CLIENT FROM
tbl_CID_Xref", con)
cob = New SqlCommandBuilder(dap)
dap.Fill(dst, "tbl_CID_XRef")
'
' Bind the controls
'
txtClientID.DataBindings.Add("Text", dst, "CLIENT_ID")
txtClient.DataBindings.Add("Text", dst, "CLIENT")
cur = Me.BindingContext(dst, "tbl_CID_XRef")

Catch ex As Exception
MessageBox.Show("Error: " & ex.Message)
End Try

----------------------------------------------------------------

This code works just fine. Now if I take out the "tbl_CID_XRef" from
the Fill method, shown below, I get the error: "Cannot create a child
list for field tbl_CID_XRef."

dap.Fill(dst)

I have a vague understanding why. But the reason why I will want to do
this is when my SELECT statement is on a JOIN of two tables like the
following:

SELECT tbl_CID_XRef.CLIENT_ID,
tbl_CID_XRef.CLIENT,
tbl_CID_XREF_History.PlanType
FROM tbl_CID_XREF_History
INNER JOIN tbl_CID_XRef ON tbl_CID_XREF_History.CLIENT_ID =
tbl_CID_XRef.CLIENT_ID

I'm joining two tables (tbl_CID_XRef and tbl_CID_XRef_History).

How do I write the Fill method for something like this?

 

[Bart Mermuys]

Hi,

I need help in understanding the DataAdapter Fill method and how it
relates to the binding to controls on a form. I have a table called
tbl_CID_XRef on SQL Server. I have written as a test the simple code
below:

----------------------------------------------------------------

Private cob As SqlCommandBuilder
Private con As SqlConnection
Private cur As CurrencyManager
Private dap As SqlDataAdapter
Private dst As New DataSet

Try
con = New SqlConnection("workstation id=KC...")
dap = New SqlDataAdapter("SELECT CLIENT_ID, CLIENT FROM
tbl_CID_Xref", con)
cob = New SqlCommandBuilder(dap)
dap.Fill(dst, "tbl_CID_XRef")
'
' Bind the controls
'
txtClientID.DataBindings.Add("Text", dst, "CLIENT_ID")
txtClient.DataBindings.Add("Text", dst, "CLIENT")
cur = Me.BindingContext(dst, "tbl_CID_XRef")

Catch ex As Exception
MessageBox.Show("Error: " & ex.Message)
End Try

----------------------------------------------------------------

This code works just fine. Now if I take out the "tbl_CID_XRef" from
the Fill method, shown below, I get the error: "Cannot create a child
list for field tbl_CID_XRef."

dap.Fill(dst)

I have a vague understanding why. But the reason why I will want to do
this is when my SELECT statement is on a JOIN of two tables like the
following:

SELECT tbl_CID_XRef.CLIENT_ID,
tbl_CID_XRef.CLIENT,
tbl_CID_XREF_History.PlanType
FROM tbl_CID_XREF_History
INNER JOIN tbl_CID_XRef ON tbl_CID_XREF_History.CLIENT_ID =
tbl_CID_XRef.CLIENT_ID

I'm joining two tables (tbl_CID_XRef and tbl_CID_XRef_History).

How do I write the Fill method for something like this?

A DataSet contains DataTable's.

The name you use as the second parameter, is the name that will be used for
the DataTable that will be created or updated in the DataSet ( dst ). When
you bind you have to use the same TableName:
dap.Fill( dst, "abc" )
txtClientID.DataBindings.Add("Text", dst, "abc.CLIENT_ID")

Now, if you don't specify a TableName then the default is "Table", so you
need to bind to "Table":
dap.Fill( dst )
txtClientID.DataBindings.Add("Text", dst, "Table.CLIENT_ID")

It doesn't matter if you do a Join query or not, the name applies to the
created DataTable which is also the name you will have to use when you bind.
It doesn't need to relate to the table name(s) in the database.


HTH,
Greetings

 

[Paul]

Thanks Bart! That's the information I needed.