insert related records

Haroldd · April 13th, 2004, 03:56 AM

hi,

i have a few related tables. When i insert a new record in the table "question" i also need to insert a record in the table "answer". To insert into "answer" i need the questionId from the record i've created in "question".
It is going wrong somewhere because when i try to get questionid from "question" it seems that this record hasn't been inserted yet. When i assign a static value to tempId it works fine, so nows my question: how to get the appropriate questionId so that i can insert a record into answer?

see below for my code

Code:

select case lcase(strType)
case "add"
    sql="insert into question (subjectId,question) values (" & request.form("selSubject") & ",'" & request.form("edQuestion") & "')"
    cmdTemp.CommandText = sql

    cmdTemp.CommandType = 1
    Set cmdTemp.ActiveConnection = adoCon

    cmdTemp.execute

    sql="select questionId from question where subjectId=" & request.form("selSubject")) & " and question='" & request.form("edQuestion") & "'"
    cmdTemp.CommandText = sql

    cmdTemp.CommandType = 1
    Set cmdTemp.ActiveConnection = adoCon
    rs.open cmdTemp
    if not rs.bof then
        tempid=RS("questionId")
    end if
    rs.close
    'tempid=41
    sql="insert into answer (questionid,numberRight,numberWrong) values (" & tempid & ",0,0)"

case "update"
    'update-statements

case "delete"
    'delete-statements

end select

cmdTemp.CommandText = sql

cmdTemp.CommandType = 1
Set cmdTemp.ActiveConnection = adoCon

cmdTemp.execute
response.redirect "adminQuestion.asp?subjectId=" & request.form("selSubject")

Thanks,

Harold

happygv · April 15th, 2004, 02:09 PM

Did you try commenting these line and check in your database, to see if the Question Rowd that you inserted is available???

Cheers!

-Vijay G

happygv · April 15th, 2004, 02:10 PM

Sorry, I forgot to mention the lines to be commented in my previous post.

'Comment the following and see if you get the value inserted in your question table.

sql="select questionId from question where subjectId=" & request.form("selSubject")) & " and question='" & request.form("edQuestion") & "'"
    cmdTemp.CommandText = sql

    cmdTemp.CommandType = 1
    Set cmdTemp.ActiveConnection = adoCon
    rs.open cmdTemp
    if not rs.bof then
        tempid=RS("questionId")
    end if
    rs.close
    'tempid=41
    sql="insert into answer (questionid,numberRight,numberWrong) values (" & tempid & ",0,0)"

Cheers!

-Vijay G