By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,061 Members | 1,563 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,061 IT Pros & Developers. It's quick & easy.

junction table

P: 10
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
Share this Question
Share on Google+
3 Replies


DrBunchman
Expert 100+
P: 979
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

P: 10
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
Expert 100+
P: 979
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.