Duplicate Recods

[evr72]

Hello, I have a form where users log in and answer different quesitons. The form gets updated with new questions. I would like to prevent the users from submiting the same questions twice. but not sure how to go about this. I am a bit new to programming and asp. Any help is greatly appreciated. Thanks!!!

[justsomeguy]

That's a little vague, but if you want to stop them from hitting refresh and resubmitting the same form, the solution is to redirect them after they submit it.

[evr72]

Sorry, I should have explained a bit better.The form can be updated with new questions at any time. The users can log in at any time to answer the questions. What I was trying to do is to redirect the user to a new page if he/she has already answer the questions.I have 3 tablesUsers useridusernamecompanyeamailQuestionsquestionidquestionquestionpart2questionpart3Answersuseridusernamecompanynameanswer1answer2answer3

Function IsInteger(Data)  If Trim(Data) = "" Then	IsInteger = False  Else	IsInteger = IsNumeric(Data & ".0e0")  End IfEnd Function'declare your variablesDim itm, userid, username, company, iDim sConnString, connection, sSQL'Receiving values from Form, assign the values entered to variablesitm = 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 databasesConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _"Data Source=" & Server.MapPath("qa.mdb")'create an ADO connection objectSet connection = Server.CreateObject("ADODB.Connection")'Open the connection to the databaseconnection.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 objectconnection.CloseSet connection = NothingResponse.Redirect "edit/answers_list.asp"%>

This populates the answers table. This is the table that might get the duplicate records and I would like to prevent that from happening

[End User]

The form can be updated with new questions at any time.

If I understand the problem, I think the thing to do would be to keep track of which questions they're already answered and prohibit them from answering them again. If that's not what you're trying to do, explain it a bit further please.

[evr72]

that is exactly what I am trying to do but not too sure how to go about it. Thanks!!!!

[justsomeguy]

You'll need to add a field to the answers table to store the question ID for each answer. When you're getting questions to show them, first look up to see if they have any previous answers, and exclude those IDs from the list of questions you get for them.

[evr72]

I already have a field with the question id in my questions table and my answers table, the field is "itm". justsomeguy I am not sure how to write this part.I've tried to join my questions table and answers table but that did not work very well.

[justsomeguy]

You can use a query like this to get questions that aren't already in the answers table:SELECT * FROM questions WHERE questionid NOT IN (SELECT itm FROM answers WHERE userid=XXX)

[evr72]

I appreciate the guidance. I will give it a try Thank youi!!!

[evr72]

ok so I tried

<%Username = Request.Form("txtUsername")		Password = Request.Form("txtPassword")'Dimension variablesDim adoCon1 			'Holds the Database Connection ObjectDim rs1Guestbook			'Holds the recordset for the record to be updatedDim strSQL1			'Holds the SQL query for the database'Create an ADO connection odjectSet adoCon1 = Server.CreateObject("ADODB.Connection")'Set an active connection to the Connection object using a DSN-less connectionadoCon1.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 objectSet rs1Guestbook = Server.CreateObject("ADODB.Recordset")'Initialise the strSQL variable with an SQL statement to query the databasestrSQL1 ="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%>

and get the following error

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

[justsomeguy]

You're just defining the query on that line, you don't need the extra parameters at the end.

[evr72]

I took those out and now the error is

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

rs1Guestbook.Open strSQL1, adoCon1

[justsomeguy]

Does the questions table have a field called itm? Compare your query with the one I wrote above.

[evr72]

Yes, the questions table has itm (question id) and the answers table has itm (question id)

[evr72]

ok this is odd,I just tried it again and now this is the error I get

Error Type:Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)[Microsoft][ODBC Microsoft Access Driver] Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'./questions/project/questionstry1.asp, line 36[/code

[evr72]

sorry, got to excitedthe erro is still the same as before. I was trying something out and accidentally took the S from Selec out

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[/code

[justsomeguy]

Ah, you're missing an ending parenthesis. You have an opening one but not a closing one.

[evr72]

ok, I added the parentesis

strSQL1 = "SELECT questions.* FROM questions WHERE itm NOT IN (SELECT itm FROM answers WHERE username='" & Request.Form("txtUsername")) & "'"

but still getting an error

Error Type:Microsoft VBScript compilation (0x800A0401)Expected end of statement/questions/project/questionstry1.asp, line 31, column 134strSQL1 = "SELECT questions.* FROM questions WHERE itm NOT IN (SELECT itm FROM answers WHERE username='" & Request.Form("txtUsername")) & "'"-------------------------------------------------------------------------------------------------------------------------------------^

[justsomeguy]

That's not the right place to add it. Use response.write to print the query out before you execute it, and compare the printed query with the one I wrote above. You're using ASP to build the query and send it to the database, but the database doesn't like the format of it. So after you build the query in ASP, print it out so you can see what you're sending to the database.