ASP Programming
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me
Go Back   Dev Shed Forums > Programming Languages - More > ASP Programming
Reload this Page

If table doesn't exist show friendly message.

Discuss If table doesn't exist show friendly message. in the ASP Programming forum on Dev Shed.
If table doesn't exist show friendly message. ASP Programming forum discussing Active Server Pages coding techniques and problem solving methods. Use VBScript or Jscript to make dynamic web applications.

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
« Previous Thread | Next Thread »  
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
Stay one step ahead of the competition. Evaluate and give feedback on some of the hottest web development tools on the market today. Make your opinion heard! Click Here
  #1  
Old June 17th, 2003, 01:01 PM
merlinti's Avatar
merlinti merlinti is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Aug 2002
Location: Miami, FL
Posts: 134 merlinti User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 Day 24 m 17 sec
Reputation Power: 6
If table doesn't exist show friendly message.
Hi All,

Just when I thought I was done , I try going to the "myfolder.asp" page and I get an error:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E37)
[MySQL][ODBC 3.51 Driver][mysqld-3.23.55-nt]Table 'streetguide.max' doesn't exist
/streetguide03/members/myfolder.asp, line 73

This is because the user didn't have a folder created, because they didn't save anything to their folder.
On another page I have code that creates a table with the same name as the UserName, only if they decide to save something in their folder.

I tried doing the show region if recordset is not empty thing, but I think I need to do it somewhere up further in the code where it does the query. I just don't know exactly where.

Here is the code I have:


[CODE]

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="../Connections/streetguide.asp" -->



<%
'/////////////////////////////////////////////////////////////////////////
' *** Restrict Access To Page: Grant or deny access to this page

MM_authorizedUsers="1"
MM_authFailedURL="../login.asp"
MM_grantAccess=false
If Session("MM_Username") <> "" Then
If (false Or CStr(Session("MM_UserAuthorization"))="") Or _
(InStr(1,MM_authorizedUsers,Session("MM_UserAuthorization"))>=1) Then
MM_grantAccess = true
End If
End If
If Not MM_grantAccess Then
MM_qsChar = "?"
If (InStr(1,MM_authFailedURL,"?") >= 1) Then MM_qsChar = "&"
MM_referrer = Request.ServerVariables("URL")
if (Len(Request.QueryString()) > 0) Then MM_referrer = MM_referrer & "?" & Request.QueryString()
MM_authFailedURL = MM_authFailedURL & MM_qsChar & "accessdenied=" & Server.URLEncode(MM_referrer)
Response.Redirect(MM_authFailedURL)
End If

'/////////////////////////////////////////////////////////////////////////////////
%>


<%
'/////////////////////////////////////////////////////////////////////////////////
' *** rsMember Recordset ***

Dim rsMember__strUserName
rsMember__strUserName = "xyz"
If (Session("MM_UserName") <> "") Then
rsMember__strUserName = Session("MM_UserName")
End If
%>
<%
Dim rsMember
Dim rsMember_numRows

Set rsMember = Server.CreateObject("ADODB.Recordset")
rsMember.ActiveConnection = MM_streetguide_STRING
rsMember.Source = "SELECT * FROM streetguide.members WHERE UserName = '" + Replace(rsMember__strUserName, "'", "''") + "'"
rsMember.CursorType = 0
rsMember.CursorLocation = 2
rsMember.LockType = 1
rsMember.Open()
rsMember_numRows = 0
UserName = rsMember.Fields.Item("UserName" ).Value

'////////////////////////////////////////////////////////////////////////////////
%>


<%
'////////////////////////////////////////////////////////////////////////////////
' *** rsMyFolder Recordset ***

Dim rsMyFolder
Dim rsMyFolder_numRows

Set rsMyFolder = Server.CreateObject("ADODB.Recordset" )
rsMyFolder.ActiveConnection = MM_streetguide_STRING
rsMyFolder.Source = "SELECT * FROM "& UserName
rsMyFolder.CursorType = 0
rsMyFolder.CursorLocation = 2
rsMyFolder.LockType = 1
rsMyFolder.Open()
rsMyFolder_numRows = 0

