Wrox Programmer Forums
|
Classic ASP Basics For beginner programmers starting with "classic" ASP 3, pre-".NET." NOT for ASP.NET 1.0, 1.1, or 2.0
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Basics section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old September 26th, 2006, 02:59 PM
Authorized User
 
Join Date: Sep 2006
Posts: 74
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to misskaos
Default Saving data to a table through form input

Okay, I figured out how to do what I was asking before. However, now that I've gotten just about through with the program it's still not saving my input to the database. Please help:

*I am not receiving an error message, but I know the problem lies somewhere in my INSERT statement.

The database i am using is the Northwinds database from SQL (if any of you are familiar--i believe it is the same one that is also in MS Access).

The tables I am using are:
Customers
Employees
Categories1(table I made up off of the original Categories table from the Northwind database).

Now, the only table I am trying to alter right now is the Categories1 table. I am trying to add data information from an input form

Form:
User enters a category name
User enters a category description

[Insert button - to add data]
[View Changes button - to take user back to "Select Table" page, to select their table again and see the new data added]

I have two files...
ViewTable.asp and InsertCategory.asp

The contents in ViewTable.asp are:
<%

        Dim strTableName
        Dim strActionName
        Dim strSubActionName

        strTableName = Request.Form("Table")
        strActionName = Request.Form("Action")
        strSubActionName = Request.Form("SubAction")


          Dim rs
           Dim ConnectionString
           Dim out

           Set rs = CreateObject("ADODB.RecordSet")

           ConnectionString = "Provider=SQLOLEDB.1;UID=sa;PWD=;Initial Catalog=Northwind;Data Source=localhost"




Function GetEmployeeTableRows()



rs.open "SELECT * " & _
"FROM EMPLOYEES" _
,ConnectionString


  out = ""

 If Not rs.EOF Then

 'out = out & "<TR>" & vbCrLf

For i = 0 To rs.Fields.Count - 1
 out = out & "<TD class=""header"">" & _
                                           rs.Fields(i).name & _
  "</TD>" & vbCrLf
        Next

    'out = out & "</TR>" & vbCrLf
End If


Dim val

While Not rs.EOF
out = out & "<TR>" & vbCrLf

For i = 0 To rs.Fields.Count - 1

val = rs.Fields(i).value

     If IsNull(val) Then
    val = "no info"
 ElseIf IsArray(val) Then
    val = "(array)"
     End If

 out = out & "<TD " & _
 "style=""vertical-align: top"" " & _
      ">" & _
   val & _
     "</TD>" & vbCrLf
Next

'out = out & "</TR>" & vbCrLf

                                                                      rs.MoveNext
Wend

%>
NOW VIEWING ALL RECORDS FROM EMPLOYEES TABLE. <BR>
PLEASE SCROLL DOWN TO SELECT A DIFFERENT TABLE.
<%

rs.close

GetEmployeeTableRows = out

End Function
Function GetCustomerTableRows
End Function
Function GetCategoryTableRows
'These two functions have the same exact code as the first function (GetEmployee...) except the Table names in the SELECT statements are different

End Function
%>

<%
If strTableName = "" Then
%>
<HTML>
<HEAD>

<TITLE>NO TABLE SELECTED</TITLE>
    <style>
      .header {
        font: bold 12pt sans-serif;
    color: blue;
      }

      td {
        font: 10pt sans-serif;
    color: red;
      }
    </style>
</HEAD>

<BODY>
An Error Has Occured:<BR>
 You must select a table name.
<P>
<FORM NAME = "frmClick" ACTION="Tables.asp">
<INPUT TYPE="SUBMIT" NAME="CHOOSE" VALUE="SELECT TABLE" onClick = "Tables.asp">
</FORM>
</BODY>

</HTML>

<%
End If
%>

<%
If strTableName = "Customers" Then
%>

<HTML>
<HEAD>

<TITLE>Upload Table CUSTOMERS</TITLE>
    <style>
      .header {
        font: bold 12pt sans-serif;
    color: purple;
      }

      td {
        font: 10pt sans-serif;
    color: blue;
      }
    </style>
</HEAD>

<BODY>
    <table border="1">
      <%= GetCustomerTableRows %>
    </table>
<P>
<FORM NAME = "frmClick" ACTION="Tables.asp">
<INPUT TYPE="SUBMIT" NAME="CHOOSE" VALUE="CHANGE TABLE" onClick = "Tables.asp">
</FORM>
</BODY>

</HTML>

<%
End If
%>

