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
850 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 2
12 Comments
 
LVL 23

Expert Comment

by:Saqib Khan
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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
LVL 23

Expert Comment

by:Saqib Khan
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:Saqib Khan
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:Saqib Khan
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
 

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:
Saqib Khan 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Using Classic ASP inside HTML pages 2 71
Connection to multiple databases 13 37
Sending ASP to server side 8 32
P3P validator , ASP, PHP ,Https and Http links 3 12
Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

733 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