'////////////////////////////////////////////////////////////////////////////////
%>

<%
'////////////////////////////////////////////////////////////////////////////////
' *** rsMyFolder Repeat Region ***

Dim Repeat1__numRows
Dim Repeat1__index

Repeat1__numRows = -1
Repeat1__index = 0
rsMyFolder_numRows = rsMyFolder_numRows + Repeat1__numRows

'////////////////////////////////////////////////////////////////////////////////
%>




<html>
<head>
<title>Directions Steet Guide - Members Home</title>
<link href="../assets/css/streets.css" rel="stylesheet" type="text/css">
<script language="JavaScript" type="text/JavaScript">

<% If rsMyFolder.EOF And rsMyFolder.BOF Then %>
<p>Your folder is empty.</p>
<% End If ' end rsMember.EOF And rsMember.BOF %>
<% If Not rsMyFolder.EOF Or Not rsMyFolder.BOF Then %>
<table width="100%" border="0" cellpadding="0" cellspacing="0" class="BorderBtmMyfolder">
<tr>
<td>&nbsp;</td>
</tr>
</table>
<% While ((Repeat1__numRows <> 0) AND (NOT rsMyFolder.EOF)) %>

<table width="100%" border="0" cellpadding="0" cellspacing="0" class="BorderBtmMyfolder">
<%If (Repeat1__numRows Mod 2) Then%>
<tr style="background-color:#D5DADB">
<%Else%>
<tr style="background-color:#C5CCCF">
<%End If%>
<td width="25" valign="middle"><div align="center"><a href="deletefromfolder.asp?FolderID=<%=(rsMyFolder.Fields.Item("FolderID").Value)%>&UserName=<%=(rsMember.Fields.Item("UserName").Value)%>"><img src="../assets/images/buttons/delete.gif" alt="Delete" width="23" height="23" border="0"></a>
</div>
</td>
<td><p><span class="StreetMyFolder"><%=(rsMyFolder.Fields.Item("StreetName").Value)%></span>, <%=(rsMyFolder.Fields.Item("Block").Value)%>, <%=(rsMyFolder.Fields.Item("City").Value)%>, <%=(rsMyFolder.Fields.Item("Direct1").Value)%>, <%=(rsMyFolder.Fields.Item("Direct2").Value)%>, <%=(rsMyFolder.Fields.Item("Cross2").Value)%>, <%=(rsMyFolder.Fields.Item("Direct3").Value)%>, <%=(rsMyFolder.Fields.Item("Zip").Value)%></p>
</td>
</tr>
</table>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
rsMyFolder.MoveNext()
Wend
%>
<% End If ' end Not rsMember.EOF Or NOT rsMember.BOF %>
<p>&nbsp; </p>
<p align="center"></p>
<p></p>
<p>&nbsp;</p>
</td>
</tr>
</table>
<p align="left">&nbsp;</p>
</div>
</td>
<td background="../assets/images/main_btm_outerBG.jpg">&nbsp;</td>
</tr>
</table>

</div>
</body>
</html>
<%
rsMember.Close()
Set rsMember = Nothing
%>
<%
rsMyFolder.Close()
Set rsMyFolder = Nothing
%>


[CODE]
Reply With Quote
  #2  
Old June 17th, 2003, 01:20 PM
Vlince Vlince is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Jun 2003
Location: Canada, Quebec, Montreal
Posts: 410 Vlince User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 5
What's line 73 ?
What is your question?

The reason I'm asking is because you show an error message but then you say:

"This is because the user didn't have a folder created, because they didn't save anything to their folder."

So you *know* why you are getting the error right?

Why not, to avoid the error mesage you are showing us, make some sort of validation so that the user *always* have a folder or that something *is* saved inside their folder? no?

Doesn't that make sense?

If your asking why? or How can I fix the error message then make a Response.Write of your SQL Query *BEFORE* you execute it, something like:

'FOR DEBUG ONLY
Response.Write strSql & "<hr>"
Response.End

