Solved

Insert records from web form (asp page) to Access db and display records from db in descending order

Posted on 2004-04-17
12
827 Views
Last Modified: 2012-05-04
Hi,
I have a web form (ASP page) that inserts the user’s input into an access database. I have as many numbers of columns in the table as there are fields in the form. Once entries are made, I display the records on another ASP page. I would like to display the entries/records made in descending order. That is the last entry made first and so on. However, when I try to add another column with auto-number to the table, the form does not work and gives me an error “Number of query values and destination fields are not the same” when I try to submit the form.

Can anyone help please? Much appreciated. Thanks ggd.
0
Comment
Question by:ggd
  • 5
  • 5
  • 2
12 Comments
 
LVL 23

Expert Comment

by:adilkhan
ID: 10852231
post your Code, insert and Select Statement.

I assume on the TOP of your page you have Insert statement.
Define all the fields in Insert statement...
insert into Table_name(field1, field2) values(11, 'test')

then after this your Select statement...
Select * from table order by field_name desc
0
 

Author Comment

by:ggd
ID: 10852261
Once the form is submitted, below is the code of the results.asp page it goes to. The problem is not so much in displaying the results (in another asp page) but at the time of submitting the form. I just want to be able to add a column to the table that contains something like a counter so that when I display the records, I can (sort) order by this counter column.

Thanks, ggd
---------------------------------------

'Information Collected

Dim Name
Dim Mobile            
Dim Email            
Dim Address
Dim ServiceNewName      
Dim Agree
========================

  Name                         =Request.form ("Name")
  Mobile                   =Request.form ("Mobile")
  Email                   =Request.form ("Email")
  Address                  =Request.form ("Address")
  ServiceNewName      =Request.form ("ServiceNewName")
  Agree             =Request.form ("Agree")

%>



<%

Dim RS
Dim SQL1
Dim Connect_Obj

 Set connect_obj =Server.CreateObject("ADODB.Connection")    
 
 Connect_Obj.Open strConnect
 
  Set RS  =Server.CreateObject("ADODB.Recordset")
 
 SQL1 = "insert into Webfmcompet  values( " _
 &"'"& Name &"','"& Mobile &"','"& Email &"', '"& Address &"', " _
 &"'"& ServiceNewName &"', '"& Agree &"')"
 Connect_Obj.execute SQL1


Connect_Obj.Close


%>

0
 

Author Comment

by:ggd
ID: 10852466
I think I might have figured the solution, but I am obviously doing something wrong still. I know the answer may be simple but please bear with me.

I have edited the code below to sort of add a counter that will add a number to my table. However, the counter is always “1” cause I am obviously doing something wrong. I am guessing because I keep reinitializing the counter=0 . I need to find a way to increment the number in column ‘Indexnum’, that way when I display entries made I can sort like so : strSQL = "SELECT Name, Mobile, Email, Address, ServiceNewName, Agree FROM Webfmcompet ORDER BY INDEXNUM DESC;"

Please help. Thanks,ggd. Code below :
====================================
<%@ Language=VBScript %>

<%Option Explicit %>
<!-- #INCLUDE FILE="webfmcompet.inc" -->

<%

'Information Collected

Dim Name
Dim Mobile            
Dim Email            
Dim Address
Dim ServiceNewName      
Dim Agree
Dim counter
Dim indexnum

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

counter=0

  Name                         =Request.form ("Name")
  Mobile                   =Request.form ("Mobile")
  Email                   =Request.form ("Email")
  Address                  =Request.form ("Address")
  ServiceNewName      =Request.form ("ServiceNewName")
  Agree             =Request.form ("Agree")
  indexnum          =(counter +1)

%>

<%

Dim RS
Dim SQL1
Dim Connect_Obj

 Set connect_obj =Server.CreateObject("ADODB.Connection")    
 
 Connect_Obj.Open strConnect
 
  Set RS  =Server.CreateObject("ADODB.Recordset")
 
 SQL1 = "insert into Webfmcompet  values( " _
 &"'"& Name &"','"& Mobile &"','"& Email &"', '"& Address &"', " _
 &"'"& ServiceNewName &"', '"& Agree &"', '"& indexnum &"')"
 Connect_Obj.execute SQL1

