When I run this script I get an error "type mismatch: ubound". Can someone please tell me why I get this error and what does it mean?
Here is the code:
<%
dim strQuery
strQuery = "SELECT * FROM testcontacts"
if request("stext") <>""then
strQuery = strQuery & "WHERE CONTACT = "" & replace(request("stext"),"","")&""
end if
Dim objConn
Set objCon=Server.CreateObjetc("ADODB.Connection")
objConn.ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;"_
& "Data Source="
objConn.ConnectionString=objConn.ConnectionString & Server.MapPath(".") & "\testcontacts"
objConn.Open
Dim objRS, arrRS
Set objRS=Server.CreateObject("ADODB.Recordset")
objRS.Open "testcontacts", objConn,,,adCmdTable
if not (recSet.EOF) then
arrRS=recSet.getRows
%>
<table>
<%
for i=0 to ubound(arrRS,2)
%>
<tr>
<%
for j=0 to ubound(arrRS,1)
%>
<td>
<%=arrRS(j,i)%>
</td>
<%
next
%></tr>
<%
next
%>
</table>
<%
else response.write("Search returned no results")
end if
%>hmmmm.....looks familiar
It looks good.
The only thing I typically do different is that I create my recordsets by doing a.....
set rs = db.execute("SELECT * FROM testcontacts")
rather than using the ADODB.RECORDSET.
I'll do some testing in a bit (busy with kids at the moment) to see if that makes a difference.Further examination of your code revealed a bunch of little things that I'm not sure if they were from re-typing into the forum or what but here's what should be a working version for you.
dim strQuery
strQuery = "SELECT * FROM testcontacts"
if request("stext") <>""then
strQuery = strQuery & "WHERE CONTACT = '" & replace(request("stext"),"","")& "'"
end if
Dim objConn
Set objConn=Server.CreateObject("ADODB.Connection")
objConn.Open(getConx("DEVLSQL"))
Dim objRS, arrRS
set objRS = server.CreateObject("ADODB.RECORDSET")
objRS.open strQuery, objConn,,,adCmdTable
if not (objRS.EOF) then
arrRS=objRS.getRows
%>
<table>
<%
for i=0 to ubound(arrRS,2)
%>
<tr>
<%
for j=0 to ubound(arrRS,1)
%>
<td>
<%=arrRS(j,i)%>
</td>
<%
next
%></tr>
<%
next
%>
</table>
<%
else response.write("Search returned no results")
end if
%>
Just to recap some of the problems I found and changed....
ObjConn was spelt objCon when you declared it.
As you build the where clause for your strQuery, you needed ' not " around the value
You built the strQuery, but when you opened your recordset you just typed in the name of the table.
You used a different recordset variable to do the getRows then the one you opened.
The version I put out here was working for me so hopefully it gets you going.)hanks Putts
One day I will be able to do this for myself (???) but until that day arrives please can you explain to me what (getConx("DEVLSQL")) means? I get a "type mismatch" on this when I put all the code in as you have given it to me.
Another thing.....
as this code links to a relational db how does it know what table to access? The tables are location, region and contacts.
Thanks for your help (..and patience
)oh, sorry about the getConx, I should've replaced that with a more generic comment.
What should be there is your connection string.
GetConx is a function that I've developed to help us keep our connection strings in one locale by having getConx retrieve the actual string by just sending in a shortened version of the name (E.G. DEVLSQL). Just replace that with your connection string.
You tell it which table to access. ASP pages (and recordsets in particular) don't access entire databases as much as they access one table (or multiple if you do a JOIN statement) at a time. So, if you need a list of contacts, you would build a SQL Statement similar to....
SELECT * FROM contacts
.....and that would return all the contacts in that table.
When working with a relational database, you might quite often need to pull from two tables at one time which is done by using a JOIN statement. To learn more about that, read the FAQ in the Databasing section of our forums here.Thanks for your help PuttsGood learning experience dude.That darn typo can ruin your entire day.
<!-- w --><a class="postlink" href="http://www.ex-designz.net">www.ex-designz.net</a><!-- w -->
Dexter
Here is the code:
<%
dim strQuery
strQuery = "SELECT * FROM testcontacts"
if request("stext") <>""then
strQuery = strQuery & "WHERE CONTACT = "" & replace(request("stext"),"","")&""
end if
Dim objConn
Set objCon=Server.CreateObjetc("ADODB.Connection")
objConn.ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;"_
& "Data Source="
objConn.ConnectionString=objConn.ConnectionString & Server.MapPath(".") & "\testcontacts"
objConn.Open
Dim objRS, arrRS
Set objRS=Server.CreateObject("ADODB.Recordset")
objRS.Open "testcontacts", objConn,,,adCmdTable
if not (recSet.EOF) then
arrRS=recSet.getRows
%>
<table>
<%
for i=0 to ubound(arrRS,2)
%>
<tr>
<%
for j=0 to ubound(arrRS,1)
%>
<td>
<%=arrRS(j,i)%>
</td>
<%
next
%></tr>
<%
next
%>
</table>
<%
else response.write("Search returned no results")
end if
%>hmmmm.....looks familiar
It looks good.
The only thing I typically do different is that I create my recordsets by doing a.....
set rs = db.execute("SELECT * FROM testcontacts")
rather than using the ADODB.RECORDSET.
I'll do some testing in a bit (busy with kids at the moment) to see if that makes a difference.Further examination of your code revealed a bunch of little things that I'm not sure if they were from re-typing into the forum or what but here's what should be a working version for you.
dim strQuery
strQuery = "SELECT * FROM testcontacts"
if request("stext") <>""then
strQuery = strQuery & "WHERE CONTACT = '" & replace(request("stext"),"","")& "'"
end if
Dim objConn
Set objConn=Server.CreateObject("ADODB.Connection")
objConn.Open(getConx("DEVLSQL"))
Dim objRS, arrRS
set objRS = server.CreateObject("ADODB.RECORDSET")
objRS.open strQuery, objConn,,,adCmdTable
if not (objRS.EOF) then
arrRS=objRS.getRows
%>
<table>
<%
for i=0 to ubound(arrRS,2)
%>
<tr>
<%
for j=0 to ubound(arrRS,1)
%>
<td>
<%=arrRS(j,i)%>
</td>
<%
next
%></tr>
<%
next
%>
</table>
<%
else response.write("Search returned no results")
end if
%>
Just to recap some of the problems I found and changed....
ObjConn was spelt objCon when you declared it.
As you build the where clause for your strQuery, you needed ' not " around the value
You built the strQuery, but when you opened your recordset you just typed in the name of the table.
You used a different recordset variable to do the getRows then the one you opened.
The version I put out here was working for me so hopefully it gets you going.)hanks Putts
One day I will be able to do this for myself (???) but until that day arrives please can you explain to me what (getConx("DEVLSQL")) means? I get a "type mismatch" on this when I put all the code in as you have given it to me.
Another thing.....
as this code links to a relational db how does it know what table to access? The tables are location, region and contacts.Thanks for your help (..and patience
)oh, sorry about the getConx, I should've replaced that with a more generic comment.What should be there is your connection string.
GetConx is a function that I've developed to help us keep our connection strings in one locale by having getConx retrieve the actual string by just sending in a shortened version of the name (E.G. DEVLSQL). Just replace that with your connection string.
You tell it which table to access. ASP pages (and recordsets in particular) don't access entire databases as much as they access one table (or multiple if you do a JOIN statement) at a time. So, if you need a list of contacts, you would build a SQL Statement similar to....
SELECT * FROM contacts
.....and that would return all the contacts in that table.
When working with a relational database, you might quite often need to pull from two tables at one time which is done by using a JOIN statement. To learn more about that, read the FAQ in the Databasing section of our forums here.Thanks for your help PuttsGood learning experience dude.That darn typo can ruin your entire day.
<!-- w --><a class="postlink" href="http://www.ex-designz.net">www.ex-designz.net</a><!-- w -->
Dexter