PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197

PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197
SQL statement does not work on SQLite-VBForums
Results 1 to 4 of 4

Thread: SQL statement does not work on SQLite

  1. #1

    Thread Starter
    New Member
    Join Date
    Oct 2014
    Posts
    10

    SQL statement does not work on SQLite

    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


    Code:
    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

  2. #2
    Hyperactive Member
    Join Date
    Nov 2017
    Posts
    463

    Re: SQL statement does not work on SQLite

    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:

    Code:
    "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.

    Code:
    "cash = cash + '" & Tender_CashValue & "'
    letting Tender_CashValue = 1000, it would look like:

    Code:
    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.

  3. #3
    Fanatic Member
    Join Date
    Sep 2017
    Posts
    680

    Re: SQL statement does not work on SQLite

    Code:
    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

  4. #4
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    100,586

    Re: SQL statement does not work on SQLite

    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.
    vb.net Code:
    1. Dim sql = "SELECT *
    2.           FROM MyTable
    3.           WHERE MyColumn = ?"
    In earlier versions, you can use an XML literal:
    vb.net Code:
    1. Dim xml = <sql>
    2.               SELECT *
    3.               FROM MyTable
    4.               WHERE MyColumn = ?
    5.           </sql>
    6. 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.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width