Connect_Obj.Close

%>
0
 
LVL 23

Expert Comment

by:adilkhan
ID: 10853732
yes you are doing it wrong. value of index will always be one, because Page is Static and value is not incrementing in a LOOP.
Plus you can not use a Variable name in SQL Select Order By Clause.

Correct solution would be this.

Add a Column in Access Database Table Called indexNum and set its data Type to AUTONUMBER.

then

 strSQL = "SELECT Name, Mobile, Email, Address, ServiceNewName, Agree FROM Webfmcompet ORDER BY INDEXNUM DESC


0
 
LVL 23

Expert Comment

by:adilkhan
ID: 10853751
And also try this Sql insert statement.

 SQL1 = "insert into Webfmcompet (Name, Mobile, Email, Address, ServiceNewName, agree, indexNum) values( " _
 &"'"& Name &"','"& Mobile &"','"& Email &"', '"& Address &"', " _
 &"'"& ServiceNewName &"', '"& Agree &"', "& &")"
0
 
LVL 23

Expert Comment

by:adilkhan
ID: 10853755
I am sorry, i mean this..

 SQL1 = "insert into Webfmcompet (Name, Mobile, Email, Address, ServiceNewName, agree) values( " _
 &"'"& Name &"','"& Mobile &"','"& Email &"', '"& Address &"', " _
 &"'"& ServiceNewName &"', '"& Agree &"')"

where all the names in brackets are the names in your Access Table.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:ggd
ID: 10856784
The first thing I did initially was simply add a column with autonumber without making any changes to my form. I get this error "“Number of query values and destination fields are not the same” when I try to submit the form.
So thats why i was wondering if you could tell me how I can generate an incremental number everytime the form is submitted so that when I use the INSERT statement to add a record to the table, I can also add this number to the "column named indexnum" in the table manually.
I was not trying to display the record in the SQL statement using the variable. I created a column name with the same variable name in the table.
That way I can sort the records to be displayed in desc order by using this column(named "indexnum") from the table in another page all together.

Thanks, ggd.
0
 

Author Comment

by:ggd
ID: 10857338
I'm increasing the points as i really need to able to display these records in descending order.
Would appreciate any help from any body.
Thanks, ggd.
0
 
LVL 23

Accepted Solution

by:
adilkhan earned 200 total points
ID: 10860414
ggd did you read my above comment?

after adding a Column in your database with autonumber data type modify your Insert statement to this.

SQL1 = "insert into Webfmcompet (Name, Mobile, Email, Address, ServiceNewName, agree) values( " _
 &"'"& Name &"','"& Mobile &"','"& Email &"', '"& Address &"', " _
 &"'"& ServiceNewName &"', '"& Agree &"')"


after the insertion is done simply order by the column name(autonumber) in desending order.

Good Luck.
0
 

Author Comment

by:ggd
ID: 10865864
adilkhan,
I didnt realise the way the INSERT statment was written could make the difference. Cause earlier when I was trying to simply add autonumber column to the table it gave that error that said query and destination fields didnt match.
So this time I changed the SQL INSERT statement as you said. And then added the column IndexNum with autonumber and it worked.
Thanks very much! I appreciate the help. ggd
0
 

Expert Comment

by:allsch2001
ID: 11279934
Hi,

I would like to do the same thing as ggd.

Just following from what is here, should my SQl str look then like this?

sSQL = "INSERT into guestbook(Full_Name, City, Country_State, Email_Address, Tel_Cell, Website_URL, Comments) values ('" & _
Full_Name & "', '" & City & "', '" & Country_State & "', '" & Email_Address & "', '" & Tel_Cell & "', '" & Website_URL & "', '" & Comments & "')ORDER BY ID(autonumber) DESC"

This did not work. My autonumber column is called ID
0
 

Expert Comment

by:allsch2001
ID: 11280205
Oh,

Here's how to fix that
Use the following str on your guestbook.asp page

sql_select="SELECT * FROM guestbook order by ID desc"

Regards,

Kim Kariuki




0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Auto Submit on dropdown box 14 51
Query not working correctly? (or how i want it to... 6 48
If-Then-Else ASP problem 6 49
Issues with Insert statement 12 8
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now