Tip 1, avoid using DSN to connect to the database
1054
Are you still connecting to the database using an ODBC system or a file DSN? Replace it with an OLE DB provider, a database connection technology that is faster and does not require the use of DSN. With OLE DB providers, you no longer have to plead with your ISP (or database administrator/webmaster) to create a System DSN, or modify the configuration for site file location changes.
Ask:
I've seen a lot of examples of connecting databases through data resource names (DSNs), but I now want to not connect to the database through DSN. Can this be achieved in ASP? Can you give a few concrete examples to illustrate? I want the new connection method to be independent of the System DSN, but you can specify the driver, server name, database, database account, and password in the database connection string.
For:
If you are using SQL Server 7, use the following database connection string:
strconnstring = "dsn= ';D river={sql SERVER};" & _
"UID=MYUID; Pwd=mypwd; "& _
"DATABASE=MYDB; Server=myserver; "
One of the most important parameters is the "driver=" section. If you want to bypass ODBC and access SQL Server directly via OLE DB (which is generally faster), use the following connection string:
strconnstring = "Provider=SQLOLEDB.1; Password=mypassword; "& _
"Persist security info=true; User Id=myuid; "& _
"Initial catalog=mydbname;" & _
"Data Source=myserver; Connect timeout=15 "
If you are using a database connection string but are unfamiliar with the OLE DB provider's connection string syntax, create one by using the Visual Basic Data Environment Designer or ADO Data control, and then copy it to the ADO Connection object. In the Immediate window, enter the command? Dataenvironment1.connection1.ConnectionString can get the code for the connection string. Note that the syntax for the Microsoft Access connection string is different, see Syntax for dsn-less Connection for MS access
See also: The Database Connections section in the ASP Performance Tuning Guide.
2, the total number of records recorded in the calculation
1062
This problem can often be encountered when you start using recordsets in ASP pages. If you want to access data in a Recordset, you must first ensure that the recordset does contain data. Keep in mind that if there is no data in the recordset, the system will display very unfriendly run-time error messages. You can use the following code to solve this problem.
Ask:
I have had several years of VB experience, but just started to learn ASP and VBScript. Now I'm going to open an Access database, calculate the total number of records in it, and display that information in a Web page. The database is named Sean.mdb, which contains a people table with three records. However, when I run the script it always says there are-1 records.
Can you tell me where the following code went wrong?
<%
Set objconn = Server.CreateObject ("ADODB. Connection ")
Set Objrst = Server.CreateObject ("ADODB. Recordset ")
objConn.Open ("Driver={microsoft Access DRIVER (*.mdb)};" dbq=
"& Server.MapPath (" Seannewelldbsean.mdb "))
strSQL = "SELECT * from people"
Objrst.open strSQL, objconn
Response.Write ("< P >" & strSQL & "</p >")
Response.Write ("< H2 >there are" & Objrst.recordcount &
"People in the database
If objrst.recordcount > 0 Then
Objrst.movefirst
Do as not objrst.eof
Response.Write ("Name =" & Objrst.fields (0))
Objrst.movenext
Loop
Else
Response.Write ("It ' s empty!")
End If
Objrst.close
Set Objrst = Nothing
Objconn.close
Set objconn = Nothing
% >
For:
The RecordCount property returns-1 in the lower version of MDAC. Please update the MDAC files to the latest version on your server, and the latest MDAC files can be found in www.microsoft.com/data.
If your Web server is managed by an ISP and you do not have permission to configure it to upgrade MDAC files, you must modify the code.
You used the following code to check for records in a recordset:
If objrst.recordcount > 0 Then ...
Use the following code instead:
If Objrst.bof and Objrst.eof Then
' Recordset is empty
Else
Do as not objrst.eof
' Process recordsets
Objrst.movenext
Loop
End If
Updated June 30, 2000, New Zealand's Daryl Egarr said:
As you can see, the code in the reader's question is not wrong. The problem is that "the RecordCount property returns-1" in a lower version of MDAC is not an error in itself, but the author should not have made that assumption in the context of the question, since no single line of code in the original question meant the use of a lower version of MDAC.
The author considers the problem in the wrong direction, and the point is that not all of the cursor types support all the properties and methods (regardless of which database system is used). The real reason the code is wrong in the problem is when you use the default CursorLocation:
Recordset.cursorlocation = adUseServer
The RecordCount property is available only if the recordset's CursorType is 1 or 3 (that is, adopenkeyset,adopenstatic). The error code does not specify CursorType, that is, 0 types of cursors are used (that is, adOpenForwardOnly, which is the fastest cursor type), at which point the reference to RecordCount will always return 0.
The solution to the problem is simple, just put the original code:
Objrst.open strSQL, objconn
Change into:
Objrst.open strSQL, objconn, 1
