Wrox Programmer Forums

Need to download code?

View our list of code downloads.

| FAQ | Members List | Calendar | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
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)



Reply With Quote
  #2 (permalink)  
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
Reply With Quote
  #3 (permalink)  
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
%>


Reply With Quote
  #4 (permalink)  
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
Reply With Quote
  #5 (permalink)  
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

Reply With Quote
  #6 (permalink)  
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
Reply With Quote
  #7 (permalink)  
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
Reply With Quote
  #8 (permalink)  
Old August 11th, 2004, 07:11 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,111
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;"

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 10:35 AM.


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