DATA DISAPPEARED!when updating using SQL statement

MilkPudding · June 23rd, 2004, 09:06 AM

I have written the following codes to update a database from a webpage. There is no error message generated after i click submit. However, when i go to the database, nothing has been updated, for some strange reason. I have configured the ODBC and aslo set the security property of my database to full control (read/write enabled)

i have used an "insert" statement before and it works perfectly.(values are inserted into the database). However, i deleted the database and replaced it with another one with the same name. And this time i'm using "update" instead of "insert" statement. I configured the ODBC and property setting again after the change. But it just stop working now...wut could have been the reason? help~~~

here is the codes:

<html>
<body>

<%

Dim data_source, con, mySQL

data_source = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
Server.MapPath("database.mdb")

SQL = "UPDATE Theatre_Property_Information Set Theatre_Name='"&request.form("theatre_name")&"' where Theatre_Number='"&request.form("theatre_number_get ")&"' "

Set con = Server.CreateObject("ADODB.Connection")
con.Open data_source
con.Execute SQL

con.Close
Set con = Nothing

response.write("Complete updating. Thank you.")
%>

</body>
</html>

happygv · June 23rd, 2004, 09:16 AM

Hi there,

Do a response.write as shown below and see if your udate statement is constructed as expected. It may be right, but the where clause would not have the value that matches with that of the table value.

Code:

SQL = "UPDATE Theatre_Property_Information Set Theatre_Name='"&request.form("theatre_name")&"' where Theatre_Number='"&request.form("theatre_number_get")&"' "

Response.write request.form("theatre_name") & "<BR>"
Response.write request.form("theatre_number_get") & "<BR>"

Response.write SQL
Response.end

I suspect the line marked in RED, should be the cause, may be it doesn't carry the data required to compare with the table value.

Also try executing the SQL statement that results from the response.write given above, directly on your database, and see if that affect any of the rows there.

Also check if you are verifying the CORRECT database, as you said, you have dropped that and recreated with same name, and shouldn't be checking with someother database at different path. Coz that happened in one such case.;)

Hope that helps.
Cheers!

_________________________
-Vijay G

Strive for Perfection

MilkPudding · June 23rd, 2004, 09:39 AM

yep...u r right!
i entered theatre_number=10524 theatre_name=testing on the form
the response comes out like this:

TESTING

UPDATE Theatre_Property_Information Set Theatre_Name='TESTING' where Theatre_Number=''

the theatre_number field is text and set as primary key. also i do have a existing record in the db with theatre_number=10524
sigh....i'm confused...help
thx a bunch

MilkPudding · June 23rd, 2004, 09:48 AM

geez.....just realized that i mistyped the name when trying to get value from the form
it should be request.form("theatre_number") not request.form("theatre_number_get")

now it works~~~~

thx a bunch~~~

p.s. i seem to be having a mental block right now.. T_T
i have been debugging for almost 1 hr and couldn't find sumthing so simple like this~~
again..thx~~~

happygv · June 23rd, 2004, 09:50 AM

Check within the FORM if this name is correct under <input name="theatre_number_get">.

theatre_number_get

I assume this could be different from what you are requesting after submit.

Cheers!

_________________________
-Vijay G

Strive for Perfection

MilkPudding · June 23rd, 2004, 11:26 AM

yeah...that was exactly the problem
now i have a new question:

how to check if an ID exist. if it does, update, if not, insert

i guess it requires a loop that compares the new id with the id field in the db
but how to request data from a database...that i don't know..T_T

lots of thx~~~

happygv · June 23rd, 2004, 11:38 AM

Code:

<%
Set con = Server.CreateObject("ADODB.Connection")
con.Open data_source

Set rs = Server.CreateObject("ADODB.RECORDSET")

Dim iCount
rs.open "select count(*) as cnt where Theatre_Number='"&request.form("theatre_number")&"'", con
If not rs.EOF then iCount = rs("cnt")
rs.Close
If CInt(iCount) > 0 then
    SQL = "UPDATE Theatre_Property_Information Set Theatre_Name='"&request.form("theatre_name")&"' where Theatre_Number='"&request.form("theatre_number")&"'"
    con.Execute SQL
    strmsg="Record " & request.form("theatre_number") & " updated successfully."
Else
    strmsg="Record not found for update."
End If

Response.write "" & strmsg & ""
%>

Hope that is what you are looking for.
Cheers!

_________________________
-Vijay G

Strive for Perfection

MilkPudding · June 24th, 2004, 08:28 AM

awesome~~~
thx...
it's hot....