Results 1 to 15 of 15

Thread: Syntax Error in query expression

  1. #1

    Thread Starter
    Registered User
    Join Date
    Feb 2015
    Posts
    2

    Syntax Error in query expression

    Dear all,

    I just join this Forum, for asking the problem I found.
    The problem are :

    1. I develop a software for human resources department.
    when I try to execute there is a error message said : Syntax error ( missing operator ) in query expression "Regency"

    2. Cannot proccesed the database. cause there is no EDMX file

    Please help me to solve these problem

    Thank you

    Regards,
    Budy

  2. #2
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,562

    Re: Syntax Error in query expression

    We're going to need more information than that. At the very least you're going to need to post your code showing where the error occurs. We'll also need to know what the sql query you're executing looks like.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  3. #3
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,829

    Re: Syntax Error in query expression

    Problem is on line 3.

    It needs to be corrected. Try removing the eels from the hovercraft.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  4. #4

    Thread Starter
    Registered User
    Join Date
    Feb 2015
    Posts
    2

    Re: Syntax Error in query expression

    Sorry guys.. this is the code
    Name:  gambar1.jpg
Views: 53
Size:  26.1 KB

  5. #5
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,829

    Re: Syntax Error in query expression

    Ugh... geezes... why do people do that? Screenshots are useless especially when it's code... we can't see it and we can't copy it and we sure as heck aren't going to retype it... A couple things I can see... you're not specifying the fields for the insert... which is a dangerous thing - what if someone changes the fields, reordering them? your insert will fail. secondly, you're concatenating the values - this is probably your issue here - you're probably trying to insert some kind of data that's breaking the syntax... plus you're open for injection attacks. Like this. You really should 1) Explicitly define the fields you're inserting into and 2) use parameters.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  6. #6
    Hyperactive Member
    Join Date
    Mar 2012
    Posts
    311

    Re: Syntax Error in query expression

    Quote Originally Posted by techgnome View Post
    You really should 1) Explicitly define the fields you're inserting into and 2) use parameters.

    -tg
    Actually, it looks like budhay99 is trying to use Parameters, but is going about it the wrong way. Notice below the query are a bunch of .Parameters.AddWithValue lines... However the query itself is not set up to use those parameters... Look at tg's signature; there is a link to "How to Use Parameters" which discusses them in general and in turn links to a thread in the code bank for examples on how to use them correctly.

  7. #7
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,829

    Re: Syntax Error in query expression

    Huh.,... I saw the string concatenation and pretty much skipped the rest of the code...

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  8. #8
    New Member
    Join Date
    Feb 2015
    Posts
    1

    Re: Syntax Error in query expression

    Hi there,
    I am having a similar problem with the following code:
    Dim strSQL As String
    strSQL = ""
    strSQL = "INSERT INTO eventlog "
    strSQL = strSQL & "(date, accountno, approvalcode, order, sku, price, discount, code, event)"
    strSQL = strSQL & " Values"
    strSQL = strSQL & "(" & "#" & Date & "#, "
    strSQL = strSQL & " '" & Me!AccountNo & "', "
    strSQL = strSQL & "'" & strSeek & "',"
    strSQL = strSQL & "'" & Me!ReturnNo & "', "
    strSQL = strSQL & "'" & Me!Combo33 & "', "
    strSQL = strSQL & "'" & 0 & "', "
    strSQL = strSQL & "'" & 0 & "', "
    strSQL = strSQL & "'" & 0 & "', "
    strSQL = strSQL & "'" & "Non Returnable Item" & "'" & ");"
    'Debug.Print strSQL
    CurrentDb.Execute strSQL

    The error I am receiving is : Syntax error in Insert Into statement.
    Is any one so kind to let me know what is wrong?
    Thanks

  9. #9
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    35,024

    Re: Syntax Error in query expression

    You may be getting the same error message, but the reason is totally different. I'm not even sure that the language is the same. That looks like VBA rather than VB.NET. VBA will have totally different constraints and different SQL. However, you are wrapping integer values (0) in single quotes. I believe that is wrong in all flavors of SQL. Strings get wrapped in single quotes, numbers do not, and dates are wrapped in either # or single quotes depending on which flavor of SQL you are using.
    My usual boring signature: Nothing

  10. #10
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,829

    Re: Syntax Error in query expression

    well, don't forget that Date is actually a keyword, so you should avoid using it as a field name... but since no one does that anymore... that couldn't possibly be the cause here...


    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  11. #11
    Hyperactive Member
    Join Date
    Mar 2012
    Posts
    311

    Re: Syntax Error in query expression

    Code:
    strSQL = strSQL & " Values"
    strSQL = strSQL & "(" & "#" & Date & "#, "
    From this part, the string will be:
    Values(#2/22/2015#, (assuming 2/22/2015 is the Date)
    Notice that there is no space after Values....

  12. #12
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,829

    Re: Syntax Error in query expression

    Quote Originally Posted by Pyth007 View Post
    Code:
    strSQL = strSQL & " Values"
    strSQL = strSQL & "(" & "#" & Date & "#, "
    From this part, the string will be:
    Values(#2/22/2015#, (assuming 2/22/2015 is the Date)
    Notice that there is no space after Values....
    There doesn't have to be... the ( is all the delimiter the SQL parser needs... now if it was "Values#2/22/2015#" ... now that would be an issue. But as long as the appropriate delimiter from one clause to the next (or any of its parts) is there, the parser is fine. The reason for a space at that point would be human readability.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  13. #13
    Hyperactive Member
    Join Date
    Mar 2012
    Posts
    311

    Re: Syntax Error in query expression

    Quote Originally Posted by techgnome View Post
    There doesn't have to be... the ( is all the delimiter the SQL parser needs... now if it was "Values#2/22/2015#" ... now that would be an issue. But as long as the appropriate delimiter from one clause to the next (or any of its parts) is there, the parser is fine. The reason for a space at that point would be human readability.

    -tg
    I did not realize that, my bad... (and here I thought I had found the issue with a nearly invisible typo) It probably is the single-quotes that Shaggy was referring to...

  14. #14
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    35,024

    Re: Syntax Error in query expression

    Or the Date that TG mentioned. Even if that isn't causing a problem, yet, there is almost no way that part is right in either use of the term. TG seems to have focused on the use of 'date' as a field name, which is a problem, but this:

    strSQL = strSQL & "(" & "#" & Date & "#, "

    is almost weirder, in a different way. In that use, Date is a type. I wouldn't think that it even COULD be a variable name. So, with Option Strict OFF, the value being supplied would be the type name, which is never going to work.

    So, there are actually several things wrong with the query, as written.
    My usual boring signature: Nothing

  15. #15
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,829

    Re: Syntax Error in query expression

    I believe when used as such Date, which is not just a type but also a BASIC keyword, that returns the current date.

    It's been so long since I've used it as such, but I want to say that there is Now which returned the current date and time, and Date which returned the current date.

    If it was the "Date" "variable" it would be a VB syntax error, not a SQL one and certainly wouldn't produce a "Error near Insert into" error. Probably wouldn't even compile in the first place.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

Posting Permissions

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



Click Here to Expand Forum to Full Width