Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

check field on two tables 1

Status
Not open for further replies.
evr72

evr72

MIS
Dec 8, 2009
265
US
Hello,

I am not sure how to approach this one.
I have two tables, ansers and questions.
The user has to log in, at log in I check if the user has any answers by doing a count if the count is zero then I redirect the user to an autosubmit page that populates the questions, then the user can log in and answer each question. If the user already has some questions populated then I redirect the user to be able to edit each question.
Up to this point I am ok all works well.
Where I am stuck is when the admin adds more questions, not sure how to add those to each user.

My ansers table has itm, userid, and about 5 answer fields.
My questions table has itm, and an X number of questions.

I was trying to do this
Code: 
Dim objconn,objRS,strSQL
Set objconn = Server.CreateObject("ADODB.Connection")
objconn.ConnectionString = "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" & Server.MapPath("qa.mdb")
objconn.Open

Set objRs = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT * FROM questions right JOIN answers ON (answers.itm = questions.itm) WHERE (((username)is null));"
objRS.Open strSQL, objconn

Do While Not objRS.EOF 

Response.Write objRs("questions.itm") & " " & objRs("answers.username") & "<br>"
objRS.MoveNext
Loop
objRs.Close
objconn.Close

and I get no results

in the questions table I have 7 records in the itm field
in the answers table I have 6 records in the itm field

the records are the itm id

1,2,3,4, etc

any help as always is welcome and appreciated!!
 
Sort by date Sort by votes
I'm not sure about your database structure. Better post iot here (all fields per table involved).
The QUESTION table could be a simple list, eg:
Question_id (unique id)
QuestionNumber (in order to sort right)
Question

"My ansers table has itm, userid, and about 5 answer fields"
5 answer FIELDS?? A question has 1 answer, eg Yes or No. Or 1 value on a 1-5 scale. Or 1 text answer.
Your ANSWER table could look like this:
Answer_id (unique id)
User_id
Question_id
Answer


So now when u add a question u determine the added Question_id. Then follows this INSERT


INSERT INTO [Answers] (user_id,question_id, answer)
SELECT distinct(user_id), {added_question_id}, {default_answer?}
FROM Answers
 
Upvote 0 Downvote
foxbox,
the tables that I have are

questions

itm (this is the question id)
cycle
functionalarea
controldescription

answers

itm
cycle
functionalarea
userid
company
username
resp
explain
narrativeofcontrol
personresponsable
manualorautomated



users

userid
username
company
email


This is what I am using to insert the questions

Code: 
<%@ Language="VBScript" %>
<% Option Explicit %>
<html>
<head>
<title>Form to database</title>
</head>
<body>
<%
Function IsInteger(Data)

  If Trim(Data) = "" Then
    IsInteger = False
  Else
    IsInteger = IsNumeric(Data & ".0e0")
  End If

End Function



'declare your variables
Dim itm, userid, username, company, i
Dim sConnString, connection, sSQL
'Receiving values from Form, assign the values entered to variables
itm = Split(Request.Form("itm"),",")

userid = Split(Request.Form("userid"),",")

username = Split(Request.Form("username"),",")

company = Split(Request.Form("company"),",")

'define the connection string, specify database
'driver and the location of database
sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("qa.mdb")
'create an ADO connection object
Set connection = Server.CreateObject("ADODB.Connection")

'Open the connection to the database
connection.Open(sConnString)
sSQL = "INSERT into signed (userid) values ('" & _
trim(userid(i)) & "') "
  connection.execute(sSQL)


For i = lBound(userid) to ubound(userid)


  'declare SQL statement that will query the database
  sSQL = "INSERT into answers (itm, userid, username, company) values ('" & _
trim(itm(i)) & "', '" & trim(userid(i)) & "', '" & trim(username(i)) & "', '" & trim(company(i)) & "')"
  connection.execute(sSQL)
Next


'execute the SQL
' connection.execute(sSQL) don't need this line anymore
'Done. Close the connection object
connection.Close
Set connection = Nothing

Response.Redirect "edit/answers_list.asp"
%>



</body>
</html>
 
Upvote 0 Downvote
sorry for the oversight, but I should also mention that I have a log in page this is what I have for my verify.asp page
the verify.asp page checks for user name and password, it also checks if there are questions or not in the questions table
Code: 
<%
	'Save the entered username and password
	username = Request.Form("txtUsername")	
	password = Request.Form("txtPassword")
	

	
	    set conn00 = server.CreateObject ("ADODB.Connection")        
    conn00.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.MapPath ("qa.mdb")
    set rs00 = server.CreateObject ("ADODB.Recordset")