<%
If strTableName = "Employees" Then
%>


<HTML>
<HEAD>

<TITLE>Upload Table EMPLOYEES</TITLE>
    <style>
      .header {
        font: bold 12pt sans-serif;
    color: purple;
      }

      td {
        font: 10pt sans-serif;
    color: blue;
      }
    </style>
</HEAD>
<BODY>

    <table border="1">
      <%= GetEmployeeTableRows %>
    </table>
<P>
<FORM NAME = "frmClick" ACTION="Tables.asp">
<INPUT TYPE="SUBMIT" NAME="CHOOSE" VALUE="CHANGE TABLE" onClick = "Tables.asp">
</FORM>

</BODY>

</HTML>

<%
End If
%>

<%
If strTableName = "Categories" Then
%>

<HTML>
<HEAD>

<TITLE>Upload Table CATEGORIES</TITLE>
    <style>
      .header {
        font: bold 12pt sans-serif;
    color: purple;
      }

      td {
        font: 10pt sans-serif;
    color: blue;
      }
    </style>
</HEAD>
<BODY>

    <table border="1">
      <%= GetCategoriesTableRows %>
    </table>
<P>
<FORM NAME = "frmClick" ACTION="Tables.asp">
<INPUT TYPE="SUBMIT" NAME="CHOOSE" VALUE="CHANGE TABLE" onClick = "Tables.asp">
</FORM>
<p>
<FORM NAME = "frmClick" ACTION="InsertCategory.asp">
<INPUT TYPE="SUBMIT" NAME="CHOOSE" VALUE="INSERT CATEGORY" onClick = "InsertCategory.asp">
</FORM>

</BODY>

</HTML>

<%
End If
%>

The contents in the InsertCategory.asp file are:

<%
        Dim rs
           Dim ConnectionString
           Dim out

           Set rs = CreateObject("ADODB.RecordSet")

           ConnectionString = "Provider=SQLOLEDB.1;UID=sa;PWD=;Initial Catalog=Northwind;Data Source=localhost"


        Dim strCategoryDescription
        Dim strCategoryName


        strCategoryDescription = Request.Form("Description")
        strCategoryName = Request.Form("CategoryName")




Function InsertCategoryTableRows()





rs.open "INSERT INTO CATEGORIES1" & _
                                                                       "(CategoryName, Description)" & _
 "VALUES(' " & strCategoryName & "' ',' '" & strCategoryDescription & "')" _
 ,ConnectionString

  out = ""



End Function
%>

<%
'the Form collection of the Request object to retrieve
'the values entered by the client into an HTML form.



strCategoryName = Request.Form("CategoryName")
strCategoryDescription = Request.Form("Description")

%>


<HTML>
<HEAD>
<TITLE>INSERT NEW CATEGORY</TITLE>
</HEAD>
<BODY>
<CENTER>
<H1>INSERT CATEGORY</H1>
Please enter category information below:
<FORM NAME = "frmClick" ACTION="InsertCategory.asp" METHOD = "POST">

Category Name: <INPUT TYPE="text" NAME = "CategoryName" ><BR>
Category Description: <INPUT TYPE="text" NAME = "Description" ><BR>

<INPUT TYPE = "Submit" VALUE = "Insert" onClick="InsertCategory.asp"><BR>
</FORM>

<FORM NAME = "frmClick" ACTION="Tables.asp" METHOD="POST">
<INPUT TYPE = "Submit" VALUE = "View Changes" onClick="Tables.asp"><BR>
New Category <%= strCategoryName %>, described as: <%= strCategoryDescription %>, has been inserted.
</FORM>


</BODY>

</HTML>

Okay, I hope I put everything you needed me to put in here for you. If you see why my data is not posting to my table PLEASE let me know!

**Reminder, I'm not receiving an error message, it's just now posting my data to the table.

THANKS FOR ALL YOUR TIME AND PATIENCE!
HAVE A GREAT DAY!
__________________
T.B.
misskaos99@yahoo.com
 
Old September 26th, 2006, 03:28 PM
Authorized User
 
Join Date: Sep 2006
Posts: 74
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to misskaos
Default

OOH IM SORRY I FORGOT!!! TWO THINGS I NOTICED

