database update error

[paulthepaddy]

hi all im geting this error
Syntax error in INSERT INTO statement.
with the following code, i dont know whats wrong as im following a guide

Dim con As New OleDb.OleDbConnection
        con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\invoices.accdb"
        Dim ds As New DataSet
        Dim da As OleDb.OleDbDataAdapter
        con.Open()
        da = New OleDb.OleDbDataAdapter("SELECT * FROM Invoices", con)
        da.Fill(ds, "Invoices")
        Dim cb As New OleDb.OleDbCommandBuilder(da)
        Dim dsNewRow As DataRow
        dsNewRow = ds.Tables("Invoices").NewRow()
        dsNewRow.Item("Invoice Number") = Global_Variables.Invoice_Name
        dsNewRow.Item("Date") = Format(System.DateTime.Today, "dd/MM/yy")
        dsNewRow.Item("Customer") = Global_Variables.customer_Name
        dsNewRow.Item("Amount") = Global_Variables.Total_Total
        dsNewRow.Item("Paid") = False
        ds.Tables("Invoices").Rows.Add(dsNewRow)
        [COLOR=red]da.Update(ds, "Invoices")[/COLOR]
        con.Close()

red code is giving the error

 

[jmcilhinney]

You should never use column names that are reserved words or contain special characters if you can avoid it. This error is occurring because of that. You have one column name with spaces in it and another that is a reserved word. You should change them if at all possible.

If it's not possible, you can set the QuotePrefix and QuoteSuffix of the CommandBuilder to "[" and "]" respectively, so the dodgy names get wrapped in brackets.

 

[paulthepaddy]

could u help me actually use this. i googled it to see what it was but the MSDN didn't really tell alot, so could you tell me where i have went wrong

Public Overridable Property QuotePrefix As String
        Get

        End Get
        Set(value As String)
            value = "["
        End Set
    End Property
    Public Overridable Property Quotesuffix As String
        Get

        End Get
        Set(value As String)
            value = "]"
        End Set
    End Property
    Public Sub AddToInvoiceTracker()
        Dim con As New OleDb.OleDbConnection
        con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\invoices.accdb"
        Dim ds As New DataSet
        Dim da As OleDb.OleDbDataAdapter
        con.Open()
        da = New OleDb.OleDbDataAdapter("SELECT * FROM Invoices", con)
        da.Fill(ds, "Invoices")
        Dim cb As New OleDb.OleDbCommandBuilder(da)
        Dim dsNewRow As DataRow
        dsNewRow = ds.Tables("Invoices").NewRow()
        dsNewRow.Item("[Invoice Number]") = Global_Variables.Invoice_Name
        dsNewRow.Item("[Date]") = Format(System.DateTime.Today, "dd/MM/yy")
        dsNewRow.Item("[Customer]") = Global_Variables.customer_Name
        dsNewRow.Item("[Amount]") = Global_Variables.Total_Total
        dsNewRow.Item("[Paid]") = False
        ds.Tables("Invoices").Rows.Add(dsNewRow)
        da.Update(ds, "Invoices")
        con.Close()
    End Sub

 

[jmcilhinney]

Where you went wrong is ignoring what I posted. You did something that I didn't say to do and you didn't do what I did say to do. Go back to your old code, do what I said and it will work.

 

[paulthepaddy]

ahh right thank you i got it now. because i didn't know what the pre/suffix did i googled it to see what it was took me a few reads of your post to catchs on that my cb would have the options in their

cb.QuotePrefix = "["
        cb.QuoteSuffix = "]"

i will try that now, but if that doesn't work then im totally lost lol

no, that didn't work, im now geting error that the databse does have colum [Invoice Name] but if i take away the " " it comes up the blue lines

EDIT: never mind i go it, it puts the names in the [ ] to make is SQL friendly i think :S just from what i had read i thought it was kind of like replacing " " with [ ].

thanks , i must say i quit like your not giving code policy, if u had gave me code then i would i have googled it and thought it was doing something it wasn't
so thanks and + rep

 

[jmcilhinney]

thanks , i must say i quit like your not giving code policy, if u had gave me code then i would i have googled it and thought it was doing something it wasn't
so thanks and + rep

That's exactly why I do it, and I'm glad that some people do appreciate that.

 

[paulthepaddy]

lol, it is probably more appreciated than you think, yea your going to get people on the forum who just want the answer, people like me who want to learn will appreciate the fact they still have to think about what is going on. unfortunatly i was on a course for ICT and we did a bit of programming, we cover bare basics data types variables maths funtions and operators, dont think we even covered subs, byval, byref quite alot of things that you should understand where covered, all we did was make a calculator lol. most of thet hings i know ATM are self taught, but still finding it hard to find good tutorials that cover more than basics. reading a book ATM that was published in 1997 so im not coming across many of the newer method and stuff, so it is nice being able to come here and get a puss in the right direction rather than. here you go copy & paste dont bother learning what it is