Syntax error in INSERT INTO statement. vb.net

  • Thread starter Thread starter Ben
  • Start date Start date
B

Ben

I am using VB.net
The Error message is :Syntax error in INSERT INTO statement. vb.net

Could this be casused by my primary Key? if my primary key is "ID" and it is
(autonumber). I tried inseting a blank field, number in a field, and the
example is with out the field at all.


INSERT INTO EST_TRI_Target_Data (TRI Facility ID, Facility Name, Street
Address, City, State, Zip Code, County, Parent Company, Parent Company Duns,
Mailing Name, Mailing Street Address, Mailing City, Mailing State, Mailing
Zip Code, Public Contact Name, Public Contact Phone, Tech Contact Name, Tech
Contact Phone, Tech Contact Email, Major Industry, Primary SIC Code) VALUES
(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)





This is the code

Dim myConnection2 As New _

OleDbConnection( _

"Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=C:\Documents and Settings\BookPC\My Documents\EST ltd USA\TRI
data\TRI_data.mdb")

'Dim mySqlDataAdapter As SqlDataAdapter

Dim strSelect2 As String = _

"SELECT * FROM EST_TRI_Target_Data"

Dim dsCmd2 As _

New OleDbDataAdapter(strSelect2, myConnection2)



'myConnection2 = New SqlConnection("server=(local)C:\Documents and
Settings\BookPC\My Documents\EST ltd USA\TRI
data\TRI_data.mdb;Trusted_Connection=yes;database=TRI_data")

'mySqlDataAdapter = New SqlDataAdapter("Select * from EST_TRI_Target_Data",
myConnection2)

Dim myDataSet As DataSet = New DataSet()

Dim myDataRow As DataRow

' Create command builder. This line automatically generates the update
commands for you, so you don't

' have to provide or create your own.

Dim myDataRowsCommandBuilder As OleDbCommandBuilder = New
OleDbCommandBuilder(dsCmd2)

' Set the MissingSchemaAction property to AddWithKey because Fill will not
cause primary

' key & unique key information to be retrieved unless AddWithKey is
specified.

dsCmd2.MissingSchemaAction = MissingSchemaAction.AddWithKey



dsCmd2.Fill(myDataSet, "EST_TRI_Target_Data")

myDataRow = myDataSet.Tables("EST_TRI_Target_Data").NewRow()

'myDataRow("TRI_Facility_ID") = "gs5436t3tsata"

'myDataRow("Parent_Company_Duns") = "db Enterprises"

'myDataRow("County") = "Nassau"

myDataSet.Tables("EST_TRI_Target_Data").Rows.Add(myDataRow)

myDataSet.Tables("EST_TRI_Target_Data").Rows(0)("ID") =
dsCmd2.MissingSchemaAction

Do While i < 21

i = i + 1

'SearchText = IIf(TextBox2.Text = "", "Tech Contact Name" & "</td><td><font
color=#990000>", TextBox2.Text)

SearchText = IIf(TextBox2.Text = "", DataFields(i, 0) & "</td><td><font
color=#990000>", TextBox2.Text)

gOptions = "rtfWholeWord"

'RichTextBox1.Find (FindString, StartPos, EndPos, Options)

' options: rtfNoHighlight,rtfWholeWord, rtfMatchCase

TextLength = RichTextBox1.TextLength

PosOne = RichTextBox1.Find(SearchText, 0, RichTextBoxFinds.NoHighlight)

PosTwo = RichTextBox1.Find("</font>", PosOne, RichTextBoxFinds.NoHighlight)

RichTextBox1.SelectionStart() = (PosOne + 29 + DataFields(i, 1))

FieldSize = PosTwo - (PosOne + 29 + DataFields(i, 1))

RichTextBox1.SelectionLength = FieldSize

'StoremyData.DesignMode.Tables(0).Rows(i)(DataFields(i, 0)) =
RichTextBox1.SelectedText

'MsgBox(RichTextBox1.SelectedText)

myDataSet.Tables("EST_TRI_Target_Data").Rows(0)(DataFields(i, 0)) =
RichTextBox1.SelectedText

'myDataRow(DataFields(i, 0)) = RichTextBox1.SelectedText

' If PosOne = -1 Then

' MsgBox("Text not found")

' Else

' MsgBox("Text Found")

' End If

Loop

'***************************************************************************


'myDataSet.Tables("EST_TRI_Target_Data").Rows.Add(myDataRow)

Debug.WriteLine(myDataRowsCommandBuilder.GetInsertCommand.CommandText)

dsCmd2.Update(myDataSet, "EST_TRI_Target_Data")

myConnection2.Close()
 
You have to enclose identifiers within [] if they contains a white space.
You may want to always do that (for example it also allows to use reserved
words if you have unintentionnaly used a column name that is also a reserved
keyword).
 
Back
Top