You put this piece of code *BEFORE* you execute it, this will show the SQL Query YOU ARE ABOUT TO EXECUTE inside your browser.

Then *copy/paste* the SQL Query into Queryanalyzer(if using SQL Server) an run it!
See the error message...

Now I'm not sure, cause its been a while, but try using [...] around your TABLE name since it has a *period* inside.

Something like:

<%
Dim strSql
strSql = "SELECT * FROM [streetguide.max]"
'FOR DEBUG ONLY
Response.write strSql & "<hr>"
Response.End

...REST OF CODE...
%>

Hope this helps!
Sincerely

Vlince
Reply With Quote
  #3  
Old June 17th, 2003, 01:38 PM
merlinti's Avatar
merlinti merlinti is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Aug 2002
Location: Miami, FL
Posts: 134 merlinti User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 Day 24 m 17 sec
Reputation Power: 6
Hi Vlince,

I changed the code here with that sql write thing:

Code:

<%
'////////////////////////////////////////////////////////////////////////////////
' ***  rsMyFolder Recordset  ***

Dim rsMyFolder
Dim rsMyFolder_numRows

Set rsMyFolder = Server.CreateObject("ADODB.Recordset" )
rsMyFolder.ActiveConnection = MM_streetguide_STRING
rsMyFolder.Source = "SELECT * FROM "& UserName
rsMyFolder.CursorType = 0
rsMyFolder.CursorLocation = 2
rsMyFolder.LockType = 1
Response.Write rsMyFolder.Source & "<hr>" 
Response.End 
rsMyFolder.Open()
rsMyFolder_numRows = 0

'////////////////////////////////////////////////////////////////////////////////
%>


This is what I get:

SELECT * FROM max


Isn't there a way to use this statement "If rsMyFolder.EOF and rsMyFolder.BOF THEN" to say if this is not true then write a message instead of giving an error? And exactly where do I put it?

Something like this:

Code:

<%
'////////////////////////////////////////////////////////////////////////////////
' ***  rsMyFolder Recordset  ***

Dim rsMyFolder
Dim rsMyFolder_numRows

Set rsMyFolder = Server.CreateObject("ADODB.Recordset" )
rsMyFolder.ActiveConnection = MM_streetguide_STRING
rsMyFolder.Source = "SELECT * FROM "& UserName
rsMyFolder.CursorType = 0
rsMyFolder.CursorLocation = 2
rsMyFolder.LockType = 1
Response.Write rsMyFolder.Source & "<hr>" 
If rsMyFolder.EOF and rsMyFolder.BOF THEN
Response.Write("No Folder")
Response.End()
rsMyFolder.Open()
rsMyFolder_numRows = 0

'////////////////////////////////////////////////////////////////////////////////
%>
Last edited by merlinti : June 17th, 2003 at 01:43 PM.
Reply With Quote
  #4  
Old June 17th, 2003, 01:51 PM
Vlince Vlince is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Jun 2003
Location: Canada, Quebec, Montreal
Posts: 410 Vlince User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 5
Of course there is
a way...there is *always* a way

What your asking is:

I want to execute an SQL Query, If *NO RECORDS* are found then I want to display an error message. Otherwise...

Now what you need to do first is:

1) Create an SQL Query
1) Create a connection Object
2) Create a Recordset Object

-----------------------
NOTE: If your SQL Query is *either* an INSERT or UPDATE then you *don't* usually use a Recordset Object since they basically don't return anything.
What you use instead, is the Execute method of the Connection Object with the *adExecuteNoRecords*

On the other hand, *IF* the SQL Query your using/about to execute *IS* a SELECT Query then you'll want/need a Recordset Object since that is where the records will be held!
-----------------------

Now assuming your SQL Query is a SELECT Query
Once executed for example:

<%

...CODE...

Set objRst = objConn.Execute strSql

'To make sure your Recordset object holds any records you do:

If objRst.EOF Then
'NO RECORDS WERE FOUND...

'Close the Recordset Object and set to nothing
objRst.Close
Set objRst = nothing

