Incorrect syntax with Declare

Good morning
I hope this will be a simple one, but I am trying to write a view to query my SQL server database, setting a variable using "declare". The code runs fine and returns a number of records, but when I try to save it, it comes up with "Incorrect syntax near the keyword DECLARE", and will not save. I am a bit of a novice when it comes to SQL, but I don't understand why it runs, but won't save. Here is my variable:
DECLARE @.ANCHORDATE AS DATETIME
SET @.ANCHORDATE = CASE WHEN datepart(mm, getdate()) BETWEEN 4 AND 9 THEN dateadd(mm, 3, DATEADD(yy, DATEDIFF(yy, 0, getdate()), 0)) ELSE dateadd(mm, - 9,
DATEADD(yy, DATEDIFF(yy, 0, getdate()), 0)) END

Thanks for any help.I am not 100% sure but you can try not to use DECLARE and try and save it. Let me know if it helps.|||I have replaced all the instances of the variable with the actual definition, and the view runs and saves fine. I was hoping to be able to keep it simple by not having to type in the variable definition each time. I don't understand why it can run, but saving it returns an error.

Incorrect syntax when there appears to be no syntax errors.

I keep receiving the following error whenever I try and call this function to update my database.

The code was working before, all I added was an extra field to update.

Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'WHERE'

Public Sub MasterList_Update(sender As Object, e As DataListCommandEventArgs)

Dim strProjectName, txtProjectDescription, intProjectID, strProjectState as String
Dim intEstDuration, dtmCreationDate, strCreatedBy, strProjectLead, dtmEstCompletionDate as String

strProjectName = CType(e.Item.FindControl("txtProjectName"), TextBox).Text
txtProjectDescription = CType(e.Item.FindControl("txtProjDesc"), TextBox).Text
strProjectState = CType(e.Item.FindControl("txtStatus"), TextBox).Text
intEstDuration = CType(e.Item.FindControl("txtDuration"), TextBox).Text
dtmCreationDate = CType(e.Item.FindControl("txtCreation"),TextBox).Text
strCreatedBy = CType(e.Item.FindControl("txtCreatedBy"),TextBox).Text
strProjectLead = CType(e.Item.FindControl("txtLead"),TextBox).Text
dtmEstCompletionDate = CType(e.Item.FindControl("txtComDate"),TextBox).Text
intProjectID = CType(e.Item.FindControl("lblProjectID"), Label).Text

Dim strSQL As String
strSQL = "Update tblProject " _
& "Set strProjectName = @.strProjectName, " _
& "txtProjectDescription = @.txtProjectDescription, " _
& "strProjectState = @.strProjectState, " _
& "intEstDuration = @.intEstDuration, " _
& "dtmCreationDate = @.dtmCreationDate, " _
& "strCreatedBy = @.strCreatedBy, " _
& "strProjectLead = @.strProjectLead, " _
& "dtmEstCompletionDate = @.dtmEstCompletionDate, " _
& "WHERE intProjectID = @.intProjectID"

Dim myConnection As New SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("connectionstring"))
Dim cmdSQL As New SqlCommand(strSQL, myConnection)

cmdSQL.Parameters.Add(new SqlParameter("@.strProjectName", SqlDbType.NVarChar, 40))
cmdSQL.Parameters("@.strProjectName").Value = strProjectName
cmdSQL.Parameters.Add(new SqlParameter("@.txtProjectDescription", SqlDbType.NVarChar, 30))
cmdSQL.Parameters("@.txtProjectDescription").Value = txtProjectDescription
cmdSQL.Parameters.Add(new SqlParameter("@.strProjectState", SqlDbType.NVarChar, 30))
cmdSQL.Parameters("@.strProjectState").Value = strProjectState
cmdSQL.Parameters.Add(new SqlParameter("@.intEstDuration", SqlDbType.NVarChar, 60))
cmdSQL.Parameters("@.intEstDuration").Value = intEstDuration
cmdSQL.Parameters.Add(new SqlParameter("@.dtmCreationDate", SqlDbType.NVarChar, 15))
cmdSQL.Parameters("@.dtmCreationDate").Value = dtmCreationDate
cmdSQL.Parameters.Add(new SqlParameter("@.strCreatedBy", SqlDbType.NVarChar, 10))
cmdSQL.Parameters("@.strCreatedBy").Value = strCreatedBy
cmdSQL.Parameters.Add(new SqlParameter("@.strProjectLead", SqlDbType.NVarChar, 15))
cmdSQL.Parameters("@.strProjectLead").Value = strProjectLead
cmdSQL.Parameters.Add(new SqlParameter("@.dtmEstCompletionDate", SqlDbType.NVarChar, 24))
cmdSQL.Parameters("@.dtmEstCompletionDate").Value = dtmEstCompletionDate
cmdSQL.Parameters.Add(new SqlParameter("@.intProjectID", SqlDbType.NChar, 5))
cmdSQL.Parameters("@.intProjectID").Value = intProjectID

myConnection.Open()
cmdSQL.ExecuteNonQuery
myConnection.Close()

MasterList.EditItemIndex = -1
BindMasterList()

End Sub

Thankyou in advance.> cmdSQL.Parameters.Add(new SqlParameter("@.intProjectID", SqlDbType.NChar, 5))

why wouldintProjectID be an NChar? or is that just misleading?|||You have an extra comma.

"dtmEstCompletionDate = @.dtmEstCompletionDate, " _
"WHERE ... "

that would result in:

dtmEstCompletionDate = '1/1/2003', WHERE

there's an extra comma before the WHERE clause. That is causing the syntax error.

Cheers
Ken|||Good one!sql

Incorrect Syntax using IF statement

Hi,

I'm new to SQL Server Programming, I work with ASP a lot, but lately
I've been trying to create Stored Procedures, etc. I'm having a
problem writing a simple IF statement.. I don't seem to understand why
it's giving me this error. I've search around on Google Groups, but I
still don't get it.

=================
USE msdb

IF NOT EXISTS (SELECT * FROM sysjobs WHERE name = 'Scheduled Nightfax')

END
=================

My error is:
Server: Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'END'.

Thanks for any help.you need a BEGIN for every END
example

DECLARE @.v BIT
SELECT @.v = 1

IF @.v = 1
BEGIN
select 'yes'
END
ELSE
BEGIN
select 'No'
END

Or without begin...end
IF @.v = 1
select 'yes'
ELSE
select 'No'

Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Thanks, I played with it a bit and I got the result I was looking for.

==============
USE msdb
DECLARE @.JobName varchar(255)
SELECT @.JobName = name FROM sysjobs WHERE name = 'Scheduled Nightfax'

IF @.JobName = 'Scheduled Nightfax'
PRINT 'YES'
ELSE
PRINT 'NO'
==============

incorrect syntax question

i have a table of data that the user can enter into, the data type is set to "text" and has worked in some test so far, but when i type data in '' marks such as :

'text here'

it gives me an incorrect syntax error, is there a way around this? or is the '' charectors invalid? thanks John

A single quote is a string separator. So if your data has single quotes you might have to excape it with double quotes: example:Select'test''s'

|||Are you using parameters? If not, you should. It will help avoid this kind of problem. Others might suggest doubling the apostrophes, but while it works, that is not the answer.|||

Mikesdotnetting:

Are you using parameters? If not, you should. It will help avoid this kind of problem. Others might suggest doubling the apostrophes, but while it works, that is not the answer.

Agreed.

|||

parameters will stop me getting this error? awesome! thanks for your help John

|||

I have an insert query which gives me a similar error, I cant see why its not working, the error is

A potentially dangerous Request.Form value was detected from the client (ctl00$ContentPlaceHolder1$CommentBox="<b>test text</b>").

My code is :

Connection.Open();
SqlCommand InsertItem = new SqlCommand("INSERT INTO TestTable(Inserted) VALUES ('@.item')", Connection);
InsertItem.Parameters.Add("@.item", SqlDbType.VarChar).Value = Textbox1.Text;
InsertItem.ExecuteNonQuery();
Connection.Close();
I simply tryed to insert the text string <b>test text</b>

Thanks John

|||

You dont need to put quotes if you are using parameterized queries.

SqlCommand InsertItem = new SqlCommand("INSERT INTO TestTable(Inserted) VALUES (@.item)", Connection);

|||It's objecting to the fact that you are trying to input html tags. Set ValidateRequest to false in the @.Page directive:http://www.asp.net/learn/whitepapers/request-validation/|||

thats awesome thanks, that answered every question i could come up with! haha John

Incorrect syntax near....?

I keep getting this error...?

