INSERT data from Form to database

[JaneB19]

Hi,

This is probably a really silyy question, but I just can't figure out why this isn't working! Maybe casting a fresh pair of eyes across it might help?!

What I'm trying to do is take the data, entered into a form, for there and pass it into a table in my Access 2000 database.

I thought that I had the coding correct, but it appears not (as it's not inserting ANY of the data).

Coding goes as follows

'Create the connection string to the database
         Dim conString As String = ("Provider=Microsoft.Jet.OLEDB.4.0; data source=" & server.mappath("LLDB.mdb"))

         'Set connection to the connection string
         Dim oleConnection As New OleDBConnection(conString)

         'open connection
         oleConnection.Open()

         'set OleDbCommand
         Dim oleCommand = New OleDbCommand("INSERT [Area], [Client], [Division], [Reporter], [Country], [Well], [Task], [Event], [Lesson] INTO Testing VALUES ('" & dataArea & "', '" & dataClient & "', '" & dataDivision & "', '" & dataReporter & "', '" & dataCountry & "', '" & dataWell & "', '" & dataTask & "', '" & dataEvent & "', '" & dataLesson & "')")


         oleCommand.Connection = oleConnection

         'close connection to database
         oleConnection.Close()

I've also tried the INSERT VALUES(data1, data2) INTO [tablename](column1, column2) but I didn't get that working either!?!

Any help is greatly appreciated

Thanks
Jane

 

[DaZZleD]

the correct SQL syntax is

INSERT INTO tableName(field1, field2, field3...) VALUES (valField1, valField2, valField3...)

--------------------------
"two wrongs don't make a right, but three lefts do" - the unknown sage

 

[JaneB19]

Thanks DaZZleD!

I managed to get something working not long after I posted the thread! Typical!!! :-S

However, it doesn't appear to be reading the values of some of my strings properly!

 Dim dataArea As String
         Dim dataWell As String
         Dim dataTask As String
         Dim dataInst As String
         Dim dataDate As String
         Dim dataEvent As String
         Dim dataLesson As String
         Dim dataClient As String
         Dim dataCountry As String
         Dim dataDivision As String
         Dim dataReporter As String
         Dim dataWellSize

         'set dataClient to ddlClient value or txtOthrClnt value
         If ddlClient.SelectedItem.Value = "Other . . ." Then
             dataClient = txtOthrClnt.Text
         Else
             dataClient = ddlClient.SelectedItem.Value
         End If

         'set dataCountry to ddlCountry value or txtOthrCntry value
         If ddlCountry.SelectedItem.Value = "Other . . ." Then
             dataCountry = txtOthrCntry.Text
         Else
             dataCountry = ddlCountry.SelectedItem.Value
         End If

         'set dataInst to ddlInst value or txtOthrInstLoc value
         If ddlInst.SelectedItem.Value = "Other . . ." Then
             dataInst = txtOthrInstLoc.Text
         Else
             dataInst = ddlInst.SelectedItem.Value
         End If

         'set dataTask to ddlTask value or txtOthrTsk value
         If ddlTask.SelectedItem.Value = "Other . . ." Then
             dataTask = txtOthrTsk.Text
         Else
             dataTask = ddlTask.SelectedItem.Value
         End If

         dataArea = rblArea.SelectedItem.Value
         dataDivision = rblDivision.SelectedItem.Value
         dataDate = txtDate.Text
         dataReporter = txtReporter.Text
         dataWell = txtWellNo.Text
         dataEvent = txtEvent.Text
         dataLesson = txtLesson.Text
         dataWellSize = ddlWlSz.SelectedItem.Value

It's the dropDownLists (ddl[Name]) that it doesn't like!

Any ideas

Thanks again
Jane

 

[DaZZleD]

what sort of error do you get?

...i'm not a vb expert, but shouldn't
Dim dataWellSize
be
Dim dataWellSize As String

just like the rest?

--------------------------
"two wrongs don't make a right, but three lefts do" - the unknown sage