rs00.Open "SELECT count(*)as intTotal FROM answers where username='" & username & "'", conn00, 1

if rs00("intTotal") <> 0 then response.redirect("edit/answers_list.asp") else set conn = server.CreateObject ("ADODB.Connection")		
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.MapPath ("qa.mdb")
set rs = server.CreateObject ("ADODB.Recordset")		
	'Open record with entered username
rs.Open "SELECT * FROM users where username='"& username &"'", conn, 1 
	 Response.Cookies("ValidUser") = Validated
	'If there is no record with the entered username, close connection
	'and go back to login with QueryString
If rs.recordcount = 0 then
		rs.close
		conn.close
		set rs=nothing
		set conn=nothing
		Response.Redirect("login.asp?login=namefailed")
	end if
	
	'If entered password is right, close connection and open mainpage
	if rs("password") = password then
		Session("Username") = username
		Response.Redirect("questionstry1.asp")

	'If entered password is wrong, close connection 
	'and return to login with QueryString
	else
		rs.Close
		conn.Close
		set rs=nothing
		set conn=nothing
		Response.Redirect("login.asp?login=passfailed")
	end if	

%>

 
Upvote 0 Downvote
I think I am complicating things. I need to check for duplicate records before insert. but not sure how to go about it
 
Upvote 0 Downvote
I think I somewhat got it but still getting some errors

the error is
Code: 
Error Type:
Microsoft VBScript compilation (0x800A0401)
Expected end of statement
/questions/project/questionstry1.asp, line 33, column 119
strSQL1 ="SELECT * FROM questions WHERE itm NOT IN (SELECT itm FROM answers WHERE username='" & Session("Username")&"'",adoCon,1
----------------------------------------------------------------------------------------------------------------------^

This is the code I am using

Code: 
<%
Username = Request.Form("txtUsername")	
	Password = Request.Form("txtPassword")
'Dimension variables
Dim adoCon1 			'Holds the Database Connection Object
Dim rs1Guestbook			'Holds the recordset for the record to be updated
Dim strSQL1			'Holds the SQL query for the database



'Create an ADO connection odject
Set adoCon1 = Server.CreateObject("ADODB.Connection")

'Set an active connection to the Connection object using a DSN-less connection
adoCon1.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("qa.mdb")

'Set an active connection to the Connection object using DSN connection
'adoCon.Open "DSN=guestbook"

'Create an ADO recordset object
Set rs1Guestbook = Server.CreateObject("ADODB.Recordset")

'Initialise the strSQL variable with an SQL statement to query the database
strSQL1 ="SELECT * FROM questions WHERE itm NOT IN (SELECT itm FROM answers WHERE username='" & Session("Username")&"'",adoCon,1 


'Open the recordset with the SQL query 
rs1Guestbook.Open strSQL1, adoCon1
%>
 
Upvote 0 Downvote
ok so I took the last part out
Code: 
, conn, 1

and now the error is

Code: 
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Missing ), ], or Item in query expression 'itm NOT IN (SELECT itm FROM answers WHERE username='''.
/questions/project/questionstry1.asp, line 35

this is line 35
Code: 
rs1Guestbook.Open strSQL1, adoCon1
 
Upvote 0 Downvote
When you have problems like these, add
Code: 
response.write strSQL1

before opening the recordset, so you can find typo's. In this case, you are missing closing parens

Code: 
strSQL1 ="SELECT * FROM questions WHERE itm NOT IN (SELECT itm FROM answers WHERE username='" & Session("Username")&"'[highlight yellow][b])[/b][/highlight]"
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

leifoet
  • Locked
  • Question
How to check if a datatable is empty ?
Replies
0
Views
193
leifoet
leifoet
penguinspeaks
  • Locked
  • Question
READ TEXT FILE BASED ON VARIABLE 1
Replies
2
Views
188
guitarzan
guitarzan
penguinspeaks
  • Locked
  • Question
Parse a database field that is comma delimited and input the results into another table
Replies
0
Views
178
penguinspeaks
penguinspeaks
penguinspeaks
  • Locked
  • Question
Return to current location on page after asp processes.
Replies
2
Views
201
penguinspeaks
penguinspeaks
JScannell
  • Locked
  • Question
Border issue with Google Chrome on an Android phone
Replies
0
Views
180
JScannell
JScannell

Part and Inventory Search

Sponsor

Back
Top