Exception Details:System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near 'c'.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException (0x80131904): Line 1: Incorrect syntax near 'c'.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +177 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +68 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +199 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2305 System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +31 System.Data.SqlClient.SqlDataReader.get_MetaData() +62 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +294 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +1021 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +314 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +20 System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +107 System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +10 System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +7 System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +139 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +140 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83 System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1659 System.Web.UI.WebControls.BaseDataList.GetData() +53 System.Web.UI.WebControls.DataList.CreateControlHierarchy(Boolean useDataSource) +267 System.Web.UI.WebControls.BaseDataList.OnDataBinding(EventArgs e) +56 System.Web.UI.WebControls.BaseDataList.DataBind() +62 System.Web.UI.WebControls.BaseDataList.EnsureDataBound() +55 System.Web.UI.WebControls.BaseDataList.CreateChildControls() +62 System.Web.UI.Control.EnsureChildControls() +97 System.Web.UI.Control.PreRenderRecursiveInternal() +50 System.Web.UI.Control.PreRenderRecursiveInternal() +171 System.Web.UI.Control.PreRenderRecursiveInternal() +171 System.Web.UI.Control.PreRenderRecursiveInternal() +171 System.Web.UI.Control.PreRenderRecursiveInternal() +171 System.Web.UI.Control.PreRenderRecursiveInternal() +171 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5729

I think it talking about my SQL Query...?? If you could help me it would greatly appreciated! Thanks

strSQLQuery =

"Select c.EmployeeID, c.[FirstName] as UserFirstName, c.[LastName] as UserLastName, eqtype.[Description] as UserEquipType, eq.[SerialNo] as UserSerialNum from EMPLOYEES c LEFT OUTER JOIN EQUIPMENT as eq on eq.EmployeeID = c.EmployeeID LEFT OUTER JOIN EQUIP_TYPE as eqtype on eqtype.EquipTypeID = eq.EquipTypeID"

I could be wrong but the error is the c.EmployeeID because the ID is IDENTITY which is a property not a column. In the mean time download SQL Prompt for free from Red Gate to use intelisense in Management Studio. Hope this helps.

http://www.red-gate.com/products/SQL_Prompt/index.htm?utm_source=sscentral&utm_medium=banner&utm_campaign=sqlprompt

|||You are right in saying that your query is causing the error. But I have to say that the query looks OK to me. Can you show us a little more of the code? Are you further modifying strSQLQuery in any manner?|||

Can you run this query in "Query analyser", it might give you more information

|||

It was a space... :(

|||

strSQLQuery =

"Select c.EmployeeID, c.[FirstName] as UserFirstName, c.[LastName] as UserLastName, eqtype.[Description] as UserEquipType, eq.[SerialNo] as UserSerialNum from EMPLOYEES c LEFT OUTER JOIN EQUIPMENT as eq on eq.EmployeeID = c.EmployeeID LEFT OUTER JOIN EQUIP_TYPE as eqtype on eqtype.EquipTypeID = eq.EquipTypeID " <<<< right after eq.EquipTypeID and "!!

Incorrect Syntax Near...

Hello all,
here is my code

objConn = newSqlConnection(System.Web.Configuration.WebConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
objCmd = new SqlCommand("SELECT * FROM catalogue WHERE Title =" +Request.QueryString["detail"], objConn);
objConn.Open();
objRdr = objCmd.ExecuteReader();
DataList1.DataSource = objRdr;
DataList1.DataBind();
objRdr.Close();
objConn.Close();

I am trying to select records from the database where they match the query string.
However when I run it, i get an error at objRdr =objCmd.ExecuteReader();, incorrect Syntax Near, and the name of thequery string

Anyone have an idea?

ThanksHi,

Assuming Title is a string/char/vchar (or whatever) type in the database, you would have to enclose the value in quotes would you not? Like so:

... WHERE Title ='" + Request.QueryString["detail"] +"'", objConn);

Just a guess though. I did not actually setup a test scenario.

James Steele|||

The error is due to the syntax in your sql statement. You can put a single quota around your value or use a parameterized query for better secure your code.

objConn = new SqlConnection(System.Web.Configuration.WebConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
objCmd = new SqlCommand("SELECT * FROM catalogue WHERE Title=@.detail, objConn);

objCmd.Parameters.Add(New SQLParameter("@.detail", Request.QueryString["detail"]);

objConn.Open();
objRdr = objCmd.ExecuteReader();
DataList1.DataSource = objRdr;
DataList1.DataBind();
objRdr.Close();
objConn.Close();

|||

i had that problem before...

try changeRequest.QueryString["detail"]

toRequest.QueryString("detail")

Incorrect syntax near XML-While creating XML Schema

Hi,

Im trying to create a xml schema like

CREATE XML SCHEMA COLLECTION BooksSchemaCollection AS
N'<?xml version="1.0" encoding="UTF-16"?>
<xsd:schema elementFormDefault="unqualified"
attributeFormDefault="unqualified"
xmlns:xsd="http://www.w3.org/2001/XMLSchema" >
<xsd:element name="book">
<xsd:complexType mixed="false">
<xsd:sequence>
<xsd:element name="name" type="xsd:string"/>
<xsd:element name="author" type="xsd:string"/>
<xsd:element name="publisher" type="xsd:string"/>
<xsd:element name="cost" type="xsd:integer"/>
<xsd:element name="comments" type="xsd:string"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>';

But when i execute , im getting a error like Incorrect syntax near 'XML' .any one know why its comming??

Thanks

Hi ,

I use SqlServer 2005 and above SQL statement works fine on my pc.

Make sure you have correct permission.

To create an XML SCHEMA COLLECTION requires at least one of the following sets of permissions:

CONTROL permission on the server
ALTER ANY DATABASE permission on the server
ALTER permission on the database
CONTROL permission in the database
ALTER ANY SCHEMA permission and CREATE XML SCHEMA COLLECTION permission in the database
ALTER or CONTROL permission on the relational schema and CREATE XML SCHEMA COLLECTION permission in the database
sql

Incorrect Syntax near WHERE Clause

<asp:SqlDataSource ID="productsUpdate" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionStringAccountType %>" SelectCommand="SELECT [ProductID], [ProductName], [ImageTitle], [ImageData], [ImageMimeType] FROM [Products] WHERE [ProductID] = @.ProductID" UpdateCommand="UPDATE [Products] SET [ImageTitle] = Null, [ImageData] = Null, [ImageMimeType] = Null WHERE [ProductID] = @.ProductID" InsertCommand="INSERT INTO [Products] ([ImageTitle], [ImageData], [ImageMimeType]) VALUES (@.ImageTitle, @.ImageData, @.MimeType) WHERE ([ProductID] = @.ProductID)"> <SelectParameters> <asp:ControlParameter ControlID="GridView1" Name="ProductID" PropertyName="SelectedValue" Type="Int32" /> </SelectParameters> <UpdateParameters> <asp:Parameter Name="ImageTitle" /> <asp:Parameter Name="ImageData" Type="string" /> <asp:Parameter Name="MimeType" Type="string" /> </UpdateParameters> <InsertParameters> <asp:Parameter Name="ImageTitle" Type="String" /> <asp:Parameter Name="ImageData" /> <asp:Parameter Name="MimeType" Type="String" /> </InsertParameters> </asp:SqlDataSource>

Hi Guys, new to development using visual studio and vb.net 2.0. and SQL express. Have a small problem.

I have an update command which im using to change the values of a blob image table to NULL so that i can then insert a new image into the table. This works fine. My problem is that my insert statement has an error and i cant figure this out. if i dont add the where clause it works fine but inserts the new image into the table without the product data, i want to insert the new image into the existing product information.

The error message i keep getting is "incorrect syntax near where"

Any help would be greatly appreciated
Thanks

Hi,

Does the following columns ]

[ImageTitle] , [ImageData], [ImageMimeType] are declared as "Allow NULLS" in the DB Design ?

|||

You need to add the following to your UpdateParameters:

<asp:ControlParameter ControlID="GridView1" Name="ProductID" PropertyName="SelectedValue"
 Type="Int32" />

|||

Yes the columns allow Null values,

Ill try the other solution and mark it if its the answer

Thanks for your replies

|||

INSERTS don't have WHERE clauses. INSERT is for adding a row to a table. UPDATE is for changing the contents of rows.

|||

Please remove the WHERE clause from InsertCommand.

InsertCommand="INSERT INTO [Products] ([ImageTitle], [ImageData], [ImageMimeType]) VALUES (@.ImageTitle, @.ImageData, @.MimeType)"

|||

Ok im going to change my approach and rephrase this whole thing to a question :)

I have a table called products which holds the product name ,price, size, bla bla, and an image, mimetype and image title, i need to update this image incase it ever needs changing, i simply want the user to be able to select a new image and then overwrite/update over the original image, i had 2 controls on this page within a details view control, one was a bound text box for the title, one was a file upload control to select the new image, however when i tried this out it kept coming up with a message that it couldnt convert varbinary into varchar so i tried this approach instead.

Any help you can provide would be greatly appreciated

Regards

|||

Hi,

Since your new question is not directly related to the original <syntax near WHERE Clause> issue, it would be best if you open up a new thread for the new question. In this way, our discussion here will not deviate too much from the original issue. This will make answer searching in the forum easier and be beneficial to other community members as well.Thank you for your understanding.

Incorrect syntax near Waltz !

Hello to everyone,

I am developing a web site connected to an sql server 2000.I recently stepped onto this error message.I googled it but didn't find anything.

I do not have any variables,tables or fields named this way.

Have you guys met this error message before?

thanks in advance

Line 1: Incorrect syntax near 'Waltz'.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near 'Waltz'.

Source Error:

Line 289:
Line 290: UpdateFilename.Connection.Open()
Line 291: UpdateFilename.ExecuteNonQuery()
Line 292: UpdateFilename.Connection.Close()
Line 293:

Source File:c:\inetpub\wwwroot\agrolasithiAdmin\AdvertInfo.aspx.vb Line:291

Stack Trace:

[SqlException: Line 1: Incorrect syntax near 'Waltz'.]
 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +182
 agrolasithiAdmin.AdvertInfo.SubmitBtn_Click(Object sender, EventArgs e) in c:\inetpub\wwwroot\agrolasithiAdmin\AdvertInfo.aspx.vb:291
 System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108
 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +57
 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18
 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
 System.Web.UI.Page.ProcessRequestMain() +1277

Are you calling a proc o executing T-SQL? Post the relevant T-SQL. Are you passing any values that contain the word "waltz".

|||

Post sql which you are using..

Incorrect syntax near 'use '

I am trying to run the following three line statement and I get an error
stating: "Line 3: Incorrect syntax near 'use '."
The database name is correct, "cms user messaging"
declare @.dbname sysname
set @.dbname = 'cms user messaging'
'use ' + @.dbname + ' DBCC SHOWFILESTATS with no_infomsgs'
Message posted via http://www.webservertalk.comTry,
declare @.dbname sysname
set @.dbname = 'cms user messaging'
exec('use [' + @.dbname + '] DBCC SHOWFILESTATS with no_infomsgs')
go
AMB
"Robert Richards via webservertalk.com" wrote:

> I am trying to run the following three line statement and I get an error
> stating: "Line 3: Incorrect syntax near 'use '."
> The database name is correct, "cms user messaging"
> declare @.dbname sysname
> set @.dbname = 'cms user messaging'
> 'use ' + @.dbname + ' DBCC SHOWFILESTATS with no_infomsgs'
> --
> Message posted via http://www.webservertalk.com
>|||The first message was incorrect. This is the correct statement I am trying
to run:
declare @.dbname sysname
set @.dbname = 'cms user messaging'
create table #datafilestats
( dbname varchar(25),
flag bit default 0,
Fileid tinyint,
[FileGroup] tinyint,
TotalExtents dec (7, 1),
UsedExtents dec (7, 1),
[Name] varchar(50),
[FileName] sysname )
declare @.string sysname
set @.string = 'use ' + @.dbname + ' DBCC SHOWFILESTATS with no_infomsgs'
insert into #datafilestats (Fileid, [FileGroup] , TotalExtents ,
UsedExtents , [Name] , [FileName]) exec (@.string)
In running this statement I get the following error message:
Server: Msg 911, Level 16, State 1, Line 1
Could not locate entry in sysdatabases for database 'cms'. No entry found
with that name. Make sure that the name is entered correctly.
Message posted via http://www.webservertalk.com|||Try,
declare @.dbname sysname
set @.dbname = 'cms user messaging'
create table #datafilestats
( dbname varchar(25),
flag bit default 0,
Fileid tinyint,
[FileGroup] tinyint,
TotalExtents dec (7, 1),
UsedExtents dec (7, 1),
[Name] varchar(50),
[FileName] sysname )
declare @.string sysname
set @.string = 'use [' + @.dbname + '] DBCC SHOWFILESTATS with no_infomsgs'
insert into #datafilestats (Fileid, [FileGroup] , TotalExtents , UsedExtents
, [Name] , [FileName])
exec (@.string)
select * from #datafilestats
...
AMB
"Robert Richards via webservertalk.com" wrote:

> The first message was incorrect. This is the correct statement I am trying
> to run:
> declare @.dbname sysname
> set @.dbname = 'cms user messaging'
> create table #datafilestats
> ( dbname varchar(25),
> flag bit default 0,
> Fileid tinyint,
> [FileGroup] tinyint,
> TotalExtents dec (7, 1),
> UsedExtents dec (7, 1),
> [Name] varchar(50),
> [FileName] sysname )
> declare @.string sysname
> set @.string = 'use ' + @.dbname + ' DBCC SHOWFILESTATS with no_infomsgs'
> insert into #datafilestats (Fileid, [FileGroup] , TotalExtents ,
> UsedExtents , [Name] , [FileName]) exec (@.string)
> In running this statement I get the following error message:
> Server: Msg 911, Level 16, State 1, Line 1
> Could not locate entry in sysdatabases for database 'cms'. No entry found
> with that name. Make sure that the name is entered correctly.
> --
> Message posted via http://www.webservertalk.com
>

