Using SET To Correct Integers In A Dbo.Table: Incorrect Sysntax Near The Keyword ‘SET’?
Jan 31, 2008
Hi all,
I have a wrong €œdbo.Samples€? table:
SampleID SampleName Matrix SampleType ChemGroup ProjectID
1 Blueriver01 Soil QA VOCs 1
7 Greentree01 Water Primary VOCs 1
8 Greentree02 Water Duplicate VOCs 1
9 Greentree03 Water QA VOCs 2
10 Greentree11 Soil Primary VOCs 1
11 Greentree11 Soil Duplicate VOCs 1
12 Greentree11 Soil QA VOCs 3
13 Redrock01 Water Primary VOCs 1
14 Redrock02 Water Duplicate VOCs 1
15 Redrock03 Water QA VOCs 2
16 Redrock11 Soil Primary VOCs 1
17 Redrock12 Soil Duplicate VOCs 1
18 Redrock13 Soil QA VOCs 3
I used the following sql code to correct the wrong ProjectIds:
USE ChemDatabase
GO
ALTER TABLE Samples
SET ProjectID = 4 WHERE SampleID = 7
SET ProjectID = 4 WHERE SampleID = 8
SET ProjectID = 5 WHERE SampleID = 9
SET ProjectID = 4 WHERE SampleID = 10
SET ProjectID = 4 WHERE SampleID = 11
SET ProjectID = 6 WHERE SampleID = 12
SET ProjectID = 7 WHERE SampleID = 13
SET ProjectID = 7 WHERE SampleID = 14
SET ProjectID = 8 WHERE SampleID = 15
SET ProjectID = 7 WHERE SampleID = 16
SET ProjectID = 7 WHERE SampleID = 17
SET ProjectID = 9 WHERE SampleID = 18
GO
I got the following error message:
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'SET'.
Please help and tell me what it is the right syntax for my €˜SET€™
used in this sql code. I think there are more mistakes in this set of sql code. Please enlighten me and advise me how to make this set of code right.
Thanks,
Scott Chang
View 4 Replies
ADVERTISEMENT
May 20, 2008
Why does the following call to a stored procedure get me this error:
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'CONVERT'.
Code Snippet
EXECUTE OpenInvoiceItemSP_RAM CONVERT(DATETIME,'01-01-2008'), CONVERT(DATETIME,'04/30/2008') , 1,'81350'
The stored procedure accepts two datetime parameters, followed by an INT and a varchar(10) in that order.
I can't find anything wrong in the syntax for CONVERT or any nearby items.
Help me please. Thank you.
View 7 Replies
View Related
Aug 3, 2006
I am having this error when using execute query for CTE
Help will be appriciated
View 9 Replies
View Related
May 22, 2008
What I am trying to create a query to check, If recDT is not value or null, then will use value from SELECT top 1 recDtim FROM Serv. Otherwise, will use the value from recDT. I have tried the below query but it doesn't work. The error says, Incorrect syntax near the keyword 'SELECT'.Incorrect syntax near the keyword 'else'.1 SELECT
2 case when recDT='' then SELECT top 1 recDtim FROM Serv else recDT end
3 FROM abc
4
Anyone can help? Thanks a lot.
View 5 Replies
View Related
May 7, 2007
Hi,I have written a stored procedure to add the records to the table in DB from the report I generate, but the sored procedure gives me this error:Incorrect syntax near the keyword 'ELSE'.I am using Sql Server 2005.SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[spCRMPublisherSummaryUpdate]( @ReportDate smalldatetime, @SiteID int, @DataFeedID int, @FromCode varchar, @Sent int, @Delivered int, @TotalOpens REAL, @UniqueUserOpens REAL, @UniqueUserMessageClicks REAL, @Unsubscribes REAL, @Bounces REAL, @UniqueUserLinkClicks REAL, @TotalLinkClicks REAL, @SpamComplaints int, @Cost int)ASDECLARE @PKID INTDECLARE @TagID INTSELECT @TagID=ID FROM Tag WHERE SiteID=@SiteID AND FromCode=@FromCode SELECT @PKID=PKID FROM DimTag WHERE TagID=@TagID AND StartDate<=@ReportDate AND @ReportDate< ISNULL(EndDate,'12/31/2050')IF @PKID IS NULL BEGIN SELECT TOP 1 @PKID=PKID FROM DimTag WHERE TagID=@TagID AND SiteID=@SiteIDENDDECLARE @LastReportDate smalldatetime, @LastSent INT, @LastDelivered INT, @LastTotalOpens Real, @LastUniqueUserOpens Real, @LastUniqueUserMessageClicks Real, @LastUniqueUserLinkClicks Real, @LastTotalLinkClicks Real, @LastUnsubscribes Real, @LastBounces Real, @LastSpamComplaints INT, @LastCost INT SELECT @Sent=@Sent-Sent,@Delivered=@Delivered-Delivered,@TotalOpens=@TotalOpens-TotalOpens,@UniqueUserOpens=@UniqueUserOpens-UniqueUserOpens,@UniqueUserMessageClicks=@UniqueUserMessageClicks-UniqueUserMessageClicks,@UniqueUserLinkClicks=@UniqueUserLinkClicks-UniqueUserLinkClicks,@TotalLinkClicks=@TotalLinkClicks-TotalLinkClicks,@Unsubscribes=@Unsubscribes-Unsubscribes,@Bounces=@Bounces-Bounces,@SpamComplaints=@SpamComplaints-SpamComplaints,@Cost=@Cost-Cost FROM CrmPublisherSummary WHERE @LastReportDate < @ReportDate AND SiteID=@SiteID AND TagPKID=@PKIDUPDATE CrmPublisherSummary SET Sent=@Sent, Delivered=@Delivered, TotalOpens=@TotalOpens, UniqueUserOpens=@UniqueUserOpens, UniqueUserMessageClicks=@UniqueUserMessageClicks, UniqueUserLinkClicks=@UniqueUserLinkClicks, TotalLinkClicks=@TotalLinkClicks, Unsubscribes=@Unsubscribes, Bounces=@Bounces, SpamComplaints=@SpamComplaints, Cost=@Cost WHERE ReportDate=@ReportDate AND SiteID=@SiteID AND TagPKID=@PKIDELSE SET NOCOUNT ON INSERT INTO CrmPublisherSummary( ReportDate, SiteID, TagPKID, Sent, Delivered, TotalOpens, UniqueUserOpens, UniqueUserMessageClicks, UniqueUserLinkClicks, TotalLinkClicks, Unsubscribes, Bounces, SpamComplaints, Cost, DataFeedID, TagID) SELECT @ReportDate, @SiteID, @PKID, @Sent, @Delivered, @TotalOpens, @UniqueUserOpens, @UniqueUserMessageClicks, @UniqueUserLinkClicks, @TotalLinkClicks, @Unsubscribes, @Bounces, @SpamComplaints, @Cost, @DataFeedID, @TagIDSET NOCOUNT OFF
View 5 Replies
View Related
May 16, 2007
Getting this error.. the page runs fine but it after entering the data it produces the following..
ERROR: Incorrect syntax near the keyword 'FROM'. with the following code...Please help!
<head runat="server"><title>Parts Lookup</title></head><body style="text-align: center"><form id="form1" runat="server"><div style="text-align: center"><br /><brpan style="font-size: 10pt; font-family: Tahoma">
Enter a Part Number</span>
<asp:TextBox ID="Productnbr" runat="server" Columns="4" Width="177px"></asp:TextBox><br /><asp:Button ID="DisplayPartNumberButton" runat="server" Text="Display Price, Description, Unit of Measure" Font-Names="Tahoma" /><br />
<br />
</div><asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="PartFilterDataSource" EnableViewState="False" Width="431px" CellPadding="4" ForeColor="#333333" GridLines="None" Font-Bold="False"><Columns><asp:BoundField DataField="PartNbr" HeaderText="Part Number" SortExpression="PartNbr" /><asp:BoundField DataField="Description" HeaderText="Description" SortExpression="Description" /><asp:BoundField DataField="Price" HeaderText="Price" SortExpression="Price" /><asp:BoundField DataField="UnitOfMeasure" HeaderText="Unit of Measure" SortExpression="UnitOfMeasure" /></Columns><FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" /><RowStyle BackColor="#F7F6F3" ForeColor="#333333" /><EditRowStyle BackColor="#999999" /><SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" /><PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" /><HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" /><AlternatingRowStyle BackColor="White" ForeColor="#284775" /></asp:GridView> <asp:SqlDataSource ID="PartFilterDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:ManManSQLConnectionString %>" SelectCommand="SELECT PartNbr, Description, UnitOfMeasure, Price; FROM Tbl_ODBC_PartsList; WHERE PartNbr = @Productnbr"><SelectParameters><asp:ControlParameter ControlID="Productnbr" Name="Productnbr" PropertyName="Text"/></SelectParameters></asp:SqlDataSource></form></body></html>
View 2 Replies
View Related
Apr 13, 2008
HiI am getting the following error on my Select statement: Incorrect syntax near the keyword 'AS'. SELECT [A], [B], [C], [D], [E], [F], [G], [H], [I], [J], [K], RowFROM(SELECT ROW_NUMBER() OVER (ORDER BY [J] DESC)AS Row, [A], [B], [C], [D], [E], [F], [G], [H], [I], [J], [K]FROM [TABLE]WHERE (([A] LIKE '%' + @A+ '%') OR ([K] LIKE '%' + @K+ '%')) AS LogWithRowNumbersWHERE (Row >=91 AND Row <= 100) I used the following select statement as a template (which works fine): SELECT [A], [B],
[C], [D], [E], [F],
[G], [H], [I], [J],
[K], [L], Row
FROM (SELECT ROW_NUMBER() OVER (ORDER
BY [H] DESC)
AS Row, [A], [B],
[C], [D], [E], [F],
[G], [H], [I], [J],
[K], [L] FROM [TABLE]
WHERE (([J] = @J) AND
([E] >= @E)) AND
(([K] < [L]) OR (([K] = 0) AND
([L] = 0)))) AS LogWithRowNumbers
WHERE (Row >= 82 AND Row <= 90) What is the difference that would make one work and the other not work?Thanks if you can help,Jon
View 2 Replies
View Related
Jun 6, 2008
Hi,I was just wondering if some kind soul on here could help me. I created a table in a database with called 'EuropeBroadcastList', with the following columns: Name, First Name, First, Last Name, Last, Title, Company, Photo.I have created a query whereby I want to create a new table with only specific columns called 'EuropeBroadcastSorted' and sort the names in 'EuropeBroadcastList' by the 'Last' column. The query is: CREATE TABLE europebroadcastsorted AS (SELECT [name], title, company, photo, [Last] FROM EuropeBroadcastList ORDER BY Last ASC); When I execute it I get the following syntax errors: Msg 156, Level 15, State 1, Line 2Incorrect syntax near the keyword 'AS'.Msg 156, Level 15, State 1, Line 4Incorrect syntax near the keyword 'ORDER'.Can any kind person help me resolve this?Thanks Rob
View 4 Replies
View Related
Feb 4, 2004
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!
View 3 Replies
View Related
Jan 17, 2005
Hi Guys,
This one is driving me nuts... I have been getting this error in my Asp.Net page. I run the profiler and then I can grab the actual query, and when I run this query in query analizer, it works just fine...
I have no clue at all about what's going wrong with my code or query...
cmd.CommandText = "exec mydatabase.dbo.mytable @select = 'Select convert(varchar(20),J.Datecreated,107)as Date, max(J.datecreated)As DateOrd from table1 J Left Join table2 C on J.CustID = C.CustID where C.Mode = 1 group by convert(varchar(20),J.DateCreated ,107) order by max(J.datecreated) DESC', @sumfunc = 'count(ID)', @pivot = 'Staff', @table = 'table1'"
-=========================
STORED Procedure
CREATE PROC myTable
(
@select varchar(8000),
@sumfunc varchar(100),
@pivot varchar(100),
@table varchar(100)
)
AS
DECLARE @sql varchar(8000), @delim varchar(1)
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
EXEC ('SELECT ' + @pivot + ' AS pivot INTO ##pivot FROM ' + @table + ' WHERE 1=2')
EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @pivot + ' FROM ' + @table + ' WHERE '
+ @pivot + ' Is Not Null')
SELECT @sql='', @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' END)' )
SELECT @delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) )
WHEN 0 THEN '' ELSE '' END
FROM tempdb.information_schema.columns
WHERE table_name='##pivot' AND column_name='pivot'
SELECT @sql=@sql + '''' + convert(varchar(100), pivot) + ''' = ' +
stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' + @pivot + ' WHEN '
+ @delim + convert(varchar(100), pivot) + @delim + ' THEN ' ) + ', ' FROM ##pivot
DROP TABLE ##pivot
SELECT @sql=left(@sql, len(@sql)-1)
SELECT @select=stuff(@select, charindex(' FROM ', @select)+1, 0, ', ' + @sql + ' ')
print @select
EXEC (@select)
SET ANSI_WARNINGS ON
SET NOCOUNT OFF
thanks in advance...
View 2 Replies
View Related
Aug 6, 2005
Hi
i want to copy tables from sql to accessi gave
SELECT Persons.* INTO Persons IN 'Backup.mdb'
FROM Persons The error is Incorrect syntax near the keyword 'in'any one reply me
View 1 Replies
View Related
Feb 14, 2006
Hi, I have Error when I write this SQL statement retrieving the non selected facilitator for a specific course:
The SQL statement:
SELECT facilitators.facilitator_id, facilitators.facilitator_name
FROM facilitators INNER JOIN
[transaction_ facilitators] ON facilitators.facilitator_id <> [transaction_ facilitators].trans_Facilitator_id
WHERE (NOT (facilitators.facilitator_id IN
(SELECT [transaction_ facilitators].trans_Facilitator_id
FROM [transaction_ facilitators]
WHERE [transaction_ facilitators].trans_Course_id = 2)))
GROUP BY facilitators.facilitator_id, facilitators.facilitator_name
ORDER BY facilitators.facilitator_id
Every thing goes right until I change this:
WHERE [transaction_ facilitators].trans_Course_id = 2
To
WHERE [transaction_ facilitators].trans_Course_id = @course_id
It gives me this error:
Incorrect syntax near the keyword "From"
Any suggestion ??
View 11 Replies
View Related
Sep 1, 2007
Hi..am getting this error "Incorrect syntax near the keyword 'inner'.
Can anyone please get me the correct query..
My Query is:
Code:
SELECT iCalls_Calls.CALL_ID,C.CALL_ID, iCalls_Calls.REQUESTOR, iCalls_Users.USER_NAME, DESCRIPTION, TYPE, SCOPE, SEVERITY, CAST(E.COUNT1/F.COUNT2 AS VarChar(5)), iCalls_Calls.STATUS_ID, iCalls_Status.STATUS_ID, iCalls_Status.STATUS_LABEL FROM iCalls_Calls C INNER JOIN(SELECT CALL_ID,COUNT(CALL_ID)AS COUNT1 FROM iCalls_Events GROUP BY CALL_ID) E ON C.CALL_ID=E.CALL_ID INNER JOIN(SELECT CALL_ID,COUNT(CALL_ID)AS COUNT2 FROM iCalls_Events WHERE EVENTS_FLAG <> 0 GROUP BY CALL_ID ) F ON C.CALL_ID=F.CALL_ID ((iCalls_Calls inner join iCalls_Status on iCalls_Calls.STATUS_ID=iCalls_Status.STATUS_ID) inner Join iCalls_Users on iCalls_Calls.REQUESTOR=iCalls_Users.USER_ID) left outer join iCalls_Messages on iCalls_Calls.CALL_ID=iCalls_Messages.CALL_ID WHERE REQUESTOR='" & Session("USER_ID") & "' AND iCalls_Calls.STATUS_ID <> 6 ORDER BY iCalls_Calls.CALL_ID
Thanks...
View 1 Replies
View Related
Oct 12, 2012
I keep getting erroe.. incorrect syntax near the keyword 'where'
"SELECT ISNULL(LastName, '') + SPACE(1) + ISNULL(FirstName, '') AS FullName,Grade,ClassID FROM studentsInfo,StudentClassDetails Where StudentsInfo.StudentID = StudentClassDetails.StudentID And FullName='" & aName & "' And where ClassID='" & clsID & "' group by fullname"
View 3 Replies
View Related
Sep 24, 2014
I'm trying to add a sub-query to my initial query, and I continue to receive the error Incorrect Syntax error. I've tried rearranging my join placements, but I continue to receive the error.
IF OBJECT_ID('TEMPDB..#TMP1C') IS NOT NULL DROP TABLE #TMP1C
select distinct T.PRIN_MON,
T.AGNT_MON,
T.SUB_ACCT_NO_MON,
s.RES_NAME_SBB,
hs.ADDR1_HSE,
hs.RES_CITY_HSE,
hs.RES_STATE_HSE,
[code].....
View 3 Replies
View Related
Aug 9, 2005
Hi Guys, I am new to SQL IF THEN Statements, I am having problems getting the code below to check out in my SQL Syntax checked.
I would really appreciate some assistance from anyone because I am sure that the answer is really simple.
CREATE PROCEDURE CheckCan2
@Position1 varchar(150),
@Position2 varchar(150),
@Position3 varchar(150),
@userid int
AS
BEGIN TRAN
If (@Position1 <> '') THEN
SELECT jobs.id, jobs.startdate, jobs.title, jobs.company, jobs.startdate, jobs.duration, jobs.salary, jobs.dateadded, jobs.city, jobs.country FROM jobs, details, seekers WHERE seekers.position=jobs.position AND seekers.userid=@userid
GO
ElseIf (@Position1 <> '') AND (@Position2 <> '') THEN
SELECT jobs.id, jobs.startdate, jobs.title, jobs.company, jobs.startdate, jobs.duration, jobs.salary, jobs.dateadded, jobs.city, jobs.country FROM jobs, details, seekers WHERE (details.discipline2=jobs.position AND details.userid=@userid) OR (seekers.position=jobs.position AND seekers.userid=@userid)
GO
ElseIf (@Position1 <> '') AND (@Position2 <> '') AND (@Position3 <> '') THEN
SELECT jobs.id, jobs.startdate, jobs.title, jobs.company, jobs.startdate, jobs.duration, jobs.salary, jobs.dateadded, jobs.city, jobs.country FROM jobs, details, seekers WHERE ((details.discipline2=jobs.position OR details.discipline3=jobs.position) AND details.userid=@userid) OR (seekers.position=jobs.position AND seekers.userid=@userid)
GO
END IF
END TRAN
View 3 Replies
View Related
Aug 22, 2006
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 syntaxnear the keyword 'THEN'. Please help, I am learning SQL Server.thanks in advance.Ismailuse misselect CLAIM_DETAILS_HCVW.INTEREST, CLAIM_DETAILS_HCVW.NET, CLAIM_HMASTERS_VS.CLAIMNO,'AMOUNT' =CASE WHEN (CLAIM_DETAILS_HCVW.INTEREST IS NULL THEN '0' ELSECLAIM_DETAILS_HCVW.INTEREST + CLAIM_DETAILS_HCVW.NET)END,FROM CLAIM_HMASTERS INNER JOIN CLAIM_HMASTERS ON CLAIM_HMASTERS_VS.CLAIMNO =CLAIM_DETAILS_HCVW.CLAIMNOwhere CLAIM_HMASTERS_VS.CLAIMNO like '200601119%'--Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forum...eneral/200608/1
View 2 Replies
View Related
Mar 10, 2007
Any Ideas as to this error message. I am trying to learn using ms sqlserver 7.0Below 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="../part3sol/style.css"></head><body bgcolor="#FFFFFF"><!--#include virtual="/adovbs.inc"--><center><%Dim oRSDim ConnDim IdDim NameDim StreetAddressDim CityDim StateDim ZipDim PhoneNumberdim sqlId = 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=AspRunnerProfessionalApplication;WSID=COMPAQAM;DATABASE=FriendsContactI nfo;Trusted_Connection=Yes")'Conn.Opensql="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=" & Idset 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 databaseConn.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 anotherrecord</button></form></center></body></html>
View 1 Replies
View Related
Aug 22, 2007
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
View 4 Replies
View Related
Sep 19, 2007
Hello, I have the following query. When I run the query I get the following error message: "Incorrect syntax near keyword SELECT"--------------------------------------------------- SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY DateAdded) AS rownum, * FROM SELECT TOP (100) PERCENT Videos.VideoId, Videos.UserId, Videos.UserName, Videos.Title, Videos.Description, Videos.Tags, Videos.VideoLength, Videos.TimesHeard, Videos.ImageURL, Videos.RecType, Videos.Language, Videos.Category, Videos.DateAdded, Videos.RewardProgram, Videos.EditorChoice, TB2.hitsFROM Videos LEFT OUTER JOIN (SELECT TOP (100) PERCENT VideoId, COUNT(*) AS hits FROM (SELECT TOP (100) PERCENT UserId, VideoId, COUNT(*) AS cnt1 FROM Hits GROUP BY VideoId, UserId) AS TB1 GROUP BY VideoId) AS TB2 ON Videos.VideoId = TB2.VideoIdORDER BY TB2.hits DESC) AS T1WHERE rownum <= 5----------------------------------------- If I run the query that is in BOLD as: SELECT *
FROM (SELECT ROW_NUMBER() OVER(ORDER BY DateAdded) AS rownum, * FROM Videos) AS T1 WHERE rownum <=5the query runs just fine. Also if I run the query that is NOT bold (above), it also runs fine. What can I do to run them both together as seen above? Thank in advance,Louis
View 4 Replies
View Related
Jan 16, 2008
Hi i have the following code in my sql database;CASE dbo.tblWorkSchedule.WorkScheduleType_ID WHEN 1 THENCASE IsNull(dbo.tblSurvey.WorkScheduleOverallStatus_ID,0) WHEN 4 THENdbo.tblWorkSchedule.UpliftedRate ELSE dbo.GetSWT_PropertyYearPeriodRate (IsNull(tblRateSchedule.WorkType_ID,0) ,tblWorkSchedule.WorkSchedule_ID , tblSurvey.PropertyYear_ID , tblSurvey.PropertyPeriod_ID ) END I want to add the following lines to it, however it gives me an error "Error 156: Incorrect syntax near the keyword "WHEN". What do i need to do, thank you. WHEN 3 THEN dbo.tblWorkSchedule.UpliftedRate ELSE dbo.GetSWT_PropertyYearPeriodRate (IsNull(tblRateSchedule.WorkType_ID,0) ,tblWorkSchedule.WorkSchedule_ID , tblSurvey.PropertyYear_ID , tblSurvey.PropertyPeriod_ID ) END
View 7 Replies
View Related
Feb 28, 2008
Hi,
I am building a website and database system for a university project, and am getting an error message when inserting data to a table (error message = "System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'WHERE'."). I would be most grateful if anybody could point out where I am going wrong! My code is;
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
public partial class Default2 : System.Web.UI.Page
{protected void Page_Load(object sender, EventArgs e)
{if ((Session["sUserName"]) == null)
{Response.Redirect("Default.aspx");
}
else
{Convert.ToString(Session["sUserName"]);
}
}protected void Register(object sender, EventArgs e)
{
//Create the ConnectionSqlConnection sqlConn =
new SqlConnection("server=Acer-Laptop\SQLEXPRESS; database=NeuCar; Trusted_Connection=true");
//Open the connection
sqlConn.Open();
//Create the Command, passing in the SQL statement and the ConnectionString queryString = "INSERT INTO Member (FirstName, LastName, Title, Email, AddressLine1, AddressLine2, TownOrCity, County, Postcode) VALUES(@myFirstName, @myLastName, @myTitle, @myEmail, @myAddressLine1, @myAddressLine2, @myTownOrCity, @myCounty, @myPostcode) WHERE Member (UserName = @myUserName); ";
SqlCommand cmd = new SqlCommand(queryString, sqlConn);cmd.Parameters.Add(new SqlParameter("@myUserName", Session["sUserName"]));
cmd.Parameters.Add(new SqlParameter("@myFirstName", FirstNameTextBox.Text));cmd.Parameters.Add(new SqlParameter("@myLastName", LastNameTextBox.Text));
cmd.Parameters.Add(new SqlParameter("@myTitle", TitleTextBox.Text));cmd.Parameters.Add(new SqlParameter("@myEmail", ConfirmEmailTextBox.Text));
cmd.Parameters.Add(new SqlParameter("@myAddressLine1", AddressLine1TextBox.Text));cmd.Parameters.Add(new SqlParameter("@myAddressLine2", AddressLine2TextBox.Text));
cmd.Parameters.Add(new SqlParameter("@myTownOrCity", TownOrCityTextBox.Text));cmd.Parameters.Add(new SqlParameter("@myCounty", CountyTextBox.Text));cmd.Parameters.Add(new SqlParameter("@myPostcode", PostcodeTextBox.Text));
cmd.ExecuteNonQuery();
Response.Redirect("Register.aspx");
//Close the connection
sqlConn.Close();
}
}
Also, is this line of code correct for passing a session variable into a parameter?;
cmd.Parameters.Add(new SqlParameter("@myUserName", Session["sUserName"]));
Many thanks for your time and help!
Chima
View 5 Replies
View Related
Apr 7, 2008
Hey, I'm having some errors when I load this code. I get the "Incorrect syntax near the keyword 'WHERE'." Error. 1 protected void Page_Load(object sender, EventArgs e)
2 {
3 SqlConnection conn;
4 SqlCommand comm;
5 SqlDataReader reader;
6 string connectionString =
7 ConfigurationManager.ConnectionStrings[
8 "ShippingSystemConnectionString1"].ConnectionString;
9 conn = new SqlConnection(connectionString);
10 comm = new SqlCommand(
11 "SELECT Header, Body, Footer FROM CMS," +
12 "WHERE CMSID = @CMSID", conn);
13 comm.Parameters.Add("@CMSID", System.Data.SqlDbType.Int);
14 comm.Parameters["@CMSID"].Value = "1";
15
16
17 conn.Open();
18 reader = comm.ExecuteReader();
19 if (reader.Read())
20 {
21 headerTxt.Text = reader["Header"].ToString();
22 bodyTxt.Text = reader["Body"].ToString();
23 footerTxt.Text = reader["Footer"].ToString();
24 }
25 reader.Close();
26
27
28 conn.Close();
29
30 }
31
Here is my stack trace:
[SqlException (0x80131904): Incorrect syntax near the keyword 'WHERE'.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +98 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +82 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +346 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +3430 System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +52 System.Data.SqlClient.SqlDataReader.get_MetaData() +130 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +371 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +1272 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +334 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +45 System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +162 System.Data.SqlClient.SqlCommand.ExecuteReader() +114 ASP.administration_cms_aspx.Page_Load(Object sender, EventArgs e) in c:SystemAdministrationCMS.aspx:34 System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +31 System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +68 System.Web.UI.Control.OnLoad(EventArgs e) +88 System.Web.UI.Control.LoadRecursive() +74 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3021 Cheers for any help
View 2 Replies
View Related
Jun 4, 2008
HiI've got a query which works fine in query analzyer but when I put it into my c# it shows the above eror messageCan you not use the following in c# sql string? " FROM table LEFT OUTER JOIN" +thanks! We are not a Code Charity
View 2 Replies
View Related
May 26, 2005
The above error message shows with the following T-SQL code of a dynamic query:
declare CheckColumn cursor for exec('select distinct ' + @myColumnName + ' from ' + @myTableName)The 2 varchar variables are properly declared. What's wrong syntactically?Thanks.
View 6 Replies
View Related
Apr 27, 2007
alter PROCEDURE Select_ShortInitials
(
@intRetVal int=null
)
as
begin
if (@intRetVal=1)
begin
begin Transaction
declare @PresentEmp as varchar(50)
declare @PEmp as varchar(50)
declare @strInsertStatment as varchar(8000)
set @strInsertStatment=''
declare InsertStatmentSupportedPhonesCur cursor
global
scroll
dynamic
optimistic
for
select name from smartjot.dbo.sysobjects where name like 'Employee_%' and name not like 'Employee_details%'
create table #tempShort(Short varchar(50))
open InsertStatmentSupportedPhonesCur
fetch first from InsertStatmentSupportedPhonesCur into @PresentEmp
while @@fetch_status = 0
begin
declare InnerCursor cursor
global
scroll
dynamic
optimistic
for
EXEC ('select distinct ShortInitial from ' + @PresentEmp)
--select distinct ShortInitial from @PresentEmp
open InnerCursor
fetch first from InnerCursor into @PEmp
while @@fetch_status = 0
begin
insert into #tempShort(Short) values(@PEmp)
fetch next from InnerCursor into @PEmp
end
close InnerCursor
deallocate InnerCursor
fetch next from InsertStatmentSupportedPhonesCur into @PresentEmp
end
close InsertStatmentSupportedPhonesCur
deallocate InsertStatmentSupportedPhonesCur
--Preparing Insert Statment for SupportedPhones Table - End
select * from #tempShort
if(@@error=0)
begin
commit transaction
end
else
begin
rollback transaction
end
end
end
View 1 Replies
View Related
Nov 12, 2014
I am getting error "Incorrect syntax near the keyword 'else'"
below is my code
declare @a varchar(15), @b Float(12) ,@c Float(12),@m varchar(15),@n Float(15),@av Float(15),@xav float(15)
SELECT @a=reverse(Substring(reverse(remarks),Charindex('tS',REVERSE(remarks))+2,4))
FROM [IVRS_MIS].[dbo].[logs] where app_name='IVFRT_POC' and remarks like '%answered%'
if @a = 1020
SELECT @m = '1022', @n = Count(@a), @av = Count(@a)/4.0 ,@b = substring(MAX(right(node_info, charindex('X',reverse(node_info))-8)),1,5) ,@c=substring(MIN(right(node_info, charindex('X',reverse(node_info))-8)),1,5),@xav=(@b+@c)/2.0
FROM [IVRS_MIS].[dbo].[logs]
[code].....
View 2 Replies
View Related
Jul 23, 2005
Dear Group,I am trying to create a view and keep getting the Incorrect syntax near thekeyword 'Declare'" error.Here is the code I am writing.Create view fixed_airs (sid, fad_a2, fad_a3) asDeclare @sid int,@fad_a2 int,@fad_a3 intselect @sid=cast(substring(subject_id,1,8)as int) ,@fad_a2 =cast (substring(fad_2_4,1,1) as int),@fad_a3=cast(substring(fad_2_4,2,1) as int)from parentpacket.Thanks for the help in advance.Jeff Magouirk
View 1 Replies
View Related
Dec 12, 2007
Hi ,
Can you tell me what is wrong with the following function. I'm getting an Incorrect syntax near the keyword 'RETURN'
FYI. I'm new in this and I'm starting to write functions in SQL server 2005.
ALTER FUNCTION [dbo].[f_sty_print_menu_per_role_per_app2]
(
-- Add the parameters for the function here
@menu_name VARCHAR(255) = NULL ,
@is_user VARCHAR(255) = NULL ,
@is_appl VARCHAR(255) = NULL
)
RETURNS VARCHAR
AS
BEGIN
-- Declare the return variable here
DECLARE @Rows NUMERIC(10)
DECLARE @RETURN_ENABLED VARCHAR(1)
-- Add the T-SQL statements to compute the return value here
SELECT @Rows = MAX(PROFILE_INDEX) FROM PROFILE_PERMISSION PP
INNER JOIN sys_menu_item ON PP.MENU_ITEM=sys_menu_item.menu_item
WHERE PP.PROFILE_INDEX in (select up.profile_index from user_profile up where up.user_id= @is_user) and
not exists (select up.profile_index from user_profile up where up.user_id= @is_user and up.profile_index=1) and
PP.APPLICATION_CODE = @is_appl AND
PP.MENU_NAME=@menu_name
Group By Profile_INdex
Exec (@Rows);
IF @Rows > 0
SELECT @RETURN_ENABLED = 'N'
ELSE
SELECT @RETURN_ENABLED = 'Y';
END
-- Return the result of the function
RETURN @RETURN_ENABLED;
END
Thank you
View 4 Replies
View Related
May 1, 2008
need help
Incorrect syntax near the keyword 'BEGIN'
what is the problem
Code Snippet
DECLARE @vempID varchar(500)
set @vempID = '68737477,51622017'
BEGIN
IF EXISTS (SELECT empID FROM table_2 WHERE charindex(','+CONVERT(varchar,[empID])+',',','+@vempID+',') > 0
BEGIN
DELETE FROM table_2
WHERE charindex(','+CONVERT(varchar,[empID])+',',','+@vempID+',') > 0
end
ELSE
BEGIN
INSERT INTO table_2
SELECT * FROM table_A
WHERE charindex(','+CONVERT(varchar,[empID])+',',','+@vempID+',') > 0
END
END
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'BEGIN'.
Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'ELSE'.
TNX
View 4 Replies
View Related
Oct 8, 2014
I'm struggling with one Syntax error
CREATE TABLE #ToolCompliance
(
SOFTWAR_ID INT
,CONTROL_CODE VARCHAR(100)
,CONTROL_STATUS VARCHAR(100)
)
INSERT INTO #ToolCompliance
VALUES(1000,'AC','SUCCESS')
[code]....
View 4 Replies
View Related
May 27, 2008
This is the error it gives me for my code and then it calls out line 102. Line 102 is my buildDD(sql, ddlPernames) When I comment out this line the error goes away, but what I don't get is this is the same way I build all of my dropdown boxes and they all work but this one. Could it not like something in my sql select statement. thanksPrivate Sub DDLUIC_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DDLUIC.SelectedIndexChanged
Dim taskforceID As Byte = ddlTaskForce.SelectedValueDim uic As String = DDLUIC.SelectedValue
sql = "select sidstrNAME_IND from CMS.dbo.tblSIDPERS where sidstrSSN_SM in (Select Case u.strSSN from tblAssignedPersonnel as u " _
& "where u.bitPresent = 1 and u.intUICID in (select intUICID from tblUIC where intTaskForceID = " & taskforceID & " and strUIC = '" & uic & "'))"ddlPerNames.Items.Add(New ListItem("", "0"))
buildDD(sql, ddlPerNames)
End Sub
View 2 Replies
View Related
Aug 4, 2015
I'm getting an error as "Incorrect syntax near the keyword 'CASE' ". When trying to run this query.
CREATE PROCEDURE dbo.SP_CDB_EA2
@RiskRef varchar(100)
--drop table #Tmp
AS
BEGIN
[code]....
View 9 Replies
View Related