'Close the Connection also and set it to nothing
objConn.Close
Set objConn = nothing

'then print an error message to your users!
Response.Write "Sorry no records were found in the database"
Response.End

Else
'YOU HAVE SOME RECORDS INSINDE YOUR RECORDSET

'So can loop if you want
While NOT objRst.EOF

Response.Write objRst(0) & "<hr>"

objRst.MoveNext
Wend

End If

'Close the Recordset Object and set to nothing
objRst.Close
Set objRst = nothing

'Close the Connection also and set it to nothing
objConn.Close
Set objConn = nothing

%>

This is off the top of my head, not tested but you get the idea...

Hope this helps!
Sincerely

Vlince
Reply With Quote
  #5  
Old June 17th, 2003, 02:50 PM
merlinti's Avatar
merlinti merlinti is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Aug 2002
Location: Miami, FL
Posts: 134 merlinti User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 Day 24 m 17 sec
Reputation Power: 6
Hi Vlince

Thanks for the help, but I still don't get what I'm doing wrong.

Here is what I have now:


Code:

<%
Dim objRst
Dim objConn
Dim strSQL

'This will list all the tables named with the specified UserName
strSQL = "SHOW TABLES LIKE " & UserName
Set objConn = Server.CreateObject("ADODB.Recordset" )
objConn.ActiveConnection = MM_streetguide_STRING
objRst.Open strSQL, objConn

If objRst.EOF THEN  'No Records Were Found
	'Close the Recordset Object and set to nothing 
objRst.Close 
Set objRst = nothing 

	'Close the Connection also and set it to nothing 
objRst.Close 
Set objRst = nothing 

	'then print an error message to your users! 
Response.Write "Sorry no records were found in the database" 
Response.End 

Else 
	'YOU HAVE SOME RECORDS INSINDE YOUR RECORDSET

'So can loop if you want 
While NOT objRst.EOF 

Response.Write objRst(0) & "<hr>" 

objRst.MoveNext 
Wend 

End If 

'Close the Recordset Object and set to nothing 
objRst.Close 
Set objRst = nothing 

'Close the Connection also and set it to nothing 
objConn.Close 
Set objConn = nothing
%>


Error:

Error Type:
Microsoft VBScript runtime (0x800A01A8)
Object required: ''
/streetguide03/members/myfolder.asp, line 69
Last edited by merlinti : June 17th, 2003 at 02:53 PM.
Reply With Quote
  #6  
Old June 17th, 2003, 03:30 PM
Vlince Vlince is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Jun 2003
Location: Canada, Quebec, Montreal
Posts: 410 Vlince User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 5
what is this
strSQL = "SHOW TABLES LIKE " & UserName


Is this some sort of new SQL Language?
SHOW TABLES LIKE

Forgive my ignorance about this new language but perhaps you should try with a simple SQL statement.

<%
. . .
. .
.

strSql = "SELECT * FROM your_table_name"
'FOR DEBUG ONLY
Response.Write strSql & "<hr>"
Response.End


Set objConn = Server.CreateObject("ADODB.Recordset" )
objConn.ActiveConnection = MM_streetguide_STRING
objRst.Open strSQL, objConn

. . .
. .
.
%>

Comment the two lines under the 'FOR DEBUG ONLY once you've made sure the SQL Query works!

Hope this helps!
Sincerely

Vlince
Reply With Quote
  #7  
Old June 17th, 2003, 04:07 PM
merlinti's Avatar
merlinti merlinti is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Aug 2002
Location: Miami, FL
Posts: 134 merlinti User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 Day 24 m 17 sec
Reputation Power: 6
Hi Vince,

The UserName is the users name and the name of their table.
The code works on another page that I have.


Code:
strSQL = "SHOW TABLES LIKE " & UserName
Response.Write strSql & "<hr>" 
Response.End



I did this and the output was this:

SHOW TABLES LIKE max

It looks correct, no?
Last edited by merlinti : June 17th, 2003 at 04:10 PM.
Reply With Quote
  #8  
