Cannot define the DataMember in code

[Douglas Buchanan]

I Cannot define the DataMember in code.

This is referencing the author table from the Pubs database in SQLS2k.


== Code 1 ~ Selected pertinent code ==

Private cnPubs As New SqlConnection
Private WithEvents cmSelectAuthor As New SqlCommand
Private WithEvents daAuthor As New SqlDataAdapter
Private WithEvents dsAuthor As New DataSet

---

daAuthor.SelectCommand = cmSelectAuthor

With daAuthor.SelectCommand
.CommandText = "Select * from authors"
.Connection = cnPubs
With .Parameters
.Add("@au_ID", SqlDbType.Char, 11, "au_ID")
.Add("@au_lname", SqlDbType.VarChar, 40, "au_lname")
.Add("@au_fname", SqlDbType.VarChar, 20, "au_fname")
.Add("@phone", SqlDbType.Char, 12, "phone")
.Add("@address", SqlDbType.VarChar, 40, "address")
.Add("@city", SqlDbType.VarChar, 20, "city")
.Add("@state", SqlDbType.Char, 2, "state")
.Add("@zip", SqlDbType.Char, 5, "zip")
.Add("@contract", SqlDbType.Bit, 1, "contract")
End With
End With

== Code 2 ~ From the Fill button ==

Me.DataGrid1.DataSource = dsAuthor
Me.DataGrid1.DataMember = "authors" << Error occurs here

== Error ==
System.ArgumentException: Cannot create a child list for field authors.


What am I missing? Why does it say anything about a child list?

 

[Justin Smith]

Reference the member as dsAuthor.Tables("authors")

 

[Douglas Buchanan]

It does not like that. It gives me the error

System.NullReferenceException: Object reference not set to an instance
of an object.

It refers directly to the line:
Me.DataGrid1.DataMember = dsAuthor.Tables("authors").ToString

(I am using Option Strict)

 

[james]

Have you tried:

dsAuthor = New Dataset("author")
daAuthor.Fill(dsAuthor, "author")
Datagrid1.SetDataBinding = (dsAuthor, "authors")

I know this works with Access, but, I am not sure about SQL.
Hope this helps.
james

 

[Justin Smith]

this will work: daAuthor.Fill(dsAuthor, "authors") because it creates a
named datatable.

you can also call

daAuthor.Fill(dsAuthor)
Me.DataGrid1.DataMember = dsAuthor.Tables(0).ToString

 

[Mike Edenfield]

I Cannot define the DataMember in code.

This is referencing the author table from the Pubs database in SQLS2k.


With daAuthor.SelectCommand
.CommandText = "Select * from authors"
.Connection = cnPubs [snip]
Me.DataGrid1.DataSource = dsAuthor
Me.DataGrid1.DataMember = "authors" << Error occurs here

You're missing the key line of code here: the Fill() method call.
Unless you specifically tell the data adapter to make a table called
"authors" when you run Fill(), as in:

daAuthor.Fill(dsAuthor, "authors");

then you will have no 'authors' table in your dataset. (The error
references a "child list" due to the way data binding works -- it's
actually binding to an IList interface on the data table.) By default,
the data adapter will auto-assign names to the results of it's
SelectCommand named "Table", "Table1", "Table2", etc. If you specify a
table name on the call to Fill(), they will instead be named "authors",
"authors1", etc. This is what you want, since you only have one table
and you want it to be called "authors".

Make sure that you are: 1. filling your dataset first, and 2. providing
the table name parameter to the Fill() method, before trying to bind the
grid. You could also remove the hard-coded table name and set:

DataMember = dsAuthor.Tables(0).TableName;

but I typically use the explicit table name method.

By the way, since you're setting both at once, the grid's
SetDataBinding() method is quicker.

--Mike

 

[Douglas Buchanan]

Mike,

Thank you for your suggestions. I have tried them with no success.

Below is my entire code in context (less designer generated code).
Below is also the error messages from my catch statement.

=== My code ================================
Imports System.Data.SqlClient

Public Class Form1
Inherits System.Windows.Forms.Form

Private cnPubs As New SqlConnection

