INSERT problem with autonumber field

Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: INSERT problem with autonumber field

  1. #1
    Anne-Marie Guest

    Default INSERT problem with autonumber field

    Hi<BR><BR>I&#039ve been trying to insert new data into my database using SQL INSERT. The only problem I&#039m having is that when I insert data into some tables the autonumber field does not show the next number in the sequence. For example, in one table the last record was 1669 and then when I used INSERT to add a record, the number given by the autonumber field was 2039. My INSERT query is as follows:<BR>tempSQL = " INSERT INTO Datetab(Hour, Day, Month, Year, [Partially assumed date])"<BR>tempSQL = tempSQL & " VALUES (&#039" & hour & "&#039, " <BR>tempSQL = tempSQL & "&#039" & day & "&#039, "<BR>tempSQL = tempSQL & "&#039" & month & "&#039, "<BR>tempSQL = tempSQL & "&#039" & year & "&#039, "<BR>tempSQL = tempSQL & assume & ") "<BR><BR>I haven&#039t included the autonumber field (recnumb) in the INSERT statement, is this my problem??<BR><BR>I&#039d appreciate any help<BR><BR>thanks <BR><BR>Anne-Marie

  2. #2
    Join Date
    Dec 1969
    Posts
    1,315

    Default RE: INSERT problem with autonumber field

    There is no need to include the autonumber field (recnumb)in<BR>the INSERT statement, but your last line might be the error.<BR>In the last line, I would try:<BR>tempSQL = tempSQL & "&#039" & assume & "&#039" & ")"<BR><BR>See what happens if you try that.<BR><BR>John Weeflaar

  3. #3
    Anne-Marie Guest

    Default RE: INSERT problem with autonumber field

    Thanks for the suggestion but the last field is a yes/no field in Access so if I put quotes around it then it produces an error. <BR><BR>Any other suggestions are welcome.<BR><BR>Anne-Marie

  4. #4
    Join Date
    Dec 1969
    Posts
    1,315

    Default RE: INSERT problem with autonumber field

    OK. Because assume is a yes/no value, I assume it is a number.<BR>So, you have to ged rid of the single apostrophes (in the middle).<BR>Thus, I would write (in the last line):<BR><BR>tempSQL = tempSQL & "" & assume & "" & ")"<BR><BR>Let me know if it still doesn&#039t work, as well as the all data types you are using.<BR><BR>John Weeflaar


  5. #5
    Anne-Marie Guest

    Default RE: INSERT problem with autonumber field

    Thanks again for the suggestion. I tried it on the INSERT query that I&#039d put in my first post and it worked fine so I tried it on another but the autonumber field jumped from 1902 to 2303. The query is as follows (I&#039ve left out the execute line as I&#039ve been writing it to the screen and then copying it into Access to see what&#039s going on):<BR><BR>tempSQL = " INSERT INTO Official2([Source1], [Source2], [Source3], [Source-Other], "<BR>tempSQL = tempSQL & "[Transcript available], [Official report/synopsis available], [Reporting agency], [Report reference], [Synopsis reference], "<BR>tempSQL = tempSQL & "[Link to report/synopsis], [Link to transcript], [Official verdict])"<BR>tempSQL = tempSQL & " VALUES (" & "" & aircla & "" & ", " <BR>tempSQL = tempSQL & "" & asn & "" & ", "<BR>tempSQL = tempSQL & "" & safeboard & "" & ", "<BR>tempSQL = tempSQL & "&#039" & othsource & "&#039, "<BR>tempSQL = tempSQL & "" & traav & "" & ", "<BR>tempSQL = tempSQL & "" & offrepav & "" & ", " <BR>tempSQL = tempSQL & "&#039" & repage & "&#039, "<BR>tempSQL = tempSQL & "&#039" & repref & "&#039, "<BR>tempSQL = tempSQL & "&#039" & synref & "&#039, "<BR>tempSQL = tempSQL & "&#039" & synlink & "&#039, "<BR>tempSQL = tempSQL & "&#039" & tralink & "&#039, "<BR>tempSQL = tempSQL & "&#039" & offverd & "&#039) "<BR>response.write tempSQL<BR><BR>The data types are <BR>Source1 yes/no<BR>Source2 yes/no<BR>Source3 yes/no<BR>Source-Other text<BR>Transcript available yes/no<BR>Official report/synopsis available yes/no<BR>Reporting agency text <BR>Report reference text<BR>Synopsis reference text<BR>Link to report/synopsis text<BR>Link to transcript text<BR>Official verdict memo<BR><BR>Hope you can see what the problem is.<BR><BR>Thanks again<BR><BR>Anne-Marie

  6. #6
    Join Date
    Dec 1969
    Posts
    1,315

    Default RE: INSERT problem with autonumber field

    OK. I&#039ll start from the VALUES line, as above this is the same:<BR><BR>tempSQL = tempSQL & " VALUES(" & aircla & ", "<BR>tempSQL = tempSQL & "" & asn & ", "<BR>tempSQL = tempSQL & "" & safeboard & ", "<BR>tempSQL = tempSQL & "&#039" & othsource & "&#039, "<BR>tempSQL = tempSQL & "" & traav & ", "<BR>tempSQL = tempSQL & "" & offrepav & ", "<BR>tempSQL = tempSQL & "&#039" & repage & "&#039, "<BR>tempSQL = tempSQL & "&#039" & repref & "&#039, "<BR>tempSQL = tempSQL & "&#039" & synref & "&#039, "<BR>tempSQL = tempSQL & "&#039" & synlink & "&#039, "<BR>tempSQL = tempSQL & "&#039" & tralink & "&#039, "<BR>tempSQL = tempSQL & "&#039" & offverd & "&#039)"<BR>response.write tempSQL<BR><BR>one error is enough to make it not running.<BR>See if it runs.<BR><BR>John Weeflaar

  7. #7
    Anne-Marie Guest

    Default RE: INSERT problem with autonumber field

    Thanks again for your perservence with this. I&#039ve tried your suggestion but it jumped again from 1912 to 2313. Once it&#039s made this initial jump then it follows on with 2314,2315,2316 etc.<BR><BR>When I response.write the query I get:<BR>INSERT INTO Official2([Source1], [Source2], [Source3],<BR>[Source-Other], [Transcript available], [Official report/synopsis available], [Reporting agency], [Report reference], [Synopsis reference], [Link to report/synopsis], [Link to transcript], [Official verdict]) VALUES (yes, yes, yes, &#039&#039, yes, yes, &#039&#039, &#039&#039, &#039&#039, &#039&#039, &#039&#039, &#039&#039) which looks ok to me although I&#039m no expert.<BR><BR>Anne-Marie

  8. #8
    Join Date
    Dec 1969
    Posts
    1,315

    Default RE: INSERT problem with autonumber field

    Well, I&#039m a learner myself rather than an expert, but it&#039s<BR>good not to give up when getting this kind of problem.<BR>OK, I don&#039t know if this makes any difference, but can you<BR>try to replace the last line with the following:<BR><BR>tempSQL = tempSQL & "&#039" & offverd & "&#039" & ")"<BR><BR>See again what is the result. Thanks<BR><BR>John Weeflaar

  9. #9
    test Guest

    Default RE: INSERT problem with autonumber field

    oooooooooooooooooooooooooooooooooooooooooooooooooo oooooooooooooooooooooooooooooooooooooooooooooooooo oooooooooooooooooooooooooooooooooooooooooooooooooo oooooooooooooooooooooooooooooooooooooooooooooooooo oooooooooooooooooooooooooooooooooooooooooooooooooo oooooooooooooooooooooooooooooooooooooooooooooooooo oooooooooooooooooooooooooooooooooooooooooooooooooo oooooooooooooooooooooooooooooooooooooooooooooooooo oooooooooooooooooooooooooooooooooooooooooooooooooo oooooooooooooooooooooooooooooooooooooooooooooooooo oooooooooooooooooooooooooooooooooooooooooooooooooo oooooooooooooooooooooooooooooooooooooooooooooooooo oooooooooooooooooooooooooooooooooooooooooooooooooo oooooooooooooooooooooooooooooooooooooooooooooooooo oooooooooooooooooooooooooooooooooooooooooooooooooo oooooooooooooooooooooooooooooooooooooooooooooooooo oooooooooooooooooooooooooooooooooooooooooooooooooo oooooooooooooooooooooooooooooooooooooooooooooooooo oooooooooooooooooooooooooooooooooooooooooooooooooo oooooooooooooooooooooooooooooooooooooooooooooooooo oooooooooooooooooooooooooooooooooooooooooooooooooo ooooooooooooooo

  10. #10
    test Guest

    Default RE: INSERT problem with autonumber field

    &#060;table><BR>&#060;tr>?<BR>&#060;td?>sadasd ad

Posting Permissions

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