Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    Regular Coder
    Join Date
    Nov 2007
    Posts
    682
    Thanks
    319
    Thanked 1 Time in 1 Post

    INSERT INTO error?

    Apparently I have a syntax error

    Code:
    Unable to add DSC075743 to database! Syntax error in INSERT INTO statement. 
    INSERT INTO images (jpg,set,sdate,sold) VALUES ('DSC075743','3','08062013','0')
    
    Unable to add DSC098743 to database! Syntax error in INSERT INTO statement. 
    INSERT INTO images (jpg,set,sdate,sold) VALUES ('DSC098743','3','08062013','0')
    
    Unable to add DSC098987 to database! Syntax error in INSERT INTO statement. 
    INSERT INTO images (jpg,set,sdate,sold) VALUES ('DSC098987','3','08062013','0')
    But I fail to see it, here is the code
    Code:
    set folder = fs.GetFolder(from)
    set fileList = folder.files
    
    'Connect to database ready
    set conn=Server.CreateObject("ADODB.Connection")
    conn.Provider="Microsoft.ACE.OLEDB.12.0"
    conn.Open Server.MapPath("/nightclub_photography/data/database/jamsnaps.mdb")
    
    'SQL Query Start
    sql_s = "INSERT INTO images (jpg,set,sdate,sold) VALUES "
    
    'Add each file an array for checking once moved
    for each i in fileList
    	if fs.GetExtensionName(i) = lcase(ftype) THEN
    		data.add counter, i.name
    		
    		'Increase counters by 1
    		num = num + 1
    		counter = counter + 1
    		
    		'Get the jpg name ready to add to database
    		jpg_name = split(i.name, ".")
    		sql=sql_s & "('"  & jpg_name(0)
    		sql=sql & "','" & uploadNum
    		sql=sql & "','" & sdate
    		sql=sql & "','0')"
    		on error resume next
    		
    		'Add images to database
    		conn.Execute sql,recaffected
    				
    		'Check if image has been added to database
    		if err<>0 then
      			Response.Write("Unable to add "&jpg_name(0)&" to database! "&Err.Description&" <br />"&sql&"<br /><br />")
    		else
    			Response.Write("<h3>" & recaffected & " added to database!</h3>")
    		end if
    		'Clear values ready for next
    		sql = sql_s
    	end if
    next
    The database table's columns are named correctly and match the code...

    Edit: offtopic, but does codingforums have syntax highlighting for other code aside from PHP?
    Last edited by martynball; 06-08-2013 at 05:07 PM.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,574
    Thanks
    80
    Thanked 4,620 Times in 4,583 Posts
    Ummm...I see what I think are a couple of problems.

    But you need to tell us what the DATA TYPES are for those fields in you Access DB:
    jpg,set,sdate,sold

    *SURELY* sdate is a DATETIME column? No? If not, it clearly SHOULD be. You will never be able to do queries such as "get me all records added in the last month" unless it is. If it *is* DATETIME, then your syntax for a date value ('08062013') is utterly wrong. If it's not, fix it.

    And surely sold and set are numeric fields of some kind???? If so, then you can't put '...' around values intended to be stored in a numeric field. (MySQL allows such sloppiness; Access is smarter than MySQL here and doesn't.)

    Finally, I think maybe that set is a keyword in Access SQL. If so, that's no big deal, but then you need to put the name in [...] to tell Access to treat it as a column name and not a keyword.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    martynball (06-09-2013)

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,574
    Thanks
    80
    Thanked 4,620 Times in 4,583 Posts
    Anyway, use Access to view the description of that table and copy/paste it here.

    If you don't use the right data types for the right purposes, you might as well not bother to use a database.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #4
    Regular Coder
    Join Date
    Nov 2007
    Posts
    682
    Thanks
    319
    Thanked 1 Time in 1 Post
    Jpg = short text (around 32 chars)
    Sdate = short text, I want the date to be the way it is, but I could change it but I prefer it the way it is for other reasons
    Set = numerical field
    Sold = numerical field

  • #5
    Regular Coder
    Join Date
    Nov 2007
    Posts
    682
    Thanks
    319
    Thanked 1 Time in 1 Post
    Changed the "set" column to "img_set" and changed the "sdate" column type to date/time type and changed the date format to ##/##/####.

    Also changed the query so the numerical fields aren't saved as strings. Works fine now.

  • #6
    Regular Coder
    Join Date
    Nov 2007
    Posts
    682
    Thanks
    319
    Thanked 1 Time in 1 Post
    One thing I am confused about is this. Ive added another date/time field, im adding a value of 05:16 but in the database it is added as 05:16:00 AM.

    How do I write a query to search for all records with that time as the following isn't working:
    "select * from images WHERE upload_time = '05:16'"

    Getting error:
    Data type mismatch in criteria expression.

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,574
    Thanks
    80
    Thanked 4,620 Times in 4,583 Posts
    Dates and times in Access *MUST* be surrounded by #...# instead of '...'.

    So:
    ... WHERE upload_time = #5:10 AM#
    if you omit the AM/PM, then 24-hour clock is assumed. So you could do
    ... WHERE upload_time = #7:32 PM#
    *OR*
    ... WHERE upload_time = #19:32#

    One thing to DEFINITELY watch out for: DATES in Access *MUST* be in either USA format (#mm/dd/yyyy#) or ISO format (#yyyy/mm/dd#). They can *NOT* be in UK format (#dd/mm/yyyy#) unfortunately.

    I strongly recommend using ISO format. So you can use a VBS function to help do that:
    Code:
    Function ISODate( dt )
        Dim iso 
        iso = year(dt) & "/" & month(dt) & "/" & day(dt)
        If TimeValue(dt) <> 0 Then iso = iso & " " & TimeValue(dt)
        ISODate = "#" & iso & "#"
    End Function
    
    ' and then use it in a query thus:
    sql = "SELECT * FROM tbl WHERE dtfield = " & ISODate(someVBScriptDateTime)
    Oh...and you can use DATEVALUE() and TIMEVALUE() In Access queries, as well.

    For example, if you wanted to find all records where upload_time is between 5PM and 6PM, *regardless* of what DATE they were uploaded:
    Code:
    sql = "SELECT * FROM images WHERE TIMEVALUE(upload_time) BETWEEN #5:00PM# AND #6:00PM#"
    Oh...and pleas don't tell me you have both upload_date and upload_time. No reason at all to do that. Just use upload_time (or upload_date_time, perhaps) to hold a DATETIME value which is both.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    martynball (06-09-2013)

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,574
    Thanks
    80
    Thanked 4,620 Times in 4,583 Posts
    And I just notice you wrote this:
    and changed the date format to ##/##/####
    Umm...no, you didn't. Or at least you didn't so far as VBScript is concerned.

    Read this:
    http://www.aspfaqs.com/aspfaqs/ShowFAQ.asp?FAQID=189

    Wow. More than 11 years ago.

    "Time is fun when you are having flies." -- Kermit the Frog
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    martynball (06-09-2013)

  • #9
    Regular Coder
    Join Date
    Nov 2007
    Posts
    682
    Thanks
    319
    Thanked 1 Time in 1 Post
    Getting an error from your code:
    Code:
    Microsoft VBScript runtime error '800a000d'
    
    Type mismatch: 'TimeValue'
    PHP Code:
    function isoDate(vbdatetime)
        
    dim iso
        iso 
    year(dt) & "/" month(dt) & "/" day(dt)
        If 
    TimeValue(dt) <> 0 Then
            iso 
    iso " " TimeValue(dt)
            
    ISODate "#" iso "#"
        
    End If
    end function

    response.write(isoDate("201-06-09 13:45")) 

  • #10
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,574
    Thanks
    80
    Thanked 4,620 Times in 4,583 Posts
    Ready to kick yourself?
    Code:
    function isoDate(vbdatetime)
        dim iso
        iso = year(dt) & "/" & month(dt) & "/" & day(dt)
        If TimeValue(dt) <> 0 Then
    etc.
    You never defined any variable dt so why did you expect to be able to use one of that name?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    martynball (06-09-2013)

  • #11
    Regular Coder
    Join Date
    Nov 2007
    Posts
    682
    Thanks
    319
    Thanked 1 Time in 1 Post
    Dammit! Thanks for that don't know how I missed it.

    Whilst you here an offtopic question, do HTA files support event listeners?

  • #12
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,574
    Thanks
    80
    Thanked 4,620 Times in 4,583 Posts
    Have never used HTA files, so no idea.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #13
    Regular Coder
    Join Date
    Nov 2007
    Posts
    682
    Thanks
    319
    Thanked 1 Time in 1 Post
    Okay thanks, another question. What is the best way to check to see if a cookie EXISTS with ASP, and if it doesn't then create one.

  • #14
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,574
    Thanks
    80
    Thanked 4,620 Times in 4,583 Posts
    There isn't any really good way.

    I just usually do
    Code:
    If Trim("" & Request.Cookies("whatever")) = "" Then
        ... the "whatever" cookie either does not exist or is blank ...
    Else
        ... the "whatever" cookie exists
    End If
    Because I *KNOW* that I will never assign a blank value to a cookie, that works for me.

    But you *can * do it a bit more painfully:
    Code:
    Function doesCookieExist( name )
        name = LCase(name)
        For Each key In Request.Cookies
            If LCase(key) = name Then
                doesCookieExist = True
                Exit Function
            End If
        Next
        doesCookieExist = False
    End Function
    
    If doesCookieExist( "zamboni" ) Then
        ... cookie named "zamboni" exists ...
    Else
       ... does not exist ...
    End If
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    martynball (06-10-2013)

  • #15
    Regular Coder
    Join Date
    Nov 2007
    Posts
    682
    Thanks
    319
    Thanked 1 Time in 1 Post
    Okay thanks, I ended up doing it similar to your second example just before you replied.


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

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