inserting problem

Hi i am having problems inserting data into my table. The site works in the following way. The user enters their details on the join page. this data is then inserted into the members table. The researchArea should be inserted and a copy of the memberid should be inserted into the MemberResearchArea table. But it is not doing this. I have attached a sample of the code so that you can see if you can identify what the problem is.



'-----------------------------------------------------------------------------------------
' Select member's details from Members table
'------------------------------------------------------------------------------------------
Dim RsMembers
strDSN4="CIPD2,CIPDuser,C1PD2"
Set CN4=Server.CreateObject("ADODB.Connection") 'make a connection
CN4.Open "CIPD2","CIPDuser","C1PD2" 'open connection
Set RsMembers=Server.CreateObject("ADODB.Recordset") 'create a recordset
strSQL4="Select firstname,surname,memberid from members where members.firstname='" & Request.form("firstname") & "' AND Members.Surname='" & request.form("Surname") & "' AND Members.Username='" & request.form("username") & "';"
RsMembers.Open strSQL4, CN4

'response.write "expertisearea: " & request.form("expertisearea") & "
"

Dim MemberResearchAreastring
MemberResearchAreastring=request.form("researcharea")

Dim MemberResearcharray

MemberResearcharray=Split(MemberResearchAreastring, ",")

'response.write "aaray pos 0:" & expertarray(0) & "
"

'response.write "aaray pos 1:" & expertarray(1)

dim maxiarray
maxiarray=UBound( MemberResearcharray)
'Response.Write "
" & maxarray
Dim membid
membid=RsMembers("memberid")

Dim counter
counter=0
Dim varresearch

For i=counter to maxiarray

varresearch=maxiarray(counter)


'-----------------------------------------------------------------------------------
' Insert row into MemberResearchAreas ie MemberID, ResearchAreaID
'-----------------------------------------------------------------------------------

Dim RsMemberResearch
strDSN5="CIPD2,CIPDuser,C1PD2"
Set CN5=Server.CreateObject("ADODB.Connection") 'make a connection
CN5.Open "CIPD2","CIPDuser","C1PD2" 'open connection
Set RsMemberResearch=Server.CreateObject("ADODB.Recordset") 'create a recordset
strSQL5="Insert into MemberResearchArea (Memberid,ResearchAreaId) VALUES ('"& membid &"','" & varresearch & "');"
RsMemberResearch.Open strSQL5, CN5

counter=counter+1

Next
CN4.Close
CN5.Close

Thanks for your help

Comments

  • For your insert statements you should try not using a adodb.recordset object. You can just use Connection.Execute(strQueryString)

    It would be faster to track this down if you could put a breakpoint where you currently have the Recordset.Open() calls and note what the SQL strings look like. Since these are built dynamically its hard to say exactly what they look like when the app isn't running.

    Once you get the SQL strings, try executing them from MS Query. If they work, its the asp. If they don't, well then they don't. You should be able to see if its malformed SQL, key violations, etc.

    -Ray

    : Hi i am having problems inserting data into my table. The site works in the following way. The user enters their details on the join page. this data is then inserted into the members table. The researchArea should be inserted and a copy of the memberid should be inserted into the MemberResearchArea table. But it is not doing this. I have attached a sample of the code so that you can see if you can identify what the problem is.
    :
    :
    :
    : '-----------------------------------------------------------------------------------------
    : ' Select member's details from Members table
    : '------------------------------------------------------------------------------------------
    : Dim RsMembers
    : strDSN4="CIPD2,CIPDuser,C1PD2"
    : Set CN4=Server.CreateObject("ADODB.Connection") 'make a connection
    : CN4.Open "CIPD2","CIPDuser","C1PD2" 'open connection
    : Set RsMembers=Server.CreateObject("ADODB.Recordset") 'create a recordset
    : strSQL4="Select firstname,surname,memberid from members where members.firstname='" & Request.form("firstname") & "' AND Members.Surname='" & request.form("Surname") & "' AND Members.Username='" & request.form("username") & "';"
    : RsMembers.Open strSQL4, CN4
    :
    : 'response.write "expertisearea: " & request.form("expertisearea") & ""
    :
    : Dim MemberResearchAreastring
    : MemberResearchAreastring=request.form("researcharea")
    :
    : Dim MemberResearcharray
    :
    : MemberResearcharray=Split(MemberResearchAreastring, ",")
    :
    : 'response.write "aaray pos 0:" & expertarray(0) & ""
    :
    : 'response.write "aaray pos 1:" & expertarray(1)
    :
    : dim maxiarray
    : maxiarray=UBound( MemberResearcharray)
    : 'Response.Write "" & maxarray
    : Dim membid
    : membid=RsMembers("memberid")
    :
    : Dim counter
    : counter=0
    : Dim varresearch
    :
    : For i=counter to maxiarray
    :
    : varresearch=maxiarray(counter)
    :
    :
    : '-----------------------------------------------------------------------------------
    : ' Insert row into MemberResearchAreas ie MemberID, ResearchAreaID
    : '-----------------------------------------------------------------------------------
    :
    : Dim RsMemberResearch
    : strDSN5="CIPD2,CIPDuser,C1PD2"
    : Set CN5=Server.CreateObject("ADODB.Connection") 'make a connection
    : CN5.Open "CIPD2","CIPDuser","C1PD2" 'open connection
    : Set RsMemberResearch=Server.CreateObject("ADODB.Recordset") 'create a recordset
    : strSQL5="Insert into MemberResearchArea (Memberid,ResearchAreaId) VALUES ('"& membid &"','" & varresearch & "');"
    : RsMemberResearch.Open strSQL5, CN5
    :
    : counter=counter+1
    :
    : Next
    : CN4.Close
    : CN5.Close
    :
    : Thanks for your help
    :
    :

  • What is Memberid on the db?
    If it's an int you dont need '' when you building query
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories