Jump to content

Duplicate Recods


Recommended Posts

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

Link to post
Share on other sites

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

Link to post
Share on other sites
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.
Link to post
Share on other sites

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.

Link to post
Share on other sites

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.

Link to post
Share on other sites

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

Link to post
Share on other sites

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

Link to post
Share on other sites

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
Link to post
Share on other sites

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
Link to post
Share on other sites

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")) & "'"-------------------------------------------------------------------------------------------------------------------------------------^

Link to post
Share on other sites

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.

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...