Re: Cannot define the DataMember in code

From: Douglas Buchanan (dbuchanan52_at_hotmail.com)
Date: 10/27/04 

Date: 26 Oct 2004 20:00:55 -0700

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

Mike Edenfield <kutulu@not.kutulu.not.org> wrote in message news:<#NMN7qruEHA.2016@TK2MSFTNGP15.phx.gbl>...
> Douglas Buchanan wrote:
> > 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