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

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.hits
FROM 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.VideoId
ORDER BY TB2.hits DESC
) AS T1
WHERE 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 <=5

the 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

Try this:

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.hits
FROM 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.VideoId
ORDER BY TB2.hits DESC

)
) AS T1
WHERE rownum <= 5

|||

Thanks Limno, but I had tried that before and I get this error:

Incorrect syntax near ')'

I don't understand why

Louis

|||

Here is a working one:

SELECT*FROM(SELECT ROW_NUMBER()OVER(ORDERBY DateAdded)AS rownum,*FROM(SELECTTOP(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.hits

FROM VideosLEFTOUTERJOIN

(SELECTTOP(100)PERCENT VideoId,COUNT(*)AS hits

FROM(SELECTTOP(100)PERCENT UserId, VideoId,COUNT(*)AS cnt1

FROM Hits

GROUPBY VideoId, UserId)AS TB1

GROUPBY VideoId)AS TB2ON Videos.VideoId= TB2.VideoId

ORDERBY TB2.hitsDESC

)AS T1) AS T2

WHERE rownum<= 5

|||

Thanks Limno, that worked

Louis

Incorrect Syntax near the keyword 'LEFT' in MSSQL6.5

I have an MSSQL6.5 server and when i run the following SQL statement, it
promt me an error message: Incorrect Syntax near the keyword 'LEFT'
SELECT LEFT(CUSTOMER_KEY, 1) FROM CUSTOMER
what happen to my SQL Server? Because i am sure my statement is correct.
if i run this sql on SQL 2000 or 7, no problem.Hi
Chekc that your column and table name is exaclty the same case as on the SQL
Server 6.5 DB. You might be running a case sensitive SQL 6.5 installation.
Regards
Mike
"yichun" wrote:
> I have an MSSQL6.5 server and when i run the following SQL statement, it
> promt me an error message: Incorrect Syntax near the keyword 'LEFT'
> SELECT LEFT(CUSTOMER_KEY, 1) FROM CUSTOMER
> what happen to my SQL Server? Because i am sure my statement is correct.
> if i run this sql on SQL 2000 or 7, no problem.
>|||Perhaps the LEFT function was introduced in 7.0? I don't have a Books Online to check against, but I
bet that you do. Check it out. If it isn't available in 6.5, use SUBSTRING instead.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"yichun" <yichun@.discussions.microsoft.com> wrote in message
news:349D4286-8C05-4E37-BDB3-00A4F4EEB077@.microsoft.com...
>I have an MSSQL6.5 server and when i run the following SQL statement, it
> promt me an error message: Incorrect Syntax near the keyword 'LEFT'
> SELECT LEFT(CUSTOMER_KEY, 1) FROM CUSTOMER
> what happen to my SQL Server? Because i am sure my statement is correct.
> if i run this sql on SQL 2000 or 7, no problem.
>

Incorrect Syntax near the keyword 'LEFT' in MSSQL6.5

I have an MSSQL6.5 server and when i run the following SQL statement, it
promt me an error message: Incorrect Syntax near the keyword 'LEFT'
SELECT LEFT(CUSTOMER_KEY, 1) FROM CUSTOMER
what happen to my SQL Server? Because i am sure my statement is correct.
if i run this sql on SQL 2000 or 7, no problem.
Hi
Chekc that your column and table name is exaclty the same case as on the SQL
Server 6.5 DB. You might be running a case sensitive SQL 6.5 installation.
Regards
Mike
"yichun" wrote:

> I have an MSSQL6.5 server and when i run the following SQL statement, it
> promt me an error message: Incorrect Syntax near the keyword 'LEFT'
> SELECT LEFT(CUSTOMER_KEY, 1) FROM CUSTOMER
> what happen to my SQL Server? Because i am sure my statement is correct.
> if i run this sql on SQL 2000 or 7, no problem.
>
|||Perhaps the LEFT function was introduced in 7.0? I don't have a Books Online to check against, but I
bet that you do. Check it out. If it isn't available in 6.5, use SUBSTRING instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"yichun" <yichun@.discussions.microsoft.com> wrote in message
news:349D4286-8C05-4E37-BDB3-00A4F4EEB077@.microsoft.com...
>I have an MSSQL6.5 server and when i run the following SQL statement, it
> promt me an error message: Incorrect Syntax near the keyword 'LEFT'
> SELECT LEFT(CUSTOMER_KEY, 1) FROM CUSTOMER
> what happen to my SQL Server? Because i am sure my statement is correct.
> if i run this sql on SQL 2000 or 7, no problem.
>

Incorrect Syntax near the keyword 'LEFT' in MSSQL6.5

I have an MSSQL6.5 server and when i run the following SQL statement, it
promt me an error message: Incorrect Syntax near the keyword 'LEFT'
SELECT LEFT(CUSTOMER_KEY, 1) FROM CUSTOMER
what happen to my SQL Server? Because i am sure my statement is correct.
if i run this sql on SQL 2000 or 7, no problem.Hi
Chekc that your column and table name is exaclty the same case as on the SQL
Server 6.5 DB. You might be running a case sensitive SQL 6.5 installation.
Regards
Mike
"yichun" wrote:

> I have an MSSQL6.5 server and when i run the following SQL statement, it
> promt me an error message: Incorrect Syntax near the keyword 'LEFT'
> SELECT LEFT(CUSTOMER_KEY, 1) FROM CUSTOMER
> what happen to my SQL Server? Because i am sure my statement is correct.
> if i run this sql on SQL 2000 or 7, no problem.
>|||Perhaps the LEFT function was introduced in 7.0? I don't have a Books Online
to check against, but I
bet that you do. Check it out. If it isn't available in 6.5, use SUBSTRING i
nstead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"yichun" <yichun@.discussions.microsoft.com> wrote in message
news:349D4286-8C05-4E37-BDB3-00A4F4EEB077@.microsoft.com...
>I have an MSSQL6.5 server and when i run the following SQL statement, it
> promt me an error message: Incorrect Syntax near the keyword 'LEFT'
> SELECT LEFT(CUSTOMER_KEY, 1) FROM CUSTOMER
> what happen to my SQL Server? Because i am sure my statement is correct.
> if i run this sql on SQL 2000 or 7, no problem.
>sql

Incorrect syntax near the keyword FROM.

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!

<headrunat="server">
<title>Parts Lookup</title>
</head>
<bodystyle="text-align: center">
<formid="form1"runat="server">
<divstyle="text-align: center">
<br/>
<brpanstyle="font-size: 10pt; font-family: Tahoma">

Enter a Part Number</span>

<asp:TextBoxID="Productnbr"runat="server"Columns="4"Width="177px"></asp:TextBox><br/>
<asp:ButtonID="DisplayPartNumberButton"runat="server"Text="Display Price, Description, Unit of Measure"Font-Names="Tahoma"/><br/>

<br/>

</div>
<asp:GridViewID="GridView1"runat="server"AutoGenerateColumns="False"DataSourceID="PartFilterDataSource"EnableViewState="False"Width="431px"CellPadding="4"ForeColor="#333333"GridLines="None"Font-Bold="False">
<Columns>
<asp:BoundFieldDataField="PartNbr"HeaderText="Part Number"SortExpression="PartNbr"/>
<asp:BoundFieldDataField="Description"HeaderText="Description"SortExpression="Description"/>
<asp:BoundFieldDataField="Price"HeaderText="Price"SortExpression="Price"/>
<asp:BoundFieldDataField="UnitOfMeasure"HeaderText="Unit of Measure"SortExpression="UnitOfMeasure"/>
</Columns>
<FooterStyleBackColor="#5D7B9D"Font-Bold="True"ForeColor="White"/>
<RowStyleBackColor="#F7F6F3"ForeColor="#333333"/>
<EditRowStyleBackColor="#999999"/>
<SelectedRowStyleBackColor="#E2DED6"Font-Bold="True"ForeColor="#333333"/>
<PagerStyleBackColor="#284775"ForeColor="White"HorizontalAlign="Center"/>
<HeaderStyleBackColor="#5D7B9D"Font-Bold="True"ForeColor="White"/>
<AlternatingRowStyleBackColor="White"ForeColor="#284775"/>
</asp:GridView>

<asp:SqlDataSourceID="PartFilterDataSource"runat="server"
ConnectionString="<%$ ConnectionStrings:ManManSQLConnectionString %>"
SelectCommand=
"SELECT PartNbr, Description, UnitOfMeasure, Price; FROM Tbl_ODBC_PartsList; WHERE PartNbr = @.Productnbr">
<SelectParameters>
<asp:ControlParameterControlID="Productnbr"Name="Productnbr"PropertyName="Text"/>
</SelectParameters>
</asp:SqlDataSource>
</form>
</body>
</html>

there is a semicolon after price. remove the semicolon

|||

Try this one if you have a column named as Productnbr:

SelectCommand="SELECT PartNbr, Description, UnitOfMeasure, Price FROM Tbl_ODBC_PartsList WHERE Productnbr = @.Productnbr">

<SelectParameters>
<asp:ControlParameterControlID="Productnbr"Name="Productnbr"PropertyName="Text"/>
</SelectParameters>

Or If you don't have a column named as Productnbr:

SelectCommand=
"SELECT PartNbr, Description, UnitOfMeasure, Price FROM Tbl_ODBC_PartsList WHERE PartNbr = @.PartNbr">
<SelectParameters>
<asp:ControlParameterControlID="Productnbr"Name="PartNbr"PropertyName="Text"/>
</SelectParameters>

sql

Incorrect syntax near the keyword 'Close'

When I created a SQL Server database by running a script, it gave me a
few errors like the following:
Incorrect syntax near the keyword 'KEY'.
Incorrect syntax near the keyword 'Close'.
Incorrect syntax near the keyword 'Open'.
Is this because those words (Key, CLose and Open) are reserved words ?
Thanks.We would definitely need to view the script in order to help you out here .
Could you post the script ?
"fniles" <fiefieniles@.yahoo.com> wrote in message
news:2067fd92.0409251452.60e065d7@.posting.google.com...
> When I created a SQL Server database by running a script, it gave me a
> few errors like the following:
> Incorrect syntax near the keyword 'KEY'.
> Incorrect syntax near the keyword 'Close'.
> Incorrect syntax near the keyword 'Open'.
> Is this because those words (Key, CLose and Open) are reserved words ?
> Thanks.|||Hi
Look at the subject "Reserved Keywords" in Books online all the words you
list are keywords. It is possible to use delimited identifiers if you want
to keep the keyword as an identifier see the topics
John
"Using Reserved Keywords" and "Delimited Identifiers" in Books online.
"fniles" <fiefieniles@.yahoo.com> wrote in message
news:2067fd92.0409251452.60e065d7@.posting.google.com...
> When I created a SQL Server database by running a script, it gave me a
> few errors like the following:
> Incorrect syntax near the keyword 'KEY'.
> Incorrect syntax near the keyword 'Close'.
> Incorrect syntax near the keyword 'Open'.
> Is this because those words (Key, CLose and Open) are reserved words ?
> Thanks.|||If you use keywords( documented in Books on line) as the names of ANY
objects in SQL you must brace them if
create table [OPEN]
([Key] int not null)
It is a good idea NOT to use reserve words if you can avoid it, because
you'll be forgetting to use the brackets and re-doing code over and
over(kind of annoying.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"fniles" <fiefieniles@.yahoo.com> wrote in message
news:2067fd92.0409251452.60e065d7@.posting.google.com...
> When I created a SQL Server database by running a script, it gave me a
> few errors like the following:
> Incorrect syntax near the keyword 'KEY'.
> Incorrect syntax near the keyword 'Close'.
> Incorrect syntax near the keyword 'Open'.
> Is this because those words (Key, CLose and Open) are reserved words ?
> Thanks.|||To add to Wayne's response, you can also SET QUOTED_IDENTIFIER ON and
enclose identifiers in double quotes. This alternative to square brackets
is the ANSI-standard method. The best practice is to avoid reserved words,
though.
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE "OPEN"
("Key" int NOT NULL)
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
"fniles" <fiefieniles@.yahoo.com> wrote in message
news:2067fd92.0409251452.60e065d7@.posting.google.com...
> When I created a SQL Server database by running a script, it gave me a
> few errors like the following:
> Incorrect syntax near the keyword 'KEY'.
> Incorrect syntax near the keyword 'Close'.
> Incorrect syntax near the keyword 'Open'.
> Is this because those words (Key, CLose and Open) are reserved words ?
> Thanks.|||CREATE TABLE tblA (
Price varchar(50) NULL,
close varchar(50) NULL,
group1 varchar(50) NULL,
Cost varchar(50) NULL
)
go
CREATE TABLE tblB (
Product varchar(50) NULL,
open datetime NULL,
close datetime NULL
)
go
CREATE TABLE tblC (
key varchar(50) NULL,
First_name varchar(50) NULL
)
go
When I replaced "Close" to "Close1", "Open" to "Open1" and "key" to
"key1", the error did not appear anymore.
Thanks.
"Hassan" <fatima_ja@.hotmail.com> wrote in message news:<uEm9gM1oEHA.1900@.TK2MSFTNGP10.phx.gbl>...
> We would definitely need to view the script in order to help you out here .
> Could you post the script ?
> "fniles" <fiefieniles@.yahoo.com> wrote in message
> news:2067fd92.0409251452.60e065d7@.posting.google.com...
> > When I created a SQL Server database by running a script, it gave me a
> > few errors like the following:
> >
> > Incorrect syntax near the keyword 'KEY'.
> > Incorrect syntax near the keyword 'Close'.
> > Incorrect syntax near the keyword 'Open'.
> >
> > Is this because those words (Key, CLose and Open) are reserved words ?
> >
> > Thanks.|||Thank you.
If I use square brackets or double quotes on the colum name, do I access
that column with the square brackets or double quotes also ?
For example:
create table tblA ( [open] varchar(50) )
When I want to select column [open], do I do the following sql statement:
select open from tblA
OR
select [open] from tblA ?
create table tblA ( "open" varchar(50) )
When I want to select column "open", do I do the following sql statement:
select "open" from tblA
OR
select "open" from tblA ?
Thank you very much.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OhJ6cs9oEHA.3728@.TK2MSFTNGP09.phx.gbl...
> To add to Wayne's response, you can also SET QUOTED_IDENTIFIER ON and
> enclose identifiers in double quotes. This alternative to square brackets
> is the ANSI-standard method. The best practice is to avoid reserved
words,
> though.
> SET QUOTED_IDENTIFIER ON
> GO
> CREATE TABLE "OPEN"
> ("Key" int NOT NULL)
> GO
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "fniles" <fiefieniles@.yahoo.com> wrote in message
> news:2067fd92.0409251452.60e065d7@.posting.google.com...
> > When I created a SQL Server database by running a script, it gave me a
> > few errors like the following:
> >
> > Incorrect syntax near the keyword 'KEY'.
> > Incorrect syntax near the keyword 'Close'.
> > Incorrect syntax near the keyword 'Open'.
> >
> > Is this because those words (Key, CLose and Open) are reserved words ?
> >
> > Thanks.
>|||Enclosures are required when you use a reserved word but it doesn't matter
whether you use square brackets or double quotes. You can mix both.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Fie Fie Niles" <fniles@.wincitesystems.com> wrote in message
news:eHXDZNDpEHA.3728@.TK2MSFTNGP09.phx.gbl...
> Thank you.
> If I use square brackets or double quotes on the colum name, do I access
> that column with the square brackets or double quotes also ?
> For example:
> create table tblA ( [open] varchar(50) )
> When I want to select column [open], do I do the following sql statement:
> select open from tblA
> OR
> select [open] from tblA ?
> create table tblA ( "open" varchar(50) )
> When I want to select column "open", do I do the following sql statement:
> select "open" from tblA
> OR
> select "open" from tblA ?
> Thank you very much.
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:OhJ6cs9oEHA.3728@.TK2MSFTNGP09.phx.gbl...
>> To add to Wayne's response, you can also SET QUOTED_IDENTIFIER ON and
>> enclose identifiers in double quotes. This alternative to square
>> brackets
>> is the ANSI-standard method. The best practice is to avoid reserved
> words,
>> though.
>> SET QUOTED_IDENTIFIER ON
>> GO
>> CREATE TABLE "OPEN"
>> ("Key" int NOT NULL)
>> GO
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "fniles" <fiefieniles@.yahoo.com> wrote in message
>> news:2067fd92.0409251452.60e065d7@.posting.google.com...
>> > When I created a SQL Server database by running a script, it gave me a
>> > few errors like the following:
>> >
>> > Incorrect syntax near the keyword 'KEY'.
>> > Incorrect syntax near the keyword 'Close'.
>> > Incorrect syntax near the keyword 'Open'.
>> >
>> > Is this because those words (Key, CLose and Open) are reserved words ?
>> >
>> > Thanks.
>>
>

Incorrect syntax near '5' where 5 is the beginning of a field name

Hi all
i get the following error
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect
syntax near
'5'. (#170)
when trying to update or delete a record from an Access Linked table. Adding
a record is fine. There is a field called 5Years, but this has not been a
problem before. Only occurred when database was moved to a new server.
SQL server version on current and old server is 2000 sp3a
jwIt's always been my understanding that fields can't start with certain
characters, like punctuation characters and numbers.
I'm surprised this ever worked. It may have been a bug that was fixed in
your recent release?
Perhaps someone else has a more knowledgeable response.sql

Incorrect syntax near '5' where 5 is the beginning of a field name

Hi all
i get the following error
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near
'5'. (#170)
when trying to update or delete a record from an Access Linked table. Adding
a record is fine. There is a field called 5Years, but this has not been a
problem before. Only occurred when database was moved to a new server.
SQL server version on current and old server is 2000 sp3a
jw
It's always been my understanding that fields can't start with certain
characters, like punctuation characters and numbers.
I'm surprised this ever worked. It may have been a bug that was fixed in
your recent release?
Perhaps someone else has a more knowledgeable response.

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 near '?' when trying to use parameters

I must be missing something simple. I have the following code that is not too complicated. I am trying to read a session variable (referenced in the <selectparameters> section) and use it to filter my SELECT statement. The select statement runs fine and displays everything in the gridview control until I put the "WHERE PackagingItemNo = ?" clause in. Then I get the error message in the title. I've tried using quotes, brackets, etc. to see if there's some syntax issue I'm missing here but I'm lost. I see numerous code examples that look identical to mine. What am I missing?

I'm mostly an Oracle and PL/SQL type so I'm a little lost here...

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" BorderColor="Black"

AllowPaging="true" DataKeyNames="InBoundID" BorderStyle="Solid" BorderWidth="1px"

Width="100%" AllowSorting="True" DataSourceID="SqlDataSource1" EmptyDataText="There are no data records to display.">

<HeaderStyle HorizontalAlign="Left" />

<Columns>

<asp:BoundField DataField="PackagingItemNo" HeaderText="Pack.Item#" SortExpression="PackagingItemNo" />

<asp:BoundField DataField="QuantityShipped" HeaderText="L" SortExpression="QuantityShipped" />

</Columns>

</asp:GridView>

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:MyDBConnectionString1 %>"

ProviderName="<%$ ConnectionStrings:MyDBConnectionString1.ProviderName %>"

SelectCommand="SELECT [InBoundID], [ShipID], [ShipItemID], [LocationID], [ArtisanShipNo], [ArrivalDate], [ArrivalTime], [ShipMode], [PackagingItemNo], [QuantityIn], [QuantityShipped], [QuantityClaimed], [ContainerType], [UnloadInvoicedYN], [CarrierName], [BillOfLading], [ShippingPointName], [ShippingPointState], [ReleaseNumber], [ProfileFlag], [TagFlag], [ActiveYN], [WHouseUserID], [UpdatedOn], [UpdateIs] FROM [WHouseInBound] WHERE PackagingItemNo = ?">

<SelectParameters>

<asp:SessionParameter Name="PackItemNo" SessionField="PackagingItemNo" DefaultValue="12345" />

</SelectParameters>

</asp:SqlDataSource>

Update, I changed WHERE PackagingItemNo = ?

to WHERE PackagingItemNo = @.PackItemNo

and now the DefaultValue value from the <SelectParemeters><SessionParameter> property is used in the query. However, the actual session value isn't used, just whatever the DefaultValue property is set to. Using ResponseWrite to display Session("PackItemNo") does display the session value as it was set by previous pages, however, so I'm not sure what I'm missing now.

|||

Hi,

I too faced the same problem while using the parameterized query in asp:SqlDataSource

I found the following solution

My Command was

SelectCommand="SELECT DISTINCT Suppliers.CompanyName FROM Suppliers INNER JOIN Products ON Products.SupplierID = Suppliers.SupplierID WHERE Products.CategoryID = @.category ORDER BY CompanyName;"

and my Parameters are like this.

<SelectParameters>

<asp:SessionParameter SessionField="category" Type=Int64 Name="category" DefaultValue=1 />

</SelectParameters>

Be sure to match your parameter names. It takes the default value 1 for the first time, then it changes according to the session variable value. Hope u have solved it too.

Incorrect syntax near '?'

Hello. When I run my application (it's used to place orders) in VB.net I get the following message: "Failed to complete order! Reason: Incorrect syntax near 'champnr'. Incorrect syntax near '?'. At System.Data.Sqlclient.Sqlcommand.ExecuteReader(CommandBehaviour cmdBehaviour, Runbehavior runbehavior, Boolean returnStream)
Here is a part of the code:
Me.NewOrder.CommandText = "INSERT INTO TTOrder (beskrivning, bestallarnr, bestdatum, bolagsnr, champnr, " & _
"costcenter, doknr, doktypnr, ordernr, projekt, timenr, ttkommentar, volvokomment" & _
"ar) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
Me.NewOrder.Connection = Me.sqlOrderDb
Me.NewOrder.Parameters.Add(New System.Data.SqlClient.SqlParameter("beskrivning", System.Data.SqlDbType.NVarChar, 0, "beskrivning"))
Me.NewOrder.Parameters.Add(New System.Data.SqlClient.SqlParameter("bestallarnr", System.Data.SqlDbType.Int, 0, "bestallarnr"))
Me.NewOrder.Parameters.Add(New System.Data.SqlClient.SqlParameter("bestdatum", System.Data.SqlDbType.DateTime, 0, "bestdatum"))
Me.NewOrder.Parameters.Add(New System.Data.SqlClient.SqlParameter("bolagsnr", System.Data.SqlDbType.Int, 0, "bolagsnr"))
Me.NewOrder.Parameters.Add(New System.Data.SqlClient.SqlParameter("champnr", System.Data.SqlDbType.NVarChar, 255, "champnr"))
Any ideas? I'm not using any stored procedures. Maybe I should?
Hi,

Me.NewOrder.CommandText = "INSERT INTO TTOrder (beskrivning, bestallarnr, bestdatum, bolagsnr, champnr, " & _
"costcenter, doknr, doktypnr, ordernr, projekt, timenr, ttkommentar, volvokomment" & _
"ar) VALUES (@.beskrivning, @.bestallarnr, @.bestdatum, @.bolagsnr, @.champnr, @.costcenter, @.doknr, @.doktypnr, @.ordernr, @.projekt, @.timenr, @.ttkommentar, @.volvokomment)

Me.NewOrder.Connection = Me.sqlOrderDb
Me.NewOrder.Parameters.Add("@.beskrivning", System.Data.SqlDbType.NVarChar)

and so on...

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Incorrect syntax near ''?''

Hello. When I run my application (it's used to place orders) in VB.net I get the following message: "Failed to complete order! Reason: Incorrect syntax near 'champnr'. Incorrect syntax near '?'. At System.Data.Sqlclient.Sqlcommand.ExecuteReader(CommandBehaviour cmdBehaviour, Runbehavior runbehavior, Boolean returnStream)
Here is a part of the code:
Me.NewOrder.CommandText = "INSERT INTO TTOrder (beskrivning, bestallarnr, bestdatum, bolagsnr, champnr, " & _
"costcenter, doknr, doktypnr, ordernr, projekt, timenr, ttkommentar, volvokomment" & _
"ar) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
Me.NewOrder.Connection = Me.sqlOrderDb
Me.NewOrder.Parameters.Add(New System.Data.SqlClient.SqlParameter("beskrivning", System.Data.SqlDbType.NVarChar, 0, "beskrivning"))
Me.NewOrder.Parameters.Add(New System.Data.SqlClient.SqlParameter("bestallarnr", System.Data.SqlDbType.Int, 0, "bestallarnr"))
Me.NewOrder.Parameters.Add(New System.Data.SqlClient.SqlParameter("bestdatum", System.Data.SqlDbType.DateTime, 0, "bestdatum"))
Me.NewOrder.Parameters.Add(New System.Data.SqlClient.SqlParameter("bolagsnr", System.Data.SqlDbType.Int, 0, "bolagsnr"))
Me.NewOrder.Parameters.Add(New System.Data.SqlClient.SqlParameter("champnr", System.Data.SqlDbType.NVarChar, 255, "champnr"))
Any ideas? I'm not using any stored procedures. Maybe I should?
Hi,

Me.NewOrder.CommandText = "INSERT INTO TTOrder (beskrivning, bestallarnr, bestdatum, bolagsnr, champnr, " & _
"costcenter, doknr, doktypnr, ordernr, projekt, timenr, ttkommentar, volvokomment" & _
"ar) VALUES (@.beskrivning, @.bestallarnr, @.bestdatum, @.bolagsnr, @.champnr, @.costcenter, @.doknr, @.doktypnr, @.ordernr, @.projekt, @.timenr, @.ttkommentar, @.volvokomment)

Me.NewOrder.Connection = Me.sqlOrderDb
Me.NewOrder.Parameters.Add("@.beskrivning", System.Data.SqlDbType.NVarChar)

and so on...

HTH, Jens Suessmeyer.

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

This post was very helpful to me, thanks

Incorrect syntax near '?' when trying to use parameters

I must be missing something simple. I have the following code that is not too complicated. I am trying to read a session variable (referenced in the <selectparameters> section) and use it to filter my SELECT statement. The select statement runs fine and displays everything in the gridview control until I put the "WHERE PackagingItemNo = ?" clause in. Then I get the error message in the title. I've tried using quotes, brackets, etc. to see if there's some syntax issue I'm missing here but I'm lost. I see numerous code examples that look identical to mine. What am I missing?

I'm mostly an Oracle and PL/SQL type so I'm a little lost here...

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" BorderColor="Black"

AllowPaging="true" DataKeyNames="InBoundID" BorderStyle="Solid" BorderWidth="1px"

Width="100%" AllowSorting="True" DataSourceID="SqlDataSource1" EmptyDataText="There are no data records to display.">

<HeaderStyle HorizontalAlign="Left" />

<Columns>

<asp:BoundField DataField="PackagingItemNo" HeaderText="Pack.Item#" SortExpression="PackagingItemNo" />

<asp:BoundField DataField="QuantityShipped" HeaderText="L" SortExpression="QuantityShipped" />

</Columns>

</asp:GridView>

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:MyDBConnectionString1 %>"

ProviderName="<%$ ConnectionStrings:MyDBConnectionString1.ProviderName %>"

SelectCommand="SELECT [InBoundID], [ShipID], [ShipItemID], [LocationID], [ArtisanShipNo], [ArrivalDate], [ArrivalTime], [ShipMode], [PackagingItemNo], [QuantityIn], [QuantityShipped], [QuantityClaimed], [ContainerType], [UnloadInvoicedYN], [CarrierName], [BillOfLading], [ShippingPointName], [ShippingPointState], [ReleaseNumber], [ProfileFlag], [TagFlag], [ActiveYN], [WHouseUserID], [UpdatedOn], [UpdateIs] FROM [WHouseInBound] WHERE PackagingItemNo = ?">

<SelectParameters>

<asp:SessionParameter Name="PackItemNo" SessionField="PackagingItemNo" DefaultValue="12345" />

</SelectParameters>

</asp:SqlDataSource>

Update, I changed WHERE PackagingItemNo = ?

to WHERE PackagingItemNo = @.PackItemNo

and now the DefaultValue value from the <SelectParemeters><SessionParameter> property is used in the query. However, the actual session value isn't used, just whatever the DefaultValue property is set to. Using ResponseWrite to display Session("PackItemNo") does display the session value as it was set by previous pages, however, so I'm not sure what I'm missing now.

|||

Hi,

I too faced the same problem while using the parameterized query in asp:SqlDataSource

I found the following solution

My Command was

SelectCommand="SELECT DISTINCT Suppliers.CompanyName FROM Suppliers INNER JOIN Products ON Products.SupplierID = Suppliers.SupplierID WHERE Products.CategoryID = @.category ORDER BY CompanyName;"

and my Parameters are like this.

<SelectParameters>

<asp:SessionParameter SessionField="category" Type=Int64 Name="category" DefaultValue=1 />

</SelectParameters>

Be sure to match your parameter names. It takes the default value 1 for the first time, then it changes according to the session variable value. Hope u have solved it too.

Incorrect syntax for backup

Hi All,
I have the following issue.
I have upgraded MS SQL Server 6.5 to 7.0.
I also have SQL Server 2000.
I have registered the SQL 7.0 server in the SQL 2000 server group.
When I run the following backup command in the query analyzer, that
runs ok with all the databases in SQL 2000 but not in the SQL 7.0.
Backup database abc to disk='\\NTDR1\DBBack\abc.dat' WITH INIT
I get the following error
Incorrect syntax near the keyword 'database'.
I have checked the permission and I am loggin as sa.
Could anyone please help me why I am unable to run the above command
for the database upgraded from SQL 6.5 to SQL 7.0, whereas the same
command works fine for SQL 2000.
I do appreciate your help.
Thanks a million in advance.
Best regards,
mamunRun this
Exec sp_dbcmptlevel 'DB NAME'
Does it say 65 ?
If it does change it to
Exec sp_dbcmptlevel 'DB NAME',70
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Mamun" <mamun_ah@.hotmail.com> wrote in message
news:6012e7ab.0310071109.53289600@.posting.google.com...
> Hi All,
> I have the following issue.
> I have upgraded MS SQL Server 6.5 to 7.0.
> I also have SQL Server 2000.
> I have registered the SQL 7.0 server in the SQL 2000 server group.
> When I run the following backup command in the query analyzer, that
> runs ok with all the databases in SQL 2000 but not in the SQL 7.0.
> Backup database abc to disk='\\NTDR1\DBBack\abc.dat' WITH INIT
> I get the following error
> Incorrect syntax near the keyword 'database'.
> I have checked the permission and I am loggin as sa.
>
> Could anyone please help me why I am unable to run the above command
> for the database upgraded from SQL 6.5 to SQL 7.0, whereas the same
> command works fine for SQL 2000.
>
> I do appreciate your help.
> Thanks a million in advance.
> Best regards,
> mamun|||"Mamun" <mamun_ah@.hotmail.com> wrote in message
news:6012e7ab.0310071109.53289600@.posting.google.com...
> I have the following issue.
> I have upgraded MS SQL Server 6.5 to 7.0.
> I also have SQL Server 2000.
> I have registered the SQL 7.0 server in the SQL 2000 server group.
> When I run the following backup command in the query analyzer, that
> runs ok with all the databases in SQL 2000 but not in the SQL 7.0.
> Backup database abc to disk='\\NTDR1\DBBack\abc.dat' WITH INIT
> I get the following error
> Incorrect syntax near the keyword 'database'.
> I have checked the permission and I am loggin as sa.
>
> Could anyone please help me why I am unable to run the above command
> for the database upgraded from SQL 6.5 to SQL 7.0, whereas the same
> command works fine for SQL 2000.
>
The MSSQLServer service must be running under the context of a service
account that has update permissions on your target UNC.
http://support.microsoft.com/default.aspx?scid=kb;en-us;207187&Product=sql2k
Steve
Steve

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 after server up for a few days

Dear All,

My VB.Net application connects to MSSQL. It is always running fine for a few days, but encounters "incorrect syntax" as following unless the server is restarted.

--
Exception occurred System.Runtime.InteropServices.COMException (0x80040E14): Line 1: incorrect syntax near 'CDO'.
at ADODB.ConnectionClass.Execute (String CommandText, Object& RecordsAffected, Int32 Options)
--

There are a few applications in the server. If certain service is stopped, my program continues to run. So I am sure that the MSSQL connections have been taken up, which causes the error. How to prove it? And is there any way to reserve some DB connections to a particular application only?

Thanks for any hint!

I attach my codes below. Anything wrong with the way that I handled the ADODB?

------------------
Public Sub SendAllEmails()
Try
Dim cn As ADODB.Connection = openConn()
Dim rs As ADODB._Recordset
Dim rs2 As ADODB._Recordset
sqlstmt = "select * FROM EMAILTABLE"
rs = cn.Execute(sqlstmt)

While Not rs.EOF

Dim MAIL_ADD_USED As String = rs.Fields("MAIL_ADD_USED").Value.ToString

sqlstmt = "select * from NAMETABLE where EMAIL = '" & MAIL_ADD & "'"
rs2 = cn.Execute(sqlstmt)

If Not rs2.EOF And ErrMsg = "" Then

ErrMsg = SendMail(MAIL_ADD, REPORT_TITLE)

If Not ErrMsg Is Nothing And ErrMsg.Equals("Success") Then
MAIL_DATE_SENT = Now.ToString
MAIL_STATUS = "S"

'wait to make sure the email is sent
System.Threading.Thread.Sleep(1000 * 30)
Else
MAIL_STATUS = "F"
End If

End If

rs2.Close()

sqlstmt = "update EMAILTABLE set " & _
" MAIL_STATUS = " & MAIL_STATUS & "," & _
" MAIL_ERRMSG = null" & _
" where EMAIL = " & MAIL_ADD

cn.Execute(sqlstmt)

rs.MoveNext()

End While

rs.Close()
cn.Close()

Catch e As Exception
EventLog1.WriteEntry("Exception: " & e.ToString)
End Try

End Sub
----------------------Dear All,

My VB.Net application connects to MSSQL. It is always running fine for a few days, but encounters "incorrect syntax" as following unless the server is restarted.

--

Are you using VB.Net? Then how come Recordset come into existence...:S
See the BOL to use VB.net (ADO.Net)|||Well I took over the codes from the programmer. vb.net is quite new to me. thanks for the hint but is there any sample about how to make use of ado.net?

Actually I am more keen to know whether the MSSQL connection can be released and allocated by DBA. There are othere applications that I simply have no control.

Thanks.|||Well I took over the codes from the programmer. vb.net is quite new to me. thanks for the hint but is there any sample about how to make use of ado.net?

Actually I am more keen to know whether the MSSQL connection can be released and allocated by DBA. There are othere applications that I simply have no control.

Thanks.

Check these...
sending mail (http://www.c-sharpcorner.com/UploadFile/sushmita_kumari/SendingMail101062006054220AM/SendingMail1.aspx?ArticleID=91ece6d8-eaaf-41ab-ac6f-533dc215eacf)

Ado.net stored proc use (http://aspalliance.com/673_CodeSnip_Calling_a_Stored_Procedure_from_ASPNE T_20)

All about Ado.net (http://aspalliance.com/articles/LearnADONET.aspx)

And you can always use Profiler to check the status of your server.And to kill process check this
Kill Process (http://msdn2.microsoft.com/en-us/library/ms173730.aspx)

Incorrect Syntax

Hello all,

Newbie here.
SQL 2000, Windows 2000

I'm trying to alter tables in my SQL DB using statements like the following:

/* AD_GROUPS */
alter table AD_GROUPS alter column AD_GROUP_NAME nvarchar(64)not null
go

/* ARTICLES */
alter table ARTICLES add column CONTENTTYPE_REF int null
go

I get error messages like:

Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'column'

I got the statements straight out of the Microsoft book "Inside Microsoft
SQL Server 2000"

Thanks in advance for helping to train this raw recruit!

JakeJust grop the "COLUMN" keyword from the ADD statement:

ALTER TABLE Articles ADD contenttype_ref INT NULL

Your ALTER COLUMN statement is correct. It's just a peculiarity of the
syntax that the word "COLUMN" isn't required after ADD.

--
David Portas
SQL Server MVP
--|||> Just grop the "COLUMN" keyword from the ADD statement:
> ALTER TABLE Articles ADD contenttype_ref INT NULL
> Your ALTER COLUMN statement is correct. It's just a peculiarity of the
> syntax that the word "COLUMN" isn't required after ADD.

And pardon the pun, but this missing part of the syntax won't be added
anytime soon, either. ;-)

--
http://www.aspfaq.com/
(Reverse address to reply.)|||> /* ARTICLES */
> alter table ARTICLES add column CONTENTTYPE_REF int null
> go

> I got the statements straight out of the Microsoft book "Inside Microsoft
> SQL Server 2000"

What page? I'd be interested to see a line like that, with the incorrect
column keyword where it is in your statement.

--
http://www.aspfaq.com/
(Reverse address to reply.)|||I'd be interested to see that also. :-)

Not that there are absolutely no mistakes in the book, but I just did a
search of the electronic version of the book, and did not find this error.
In fact, I found this note, basically warning about the word 'column' not
being used when adding a new column:

NOTE

-----------------------
--

Notice the syntax difference between dropping a column and adding a new
column: the word COLUMN is required when dropping a column, but not when
adding a new column to a table.

My guess is that Jake pulled the ALTER TABLE ALTER COLUMN syntax out of the
book, and then changed ALTER COLUMN to ADD COLUMN.

--
HTH
------
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com

"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OQBNC0cbEHA.3792@.TK2MSFTNGP09.phx.gbl...
> > /* ARTICLES */
> > alter table ARTICLES add column CONTENTTYPE_REF int null
> > go
> > I got the statements straight out of the Microsoft book "Inside
Microsoft
> > SQL Server 2000"
> What page? I'd be interested to see a line like that, with the incorrect
> column keyword where it is in your statement.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)|||> My guess is that Jake pulled the ALTER TABLE ALTER COLUMN syntax out of
the
> book, and then changed ALTER COLUMN to ADD COLUMN.

That was my guess too, but wanted to prod a bit more; maybe he found
something the rest of us missed. ;-)

A

Incorrect Syantax

Hi,
I am trying to use the following statement inmy trigger; but I am getting
error "incorrect syntax near ',' .
IF (@.CompanyOrderNo,@.Status NOT IN ( SELECT COMPANY_OrderNO,Status_ID from
OrderStatus))
How to correct this?
Thanks
pmudIF ( SELECT count(*) from OrderStatus
where COMPANY_OrderNO = @.CompanyOrderNo and Status_ID=@.Status ) = 0
"pmud" wrote:

> Hi,
> I am trying to use the following statement inmy trigger; but I am getting
> error "incorrect syntax near ',' .
>
> IF (@.CompanyOrderNo,@.Status NOT IN ( SELECT COMPANY_OrderNO,Status_ID fr
om
> OrderStatus))
> How to correct this?
> Thanks
> --
> pmud|||This works... Thanks!
--
pmud
"tthrone" wrote:
> IF ( SELECT count(*) from OrderStatus
> where COMPANY_OrderNO = @.CompanyOrderNo and Status_ID=@.Status ) = 0
> "pmud" wrote:
>