MEW_TWO
asked on
Update MDB record !!
Hi !
Is the code below a corect UPDATE code, or can someone
correct it !!
<%
Set Conn = Server.CreateObject("ADODB .Connectio n")
DSN = "DRIVER={Microsoft Access Driver (*.mdb)}; "
DSN = DSN & "DBQ=c:/passord/database.m db"
Conn.Open DSN
strSQL = "UPDATE DISTINCTROW personer set "
strSQL = strSQL & "Fornavn = "'" & Request.Form("Fornavn") & "', "
strSQL = strSQL & "Etternavn = "'" & Request.Form("etternavn") & "', "
strSQL = strSQL & "Telefon = "'" & Request.Form("telefon") & "', "
strSQL = strSQL & "Fodtselsdato = "'" & Request.Form("fodt") & "', "
strSQL = strSQL & "Mail = "'" & Request.Form("mail") & "', "
strSQL = strSQL & "Adresse = "'" & Request.Form("Adresse") & "', "
strSQL = strSQL & "Postnummer = "'" & Request.Form("postnummer") & "', "
strSQL = strSQL & "Poststed = "'" & Request.Form("poststed") & "', "
strSQL = strSQL & "bruker = "'" & Request.Form("bruker") & "', "
strSQL = strSQL & "type = "'" & session ("abbonement") & "', "
strSQL = strSQL & "regdato = "'" & NOW & "', "
strSQL = strSQL & "Passord = "'" & passord & "' where ID=Request.QueryString("ID ")"
strSQL = strSQL & "values( "
strSQL = strSQL & "'" & Request.Form("Fornavn") & "', "
strSQL = strSQL & "'" & Request.Form("etternavn") & "', "
strSQL = strSQL & "'" & Request.Form("telefon") & "', "
strSQL = strSQL & "'" & Request.Form("fodt") & "', "
strSQL = strSQL & "'" & Request.Form("mail") & "', "
strSQL = strSQL & "'" & Request.Form("Adresse") & "', "
strSQL = strSQL & "'" & Request.Form("postnummer") & "', "
strSQL = strSQL & "'" & Request.Form("poststed") & "', "
strSQL = strSQL & "'" & Request.Form("bruker") & "', "
strSQL = strSQL & "'" & session ("abbonement") & "', "
strSQL = strSQL & "'" & NOW & "', "
strSQL = strSQL & "'" & passord & "')"
' SQL s?tning eksekveres
response.write strSQL
Conn.Execute(strSQL)
response.write rs("id")
response.write rs("fornavn")
' Luk databaseforbindelse
Conn.Close
Set Conn = Nothing
set faluring = Nothing
response.redirect ("profil.asp")
%>
Is the code below a corect UPDATE code, or can someone
correct it !!
<%
Set Conn = Server.CreateObject("ADODB
DSN = "DRIVER={Microsoft Access Driver (*.mdb)}; "
DSN = DSN & "DBQ=c:/passord/database.m
Conn.Open DSN
strSQL = "UPDATE DISTINCTROW personer set "
strSQL = strSQL & "Fornavn = "'" & Request.Form("Fornavn") & "', "
strSQL = strSQL & "Etternavn = "'" & Request.Form("etternavn") & "', "
strSQL = strSQL & "Telefon = "'" & Request.Form("telefon") & "', "
strSQL = strSQL & "Fodtselsdato = "'" & Request.Form("fodt") & "', "
strSQL = strSQL & "Mail = "'" & Request.Form("mail") & "', "
strSQL = strSQL & "Adresse = "'" & Request.Form("Adresse") & "', "
strSQL = strSQL & "Postnummer = "'" & Request.Form("postnummer")
strSQL = strSQL & "Poststed = "'" & Request.Form("poststed") & "', "
strSQL = strSQL & "bruker = "'" & Request.Form("bruker") & "', "
strSQL = strSQL & "type = "'" & session ("abbonement") & "', "
strSQL = strSQL & "regdato = "'" & NOW & "', "
strSQL = strSQL & "Passord = "'" & passord & "' where ID=Request.QueryString("ID
strSQL = strSQL & "values( "
strSQL = strSQL & "'" & Request.Form("Fornavn") & "', "
strSQL = strSQL & "'" & Request.Form("etternavn") & "', "
strSQL = strSQL & "'" & Request.Form("telefon") & "', "
strSQL = strSQL & "'" & Request.Form("fodt") & "', "
strSQL = strSQL & "'" & Request.Form("mail") & "', "
strSQL = strSQL & "'" & Request.Form("Adresse") & "', "
strSQL = strSQL & "'" & Request.Form("postnummer")
strSQL = strSQL & "'" & Request.Form("poststed") & "', "
strSQL = strSQL & "'" & Request.Form("bruker") & "', "
strSQL = strSQL & "'" & session ("abbonement") & "', "
strSQL = strSQL & "'" & NOW & "', "
strSQL = strSQL & "'" & passord & "')"
' SQL s?tning eksekveres
response.write strSQL
Conn.Execute(strSQL)
response.write rs("id")
response.write rs("fornavn")
' Luk databaseforbindelse
Conn.Close
Set Conn = Nothing
set faluring = Nothing
response.redirect ("profil.asp")
%>
one more thing:
the syntax for updating is
update <tablename>
set
<column> = <new value>,
<column> = <new value>,
<column> = <new value>
where <boolean condition>
for example:
strSQL = "UPDATE personer set " & _
"Fornavn = '" & Replace(Request.Form("Forn avn"), "'", "''") & "', " & _
"Etternavn = '" & Replace(Request.Form("ette rnavn"), "'", "''") & "', " & _
"where where ID=" & Request.Form("ID")
YOUR MISTAKES:
1. you use Request.Form and Request.QueryString at the same time. Should use only one.
2. You have extra "double quotes" when you build the query.
the syntax for updating is
update <tablename>
set
<column> = <new value>,
<column> = <new value>,
<column> = <new value>
where <boolean condition>
for example:
strSQL = "UPDATE personer set " & _
"Fornavn = '" & Replace(Request.Form("Forn
"Etternavn = '" & Replace(Request.Form("ette
"where where ID=" & Request.Form("ID")
YOUR MISTAKES:
1. you use Request.Form and Request.QueryString at the same time. Should use only one.
2. You have extra "double quotes" when you build the query.
always, copy and paste problem :-)
strSQL = "UPDATE personer set " & _
"Fornavn = '" & Replace(Request.Form("Forn avn"), "'", "''") & "', " & _
"Etternavn = '" & Replace(Request.Form("ette rnavn"), "'", "''") & "' " & _
" where ID=" & Request.Form("ID")
NOTE: no comma before "where"
strSQL = "UPDATE personer set " & _
"Fornavn = '" & Replace(Request.Form("Forn
"Etternavn = '" & Replace(Request.Form("ette
" where ID=" & Request.Form("ID")
NOTE: no comma before "where"
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
as said.. correct your syntax.. values should be only in an insert statement.. also don't forget to set write permissions on the mdb if it's hosted on an ntfs partition
Silvers5 is correct. That's why I removed the values from the sql string above.
hongjun
hongjun
ASKER
I've now changed my code, and this is how it look
when I finaly got it to work :
' Databaseforbindelse - husk at angive sti til din database
Set Conn = Server.CreateObject("ADODB .Connectio n")
DSN = "DRIVER={Microsoft Access Driver (*.mdb)}; "
DSN = DSN & "DBQ=c:/passord/database.m db"
Conn.Open DSN
strSQL = "UPDATE DISTINCTROW personer set "
strSQL = strSQL & "Fornavn = '" & replace(Request.Form("Forn avn"), "'", "''") & "', "
strSQL = strSQL & "Etternavn = '" & replace(Request.Form("ette rnavn"), "'", "''") & "', "
strSQL = strSQL & "Telefon = '" & replace(Request.Form("tele fon"), "'", "''") & "', "
strSQL = strSQL & "Fodtselsdato = '" & replace(Request.Form("fodt "), "'", "''") & "', "
strSQL = strSQL & "Mail = '" & replace(Request.Form("mail "), "'", "''") & "', "
strSQL = strSQL & "Adresse = '" & replace(Request.Form("Adre sse"), "'", "''") & "', "
strSQL = strSQL & "Postnummer = '" & replace(Request.Form("post nummer"), "'", "''") & "', "
strSQL = strSQL & "Poststed = '" & replace(Request.Form("post sted"), "'", "''") & "', "
strSQL = strSQL & "bruker = '" & replace(Request.Form("bruk er"), "'", "''") & "', "
strSQL = strSQL & "type = '" & replace(session("abbonemen t"), "'", "''") & "', "
strSQL = strSQL & "regdato = '" & Now() & "', "
strSQL = strSQL & "Passord = '" & replace(Request.Form("pass 1"), "'", "''") & "' where ID ="& Request.QueryString("id")
' SQL execute
response.write strSQL
Conn.Execute(strSQL)
'response.write rs("id")
'response.write rs("fornavn")
' close sql
Conn.Close
Set Conn = Nothing
set faluring = Nothing
response.redirect ("profil.asp")
%>
But, one more thing, Why do the ....where ID ="& Request.QueryString("id")
Get "" when I've passed it from 'profil.asp' like this :
<form action="profil_rett.asp?id ="&rs("ID" ) method="post">
when I finaly got it to work :
' Databaseforbindelse - husk at angive sti til din database
Set Conn = Server.CreateObject("ADODB
DSN = "DRIVER={Microsoft Access Driver (*.mdb)}; "
DSN = DSN & "DBQ=c:/passord/database.m
Conn.Open DSN
strSQL = "UPDATE DISTINCTROW personer set "
strSQL = strSQL & "Fornavn = '" & replace(Request.Form("Forn
strSQL = strSQL & "Etternavn = '" & replace(Request.Form("ette
strSQL = strSQL & "Telefon = '" & replace(Request.Form("tele
strSQL = strSQL & "Fodtselsdato = '" & replace(Request.Form("fodt
strSQL = strSQL & "Mail = '" & replace(Request.Form("mail
strSQL = strSQL & "Adresse = '" & replace(Request.Form("Adre
strSQL = strSQL & "Postnummer = '" & replace(Request.Form("post
strSQL = strSQL & "Poststed = '" & replace(Request.Form("post
strSQL = strSQL & "bruker = '" & replace(Request.Form("bruk
strSQL = strSQL & "type = '" & replace(session("abbonemen
strSQL = strSQL & "regdato = '" & Now() & "', "
strSQL = strSQL & "Passord = '" & replace(Request.Form("pass
' SQL execute
response.write strSQL
Conn.Execute(strSQL)
'response.write rs("id")
'response.write rs("fornavn")
' close sql
Conn.Close
Set Conn = Nothing
set faluring = Nothing
response.redirect ("profil.asp")
%>
But, one more thing, Why do the ....where ID ="& Request.QueryString("id")
Get "" when I've passed it from 'profil.asp' like this :
<form action="profil_rett.asp?id
Try this
<form action='<%response.write(" profil_ret t.asp?id=" & rs("ID"))%>' method="post">
hongjun
<form action='<%response.write("
hongjun
1. if the field is string type then use
strSQL = strSQL & "Fornavn = '" & Replace(Request.Form("Forn
2. if the field is number then use
strSQL = strSQL & "Fornavn = " & Request.Form("Fornavn") & ", "
3. if the field is date then use
strSQL = strSQL & "Fornavn = #" & Now & "#, "