473,326 Members | 2,173 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

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 2554
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

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

Similar topics

1
by: mksql | last post by:
As an example, I am building an authentication mechanisim that will use data in the 3 left tables to determine rights to objects in a destination table, diagrammed below. In this structure,...
8
by: Marcy | last post by:
I am trying to build a database to keep track of training topics completed by people in my department. Our department has a set of 37 training topics. There are 7 job classifications in the...
1
by: Riley DeWiley | last post by:
I have an application with two tables, A and B. Each has an autonumber unique ID field, plus other data. I have a junction table, AB, containing fields AID, BID, and Count (a number). AB has...
1
by: LurfysMa | last post by:
I am working on an electronic flashcard program. Most of the subjects are simple lists of questions and answers. Those seem to be working. Some of the "subjects" have "categories" of questions. ...
3
by: inthemix | last post by:
My goal is to have a sub-form located on the main form which containts a listbox. The user will be able to select anany number of offices (by officeName) from this listbox. IMO the design is very...
1
by: que576 | last post by:
I have created a junction table so that I can relate data from 2 other tables. Table 1 - Category Table (has the following fields with data) CatID (primary key) CategoryName CategoryStatus ...
2
by: Henry Stockbridge | last post by:
Hi, I need a recommendation when to add a record to a junction table that complements a many to many relationship. There will be a Contacts form, and an Interests subform with the parent/child...
1
by: bg_ie | last post by:
I'm designing a database with 3 tables called Function, Test and Scene. A Function has multiple Tests, but a Test has only one Function. A many to many relationship exists between Test and Scene...
1
by: pwebbie | last post by:
In an MS Access Project tied to SQL Server, I am trying to create a data entry form that allows the user to enter info about a law, and then (in a datasheet) edit/insert related web site records...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.