| This example is for those looking to write records from an asp page to Microsoft SQL Server 6.5 or 7.0 database using a stored procedure without trying to use the command object like in Microsoft's examples. I could never get those to work! So I invented my own. I'm probably not the 1st to use this method but I was happy I discovered it! This example also after writing the records to the database, information can be retrieved (i.e an Identity column #) and write data back to the asp page & your end user. Sorry this demo is a lot more babble than any other demo's but the concept here is a bit more involved. Most of the examples I found on the net all relate to writing from
ASP to Access97 databases. This is all fine when the app is departmental
in nature or just using the Access db to extract pre-existing information to be
written to the asp page. If your web application is doing inserts, updates or
deletes and have a fair amount of users, use a MS SQL Server database. Any kind
of size to the audience using the web app and I strongly recommend
Microsoft SQL Server as the back-end database. Another nice feature in using SQL Server is then the owners of the data can easily
access live data through an Access 97 database via an ODBC driver. You can build
your front end for the end user of the data using Access 97. I have found most
people like using Access as a front end tool that does Querying/Reporting. In my
opinion that is why Access is the perfect front-end tool. (And NO Microsoft didn't
pay me to say that!!! Wish they did though!!):)) Please take sometime to review this free code, I have invested a great deal of time
searching the Net for information on Active Server Pages. I always believe you get
10 times or more back of what you give. Please take sometime and Click here to download the SQL Server scripts to create the tables in this example.
d. You have created some input form and a asp page to do the processing. The below
example show two WebPages(1: An input form, 2: Processformdata.asp that contains
all the asp code. PAGE ONE INPUT FORM: (SomeInputform.html)
1. the user will input some information and click submit to
an Active server page. This example we will input a firstname
and a last name. Then click submit PAGE TWO ASP PAGE CODE: (processformdata.asp)
<%
'Declare variables
dim strconn
dim conn
dim rs
dim strsql
dim strsql2
dim strsql3
dim recordIDvalue
'This formats the data from the Input form and will be passed into the database.
'At the end of the formatted string is how I return the value
'This easily could be Select * from TableName to return the whole recordset
strsql1 = "Declare @req_num int Exec spName"
strsql2 = "'" & request.form("txtfield1") & "'," & "'" & request.form("txtField2") "'," & @req_num OUTPUT
strsql3 = "select table1.recordid from table1 where recordid = @req_num"
strsql = strsql1 & strsql2 & strsql3
'In this example the formatted string will look like this. A good way to test
'for this is to do a response.write out your code that will be executed on the asp page
'Copy and Paste in the query window of SQL server 6.5 or 7.0. This will help you determine if your syntax is right. Formatted SQL is a bit fussy.
declare @req_num int EXEC spName 'John','Doe', @REQ_NUM OUTPUT SELECT @@Identity
'Set your connection & recordset objects
set conn = server.createobject("adodb.connection")
conn.open "dsnname or connectionstring"
set rs = server.createobject("adodb.recordset")
rs = conn.execute(strsql)
'Set a local variable that will be used to write out the value
recordIDvalue = rs("Identity_Value_That_was_Created_When_Inserted")
conn.close
Set rs = nothing
set conn = nothing
%>
<html>
<head>
<title>Asp page that will do the processing</title>
</head>
<body>
<p>Your record number is:<% = recordIDvalue %> </p>
</body>
</html> | 
developerWorks - FREE Tools!