Check table for duplicates before inserting new records.. ERROR 1

[Anthony904]

Hi,

I am trying to check for duplicates before inserting into my Access Table.

I am getting an error.

Microsoft VBScript runtime error '800a01a8' 

Object required: '' 

/aspx/ip21/Survey_Process.asp, line 87

	Dim name, date1, Building
	Dim sConnString, connection, sSQL, RS

	' Receiving values from Form, assign the values entered to variables
	name = Request.Form("name")
	date1 = Request.Form("date1")
	Building =Request.Form("Building")
	
	'Add check here for duplicates before INSERT
	'If Duplicate found .. display duplicate and go correct.

[COLOR=red]set RS = connection.execute("select * from Sugg_tbl where Name = '"& request("Name") &"' ")[/color]

        if RS.EOF = True then
 	'no duplicate of new data found, so insert data
 	
	'declare SQL statement that will query the database 
	sSQL = "INSERT into Sugg_tbl (name, date1, Building) values ('" & _
	name & "', '" & date1 & "', '" & Building & "')" 
         
        else
        
        response.write "duplicate found"

	End If
	
	'define the connection string, specify database 
	'driver and the location of database
	sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _ 
	"Data Source=" & Server.MapPath("asc.mdb") 


	'create an ADO connection object 
Set connection = Server.CreateObject("ADODB.Connection") 
set rs = Server.CreateObject("ADODB.recordset")

Any Ideas?

Thanks!

 

[Chopstik]

I see where you've dim'ed your connection object but not where you've actually instantiated it, thus there is no connection object from which you can create your recordset. You'll need to first instantiated connection and then go from there.

set connection = [COLOR=green]'however you connect to your DB[/color]
set RS = connection.execute("select * from Sugg_tbl where Name = '"& request("Name") &"' ")

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

 

[Anthony904]

Thanks for the response..

I did as you suggested... and am now getting a different error...

ADODB.Connection error '800a0e78' 

Operation is not allowed when the object is closed. 

/aspx/ip21/Survey_Process.asp, line 91

line 91

set rs = connection.execute("select * from Sugg_tbl where Name = '"& request("Name") &"' ")

I added the line in read to try to open.. nogo..

	'create an ADO connection object 
Set connection = Server.CreateObject("ADODB.Connection") 
Set rs = Server.CreateObject("ADODB.recordset")

        set rs = connection.execute("select * from Sugg_tbl where Name = '"& request("Name") &"' ")
        
        [COLOR=red]rs.open sSQL, connection[/color]
        if RS.EOF = True then

 

[Chopstik]

Ok, you set the connection object to a adodb.connection, but you haven't yet properly defined it. For example (from

http://connectionstrings.com

for a SQL DB):

"Provider=sqloledb;Data Source=Aron1;Initial Catalog=pubs;User Id=sa;Password=asdasd;"

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

 

[Anthony904]

I'm not sure I understand...

I thought I was defining it here..

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

Thanks for the input..

 

[Chopstik]

Ok, you did, but you did all of that *after* you tried using it to open your recordset. You need to do that *before* you open your recordset.

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

 

[Anthony904]

Excellent.. It is working now..

Thanks!