Updating records help

bspradeep · #1 (**permalink**) July 5th, 2003, 06:28 PM

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

NotNowJohn · #2 (**permalink**) July 6th, 2003, 06:11 AM

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

NotNowJohn · #3 (**permalink**) July 6th, 2003, 06:16 AM

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

...but the Soon is eclipsed by the Moon

bspradeep · #4 (**permalink**) July 7th, 2003, 10:55 PM

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

NotNowJohn · #5 (**permalink**) July 9th, 2003, 02:35 AM

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

NotNowJohn · #6 (**permalink**) July 9th, 2003, 02:41 AM

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

NotNowJohn · #7 (**permalink**) July 9th, 2003, 02:47 AM

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

bspradeep · #8 (**permalink**) July 13th, 2003, 01:02 AM

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

NotNowJohn · #9 (**permalink**) July 13th, 2003, 01:44 AM

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

Helmut · #10 (**permalink**) July 14th, 2003, 01:51 PM

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

Need to download code?

Navigation