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 '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''

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 Items

Hi,

I am getting a mysterious error message, and it doesnt say which line it referres to, just gives me a stack trace.

Could somone decipher it for me?:

System.Data.SqlClient.SqlException: Incorrect syntax near 'items'.

[SqlException (0x80131904): Incorrect syntax near 'items'.]
 System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +180
 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) +2411
 System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) +190
 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +380
 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +115
 System.Web.UI.WebControls.SqlDataSourceView.ExecuteDbCommand(DbCommand command, DataSourceOperation operation) +395
 System.Web.UI.WebControls.SqlDataSourceView.ExecuteInsert(IDictionary values) +405
 System.Web.UI.WebControls.SqlDataSource.Insert() +13
 detailproview.Button2_Command(Object sender, CommandEventArgs e) +41
 System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +75
 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +155
 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +4886

My page code is:

private bool ExecuteUpdate(int quantity)
{
 SqlConnection con = new SqlConnection();
 con.ConnectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\ASPNETDB.MDF;Integrated Security=True;User Instance=True";
 con.Open();
 SqlCommand command = new SqlCommand();
 command.Connection = con;
 TextBox TextBox1 = (TextBox)FormView1.FindControl("TextBox1");
 Label labname = (Label)FormView1.FindControl("Label3");
 Label labid = (Label)FormView1.FindControl("Label13");
 command.CommandText = "UPDATE Items SET Quantityavailable = @.qty WHERE productID=@.productID";
 command.Parameters.Add("@.qty", TextBox1.Text);
 command.Parameters.Add("@.productID", labid.Text);
 command.ExecuteNonQuery();
 con.Close();
 return true;
}
 private bool ExecuteInsert(String quantity)
 {
 SqlConnection con = new SqlConnection();
 con.ConnectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\ASPNETDB.MDF;Integrated Security=True;User Instance=True";
 con.Open();
 SqlCommand command = new SqlCommand();
 command.Connection = con;
 TextBox TextBox1 = (TextBox)FormView1.FindControl("TextBox1");
 Label labname = (Label)FormView1.FindControl("Label3");
 Label labid = (Label)FormView1.FindControl("Label13");
 command.CommandText = "INSERT INTO Transactions (Usersname)VALUES (@.User)"+
 "INSERT INTO Transactions (Itemid)VALUES (@.productID)"+
 "INSERT INTO Transactions (itemname)VALUES (@.Itemsname)"+
 "INSERT INTO Transactions (Date)VALUES (+DateTime.Now.ToString() +)"+
 "INSERT INTO Transactions (Qty)VALUES (@.qty)"+
 command.Parameters.Add("@.User", System.Web.HttpContext.Current.User.Identity.Name);
 command.Parameters.Add("@.Itemsname", labname.Text);
 command.Parameters.Add("@.productID", labid.Text);
 command.Parameters.Add("@.qty", TextBox1.Text);
 command.ExecuteNonQuery();
 con.Close();
 return true;
 }
protected void Button2_Click(object sender, EventArgs e)
{
 TextBox TextBox1 = FormView1.FindControl("TextBox1") as TextBox;
 ExecuteUpdate(Int32.Parse(TextBox1.Text) );
}
protected void Button2_Command(object sender, CommandEventArgs e)
 {
 if (e.CommandName == "Update")
 {
 SqlDataSource1.Insert();
 }
 }
}.

Thanks!

Jon 

The error is referring to the SQL syntax you are using in your queries. There is a line in your stack trace that reads "System.Web.UI.WebControls.SqlDataSource.Insert() +13" which tends to suggest that the problem is in your INSERT statement, although looking at your INSERT statement I cannot see where "items" is mentioned. One bit that does confuse me is your Button2_Comand event-hander: why do you checke.CommandName == "Update" and then callSqlDataSource1.Insert() ?

Hope this helps

|||

Hi,

I cant see where items is mentioned either..

I checked update because the buttons command name is update, but I also want it to insert.. (it both updates and inserts).

Where else could the items error be happening??

Thanks,

Jon

|||

I have just noticed that you haveExecuteUpdateandExecuteInsertmethods defined in your code, but you are calling the Insert method of theSqlDataSource1control: what SQL statements have you got defined against SqlDataSource1?

|||

Well the data is on a formview which is attached so Sqdatasource1..

Perhaps I should try to called insert method of execute insert - how would I write this?:

