create table if it doesn't exist, otherwise write to it.

Dev Shed Forums Sponsor:

Stop making mediocre tutorials.The best tutorials are video! Camtasia Studio makes it easy to create engaging, buzz-building screen videos at any size, in any popular format. Download the free trial!

June 10th, 2003, 12:41 PM

merlinti

Contributing User

Join Date: Aug 2002

Location: Miami, FL

Posts: 134

Time spent in forums: 1 Day 24 m 17 sec
Reputation Power: 6

create table if it doesn't exist, otherwise write to it.

Hello,

I have an asp page that gives results for a search from a previous page from streetguide.streets in MySql. The page displays the UserName of the person logged in.

I have the results of this query displayed inside a form with hidden variables. When the user clicks "save to my folder" I have it create a table in the mysql db with the same name as the user name, and then write to this table.

Now I can't get it to make the first column "FolderID int not null primary key auto_increment"
I get an error when I try that.

Also I nee the person to be able to write to their table as many times as they want. So I guess I need some code that will say:

If a table exists with the name of this persons "UserName", then skip the statement that creates the table and go to the next statement that will just write to it.?

Here is what I have so far:

Code:


<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>

<%
        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

'This captures your username from the previous form as well as other data
UserName = Request.Form("UserName" )
FolderID = 1
StreetName = Request.Form("StreetName" )
Block = Request.Form("Block" )
City = Request.Form("City" )


