Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 224
  • Last Modified:

Diplaying database table info after an insert statement with asp

My goal is to display on the screen a confirmation number after submitting/inserting data to the database (just an Access db). This is what I currently have:


asp page 1
----------
gets data from user to insert into table, and a submit button.


asp page 2
----------
1. open connection to db
2. execute insert statement
3. close connection
4. display "Data submitted."


One of my table columns is called "num", and it's and AutoNumber data type that's unique (it increments each new row by one).

My question is, how would I display back with "Data submitted" on the screen "Your confirmation number is: " then have the "num" value for that insert statement as the confirmation number?

Thanks for any help.
0
scottie_24
Asked:
scottie_24
  • 9
  • 6
  • 3
  • +2
1 Solution
 
nurbekCommented:
how do you use the "num"
it is generated automatically or not?

you can get the Max value of unigue field

or get the last record by orderinf

SQL = "Select * From mytable order by myid DESC"
0
 
JohnModigCommented:
Make the following changes:

asp page 2
----------
1. open connection to db
2. execute insert statement
3. close connection
4. open a new connection to db
5. create a recordset and pull out the last record
6. display "Data submitted."
7. close connection and recordset

If you need help with code, just let me know ;)
0
 
peh803Commented:
>1. open connection to db
>2. execute insert statement
>3. close connection
>4. open a new connection to db
>5. create a recordset and pull out the last record
>6. display "Data submitted."
>7. close connection and recordset

You should not need to open / close the connection twice to do what you're after.

Try this method instead (for example, let's insert firstname, lastname, and jobtitle into a db and then retrieve the autonumber employeeID):

<%
Dim sSQL : sSQL = "SELECT * FROM EmployeeTable "   '' fetch empty recordset
Dim objConn : set objConn = server.createObject("adodb.connection")
Dim rsInsert : set rsInsert = server.createobject("adodb.recordset")
Dim sConnStr : sConnStr = "[valid connection string to your data source]"
Dim lTheID : lTheID = 0

objConn.open sConnStr
rsInsert.open sSQL, objConn, 1, 3, 1
rsInsert.AddNew()
rsInsert.fields("FirstName") = Request.Form("FirstName")
rsInsert.fields("LastName") = Request.Form("LastName")
rsInsert.fields("JobTitle")= Request.Form("JobTitle")
rsInsert.update()
rsInsert.requery()
rsInsert.moveLast()
lTheID = rsInsert.fields("EmployeeID").value

rsInsert.Close()
objConn.close

set rsInsert = nothing
set objConn = nothing

response.write "The New ID is: " & lTheID & "<BR>"
%>

Regards,
peh803
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
scottie_24Author Commented:
Hi nurbek,

Num is automatically generated by Access.

JohnModig, yes I need help with the code if you could, thank you.

Scottie.
0
 
scottie_24Author Commented:
Hi JohnModig, looks like peh803 has the code, let me try it.
0
 
peh803Commented:
@scottie_24 : fyi, you may not even need these lines:

rsInsert.requery()
rsInsert.moveLast()

But for good measure, I have always used them when performing this operation.

The code above should do exactly what you're after.

Thanks,
peh803
0
 
scottie_24Author Commented:
peh803, I've altered your code a little bit to fit what I should need as follows:

<%
 'I have code here that gets input from user for col1 and col2, works fine
 ...

  Dim data_source, conn, sql_insert, rs, sql_select, last_num
 
  last_num = 0

  data_source = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
  Server.MapPath("tester.mdb")

  sql_insert = "insert into tableX (col1, col2) values ('" & col1 & "', '" & col2 & "')"

  sql_select = "select * from tableX"

  Set conn = Server.CreateObject("ADODB.Connection")
  Set rs = Server.CreateObject("ADODB.Recordset")
 
  conn.Open data_source
  rs.Open sql_select, conn, 1, 3, 1
 
  conn.Execute sql_insert

  rs.requery()
  rs.moveLast()
  last_num = rs.fields("num").value

  ' Done. Close the connection and recordset
  rs.Close
  conn.Close

  Set rs = Nothing
  Set conn = Nothing

  Response.Write "Confirmation: " & last_num & "<br>"

%>


The above isn't displaying the value for the most recent "num" submission in my table. Instead I keep getting the "num" value of the second row in my table, no matter how many records were inserted after it. Any suggestions?

Is the line of "  rs.Open sql_select, conn, 1, 3, 1" being redundant by opening "conn" again when it appears to already be open from the previous line?

0
 
peh803Commented:
yeah, what you have isn't quite right -- you need to use the recordset to do the insert -- you won't have an insert statement anymore.

based on what you posted above, here's what your code would probably look like:

<%
 'I have code here that gets input from user for col1 and col2, works fine
 ...

  Dim data_source, conn, sql_insert, rs, sql_select, last_num
 
  last_num = 0

  data_source = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
  Server.MapPath("tester.mdb")

  sql_select = "select * from tableX"

  Set conn = Server.CreateObject("ADODB.Connection")
  Set rs = Server.CreateObject("ADODB.Recordset")
 
  conn.Open data_source
  rs.Open sql_select, conn, 1, 3, 1
  rs.AddNew()
  rs.fields("col1") = col1
  rs.fields("col2") = col2
  rs.update()
  rs.requery()
  rs.moveLast()
  last_num = rs.fields("num").value

  ' Done. Close the connection and recordset
  rs.Close
  conn.Close

  Set rs = Nothing
  Set conn = Nothing

  Response.Write "Confirmation: " & last_num & "<br>"

%>

Make sense?

Thanks,
peh803
0
 
nurbekCommented:
scotie_24

when you use execute command you dont need for recordsset
try like
--------------------- (you will no use this part)
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open data_source
sql_insert = "insert into tableX (col1, col2) values ('" & col1 & "', '" & col2 & "')"
conn.Execute sql_insert
-------------------------------------------

but when you use recordset  you need a code like that
also to catch the last record id
Set conn = Server.CreateObject("ADODB.Connection")

Set rs = Server.CreateObject("ADODB.Recordset")
  conn.Open data_source
  rs.Open sql_select, conn, 1, 3, 1
 rs.AddNew
rs("col1") = col1
rs("col2") = col2
rs.update

  rs.requery()
  rs.moveLast()
  last_num = rs.fields("num").value
  rs.Close
  conn.Close

0
 
scottie_24Author Commented:
Thanks for the posts everyone, it's going to be several hours before I can test what's been posted. I'll let you know how it goes later.

Thanks again.
0
 
peh803Commented:
seems like nurbek agrees with me

:-D
0
 
nurbekCommented:
surely :)
0
 