Private WithEvents cmSelectAuthor As New SqlCommand
Private WithEvents cmInsertAuthor As New SqlCommand
Private WithEvents cmUpdateAuthor As New SqlCommand
Private WithEvents cmDeleteAuthor As New SqlCommand

Private WithEvents daAuthor As New SqlDataAdapter
Private WithEvents dsAuthor As New DataSet

'''' Designer generated code removed from here '''''''''''''''''

Private Sub Form1_Load(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles MyBase.Load

'Connection string

cnPubs.ConnectionString = "workstation id=SEDNA;packet
size=4096;integrated security=SSPI;data source=.;persist security
info=False;initial catalog=pubs"

'Define the DataAdapter Commands

With daAuthor
.SelectCommand = cmSelectAuthor
.InsertCommand = cmInsertAuthor
.UpdateCommand = cmUpdateAuthor
.DeleteCommand = cmDeleteAuthor
End With

'Select Command

With daAuthor.SelectCommand
.CommandText = "Select * from authors"
.Connection = cnPubs
With .Parameters
.Add("@au_ID", SqlDbType.Char, 11, "au_ID")
.Add("@au_lname", SqlDbType.VarChar, 40, "au_lname")
.Add("@au_fname", SqlDbType.VarChar, 20, "au_fname")
.Add("@phone", SqlDbType.Char, 12, "phone")
.Add("@address", SqlDbType.VarChar, 40, "address")
.Add("@city", SqlDbType.VarChar, 20, "city")
.Add("@state", SqlDbType.Char, 2, "state")
.Add("@zip", SqlDbType.Char, 5, "zip")
.Add("@contract", SqlDbType.Bit, 1, "contract")
End With
End With

'Insert Command

With daAuthor.InsertCommand
.CommandText = "INSERT INTO authors (au_id, au_lname,
au_fname, phone, address, city, state, zip, contract) VALUES (@au_id,
@au_lname, @au_fname, @phone, @address, @city, @state, @zip,
@contract)"
.Connection = cnPubs
With .Parameters
.Add("@au_ID", SqlDbType.Char, 11, "au_ID")
.Add("@au_lname", SqlDbType.VarChar, 40, "au_lname")
.Add("@au_fname", SqlDbType.VarChar, 20, "au_fname")
.Add("@phone", SqlDbType.Char, 12, "phone")
.Add("@address", SqlDbType.VarChar, 40, "address")
.Add("@city", SqlDbType.VarChar, 20, "city")
.Add("@state", SqlDbType.Char, 2, "state")
.Add("@zip", SqlDbType.Char, 5, "zip")
.Add("@contract", SqlDbType.Bit, 1, "contract")
End With
End With

'Update Command

With daAuthor.UpdateCommand
.CommandText = "UPDATE authors SET au_lname = @au_lname,
au_fname = @au_fname, phone= @phone, address = @address, city =
@city, state = @state, zip = @zip, contract = @contract WHERE au_id =
@au_id"
.Connection = cnPubs
With .Parameters
.Add("@au_ID", SqlDbType.Char, 11, "au_ID")
.Add("@au_lname", SqlDbType.VarChar, 40, "au_lname")
.Add("@au_fname", SqlDbType.VarChar, 20, "au_fname")
.Add("@phone", SqlDbType.Char, 12, "phone")
.Add("@address", SqlDbType.VarChar, 40, "address")
.Add("@city", SqlDbType.VarChar, 20, "city")
.Add("@state", SqlDbType.Char, 2, "state")
.Add("@zip", SqlDbType.Char, 5, "zip")
.Add("@contract", SqlDbType.Bit, 1, "contract")
End With
End With

'Delete Command

With daAuthor.DeleteCommand
.CommandText = "DELETE FROM authors WHERE au_id = @au_id"
.Connection = cnPubs
With .Parameters
.Add("@au_id", SqlDbType.Char, 11, "au_id")
End With
End With

End Sub

Private Sub Button1_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button1.Click

Try

cnPubs.Open()
Me.daAuthor.Fill(dsAuthor, "authors")
cnPubs.Close()

