 |
| 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 software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
|
|
|
|
July 5th, 2003, 06:28 PM
|
|
Authorized User
|
|
Join Date: Jul 2003
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|
|
July 6th, 2003, 06:11 AM
|
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 158
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|
|
July 6th, 2003, 06:16 AM
|
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 158
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
oops - sorry for hor scroll :)
I don't like VB's new lines:)
...but the Soon is eclipsed by the Moon
|
|
July 7th, 2003, 10:55 PM
|
|
Authorized User
|
|
Join Date: Jul 2003
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|
|
July 9th, 2003, 02:35 AM
|
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 158
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|
|
July 9th, 2003, 02:41 AM
|
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 158
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|
|
July 9th, 2003, 02:47 AM
|
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 158
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|
|
July 13th, 2003, 01:02 AM
|
|
Authorized User
|
|
Join Date: Jul 2003
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|
|
July 13th, 2003, 01:44 AM
|
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 158
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|
|
July 14th, 2003, 01:51 PM
|
|
Authorized User
|
|
Join Date: Jul 2003
Posts: 16
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|
|
|