1.) I SAID I HAVE TWO TABLES I ACTUALLY HAVE THREE, THE THIRD ONE IS THE FIRST PAGE: Tables.asp
2.) I NOTICED I HAVE MY INSERT STATEMENT IN THERE BUT IM NOT CALLING IT AT THE BOTTOM IN MY HTML CODE (HOW I COULD HAVE MISSED THAT I HAVE NOOOOO IDEA!)--SO I'M TRYING TO FIGURE OUT WHY IT'S NOT WORKING AGAIN. I PUT <%= GetInsertCategoryTableRows %> in the HTML body of the InsertCategory page (still has not fixed my problem, so I'm going to experiment for a bit)

But back to the first thing I noticed, the Tables.asp file.! The contents of that file are:

<HTML>
<HEAD>

<TITLE>Upload Table SELECT TABLE</TITLE>
    <style>
      .header {
        font: bold 12pt sans-serif;
    color: green;
      }

      td {
        font: 10pt sans-serif;
    color: red;
      }
    </style>
</HEAD>


 <FORM NAME = "frmTables" ACTION = "ViewTable.asp" METHOD = "POST">

        Select Table:
                <SELECT NAME="Table">
            <OPTION VALUE="">
                        <OPTION VALUE="Customers">Customers
                      <OPTION VALUE="Employees">Employees
                     <OPTION VALUE="Categories">Categories
                </SELECT>


<P>
<INPUT TYPE="SUBMIT" NAME="CHOOSE" VALUE="SELECT" onClick = "ViewTable.asp">
</FORM>

    HOPE THIS HELPS YOU HELP ME! THANKS A BUNCH!

Toni Burgess
misskaos99@yahoo.com
 
Old September 26th, 2006, 03:36 PM
Wrox Author
 
Join Date: Oct 2005
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

try this
Code:
INSERT INTO CATEGORIES1(CategoryName, Description)VALUES('" & strCategoryName &"','" & strCategoryDescription &"')
as opposed to this

Code:
INSERT INTO CATEGORIES1(CategoryName, Description)VALUES('" & strCategoryName & "' ',' '" & strCategoryDescription & "')
--Stole this from a moderator

I will only tell you how to do it, not do it for you.
Unless, of course, you want to hire me to do work for you.
 
Old September 26th, 2006, 03:43 PM
Authorized User
 
Join Date: Sep 2006
Posts: 74
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to misskaos
Default

OMG, DO YOU HAVE ANY IDEA HOW LONG I'VE BEEN TRYING TO FIGURE THAT OUT?!!? IT'S JUST RIDICULOUS THAT WE'RE ALMOST THE SAME AGE AND YOU ARE SOOOOOOOOOOOOO MUCH BETTER/FASTER AT THIS THAN I AM! YOU ARE GREAT! THANK YOU SOOOOOOOOOOOOOOOOOOO MUCH!!!

Quote:
quote:Originally posted by dparsons
 try this
Code:
INSERT INTO CATEGORIES1(CategoryName, Description)VALUES('" & strCategoryName &"','" & strCategoryDescription &"')
as opposed to this

Code:
INSERT INTO CATEGORIES1(CategoryName, Description)VALUES('" & strCategoryName & "' ',' '" & strCategoryDescription & "')
--Stole this from a moderator

I will only tell you how to do it, not do it for you.
Unless, of course, you want to hire me to do work for you.
Toni Burgess
misskaos99@yahoo.com
 
Old September 26th, 2006, 04:31 PM
Wrox Author
 
Join Date: Oct 2005
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

No worries, it just comes with experience, i have only been doing this a "few" years ;] Glad it worked out for you.

--Stole this from a moderator

I will only tell you how to do it, not do it for you.
Unless, of course, you want to hire me to do work for you.
 
Old September 27th, 2006, 11:17 AM
Authorized User
 
Join Date: Sep 2006
Posts: 74
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to misskaos
Default

Dude, I know you're going to be like "Geez, does this girl know anything!" But it's a work in progress! This only my second-third week working with ASP and SQL -- it's pretty cool though. Anyways, back to the matter at hand....

Okay, I've gotten everything doing what I originally wanted it to do! Now I'm trying to enhance it a bit. I figure the more implementing and things I do with this the more information I'll retain, so I'll be able to do it in the future with pages actually related to my job. Programmer in training ;) Some of this I learned already in school, but researching all of this really opens my eyes to how much they DIDN'T teach us!

Well, this is what I'm trying to do now...(and I'm researching it on the internet and in my books, but sometimes the syntax I find won't work properly in my program, so I have to search a little more, sometimes alter it, or whatever just to make it work).....

Okay, now I've got FOUR pages: Tables.asp, ViewTable.asp, InsertCategory.asp, DeleteCategory.asp

