MS-SQL DateTime field --> MySQL int(10)unsigned HOW!?!?

[rawnet]

Hi,

I'm trying to import data from an MS-SQL database into MySQL and have run up to a problem resulting in :

[TCX][MyODBC]You have an error in your SQL syntax near 'User(usergroupid, username, password, email, styleid, adminemail, showemail, inv' at line 1

I'm inserting data from an MS-SQL2000 DateTime (length 8) field into a MySQL int(10)unsigned field (apparently according to the GUI I'm using for the MySQL database).

My question is - how do I insert this? I'm currently using :

<cfquery name="qPutMySQLUser" datasource="vBulletin">
INSERT INTO User(usergroupid, username, password, email, styleid, adminemail, showemail, invisible, joindate, cookieuser, lastvisit, receivepm, emailonpm, pmpopup)
VALUES (2, <cfif qGetMSSQL.screenname EQ "">'#qGetMSSQL.firstname# #qGetMSSQL.surname#'<cfelse>'#qGetMSSQL.screenname#'</cfif>, '#qGetMSSQL.Password#', '#qGetMSSQL.Email_Address#', 1, 0, 0, #qGetMSSQL.DateJoined#, 1, #qGetMSSQL.DateJoined#, 1, 1, 1)
</cfquery>

I was wondering if I need to format qGetMSSQL.DateJoined in any way?

Cheers,

Ross

[rawnet]

Ug. Been working on this all weekend but still no joy. The following code works without the first query's WHERE clause. I need to remove that, but when I do it gives the Syntax error like in the above thread :

<cfquery name="qGetMSSQL" datasource="vBulletin">
SELECT *
FROM Pals
WHERE (firstname = 'Ross') AND (surname = 'williams')
</cfquery>

<cfloop query="qGetMSSQL">

    <cfquery name="qPutMySQLUser" datasource="vBulletin">
    INSERT INTO "user"(usergroupid, username, password, email, styleid, adminemail, showemail, invisible, joindate, cookieuser, lastvisit, receivepm, emailonpm, pmpopup)
    VALUES (2, <cfif qGetMSSQL.screenname EQ "">'#qGetMSSQL.firstname##qGetMSSQL.surname#',<cfelse>'#qGetMSSQL.screenname#',</cfif> '#qGetMSSQL.Password#', '#qGetMSSQL.Email_Address#', 0, 1, 0, 0, 1002661767, 1, 1002661767, 1, 1, 1)
    </cfquery>
    
    <cfquery name="qPutMySQLUserField" datasource="vBulletin">
    INSERT INTO "userfield"(userid, field5, field6, field7, field8, field9, field10, field11, field12, field13, field14, field15, field16, field17, field18, field19, field20, field21, field22, field23, field24)
    VALUES (last_insert_id(), '#qGetMSSQL.Other_Surname#', '#qGetMSSQL.Service_Number#', '#qGetMSSQL.Homepage#', '#qGetMSSQL.Branch_Trades#', '#qGetMSSQL.Bases_Served#', '#qGetMSSQL.Squadrons_Served#', '#qGetMSSQL.Year_Start#', '#qGetMSSQL.Year_End#', '#qGetMSSQL.Other_Comments#', '#qGetMSSQL.Firstname#', '#qGetMSSQL.surname#', '#qGetMSSQL.nickname#', '#qGetMSSQL.Middlename#', '#qGetMSSQL.Address1#', '#qGetMSSQL.Address2#', '#qGetMSSQL.Address3#', '#qGetMSSQL.Address4#', '#qGetMSSQL.Address5#', '#qGetMSSQL.Address6#', <cfif qGetMSSQL.Service EQ 1>'Royal Air Force'<cfelseif qGetMSSQL.Service EQ 2>'Royal New Zealand Air Force'<cfelseif qGetMSSQL.Service EQ 3>'Royal Australian Air Force'<cfelseif qGetMSSQL.Service EQ 4>'Royal Canadian Air Force'<cfelseif qGetMSSQL.Service EQ 5>'British Army'<cfelseif qGetMSSQL.Service EQ 6>'Royal Navy'<cfelseif qGetMSSQL.Service EQ 7>'Australian Army'<cfelseif qGetMSSQL.Service EQ 8>'Australian Navy'<cfelseif qGetMSSQL.Service EQ 9>'Canadian Army'<cfelseif qGetMSSQL.Service EQ 10>'Canadian Navy'<cfelse>'Non-Military'</cfif>)
    </cfquery>

</cfloop> 

[rawnet]

PLEASE - Anybody - can anyone help with this? I am entirely stuck and can progress no further with my site (which reunites ex-servicemen and women) until this is finished. Any ideas guys/gals?

Ross

[DaveMaxwell]

It could be a couple things:

1) I believe password is a reserved word in MS SQL. You might need to change that field name in the table
2) You may need to add single quotes around the date variables.
3) Make sure the date is valid and that all the fields are being passed as the correct data type (ie numeric fields are numeric, the dates are valid, etc)

Hope this gives you someplace to start. Wish I could be more help but I know just about squat about CF.