'Me.DataGrid1.SetDataBinding(dsAuthor, "authors")
Me.DataGrid1.DataSource = dsAuthor
Me.DataGrid1.DataMember = dsAuthor.Tables(0).TableName

Catch ex As Exception
MessageBox.Show(ex.ToString)
MessageBox.Show(ex.Source)

End Try

End Sub

Private Sub Button2_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button2.Click

Try
daAuthor.Update(dsAuthor.Tables("authors"))

Catch ex As Exception
MessageBox.Show(ex.Message)
MessageBox.Show(ex.ToString)

End Try
End Sub

End Class

=== ex.toString ============================

System.Data.SqlClient.SqlException: Prepared statement '(@au_ID
char(11),@au_lname varchar(40),@au_fname
varchar(20),@ph' expects parameter @au_ID, which was not supplied.
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean
returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
behavior)
at System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior
behavior)
at System.Data.Common.DbDataAdapter.FillFromCommand(Object data,
Int32 startRecord, Int32 maxRecords, String
srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
startRecord, Int32 maxRecords, String srcTable,
IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String
srcTable)
at _2405_SqlDataAdapterObject.Form1.Button1_Click(Object sender,
EventArgs e) in C:\Documents and
Settings\dbuchanan\My Documents\Visual Studio
Projects\2405_SqlDataAdapterObject\Form1.vb:line 199

=== ex.Source ============================

..Net SqlClient Data Provider

==========================================
This code does not work. Where is the problem?

What does this statement (from the error message) mean?
@ph' expects parameter @au_ID, which was not supplied.

Doug

I Cannot define the DataMember in code.

This is referencing the author table from the Pubs database in SQLS2k.


With daAuthor.SelectCommand
.CommandText = "Select * from authors"
.Connection = cnPubs [snip]
Me.DataGrid1.DataSource = dsAuthor
Me.DataGrid1.DataMember = "authors" << Error occurs here

You're missing the key line of code here: the Fill() method call.
Unless you specifically tell the data adapter to make a table called
"authors" when you run Fill(), as in:

daAuthor.Fill(dsAuthor, "authors");

then you will have no 'authors' table in your dataset. (The error
references a "child list" due to the way data binding works -- it's
actually binding to an IList interface on the data table.) By default,
the data adapter will auto-assign names to the results of it's
SelectCommand named "Table", "Table1", "Table2", etc. If you specify a
table name on the call to Fill(), they will instead be named "authors",
"authors1", etc. This is what you want, since you only have one table
and you want it to be called "authors".

Make sure that you are: 1. filling your dataset first, and 2. providing
the table name parameter to the Fill() method, before trying to bind the
grid. You could also remove the hard-coded table name and set:

DataMember = dsAuthor.Tables(0).TableName;

but I typically use the explicit table name method.

By the way, since you're setting both at once, the grid's
SetDataBinding() method is quicker.

--Mike

 

[Mike Edenfield]

Mike,

Thank you for your suggestions. I have tried them with no success.

Below is my entire code in context (less designer generated code).
Below is also the error messages from my catch statement.

With daAuthor.SelectCommand
.CommandText = "Select * from authors"
.Connection = cnPubs
With .Parameters
.Add("@au_ID", SqlDbType.Char, 11, "au_ID")
.Add("@au_lname", SqlDbType.VarChar, 40, "au_lname")
.Add("@au_fname", SqlDbType.VarChar, 20, "au_fname")
.Add("@phone", SqlDbType.Char, 12, "phone")
.Add("@address", SqlDbType.VarChar, 40, "address")
.Add("@city", SqlDbType.VarChar, 20, "city")
.Add("@state", SqlDbType.Char, 2, "state")
.Add("@zip", SqlDbType.Char, 5, "zip")
.Add("@contract", SqlDbType.Bit, 1, "contract")
End With
End With

This part is the problem. Your SelectCommand does not have parameters.
You are adding paramters that aren't going to be filled in when the
SQL runs, so the server won't know what to do. Since you don't have any
parameters in your CommandText (it's not a stored proc and has no
parameter placeholders) don't add any to it's Parameters collection.

--Mike

 

[Douglas Buchanan]

Mike,

Thank you for your helpful answer! It worked.

--Doug