Response.Write(UserName)
Response.Write("

" )
Response.Write(StreetName)
Response.Write("
" )
Response.Write(Block)
Response.Write("
" )
Response.Write(City)
Response.Write(FolderID)



'Insert username into SQL String
strSQL = "CREATE TABLE " & UserName & " (FolderID int(20), StreetName varchar(50), Block varchar(50), City varchar(50))"
conn.Execute strSQL 

'Insert data into new table
strSQL2 = "INSERT INTO " & UserName & " (FolderID, StreetName, Block, City) VALUES ('" & FolderID & "','" & StreetName & "','" & Block & "','" & City & "')"
conn.Execute strSQL2


%>

thanks for the help

June 11th, 2003, 09:17 PM

mohecan

Contributing User

Join Date: Feb 2003

Location: Melbourne, Australia

Posts: 212

Time spent in forums: < 1 sec
Reputation Power: 6

You can use schema properties for the database to determine whether or not the table exists.
see http://www.devguru.com/Technologies...openschema.html
thus determining whether or not to insert into the table, or create the table.

__________________
How can I soar like an eagle when
I'm flying with turkey's?

June 12th, 2003, 11:05 AM

merlinti

Contributing User

Join Date: Aug 2002

Location: Miami, FL

Posts: 134

Time spent in forums: 1 Day 24 m 17 sec
Reputation Power: 6

I got that part figured out by a kind fellow (Tiranis) who helped me out, so I thought I'd share it with you.

Here it is:

Code:


<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<%
        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>

              </div>
            </td>
            <td><div align="center">
                <table width="100%" border="0" cellspacing="0" cellpadding="0">
                  <tr class="SignPost">
                    <td>&nbsp;</td>
                    <td valign="top"><div align="center"> <img src="../assets/images/buttons/signpost01.jpg" width="14" height="5"></div>
                    </td>
                    <td>&nbsp;</td>
                  </tr>
                  <tr>
                    <td valign="top"><div align="right"><img src="../assets/images/buttons/btn_bg_left01.jpg" width="12" height="25"></div>
                    </td>
                    <td width="45" valign="top" background="../assets/images/buttons/btn_bg_mid01.jpg" class="SubNavText"><div align="center"><a href="../contact.asp" class="SubNavText">Contact</a></div>
                    </td>
                    <td valign="top"><div align="left"><img src="../assets/images/buttons/btn_bg_right01.jpg" width="12" height="25"></div>
                    </td>
                  </tr>
                </table>
              </div>
            </td>
            <td width="10">&nbsp;</td>
          </tr>
        </table>
        <table width="100%" border="0" cellspacing="6" cellpadding="6">
          <tr>
            <td valign="top"><table width="100%" border="0" cellpadding="0" cellspacing="0" class="MemberLinkBG">
              <tr>
                <td width="5">&nbsp;</td>
                <td width="270"><p><span class="WelcomeBackTxt">Welcome back</span><font color="#FFFFFF"> </font><span class="WelcomeBackTxt"></span></p></td>
                <td><p align="right"><a href="streetsearch.asp" class="MemberLinks">STREET SEARCH</a> <font color="#000000" size="1">l</font>  <a href="locations.asp" class="MemberLinks">OTHER
                      LOCATIONS</a> <font color="#000000" size="1">l</font><a href="maps.asp" class="MemberLinks"> MAPS</a></p></td>
                <td width="5">&nbsp;</td>
              </tr>
            </table>            
              <br>
              <table width="100%" border="0" cellspacing="4" cellpadding="4">
              <tr>
                <td><span class="Title">Added 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>&nbsp;</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>
      <td width="750" valign="top" background="../assets/images/bottom.jpg"><div align="left"><img src="../assets/images/blank.gif" width="22" height="35" align="absmiddle"> <span class="BottomText">© 2003
            Directions™</span><font size="2" face="Verdana, Arial, Helvetica, sans-serif"><img src="../assets/images/blank.gif" width="450" height="35" align="middle"></font><span class="BottomText">Website
            by Computer Intellect</span></div></td>
      <td background="../assets/images/main_btm_outerBG.jpg">&nbsp;</td>
    </tr>
  </table>
</div>
</body>
</html>

Now my problem is that when the user goes to the "myfolder.asp" page and it shows a repeating region of the content of their table, I have a delete record button on each row, but I get an error in the browser:

Error Type:
ADODB.Field (0x800A0BCD)
Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.
/streetguide03/members/myfolder.asp, line 416

I placed a delete record behavior on the button and that's when I get the error, but just listing the content was OK before.

thanks

June 12th, 2003, 06:46 PM

merlinti

Contributing User

Join Date: Aug 2002

Location: Miami, FL

Posts: 134

Time spent in forums: 1 Day 24 m 17 sec
Reputation Power: 6

OK, I got it somewhat working.

It's deleting the first record in the users table instead of the one I selected to delete.

Here's the code:

Code:


<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="../Connections/streetguide.asp" -->
<%
' *** Edit Operations: declare variables

Dim MM_editAction
Dim MM_abortEdit
Dim MM_editQuery
Dim MM_editCmd

Dim MM_editConnection
Dim MM_editTable
Dim MM_editRedirectUrl
Dim MM_editColumn
Dim MM_recordId

Dim MM_fieldsStr
Dim MM_columnsStr
Dim MM_fields
Dim MM_columns
Dim MM_typeArray
Dim MM_formVal
Dim MM_delim
Dim MM_altVal
Dim MM_emptyVal
Dim MM_i

MM_editAction = CStr(Request.ServerVariables("SCRIPT_NAME"))
If (Request.QueryString <> "") Then
  MM_editAction = MM_editAction & "?" & Request.QueryString
End If

' boolean to abort record edit
MM_abortEdit = false

' query string to execute
MM_editQuery = ""
%>
<%
' *** Delete Record: declare variables

if (CStr(Request("MM_delete")) = "DeleteRecord" And CStr(Request("MM_recordId")) <> "") Then

  MM_editConnection = MM_streetguide_STRING
  MM_editTable = "streetguide.merlin"
  MM_editColumn = "FolderID"
  MM_recordId = "" + Request.Form("MM_recordId") + ""
  MM_editRedirectUrl = "myfolder.asp"

  ' append the query string to the redirect URL
  If (MM_editRedirectUrl <> "" And Request.QueryString <> "") Then
    If (InStr(1, MM_editRedirectUrl, "?", vbTextCompare) = 0 And Request.QueryString <> "") Then
      MM_editRedirectUrl = MM_editRedirectUrl & "?" & Request.QueryString
    Else
      MM_editRedirectUrl = MM_editRedirectUrl & "&" & Request.QueryString
    End If
  End If
  
End If
%>
<%
' *** Delete Record: construct a sql delete statement and execute it

If (CStr(Request("MM_delete")) <> "" And CStr(Request("MM_recordId")) <> "") Then

  ' create the sql delete statement
  MM_editQuery = "delete from " & MM_editTable & " where " & MM_editColumn & " = " & MM_recordId

  If (Not MM_abortEdit) Then
    ' execute the delete
    Set MM_editCmd = Server.CreateObject("ADODB.Command")
    MM_editCmd.ActiveConnection = MM_editConnection
    MM_editCmd.CommandText = MM_editQuery
    MM_editCmd.Execute
    MM_editCmd.ActiveConnection.Close

    If (MM_editRedirectUrl <> "") Then
      Response.Redirect(MM_editRedirectUrl)
    End If
  End If

End If
%>
<%
        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 
%>
<%
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
%>




<% 
Dim rsMyFolder
Dim rsMyFolder_numRows
Dim UserName

UserName = rsMember.Fields.Item("UserName").Value
%>

<%
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
Response.Write(UserName)
%>
<%
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>
                             <form ACTION="<%=MM_editAction%>" METHOD="POST" enctype="application/x-www-form-urlencoded" name="DeleteRecord" id="DeleteRecord">
            <p><span class="Title"> &nbsp;My 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></p>
            
            
            
            
            
            <% 
While ((Repeat1__numRows <> 0) AND (NOT rsMyFolder.EOF)) 
%>
            <table width="100%" border="0" cellspacing="4" cellpadding="4">
                <tr>
                    <td width="25"><div align="center">
                        <input type="submit" name="Submit" value="Delete">
                      </div>
                    </td>
                    <td><p><%=(rsMyFolder.Fields.Item("StreetName").Value)%>, <%=(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
  rsMyFolder.MoveFirst()
%>

            <p>&nbsp;              </p>
            <p align="center">&nbsp;</p>
            <p></p>
                    
                        <input type="hidden" name="MM_delete" value="DeleteRecord">
            <input type="hidden" name="MM_recordId" value="<%= rsMyFolder.Fields.Item("FolderID").Value %>">
                </form>
            <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>
      <td width="750" valign="top" background="../assets/images/bottom.jpg"><div align="left"><img src="../assets/images/blank.gif" width="22" height="35" align="absmiddle"> <span class="BottomText">© 2003
            Directions™</span><font size="2" face="Verdana, Arial, Helvetica, sans-serif"><img src="../assets/images/blank.gif" width="450" height="35" align="middle"></font><span class="BottomText">Website
            by Computer Intellect</span></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
%>

thanks

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: