Field not a DataColumn or DataRelation

flashmanTom · January 30th, 2008, 09:55 AM

In chapter 16 of 'Beginning Visual Basic 2005', the try it out exercise has me adding a new record. However, when I run the program I get a 'Field not a DataColumn or DataRelation' exception. I've included the offending code and the exception below:

Private Sub btnAdd_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnAdd.Click
        ' Declare local variables and objects...
        Dim intPosition As Integer, intMaxID As Integer
        Dim strID As String
        Dim objCommand As SqlCommand = New SqlCommand()
        ' Set the DataView object to the DataSet object...
objDataView = New DataView(objDataSet.Tables("authors"))

        ' Save the current record position...
        intPosition = objCurrencyManager.Position
        ' Create a new SqlCommand object...
Dim maxIDCommand As SqlCommand = New SqlCommand _
            ("SELECT MAX(title_id) AS MaxID " & _
            "FROM titles WHERE title_id LIKE 'DM%'", objConnection)
        ' Open the connection, execute the command
        objConnection.Open()
        Dim maxID As Object = maxIDCommand.ExecuteScalar()

        ' If the MaxID column is null...
        If maxID Is DBNull.Value Then
            ' Set a default value of 1000...
            intMaxID = 1000
        Else
            ' otherwise set the strID variable to the value in MaxID...
            strID = CType(maxID, String)
            ' Get the integer part of the string...
            intMaxID = CType(strID.Remove(0, 2), Integer)
            ' Increment the value...
            intMaxID += 1
        End If

        ' Finally, set the new ID...
        strID = "DM" & intMaxID.ToString

        ' Set the Sql Command object properties...
objCommand.Connection = objConnection
        objCommand.CommandText = "INSERT INTO titles " & _
            "(title_id, title, type, price, pubdate) " & _
            "VALUES(@title_id,@title,@type,@price,@pubdate );" & _
            "INSERT INTO titleauthor (au_id, title_id) VALUES(@au_id,@title_id)"

        ' Add parameters for the placeholders in the SQL in the
        ' CommandText property...

        ' Parameter for the title_id column...
        objCommand.Parameters.AddWithValue("@title_id", strID)

        ' Parameter for the title column...
        objCommand.Parameters.AddWithValue("@title", txtBookTitle.Text)

        ' Parameter for the type column
        objCommand.Parameters.AddWithValue("@type", "Demo")
        ' Parameter for the price column...
        objCommand.Parameters.AddWithValue("@price", txtPrice.Text).DbType _
            = DbType.Currency

        ' Paramter for the pubdate column
        objCommand.Parameters.AddWithValue("@pubdate", Date.Now)

        ' Parameter for the au_id column...
objCommand.Parameters.AddWithValue _
            ("@au_id", BindingContext(objDataView).Current("au_id"))
        ' Execute the SqlCommand object to insert the new data...
        Try
            objCommand.ExecuteNonQuery()
        Catch SQlExceptionErr As SqlException
            MessageBox.Show(SQlExceptionErr.Message)
        End Try

        ' Close the connection...
        objConnection.Close()

        ' Fill the dataset and bind the fields...
        FillDataSetAndView()
        BindFields()

        ' Set the record position to the one that you saved...
        objCurrencyManager.Position = intPosition

        ' Show the current record position...
        ShowPosition()

        ' Display a message that the record was added...
        ToolStripStatusLabel1.Text = "Record added"
    End Sub

__________________________________________________ ______________

System.ArgumentException was unhandled
  Message="au_id is neither a DataColumn nor a DataRelation for table authors."
  Source="Microsoft.VisualBasic"
  StackTrace:
       at Microsoft.VisualBasic.CompilerServices.Symbols.Con tainer.InvokeMethod(Method TargetProcedure, Object[] Arguments, Boolean[] CopyBack, BindingFlags Flags)
       at Microsoft.VisualBasic.CompilerServices.NewLateBind ing.CallMethod(Container BaseReference, String MethodName, Object[] Arguments, String[] ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack, BindingFlags InvocationFlags, Boolean ReportErrors, ResolutionFailure& Failure)
       at Microsoft.VisualBasic.CompilerServices.NewLateBind ing.InternalLateIndexGet(Object Instance, Object[] Arguments, String[] ArgumentNames, Boolean ReportErrors, ResolutionFailure& Failure)
       at Microsoft.VisualBasic.CompilerServices.NewLateBind ing.LateIndexGet(Object Instance, Object[] Arguments, String[] ArgumentNames)
       at BindingExample.Form1.btnAdd_Click(Object sender, EventArgs e) in C:\Users\Tom Magaro\Documents\Visual Studio 2005\Projects\BindingExample\BindingExample\Form1. vb:line 240
       at System.Windows.Forms.Control.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnMouseUp(MouseEventAr gs mevent)
       at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
       at System.Windows.Forms.Control.WndProc(Message& m)
       at System.Windows.Forms.ButtonBase.WndProc(Message& m)
       at System.Windows.Forms.Button.WndProc(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.O nMessage(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.W ndProc(Message& m)
       at System.Windows.Forms.NativeWindow.DebuggableCallba ck(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
       at System.Windows.Forms.UnsafeNativeMethods.DispatchM essageW(MSG& msg)
       at System.Windows.Forms.Application.ComponentManager. System.Windows.Forms.UnsafeNativeMethods.IMsoCompo nentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
       at System.Windows.Forms.Application.ThreadContext.Run MessageLoopInner(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.ThreadContext.Run MessageLoop(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.Run(ApplicationCo ntext context)
       at Microsoft.VisualBasic.ApplicationServices.WindowsF ormsApplicationBase.OnRun()
       at Microsoft.VisualBasic.ApplicationServices.WindowsF ormsApplicationBase.DoApplicationModel()
       at Microsoft.VisualBasic.ApplicationServices.WindowsF ormsApplicationBase.Run(String[] commandLine)
       at BindingExample.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
       at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
       at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.Run UsersAssembly()
       at System.Threading.ThreadHelper.ThreadStart_Context( Object state)
       at System.Threading.ExecutionContext.Run(ExecutionCon text executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()

__________________________________________________ _________________

Any help on this would be greatly appreciated.

Thomas G Magaro