asked on

Display Results to Grid / Table

I am able to connect to a db and send a SQL successfully, but I need the results to populate a grid or table. How can I do that? Below is the code that connect to the db and returns the records one by one to a msgbox.

Thanks in advance.
Pandkyon

<HTML>
<HEAD>
<TITLE>Working With VBScript: Exercise 2</TITLE>
<SCRIPT LANGUAGE="VBScript">
<!-- Add this to instruct non-IE browsers to skip over VBScript modules.
Option Explicit
 
Sub cmdCalculate_OnClick
msgbox "click"
dim conn
set conn = CreateObject("ADODB.Connection")
' Connect to the database
dim strconnect
strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=R:\TMP\testDB.mdb;"
conn.Open strConnect
 
msgbox "db opened"
dim strSQL
 
StrSQL = "SELECT * FROM claimCounts WHERE Submitted >244000"
dim rs
Set rs = conn.Execute(StrSQL)
Do While not rs.EOF
    msgbox "The information is " & RS(0)
    
    rs.MoveNext
Loop
 
  
  End Sub
-->
</SCRIPT>
</HEAD>

Open in new window

ASKER CERTIFIED SOLUTION

dan_neal

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

pandkyon

ASKER

Dan,

Thanks for the response; I modified the code and I've attached the results. It appears that it cycling though and returning the field names and not the actual values. See the snapshot of the results.

Pandkyon
results.doc

dan_neal

Darn copy and paste.
Do While loop should look like this:

         Do While not rs.EOF
		strtbl = strtbl & "<tr>"
		for i = 0 to rs.fields.count - 1
			strtbl = strtbl & "<td>" & rs(i) & "</td>"
		next
		strtbl = strtbl & "</tr>"
		rs.MoveNext
	Loop

Open in new window

pandkyon

ASKER

Great Job, Dan! Thanks

pandkyon

ASKER

Final code:

<HTML>
<HEAD>
<TITLE>Working With VBScript: Exercise 2</TITLE>
<SCRIPT LANGUAGE="VBScript">
<!-- Add this to instruct non-IE browsers to skip over VBScript modules.
Option Explicit
 
Sub cmdCalculate_OnClick
msgbox "click"
dim conn
set conn = CreateObject("ADODB.Connection")
' Connect to the database
dim strconnect
strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=R:\TMP\testDB.mdb;"
conn.Open strConnect
 
msgbox "db opened"
dim strSQL
dim strTbl, i, c
strTbl = ""
StrSQL = "SELECT * FROM claimCounts WHERE Submitted >244000"
dim rs
Set rs = conn.Execute(StrSQL)
if not rs.eof then
	strTbl = "<table>"
	strtbl = strtbl & "<tr>"
	for each c in rs.fields
		strtbl = strtbl & "<th>" & c.name & "</th>"
	next
	strtbl = strtbl & "</tr>"
 
Do While not rs.EOF
		strtbl = strtbl & "<tr>"
		for i = 0 to rs.fields.count - 1
			strtbl = strtbl & "<td>" & rs(i) & "</td>"
		next
		strtbl = strtbl & "</tr>"
		rs.MoveNext
	Loop
 
	strtbl = strtbl & "</table>"
end if
rs.close
conn.close
set rs = nothing
set conn = nothing
ResultsTbl.innerhtml = strtbl
End Sub
-->
</SCRIPT>
</HEAD>
<body>
<div id="ResultsTbl"></div>
</body>
</html>
 
 <INPUT TYPE="Button" NAME="cmdCalculate" VALUE="Run Query">

Open in new window

dan_neal

Great. Glad to help.