[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2120
  • Last Modified:

ERROR: Missing semicolon (;) at end of SQL statement.

Hi, I'm trying to insert values into an access Db via C#.  Everytime I try to execute the following command:

cmd.CommandText="INSERT INTO " + ac + " (Phone_Number,Custom) VALUES " + "('" + number + "','" + pn.Custom.ToString() + "') " +
                                    "WHERE '" + number + "' NOT IN (Phone_Number);";

I get this error:
Missing semicolon (;) at end of SQL statement.

Here is what the query looks like with values inside the variables:

INSERT INTO 240 (Phone_Number,Custom) VALUES ('5555555','1') WHERE '5555555' NOT IN (Phone_Number);

Any ideas?
Thanks

0
ggalbo
Asked:
ggalbo
  • 5
  • 4
  • 2
  • +2
1 Solution
 
Stephen_PerrettCommented:
Have you tried pasting you SQL directly into a new Access Query in SQL view

I'm wondering that your SQL should be as follows:

INSERT INTO 240 (Phone_Number,Custom) VALUES ('5555555','1') WHERE Phone_Number<> '5555555';

Steve
0
 
Stephen_PerrettCommented:
No, that wouldn't be right

If you are wanting to append a row into a table called '240',  I would think you don't need the WHERE clause at all

Can you explain what you want your query to do?

Steve
0
 
ggalboAuthor Commented:
I may have it wrong.  I'm very new to SQL.  I don't want repeats of phone numbers in my list, which is why i have that where clause.  Basically, I get a new list of 'do not call' numbers once a month, and I'd like to add new ones to the list.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Bob LambersonSoftware EngineerCommented:
Stephen is right, the INSERT INTO statement is used in what is known as an "append" query, because it adds a new record to the end of the table. For that reason he is also correct that the WHERE statement is of no value in this particular append query.

It sounds like you are trying to update a record that already exists, but you will need to explain specifically what you are after.

If you simply UPDATE "WHERE '" + number + "' NOT IN (Phone_Number);"  you would put the new number in every record that has a phone number different from "number",

Bob
0
 
Stephen_PerrettCommented:
One thing you could do to ensure that your table doesn't get duplicate numbers added to it, is to set the Indexed property of the Phone_Number field to "Yes (No Duplicates)" in design view of the Access table. I guess that then if you run the query "INSERT INTO 240 (Phone_Number,Custom) VALUES ('5555555','1');" an error will occur. Perhaps you can then handle this error or perhaps you can use a transaction (something I don't know about) instead.

Steve
0
 
Stephen_PerrettCommented:
an error will occur.

should read

an error will occur if the number already exists in the table.

Steve
0
 
Bob LambersonSoftware EngineerCommented:
Here is an sql way of doing the same thing.

You will need to use a unique field in the table ( I have used custID here - just replace it with yours) and you will need to have a Reference set to DAO 3.6
You can call this function from a form button.

Public Function udateit()
    Dim phnumber As String  'your number variable - number is a reserved name and can create confusion when used in code.
    Dim db As Database
    Dim sql As String
    Dim custID As String
    custID = "walmart"      'change this to whatever customer or record id you have that is unique.
    phnumber = 5552222      'change to your phone number.
    Set db = CurrentDb
    sql = "UPDATE brands2 SET brands2.Phone_number = '" & phnumber & "' WHERE (((brands2.Phone_number)not in (select custId,Phone_number from brands2 where Phone_number = '" & phnumber & "' and " & custID & " <> Customer_ID)))"
    db.Execute (sql)

End Function



Bob
0
 
hnasrCommented:
You may need to modify the code.  Look for the record with the required Phone_Number, Cutom.  If not there then DoCmd.RunSQL "your querry without the where clase"
0
 
GRayLCommented:
The proper string concatination character is & not +.  Trying changing to ampersand and see if it fixes anything.
0
 
ggalboAuthor Commented:
GRayL: I am using c# not vb
hnaser: I tried that technique.  While it works, it is too slow
Stephen, I will try the the error handling techinque next.  I believe that it too will be slow, because error handling seems to be very slow in .NET.
Bob, I will also try your SQL syntax.  I tried similar SQL code, and i was still getting the semi colon error, but maybe I missed something.

I'll keep you guys posted.

Thanks for all the responses.  If any new ideas pop in your head, keep em comin' ;)
0
 
ggalboAuthor Commented:
I may have been to quick to say that hnaser's technique was too slow.  I just tried INSERTING, without checking for any previous instances of a phone number in the table, and it is taking a long time.   I had assumed the select AND the insert was taking too long, but the insert seems to be taking the bulk of the time.  After I check Bob's technique, I will post points to the solution that worked best for me.  I then will post another question, "How can I speed up this process!!!" :)
0
 
GRayLCommented:
If you index on the phone number and don't allow duplicates ... ??
0
 
Stephen_PerrettCommented:
The following coding done using vb.net seems to do what you want and fairly quick to throw exception. I think C# is not much different. Hope this helps.
 
       Dim cn As New OleDbConnection(strConnect)
        cn.Open()
        Dim strSQL As String = "INSERT INTO 240 (Phone_number, Custom) Values ('" & strPhoneNumber & "', '" & strCustom & "')"
        Dim cmd As New OleDbCommand
        cmd.Connection = cn
        cmd.CommandText = strSQL
        Try
            cmd.ExecuteNonQuery()
        Catch exp As OleDbException
            If exp.ErrorCode = -2147467259 Then
                MessageBox.Show("Phone Number Already Appended", "Append Failure")
            End If
        Catch exp As Exception
            ' Will catch any error that we're not explicitly trapping.
            MessageBox.Show(exp.message, Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Stop)
        End Try
        cn.Close()



Steve
0
 
ggalboAuthor Commented:
I did something similar...unfortunately, even INSERTING, even without checking for dupes, is too slow ( a list can contain a few million rows).  To circumvent this, a while back I came up with a solution that used sorted flat files with binary searching; but updating was overly complex.  I guess maybe I should try another db.  Any suggestions?
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 4
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now