have a problem adding data to db

[vbinuyasha]

when i try to add data to a access db i get this error
Update requires a valid InsertCommand when passed DataRow collection with new rows.

here is the code once again

Option Explicit On
Imports System.Data
Imports System.Data.OleDb
Public Class Form1
Inherits System.Windows.Forms.Form
' create a connection string
Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\Alex\My Documents\Visual Studio Projects\WindowsApplication3\Cars.mdb"
Dim myConnection As OleDbConnection = New OleDbConnection
Dim dbCon As ADODB.Connection
' create a new dataset
Dim ds As DataSet = New DataSet
' create a data adapter
Dim da As OleDbDataAdapter = New OleDbDataAdapter("Select * from CarInfo", myConnection)
Private m_ucContactFile As ContactFile
Private Sub ContactsForm_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
myConnection.ConnectionString = connString
' fill dataset
da.Fill(ds, "CarInfo")
' write dataset contents to an xml file by calling WriteXml method
' Attach DataSet to DataGrid
DataGrid1.DataSource = ds.DefaultViewManager
End Sub
#Region " Windows Form Designer generated code "
Public Sub New()
MyBase.New()
'This call is required by the Windows Form Designer.
InitializeComponent()
'Add any initialization after the InitializeComponent() call
End Sub
'Form overrides dispose to clean up the component list.
Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
If disposing Then
If Not (components Is Nothing) Then
components.Dispose()
End If
End If
MyBase.Dispose(disposing)
End Sub
'Required by the Windows Form Designer
Private components As System.ComponentModel.IContainer
'NOTE: The following procedure is required by the Windows Form Designer
'It can be modified using the Windows Form Designer.
'Do not modify it using the code editor.
Friend WithEvents txtmake As System.Windows.Forms.TextBox
Friend WithEvents txtmodel As System.Windows.Forms.TextBox
Friend WithEvents txtcolor As System.Windows.Forms.TextBox
Friend WithEvents txtyear As System.Windows.Forms.TextBox
Friend WithEvents btnAdd As System.Windows.Forms.Button
Friend WithEvents lblmake As System.Windows.Forms.Label
Friend WithEvents lblmodel As System.Windows.Forms.Label
Friend WithEvents lblcolor As System.Windows.Forms.Label
Friend WithEvents lblyear As System.Windows.Forms.Label
Friend WithEvents btnClear As System.Windows.Forms.Button
Friend WithEvents btnDelete As System.Windows.Forms.Button
Friend WithEvents DataGrid1 As System.Windows.Forms.DataGrid
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
Me.txtmake = New System.Windows.Forms.TextBox
Me.txtmodel = New System.Windows.Forms.TextBox
Me.txtcolor = New System.Windows.Forms.TextBox
Me.txtyear = New System.Windows.Forms.TextBox
Me.btnAdd = New System.Windows.Forms.Button
Me.lblmake = New System.Windows.Forms.Label
Me.lblmodel = New System.Windows.Forms.Label
Me.lblcolor = New System.Windows.Forms.Label
Me.lblyear = New System.Windows.Forms.Label
Me.btnClear = New System.Windows.Forms.Button
Me.btnDelete = New System.Windows.Forms.Button
Me.DataGrid1 = New System.Windows.Forms.DataGrid
CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).BeginInit()
Me.SuspendLayout()
'
'txtmake
'
Me.txtmake.Location = New System.Drawing.Point(448, 48)
Me.txtmake.Name = "txtmake"
Me.txtmake.Size = New System.Drawing.Size(152, 20)
Me.txtmake.TabIndex = 1
Me.txtmake.Text = ""
'
'txtmodel
'
Me.txtmodel.Location = New System.Drawing.Point(448, 96)
Me.txtmodel.Name = "txtmodel"
Me.txtmodel.Size = New System.Drawing.Size(152, 20)
Me.txtmodel.TabIndex = 2
Me.txtmodel.Text = ""
'
'txtcolor
'
Me.txtcolor.Location = New System.Drawing.Point(448, 144)
Me.txtcolor.Name = "txtcolor"
Me.txtcolor.Size = New System.Drawing.Size(152, 20)
Me.txtcolor.TabIndex = 3
Me.txtcolor.Text = ""
'
'txtyear
'
Me.txtyear.Location = New System.Drawing.Point(448, 192)
Me.txtyear.Name = "txtyear"
Me.txtyear.Size = New System.Drawing.Size(152, 20)
Me.txtyear.TabIndex = 4
Me.txtyear.Text = ""
'
'btnAdd
'
Me.btnAdd.Location = New System.Drawing.Point(448, 224)
Me.btnAdd.Name = "btnAdd"
Me.btnAdd.TabIndex = 5
Me.btnAdd.Text = "Add Car"
'
'lblmake
'
Me.lblmake.Location = New System.Drawing.Point(456, 24)
Me.lblmake.Name = "lblmake"
Me.lblmake.TabIndex = 6
Me.lblmake.Text = "Make"
'
'lblmodel
'
Me.lblmodel.Location = New System.Drawing.Point(456, 72)
Me.lblmodel.Name = "lblmodel"
Me.lblmodel.TabIndex = 7
Me.lblmodel.Text = "Model"
'
'lblcolor
'
Me.lblcolor.Location = New System.Drawing.Point(456, 120)
Me.lblcolor.Name = "lblcolor"
Me.lblcolor.TabIndex = 8
Me.lblcolor.Text = "Color"
'
'lblyear
'
Me.lblyear.Location = New System.Drawing.Point(456, 168)
Me.lblyear.Name = "lblyear"
Me.lblyear.TabIndex = 9
Me.lblyear.Text = "Year"
'
'btnClear
'
Me.btnClear.Location = New System.Drawing.Point(448, 256)
Me.btnClear.Name = "btnClear"
Me.btnClear.TabIndex = 10
Me.btnClear.Text = "Clear"
'
'btnDelete
'
Me.btnDelete.Location = New System.Drawing.Point(448, 288)
Me.btnDelete.Name = "btnDelete"
Me.btnDelete.TabIndex = 11
Me.btnDelete.Text = "Delete"
'
'DataGrid1
'
Me.DataGrid1.DataMember = ""
Me.DataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText
Me.DataGrid1.Location = New System.Drawing.Point(24, 40)
Me.DataGrid1.Name = "DataGrid1"
Me.DataGrid1.Size = New System.Drawing.Size(408, 368)
Me.DataGrid1.TabIndex = 12
'
'Form1
'
Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
Me.ClientSize = New System.Drawing.Size(712, 419)
Me.Controls.Add(Me.DataGrid1)
Me.Controls.Add(Me.btnDelete)
Me.Controls.Add(Me.btnClear)
Me.Controls.Add(Me.lblyear)
Me.Controls.Add(Me.lblcolor)
Me.Controls.Add(Me.lblmodel)
Me.Controls.Add(Me.lblmake)
Me.Controls.Add(Me.btnAdd)
Me.Controls.Add(Me.txtyear)
Me.Controls.Add(Me.txtcolor)
Me.Controls.Add(Me.txtmodel)
Me.Controls.Add(Me.txtmake)
Me.Name = "Form1"
Me.Text = "Form1"
CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).EndInit()
Me.ResumeLayout(False)
End Sub
#End Region
Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
Dim objRow As DataRow
'Create a new DataRow object for this table
objRow = ds.Tables("CarInfo").NewRow()
'Edit Each Field value
objRow.Item("Make") = txtmake.Text
objRow.Item("Model") = txtmodel.Text
objRow.Item("Color") = txtcolor.Text
objRow.Item("Year") = txtyear.Text
'Officially add the DataRow to our table
ds.Tables("CarInfo").Rows.Add(objRow)
da.Update(ds, "CarInfo")
ds.AcceptChanges()
End Sub
Private Sub btnClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClear.Click
txtmake.Clear()
txtmodel.Clear()
txtcolor.Clear()
txtyear.Clear()
End Sub
Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
End Sub
End Class


