| 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> |