problem adding new record to access database table

[steve]

Hi All

I am having trouble adding a new record to an access database
Error is 'Syntax error in INSERT INTO Statement'

Updates are OK

Here is the code

Any suggestions please. I am new to vb.net from VB6

regards
Steve


Dim path As String, myimage As Bitmap, datapath As String

Dim cn As New OleDbConnection

Dim da As New OleDbDataAdapter

Dim ds As New DataSet, x As Long, strSQl As String

Dim dt As New DataTable, photopath As String

Dim cmdbuilder As OleDbCommandBuilder

Dim cmd As OleDbCommand

If cbmembers.Text = "" Then

Exit Sub

End If

Try

path = System.Reflection.Assembly.GetExecutingAssembly.Location

datapath = System.IO.Path.GetDirectoryName(path) & "\data\u2.mdb"

cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
datapath & ";Persist Security Info=False;"

cn.Open()

da = New OleDbDataAdapter("Select * from [members] where [number] = " &
cbmembers.SelectedValue, cn)

cmdbuilder = New OleDbCommandBuilder(da)

da.Fill(ds, "Members")

Dim dsRow As DataRow

If flgnew Then

dsRow = ds.Tables("members").NewRow()

Else

dsRow = ds.Tables("members").Rows(0)

End If

dsRow.Item("surname") = txtsurname.Text

dsRow.Item("name") = txtname.Text

dsRow.Item("street") = txtstreet.Text

dsRow.Item("suburb") = txtsuburb.Text

If txtcardno.Text <> "" Then

dsRow.Item("cardnumber") = CLng(txtcardno.Text)

End If

If txtdatejoined.Text <> "" Then

dsRow.Item("date") = CDate(txtdatejoined.Text)

Else

dsRow.Item("date") = DBNull.Value

End If

If txtdob.Text <> "" Then

dsRow.Item("dob") = CDate(txtdob.Text)

Else

dsRow.Item("dob") = DBNull.Value

End If

dsRow.Item("valid") = cbvalid.Checked

If flgnew = True Then

ds.Tables("members").Rows.Add(dsRow)

da.InsertCommand = cmdbuilder.GetInsertCommand

Else

da.UpdateCommand = cmdbuilder.GetUpdateCommand

End If

da.Update(ds, "Members")

ds.Tables("members").AcceptChanges()

flgnew = False

MsgBox("Member Details Saved OK")

Catch objException As Exception

MsgBox(objException.Message)



Finally

da = Nothing

ds = Nothing

cn = Nothing

End Try

 

[Cor Ligthert]

Steve,

Where have you placed this code, is there something of a sub or a function.

Definitly are you using and showing to much code.

I see a bitmap and more thinks like that, which are unused. What are you
expecting from us how to help you when you give us inferior information?

However basicly is the fault probably that you are trying to add selective
the commands to the dataadapter. While a command like this set directly
after by instance the fill, will do probably everything for you.

dim cmb as new oledbcommandbuilder(da)

I hope this helps anyhow

Cor

 

[Rogas69]

yeah, but what is the insert command?
peter