Wrox Programmer Forums
|
Classic ASP Components Discussions specific to components in ASP 3.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Components section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old September 6th, 2003, 09:13 AM
Authorized User
 
Join Date: Sep 2003
Posts: 81
Thanks: 0
Thanked 0 Times in 0 Posts
Default SQL update records

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







 
Old September 6th, 2003, 11:01 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 344
Thanks: 0
Thanked 1 Time in 1 Post
Default

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.
 
Old September 7th, 2003, 05:13 AM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

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.
 
Old September 9th, 2003, 06:29 AM
Authorized User
 
Join Date: Sep 2003
Posts: 81
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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




 
Old September 9th, 2003, 06:40 AM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

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.
 
Old September 9th, 2003, 09:48 AM
Authorized User
 
Join Date: Sep 2003
Posts: 81
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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)






Similar Threads
Thread Thread Starter Forum Replies Last Post
UpDate Records on a Form ManFriday Access 2 September 8th, 2007 04:21 AM
Update Records bspeck Dreamweaver (all versions) 5 October 5th, 2004 03:05 PM
UPDATE RECORDS!!! a_pathak BOOK: Beginning ASP 3.0 1 March 3rd, 2004 04:00 AM
update records error damnnono_86 Access 1 January 28th, 2004 03:22 PM
Help! Cannot update records mcalder Classic ASP Databases 6 August 14th, 2003 01:54 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.