INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS
Come Join Us!
Are you a
Computer / IT professional?
Join Tek-Tips now!
- Talk With Other Members
- Be Notified Of Responses
To Your Posts
- Keyword Search
- One-Click Access To Your
Favorite Forums
- Automated Signatures
On Your Posts
- Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.
Partner With Us!
"Best Of Breed" Forums Add Stickiness To Your Site
(Download This Button Today!)
Feedback
"...Many thanks to you for putting it together and to the forum members for taking the time to post their replies and give their time to help others. Their isn't another site that can touch it..."
Geography
Where in the world do Tek-Tips members come from?
|
convert insert into update
|
|
|
evr72 (MIS) |
18 Jun 10 13:23 |
Hello, thanks to Mr. BigRed1212 and Mr. gmmastros, I was able to create my insert page now the question is, how can I turn this into an edit page. the problem is that the insert is taking the data from horizontal and putting it vertical. With the current update script that I wrote. It puts all the data into one field for example my input answer1 answer2 answer3 32 33 34 41 37 25 5 7 1 after my update is executed I get this answer1 answer2 answer3 32,41,5 33,37,7 34,25,1 I have tried a few things with no luck. here is the input page CODE<%@ Language="VBScript" %>
<% Option Explicit %>
<html>
<head>
<title>Form to database</title>
</head>
<body>
<%
Function IsInteger(Data)
If Trim(Data) = "" Then
IsInteger = False
Else
IsInteger = IsNumeric(Data & ".0e0")
End If
End Function
'declare your variables
Dim itm, userid, i
Dim sConnString, connection, sSQL
'Receiving values from Form, assign the values entered to variables
itm = Split(Request.Form("itm"),",")
userid = Split(Request.Form("userid"),",")
'define the connection string, specify database
'driver and the location of database
sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("qa.mdb")
'create an ADO connection object
Set connection = Server.CreateObject("ADODB.Connection")
'Open the connection to the database
connection.Open(sConnString)
For i = lBound(userid) to ubound(userid)
'declare SQL statement that will query the database
sSQL = "update answers (userid,itm) values ('" & _
trim(userid(i)) & "', '" & trim(itm(i)) & "')"
connection.execute(sSQL)
Next
'execute the SQL
' connection.execute(sSQL) don't need this line anymore
'Done. Close the connection object
connection.Close
Set connection = Nothing
Response.Redirect "thank_you.asp"
%>
</body>
</html>
As always any help or suggestions are very much appreciated!! |
|
Your update query is wrong. The basic structure of an update query is like this: [/code] Update YourTableName Set Column1 = 'Blah', Column2 = 'Woo' Where SomeOtherColumn = 12 [/code] When you issue the update command, the Where clause is important. Sometimes you want to update multiple rows and sometimes you want to update a single row. The where clause controls which row(s) are going to get updated. In the example I show above... if there are no rows where the SomeOtherColumn = 12, then the query will run, it just won't affect any rows. Meaning, it will appear to do stuff, but actually won't. Make sense? -George
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom |
|
|
evr72 (MIS) |
21 Jun 10 8:35 |
Sorry for the late reply, horrible storms in here, lost power and just got a chance to repply. Mr gmmastros, I think I get what you are saying, but the code that I posted is not my update code, that is the code that you and Mr. BigRed1212 helped me build. the actual update code that I have, works, but as I described, it inserts all the data into one field. I guess my question is, could I update all the data at once or do I have to update each column at the time? here is what I use for my update code CODE<%@ Language="VBScript" %>
<% Option Explicit %>
<html>
<head>
<title>Form to database</title>
</head>
<body>
<%
Function IsInteger(Data)
If Trim(Data) = "" Then
IsInteger = False
Else
IsInteger = IsNumeric(Data & ".0e0")
End If
End Function
'declare your variables
Dim itm, userid, i
Dim sConnString, connection, sSQL
'Receiving values from Form, assign the values entered to variables
itm = Split(Request.Form("itm"),",")
userid = Split(Request.Form("userid"),",")
'define the connection string, specify database
'driver and the location of database
sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("qa.mdb")
'create an ADO connection object
Set connection = Server.CreateObject("ADODB.Connection")
'Open the connection to the database
connection.Open(sConnString)
For i = lBound(userid) to ubound(userid)
'declare SQL statement that will query the database
sSQL = "update answers (userid,itm) values ('" & _
trim(userid(i)) & "', '" & trim(itm(i)) & "')"
connection.execute(sSQL)
Next
'execute the SQL
' connection.execute(sSQL) don't need this line anymore
'Done. Close the connection object
connection.Close
Set connection = Nothing
Response.Redirect "updatedpage.asp"
%>
</body>
</html> |
|
|
wvdba (IS/IT--Management) |
24 Jun 10 14:14 |
your update statement should be like this:
sSQL = "update answers set userid = '" & trim(userid(i)) & "', item = '" & trim(itm(i)) & "';" |
|
|
evr72 (MIS) |
24 Jun 10 14:47 |
thanks wvdba, I added CODEdim IngRecordNo
lngRecordNo = Request.Form("itm")
sSQL = "update answers set explain = '" & trim(explain(i)) & "', resp = '" & trim(resp(i)) & "' itm=" & lngRecordNo;" but I get the following error CODEError Type:
Microsoft VBScript compilation (0x800A0401)
Expected end of statement
/questions/update.asp, line 49, column 115
sSQL = "update answers set explain = '" & trim(explain(i)) & "', resp = '" & trim(resp(i)) & "' itm=" & lngRecordNo;"
------------------------------------------------------------------------------------------------------------------^ |
|
|
evr72 (MIS) |
24 Jun 10 14:50 |
took the ;" at the end of IngRecordNo and now get CODEError Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error (missing operator) in query expression ''yes' itm=1'.
/questions/update.asp, line 50 |
|
|
evr72 (MIS) |
24 Jun 10 14:51 |
sorry forgot to post line 50 CODE49 sSQL = "update answers set explain = '" & trim(explain(i)) & "', resp = '" & trim(resp(i)) & "' itm=" & lngRecordNo
50 connection.execute(sSQL)
51 Next |
|
|
evr72 (MIS) |
24 Jun 10 15:03 |
I added where before my itm= CODEsSQL = "update answers set explain = '" & trim(explain(i)) & "', resp = '" & trim(resp(i)) & "' where itm=" & lngRecordNo
connection.execute(sSQL)
Next and now i get CODEsSQL = "update answers set explain = '" & trim(explain(i)) & "', resp = '" & trim(resp(i)) & "' where itm=" & lngRecordNo
connection.execute(sSQL)
Next |
|
and now you get...... ??? |
|
|
evr72 (MIS) |
28 Jun 10 16:43 |
After revising my code. I descided to re-write the whole thing. And following your advice it worked!!!
Thanks!!!! |
|
|
 |
|
Talk To Other Members
Read More Threads Like This One