Old June 17th, 2003, 04:21 PM
Vlince Vlince is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Jun 2003
Location: Canada, Quebec, Montreal
Posts: 410 Vlince User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 5
Of course it looks correct

You are making a Response.write of your variable *strSql*

And if you look carefully, this is what *you* did in your previous post:


---BEGIN QUOTE---
'This will list all the tables named with the specified UserName
strSQL = "SHOW TABLES LIKE " & UserName
---END QUOTE---

So of course the output is going to be:
SHOW TABLES LIKE max

But my question to you is:
Is this so called SQL Query good?
In other words, did *you* copy/paste this Query into QueryAnalyzer(if using SQL Server)?
Did you?

If you did, then what did SQL Server say?

Did it say it was ok?

Did it returned to you the records *you* wanted/expected?

What did the SQL Server say?

You are showing me result of your Response.Write and I thank you for that but that isn't going to help you now is it?

Try this:

Replace your SQL Query:
strSQL = "SHOW TABLES LIKE " & UserName
Response.Write strSql & "<hr>"
Response.End

With this one:
strSQL = "SELECT * FROM table_name"
Response.Write strSql & "<hr>"
Response.End


Where table_name is the name of one of your TABLE inside your database.

Now if you decide to use streetguide.max for the table_name then I strongly suggest you use the [...] syntax.

Bottom line should be this:
strSQL = "SELECT * FROM [streetguide.max]"
Response.Write strSql & "<hr>"
Response.End

Now I can't help you more then that unless you tell me what happens when you copy/paste your Query into QueryAnalyzer and execute it!

Not only that, you could avoid *all* these post if you would've tried this in the first place, that is putting the [....] around your streetguide.max TABLE.

Try it...please

Now don't get me wrong, I'm not mad or anything I'm just asking you to do these test so that *we* can both try and fix your problem !

Hope this helps!
Sincerely

Vlince
Reply With Quote
  #9  
Old June 17th, 2003, 04:41 PM
merlinti's Avatar
merlinti merlinti is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Aug 2002
Location: Miami, FL
Posts: 134 merlinti User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 Day 24 m 17 sec
Reputation Power: 6
Sorry Vlince, but I only have MySQL.
I don't know of any QueryAnalyzer.
Is that available for MySQL?

I did the changes and came up with this:

SELECT * FROM max
Reply With Quote
  #10  
Old June 17th, 2003, 05:00 PM
Vlince Vlince is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Jun 2003
Location: Canada, Quebec, Montreal
Posts: 410 Vlince User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 5
Don't be sorry...its not your fault in fact its nobody's fault, that is the software you have which is great, although I've never used MySQL but I'm pretty sure they have some sort of tools(client tools) that allows you to paste an SQL Query and run it no?

Anyway...

Ok so you did the changes now, do you still get the error message from your first post ?

Also, do you have a table named max or is your table named streetguide.max?

If you tell me...well my table name is streetguide.max then you know in advance that the SQL Query:
SELECT * FROM max
Won't work right?

Ask yourself these question this way you'll know the answer without even testing it right?

Again, if you have *NO* table called max then you agree with me that the SQL Query won't work right! and you know that without even testing it cause you're asking for a table name that doesn't even exist!

Anyway...

Your inital problem(and I looked at your first post) was that you were getting an error:


Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E37)
[MySQL][ODBC 3.51 Driver][mysqld-3.23.55-nt]Table 'streetguide.max' doesn't exist
/streetguide03/members/myfolder.asp, line 73


So did you read the error message?
Look here:
Table 'streetguide.max' doesn't exist
That's right, the TABLE streetguide.max DOESN'T EXIST

So this means you'll have to figure what *you* are doing wrong

Anyway, I'm leaving to go home now

Hope this helps!
Sincerely

Vlince
Reply With Quote
  #11  
Old June 17th, 2003, 05:57 PM
merlinti's Avatar
merlinti merlinti is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Aug 2002
Location: Miami, FL
Posts: 134 merlinti User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 Day 24 m 17 sec
Reputation Power: 6
Hi Vlince,

