SQL statement does not work on SQLite

[AndyGable]

hi everyone

Could some one help me please I can not seem to get this bit of SQL code to work on a SQLite Database

SQLCommand = vbNullString
SQLCommand += "insert into tenders ("
SQLCommand += "pos, "
SQLCommand += "cash, "
SQLCommand += "cheque, "
SQLCommand += "card, "
SQLCommand += "giftvoucher, "
SQLCommand += "giftcard, "
SQLCommand += "coupon) "
SQLCommand += "values('"
SQLCommand += Format(Val(PoSNumber), "000") & "','"
SQLCommand += Tender_CashValue & "','"
SQLCommand += Tender_ChequeValue & "','"
SQLCommand += Tender_CardValue & "','"
SQLCommand += Tender_GiftVoucherValue & "','"
SQLCommand += Tender_GiftCardValue & "','"
SQLCommand += Tender_CouponsValue & "') "
SQLCommand += "on duplicate key update"
SQLCommand += "cash = cash + '" & Tender_CashValue & "','"
SQLCommand += "cheque = cheque + '" & Tender_ChequeValue & "','"
SQLCommand += "card = card + '" & Tender_CardValue & "','"
SQLCommand += "giftvoucher = giftvoucher + '" & Tender_GiftVoucherValue & "','"
SQLCommand += "giftcard = giftcard + '" & Tender_GiftCardValue & "','"
SQLCommand += "coupon = coupon + '" & Tender_CouponsValue & "');"

what am i doing wong it works fine on my MySQL Server database

[OptionBase1]

I have no experience with SQLite or MySQL for that matter, but two major issues stand out to me at first glance:

You have no separation defined between "on duplicate key update" and "cash = cash...", so your string in that area would look like this:

"on duplicate key updatecash = cash + ..."

The other issue I see is that you are enclosing presumably numeric values inside of single quotes and then doing mathematical operations on them.

"cash = cash + '" & Tender_CashValue & "'

letting Tender_CashValue = 1000, it would look like:

cash = cash + '1000'

Maybe some DB engines are more forgiving than others as far as dealing with this, but I would imagine that isn't the "proper" way of doing math operations inside of a SQL statement for any DB engine.

Good luck.

[kpmc]

SQLCommand = vbNullString 'We have no idea what this is
SQLCommand += "insert into tenders ("
SQLCommand += "pos, "
SQLCommand += "cash, "
SQLCommand += "cheque, "
SQLCommand += "card, "
SQLCommand += "giftvoucher, "
SQLCommand += "giftcard, "
SQLCommand += "coupon) "
SQLCommand += "values('"
SQLCommand += Format(Val(PoSNumber), "000") & "','" No idea what datatype PoSNumber is
SQLCommand += Tender_CashValue & "','" 'No idea
SQLCommand += Tender_ChequeValue & "','" 'No idea
SQLCommand += Tender_CardValue & "','" 'No idea
SQLCommand += Tender_GiftVoucherValue & "','" 'No idea
SQLCommand += Tender_GiftCardValue & "','" ' No idea
SQLCommand += Tender_CouponsValue & "') " ' Noidea
SQLCommand += "on duplicate key update" 'is this a tablename? you need to enclose it in []
SQLCommand += "cash = cash + '" & Tender_CashValue & "','" 'more variables we dont know type or if it even has a vaule
SQLCommand += "cheque = cheque + '" & Tender_ChequeValue & "','" 'no idea
SQLCommand += "card = card + '" & Tender_CardValue & "','" 'no idea
SQLCommand += "giftvoucher = giftvoucher + '" & Tender_GiftVoucherValue & "','"
SQLCommand += "giftcard = giftcard + '" & Tender_GiftCardValue & "','"  'no idea
SQLCommand += "coupon = coupon + '" & Tender_CouponsValue & "');"

'no idea

Your statement is making my eyes bleed. you should really be learning about parameters

[jmcilhinney]

Pretty much every database has its own flavour of SQL. There is a SQL standard and most databases implement most of it but they may do so in different ways and they may also have their own additional functionality on top of that. The fact that some SQL code works in one database is no guarantee that it will in another. If you want to know what constitutes valid SQL in SQLite then you should read the SQLite documentation.

Also, the way you're building your SQL code is terrible. For one thing, you're mixing and matching '+' and '&' for concatenation. '&' is the concatenation operator so use it EVERY time. That said, you should be using concatenation at all. VB 2015 and later support multiline String literals so you can write the whole query as a single String, e.g.

Dim sql = "SELECT *
           FROM MyTable
           WHERE MyColumn = ?"

In earlier versions, you can use an XML literal:

Dim xml = <sql>
              SELECT *
              FROM MyTable
              WHERE MyColumn = ?
          </sql>
Dim sql = xml.Value

You will end up with some whitespace in the SQL code if you indent it like that but the database doesn't care so it's generally of no consequence.

Notice also that I have used a parameter placeholder ('?' symbol). Concatenating values into SQL code is bad for numerous reasons. You should always use parameters. If you're not sure how, follow the Blog link in my signature below and check out my post on Parameters In ADO.NET. Note that each database may use different prefixes and some may not support named parameters.