INSERT INTO date problem? (1 Viewer)

Minty

Minty

AWF VIP
Local time
Today, 03:23
Joined
Jul 26, 2013
Messages
10,371
I'm trying to code a bulk update to a table with new records by using an INSERT as follows;
Code: 
 sSQL_Status = ""
    sSQL_Status = sSQL_Status & "INSERT INTO [Copy_Job Details] (Account_No, DelSiteID, [Order Number], [Currency], [Model Number], Manufacturer, [Serial Number], [Stock Code / Part Number], [Repair Price], [RMA Issued Date], [Repair Type], InvStatus, ETA, RepairStart, [RMA Received Date], ShipPriority ) "
    sSQL_Status = sSQL_Status & "VALUES ( 1194221, 7553, " & sCurrentOrder & ", " & sCurr & ", " & sPartNo & ", " & sManu & ", " & sSerialNo & ", " & sPartNo & ", 25, #" & strDate & "#, " & sRepairType & ", 1, #" & strDate7 & "#, #" & strDate & "#, #" & strDate & "#, 23)"

In the immediate window I get the following;
Code: 
INSERT INTO [Copy_Job Details] (Account_No, DelSiteID, [Order Number], [Currency], [Model Number], Manufacturer, [Serial Number], [Stock Code / Part Number], [Repair Price], [RMA Issued Date], [Repair Type], InvStatus, ETA, RepairStart, [RMA ReINSERT INTO [Copy_Job Details] (Account_No, DelSiteID, [Order Number], [Currency], [Model Number], Manufacturer, [Serial Number], [Stock Code / Part Number], [Repair Price], [RMA Issued Date], [Repair Type], InvStatus, ETA, RepairStart, [RMA Received Date], ShipPriority ) VALUES ( 1194221, 7553, M65407_1, $, MR-L5MQN-02, Quantum, None, MR-L5MQN-02, 25, #10/14/2013#, "Consignment Stock", 1, #10/21/2013#, #10/14/2013#, #10/14/2013#, 23)

I keep getting an incorrect syntax near '#' error but I thought the dates were formatted correctly. I'm assuming this is another noob beginners error, but can't see it.

Apologies for the awful field names in advance - They can't easily be changed... (Inherited project):eek:
 
N

namliam

The Mailman - AWF VIP
Local time
Today, 04:23
Joined
Aug 11, 2003
Messages
11,695
Making things readable....
Code: 
INSERT INTO [Copy_Job Details] (Account_No, DelSiteID, [Order Number], [Currency], [Model Number], Manufacturer, [Serial Number], [Stock Code / Part Number], [Repair Price], [RMA Issued Date],       [Repair Type], InvStatus,          ETA,  RepairStart, [RMA ReINSERT INTO [Copy_Job Details] (Account_No, DelSiteID, [Order Number], [Currency], [Model Number], Manufacturer, [Serial Number], [Stock Code / Part Number], [Repair Price], [RMA Issued Date], [Repair Type], InvStatus, ETA, RepairStart, [RMA Received Date], ShipPriority ) 
                          VALUES ( 1194221,      7553,       M65407_1,          $,    MR-L5MQN-02,      Quantum,            None,                MR-L5MQN-02,             25,      #10/14/2013#, "Consignment Stock",         1, #10/21/2013#, #10/14/2013#, #10/14/2013#, 23)

It looks like you made a pasting error ? :banghead:

fixing the pasting error:
Code: 
INSERT INTO [Copy_Job Details] (Account_No, DelSiteID, [Order Number], [Currency], [Model Number], Manufacturer, [Serial Number], [Stock Code / Part Number], [Repair Price], [RMA Issued Date],       [Repair Type], InvStatus,          ETA,  RepairStart, [RMA Received Date], ShipPriority ) 
                          VALUES ( 1194221,      7553,       M65407_1,          $,    MR-L5MQN-02,      Quantum,            None,                MR-L5MQN-02,             25,      #10/14/2013#, "Consignment Stock",         1, #10/21/2013#, #10/14/2013#,        #10/14/2013#, 23)
It looks like you are missing some quotes (") here and there like you are doing at Consignment Stock
 
D

DavidAtWork

Registered User.
Local time
Today, 03:23
Joined
Oct 25, 2011
Messages
699
Some of your syntax doesn't look quite right, generally follow this
Code: 
 ' for string variables 
'" & strVar & "'   OR """ & strVar & """
'for numbers
" & numVar & "   OR '" + str(numVar) + "'
'for dates
#" & dateVar & "#   OR '" + format(dateVar) + "'


David
 
Minty

Minty

AWF VIP
Local time
Today, 03:23
Joined
Jul 26, 2013
Messages
10,371
Thank you for the replies, my air code was rather hastily written...
I've added the appropriate quote levels and am still getting the same error - The Insert string is now ;
Code: 
INSERT INTO [Copy_Job Details] (Account_No, DelSiteID, [Order Number], [Currency], [Model Number], Manufacturer, [Serial Number], [Stock Code / Part Number], [Repair Price], [RMA Issued Date],       [Repair Type], InvStatus,          ETA,  RepairStart, [RMA Received Date], ShipPriority ) 
VALUES (  	     		   1194221,      7553,     "M65407_1",        "$",  "MR-L5MQN-02",    "Quantum",          "None",              "MR-L5MQN-02",             25,      #10/14/2013#, "Consignment Stock",         1, #10/21/2013#, #10/14/2013#,        #10/14/2013#,    23)

And the source (Not so tidy)
Code: 
sSQL_Status = ""
    sSQL_Status = sSQL_Status & "INSERT INTO [Copy_Job Details] " _
                              & "(Account_No, DelSiteID, [Order Number], " _
                              & "[Currency], [Model Number], Manufacturer, " _
                              & "[Serial Number], [Stock Code / Part Number], [Repair Price], " _
                              & "[RMA Issued Date], [Repair Type], InvStatus, " _
                              & "ETA, RepairStart, [RMA Received Date], " _
                              & "ShipPriority ) " & vbCrLf
    sSQL_Status = sSQL_Status & "VALUES ( 1194221, 7553, """ & sCurrentOrder & """, " & sCurr & ", """ & sPartNo & """, " & sManu & ", " & sSerialNo & ", """ & sPartNo & """, 25, #" & strDate & "#, " & sRepairType & ", 1, #" & strDate7 & "#, #" & strDate & "#, #" & strDate & "#, 23)"

    
    Debug.Print sSQL_Status
    Set cn = New ADODB.Connection
    cn.Open "dsn=Repairs2000"
    cn.Execute (sSQL_Status)
    cn.Close

The error seems to be date related ?
 
pr2-eugin

pr2-eugin

Super Moderator
Local time
Today, 03:23
Joined
Nov 30, 2011
Messages
8,494
I cannot see anything wrong.. If you copy and paste the generated SQL query into the Query wizard, can you execute it? If there is any error it will point you to the exact error..

Also try.. I can see you used vbCrLf (Why?) Currency a String? (Or double?) your Debug.Print suggests it is a String..
Code: 
    sSQL_Status = "INSERT INTO [Copy_Job Details] " & _
                  "(Account_No, DelSiteID, [Order Number], " & _
                  "[Currency], [Model Number], Manufacturer, " & _
                  "[Serial Number], [Stock Code / Part Number], [Repair Price], " &  _
                  "[RMA Issued Date], [Repair Type], InvStatus, " & _
                  "ETA, RepairStart, [RMA Received Date], " & _
                  "ShipPriority) VALUES (1194221, 7553, """ & sCurrentOrder & """, """ & _
                  sCurr & """, """ & sPartNo & """, " & sManu & ", " & sSerialNo & ", """ & sPartNo & """, 25, #" & _
                  strDate & "#, " & sRepairType & ", 1, #" & strDate7 & "#, #" & strDate & "#, #" & strDate & "#, 23)"
    Debug.Print sSQL_Status
 
Minty

Minty

AWF VIP
Local time
Today, 03:23
Joined
Jul 26, 2013
Messages
10,371
Well I could not see anything wrong - and in the query window my code works... :confused:

The currency is a text symbol field required for our accounts system (don't ask) - so string is correct.

The vbCrLf was purely to make the immediate window output more readable...

I'm mystified? The code you posted up is coming up with an error - I'll double check it shortly. ( I got too confused with all the quotes trying to split the lines in the values statement ;) )

Edit: There were an extra set of "" around the currency symbol, once fixed - same net result: Runtime Error '-2147217900 (80040e14)' Incorrect Syntax near '#'
 
Last edited:
Minty

Minty

AWF VIP
Local time
Today, 03:23
Joined
Jul 26, 2013
Messages
10,371
UPDATE: This is definitely a date issue, I substituted Now into the first date field and the error changes to "somewhere near 11" which is the current time in the US time zone I'm working remotely into.
 
N

namliam

The Mailman - AWF VIP
Local time
Today, 04:23
Joined
Aug 11, 2003
Messages
11,695
Is one of the date fields not really a date field?

Can you attach (a dummy version) of your database ? Seems currious
 
D

DavidAtWork

Registered User.
Local time
Today, 03:23
Joined
Oct 25, 2011
Messages
699
If you've nailed the problem to a date issue, then as nam suggests, check all the date fields are indeed date fields. Otherwise I can only suggest taking it apart, start with just a few fields and keep adding more fields until you find the error.

David
 
J

JHB

Have been here a while
Local time
Today, 04:23
Joined
Jun 17, 2012
Messages
7,732
Minty said:
Code: 
INSERT INTO [Copy_Job Details] (Account_No, DelSiteID, [Order Number], [Currency], [Model Number], Manufacturer, [Serial Number], [Stock Code / Part Number], [Repair Price], [RMA Issued Date],       [Repair Type], InvStatus,          ETA,  RepairStart, [RMA Received Date], ShipPriority ) 
VALUES (                      1194221,      7553,     "M65407_1",        "$",  "MR-L5MQN-02",    "Quantum",          "None",              "MR-L5MQN-02",             25,      #10/14/2013#, "Consignment Stock",         1, #10/21/2013#, #10/14/2013#,        #10/14/2013#,    23)
Click to expand...
What is the field type for [Serial Number]? I see you are trying to store text value in it, ("None"/None)!
 
Minty

Minty

AWF VIP
Local time
Today, 03:23
Joined
Jul 26, 2013
Messages
10,371
Okay - After stripping things right back, I was still getting errors but different ones.

Confession time - I was opening a connection to the wrong DB.... :banghead:
Why it wasn't telling me the table wasn't available I don't know, but it seemed to be connecting!

Apologies for wasting your time on it, I have however learnt a great deal about date formats .... :rolleyes:
 
You must log in or register to reply here.

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom