SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    Online Dating Guru rawnet's Avatar
    Join Date
    Jan 2002
    Location
    Windsor, UK
    Posts
    246
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

  2. #2
    Online Dating Guru rawnet's Avatar
    Join Date
    Jan 2002
    Location
    Windsor, UK
    Posts
    246
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Exclamation ARGGGH!!!

    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 :

    PHP Code:
    <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"(usergroupidusernamepasswordemailstyleidadminemailshowemailinvisiblejoindatecookieuserlastvisitreceivepmemailonpmpmpopup)
        
    VALUES (2, <cfif qGetMSSQL.screenname EQ "">'#qGetMSSQL.firstname##qGetMSSQL.surname#',<cfelse>'#qGetMSSQL.screenname#',</cfif'#qGetMSSQL.Password#''#qGetMSSQL.Email_Address#'0100100266176711002661767111)
        </
    cfquery>
        
        <
    cfquery name="qPutMySQLUserField" datasource="vBulletin">
        
    INSERT INTO "userfield"(useridfield5field6field7field8field9field10field11field12field13field14field15field16field17field18field19field20field21field22field23field24)
        
    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

  3. #3
    Online Dating Guru rawnet's Avatar
    Join Date
    Jan 2002
    Location
    Windsor, UK
    Posts
    246
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Bump Bump

    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

  4. #4
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,267
    Mentioned
    116 Post(s)
    Tagged
    1 Thread(s)
    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.
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse


Bookmarks

Posting Permissions

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