2. ASPRunnerPro General questions
  3. Avoid Duplicate entries
This topic is locked

Avoid Duplicate entries

 		9/11/2006 3:39:56 PM
ASPRunnerPro General questions
G
 gzusfreak author

In my "Projects" database, I need to avoid duplicate "Project_Number" If the project number exist, i need it to not add, but write "Project Number Exist".
I have copied the code below that is not working (the Project_Number is a primary key so the duplicate doesn't enter, but returns the error page.)
The DB is Sql. The table name is "Projects" the field i need to check is "Project_Number"
Any assistance would be appreciated.
'** Check if specific record exists ****

strSQLExists = "select * from Projects where Project_Number = 'Project_Number'"

set rsExists = CreateObject("ADODB.Recordset")

rsExists.Open strSQLExists, dbConnection
if not rsExists.eof then

' if record exists do something

Respone.write "This Project Number already Exist"

BeforeAdd=False

else

' if dont exist do something else
end if

rsExists.Close : set rsExists = Nothing
BeforeAdd = True
Sergey Kornilov admin 9/11/2006

Try the following:

'** Check if specific record exists ****

*strSQLExists = "select from Projects where Project_Number = '" & dict("Project_Number") & "'"**

set rsExists = CreateObject("ADODB.Recordset")

rsExists.Open strSQLExists, dbConnection
if not rsExists.eof then

' if record exists do something

Respone.write "This Project Number already Exist"

BeforeAdd=False

else

' if dont exist do something else
end if

rsExists.Close : set rsExists = Nothing
BeforeAdd = True
G
 		gzusfreak author 9/12/2006

Thanks, I added the text:

strSQLExists = "select * from Projects where Project_Number = '" & dict("Project_Number") & "'"
And got the following:
Technical information
Error number - 424
Error description - Object required
URL - /ASPRunnerPro/Projects_add.asp
SQL query - select [Branch], [Project_Created_By], [Date_Project_Added], [Project_Name], [Project_Number], [Planroom], [Client_Name], [Project_Password], [Primary_Contact] From [dbo].[Projects] where (1=0)
Additional info - Event: RetVal = BeforeAdd(dict)
Any Ideas?
J
 		Jane 9/13/2006

Hi,
what event you use?

Also please post here full event code and I'll try to find what's wrong.
G
 		gzusfreak author 9/13/2006

Is this what you need? Or do you need me to send you a zip file of all of the pages?
Function BeforeAdd(dict)
' Parameters:

' dict - Scripting.Dictionary object.

' Each field on the Add form represented as 'Field name'-'Field value' pair
'** Check if specific record exists ****

strSQLExists = "select * from Projects where Project_Number = '" & dict("Project_Number") & "'"

set rsExists = CreateObject("ADODB.Recordset")

rsExists.Open strSQLExists, dbConnection
if not rsExists.eof then

' if record exists do something

Respone.write "This Project Number already Exist"
BeforeAdd=False

else

' if dont exist do something else
end if

rsExists.Close : set rsExists = Nothing
BeforeAdd = True
' set BeforeAdd to True if you like to proceed with adding new record

' set it to False in other case
End Function
Thanks for your help.
Sergey Kornilov admin 9/13/2006

If Project_Number is a numeric field you need to remove single quotes around it.
Also it's a good idea to print SQL query on the Add page to see what exactly is happens. You can copy and paste it SQL Query Analyzer to see if it runs without errors.
G
 		gzusfreak author 9/13/2006

I am getting closer. I removed the single quotes and got this error:
Error number -2147217900
Error description Invalid column name 'QRS_1234'.
URL /ASPRunnerPro/Projects_add.asp
SQL query select [Branch], [Project_Created_By], [Date_Project_Added], [Project_Name], [Project_Number], [Planroom], [Client_Name], [Project_Password], [Primary_Contact] From [dbo].[Projects] where (1=0)
Additional info Event: RetVal = BeforeAdd(dict)
---------------------------------
"QRS_1234" is the intentional duplicate entry (testing to be sure that it catches duplicates). The actual column name is "Project_Number" where i am searching for the duplicate entry.
I have pasted the code as it stands.
Earlier you mentioned printing the query - how do i add that to the page?
J
 		Jane 9/14/2006

To print SQL queru use Response.Write clause:

'** Check if specific record exists ****

strSQLExists = "select * from Projects where Project_Number = '" & dict("Project_Number") & "'"

set rsExists = CreateObject("ADODB.Recordset")

Response.Write strSQLExists