Wrox Programmer Forums

Need to download code?

View our list of code downloads.

| FAQ | Members List | Search | Today's Posts | Mark Forums Read
Classic ASP Databases Discuss using ASP 3 to work with data in databases, including ASP Database Setup issues from the old P2P forum on this specific subtopic. See also the book forum Beginning ASP.NET Databases for questions specific to that book. 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 Databases section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old July 5th, 2003, 06:28 PM
Authorized User
 
Join Date: Jul 2003
Location: , , .
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts
Default Updating records help

Please can anyone tell me how to update a database from an ASP page. The database fields include name, age, mail-id, address, company address, phone etc. I'm a beginner please help me & I've gone mad trying all possible types.Please Please Please Please do help me

Thanx
Pradeep

Reply With Quote
  #2 (permalink)  
Old July 6th, 2003, 06:11 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Wien, Wien, Austria.
Posts: 158
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via ICQ to NotNowJohn
Default

Pradeep,

In ASP all DB manipulations are realized through ADO objects.
Here is a sample of DB updating, but I recommend you some ADO tutorial. One easy and short u can read on http://www.w3schools.com/ado/

Example:
Database: Access 2000
Database name: ExampleDB
Database table: Customer
Table fields:
ID int,
Name text(40),
Email text(60),
Age int
'Updating the record with specified ID
ID=1
new_Name="Natasa Radivojevic"
new_Email="natasa_patasa@natasapatasa.com"
new_Age=30
Code:
Dim cn
Dim db_path
Dim sSQL
db_path=Server.MapPath("Customer.mdb")
connString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & db_path &";"
set cn=Server.CreateObject("ADODB.Connection")
cn.ConnectionString=connString
cn.Open
sSQL="UPDATE Customer SET Name='" & new_Name & "', Email='" & new_Email & "',Age=" & new_age & " WHERE ID=" & ID
cn.Execute sSQL
cn.Close
Set cn=Nothing
***Notice that you have to enclosed string values with an apostrophe in contrast to integer(number)
values.
However, if you need a quick help for this situation, send us the structure of the table you want to update, and you will receive the code exactly matching your purpose.

Regards,

...but the Soon is eclipsed by the Moon
Reply With Quote
  #3 (permalink)  
Old July 6th, 2003, 06:16 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Wien, Wien, Austria.
Posts: 158
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via ICQ to NotNowJohn
Default

oops - sorry for hor scroll :)
I don't like VB's new lines:)

...but the Soon is eclipsed by the Moon
Reply With Quote
  #4 (permalink)  
Old July 7th, 2003, 10:55 PM
Authorized User
 
Join Date: Jul 2003
Location: , , .
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Please can anyone tell me how to update a database from an ASP page. I'm a beginner please help me & I've gone mad trying all possible types.Please Please Please Please do help me and I'm using MS-Access 2000,the structure of the table is as below:-

Username(Name of the person)-Text(size-150)
MailAddr(Email-id of the person)-Text(Size-80)
Residence(Residential Address)-Text
Phone(Residence phone number)-Number
Fax(Residence Fax Number)-Number
Mobile(Mobile number)-Number
Company(Name of the company working)-Text
Address(Address of the company)-Text
dept(name of the department working)-Text
office(Office phone number)-Number
officefax(Company fax number)-Number
notes(General Notes)-Text


I have entered details to the database and when i click the update button against particular person a new page should be opened wherein I can update some fields.

Thanx
Pradeep



Reply With Quote
  #5 (permalink)  
Old July 9th, 2003, 02:35 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Wien, Wien, Austria.
Posts: 158
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via ICQ to NotNowJohn
Default

