Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Classic ASP Databases Discuss using ASP 3 to work with data in databases, including ASP Database Setup issues from the old P2P forum on this specific subtopic. See also the book forum Beginning ASP.NET Databases for questions specific to that book. NOT for ASP.NET 1.0, 1.1, or 2.0.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Databases 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 August 10th, 2004, 09:15 AM
Authorized User
 
Join Date: Jun 2004
Location: miami, fl, USA.
Posts: 71
Thanks: 0
Thanked 0 Times in 0 Posts
Default Getting error in sql statement

I am getting this error!


Microsoft OLE DB Provider for SQL Server error '80040e14'

Incorrect syntax near the keyword 'FROM'.

/forms/Start2.asp, line 65

Line 65 is:

Set ObjRS = Server.CreateObject("ADODB.Recordset")


strSql = "SELECT Top 7 FROM Escalation_Forms WHERE Escalation_Type = '"& sqlselected & "'" & sqlStatus

line 65 " objRS.Open strSql, objConn, 0, 1"
Response.Write(strSQL)



 
Old August 10th, 2004, 11:02 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Harrisburg, PA, USA.
Posts: 1,998
Thanks: 0
Thanked 3 Times in 3 Posts
Default

Hey,

You aren't selecting any fields. You need to provide fields or * to select:

SELECT Top 7 * FROM Escalation_Forms
 
Old August 10th, 2004, 02:23 PM
Authorized User
 
Join Date: Jun 2004
Location: miami, fl, USA.
Posts: 71
Thanks: 0
Thanked 0 Times in 0 Posts
Default

When ever I finish on thing something else alway come up. I clean up the database and re-submit new data. They all fall under open. Sql statement worked fine with the open work order. When choose one of the jobs then change the status to pending. Next I try to retrieve that pending job it comes up with nothing. Coud be the way I am updating the information within the database? For example I have the following to updat my records: Should I change it to ObjRS.ADDNew
Code:
<%

Dim ObjConn, ObjRS, strSQL, TicketNum


set objConn=Server.CreateObject("ADODB.CONNECTION")

ObjConn.Open "Provider=sqloledb;Data Source=Flmirsql02;Initial Catalog=Source_Forms;User Id=Source_Forms_User;Password=password;"


Set ObjRS = Server.CreateObject("ADODB.Recordset")

strSQL = "UPDATE Escalation_Forms SET Status='"& request.form("Status") &"', Wip_Com='"& request.form("Wip_Com") &"' Where Ticket_Number=" & Session("TicketNum")

ObjRS.open strSQL, ObjConn, 3,3

if err<> 0 then
Response.Write("Something went wrong! Please Contact you Administrator!")

Else
Response.Write("Ticket Number" & TicketNum & " was successfully updated!")

End if

Set ObjRS =Nothing
ObjConn.Close
Set ObjConn = Nothing
%>


 
Old August 10th, 2004, 03:08 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Harrisburg, PA, USA.
Posts: 1,998
Thanks: 0
Thanked 3 Times in 3 Posts
Default

Hey,

I personally never used the objrs.AddNew method. Your update statement seems fine, but I would check for an existing Session("TicketNum") before updating.

Are you getting an error statement?

Brian
 
Old August 10th, 2004, 03:30 PM
Authorized User
 
Join Date: Jun 2004
Location: miami, fl, USA.
Posts: 71
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Do you know how I can get the statement to retrieve only the current data or the on that is listed on left hand?

Here's how it look in the database right for status:

Job Status: Pending , Open
            current old

I hope this makes sense. This not easy but I like the challenge

 
Old August 10th, 2004, 11:02 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

Calibus,

Quote:
quote:strSQL = "UPDATE Escalation_Forms SET Status='"& request.form("Status") &"', Wip_Com='"& request.form("Wip_Com") &"' Where Ticket_Number=" & Session("TicketNum")

ObjRS.open strSQL, ObjConn, 3,3
You are constructing an update statement and trying to open a recordset using that. Why is that so? Now what is that you would do result in the recordset after executing update statement. I am not sure if you would end up seeing some error with this. But this is not right.

When you want to execute your DMLs(Data Manipulation Language), you got to use
Code:
ObjConn.Execute(strSQL)
Use Recordset Object only when your query returns a resultset which you got to use for later processing.

Hope that helps.

_________________________
- Vijay G
Strive for Perfection
 
Old August 10th, 2004, 11:08 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

Quote:
quote:Originally posted by Calibus
 Do you know how I can get the statement to retrieve only the current data or the on that is listed on left hand?
...
I am not sure what you wanted from that post. Can ou explain more on that?

Cheers!

_________________________
- Vijay G
Strive for Perfection
 
Old August 11th, 2004, 07:11 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,110
Thanks: 0
Thanked 3 Times in 3 Posts
Default

Do you have a 'date' column?

If you do you can "SELECT TOP 10 the_date, whatever_column, column_3 FROM table_name ORDER BY the_date DESC;"





Similar Threads
Thread Thread Starter Forum Replies Last Post
T-SQL statement causing error with insert/update saf SQL Language 0 September 7th, 2007 04:48 PM
convert a SQL Statement from MS Access to a SQL Corey Access 6 March 28th, 2007 12:33 PM
Syntax error on SQL statement Chris1 Access VBA 1 September 7th, 2004 07:47 AM
SQL Statement Error riskey_457 SQL Server ASP 9 June 10th, 2003 11:35 AM
SQL Statement Error riskey_457 Access ASP 4 June 9th, 2003 11:31 PM





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