any ideas

 

[Schenz]

First, in answer to your question: the DataAdapter needs an InsertCommand Added to it so it knows how to Insert the row into your database. Read more about DataAdapters in the Help system.

Second, it is much more help ful if you only paste in the part of code you are having problems with. In this case it is:
PrivateSub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
Dim objRow As DataRow
'Create a new DataRow object for this table
objRow = ds.Tables("CarInfo").NewRow()
'Edit Each Field value
objRow.Item("Make") = txtmake.Text
objRow.Item("Model") = txtmodel.Text
objRow.Item("Color") = txtcolor.Text
objRow.Item("Year") = txtyear.Text
'Officially add the DataRow to our table
ds.Tables("CarInfo").Rows.Add(objRow)
da.Update(ds, "CarInfo")
ds.AcceptChanges()
EndSub

also please wrap it in the code tag:

Then it will come out like this:

[/COLOR][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] btnAdd_Click([/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] sender [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Object, [/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] e [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.EventArgs) [/SIZE][SIZE=2][COLOR=#0000ff]Handles[/COLOR][/SIZE][SIZE=2] btnAdd.Click
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] objRow [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] DataRow
[/SIZE][SIZE=2][COLOR=#008000]'Create a new DataRow object for this table
[/COLOR][/SIZE][SIZE=2]objRow = ds.Tables("CarInfo").NewRow()
[/SIZE][SIZE=2][COLOR=#008000]'Edit Each Field value
[/COLOR][/SIZE][SIZE=2]objRow.Item("Make") = txtmake.Text
objRow.Item("Model") = txtmodel.Text
objRow.Item("Color") = txtcolor.Text
objRow.Item("Year") = txtyear.Text
[/SIZE][SIZE=2][COLOR=#008000]'Officially add the DataRow to our table
[/COLOR][/SIZE][SIZE=2]ds.Tables("CarInfo").Rows.Add(objRow)
da.Update(ds, "CarInfo")
ds.AcceptChanges()
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub
[COLOR=Black]

That is much more readable than getting all the stuff that VS made automatically for you, that we do not even care about.

Also, I must ask this as I do every time I see Access, Why are you using Access? MSDE is free, SQL Express is free, mySQL is free. These are MUCH better databases, and more likely to be run into in a real programming job.

 

[vbinuyasha]

the only reason i am useing access is because it is a program i am just playing with and i got access for free from my parents lasy month

secondly what would that command look like?

 

[Schenz]

An InsertCommand is like a SELECT, but with INSERT syntax.

INSERT INTO {TableName} ({FieldList}) VALUES ({Values})