SQL update records

topshed · September 6th, 2003, 09:13 AM

Hi,

I need help please

I am still not up to speed with ASP and I have a problem trying to update a database on line using this code

SQLstmt = "UPDATE br_stds SET "
    SQLstmt = SQLstmt & "br_no='" & TRIM(Request.Form("br_no")) & "', "
    SQLstmt = SQLstmt & "lclas='" & TRIM(Request.Form("lclas")) & "', "
    SQLstmt = SQLstmt & "class2='" & TRIM(Request.Form("class2")) & "', "
    SQLstmt = SQLstmt & "loco_name='" & TRIM(Request.Form("loco_name")) & "', "
    SQLstmt = SQLstmt & "builder='" & TRIM(Request.Form("builder")) & "', "
    SQLstmt = SQLstmt & "build_no='" & TRIM(Request.Form("build_no")) & "', "
    SQLstmt = SQLstmt & "mm_built='" & TRIM(Request.Form("mm_built")) & "', "
    SQLstmt = SQLstmt & "yy_built='" & TRIM(Request.Form("yy_built")) & "', "
    SQLstmt = SQLstmt & "mm_wdn='" & TRIM(Request.Form("mm_wdn")) & "', "
    SQLstmt = SQLstmt & "yy_wdn='" & TRIM(Request.Form("yy_wdn")) & "', "
    SQLstmt = SQLstmt & "code='" & TRIM(Request.Form("code")) & "', "
    SQLstmt = SQLstmt & "last_shed='" & TRIM(Request.Form("last_shed")) & "', "
    SQLstmt = SQLstmt & "mm_cut='" & TRIM(Request.Form("mm_cut")) & "', "
    SQLstmt = SQLstmt & "yy_cut='" & TRIM(Request.Form("yy_cut")) & "', "
    SQLstmt = SQLstmt & "cut='" & TRIM(Request.Form("cut")) & "', "
    SQLstmt = SQLstmt & "notes='" & TRIM(Request.Form("notes")) & "' "
    SQLstmt = SQLstmt & " WHERE ID=" & TRIM(Request.Form("Recordid"))

    %>
SQL statement: <%=SQLstmt%>
<%
    Conn.Execute (SQLstmt)

When I run this piece of code the SQL statement prints out so

SQL statement: UPDATE br_stds SET br_no='71000', lclas='8P', class2='', loco_name='Duke of Gloucester', builder='Crewe', build_no='E486', mm_built='May', yy_built='1954', mm_wdn='Nov', yy_wdn='1962', code='5A', last_shed='Crewe North', mm_cut='Intact', yy_cut='', cut='Preserved', notes='test' WHERE ID=57

Now I cannot find anything wrong with the statement or the request BUT i get this error message

"Microsoft JET Database Engine error '80040e07'

Data type mismatch in criteria expression.

Line 257 "
which is the line Conn.Execute (SQLstmt)

Can anyone please point me in the right direction

Regards Topshed

Greg Griffiths · September 6th, 2003, 11:01 AM

Can you run the statement in Query analyser without any errors ? If not, then try and resolve them there as that should tell you where your code is not working properly.

Imar · September 7th, 2003, 05:13 AM

Hi Topshed,

What are the datatypes for the year columns (starting with yy_) in your database? Are they numeric or of a text type? If they are numeric, you can't use apostrophes to delimit the values for these columns. If that is the case,

Code:

yy_built='1954', mm_wdn='Nov', yy_wdn='1962'

should be:

Code:

yy_built=1954, mm_wdn='Nov', yy_wdn=1962

HtH,

Imar

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

topshed · September 9th, 2003, 06:29 AM

Thank you for that,
The Database is Access by the way

I have removed the apostrophes from the yy_ and br_no (They are the only four number fields) but the result now shows I have a syntax error in Update statement thus:

SQL statement: UPDATE br_stds SET br_no=71000, lclas='8P', class2='', loco_name='Duke of Gloucester', builder='Crewe', build_no='E486', mm_built='May', yy_built=1954, mm_wdn='Nov', yy_wdn=1962, code='5A', last_shed='Crewe North', mm_cut='Intact', yy_cut=, cut='Preserved', notes='test' WHERE ID=57
--------------------------------------------------------------------------------

Microsoft JET Database Engine error '80040e14'

Syntax error in UPDATE statement.

/34a/up/moddatabr.asp, line 257

I'll get the gamg of this on day

Topshed

Imar · September 9th, 2003, 06:40 AM

Hi,

Take a look at this:

Code:

yy_cut=,

You are not supplying a value for this column.

Either pass a value, or '' or whatever the database expects.

To help you debugging, try these kind of queries in the Access Query Designer first. This will help you locate problems quicker than through ASP as Access is a bit better in visually representing your queries and giving feedback.

Cheers,

Imar

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

topshed · September 9th, 2003, 09:48 AM

Thank you so very much, I have learned a few things tonight, trying to bash sample code to do what you want aint that easy for a self taught idiot.

Best of all I should not need to ask so many dumb questions in the future

Regards Topshed (Roy)