Run query and search from ASP

One of known operations we do on the database is searching and matching records that exist in database tables based on specific conditions. For example in login task, we search the USER table using users’ input data which it will be in this case “username” and “password”. In this example we are presenting example to show the content of table USER using select query:

As you many notice that we added where clause to find a match. To run the previous query on MYSQL we run the following snippet:


This code that uses ADODB (ActiveX Data Objects) to interact with a database and retrieve data. The first line of code creates an object "myConnection" using the "Server.CreateObject()" method and passing the argument "ADODB.Connection", this creates an instance of a Connection object. The next line sets the "Mode" property of the connection object to "adModeRead" which specifies that the connection is only being used for reading data from the database. The following line sets the "ConnectionString" property of the connection object to a variable named "aConnectionString" which should contain the connection string required to connect to the database.
The next line is calling the Open() method on the connection object to open a connection to the database using the specified connection string. The following line creates an object "queryResults" using the "Server.CreateObject()" method and passing the argument "ADODB.Recordset", this creates an instance of a Recordset object. The next line uses the "Open()" method of the Recordset object, this method is used to execute a SQL query on the database and open a recordset from the result. The first argument of the method is the SQL query which is "Select name, address, firstname, lastname from user where username='msi'", this query retrieves the name, address, firstname and lastname of the user where the username is "msi". The second argument is the connection object that should be used to execute the query. The following if statement checks if the EOF (End of File) property of the Recordset object is true, which means there are no more records in the recordset, if this is the case, the script will write "User not found" in the response, otherwise, it will write "User with name " & the value of the name field of the record in the response. At the end, the script sets the queryResults object to nothing, closes the connection, and sets the myConnection object to nothing.


In addition to what I've already mentioned, it's worth noting that the script uses the ADODB library to interact with the database, ADODB is a library that provides an OLE DB (Object Linking and Embedding, Database) interface for accessing data from various data sources. It allows for a consistent way of accessing data regardless of the underlying data provider. It's also worth noting that the script is using a hardcoded SQL statement which is not recommended for security reasons. Instead, it's recommended to use parameterized queries to prevent SQL injection attacks. It's also important to note that this script is using the "Response" object to write the result in the response, this object is a built-in ASP object that provides methods for sending output to the user. It's also worth mentioning that ADODB has been deprecated in newer versions of ASP and it is recommended to use other alternatives like ADO.NET to interact with databases in an ASP.NET application. It's also important to make sure that the connection string variable contains the correct information to connect to the database, also the database should be configured to allow remote connections if the script is running on a different server. It's also important to handle any errors that may occur during the execution of the script like connection failure or invalid SQL statement, also it's important to close the connection and release any resources after the data has been retrieved. This can be done by using the "On Error" statement to handle any exceptions that may occur and using the "Finally" block to make sure that the connection is closed and resources are released regardless of whether an error occurs or not. This will help to prevent any memory leaks or other issues that may arise from not properly cleaning up resources after they are no longer needed.



To start a connection please check this article :
asp-asp-net/connect-to-mysql-from-asp-t10129.html

If you are updating a record and want to show the number of updated rows :