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
816 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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:  The Exchange of information …
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

743 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

12 Experts available now in Live!

Get 1:1 Help Now