ASP Pages and Databases

[chris124]

Hello

I'm a beginner to asp and databases and wish to learn how to create one for a website, I have at present setup an access database (although there isn't anything in it yet), hosted it on my windows server, and it's now active, what I need to do now is to create a form that will call specific data from it and display that information in an asp page.

I know not everyone likes DreamweaverMX 2004 for creating asp pages but for now until I get a hang of it I wish to use it, I am creating my pages using the ASP VBScript template from Dreamweaver, I have created a database connection within it (I believe it works).

I need a quick lesson on how to create the form, retrieve the specific data and publish it on an asp webpage, I have purposely left the database empty for this lesson, another reason for leaving this database empty is because I have access to a ASP.Net Enterprice Manager through the ISP, there are many fields on this that I don't understand so if anyone knows how to fully use this it would be appreciated.

Thanks
Chris124

[skhanal]

www.asp.net is a good resource for asp programmers.

[Dan McGraw]

The challange here is in two parts. the ASP and the script to access the DB.

Someone else may have to supply the ASP stuff. The scripting stuff is pretty easy.

Below is some code I have 'borrowed' from Remote Control (its available for free at www.laek.com)

What the script below will do is log onto a DB, search a given table and field for a specified value and return the whole row. I hope this helps along the way.

Now you need someone who knows ASP to wrap around it.






'SENDIMMEDIATE has been selected
'SCRIPTPROCESS = Create
Server = "ServerName"
UserName = "UserName"
Password = "Password"
Database = "DBName"
Table = "TableName"
Field = "FieldName"
Search = "SearchValue"
On Error Resume Next

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adUseClient = 3
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")
Dim strTemp
Dim strFieldValue
Dim intPos
Dim intPos2

If UserName = "" Then
objConnection.Open "Provider=sqloledb;Data Source=" & Server & ";Initial Catalog=" & Database & ";Integrated Security=SSPI"
Else
objConnection.Open "Provider=sqloledb;Data Source=" & Server & ";Initial Catalog=" & Database & ";User Id=" & UserName & ";Password=" & Password & ""
End If
If objConnection.State = 0 Then
WScript.Echo "Could not connect to SQL database on " & Server
Else
objRecordset.CursorLocation = adUseClient
objRecordset.Open "SELECT * FROM " & Table & " WHERE " & Field & " LIKE '%" & Search & "%' ", objConnection, adOpenStatic, adLockOptimistic
WScript.Echo "Found " & objRecordset.RecordCount & " records"
If Not objRecordset.EOF Then
objRecordset.MoveFirst
While Not objRecordset.EOF
WScript.Echo " "
WScript.Echo " "
WScript.Echo "************************************************* ****************************"
WScript.Echo "Record " & objRecordset.AbsolutePosition
WScript.Echo "************************************************* ****************************"
For Each objField in objRecordset.Fields
If Search = "" Then
WScript.Echo objField.Name & ":" & objField.Value
Else
If UCase(objField.Name) = UCase(Field) Then
strFieldValue = objField.Value
strTemp = ""
intPos = 1
intPos2 = 1
While intPos2 > 0
intPos2 = InStr(intPos, strFieldValue, Search)
if intPos2 > 0 Then
strTemp = strTemp & Mid(strFieldValue, intPos, intPos2 - intPos) & "===>" & Search & "<==="
intPos = intPos2 + Len(Search)
End If
Wend
If intPos < Len(strFieldValue) Then
strTemp = strTemp & Mid(strFieldValue, intPos, Len(strFieldValue) - intPos + 1)
End If
WScript.Echo objField.Name & ":" & strTemp
Else
WScript.Echo objField.Name & ":" & objField.Value
End If
End If
Next
objRecordset.MoveNext
Wend
End If
objRecordset.Close
objConnection.Close
End If