JohnModigCommented:
>>The above isn't displaying the value for the most recent "num" submission in my table. Instead I keep getting the "num" value of the second row in my table, no matter how many records were inserted after it. Any suggestions?

Told you so. :p
You will need that extra recordset (point no 5 in my previous post) to pull out the newly autogenerated number.
0
 
peh803Commented:
>> Told you so. :p
>> You will need that extra recordset (point no 5 in my previous post) to pull out the newly autogenerated number.

I use the above technique to retrieve autogenerated ID's in several applications, and I have had no trouble with it.

If done properly, the extraneous overhead created by additional opening / closing of connections, new recordsets, etc. can all be avoided.

Regards,
peh803
0
 
peh803Commented:
Besides, if you will notice based on my and nurbek's recent posts, the questioner was not implementing the suggested solution properly.  Once he makes these changes, I am confident that it will work for him.

peh803
0
 
fozyletCommented:
@peh803,
what does the requery/movelast do?! (You said that is not necessary)

@JohnModig,
The method you mentioned does not guarantee to return the id... see this senario (simultaneous use).

rs1-1 of user1 updates
rs2-1 of user2 updates
rs1-2 of user1 queries... get user2's confirmation!
0
 
peh803Commented:
>>what does the requery/movelast do?! (You said that is not necessary)

From here: http://www.w3schools.com/ado/met_rs_requery.asp

  The MoveLast Method
  --------------------------------------------------------
  The Requery method updates the data in a Recordset by re-executing the query that made the original Recordset.
  Tip: Use this method to refresh the entire contents of a Recordset.
  Note: With this method you call the Close and Open methods in one.

From here: http://www.w3schools.com/ado/met_rs_movefirst.asp

  The MoveLast Method
  --------------------------------------------------------
  This method is used to move to the last record in a Recordset object. It also make the last record the current record.
  Note: Calling MoveFirst or MoveLast when the Recordset is empty generates an error.
  Note: An error will occur if the Recordset object does not support bookmarks or backward cursor movement.

The thought behind it "not being required" is that you've just done "addNew" and "update" to the current item in the recordset, and so you should be able to request the current ID from the recordset and receive the one that was just created; however, requerying the recordset and moving to the last record in the recordset guarantees that you have the correct record.  Again, it's an optional level of "being careful" that I have used in my own coding without incident.

Let me know if you have any other quetions.

Best Regards,
peh803
0
 
scottie_24Author Commented:
This is what my code now looks like:

<%  

  'I have code here that gets input from user for col1 and col2, works fine
  ...

  last_num = 0

  data_source = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
  Server.MapPath("tester.mdb")

  sql_select = "select * from tableX"

  ' Creating Connection Object and opening the database
  Set conn = Server.CreateObject("ADODB.Connection")
  Set rs = Server.CreateObject("ADODB.Recordset")

  conn.Open data_source
  rs.Open sql_select, conn, 1, 3, 1

  rs.AddNew()

  rs.fields("col1") = col1
  rs.fields("col2") = col2

  rs.update()
  rs.requery()
  rs.moveLast()
  last_num = rs.fields("num").value


  ' Done. Close the connection and recordset
  rs.Close
  conn.Close

  Set rs = Nothing
  Set conn = Nothing

  Response.Write "Confirmation: " & last_num & "<br>"

%>


The problem is the same as I reported earlier in that it's displaying "Confirmation: 30" everytime, even though I have several records inserted after the row with num of 30. The row with num of 30 is my second row.
0
 
peh803Commented:
please comment out these lines:

  rs.requery()
  rs.moveLast()

And try again.

Thank you,
peh803
0
 
scottie_24Author Commented:
It's works now, thank you peh803. I'll assign the points now.
0
 
peh803Commented:
glad to help out!

Thanks,
peh803
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 9
  • 6
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now