if (e.CommandName == "Update")
{
ExecuteInsert.Insert();

?

Thanks!

Jon

|||You should just be able to write this:

if (e.CommandName =="Update") { ExecuteInsert();}

|||

Hi,

I tried that but got the error:

No overload for method 'ExecuteInsert' takes '0' arguments

Line 86: ExecuteInsert();

??

Thanks,

Jon 

|||

Ah, sorry, I didn't notice that yourExecuteInsertrequired a quantity parameter: just pass the method the quantity you require to be inserted.

Incorrect syntax near '@Sites'.

I have the following code but it keeps erroring on the last line and I'm unsure as to why it is doing it?

Here is the error message

Msg 102, Level 15, State 1, Line 42

Incorrect syntax near '@.Sites'.

declare @.Sites varchar(50)

declare @.Kit_No char(20)

declare @.Location char(2)

set @.Location = 'Ho'

set @.Kit_No = 'mo1k'

if (SELECT sitetype from gss.dbo.kup_regions where region_code = @.Location) = 10

begin

set @.Sites = '''Pe'',''Hg'',''Vo'',''' + @.Location + ''''

end

select

KR.Region_Code,

KR.Region_Name,

Z.Qty,

Z.Kit_Description,

Z.BookedOutToDate as Usage,

Z.Local_Cost,

(select overstock from gss.dbo.vGss_overstock where region_code = Z.region_code and kit_no = 'm01k' )as Rolling_Avg,

C.Symbol,

Z.FOB,

(SELECT

Price

FROM

gss.dbo.FedEx_Rates Fed

WHERE

SourceRegion = (SELECT Region_Code FROM gss.dbo.KUP_Regions WHERE Region_Name = 'penistone')

AND Weight = (SELECT MAX (Weight) FROM gss.dbo.FedEx_Rates WHERE Weight < (SELECT Packed_Unit_Weight From gss.dbo.KUP_Kits WHERE Kit_Code = (select Kit_Code from gss.dbo.GSS_Kits where Kit_No = 'm01k' ))+0.5)

AND Fed.DestRegion=KR.Region_Code) as Fedex_Price

from

(gss.dbo.kup_regions KR with (nolock)

left outer join

(select KRD.Qty,KRD.BookedOutToDate,KRD.Local_Cost,KRD.FOB,GK.Kit_Description,KRD.Region_code,KRD.Archive_Date

from gss.dbo.kup_region_data KRD with (nolock) inner join gss.dbo.gss_kits GK

on KRD.kit_code = GK.kit_code where GK.kit_no =@.Kit_No and KRD.archive_date is not null )Z

on KR.region_code = Z.region_code)

inner join gss.dbo.Currency C with (nolock) on C.Country_Code = KR.Country_Code

where KR.ExpectExtract = 1 and KR.Designation = 'p' and KR.Region_code in @.Sites

Many thanks for any help

The IN keyword requires a parenthesis, modify the last line as follows:

where KR.ExpectExtract = 1 and KR.Designation = 'p' and KR.Region_code in (@.Sites)

|||

Simon:

If @.sites is a simple 1-item variable then what carlop says is correct.

However, I assume that @.sites is a "string list" -- that is, it is a list of region codes such as @.Sites = 'rg1,rg2,rg3'. If that is the case there are a couple of solutions. A good read is at website:

http://www.sommarskog.se/arrays-in-sql.html

The "cheap" solution here is to convert your huge select statement into a string and then execute the converted string. This is in fact a simplified overview, but you will also need change this:

KR.Region_code in @.Sites

into this:

' ... KR.Region_code in ( ' + @.Sites + ') '

However, this solution might leave you vulnerable to SQL injection and this is not what I would do.

I would either create a function to list out your string list or I would just process your "string list" and load each separate entry into a table variable and change your IN portion into selecting the region codes out of the list.

(BLEAH my comments sound like a bunch of mumbo-jumbo. Let me find an example.)

Dave

|||

-- --
-- Use a "stringList" function to enumerate the parts of the "@.sites" string
-- --

declare @.sites varchar (20) set @.sites = 'N,A'

declare @.kup_regions table (Region_code char (1), Region_Name varchar (25))
insert into @.kup_regions values ('N', 'Northern Region')
insert into @.kup_regions values ('E', 'Eastern Region')
insert into @.kup_regions values ('W', 'West Region')
insert into @.kup_regions values ('S', 'South Region')
insert into @.kup_regions values ('A', 'A non-conforming Region')

-- An example using an inner join -
select a.region_code,
a.region_name
from @.kup_regions a
inner join stringList (',', @.sites) b
on b.entry = left (a.region_code,1)

-- An example using an "IN" clause
select a.region_code,
a.region_name
from @.kup_regions a
where a.region_code in
(select entry from stringList (',', @.sites) )

-- -
-- Sample Output
-- -

-- region_code region_name
-- -- -
-- N Northern Region
-- A A non-conforming Region

Incorrect Syntax error?

I am getting the following error when attempting to call a certain function:

"Incorrect Syntax near spListPrograms, Line 1"

Here is the sproc:

ALTER PROCEDUREspListPrograms

@.parentIDint= 0

AS

SELECTpwbsID, pwbsTitleFROMProgramWBSWHEREpwbsParent = @.parentID

It is being called from this function:

PublicSharedFunction ListProgramChildNodes(OptionalByVal parentIDAsInteger = 0)As SqlDataReader

Dim cmdAsNew SqlCommand("spListPrograms", strConn)

cmd.Parameters.AddWithValue("@.parentID", parentID)

Try

strConn.Open()

Return cmd.ExecuteReader(CommandBehavior.CloseConnection)

Catch eAs SqlException

Dim errorMessagesAsString =""

Dim iAsInteger

For i = 0To e.Errors.Count - 1

errorMessages +="Index #" & i.ToString() & ControlChars.NewLine _

&"Message: " & e.Errors(i).Message & ControlChars.NewLine _

&"LineNumber: " & e.Errors(i).LineNumber & ControlChars.NewLine _

&"Source: " & e.Errors(i).Source & ControlChars.NewLine _

&"Procedure: " & e.Errors(i).Procedure & ControlChars.NewLine

Next i

MsgBox(errorMessages)

Finally

strConn.Close()

EndTry

EndFunction

The sproc works just fine when I execute it from the database directly in SQl Express. What could cause an incorrect syntax error? There's hardly any syntax there for an error to occur Thanks for any help you can give me.

Try specifying that the commandtype is a stored procedure and see if that takes care of your problem:

cmd.CommandType = CommandType.StoredProcedure

|||Thanks, I did finally figure that out after about 3 hours of pulling my hair out. Must need sleep or something...

Incorrect syntax error

Greetings
I am receiving an incorrect syntax error on line 26 of this SQL. This is not the complete SQL. Any help is appreciated
thanks.
SELECT DISTINC
rtrim(t_Mfg_Order.Mfg_Order_Nbr) "Mfg_Order_Nbr"
t_Mfg_Order_Output.MMM_Id_Nbr "BOM_MMM_Id_Nbr"
t_Mfg_Order_BOM_Component_a.MMM_Id_Nbr "BOM_Component_MMM_Id_Nbr"
t_Mfg_Order_BOM_Component_a.Mfg_Order_Operation_Seq_Nbr
t_Mfg_Order_BOM_Component_a.BOM_Component_Seq_Nbr
MIN(upper (t_Item_Master_b.Inventory_UOM)) "Parent_Mfg_Invt_Unit"
MIN(t_Mfg_Order.BOM_Id) "BOM_Id"
MIN(t_Mfg_Order_Output.BOM_Qty) "BOM_Qty"
MIN( upper (t_Item_Master_a.Inventory_UOM)) "Component_Mfg_Invt_Unit"
planned_qty = MAX(qty
FROM (SELECT ISNULL((SELECT SUM(t_Mfg_Order_BOM_Component_b.Planned_Input_Qty
FROM t_Mfg_Order_BOM_Component t_Mfg_Order_BOM_Component_
WHER
t_Mfg_Order_BOM_Component_a.MMM_Facility_Code = t_Mfg_Order_BOM_Component_b.MMM_Facility_Code AN
t_Mfg_Order_BOM_Component_a.Mfg_Order_Nbr = t_Mfg_Order_BOM_Component_b.Mfg_Order_Nbr AN
t_Mfg_Order_BOM_Component_a.Mfg_Order_Operation_Seq_Nbr = t_Mfg_Order_BOM_Component_b.Mfg_Order_Operation_Seq_Nbr AN
t_Mfg_Order_BOM_Component_a.MMM_Id_Nbr = t_Mfg_Order_BOM_Component_b.MMM_Id_Nbr AN
((t_Mfg_Order_BOM_Component_a.BOM_Component_Seq_Nbr = t_Mfg_Order_BOM_Component_b.BOM_Component_Seq_Nbr) o
(t_Mfg_Order_BOM_Component_a.BOM_Component_Seq_Nbr = t_Mfg_Order_BOM_Component_b.Alt_For_Component_Seq_Nbr) o
(t_Mfg_Order_BOM_Component_a.Alt_For_Component_Seq_Nbr = t_Mfg_Order_BOM_Component_b.BOM_Component_Seq_Nbr) O
(t_Mfg_Order_BOM_Component_b.Alt_For_Component_Seq_Nbr = t_Mfg_Order_BOM_Component_a.Alt_For_Component_Seq_Nbr))),1
FROM t_Mfg_Order_BOM_Component t_Mfg_Order_BOM_Component_b) AS X(qty)
Load_Usage_qty = MIN(qty
FROM (SELECT ISNULL((SELECT SUM(ISNULL(t_Load_Usage_c.Load_Usage_Qty,0)
FROM t_Load_Usage t_Load_Usage_c
t_Load t_Load_
WHERE t_Mfg_Order_BOM_Component_a.MMM_Facility_Code = t_Load_Usage_c.MMM_Facility_Code AN
t_Mfg_Order_BOM_Component_a.Mfg_Order_Nbr = t_Load_Usage_c.Mfg_Order_Nbr AN
t_Mfg_Order_BOM_Component_a.Mfg_Order_Operation_Seq_Nbr = t_Load_Usage_c.Mfg_Order_Operation_Seq_Nbr AN
t_Mfg_Order_BOM_Component_a.MMM_Id_Nbr = t_Load_c.MMM_Id_Nbr AN
t_Load_c.Load_Id = t_Load_Usage_c.Load_Id),0
FROM t_Load_Usage t_Load_Usage_c, t_Load t_Load_c) AS Y(qty)
MIN(t_Mfg_Order_BOM_Component_a.Phase_In_Phase_Out_Code) "Phase_In_Phase_Out_Code"
MIN(t_Mfg_Order_BOM_Component_a.Component_Desc) "Component_Desc"
MIN(t_Mfg_Order_BOM_Component_a.Phase_In_Datetime) "Start_Eff_Date"
MIN(t_Mfg_Order_BOM_Component_a.Phase_Out_Datetime) "End_Eff_Date"
MIN(t_Mfg_Order_BOM_Component_a.Alternate_Priority_Nbr) "Alternate_Priority_Nbr"
MIN(t_Mfg_Order_BOM_Component_a.Alt_For_Component_Seq_Nbr) "Alt_For_Component_Seq_Nbr",It seems like instead of ... planned_qty = MAX(qty) FROM ...
you should have ... planned_qty = (SELECT MAX(qty) FROM ... )
Igor
"CraigG" <anonymous@.discussions.microsoft.com> wrote in message
news:99CE603F-EC7B-4060-A986-9C1A1E8F9617@.microsoft.com...
> Greetings;
> I am receiving an incorrect syntax error on line 26 of this SQL. This is
not the complete SQL. Any help is appreciated.
> thanks..
> SELECT DISTINCT
> rtrim(t_Mfg_Order.Mfg_Order_Nbr) "Mfg_Order_Nbr",
> t_Mfg_Order_Output.MMM_Id_Nbr "BOM_MMM_Id_Nbr",
> t_Mfg_Order_BOM_Component_a.MMM_Id_Nbr "BOM_Component_MMM_Id_Nbr",
> t_Mfg_Order_BOM_Component_a.Mfg_Order_Operation_Seq_Nbr,
> t_Mfg_Order_BOM_Component_a.BOM_Component_Seq_Nbr,
> MIN(upper (t_Item_Master_b.Inventory_UOM)) "Parent_Mfg_Invt_Unit",
> MIN(t_Mfg_Order.BOM_Id) "BOM_Id",
> MIN(t_Mfg_Order_Output.BOM_Qty) "BOM_Qty",
> MIN( upper (t_Item_Master_a.Inventory_UOM))
"Component_Mfg_Invt_Unit",
> planned_qty = MAX(qty)
> FROM (SELECT ISNULL((SELECT
SUM(t_Mfg_Order_BOM_Component_b.Planned_Input_Qty)
> FROM t_Mfg_Order_BOM_Component t_Mfg_Order_BOM_Component_b
> WHERE
> t_Mfg_Order_BOM_Component_a.MMM_Facility_Code =t_Mfg_Order_BOM_Component_b.MMM_Facility_Code AND
> t_Mfg_Order_BOM_Component_a.Mfg_Order_Nbr =t_Mfg_Order_BOM_Component_b.Mfg_Order_Nbr AND
> t_Mfg_Order_BOM_Component_a.Mfg_Order_Operation_Seq_Nbr
= t_Mfg_Order_BOM_Component_b.Mfg_Order_Operation_Seq_Nbr AND
> t_Mfg_Order_BOM_Component_a.MMM_Id_Nbr =t_Mfg_Order_BOM_Component_b.MMM_Id_Nbr AND
> ((t_Mfg_Order_BOM_Component_a.BOM_Component_Seq_Nbr =t_Mfg_Order_BOM_Component_b.BOM_Component_Seq_Nbr) or
> (t_Mfg_Order_BOM_Component_a.BOM_Component_Seq_Nbr =t_Mfg_Order_BOM_Component_b.Alt_For_Component_Seq_Nbr) or
> (t_Mfg_Order_BOM_Component_a.Alt_For_Component_Seq_Nbr =t_Mfg_Order_BOM_Component_b.BOM_Component_Seq_Nbr) OR
> (t_Mfg_Order_BOM_Component_b.Alt_For_Component_Seq_Nbr =t_Mfg_Order_BOM_Component_a.Alt_For_Component_Seq_Nbr))),1)
> FROM t_Mfg_Order_BOM_Component t_Mfg_Order_BOM_Component_b) AS
X(qty),
> Load_Usage_qty = MIN(qty)
> FROM (SELECT ISNULL((SELECT
SUM(ISNULL(t_Load_Usage_c.Load_Usage_Qty,0))
> FROM t_Load_Usage t_Load_Usage_c,
> t_Load t_Load_c
> WHERE t_Mfg_Order_BOM_Component_a.MMM_Facility_Code =t_Load_Usage_c.MMM_Facility_Code AND
> t_Mfg_Order_BOM_Component_a.Mfg_Order_Nbr =t_Load_Usage_c.Mfg_Order_Nbr AND
> t_Mfg_Order_BOM_Component_a.Mfg_Order_Operation_Seq_Nbr
= t_Load_Usage_c.Mfg_Order_Operation_Seq_Nbr AND
> t_Mfg_Order_BOM_Component_a.MMM_Id_Nbr =t_Load_c.MMM_Id_Nbr AND
> t_Load_c.Load_Id = t_Load_Usage_c.Load_Id),0)
> FROM t_Load_Usage t_Load_Usage_c, t_Load t_Load_c) AS Y(qty),
> MIN(t_Mfg_Order_BOM_Component_a.Phase_In_Phase_Out_Code)
"Phase_In_Phase_Out_Code",
> MIN(t_Mfg_Order_BOM_Component_a.Component_Desc) "Component_Desc",
> MIN(t_Mfg_Order_BOM_Component_a.Phase_In_Datetime)
"Start_Eff_Date",
> MIN(t_Mfg_Order_BOM_Component_a.Phase_Out_Datetime) "End_Eff_Date",
> MIN(t_Mfg_Order_BOM_Component_a.Alternate_Priority_Nbr)
"Alternate_Priority_Nbr",
> MIN(t_Mfg_Order_BOM_Component_a.Alt_For_Component_Seq_Nbr)
"Alt_For_Component_Seq_Nbr",sql

Incorrect syntax error

Greetings;
I am receiving an incorrect syntax error on line 26 of this SQL. This is no
t the complete SQL. Any help is appreciated.
thanks..
SELECT DISTINCT
rtrim(t_Mfg_Order.Mfg_Order_Nbr) "Mfg_Order_Nbr",
t_Mfg_Order_Output.MMM_Id_Nbr "BOM_MMM_Id_Nbr",
t_Mfg_Order_BOM_Component_a.MMM_Id_Nbr "BOM_Component_MMM_Id_Nbr",
t_Mfg_Order_BOM_Component_a.Mfg_Order_Operation_Seq_Nbr,
t_Mfg_Order_BOM_Component_a.BOM_Component_Seq_Nbr,
MIN(upper (t_Item_Master_b.Inventory_UOM)) "Parent_Mfg_Invt_Unit",
MIN(t_Mfg_Order.BOM_Id) "BOM_Id",
MIN(t_Mfg_Order_Output.BOM_Qty) "BOM_Qty",
MIN( upper (t_Item_Master_a.Inventory_UOM)) "Component_Mfg_Invt_Unit",
planned_qty = MAX(qty)
FROM (SELECT ISNULL((SELECT SUM(t_Mfg_Order_BOM_Component_b.Planned_Input_Qt
y)
FROM t_Mfg_Order_BOM_Component t_Mfg_Order_BOM_Component_b
WHERE
t_Mfg_Order_BOM_Component_a.MMM_Facility_Code = t_Mfg_Order_BOM_Component_b.
MMM_Facility_Code AND
t_Mfg_Order_BOM_Component_a.Mfg_Order_Nbr = t_Mfg_Order_BOM_Component_b.Mfg_
Order_Nbr AND
t_Mfg_Order_BOM_Component_a.Mfg_Order_Operation_Seq_Nbr = t_Mfg_Order_BOM_Co
mponent_b.Mfg_Order_Operation_Seq_Nbr AND
t_Mfg_Order_BOM_Component_a.MMM_Id_Nbr = t_Mfg_Order_BOM_Component_b.MMM_Id_
Nbr AND
((t_Mfg_Order_BOM_Component_a.BOM_Component_Seq_Nbr = t_Mfg_Order_BOM_Compon
ent_b.BOM_Component_Seq_Nbr) or
(t_Mfg_Order_BOM_Component_a.BOM_Component_Seq_Nbr = t_Mfg_Order_BOM_Compone
nt_b.Alt_For_Component_Seq_Nbr) or
(t_Mfg_Order_BOM_Component_a.Alt_For_Component_Seq_Nbr = t_Mfg_Order_BOM_Com
ponent_b.BOM_Component_Seq_Nbr) OR
(t_Mfg_Order_BOM_Component_b.Alt_For_Component_Seq_Nbr = t_Mfg_Order_BOM_Com
ponent_a.Alt_For_Component_Seq_Nbr))),1)
FROM t_Mfg_Order_BOM_Component t_Mfg_Order_BOM_Component_b) AS X(qty),
Load_Usage_qty = MIN(qty)
FROM (SELECT ISNULL((SELECT SUM(ISNULL(t_Load_Usage_c.Load_Usage_Qty,0))
FROM t_Load_Usage t_Load_Usage_c,
t_Load t_Load_c
WHERE t_Mfg_Order_BOM_Component_a.MMM_Facility_Code = t_Load_Usage_c.MMM_Fac
ility_Code AND
t_Mfg_Order_BOM_Component_a.Mfg_Order_Nbr = t_Load_Usage_c.Mfg_Order_Nbr AND
t_Mfg_Order_BOM_Component_a.Mfg_Order_Operation_Seq_Nbr = t_Load_Usage_c.Mfg
_Order_Operation_Seq_Nbr AND
t_Mfg_Order_BOM_Component_a.MMM_Id_Nbr = t_Load_c.MMM_Id_Nbr AND
t_Load_c.Load_Id = t_Load_Usage_c.Load_Id),0)
FROM t_Load_Usage t_Load_Usage_c, t_Load t_Load_c) AS Y(qty),
MIN(t_Mfg_Order_BOM_Component_a.Phase_In_Phase_Out_Code) "Phase_In_Phase_Out
_Code",
MIN(t_Mfg_Order_BOM_Component_a.Component_Desc) "Component_Desc",
MIN(t_Mfg_Order_BOM_Component_a.Phase_In_Datetime) "Start_Eff_Date",
MIN(t_Mfg_Order_BOM_Component_a.Phase_Out_Datetime) "End_Eff_Date",
MIN(t_Mfg_Order_BOM_Component_a.Alternate_Priority_Nbr) "Alternate_Priority_
Nbr",
MIN(t_Mfg_Order_BOM_Component_a.Alt_For_Component_Seq_Nbr) "Alt_For_Componen
t_Seq_Nbr",It seems like instead of ... planned_qty = MAX(qty) FROM ...
you should have ... planned_qty = (SELECT MAX(qty) FROM ... )
Igor
"CraigG" <anonymous@.discussions.microsoft.com> wrote in message
news:99CE603F-EC7B-4060-A986-9C1A1E8F9617@.microsoft.com...
> Greetings;
> I am receiving an incorrect syntax error on line 26 of this SQL. This is
not the complete SQL. Any help is appreciated.
> thanks..
> SELECT DISTINCT
> rtrim(t_Mfg_Order.Mfg_Order_Nbr) "Mfg_Order_Nbr",
> t_Mfg_Order_Output.MMM_Id_Nbr "BOM_MMM_Id_Nbr",
> t_Mfg_Order_BOM_Component_a.MMM_Id_Nbr "BOM_Component_MMM_Id_Nbr",
> t_Mfg_Order_BOM_Component_a.Mfg_Order_Operation_Seq_Nbr,
> t_Mfg_Order_BOM_Component_a.BOM_Component_Seq_Nbr,
> MIN(upper (t_Item_Master_b.Inventory_UOM)) "Parent_Mfg_Invt_Unit",
> MIN(t_Mfg_Order.BOM_Id) "BOM_Id",
> MIN(t_Mfg_Order_Output.BOM_Qty) "BOM_Qty",
> MIN( upper (t_Item_Master_a.Inventory_UOM))
"Component_Mfg_Invt_Unit",
> planned_qty = MAX(qty)
> FROM (SELECT ISNULL((SELECT
SUM(t_Mfg_Order_BOM_Component_b.Planned_Input_Qty)
> FROM t_Mfg_Order_BOM_Component t_Mfg_Order_BOM_Component_b
> WHERE
> t_Mfg_Order_BOM_Component_a.MMM_Facility_Code =
t_Mfg_Order_BOM_Component_b.MMM_Facility_Code AND
> t_Mfg_Order_BOM_Component_a.Mfg_Order_Nbr =
t_Mfg_Order_BOM_Component_b.Mfg_Order_Nbr AND
> t_Mfg_Order_BOM_Component_a.Mfg_Order_Operation_Seq_Nbr
= t_Mfg_Order_BOM_Component_b.Mfg_Order_Operation_Seq_Nbr AND
> t_Mfg_Order_BOM_Component_a.MMM_Id_Nbr =
t_Mfg_Order_BOM_Component_b.MMM_Id_Nbr AND
> ((t_Mfg_Order_BOM_Component_a.BOM_Component_Seq_Nbr =
t_Mfg_Order_BOM_Component_b.BOM_Component_Seq_Nbr) or
> (t_Mfg_Order_BOM_Component_a.BOM_Component_Seq_Nbr =
t_Mfg_Order_BOM_Component_b.Alt_For_Component_Seq_Nbr) or
> (t_Mfg_Order_BOM_Component_a.Alt_For_Component_Seq_Nbr =
t_Mfg_Order_BOM_Component_b.BOM_Component_Seq_Nbr) OR
> (t_Mfg_Order_BOM_Component_b.Alt_For_Component_Seq_Nbr =
t_Mfg_Order_BOM_Component_a. Alt_For_Component_Seq_Nbr))),1)rkred">
> FROM t_Mfg_Order_BOM_Component t_Mfg_Order_BOM_Component_b) AS
X(qty),
> Load_Usage_qty = MIN(qty)
> FROM (SELECT ISNULL((SELECT
SUM(ISNULL(t_Load_Usage_c.Load_Usage_Qty,0))
> FROM t_Load_Usage t_Load_Usage_c,
> t_Load t_Load_c
> WHERE t_Mfg_Order_BOM_Component_a.MMM_Facility_Code =
t_Load_Usage_c.MMM_Facility_Code AND
> t_Mfg_Order_BOM_Component_a.Mfg_Order_Nbr =
t_Load_Usage_c.Mfg_Order_Nbr AND
> t_Mfg_Order_BOM_Component_a.Mfg_Order_Operation_Seq_Nbr
= t_Load_Usage_c.Mfg_Order_Operation_Seq_Nbr AND
> t_Mfg_Order_BOM_Component_a.MMM_Id_Nbr =
t_Load_c.MMM_Id_Nbr AND
> t_Load_c.Load_Id = t_Load_Usage_c.Load_Id),0)
> FROM t_Load_Usage t_Load_Usage_c, t_Load t_Load_c) AS Y(qty),
> MIN(t_Mfg_Order_BOM_Component_a.Phase_In_Phase_Out_Code)
"Phase_In_Phase_Out_Code",
> MIN(t_Mfg_Order_BOM_Component_a.Component_Desc) "Component_Desc",
> MIN(t_Mfg_Order_BOM_Component_a.Phase_In_Datetime)
"Start_Eff_Date",
> MIN(t_Mfg_Order_BOM_Component_a.Phase_Out_Datetime) "End_Eff_Date",
> MIN(t_Mfg_Order_BOM_Component_a.Alternate_Priority_Nbr)
"Alternate_Priority_Nbr",
> MIN(t_Mfg_Order_BOM_Component_a.Alt_For_Component_Seq_Nbr)
"Alt_For_Component_Seq_Nbr",

Inconsistent stored procedure syntax error

We have a stored procedure that is being called from a vb6 application. The exact line of code is below.

mats_Reports_CSR_OB_Hourly_Percent('11/1/2006 4:51:27 PM','12/1/2006 4:51:27 PM')

when executing this in query analyzer it will always fail due to the parenthesis. However when this same line of code is passed to sql server in the vb6 application it usually works. There are times it fails with a syntax error message similar to:

Incorrect syntax near '{

I fixed the problem last time by removing the parenthesis in the vb6 code. However I can now put the parenthesis back into the code and it works again now.

Prior to my recent intervention the code was not being changed as it will work for days and then randomly stop working. Then without any change start working by itself again.

What is causing this behavior? How do I make it always work?

Calling a sql stored procedure is not like calling a vb function. Since you're calling a stored procedure, consider using Parameters collection to pass into desired inputs.

http://windowssdk.msdn.microsoft.com/en-gb/library/ms675869.aspx|||

Thank you for your response.

Your response seems to imply there is a bug in the way ODBC handles stored procedure calls?

This application was built over 3 years ago. It is not a realistic solution to switch connections from ODBC to ADO, as it would require the entire application to be recoded.

I have spent the better part of the day yesterday doing research on this problem and it seems it has something to do with ODBC escape sequences. What conditions would change how sql server parses a statement? There has to be something the server is automatically deciding on using that changes, because the same code works and then doesn't work (running against the same data).

|||No. What I hinted at is that you shouldn't call a proc like so:

myproc(para1,para2)

Instead you should do:

cmd.CommandText="myproc para1, para2"

or create a parameters collection for your parameters.|||I already know not using the parenthesis will work. I was looking for why it's happening.|||There is a simple answer: Because its the syntax of calling stored procedures that way. Did you ever ask yourself why a vb function is called like SomeFunction('Something') and not like SomeFunction*'Something'* :-) Its simply the syntax that is specified for calling the procedure.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

OK, your missing what I am getting at. I will make an analogy:

Say your using your keyboard, everytime you press the the 'k' key you'd expect the letter k every time you pressed it.

Now if sometimes when you pressed the 'k' key you got the letter q you'd pull the keyboard and replace it with one that works, or you'd send the keyboard in for repair.

Now what I am describing is the same way. If you call a function using the syntax I already described above you'd expect it to work every time you used it. What I am getting though is sometimes the same syntax is sucessfully running, other times it is giving a syntax error. I am asking why is this occuring? What can you think of that would change to cause this behavior?

|||Where is the error thrown ? Could it be that this is some SQL Server error (not actually in the frontend) which is caused by some malformed composed dynamic SQL in the backend, producing error messages like this ? Are you able to start profiler on the server to see what happends behind the scenes and what comamdns are fired against the database.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

include the report date

Best case, I include the report date in the report header. Worst case, in
the subject line of the subscription email. I'm using SQL/RS v2000, VS 2003,
anybody know how I can do this? nothing crazy, just like this:
ReportName xx/xx/xxxx
-- LynnI tried to add a text box into the header, just filling it w/this expression:
select LEFT(Dateadd(mm,-0,getdate()),11)
it fails indicating
'the value expression for the textbox 'textbox7' contains and error:
[BC30201] Expression expected.'
please advise
-- Lynn
"Lynn" wrote:
> Best case, I include the report date in the report header. Worst case, in
> the subject line of the subscription email. I'm using SQL/RS v2000, VS 2003,
> anybody know how I can do this? nothing crazy, just like this:
> ReportName xx/xx/xxxx
> -- Lynn|||Please disregard, I got it. I included a text box with this as the
expression, everything is fine now, thanks anyway:
=Format(today()," dd MMM yy" )
-- Lynn
"Lynn" wrote:
> I tried to add a text box into the header, just filling it w/this expression:
> select LEFT(Dateadd(mm,-0,getdate()),11)
> it fails indicating
> 'the value expression for the textbox 'textbox7' contains and error:
> [BC30201] Expression expected.'
> please advise
> -- Lynn
>
> "Lynn" wrote:
> > Best case, I include the report date in the report header. Worst case, in
> > the subject line of the subscription email. I'm using SQL/RS v2000, VS 2003,
> > anybody know how I can do this? nothing crazy, just like this:
> >
> > ReportName xx/xx/xxxx
> >
> > -- Lynn|||Just an FYI, you can use expression builder, look at the global variables.
One of those has what you want.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Lynn" <Lynn@.discussions.microsoft.com> wrote in message
news:DA80718F-E475-4891-93A5-EAEB1CB3D2D7@.microsoft.com...
> Best case, I include the report date in the report header. Worst case, in
> the subject line of the subscription email. I'm using SQL/RS v2000, VS
> 2003,
> anybody know how I can do this? nothing crazy, just like this:
> ReportName xx/xx/xxxx
> -- Lynn|||Will do, Bruce, thank you. I will check it out.
-- Lynn
oh, oh, just a slightly related question -- i've created my browser role and
i'm trying to send out this : http://webserver/reports
such that certain people can have direct access to a number of my reports
right now there's a 'Users Folders' in there along with my reports. I'm
unable to hide that. Do you know how to get rid of it?
"Bruce L-C [MVP]" wrote:
> Just an FYI, you can use expression builder, look at the global variables.
> One of those has what you want.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Lynn" <Lynn@.discussions.microsoft.com> wrote in message
> news:DA80718F-E475-4891-93A5-EAEB1CB3D2D7@.microsoft.com...
> > Best case, I include the report date in the report header. Worst case, in
> > the subject line of the subscription email. I'm using SQL/RS v2000, VS
> > 2003,
> > anybody know how I can do this? nothing crazy, just like this:
> >
> > ReportName xx/xx/xxxx
> >
> > -- Lynn
>
>|||Yes, you can remove the browser role from that particular folder. Normally
the folder inherits from the parent so all folders initially have the same
roles assigned to them. Click on the folder, properties, security and remove
the browser role. Or, if it is just that you don't want them distracted by
it, you could hide in list view (properties, general).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Lynn" <Lynn@.discussions.microsoft.com> wrote in message
news:CDBC9A73-7FD8-4271-9BCE-73D03A0BD3D1@.microsoft.com...
> Will do, Bruce, thank you. I will check it out.
> -- Lynn
> oh, oh, just a slightly related question -- i've created my browser role
> and
> i'm trying to send out this : http://webserver/reports
> such that certain people can have direct access to a number of my reports
> right now there's a 'Users Folders' in there along with my reports. I'm
> unable to hide that. Do you know how to get rid of it?
> "Bruce L-C [MVP]" wrote:
>> Just an FYI, you can use expression builder, look at the global
>> variables.
>> One of those has what you want.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Lynn" <Lynn@.discussions.microsoft.com> wrote in message
>> news:DA80718F-E475-4891-93A5-EAEB1CB3D2D7@.microsoft.com...
>> > Best case, I include the report date in the report header. Worst case,
>> > in
>> > the subject line of the subscription email. I'm using SQL/RS v2000, VS
>> > 2003,
>> > anybody know how I can do this? nothing crazy, just like this:
>> >
>> > ReportName xx/xx/xxxx
>> >
>> > -- Lynn
>>