Thanks for all the help.

Maybe I didn't make it clear as to what I wanted to do.

I know that the table doen't exist, and that's why I want the message to appear saying "You have nothing in your folder.

It would find the table if they created one on this page: "addtomyfolder.asp"

Here is most of the code for that page (it works).

Code:

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="../Connections/streetguide.asp" -->
<%
'/////////////////////////////////////////////////////////////////////////////////
' ***  rsMember Recordset  ***

Dim rsMember__strUserName
rsMember__strUserName = "xyz"
If (Session("MM_UserName")  <> "") Then 
  rsMember__strUserName = Session("MM_UserName") 
End If
%>
<%
Dim rsMember
Dim rsMember_numRows

Set rsMember = Server.CreateObject("ADODB.Recordset")
rsMember.ActiveConnection = MM_streetguide_STRING
rsMember.Source = "SELECT *  FROM streetguide.members  WHERE UserName = '" + Replace(rsMember__strUserName, "'", "''") + "'"
rsMember.CursorType = 0
rsMember.CursorLocation = 2
rsMember.LockType = 1
rsMember.Open()
rsMember_numRows = 0
UserName = rsMember.Fields.Item("UserName" ).Value

'////////////////////////////////////////////////////////////////////////////////
%>
<%
        MM_streetguide_STRING = "driver={mysql ODBC 3.51 Driver};server=localhost;uid=uid;"
        MM_streetguide_STRING = MM_streetguide_STRING & "pwd=pwd;database=streetguide"             
        Set conn = Server.CreateObject("ADODB.Connection") 
                conn.Open MM_streetguide_STRING 
%>

<% 

'This is where the work happens
Dim strSQL
Dim strSQL2
Dim UserName
Dim FolderID
Dim StreetName
Dim Block
Dim City
Dim	State1
Dim	Direct1
Dim	Cross1
Dim	Direct2
Dim	Cross2
Dim	Direct3
Dim	Zip


'This captures your username from the previous form as well as other data
UserName = Request.Form("UserName")
StreetName = Request.Form("StreetName" )
Block = Request.Form("Block" )
City = Request.Form("City" )
States = Request.Form("States" )
Direct1 = Request.Form("Direct1" )
Cross1 = Request.Form("Cross1" )
Direct2 = Request.Form("Direct2" )
Cross2 = Request.Form("Cross2" )
Direct3 = Request.Form("Direct3" )
Zip = Request.Form("Zip" )



%>





<%
Set oRs = Server.CreateObject("ADODB.Recordset" )
'This will list all the tables named with the specified UserName
strSQL = "SHOW TABLES LIKE '" & UserName & "';"
oRs.Open strSQL,conn,2,3

If oRs.EOF and oRs.BOF THEN  'means that the table does not exist
	'need to create table
	'Insert username into SQL String
	strSQL = "CREATE TABLE " & UserName & " (FolderID int not null primary key auto_increment, StreetName varchar(100), Block varchar(100), City varchar(100), States varchar(100), Direct1 varchar(100), Cross1 varchar(100), Direct2 varchar(100), Cross2 varchar(100), Direct3 varchar(100), Zip varchar(50))"
	conn.Execute strSQL 
End If
	
'Insert data into table whether we created it or not
'Don't need Folder ID since it is auto_increment
strSQL2 = "INSERT INTO " & UserName & " (StreetName, Block, City, States, Direct1, Cross1, Direct2, Cross2, Direct3, Zip) VALUES ('" & StreetName & "','" & Block & "','" & City & "','" & States & "','" & Direct1 & "','" & Cross1 & "','" & Direct2 & "','" & Cross2 & "','" & Direct3 & "','" & Zip & "')"
conn.Execute strSQL2


%>