Incorrect syntax near the keyword 'WHERE'.

Any Ideas as to this error message. I am trying to learn using ms sql
server 7.0

Below is the code I am using for an update to a MS Sql Database.

<%@. Language=VBScript %>
<% Option Explicit %>

<html>
<head>
<title>Sample Script 2 - Part 3 </title>
<!-- copyright MDFernandez -->
<link rel="stylesheet" type="text/css" href="http://links.10026.com/?link=../part3sol/style.css">
</head>
<body bgcolor="#FFFFFF">
<!--#include virtual="/adovbs.inc"-->

<center>
<%

Dim oRS
Dim Conn

Dim Id
Dim Name
Dim StreetAddress
Dim City
Dim State
Dim Zip
Dim PhoneNumber
dim sql

Id = request.form("Id")
Name = request.form("Name")
StreetAddress = request.form("StreetAddress")
City = request.form("City")
State = request.form("State")
Zip = request.form("Zip")
PhoneNumber = request.form("PhoneNumber")

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.open =("DRIVER=SQL Server;SERVER=(local);UID=;APP=AspRunner
Professional
Application;WSID=COMPAQAM;DATABASE=FriendsContactI nfo;Trusted_Connection=Yes")
'Conn.Open

sql="update FPFriends"
sql=sql & " set Name='" & Name & "',"
sql=sql & "StreetAddress='" & StreetAddress & "',"
sql=sql & "Ciy='" & City & "',"
sql=sql & "State='" & State & "',"
sql=sql & "Zip='" & Zip & "',"
sql=sql & "PhoneNumber='" & PhoneNumber & "',"
sql=sql & " WHERE Id=" & Id

set oRS=Conn.Execute (sql)
response.write "<font face='arial' size=4>"
response.write "<br><br>The record has been updated."
response.write "</b></font>"
' close the connection to the database
Conn.Close
%>
<!-- don't include in sample code display -->
<form>
<input type="button" value=" Close This Window "
onClick="window.location='aboutus.htm'"><br>
<button onClick="window.location='menu1_1.asp'">Update another
record</button>

</form>

</center>
</body>
</html>sql=sql & "PhoneNumber='" & PhoneNumber & "',"

It looks like the syntax error is due to the extraneous comma after the last
column.

I strongly suggest you google 'SQL injection'. Your current code will allow
a hacker can execute any arbitrary SQL statement. The best protection
against injection is to use parameterized SQL statements, stored procedures
and validate user input. Never build a SQL Statement string by
concatenating user input values. The example below uses a parameterized
UPDATE statement via OLEDB:

Const adParamInput = 1
Const adInteger = 3
Const adVarChar = 200

Set Conn = CreateObject("ADODB.Connection")
Set Command = CreateObject("ADODB.Command")

Conn.Open _
"Provider=SQLOLEDB;" & _
"Data Source=(local);" & _
"Integrated Security=SSPI;" & _
"Initial Catalog=FriendsContactInfo;" & _
"App=AspRunner Professional Application"

Command.ActiveConnection = Conn

Command.CommandText = _
" UPDATE dbo.FPFriends" & _
" SET" & _
" Name=?," & _
" StreetAddress=?," & _
" Ciy=?," & _
" State=?," & _
" Zip=?," & _
" PhoneNumber=?" & _
" WHERE Id=?"

Set parameter = Command.CreateParameter( _
"Name", _
adVarChar, _
adParamInput, _
30)
parameter.Value = Name
Command.Parameters.Append parameter

Set parameter = Command.CreateParameter( _
"StreetAddress", _
adVarChar, _
adParamInput, _
30)
parameter.Value = StreetAddress
Command.Parameters.Append parameter

Set parameter = Command.CreateParameter( _
"City", _
adVarChar, _
adParamInput, _
30)
parameter.Value = City
Command.Parameters.Append parameter

Set parameter = Command.CreateParameter( _
"State", _
adVarChar, _
adParamInput, _
2)
parameter.Value = State
Command.Parameters.Append parameter

Set parameter = Command.CreateParameter( _
"Zip", _
adVarChar, _
adParamInput, _
5)
parameter.Value = Zip
Command.Parameters.Append parameter

Set parameter = Command.CreateParameter( _
"PhoneNumber", _
adVarChar, _
adParamInput, _
15)
parameter.Value = PhoneNumber
Command.Parameters.Append parameter

Set parameter = Command.CreateParameter( _
"Id", _
adInteger, _
adParamInput)
parameter.Value = Id
Command.Parameters.Append parameter

Command.Execute

Conn.Close

--
Hope this helps.

Dan Guzman
SQL Server MVP

"DaveF" <jeacdf@.excite.comwrote in message
news:1173540573.073247.128620@.t69g2000cwt.googlegr oups.com...

Quote:

Originally Posted by

Any Ideas as to this error message. I am trying to learn using ms sql
server 7.0
>
Below is the code I am using for an update to a MS Sql Database.
>
<%@. Language=VBScript %>
<% Option Explicit %>
>
<html>
<head>
<title>Sample Script 2 - Part 3 </title>
<!-- copyright MDFernandez -->
<link rel="stylesheet" type="text/css" href="http://links.10026.com/?link=../part3sol/style.css">
</head>
<body bgcolor="#FFFFFF">
<!--#include virtual="/adovbs.inc"-->
>
<center>
<%
>
Dim oRS
Dim Conn
>
Dim Id
Dim Name
Dim StreetAddress
Dim City
Dim State
Dim Zip
Dim PhoneNumber
dim sql
>
Id = request.form("Id")
Name = request.form("Name")
StreetAddress = request.form("StreetAddress")
City = request.form("City")
State = request.form("State")
Zip = request.form("Zip")
PhoneNumber = request.form("PhoneNumber")
>
>
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.open =("DRIVER=SQL Server;SERVER=(local);UID=;APP=AspRunner
Professional
Application;WSID=COMPAQAM;DATABASE=FriendsContactI nfo;Trusted_Connection=Yes")
'Conn.Open
>
>
sql="update FPFriends"
sql=sql & " set Name='" & Name & "',"
sql=sql & "StreetAddress='" & StreetAddress & "',"
sql=sql & "Ciy='" & City & "',"
sql=sql & "State='" & State & "',"
sql=sql & "Zip='" & Zip & "',"
sql=sql & "PhoneNumber='" & PhoneNumber & "',"
sql=sql & " WHERE Id=" & Id
>
>
>
set oRS=Conn.Execute (sql)
response.write "<font face='arial' size=4>"
response.write "<br><br>The record has been updated."
response.write "</b></font>"
' close the connection to the database
Conn.Close
%>
<!-- don't include in sample code display -->
<form>
<input type="button" value=" Close This Window "
onClick="window.location='aboutus.htm'"><br>
<button onClick="window.location='menu1_1.asp'">Update another
record</button>
>
</form>
>
</center>
</body>
</html>
>

Incorrect syntax near the keyword ''WHERE''

Hello,
I'm receiving this error: Msg 156, Level 15, State 1, Procedure pnpcart_GetCustomer_Details, Line 50
Incorrect syntax near the keyword 'WHERE'.

Code Snippet

ALTER PROCEDURE [dbo].[pnpcart_GetCustomer_Details]
-- Add the parameters for the stored procedure here
@.inSearchBy varchar(20),
@.inSearchFor varchar(100)

AS

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @.SQL varchar(500)
if (@.inSearchBy='Email' or @.inSearchBy='HomePhone')
begin
set @.SQL = 'SELECT * FROM dbo.pnpcart_Customer_Details WHERE ' + @.inSearchBy + ' = ''' + @.inSearchFor + ''''
exec ( @.SQL )
end
else
begin
SELECT
dbo.pnpcart_Customer_Details.UserID, dbo.aspnet_Users.UserName
FROM
dbo.pnpcart_Customer_Details INNER JOIN dbo.aspnet_Users
ON
dbo.pnpcart_Customer_Details.UserID = dbo.aspnet_Users.UserName
GROUP BY
dbo.pnpcart_Customer_Details.UserID, dbo.aspnet_Users.UserName
WHERE dbo.aspnet_Users.UserName = ''+@.inSearchFor+''
end
END

My logic behind this code is utilize .NET membership database tables with one of my tables called pnpcart_Customer_Details. Instead of recreating the wheel I decided to INNER JOIN the columns dbo.pnpcart_Customer_Details.UserID = dbo.aspnet_Users.UserName, and do a search for the value of @.inSearchFor. Every time I want to search for a value of a variable I always end up creating dynamic sql code. I'm trying to stay away from dynamic sql. Any help with my error and how to stay away from dynamic sql is greatly appreciated. Thanks!

-Rich

The where clause should present before the Group By clause,

Code Snippet

SELECT

dbo.pnpcart_Customer_Details.UserID

, dbo.aspnet_Users.UserName

FROM

dbo.pnpcart_Customer_Details

INNER JOIN dbo.aspnet_Users

ON dbo.pnpcart_Customer_Details.UserID = dbo.aspnet_Users.UserName

WHERE

dbo.aspnet_Users.UserName = @.inSearchFor

GROUP BY

dbo.pnpcart_Customer_Details.UserID

, dbo.aspnet_Users.UserName

Or

Code Snippet

Set @.SQL = 'SELECT

dbo.pnpcart_Customer_Details.UserID

, dbo.aspnet_Users.UserName

FROM

dbo.pnpcart_Customer_Details

INNER JOIN dbo.aspnet_Users

ON dbo.pnpcart_Customer_Details.UserID = dbo.aspnet_Users.UserName

WHERE

dbo.aspnet_Users.UserName in (' +@.inSearchFor + ')

GROUP BY

dbo.pnpcart_Customer_Details.UserID

, dbo.aspnet_Users.UserName'

|||

You can do this:

Code Snippet

ALTER PROCEDURE [dbo].[pnpcart_GetCustomer_Details]
-- Add the parameters for the stored procedure here
@.inSearchBy varchar(20),
@.inSearchFor varchar(100)

AS

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @.SQL nvarchar(500)
if (@.inSearchBy='Email' or @.inSearchBy='HomePhone')
begin
set @.SQL = 'SELECT * FROM dbo.pnpcart_Customer_Details

WHERE ' + @.inSearchBy + ' = @.inSearchFor'

exec sp_executesql @.SQL, N'@.inSearchFor varchar(100)', @.inSearchFor

end
else
begin
SELECT
dbo.pnpcart_Customer_Details.UserID, dbo.aspnet_Users.UserName
FROM
dbo.pnpcart_Customer_Details INNER JOIN dbo.aspnet_Users
ON
dbo.pnpcart_Customer_Details.UserID = dbo.aspnet_Users.UserName

WHERE dbo.aspnet_Users.UserName = @.inSearchFor
GROUP BY
dbo.pnpcart_Customer_Details.UserID, dbo.aspnet_Users.UserName
end
END

sql

Incorrect syntax near the keyword ''WHERE''

Hello,
I'm receiving this error: Msg 156, Level 15, State 1, Procedure pnpcart_GetCustomer_Details, Line 50
Incorrect syntax near the keyword 'WHERE'.

Code Snippet

ALTER PROCEDURE [dbo].[pnpcart_GetCustomer_Details]
-- Add the parameters for the stored procedure here
@.inSearchBy varchar(20),
@.inSearchFor varchar(100)

AS

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @.SQL varchar(500)
if (@.inSearchBy='Email' or @.inSearchBy='HomePhone')
begin
set @.SQL = 'SELECT * FROM dbo.pnpcart_Customer_Details WHERE ' + @.inSearchBy + ' = ''' + @.inSearchFor + ''''
exec ( @.SQL )
end
else
begin
SELECT
dbo.pnpcart_Customer_Details.UserID, dbo.aspnet_Users.UserName
FROM
dbo.pnpcart_Customer_Details INNER JOIN dbo.aspnet_Users
ON
dbo.pnpcart_Customer_Details.UserID = dbo.aspnet_Users.UserName
GROUP BY
dbo.pnpcart_Customer_Details.UserID, dbo.aspnet_Users.UserName
WHERE dbo.aspnet_Users.UserName = ''+@.inSearchFor+''
end
END

My logic behind this code is utilize .NET membership database tables with one of my tables called pnpcart_Customer_Details. Instead of recreating the wheel I decided to INNER JOIN the columns dbo.pnpcart_Customer_Details.UserID = dbo.aspnet_Users.UserName, and do a search for the value of @.inSearchFor. Every time I want to search for a value of a variable I always end up creating dynamic sql code. I'm trying to stay away from dynamic sql. Any help with my error and how to stay away from dynamic sql is greatly appreciated. Thanks!

-Rich

The where clause should present before the Group By clause,

Code Snippet

SELECT

dbo.pnpcart_Customer_Details.UserID

, dbo.aspnet_Users.UserName

FROM

dbo.pnpcart_Customer_Details

INNER JOIN dbo.aspnet_Users

ON dbo.pnpcart_Customer_Details.UserID = dbo.aspnet_Users.UserName

WHERE

dbo.aspnet_Users.UserName = @.inSearchFor

GROUP BY

dbo.pnpcart_Customer_Details.UserID

, dbo.aspnet_Users.UserName

Or

Code Snippet

Set @.SQL = 'SELECT

dbo.pnpcart_Customer_Details.UserID

, dbo.aspnet_Users.UserName

FROM

dbo.pnpcart_Customer_Details

INNER JOIN dbo.aspnet_Users

ON dbo.pnpcart_Customer_Details.UserID = dbo.aspnet_Users.UserName

WHERE

dbo.aspnet_Users.UserName in (' +@.inSearchFor + ')

GROUP BY

dbo.pnpcart_Customer_Details.UserID

, dbo.aspnet_Users.UserName'

|||

You can do this:

Code Snippet

ALTER PROCEDURE [dbo].[pnpcart_GetCustomer_Details]
-- Add the parameters for the stored procedure here
@.inSearchBy varchar(20),
@.inSearchFor varchar(100)

AS

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @.SQL nvarchar(500)
if (@.inSearchBy='Email' or @.inSearchBy='HomePhone')
begin
set @.SQL = 'SELECT * FROM dbo.pnpcart_Customer_Details

WHERE ' + @.inSearchBy + ' = @.inSearchFor'

exec sp_executesql @.SQL, N'@.inSearchFor varchar(100)', @.inSearchFor

end
else
begin
SELECT
dbo.pnpcart_Customer_Details.UserID, dbo.aspnet_Users.UserName
FROM
dbo.pnpcart_Customer_Details INNER JOIN dbo.aspnet_Users
ON
dbo.pnpcart_Customer_Details.UserID = dbo.aspnet_Users.UserName

WHERE dbo.aspnet_Users.UserName = @.inSearchFor
GROUP BY
dbo.pnpcart_Customer_Details.UserID, dbo.aspnet_Users.UserName
end
END

Incorrect syntax near the keyword ''WHERE''

Hello,
I'm receiving this error: Msg 156, Level 15, State 1, Procedure pnpcart_GetCustomer_Details, Line 50
Incorrect syntax near the keyword 'WHERE'.

Code Snippet

ALTER PROCEDURE [dbo].[pnpcart_GetCustomer_Details]
-- Add the parameters for the stored procedure here
@.inSearchBy varchar(20),
@.inSearchFor varchar(100)

AS

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @.SQL varchar(500)
if (@.inSearchBy='Email' or @.inSearchBy='HomePhone')
begin
set @.SQL = 'SELECT * FROM dbo.pnpcart_Customer_Details WHERE ' + @.inSearchBy + ' = ''' + @.inSearchFor + ''''
exec ( @.SQL )
end
else
begin
SELECT
dbo.pnpcart_Customer_Details.UserID, dbo.aspnet_Users.UserName
FROM
dbo.pnpcart_Customer_Details INNER JOIN dbo.aspnet_Users
ON
dbo.pnpcart_Customer_Details.UserID = dbo.aspnet_Users.UserName
GROUP BY
dbo.pnpcart_Customer_Details.UserID, dbo.aspnet_Users.UserName
WHERE dbo.aspnet_Users.UserName = ''+@.inSearchFor+''
end
END

My logic behind this code is utilize .NET membership database tables with one of my tables called pnpcart_Customer_Details. Instead of recreating the wheel I decided to INNER JOIN the columns dbo.pnpcart_Customer_Details.UserID = dbo.aspnet_Users.UserName, and do a search for the value of @.inSearchFor. Every time I want to search for a value of a variable I always end up creating dynamic sql code. I'm trying to stay away from dynamic sql. Any help with my error and how to stay away from dynamic sql is greatly appreciated. Thanks!

-Rich

The where clause should present before the Group By clause,

Code Snippet

SELECT

dbo.pnpcart_Customer_Details.UserID

, dbo.aspnet_Users.UserName

FROM

dbo.pnpcart_Customer_Details

INNER JOIN dbo.aspnet_Users

ON dbo.pnpcart_Customer_Details.UserID = dbo.aspnet_Users.UserName

WHERE

dbo.aspnet_Users.UserName = @.inSearchFor

GROUP BY

dbo.pnpcart_Customer_Details.UserID

, dbo.aspnet_Users.UserName

Or

Code Snippet

Set @.SQL = 'SELECT

dbo.pnpcart_Customer_Details.UserID

, dbo.aspnet_Users.UserName

FROM

dbo.pnpcart_Customer_Details

INNER JOIN dbo.aspnet_Users

ON dbo.pnpcart_Customer_Details.UserID = dbo.aspnet_Users.UserName

WHERE

dbo.aspnet_Users.UserName in (' +@.inSearchFor + ')

GROUP BY

dbo.pnpcart_Customer_Details.UserID

, dbo.aspnet_Users.UserName'

|||

You can do this:

Code Snippet

ALTER PROCEDURE [dbo].[pnpcart_GetCustomer_Details]
-- Add the parameters for the stored procedure here
@.inSearchBy varchar(20),
@.inSearchFor varchar(100)

AS

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @.SQL nvarchar(500)
if (@.inSearchBy='Email' or @.inSearchBy='HomePhone')
begin
set @.SQL = 'SELECT * FROM dbo.pnpcart_Customer_Details

WHERE ' + @.inSearchBy + ' = @.inSearchFor'

exec sp_executesql @.SQL, N'@.inSearchFor varchar(100)', @.inSearchFor

end
else
begin
SELECT
dbo.pnpcart_Customer_Details.UserID, dbo.aspnet_Users.UserName
FROM
dbo.pnpcart_Customer_Details INNER JOIN dbo.aspnet_Users
ON
dbo.pnpcart_Customer_Details.UserID = dbo.aspnet_Users.UserName

WHERE dbo.aspnet_Users.UserName = @.inSearchFor
GROUP BY
dbo.pnpcart_Customer_Details.UserID, dbo.aspnet_Users.UserName
end
END

Incorrect syntax near the keyword WHERE

Can someone help me? I am trying to script an SQL statement that would allow someone to INSERT a new username into a database where it is not a duplicate entry. The table is like this:
UserID - int, 4, identity(1,1)
UserName - nvarchar(50)
UserPass - nvarchar(50)

The code to execute this where i am getting the errors is this:

Function ChooseUName()
 If Page.IsValid Then
 Dim objCon As New SqlConnection(con)
 Dim sqlInsert As String = "INSERT INTO tblUser (UserName) " & _
 "VALUES (@.Username) WHERE NOT EXISTS (SELECT UserName FROM tblUser)"
 Dim cmd As New SqlCommand(sqlInsert, objCon)
 cmd.Parameters.Add("@.Username", SqlDbType.NVarChar, 50)
 cmd.Parameters("@.Username").Value = txtUsername.Text
 Dim id As Integer
 Try
 objCon.Open()
 id = cmd.ExecuteScalar()
 Finally
 If objCon.State = ConnectionState.Open Then
 objCon.Close()
 End If
 End Try
 Response.Write("Your User ID is: " & id.ToString())
 Response.End()
 End If
 End Function

This is the error:

Incorrect syntax near the keyword 'WHERE'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'WHERE'.
Source Error:
Line 73: Try
Line 74: objCon.Open()
Line 75: id = cmd.ExecuteScalar()
Line 76: Finally
Line 77: If objCon.State = ConnectionState.Open Then

PLEASE HELP!! I'M ON A STRICT DEADLINE!!! :o THANKS IN ADVANCE!You need something more like this:

Dim sqlInsert As String = "IF NOT EXISTS (SELECT UserName FROM tblUser WHERE UserName=@.UserName) INSERT INTO tblUser (UserName) VALUES (@.Username)"

Terri|||THANK YOU SO VERY MUCH!!!

That worked like a charm. Do you or anyone else know of any resources online where i can learn more about using the SQL Syntax in my web apps? I've never seen such a string and now it's like a whole new world has opened up!

thanks again.|||You definitely need to haveSQL Server 2000 Books Online. This will help you tremendously with syntax issues. It's large but well worth the free download.

Another place you could look isMicrosoft ASP.NET Quickstarts Tutorial -- Server-Side Data Access. The section on "Inserting Data in a SQL Database" has coding to handle your situation, although they just do the INSERT and then test to see if it violated the primary key.

There are others out there, but those 2 resources come to mind first.

Terri

Incorrect syntax near the keyword 'UNION'. ??

Hi,
Why does this query give this error?
SELECT TOP 1 tblSMS.*, tblCampaigns.Mail
FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign =
tblCampaigns.Campaign
WHERE (Number = '+32479990284') OR (Number = '0479990284')
ORDER BY SendDate DESC
UNION
SELECT DISTINCT tblSMS.*, tblCampaigns.Mail
FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign =
tblCampaigns.Campaign
WHERE ((Number = '+32479990284') OR (Number = '0479990284')) AND (SendDate
> DATEADD(hh, 48 ,GETDATE() ))
Error:
Server: Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'UNION'.
What I need to do is Select all the records (tblSMS) with SendDate from the
last 48 hours, and I have also to select the Last one (in case it isn't yet
in the last 48 hours).
Does anybody knwos what goes wrong?
If I try the two query's separetly they work fine...
Thanks a lot,
Pieter
You can't have an ORDER BY before the UNION. Put the ORDER BY after the
final SELECT.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:u1zDVHzxEHA.4040@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Why does this query give this error?
> SELECT TOP 1 tblSMS.*, tblCampaigns.Mail
> FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign =
> tblCampaigns.Campaign
> WHERE (Number = '+32479990284') OR (Number = '0479990284')
> ORDER BY SendDate DESC
> UNION
> SELECT DISTINCT tblSMS.*, tblCampaigns.Mail
> FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign =
> tblCampaigns.Campaign
> WHERE ((Number = '+32479990284') OR (Number = '0479990284')) AND
(SendDate
> Error:
> Server: Msg 156, Level 15, State 1, Line 5
> Incorrect syntax near the keyword 'UNION'.
> What I need to do is Select all the records (tblSMS) with SendDate from
the
> last 48 hours, and I have also to select the Last one (in case it isn't
yet
> in the last 48 hours).
> Does anybody knwos what goes wrong?
> If I try the two query's separetly they work fine...
> Thanks a lot,
> Pieter
>
|||An Order By can only be used to determine the output of the entire result
set when you use
UNION. You can't use it with the individual queries.
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:u1zDVHzxEHA.4040@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Why does this query give this error?
> SELECT TOP 1 tblSMS.*, tblCampaigns.Mail
> FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign =
> tblCampaigns.Campaign
> WHERE (Number = '+32479990284') OR (Number = '0479990284')
> ORDER BY SendDate DESC
> UNION
> SELECT DISTINCT tblSMS.*, tblCampaigns.Mail
> FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign =
> tblCampaigns.Campaign
> WHERE ((Number = '+32479990284') OR (Number = '0479990284')) AND
(SendDate
> Error:
> Server: Msg 156, Level 15, State 1, Line 5
> Incorrect syntax near the keyword 'UNION'.
> What I need to do is Select all the records (tblSMS) with SendDate from
the
> last 48 hours, and I have also to select the Last one (in case it isn't
yet
> in the last 48 hours).
> Does anybody knwos what goes wrong?
> If I try the two query's separetly they work fine...
> Thanks a lot,
> Pieter
>
|||By the way, that will totally distroy your TOP 1 logic... but here's a very
simple solution:
SELECT *
FROM
(SELECT TOP 1 tblSMS.*, tblCampaigns.Mail
FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign =
tblCampaigns.Campaign
WHERE (Number = '+32479990284') OR (Number = '0479990284')
ORDER BY SendDate DESC) x
UNION
SELECT DISTINCT tblSMS.*, tblCampaigns.Mail
FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign =
tblCampaigns.Campaign
WHERE ((Number = '+32479990284') OR (Number = '0479990284'))
AND (SendDate > DATEADD(hh, 48 ,GETDATE() ))
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:u1zDVHzxEHA.4040@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Why does this query give this error?
> SELECT TOP 1 tblSMS.*, tblCampaigns.Mail
> FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign =
> tblCampaigns.Campaign
> WHERE (Number = '+32479990284') OR (Number = '0479990284')
> ORDER BY SendDate DESC
> UNION
> SELECT DISTINCT tblSMS.*, tblCampaigns.Mail
> FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign =
> tblCampaigns.Campaign
> WHERE ((Number = '+32479990284') OR (Number = '0479990284')) AND
(SendDate
> Error:
> Server: Msg 156, Level 15, State 1, Line 5
> Incorrect syntax near the keyword 'UNION'.
> What I need to do is Select all the records (tblSMS) with SendDate from
the
> last 48 hours, and I have also to select the Last one (in case it isn't
yet
> in the last 48 hours).
> Does anybody knwos what goes wrong?
> If I try the two query's separetly they work fine...
> Thanks a lot,
> Pieter
>
|||You can't put ORDER BY inside individual parts of the UNION. Move it to the
end...
http://www.aspfaq.com/
(Reverse address to reply.)
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:u1zDVHzxEHA.4040@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Why does this query give this error?
> SELECT TOP 1 tblSMS.*, tblCampaigns.Mail
> FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign =
> tblCampaigns.Campaign
> WHERE (Number = '+32479990284') OR (Number = '0479990284')
> ORDER BY SendDate DESC
> UNION
> SELECT DISTINCT tblSMS.*, tblCampaigns.Mail
> FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign =
> tblCampaigns.Campaign
> WHERE ((Number = '+32479990284') OR (Number = '0479990284')) AND
(SendDate
> Error:
> Server: Msg 156, Level 15, State 1, Line 5
> Incorrect syntax near the keyword 'UNION'.
> What I need to do is Select all the records (tblSMS) with SendDate from
the
> last 48 hours, and I have also to select the Last one (in case it isn't
yet
> in the last 48 hours).
> Does anybody knwos what goes wrong?
> If I try the two query's separetly they work fine...
> Thanks a lot,
> Pieter
>
|||Thansk guys!! It works fine now!
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uqB6AMzxEHA.2036@.TK2MSFTNGP12.phx.gbl...
> By the way, that will totally distroy your TOP 1 logic... but here's a
very
> simple solution:
> SELECT *
> FROM
> (SELECT TOP 1 tblSMS.*, tblCampaigns.Mail
> FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign =
> tblCampaigns.Campaign
> WHERE (Number = '+32479990284') OR (Number = '0479990284')
> ORDER BY SendDate DESC) x
> UNION
> SELECT DISTINCT tblSMS.*, tblCampaigns.Mail
> FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign =
> tblCampaigns.Campaign
> WHERE ((Number = '+32479990284') OR (Number = '0479990284'))
> AND (SendDate > DATEADD(hh, 48 ,GETDATE() ))
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> news:u1zDVHzxEHA.4040@.TK2MSFTNGP11.phx.gbl...
> (SendDate
> the
> yet
>

Incorrect syntax near the keyword 'UNION'. ??

Hi,
Why does this query give this error?
SELECT TOP 1 tblSMS.*, tblCampaigns.Mail
FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign =
tblCampaigns.Campaign
WHERE (Number = '+32479990284') OR (Number = '0479990284')
ORDER BY SendDate DESC
UNION
SELECT DISTINCT tblSMS.*, tblCampaigns.Mail
FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign =
tblCampaigns.Campaign
WHERE ((Number = '+32479990284') OR (Number = '0479990284')) AND (SendDate
> DATEADD(hh, 48 ,GETDATE() ))
Error:
Server: Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'UNION'.
What I need to do is Select all the records (tblSMS) with SendDate from the
last 48 hours, and I have also to select the Last one (in case it isn't yet
in the last 48 hours).
Does anybody knwos what goes wrong?
If I try the two query's separetly they work fine...
Thanks a lot,
PieterYou can't have an ORDER BY before the UNION. Put the ORDER BY after the
final SELECT.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:u1zDVHzxEHA.4040@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Why does this query give this error?
> SELECT TOP 1 tblSMS.*, tblCampaigns.Mail
> FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign =
> tblCampaigns.Campaign
> WHERE (Number = '+32479990284') OR (Number = '0479990284')
> ORDER BY SendDate DESC
> UNION
> SELECT DISTINCT tblSMS.*, tblCampaigns.Mail
> FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign =
> tblCampaigns.Campaign
> WHERE ((Number = '+32479990284') OR (Number = '0479990284')) AND
(SendDate
> Error:
> Server: Msg 156, Level 15, State 1, Line 5
> Incorrect syntax near the keyword 'UNION'.
> What I need to do is Select all the records (tblSMS) with SendDate from
the
> last 48 hours, and I have also to select the Last one (in case it isn't
yet
> in the last 48 hours).
> Does anybody knwos what goes wrong?
> If I try the two query's separetly they work fine...
> Thanks a lot,
> Pieter
>|||An Order By can only be used to determine the output of the entire result
set when you use
UNION. You can't use it with the individual queries.
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:u1zDVHzxEHA.4040@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Why does this query give this error?
> SELECT TOP 1 tblSMS.*, tblCampaigns.Mail
> FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign =
> tblCampaigns.Campaign
> WHERE (Number = '+32479990284') OR (Number = '0479990284')
> ORDER BY SendDate DESC
> UNION
> SELECT DISTINCT tblSMS.*, tblCampaigns.Mail
> FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign =
> tblCampaigns.Campaign
> WHERE ((Number = '+32479990284') OR (Number = '0479990284')) AND
(SendDate
> Error:
> Server: Msg 156, Level 15, State 1, Line 5
> Incorrect syntax near the keyword 'UNION'.
> What I need to do is Select all the records (tblSMS) with SendDate from
the
> last 48 hours, and I have also to select the Last one (in case it isn't
yet
> in the last 48 hours).
> Does anybody knwos what goes wrong?
> If I try the two query's separetly they work fine...
> Thanks a lot,
> Pieter
>|||By the way, that will totally distroy your TOP 1 logic... but here's a very
simple solution:
SELECT *
FROM
(SELECT TOP 1 tblSMS.*, tblCampaigns.Mail
FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign =
tblCampaigns.Campaign
WHERE (Number = '+32479990284') OR (Number = '0479990284')
ORDER BY SendDate DESC) x
UNION
SELECT DISTINCT tblSMS.*, tblCampaigns.Mail
FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign =
tblCampaigns.Campaign
WHERE ((Number = '+32479990284') OR (Number = '0479990284'))
AND (SendDate > DATEADD(hh, 48 ,GETDATE() ))
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:u1zDVHzxEHA.4040@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Why does this query give this error?
> SELECT TOP 1 tblSMS.*, tblCampaigns.Mail
> FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign =
> tblCampaigns.Campaign
> WHERE (Number = '+32479990284') OR (Number = '0479990284')
> ORDER BY SendDate DESC
> UNION
> SELECT DISTINCT tblSMS.*, tblCampaigns.Mail
> FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign =
> tblCampaigns.Campaign
> WHERE ((Number = '+32479990284') OR (Number = '0479990284')) AND
(SendDate
> Error:
> Server: Msg 156, Level 15, State 1, Line 5
> Incorrect syntax near the keyword 'UNION'.
> What I need to do is Select all the records (tblSMS) with SendDate from
the
> last 48 hours, and I have also to select the Last one (in case it isn't
yet
> in the last 48 hours).
> Does anybody knwos what goes wrong?
> If I try the two query's separetly they work fine...
> Thanks a lot,
> Pieter
>|||You can't put ORDER BY inside individual parts of the UNION. Move it to the
end...
http://www.aspfaq.com/
(Reverse address to reply.)
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:u1zDVHzxEHA.4040@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Why does this query give this error?
> SELECT TOP 1 tblSMS.*, tblCampaigns.Mail
> FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign =
> tblCampaigns.Campaign
> WHERE (Number = '+32479990284') OR (Number = '0479990284')
> ORDER BY SendDate DESC
> UNION
> SELECT DISTINCT tblSMS.*, tblCampaigns.Mail
> FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign =
> tblCampaigns.Campaign
> WHERE ((Number = '+32479990284') OR (Number = '0479990284')) AND
(SendDate
> Error:
> Server: Msg 156, Level 15, State 1, Line 5
> Incorrect syntax near the keyword 'UNION'.
> What I need to do is Select all the records (tblSMS) with SendDate from
the
> last 48 hours, and I have also to select the Last one (in case it isn't
yet
> in the last 48 hours).
> Does anybody knwos what goes wrong?
> If I try the two query's separetly they work fine...
> Thanks a lot,
> Pieter
>|||Thansk guys!! It works fine now!
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uqB6AMzxEHA.2036@.TK2MSFTNGP12.phx.gbl...
> By the way, that will totally distroy your TOP 1 logic... but here's a
very
> simple solution:
> SELECT *
> FROM
> (SELECT TOP 1 tblSMS.*, tblCampaigns.Mail
> FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign =
> tblCampaigns.Campaign
> WHERE (Number = '+32479990284') OR (Number = '0479990284')
> ORDER BY SendDate DESC) x
> UNION
> SELECT DISTINCT tblSMS.*, tblCampaigns.Mail
> FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign =
> tblCampaigns.Campaign
> WHERE ((Number = '+32479990284') OR (Number = '0479990284'))
> AND (SendDate > DATEADD(hh, 48 ,GETDATE() ))
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> news:u1zDVHzxEHA.4040@.TK2MSFTNGP11.phx.gbl...
> (SendDate
> the
> yet
>sql

Incorrect syntax near the keyword 'UNION'. ??

Hi,
Why does this query give this error?
SELECT TOP 1 tblSMS.*, tblCampaigns.Mail
FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign = tblCampaigns.Campaign
WHERE (Number = '+32479990284') OR (Number = '0479990284')
ORDER BY SendDate DESC
UNION
SELECT DISTINCT tblSMS.*, tblCampaigns.Mail
FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign = tblCampaigns.Campaign
WHERE ((Number = '+32479990284') OR (Number = '0479990284')) AND (SendDate
> DATEADD(hh, 48 ,GETDATE() ))
Error:
Server: Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'UNION'.
What I need to do is Select all the records (tblSMS) with SendDate from the
last 48 hours, and I have also to select the Last one (in case it isn't yet
in the last 48 hours).
Does anybody knwos what goes wrong?
If I try the two query's separetly they work fine...
Thanks a lot,
PieterYou can't have an ORDER BY before the UNION. Put the ORDER BY after the
final SELECT.
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:u1zDVHzxEHA.4040@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Why does this query give this error?
> SELECT TOP 1 tblSMS.*, tblCampaigns.Mail
> FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign => tblCampaigns.Campaign
> WHERE (Number = '+32479990284') OR (Number = '0479990284')
> ORDER BY SendDate DESC
> UNION
> SELECT DISTINCT tblSMS.*, tblCampaigns.Mail
> FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign => tblCampaigns.Campaign
> WHERE ((Number = '+32479990284') OR (Number = '0479990284')) AND
(SendDate
> > DATEADD(hh, 48 ,GETDATE() ))
> Error:
> Server: Msg 156, Level 15, State 1, Line 5
> Incorrect syntax near the keyword 'UNION'.
> What I need to do is Select all the records (tblSMS) with SendDate from
the
> last 48 hours, and I have also to select the Last one (in case it isn't
yet
> in the last 48 hours).
> Does anybody knwos what goes wrong?
> If I try the two query's separetly they work fine...
> Thanks a lot,
> Pieter
>|||An Order By can only be used to determine the output of the entire result
set when you use
UNION. You can't use it with the individual queries.
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:u1zDVHzxEHA.4040@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Why does this query give this error?
> SELECT TOP 1 tblSMS.*, tblCampaigns.Mail
> FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign => tblCampaigns.Campaign
> WHERE (Number = '+32479990284') OR (Number = '0479990284')
> ORDER BY SendDate DESC
> UNION
> SELECT DISTINCT tblSMS.*, tblCampaigns.Mail
> FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign => tblCampaigns.Campaign
> WHERE ((Number = '+32479990284') OR (Number = '0479990284')) AND
(SendDate
> > DATEADD(hh, 48 ,GETDATE() ))
> Error:
> Server: Msg 156, Level 15, State 1, Line 5
> Incorrect syntax near the keyword 'UNION'.
> What I need to do is Select all the records (tblSMS) with SendDate from
the
> last 48 hours, and I have also to select the Last one (in case it isn't
yet
> in the last 48 hours).
> Does anybody knwos what goes wrong?
> If I try the two query's separetly they work fine...
> Thanks a lot,
> Pieter
>|||By the way, that will totally distroy your TOP 1 logic... but here's a very
simple solution:
SELECT *
FROM
(SELECT TOP 1 tblSMS.*, tblCampaigns.Mail
FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign = tblCampaigns.Campaign
WHERE (Number = '+32479990284') OR (Number = '0479990284')
ORDER BY SendDate DESC) x
UNION
SELECT DISTINCT tblSMS.*, tblCampaigns.Mail
FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign =tblCampaigns.Campaign
WHERE ((Number = '+32479990284') OR (Number = '0479990284'))
AND (SendDate > DATEADD(hh, 48 ,GETDATE() ))
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:u1zDVHzxEHA.4040@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Why does this query give this error?
> SELECT TOP 1 tblSMS.*, tblCampaigns.Mail
> FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign => tblCampaigns.Campaign
> WHERE (Number = '+32479990284') OR (Number = '0479990284')
> ORDER BY SendDate DESC
> UNION
> SELECT DISTINCT tblSMS.*, tblCampaigns.Mail
> FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign => tblCampaigns.Campaign
> WHERE ((Number = '+32479990284') OR (Number = '0479990284')) AND
(SendDate
> > DATEADD(hh, 48 ,GETDATE() ))
> Error:
> Server: Msg 156, Level 15, State 1, Line 5
> Incorrect syntax near the keyword 'UNION'.
> What I need to do is Select all the records (tblSMS) with SendDate from
the
> last 48 hours, and I have also to select the Last one (in case it isn't
yet
> in the last 48 hours).
> Does anybody knwos what goes wrong?
> If I try the two query's separetly they work fine...
> Thanks a lot,
> Pieter
>|||You can't put ORDER BY inside individual parts of the UNION. Move it to the
end...
--
http://www.aspfaq.com/
(Reverse address to reply.)
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:u1zDVHzxEHA.4040@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Why does this query give this error?
> SELECT TOP 1 tblSMS.*, tblCampaigns.Mail
> FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign => tblCampaigns.Campaign
> WHERE (Number = '+32479990284') OR (Number = '0479990284')
> ORDER BY SendDate DESC
> UNION
> SELECT DISTINCT tblSMS.*, tblCampaigns.Mail
> FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign => tblCampaigns.Campaign
> WHERE ((Number = '+32479990284') OR (Number = '0479990284')) AND
(SendDate
> > DATEADD(hh, 48 ,GETDATE() ))
> Error:
> Server: Msg 156, Level 15, State 1, Line 5
> Incorrect syntax near the keyword 'UNION'.
> What I need to do is Select all the records (tblSMS) with SendDate from
the
> last 48 hours, and I have also to select the Last one (in case it isn't
yet
> in the last 48 hours).
> Does anybody knwos what goes wrong?
> If I try the two query's separetly they work fine...
> Thanks a lot,
> Pieter
>|||Thansk guys!! It works fine now!
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uqB6AMzxEHA.2036@.TK2MSFTNGP12.phx.gbl...
> By the way, that will totally distroy your TOP 1 logic... but here's a
very
> simple solution:
> SELECT *
> FROM
> (SELECT TOP 1 tblSMS.*, tblCampaigns.Mail
> FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign => tblCampaigns.Campaign
> WHERE (Number = '+32479990284') OR (Number = '0479990284')
> ORDER BY SendDate DESC) x
> UNION
> SELECT DISTINCT tblSMS.*, tblCampaigns.Mail
> FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign => tblCampaigns.Campaign
> WHERE ((Number = '+32479990284') OR (Number = '0479990284'))
> AND (SendDate > DATEADD(hh, 48 ,GETDATE() ))
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> news:u1zDVHzxEHA.4040@.TK2MSFTNGP11.phx.gbl...
> > Hi,
> >
> > Why does this query give this error?
> > SELECT TOP 1 tblSMS.*, tblCampaigns.Mail
> > FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign => > tblCampaigns.Campaign
> > WHERE (Number = '+32479990284') OR (Number = '0479990284')
> > ORDER BY SendDate DESC
> > UNION
> > SELECT DISTINCT tblSMS.*, tblCampaigns.Mail
> > FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign => > tblCampaigns.Campaign
> > WHERE ((Number = '+32479990284') OR (Number = '0479990284')) AND
> (SendDate
> > > DATEADD(hh, 48 ,GETDATE() ))
> >
> > Error:
> > Server: Msg 156, Level 15, State 1, Line 5
> > Incorrect syntax near the keyword 'UNION'.
> >
> > What I need to do is Select all the records (tblSMS) with SendDate from
> the
> > last 48 hours, and I have also to select the Last one (in case it isn't
> yet
> > in the last 48 hours).
> >
> > Does anybody knwos what goes wrong?
> >
> > If I try the two query's separetly they work fine...
> >
> > Thanks a lot,
> >
> > Pieter
> >
> >
>

Incorrect syntax near the keyword 'THEN'.

Hi All,
I'm trying to put an if statement in my SP, which incidently is comparing
two dates, thats by the by, because I cant even get this simple if
statement
to run with out error:
IF (9 > 8) THEN
PRINT '9 is greater than 8'
END IF
Also tried:
IF (9 > 8)
PRINT '9 is greater than 8'
END IF
And:
IF (9 > 8)
PRINT '9 is greater than 8'
END
Help! Whats wrong here?
Simon.
---
I am using the free version of SPAMfighter for private users.
It has removed 2976 spam emails to date.
Paying users do not have this message in their emails.
Try www.SPAMfighter.com for free now!Forgot to add the error messages for the 2nd and 3rd attempts:

> IF (9 > 8)
> PRINT '9 is greater than 8'
> END IF
Incorrect syntax near the keyword 'END'.

> IF (9 > 8)
> PRINT '9 is greater than 8'
> END
Incorrect syntax near the keyword 'END'.|||do you have SQL Server Books Online? The help on IF is pretty clear -
it's not like VB [if you don't have it, you need to get it]
Simon Harris wrote:
> Hi All,
> I'm trying to put an if statement in my SP, which incidently is comparing
> two dates, thats by the by, because I cant even get this simple if
> statement
> to run with out error:
> IF (9 > 8) THEN
> PRINT '9 is greater than 8'
> END IF
> Also tried:
> IF (9 > 8)
> PRINT '9 is greater than 8'
> END IF
> And:
> IF (9 > 8)
> PRINT '9 is greater than 8'
> END
> Help! Whats wrong here?
> Simon.
>
> ---
> I am using the free version of SPAMfighter for private users.
> It has removed 2976 spam emails to date.
> Paying users do not have this message in their emails.
> Try www.SPAMfighter.com for free now!
>|||Thanks Trey, I do have SQL Books online (Forgot about it...Too used to
Googling!) after reading that it makes sense, and works.
Thank you!

Incorrect syntax near the keyword THEN

Hi Everyone,
I really tried to not post this question but I gave up. I tried brackets,
parenth...etc but nothing worked. I get this error message: Incorrect syntax
near the keyword 'THEN'. Please help, I am learning SQL Server.
thanks in advance.
Ismail

use mis
select CLAIM_DETAILS_HCVW.INTEREST, CLAIM_DETAILS_HCVW.NET, CLAIM_HMASTERS_VS.

CLAIMNO,
'AMOUNT' =
CASE WHEN (CLAIM_DETAILS_HCVW.INTEREST IS NULL THEN '0' ELSE
CLAIM_DETAILS_HCVW.INTEREST + CLAIM_DETAILS_HCVW.NET)
END,

FROM CLAIM_HMASTERS INNER JOIN CLAIM_HMASTERS ON CLAIM_HMASTERS_VS.CLAIMNO =
CLAIM_DETAILS_HCVW.CLAIMNO

where CLAIM_HMASTERS_VS.CLAIMNO like '200601119%'

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forum...eneral/200608/1Try putting the ELSE part out of the brackets
i.e
CASE
WHEN (CLAIM_DETAILS_HCVW.INTEREST IS NULL THEN '0' )
ELSE CLAIM_DETAILS_HCVW.INTEREST + CLAIM_DETAILS_HCVW.NET
END
--
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
Make SQL Server faster - www.quicksqlserver.com
___________________________________

"ielmrani via SQLMonster.com" <u21259@.uwewrote in message
news:65279dafaa057@.uwe...

Quote:

Originally Posted by

Hi Everyone,
I really tried to not post this question but I gave up. I tried brackets,
parenth...etc but nothing worked. I get this error message: Incorrect

syntax

Quote:

Originally Posted by

near the keyword 'THEN'. Please help, I am learning SQL Server.
thanks in advance.
Ismail
>
use mis
select CLAIM_DETAILS_HCVW.INTEREST, CLAIM_DETAILS_HCVW.NET,

CLAIM_HMASTERS_VS.

Quote:

Originally Posted by

>
CLAIMNO,
'AMOUNT' =
CASE WHEN (CLAIM_DETAILS_HCVW.INTEREST IS NULL THEN '0' ELSE
CLAIM_DETAILS_HCVW.INTEREST + CLAIM_DETAILS_HCVW.NET)
END,
>
FROM CLAIM_HMASTERS INNER JOIN CLAIM_HMASTERS ON CLAIM_HMASTERS_VS.CLAIMNO

=

Quote:

Originally Posted by

CLAIM_DETAILS_HCVW.CLAIMNO
>
where CLAIM_HMASTERS_VS.CLAIMNO like '200601119%'
>
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forum...eneral/200608/1
>
>

|||That's it. Thank you so much.
Jack Vamvas wrote:

Quote:

Originally Posted by

>Try putting the ELSE part out of the brackets
>i.e
>CASE
>WHEN (CLAIM_DETAILS_HCVW.INTEREST IS NULL THEN '0' )
>ELSE CLAIM_DETAILS_HCVW.INTEREST + CLAIM_DETAILS_HCVW.NET
>END
>--
>--
>Jack Vamvas
>___________________________________
>Receive free SQL tips - www.ciquery.com/sqlserver.htm
>Make SQL Server faster - www.quicksqlserver.com
>___________________________________
>

Quote:

Originally Posted by

>Hi Everyone,
>I really tried to not post this question but I gave up. I tried brackets,

>[quoted text clipped - 20 lines]

Quote:

Originally Posted by

>Message posted via SQLMonster.com
>http://www.sqlmonster.com/Uwe/Forum...eneral/200608/1

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forum...eneral/200608/1