Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 865
  • Last Modified:

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

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
ggd
Asked:
ggd
  • 5
  • 5
  • 2
1 Solution
 
Saqib KhanSenior DeveloperCommented:
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
 
ggdAuthor Commented:
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
 
ggdAuthor Commented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Saqib KhanSenior DeveloperCommented:
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
 
Saqib KhanSenior DeveloperCommented:
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
 
Saqib KhanSenior DeveloperCommented:
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
 
ggdAuthor Commented:
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
 
ggdAuthor Commented:
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
 
Saqib KhanSenior DeveloperCommented:
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
 
ggdAuthor Commented:
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
 
allsch2001Commented:
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
 
allsch2001Commented:
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 5
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now