<html>
<head>
<title>Directions Steet Guide - Members Home</title>
                <td><span class="Title">Saved To Your Folder &nbsp;<img src="../assets/images/title_icon02.gif" width="13" height="11"> <img src="../assets/images/title_icon02.gif" width="13" height="11"> <img src="../assets/images/title_icon02.gif" width="13" height="11"> </span></td>
                <td><div align="right"><a href="javascript:history.back()" onFocus="if(this.blur)this.blur()"><img src="../assets/images/back.jpg" width="100" height="26" border="0" align="absmiddle"></a></div></td>
              </tr>
            </table>
            <p>&nbsp;The following directions have been added to your folder: </p>
            <p align="center" class="TitleGreen"><%= Request.Form("StreetName") %>, <%= Request.Form("Block") %>, <%= Request.Form("City") %>, <%= Request.Form("State") %></p>
            <table width="75%" border="0" align="center" cellpadding="0" cellspacing="1">
              <tr>
                <td bgcolor="#666666"><table width="100%" border="0" cellspacing="1" cellpadding="0">
                    <tr>
                      <td bgcolor="#F0EFEC"><table width="100%" border="0" cellspacing="6" cellpadding="6">
                          <tr>
                            <td><p align="center">&nbsp <%= Request.Form("Direct1") %> <%= Request.Form("Cross1") %> <%= Request.Form("Direct2") %>,<%= Request.Form("Cross2") %> <%= Request.Form("Direct3") %><%= Request.Form("Zip") %></p>
                            </td>
                          </tr>
                        </table>
                      </td>
                    </tr>
                  </table>
                </td>
              </tr>
            </table>
            <p align="center"><img src="../assets/images/folder_full.gif" alt="Added to your folder" width="45" height="38" align="absmiddle"> &nbsp;<font size="3"><strong>Saved</strong></font></p>
            <p>&nbsp;</p></td>
          </tr>
        </table>
        <p align="left">&nbsp;</p>
        </div>
      </td>
      <td background="../assets/images/main_btm_outerBG.jpg">&nbsp;</td>
    </tr>
  </table>
  <table width="100%" border="0" cellspacing="0" cellpadding="0">
    <tr>
      <td background="../assets/images/main_btm_outerBG.jpg">&nbsp;</td></body>
</html>
<%
rsMember.Close()
Set rsMember = Nothing
%>
Reply With Quote
  #12  
Old June 17th, 2003, 07:14 PM
Vlince Vlince is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Jun 2003
Location: Canada, Quebec, Montreal
Posts: 410 Vlince User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 5
Got home
had a nice dinner now I'm back in business

Ok now that we both understand what you want this is my suggestion:

Now before I start, I'd like you to know that your oRs is a Recordset. In order to look for:
*oRs.EOF* OR *oRs.BOF* your recordset must be *valide*

The thing with your code is that your recordset is in fact NOT EVEN A RECORDSET because you are getting an error *BEFORE* it even is transformed into a recordset.

Your SQL Query *ISN'T* even valide so the MySQL database says:
"Wo buddy...the SQL Query your giving me isn't good so here's an error"

NOTE: Since I don't/never used MySQL maybe it is a valide SQL Query but the bottom line is that *INSIDE* your SQL Query there is a *MISTAKE* (Could be that the table name doesn't even exist for example) now the MySQL database will *try* and run that SQL Query and it will give you an error message.

Back to your asp code, since the MySQL gave back an error message, the oRs asn't even been transformed into a recordset so no matter how hard you try to do:
oRs.EOF or oRs.BOF
it wont work!

In fact the code(oRs.EOF) isn't even being interpreted because you have an error before.

So my suggestion is:
1) You should obviously re-think the design of what/how you do things but if you can't you'll have to go with a PATCH...God I hate that...

Here add this:

<%
On Error Resume Next
strSQL = "SHOW TABLES LIKE '" & UserName & "';"
oRs.Open strSQL,conn,2,3

If Err.Number <> 0 Then

'Close recordset object
oRs.Close
Set oRs = nothing

'Close connection object
conn.Close
Set conn = nothing

'Print error message to user
Response.Write "You have nothing in your folder!"
Response.End

End If


Hope this helps!
Sincerely

Vlince
Reply With Quote
  #13  
Old June 18th, 2003, 10:41 AM
merlinti's Avatar
merlinti merlinti is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)