I've got it viewing the table you select and I can Insert a category name and delete it from the database... NOWWW what I wanted to do was do a validation check on the Category name before it is inserted or deleted. If the table already exist when I try to insert it I want a msgbox to appear informing the user of this; never leaving the Insert Page. Once user pushes OK the box goes away and he/she is still at the Insert page to attempt to type in ANOTHER Category name to insert. If the name doesn't exist then it will add it to the database; msgbox reappears informing the user that the category name had been added to the table. NEVER leaving the Insert page. I want to do this same thing for deleting a Category name. If it doesn't exist I want the user to be informed, but I also want the page to stay on the delete category page, etc, etc. (The user can choose to continue deleting or inserting data or they'll be able to click the "view changes" button and it will take them back to the tables page to select the table they are trying to view--it's elementary I know but I have to start somewhere)

Now, I haven't changed a whole lot to the ViewTable, Tables, InsertCategory.asp files (code listed in above FORUM post so I'm not going to repost them. If needed though, please let me know.)

The code for the DeleteCategory.asp file is:

<%
        Dim rs
           Dim ConnectionString
           Dim out

           Set rs = Server.CreateObject("ADODB.Recordset")
        Set ConnectionString = Server.CreateObject("ADODB.Connection")
           ConnectionString.Open "Provider=SQLOLEDB.1;UID=sa;PWD=;Initial Catalog=Northwind;Data Source=localhost"

        Dim strCategoryDescription
        Dim strCategoryName


        strCategoryDescription = Request.Form("Description")
        strCategoryName = Request.Form("CategoryName")

Function DeleteCategoryTableRows()

strCategoryName = Request.Form("CategoryName")
strCategoryDescription = Request.Form("Description")

  sql="DELETE FROM CATEGORIES1"
  sql=sql & " WHERE CategoryName='" & strCategoryName & "'"
  on error resume next
  rs.open sql, ConnectionString
  if err<>0 then
    response.write("No Delete Permissions!")
  end if

ConnectionString.close
set ConnectionString = nothing

End Function
%>

<HTML>
<BODY>
<FORM NAME = "frmDelete" ACTION="DeleteCategory.asp" METHOD = "POST">
CategoryName: <INPUT TYPE="text" NAME = "CategoryName" ><BR>
<INPUT TYPE = "Submit" VALUE = "Delete" onClick="DeleteCategory.asp"><BR>

        <%= DeleteCategoryTableRows %>
Category <%= strCategoryName %>, has been deleted.<br>
</FORM>
    <FORM NAME = "frmClick" ACTION="Tables.asp" METHOD = "POST">
    <INPUT TYPE = "Submit" VALUE = "View Changes" onClick="Tables.asp"><BR>

    </FORM>
</BODY>
</HTML>

I haven't attempted to put the Msgbox in there anywhere, well I did, but it jus screwed my program up tooo much so I just took it all out. I'm not quite sure where exactly I need to put it. However, calling the Message Box does give me a permission not allowed error (discovered that when I first tried to put it in the DELETE form on the ViewTable.asp page).

If you can help thanks a lot, until then I'll keep searching!

Quote:
quote:Originally posted by dparsons
 No worries, it just comes with experience, i have only been doing this a "few" years ;] Glad it worked out for you.

--Stole this from a moderator

I will only tell you how to do it, not do it for you.
Unless, of course, you want to hire me to do work for you.
Toni Burgess
misskaos99@yahoo.com
 
Old September 27th, 2006, 11:33 AM
Wrox Author
 
Join Date: Oct 2005
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

Ok lets work with the first part of your post, you said that you want to check to see if the category name exists or not but the way I read that is that you are tying to check to see if a table has the same name as the value being inserted? I think what you mean is that you want to check to see if the value exists in the table.

What i might do is something like

SELECT * from [table] where column = value

Execute that query and check to see if you have a rowset greater then 0, if you do then you know the category already exists, if not call your INSERT.

For you MessageBox problems read this article:

http://classicasp.aspfaq.com/general...-from-asp.html

Then after reading that its just basic logic as to what messagebox the user sees:

If category doesnt exist
   //CALL INSERT
   //Display Message box
Else Next condition
etc etc etc
End If

To stay at the insert page just do this on InsertPage.asp

<form action="insertpage.asp">

and that will keep you from navigating away from that page.

hth

Exectue the query and check to see if you have a row set returned to you (If the rowset is 0 then you know the value doesnt exist)



--Stole this from a moderator

