ASP Q: Simple Script Not Working :(

[tscpa1]

This problem is so simple it's driving me nuts!!!

I have a form to update a field... 2 pages... real easy...

Form page code:
<form method ="post" action="cpeupdated.asp">
<font color="white">Type in the Courseno to change:
<input type=text name=courseNo><br><br>
cityinput type=text name=city><br>
<input type=submit value="Go">
</font></form>


Update Page code:

<%
city=request.form("city")
courseNo=request.form("courseNo")

dbase="../fpdb/cpe.mdb"
myconnection="PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE="
myconnection=myconnection & server.mappath(dbase)& ";"

Set Conn=server.CreateObject("adodb.Connection")
Conn.open myconnection
SQL = "UPDATE courses SET city='"& city & "' where courseNo='"& courseNo & "'"
Set rsRecords=conn.execute(SQL)
response.redirect("cpeupdate.asp")
%>

Can Anyone help????????????

Thanks!

[-SeFu-]

Im pretty new to ASP... but i think i might know the problem your having...

You didnt post the error message but i assume its an SQL message?

And if so then I think the problem is in the SQL statement:

I assume that you have the field set to "number" and if so... then you cant put the single quotes around it (i believe).
Because I was having the same exact problem yesterday and it drove me nuts for 30 minutes!

Finally I had tried EVERYTHING i knew and then i see those quotes and figured... what the heck... I took em out and it worked...

so try changing that code to this:

SQL = "UPDATE courses SET city='" & city & "' where courseNo=" & courseNo

Hope that Helps!!

[tscpa1]

Still not working... .

You can see it at http://www.tscpa.com/cpe/CPEupdate_.asp.

The message I now get is:

Type mismatch

The message I was getting was :

Data type mismatch in criteria expression.

both in line 12: Set rsRecords=conn.execute(SQL)

Thanks for trying...

[goober]

dim all variables. This usually solves half the problem. Also, add this line to the top of your page:

<%Option Explicit%>

This will solve most of your problems for the future with coding and misspelling. It's a must for any ASP document, in my opinion.

Also, try not to use conn.Execute. I would recommend using a recordset to get the records. Like this:

<%dim RS1
set RS1 = Server.CreateObject("ADODB.Recordset")
SQL1="SELECT Blah blah blah, etc."
RS1.Open SQL1, conn, 1, 1%>

At this point, RS1 would contain all the records you selected. To reference a record, you simply say:

<%=RS1("FieldName")%>

Hope this helps.

Any more questions? Just post back here.

'Till next time.

[DaveMaxwell]

#1) Make sure there are values in the two variables. that can cause problems.

#2) Try changing the execute statement to: Conn.Execute (strSql). I've never seen a set variable= for an update

If CourseNo is an integer, then you don't want quotes around the field. If it's alphanumeric, then you do....

[tscpa1]

Wooohooo! Success....


SeFu was right in the first case...just a lille left-over morsel I forgot to delete when implemenitng his solution.

I also dim'ed all variables and add the Option explicit... I will try to do so forever....

Thanks all!

[dethfire]

yup, thats very important! Make that the first time you script. I usually make comments too, to help me remind myself if I'll side-tracked.

[-SeFu-]

WOOHOOO
I cant wipe this smile off my face!!!
my first successful ASP help!!!

I can now consider myself a novice ASPer! AOOWEEE


Id like to thank all the little people... without which I would never have reached this level...

[pinkstar]

Quotes give me this:

Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.

/onlinepay2/query3.asp, line 25

No quotes give me this:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'Field1 = & strAcct1 & AND Field2 = & strAcct2 &'.

/onlinepay2/query3.asp, line 25

Both Field1 and Field2 are integers that come from a form.

Help?

Toni

Originally posted by -SeFu-
Im pretty new to ASP... but i think i might know the problem your having...

You didnt post the error message but i assume its an SQL message?

And if so then I think the problem is in the SQL statement:

I assume that you have the field set to "number" and if so... then you cant put the single quotes around it (i believe).
Because I was having the same exact problem yesterday and it drove me nuts for 30 minutes!

Finally I had tried EVERYTHING i knew and then i see those quotes and figured... what the heck... I took em out and it worked...

so try changing that code to this:

SQL = "UPDATE courses SET city='" & city & "' where courseNo=" & courseNo

Hope that Helps!!

PS: All I need to do is select the table data for display on a page.

[goober]

I'm assuming you have a where clause?

For all your numbers, to make sure they're numbers, use the CInt() function on them.

numbers have no quotes, text has single quotes. You've already been told that here, I believe.

I would just suggest the CInt() function and see where that leads you. here's how to use it:

dim var1
'a number in string format
var1 = "2345"
'change the string to a number
var1 = CInt(var1)

Hope this helps!

'Till next time......

[pinkstar]

Goober, thanks for the suggestion. It didn't work. I got the same error messages.

Also, for me, var1 has to equal Request.Form("something from a form"), or even better: something from an authentication appliction.

My login/account info is in three parts:

1. Authenticate username/password.
2. Authentication generates account number(s) to be used in SQL string.
3. SQL string grabs account info for relevant account number(s).

Oh yeah, I do have WHERE in my SQL string.

Originally posted by goober
I'm assuming you have a where clause?

For all your numbers, to make sure they're numbers, use the CInt() function on them.

numbers have no quotes, text has single quotes. You've already been told that here, I believe.

I would just suggest the CInt() function and see where that leads you. here's how to use it:

dim var1
'a number in string format
var1 = "2345"
'change the string to a number
var1 = CInt(var1)

Hope this helps!

'Till next time......

[goober]

Alright, a few things I've seen before I address the actual problem:

1. Use Option Explicit and always dim your variables. It prevents spelling errors and is a must for any serious ASP developer.

2. Whenever you redirect, Always add this line of code to the top of the page:

<%Response.Buffer = True%>

Okay, now on to your problem. I believe the conn.execute is the problem. Try doing it this way:

<%Option Explicit
dim city, courseNo, dbase, myConnection, Conn, SQL, rsRecords
city=request.form("city") 
courseNo=request.form("courseNo") 

dbase="../fpdb/cpe.mdb" 
myconnection="PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" 
myconnection=myconnection & server.mappath(dbase) & ";" 

Set Conn=server.CreateObject("ADODB.Connection") 
Set RSRecords = Server.CreateObject("ADODB.Recordset")
Conn.open myconnection 
SQL = "Select * FROM  courses WHERE courseNo='"& courseNo & "'"
RSRecords.Open SQL, myconnection, adOpenKeyset, adLockOptimistic
RSRecords.Fields("city") = city
RSRecords.Update
response.redirect("cpeupdate.asp") 
%>

Let me know how it works out.

'Till next time!

[pinkstar]

I put something together that works for me! It's not exactly for the same kind of project, but it might help, especially if you have ASP 2.0 like me:

http://www.sitepointforums.com/showt...threadid=23933 (scroll down)