Insert Into Syntax Error when adding new rows
Insert Into Syntax Error when adding new rows
Insert Into Syntax Error when adding new rows
Insert Into Syntax Error when adding new rows
Insert Into Syntax Error when adding new rows
Insert Into Syntax Error when adding new rows Insert Into Syntax Error when adding new rows Insert Into Syntax Error when adding new rows Insert Into Syntax Error when adding new rows Insert Into Syntax Error when adding new rows Insert Into Syntax Error when adding new rows Insert Into Syntax Error when adding new rows Insert Into Syntax Error when adding new rows
Insert Into Syntax Error when adding new rows Insert Into Syntax Error when adding new rows
Insert Into Syntax Error when adding new rows
Go Back  Xtreme Visual Basic Talk > > > Insert Into Syntax Error when adding new rows


Reply
 
Thread Tools Display Modes
  #1  
Old 01-02-2014, 11:59 PM
JD_FAN JD_FAN is offline
Newcomer
 
Join Date: Jan 2014
Posts: 3
Default Insert Into Syntax Error when adding new rows


Tried This
Code:
   Dim drNewRow As DataRow = m_dtContacts.NewRow()

        With frmMain
            drNewRow("ID") = .txtID.Text
            drNewRow("Contact Name") = .txtContactName.Text
            drNewRow("Address") = .txtAddress.Text
            drNewRow("Town") = .txtTown.Text
            drNewRow("County") = .txtCounty.Text
            drNewRow("PostCode") = .txtPostCode.Text
            drNewRow("PhoneNo") = .txtPhoneNo.Text
            drNewRow("MobileNo") = .txtMobileNo.Text
            drNewRow("Fax") = .txtFax.Text
            drNewRow("email") = .txtemail.Text
        End With

        m_dtContacts.Rows.Add(drNewRow)
        m_daContacts.Update(m_dtContacts)
        m_intRowPosition = m_dtContacts.Rows.Count - 1
and I also tried this
Code:
   Dim drNewRow As DataRow = m_dtContacts.NewRow()

        With frmMain
            drNewRow("ID") = .txtID.Text
            drNewRow([Contact Name]") = .txtContactName.Text
            drNewRow("Address") = .txtAddress.Text
            drNewRow("Town") = .txtTown.Text
            drNewRow("County") = .txtCounty.Text
            drNewRow("PostCode") = .txtPostCode.Text
            drNewRow("PhoneNo") = .txtPhoneNo.Text
            drNewRow("MobileNo") = .txtMobileNo.Text
            drNewRow("Fax") = .txtFax.Text
            drNewRow("email") = .txtemail.Text
        End With

        m_dtContacts.Rows.Add(drNewRow)
        m_daContacts.Update(m_dtContacts)
        m_intRowPosition = m_dtContacts.Rows.Count - 1
and I also tried this
Code:
Dim drNewRow As DataRow = m_dtContacts.NewRow()

        With frmMain
            drNewRow(0) = CLng(.txtID.Text)
            drNewRow(1) = .txtContactName.Text
            drNewRow(2) = .txtAddress.Text
            drNewRow(3) = .txtTown.Text
            drNewRow(4) = .txtCounty.Text
            drNewRow(5) = .txtPostCode.Text
            drNewRow(6) = .txtPhoneNo.Text
            drNewRow(7) = .txtMobileNo.Text
            drNewRow(8) = .txtFax.Text
            drNewRow(9) = .txtemail.Text

        End With

        m_dtContacts.Rows.Add(drNewRow)
        m_daContacts.Update(m_dtContacts)
        m_intRowPosition = m_dtContacts.Rows.Count - 1
I do not have control to change the field "Contact Name". When I run the code I get
An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll
{"Syntax error in INSERT INTO statement."}
Reply With Quote
  #2  
Old 01-30-2014, 11:24 AM
CharlieMay's Avatar
CharlieMay CharlieMay is offline
Regular
 
Join Date: Sep 2009
Location: Indiana
Posts: 89
Default

Where are you specifying your commandbuilder?
Can you show that code?

The CommandBuilder has a QuotePrefix and a QuoteSuffix property. This is for these types of situations in that is allows you to specify what to wrap the columns with. As you probably already know wrapping a column name in [] allows you to have spaces in your database. In a string-based INSERT statement you would just

INSERT INTO [My Table] ([My Column]) VALUES ('ColumnValue')

But when you use the commandbuilder, you need to tell the commandbuilder what to use to prefix and suffix column names (in case they contain spaces that you can't control)

So a typical commandBuilder would be set up like:

Code:
Dim cb as New SQLCommandBuilder(myDataAdapter)
'Now just specify the prefix and suffix
  cb.QuotePrefix = "["
  cb.QuoteSuffix = "]"
MSDN QuotePrefix
__________________
I thought I knew what I was doing...
...And then I turned on Option Strict

Last edited by CharlieMay; 01-30-2014 at 11:51 AM.
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Insert Into Syntax Error when adding new rows
Insert Into Syntax Error when adding new rows
Insert Into Syntax Error when adding new rows Insert Into Syntax Error when adding new rows
Insert Into Syntax Error when adding new rows
Insert Into Syntax Error when adding new rows
Insert Into Syntax Error when adding new rows Insert Into Syntax Error when adding new rows Insert Into Syntax Error when adding new rows Insert Into Syntax Error when adding new rows Insert Into Syntax Error when adding new rows Insert Into Syntax Error when adding new rows Insert Into Syntax Error when adding new rows
Insert Into Syntax Error when adding new rows
Insert Into Syntax Error when adding new rows
 
Insert Into Syntax Error when adding new rows
Insert Into Syntax Error when adding new rows
 
-->