• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 222
  • Last Modified:

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

0
pandkyon
Asked:
pandkyon
  • 3
  • 3
1 Solution
 
dan_nealCommented:
Give this a whirl.  Let me know if you have any questions.
<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
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 each c in rs.fields
			strtbl = strtbl & "<td>" & c.name & "</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>

Open in new window

0
 
pandkyonAuthor Commented:
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
0
 
dan_nealCommented:
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

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
pandkyonAuthor Commented:
Great Job, Dan!  Thanks
0
 
pandkyonAuthor Commented:
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

0
 
dan_nealCommented:
Great.  Glad to help.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now