Search Specifice Code

[Fary4u]

Hi

i'm try to search record with pertcluar record
My product ID is autonumber & user enter the Product Name

i need to find from my Product ID from Product Name ( pnam )
can any body know's where is the problem in my codeing ?

here is the code
[PHP]
Dim prid, Conn1, tstkprd
set Conn1 = Server.CreateOb ject("ADODB.Con nection")
Conn1.Open ConString
Set rstkprd = Conn1.Execute(" select productID from products where productName= '"&pnam&"'")

response.write rstkprd("produc tName")
prid = rstkprd("produc tID")

rstkprd.Close
set rstkprd = Nothing
Conn1.Close
set Conn1 = Nothing
[/PHP]

[DrBunchman]

Hi Fary4u,

You don't say whether you are getting an error or not but I can see a couple of problems in your code:

The line:

response.write rstkprd("productName")

will error because you haven't selected productName when you open the recordset. You'll need to include it as follows:

Set rstkprd = Conn1.Execute("select productID, productName from products where productName= '"&pnam&"'")

You should also add a condition which tests whether any records have been returned before you try to use the recordset. The recordset.EOF property tests whether the recordset has reached the End Of File and will be true if no records are returned. Like so:

 If Not rstkprd.EOF Then 
response.write rstkprd("productName")
prid = rstkprd("productID")
End If

See how you get on after making these changes. If you still get problems then please post back but include any error messages that you receive.

Hope this helps,

Dr B

[Fary4u]

Dr Bunchman thx for kindly reply

actually the problem is i'm saving New record & retriving the value at the same time,

when i close the connection & retriving the record then it's working fine
But when i using within the connection then giving me error

here is my code:-

Dim objrs,objConn
Dim pnam, categ

	pnam=Trim(request("txtpname"))
	categ=Trim(request("txtcat"))
	
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.open ConString
set objrs = server.createObject("ADODB.RecordSet")
objrs.open "Select catalogID, productName from products where productName='" & pnam & "'",objConn,Adopendynamic,Adlockoptimistic
if objrs.eof then
	objrs.Addnew
		objrs("catalogID")=categ
		objrs("productName")=pnam
	objrs.update

response.write" New Record save Successfully"
	CreateNew1
else
response.write" This Record already in a Database
end if

objrs.close
set objrs= Nothing
objConn.Close
set objConn = Nothing

    Sub CreateNew1()
		Dim prid, Conn1, tstkprd
		set Conn1 = Server.CreateObject("ADODB.Connection")
		Conn1.Open ConString
		Set rstkprd = Conn1.Execute("select productID, productName from products where productName= '"&pnam&"'")
	
		If not rstkprd.EOF Then 
			response.write rstkprd("productName")
			response.write rstkprd("productID")
		End If
		
		rstkprd.Close
		set rstkprd = Nothing
		Conn1.Close
		set Conn1 = Nothing
    End Sub

in Line 18 Calling Procedure if i called it after connection close then working fine

But with in the code it's want allowed me to do that.

i've already try different methods like

objrs.Addnew
. . . 
objrs.update
response.write objrs.("productID")

if have some thing different idea or any solution please i realy appricate that

[DrBunchman]

What's the error and is it occuring on line 18? If not which line is it?

Dr B

[Fary4u]

please read it . . .
i need to do 2 connections on the same time
1 is saving record but before it's close connection it's return value & save 2nd record

Dim objrs,objConn
Dim pnam, categ

	pnam=Trim(request("txtpname"))
	categ=Trim(request("txtcat"))
	
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.open ConString
set objrs = server.createObject("ADODB.RecordSet")
objrs.open "Select catalogID, productName from products where productName='" & pnam & "'",objConn,Adopendynamic,Adlockoptimistic
if objrs.eof then
	objrs.Addnew
		objrs("catalogID")=categ
		objrs("productName")=pnam
	objrs.update

	Dim prid, Conn1, tstkprd
	set Conn1 = Server.CreateObject("ADODB.Connection")
	Conn1.Open ConString
	Set rstkprd = Conn1.Execute("select productID, productName from products where productName= '"&pnam&"'")
	
	If not rstkprd.EOF Then 
		response.write rstkprd("productName")
		response.write rstkprd("productID")
	End If
		
	rstkprd.Close
	set rstkprd = Nothing
	Conn1.Close
	set Conn1 = Nothing

response.write" New Record save Successfully"
else
response.write" This Record already in a Database
end if

objrs.close
set objrs= Nothing
objConn.Close
set objConn = Nothing

after line 22 not execute coz it's not taking value from the database but if i'm asking 2 how to do multipule record set's with 2 connection ?

i think so u get my point.
please feel free to reply.
thanks in advance

[DrBunchman]

So....are you getting an error message or not? If so what is it?

I've run your code against my own test db and it has worked without any problem.

Can I ask why you are using two recordset and connection objects? I'm not sure that you need to but then i'm still not sure exactly what you're trying to do!

What I think you want to do is this:

1. Run a query against the database to check if a record exists,
2. If it doesn't then insert a new record,
3. Tell the user that the record has been updated.

Am I missing something?

Dr B