469,890 Members | 1,499 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,890 developers. It's quick & easy.

junction table

Hello,

I have a questinarrie webform I use INSERT statement(s) to insert a new record in the User and Questions tables and then (HERE IS MY PROBLEM) I need to update my junction table (QuestionScores - has two fields; questionid and scoreID).

What should be a statement to update my junction table?
This is my code except updates to junction table:
Expand|Select|Wrap|Line Numbers
  1.  
  2. <%
  3. ' Declaring variables
  4. Dim tmpFullName, tmpAge, tmpDateDB, tmpMedicalRecord, tmpExamOne, tmpExamTwo, Question1, Question2, Question3, data_source, conn, conOne, sql_insert
  5.  
  6.  
  7. ' Receiving values from Form
  8. tmpName = (Request.Form("FullName"))
  9. tmpAge = (Request.Form("age"))
  10. tmpDateDB = (Request.Form("dateDB"))
  11.      tmpMedicalRecord = (Request.Form("MedicalRecord"))
  12.  
  13.      Set objConn = Server.CreateObject("ADODB.Connection")
  14.         objConn.connectionstring = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" _ 
  15. & Server.Mappath("Questions.mdb") & ";" 
  16. objConn.Open
  17.  
  18.     SQLstmt = "INSERT INTO users (txtFullName, intAge, txtDateDB, intMedicalRecord)"
  19.     SQLstmt = SQLstmt & " VALUES (" 
  20.     SQLstmt = SQLstmt & "'" & tmpName & "',"
  21.     SQLstmt = SQLstmt & "'" & tmpAge & "',"
  22.     SQLstmt = SQLstmt & "'" & tmpDateDB & "',"
  23.     SQLstmt = SQLstmt & "'" & tmpMedicalRecord & "'"
  24.  
  25.  
  26.     SQLstmt = SQLstmt & ")"
  27.     SET RS = objConn.Execute(SQLstmt)
  28.  
  29.     SQLstmt = "SELECT MAX(idUsers) as MaxUserId FROM Users;" 
  30.     Set RS=objConn.Execute(SQLstmt) 
  31.     thisRecord = rs("MaxUserId")
  32.  
  33.     For x = 0 to 30
  34.  
  35.  
  36.     tmpAnswer = Request.Form("Question" & x)
  37.     SQLstmt = "INSERT INTO Questions (idUser, QuestionNumber, QuestionAnswer)"
  38.     SQLstmt = SQLstmt & " VALUES (" 
  39.     SQLstmt = SQLstmt & "'" & thisRecord & "',"
  40.     SQLstmt = SQLstmt & "'" & x & "',"
  41.     SQLstmt = SQLstmt & "'" & tmpAnswer & "'"
  42.     SQLstmt = SQLstmt & ")"
  43.  
  44.     SET RS = objConn.Execute(SQLstmt)
  45.     next
  46.  
  47.  
  48.  
  49. objConn.Close
  50. Set objConn = Nothing
  51.  
  52. Response.Write "All records were successfully entered into the database."
  53.  
  54.  
  55. %>
  56.  
Apr 18 '08 #1
3 2346
DrBunchman
979 Expert 512MB
Hi dbertanjoli,

The SQL syntax for updating a database is as follows:
Expand|Select|Wrap|Line Numbers
  1. sSQL = "UPDATE Table SET Column1 = '" & Value1 & "', Column2 = '" & Value2 & "' "
Hope this helps,

Dr B
Apr 19 '08 #2
Hello,
I tried but it doesn't work. Please see my code above. I just want newly made id to insert into my id table.
Apr 19 '08 #3
DrBunchman
979 Expert 512MB
You need to be careful with your terminology - UPDATE and INSERT are two different things in relation to SQL hence my reply above.

INSERT creates a new record

UPDATE changes an existing record

To use a newly created ID you can use @@identity. To make use of it I would recommend that you create a stored procedure to do your inserts. There is an example of the use of @@identity here.

Or if you are sure that the last record in the table is going to be the one you just created you can use the following bit of SQL to get the newly created ID:
Expand|Select|Wrap|Line Numbers
  1.  SELECT Top 1 ID FROM Table ORDER BY ID DESC
Hope this helps. If you need any more help with this then let me know.

Dr B
Apr 19 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by mksql | last post: by
8 posts views Thread by Marcy | last post: by
1 post views Thread by LurfysMa | last post: by
2 posts views Thread by Henry Stockbridge | last post: by
reply views Thread by Salome Sato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.