Printable View
I have started doing the ASP three months back. Now I can do some programs pretty well. I have also made a form in ASP. The main problem I am facing is while getting the values from the database. Along with that I want to put that values into the Listbox. Can anyone tell me how to get the values from the database in ASP? Please help me as soon as possible.!!:notworthy
I am assuming that you are using the Microsoft Access as a database. You will have to choose 'Blank Access Database' from the starting menu, to create a database after opening Microsoft Access. After creating the database, you will have to enter the data that has to be tested by creating the web page to display. After writing the HTML coding, you will have to write down the code for ASP to connect to the database, which will look similar like this :
Code:<%
Dim adoCon
Dim rsGuestbook
Dim strSQL
Set adoCon = Server.CreateObject("ADODB.Connection")
%>
Before connecting the database to the ASP, you should add something to your database. After adding the data you can connect the database to ASP. After doing this you will need to create a page to display the contents of the database. Because once the contents are displayed, you can select which entry you want to update. The following form will help you a lot :
HTML Code:<html>
<head>
<title>Selected Entry gets Updated</title>
</head>
<body bgcolor="grey" text="black">
<%
Dim adoCon
Dim rsGuestbook
Dim strSQL
Set adoCon = Server.CreateObject("ADODB.Connection")
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("Update.mdb")
Set rsupdate = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT tblComments.* FROM tblComments;"
rsupdate.Open strSQL, adoCon
Do While not rsupdate.EOF
Response.Write ("<br>")
Response.Write ("<a href=""update_form.asp?ID=" & rsupdate("ID_no") & """>")
Response.Write (rsupdate("Name"))
Response.Write ("</a>")
Response.Write ("<br>")
Response.Write (rsupdate("Comments"))
Response.Write ("<br>")
rsupdate.MoveNext
Loop
rsupdate.Close
Set rsupdate = Nothing
Set adoCon = Nothing
%>
</body>
</html>
I am trying to tell the steps that are involved while creating the database. First you will have to create an ODBC DSN on Your Web Server. For creating
an ODBC Data Source Name, you can use the ODBC Administrator which is present in the Control Panel. The reason behind creating that is the ODBC Administrator allows you to create and manage ODBC DSNs. A System DSN is available to your ASP page even if no user is logged on the machine. Then you will have to generate your ASP Page to Access the Database. After doing this you can able to get the values from the database.
I think I have got the perfect code for you. The following code connects the database and takes the values from the table and stores it in a recordset which is rsValues in the coding. After that the values are added in to the option form. For doing this I have also used the do...loop. Here is the coding :
Code:<%
set objConn=server.createobject("adodb.connection")
objConn.open "DSN=dbDSN;uid=username;pwd=pass"
set rsValues=objConn.execute("select field from table where )
%>
<Form>
<Select>
<%
do while not rsValues.eof %>
<option> <%=rsValues(0)%> </option>
<%
rsValues.movenext
loop
rsValues.close
set rsValues = nothing
objConn.close
set objConn=nothing
%>
</Select>
</form>
You can also use the code that is described as follows. The code for the trial.asp is shown below :
You can modify it accordingly.HTML Code:<%@Language=VBScript %>
<HTML>
<BODY>
<%
Dim Connect ,RS, Query, Details
Details = Trim(Request("Trial"))
Set Connect = Server.CreateObject("ADODB.Connection")
Connect.Open "Catalog"
Set RS = Server.CreateObject("ADODB.Recordset")
Query = "SELECT * FROM Items "
Query = Query & "WHERE ItemType='"& Details & "' "
Query = Query & "ORDER BY [ItemName]"
Set RS = Conn.Execute(Query)
If RS.EOF Then
Response.Write "There are no Details available for " & Request("Details")"
Else
%>
<table border="1" width="100%" cellpadding="2">
<tr>
<td><strong><font face="Arial" size="2">Name</font></strong></td>
<td><strong><font face="Arial" size="2">Product Type</font></strong></td>
<td><strong><font face="Arial" size="2">Details</font></strong></td>
<td><strong><font face="Arial" size="2">Price</font></strong></td>
</tr>
<%
Do While Not RS.EOF
%>
<tr>
<td><font face="Arial" size="2"><% = RS("Name") %></font></td>
<td><font face="Arial" size="2"><% = RS("ProductType") %></font></td>
<td><font face="Arial" size="2"><% = RS("Details") %></font></td>
<td><font face="Arial" size="2"><% = RS("Price") %></font></td>
</tr>
<%
RS.MoveNext
Loop
%>
</table>
<%
End If
RS.Close
Set RS = Nothing
Conn.Close
Set Conn = Nothing
%>
</BODY>
</HTML>