I will only tell you how to do it, not do it for you.
Unless, of course, you want to hire me to do work for you.
 
Old September 27th, 2006, 01:19 PM
Authorized User
 
Join Date: Sep 2006
Posts: 74
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to misskaos
Default

Well, I'm trying to do what you advised me to. However, I have never used "rowset" before and I'm searching to see how to use it, but I can't find anything for the correct syntax, or an example of it being used. I mean Im not sure if I need to declare and initialize it to zero, create a counter to add 1 whenever it comes across a rowset; but then it looks like it's a built in function--im just lost; I'm not sure how to implement rowset into my code. I'm sure it has to go on the ViewTable.asp file where I have an input box for the user to enter a category name they would like to delete; in the submit buttons "onClick" I put CategoryNameValidate(); I created this function and put the SELECT statement in there. UNDER the select statement I wrote... you know what, sometimes I can be a little slow; let me post the code.. one moment...

ViewTable.asp
[...previously posted code...]
<FORM NAME = "frmDelete" ACTION="DeleteCategory2.asp" METHOD="POST">
Category Name: <INPUT TYPE="text" NAME = "CategoryName" ><BR>
<INPUT TYPE="SUBMIT" NAME="DELETE" VALUE="DELETE CATEGORY" onClick ="CategoryNameValidate()">
</FORM>
[...previously posted code...]

and I have my function set up like this:
[...previously posted code...]
Function CategoryNameValidate()
rs.open "SELECT * FROM CATEGORIES1" & _
         "WHERE CategoryName = '"strCategoryName"'" _
         ,Connection
        
    If (CategoryName <> strCategoryName) Then
    document.write(strMessage)
    End if
End Function
[...previously posted code...]

I also have strMessage declared/initiated before all of the functions as:

Dim strMessage
strMessage = "Category Name does not exists"

I'm not gettin' an error message, it's just not doing anything.
I'll try to figure out the messagebox thing later--it's pointless anyway, I just wanted to make sure I knew how to do it.

Thanks again for all your help, you must really enjoy helping people (in regards to programming, of course)?

Toni Burgess
misskaos99@yahoo.com
 
Old September 27th, 2006, 01:28 PM
Wrox Author
 
Join Date: Oct 2005
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

Ok no problem, where you open your recordset do this:
Dim iRowCount

rs.open "[SQL Statement]", Connection, 1, 1
if not rs.eof then
   iRowCount = rs.recordCount
end if

That should work.


--Stole this from a moderator

I will only tell you how to do it, not do it for you.
Unless, of course, you want to hire me to do work for you.
 
Old September 27th, 2006, 01:54 PM
Authorized User
 
Join Date: Sep 2006
Posts: 74
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to misskaos
Default

Okay I did that and got this:

Error Type:
Microsoft VBScript compilation (0x800A0401)
Expected end of statement
/tburgess/ViewTable2.asp, line 21, column 12
,Connection 1,1
-----------^
Using the following code:

[...code...]

         ConnectionString = "Provider=SQLOLEDB.1;UID=sa;PWD=;Initial Catalog=Northwind;Data Source=localhost"
        Set rs = CreateObject("ADODB.RecordSet")

        Dim iRowCount

        rs.open "SELECT * FROM CATEGORIES1 WHERE CategoryName = '" & strCategoryName & "'" _
                     ,Connection 1,1

            if not rs.eof then
             iRowCount = rs.recordCount
            end if
[...code...]

function:

[...code...]
Function CategoryNameValidate()

    If (IRowCount > 0 ) Then
    document.write(strMessage)
    End if

End Function
[...code...]

I just can't figure out how to use that rowset feature.
I'm guessing recordCount is already a built in attribute of the recordset? And I don't understand what the 1,1 is for? I tried taking it out of the code and this is the error i got:

Error Type:
ADODB.Recordset (0x800A0BB9)
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.
/tburgess/ViewTable2.asp, line 20

Lost.

Toni Burgess
misskaos99@yahoo.com





Similar Threads
Thread Thread Starter Forum Replies Last Post
Saving data using form misskaos Classic ASP Basics 3 October 27th, 2006 03:16 PM
Saving Form Data Earl Hickey ASP.NET 1.0 and 1.1 Basics 5 July 25th, 2006 01:10 PM
Auto-populate table data into form jeff394 Access VBA 2 April 19th, 2006 02:55 PM
Coding for data input from one form to 2 tables... dgarcia1128 Access VBA 1 February 19th, 2004 12:35 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.