first_page.asp display the Members list:
Code:
<p>MEMBERS</p>
<% 
Dim cn
Dim rs
Dim sSQL
Set cn=Server.CreateObject("ADODB.Connection")
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath(YourDBName)
sSQL="SELECT * FROM YourTableName"
Response.Write "<table cellspacing=5><tr  bgcolor=e0e0e0>"
Response.Write "<td class=td_hed>UserName</td>"
Response.Write "<td class=td_hed>Email</td>"
Response.Write "<td class=td_hed>Residence</td>"
Response.Write "<td class=td_hed>Details</td></tr>"
Set rs=cn.Execute(sSQL)
While not rs.EOF
    Response.Write "<tr>"
    Response.Write "<td>" & rs.Fields(1) & "</td>"
    Response.Write "<td>" & rs.Fields(2) & "</td>"
    Response.Write "<td>" & rs.Fields(3) & "</td>"
    Response.Write "<td><a href=""second_page.asp?id=" & rs.Fields(0) & """>Details</a></td>"
    Response.Write "</tr>"
    rs.movenext
Wend
rs.Close
Set rs=Nothing
cn.Close
Set cn=Nothing
%>
And on the second page you can provide full details for the specified user and the possibility to change user details.


...but the Soon is eclipsed by the Moon
Reply With Quote
  #6 (permalink)  
Old July 9th, 2003, 02:41 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Wien, Wien, Austria.
Posts: 158
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via ICQ to NotNowJohn
Default

second_page.asp:
Code:
<%
Function GetMemberDetails(id)
Dim cn
Dim sSQL
Dim rs
Dim ret_string
Set cn=Server.CreateObject("ADODB.Connection")
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath(YourDBName)
sSQL="SELECT * FROM YourTableName WHERE id=" & id
ret_string="<table cellspacing=5><tr  bgcolor=e0e0e0>"
ret_string=ret_string & "<td class=td_hed>Field Name</td>"
ret_string=ret_string & "<td class=td_hed>Field Value</td></tr>"
set rs=cn.Execute(sSQL)
If not rs.EOF Then
    ret_string=ret_string & "<tr><td class=td_hed>UserName</td>"
    ret_string=ret_string & "<td><input type=text name=username value=" & rs.Fields(1) & " size=20></td></tr>"
    ret_string=ret_string & "<tr><td class=td_hed>MailAddr</td>"
    ret_string=ret_string & "<td><input type=text name=email value=" & rs.Fields(2) & " size=20></td></tr>"
    ret_string=ret_string & "<tr><td class=td_hed>Residence</td>"
    ret_string=ret_string & "<td><input type=text name=residence value=" & rs.Fields(3) & " size=20></td></tr>"
    ret_string=ret_string & "<tr><td class=td_hed>Phone</td>"
    ret_string=ret_string & "<td><input type=text name=phone value=" & rs.Fields(4) & " size=20></td></tr>"
    ret_string=ret_string & "<tr><td class=td_hed>Fax</td>"
    ret_string=ret_string & "<td><input type=text name=fax value=" & rs.Fields(5) & " size=20></td></tr>"
    ret_string=ret_string & "<tr><td class=td_hed>Mobile</td>"
    ret_string=ret_string & "<td><input type=text name=mobile value=" & rs.Fields(6) & " size=20></td></tr>"
    ret_string=ret_string & "<tr><td class=td_hed>Company</td>"
    ret_string=ret_string & "<td><input type=text name=company value=" & rs.Fields(7) & " size=20></td></tr>"
    ret_string=ret_string & "<tr><td class=td_hed>Address</td>"
    ret_string=ret_string & "<td><input type=text name=address value=" & rs.Fields(8) & " size=20></td></tr>"
    ret_string=ret_string & "<tr><td class=td_hed>Dept</td>"
    ret_string=ret_string & "<td><input type=text name=dept value=" & rs.Fields(9) & " size=20></td></tr>"
    ret_string=ret_string & "<tr><td class=td_hed>Office</td>"
    ret_string=ret_string & "<td><input type=text name=office value=" & rs.Fields(10) & " size=20></td></tr>"
    ret_string=ret_string & "<tr><td class=td_hed>OfficeFax</td>"
    ret_string=ret_string & "<td><input type=text name=officefax value=" & rs.Fields(11) & " size=20></td></tr>"
    ret_string=ret_string & "<tr><td class=td_hed>Notes</td>"
    ret_string=ret_string & "<td><input type=text name=notes value=" & rs.Fields(12) & " size=20></td></tr>"
End If
rs.Close
Set rs=Nothing
cn.Close
Set cn=Nothing
ret_string=ret_string & "</table>"
GetMemberDetails=ret_string
End Function

Function UpdateYourDB(id,username,email,residence,phone,fax,mobile,company,address,dept,office,officefax,notes)
    Dim cn
    Dim sSQL
    Set cn=Server.CreateObject("ADODB.Connection")
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath(YourDB)
    sSQL="UPDATE YourTablename SET Username='" & username & "',MailAddr='" & email & "',Residence='" & residence
    sSQL=sSQL & "',Phone=" & phone & ",Fax=" & fax & ",Mobile=" & mobile & ",Company='" & company
    sSQL=sSQL & "',Address='" & address & "',dept='" & dept & "',office=" & office  
    sSQL=sSQL & ",officefax=" & officefax & ",notes='" & notes & "' WHERE id=" & id
    cn.Execute sSQL,lRecs
    cn.Close
    Set cn=Nothing
    UpdateYourDB=lRecs
End Function

If Request.Form("submit")<>"" Then
    id=Request.Form("id")
    username=Request.Form("username")
    email=Request.Form("email")
    residence....
    If UpdateYourDB(id,username,email,residence,phone,fax,mobile,company,address,dept,office,officefax,notes)=1 Then
        Response.Write "Success message"
    Else
        Response.Write "Failed message"
    End If

Else
    If (Request.QueryString("id")>0) Then
        Response.Write "<form name=""form"" method=""post"" action=""second_page.asp"">"

        id=Request.QueryString("id")
        Response.Write GetMemberDetails(id)
        Response.Write "<input type=hidden name=id value=" & id & ">"
        Response.Write "<input type=submit name=submit value=""Update Changes"">"
        Response.Write "</form>"
    Else
        Response.Write "Wrong parameter!!!"
    End If
End If
%>
...but the Soon is eclipsed by the Moon
Reply With Quote
  #7 (permalink)  
Old July 9th, 2003, 02:47 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Wien, Wien, Austria.
Posts: 158
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via ICQ to NotNowJohn
Default

One note:
In these examples I have added another one field named ID, and this field is unique in the table; I used it in order to determinate appropriate record and its actions. You can use, however, an existing unique field (Username, I guess), in the same manner as I used ID in these examples.

Regs,
NNJ

...but the Soon is eclipsed by the Moon
Reply With Quote
  #8 (permalink)  
Old July 13th, 2003, 01:02 AM
Authorized User
 
Join Date: Jul 2003
Location: , , .
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for your help NNJ but still I'm not able to edit the contents and I'm getting error"Wrong parameter!!!" as per you code.I tried the same code and also changed the Primary key to username but no use.More details regarding to this is given:-

I'm a beginner.Please do help me.

the structure of the table is as below:-

Database used:- MS-Access 2000
NAME OF THE TABLE-contacts



groupname-*

id(Autonumber)-PRIMARY KEY
Username(Name of the person)-Text(size-150)
MailAddr(Email-id of the person)-Text(Size-80)
Residence(Residential Address)-Text
Phone(Residence phone number)-Number
Fax(Residence Fax Number)-Number
Mobile(Mobile number)-Number
Company(Name of the company working)-Text
Address(Address of the company)-Text
dept(name of the department working)-Text
office(Office phone number)-Number
officefax(Company fax number)-Number
notes(General Notes)-Text

*groupname=This field contains registered username, when the user log into his account he will be able to view or edit the details pertaining to only his account and he should not be able to see other users account or records.

This is like a database website wherein a new user registers and a new space for his records is created under his name in groupname field in the table contacts.

I have entered details to the database and when i click the update button against particular person a new page should be opened wherein I can update fields.


Thanx
Pradeep

Reply With Quote
  #9 (permalink)  
Old July 13th, 2003, 01:44 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Wien, Wien, Austria.
Posts: 158
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via ICQ to NotNowJohn
Default

On which page you received an error - first or second?
And send me full error details with error line number and appropriate code near the line.

...but the Soon is eclipsed by the Moon
Reply With Quote
  #10 (permalink)  
Old July 14th, 2003, 01:51 PM
Authorized User
 
Join Date: Jul 2003
Location: , , .
Posts: 16
Thanks: 0
Thanked 0 Times in 0 Posts
Default

This code works wonderful!

I have a q,

when there is an empty cell my boxes show the size of the cell

for example

Phone: size20

how can i fix that so when the field is empty it shows nothing?

Oh, and is there anyway to make a basic search function for this?
or even show fields by category?

Wow i think this is too much to ask for :o(

...helmut
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
problem in updating records & finding records naveed77 VB Databases Basics 1 January 16th, 2007 12:12 PM
problem in updating records & finding records naveed77 VB How-To 1 January 16th, 2007 12:10 PM
Updating Records marmer Classic ASP Databases 1 August 23rd, 2003 11:12 AM
Updating records bspradeep Classic ASP Databases 0 August 20th, 2003 09:27 AM
Updating records help bspradeep Classic ASP Databases 0 July 7th, 2003 10:51 PM



All times are GMT -4. The time now is 02:39 PM.


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