Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dtanis
Starting Member
14 Posts |
 Posted - 2004-07-02 : 10:50:52
|
| Hello. I am a novice to ASP and was hoping someone couldhelp me with this problem.I have a submission page where data is collected and thenwritten to a SQL Server table. The table has an identity field,which I am using as "ticket number" of sorts.I set this part up in FrontPage, using the tool. Very little raw ASP.A confirmation page is generated which shows the data just submitted. I would like to use a field(s) from there or from the previous page where the data was actually keyed to query the table, and then display the identity field (aka "ticket number"). What is the syntax for this? I have tried a couple ways without success.Any help is more than welcome!! Thank you!Below is a sample of the select statement I am trying to use on the confirmation page. I am currently using the MAX function...but I know this is just smoke and mirrors, and will eventually get me into trouble.<%Dim con, rs, str set con=Server.CreateObject("ADODB.CONNECTION")con.Open "Provider=sqloledb;Data Source=Flmirsql02;Initial Catalog=Source_Forms;User Id=Source_Forms_User;Password=password;"str="SELECT Max(Ticket_Number) AS ticketID FROM Escalation_Forms" set rs = con.execute(str)if rs.eof then Response.write("No records returned")end if do Until rs.eofResponse.write rs("TicketID") & "</a><br>" rs.MoveNext loop rs.Closecon.Close Set rs =NothingSet con =Nothing%> ----------------------------------------------------------------------------------------------------------------------latest attempt----------------------------------------------------------------------------------------------------------------------<%Dim con, rs, str, name set con=Server.CreateObject("ADODB.CONNECTION")con.Open "Provider=sqloledb;Data Source=Flmirsql02;Initial Catalog=FAB_DADI;User Id=TEST_USER;Password=password;"'set name = Request.test_submit.("NAME")str="SELECT Ticket_Number AS ticketID FROM TEST Where Name ='"Request.test_submit.(NAME)"'"set rs = con.execute(str)if rs.eof then Response.write("No records returned")end if do Until rs.eofResponse.write rs("TicketID") & "</a><br>" rs.MoveNext loop rs.Closecon.Close Set rs =NothingSet con =Nothing%> |
|
|
JasonGoff
Posting Yak Master
158 Posts |
Posted - 2004-07-02 : 11:41:28
|
Create a stored proc (if you haven't already) that does the insert of new data for you, rather than using a dynamic SQL string from ASP.Call the stored proc from your ASP code, (see the Command object and Parameters collection in ADO). Within the stored proc, use SELECT IDENT_CURRENT('escalation_forms') to return the latest identity column on that table, and return that to the ASP. |
 |
|
|
dtanis
Starting Member
14 Posts |
Posted - 2004-07-02 : 14:00:29
|
| Thank you. That is a start. Say I wanted to query the table that had any other tickets with the phone number just submitted. Is there a syntax for calling fields from the previous form and using it in a SQL string? I know this might not be the best way...but again, my understanding of ASP is limited. Thanks! |
|
|
|
dtanis
Starting Member
14 Posts |
Posted - 2004-07-02 : 14:00:34
|
| Thank you. That is a start. Say I wanted to query the table that had any other tickets with the phone number just submitted. Is there a syntax for calling fields from the previous form and using it in a SQL string? I know this might not be the best way...but again, my understanding of ASP is limited. Thanks! |
|
|
|
|
|
|
|
|
New Attempt-SQL Using ASP Field From Previous Page