?
Solved

Diplaying database table info after an insert statement with asp

Posted on 2005-02-28
21
Medium Priority
?
223 Views
Last Modified: 2010-07-27
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
Comment
Question by:scottie_24
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 6
  • 3
  • +2
21 Comments
 
LVL 13

Expert Comment

by:nurbek
ID: 13423742
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
 
LVL 11

Expert Comment

by:JohnModig
ID: 13423762
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
 
LVL 19

Expert Comment

by:peh803
ID: 13423836
>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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:scottie_24
ID: 13423854
Hi nurbek,

Num is automatically generated by Access.

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

Scottie.
0
 

Author Comment

by:scottie_24
ID: 13423872
Hi JohnModig, looks like peh803 has the code, let me try it.
0
 
LVL 19

Expert Comment

by:peh803
ID: 13424090
@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
 

Author Comment

by:scottie_24
ID: 13424327
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
 
LVL 19

Accepted Solution

by:
peh803 earned 2000 total points
ID: 13424389
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
 
LVL 13

Expert Comment

by:nurbek
ID: 13424416
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
 

Author Comment

by:scottie_24
ID: 13424432
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
 
LVL 19

Expert Comment

by:peh803
ID: 13424441
seems like nurbek agrees with me

:-D
0
 
LVL 13

Expert Comment

by:nurbek
ID: 13424463
surely :)
0
 
LVL 11

Expert Comment

by:JohnModig
ID: 13424627
>>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
 
LVL 19

Expert Comment

by:peh803
ID: 13424662
>> 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
 
LVL 19

Expert Comment

by:peh803
ID: 13424673
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
 
LVL 8

Expert Comment

by:fozylet
ID: 13427273
@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
 
LVL 19

Expert Comment

by:peh803
ID: 13429616
>>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
 

Author Comment

by:scottie_24
ID: 13430495
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
 
LVL 19

Expert Comment

by:peh803
ID: 13430578
please comment out these lines:

  rs.requery()
  rs.moveLast()

And try again.

Thank you,
peh803
0
 

Author Comment

by:scottie_24
ID: 13430762
It's works now, thank you peh803. I'll assign the points now.
0
 
LVL 19

Expert Comment

by:peh803
ID: 13431484
glad to help out!

Thanks,
peh803
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:   The Exchange of informatio…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question