junction table

[dbertanjoli]

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:

 
<%
' Declaring variables
Dim tmpFullName, tmpAge, tmpDateDB, tmpMedicalRecord, tmpExamOne, tmpExamTwo, Question1, Question2, Question3, data_source, conn, conOne, sql_insert
 
 
' Receiving values from Form
tmpName = (Request.Form("FullName"))
tmpAge = (Request.Form("age"))
tmpDateDB = (Request.Form("dateDB"))
	 tmpMedicalRecord = (Request.Form("MedicalRecord"))
 
	 Set objConn = Server.CreateObject("ADODB.Connection")
		objConn.connectionstring = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" _ 
& Server.Mappath("Questions.mdb") & ";" 
objConn.Open
 
	SQLstmt = "INSERT INTO users (txtFullName, intAge, txtDateDB, intMedicalRecord)"
	SQLstmt = SQLstmt & " VALUES (" 
	SQLstmt = SQLstmt & "'" & tmpName & "',"
	SQLstmt = SQLstmt & "'" & tmpAge & "',"
	SQLstmt = SQLstmt & "'" & tmpDateDB & "',"
	SQLstmt = SQLstmt & "'" & tmpMedicalRecord & "'"
 
 
	SQLstmt = SQLstmt & ")"
	SET RS = objConn.Execute(SQLstmt)
 
	SQLstmt = "SELECT MAX(idUsers) as MaxUserId FROM Users;" 
	Set RS=objConn.Execute(SQLstmt) 
	thisRecord = rs("MaxUserId")
 
	For x = 0 to 30
 
 
	tmpAnswer = Request.Form("Question" & x)
	SQLstmt = "INSERT INTO Questions (idUser, QuestionNumber, QuestionAnswer)"
	SQLstmt = SQLstmt & " VALUES (" 
	SQLstmt = SQLstmt & "'" & thisRecord & "',"
	SQLstmt = SQLstmt & "'" & x & "',"
	SQLstmt = SQLstmt & "'" & tmpAnswer & "'"
	SQLstmt = SQLstmt & ")"
 
	SET RS = objConn.Execute(SQLstmt)
	next
 
 
 
objConn.Close
Set objConn = Nothing
 
Response.Write "All records were successfully entered into the database."
 
 
%>

[DrBunchman]

Hi dbertanjoli,

The SQL syntax for updating a database is as follows:

sSQL = "UPDATE Table SET Column1 = '" & Value1 & "', Column2 = '" & Value2 & "' "

Hope this helps,

Dr B

[dbertanjoli]

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.

[DrBunchman]

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:

 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