SQL Syntax - Incorrect Syntas near '1'

14 Sep 2006 12:08 AM

Greg P.

I have created an insert statement by concatenation in VS2005 going to
SQLSwerver 2005. If i copy the string out of my watch window and run it as a
query on my Database it works fine. If I do an execute non-query on it I the
the above error.

I have adjusted the syntax by removing all of the variables I was using and
I still get the same error. I added constants (never using a #1) and still
get the same error. The code is as follows. I blow up on line ExecuteSQL =
cmdExecuteSQL.ExecuteNonQuery(). So the connection opens, the command is
assigned correctly so it must be the string right? Wrong the string works
fine in a Query in Sql Server 05. I have no idea what the problem is?

Dim strSql as string = ...
Dim strCnn As String = My.Settings.csStrategy.ToString
        Dim CnnStrat As SqlConnection = New SqlConnection(strCnn)
        Try
            CnnStrat.Open()
            Dim cmdExecuteSQL As SqlCommand = New SqlCommand(strSql, CnnStrat)
            cmdExecuteSQL.CommandText = CommandType.Text

            ExecuteSQL = cmdExecuteSQL.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox(ex.ToString)
        Finally
            CnnStrat.Close()
        End Try

Thanks,

14 Sep 2006 12:12 AM

Warren Brunk

It would help to see the Query or atleast how you are using the
ExecuteNonQuery...

--
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/

Show quote

"Greg P." <Gr***@discussions.microsoft.com> wrote in message
news:85560760-55C0-4F29-BD14-77763B79BC71@microsoft.com...
>I have created an insert statement by concatenation in VS2005 going to
> SQLSwerver 2005. If i copy the string out of my watch window and run it
> as a
> query on my Database it works fine. If I do an execute non-query on it I
> the
> the above error.
>
> I have adjusted the syntax by removing all of the variables I was using
> and
> I still get the same error. I added constants (never using a #1) and
> still
> get the same error. The code is as follows. I blow up on line ExecuteSQL
> =
> cmdExecuteSQL.ExecuteNonQuery(). So the connection opens, the command is
> assigned correctly so it must be the string right? Wrong the string works
> fine in a Query in Sql Server 05. I have no idea what the problem is?
>
> Dim strSql as string = ...
> Dim strCnn As String = My.Settings.csStrategy.ToString
>        Dim CnnStrat As SqlConnection = New SqlConnection(strCnn)
>        Try
>            CnnStrat.Open()
>            Dim cmdExecuteSQL As SqlCommand = New SqlCommand(strSql,
> CnnStrat)
>            cmdExecuteSQL.CommandText = CommandType.Text
>
>            ExecuteSQL = cmdExecuteSQL.ExecuteNonQuery()
>        Catch ex As Exception
>            MsgBox(ex.ToString)
>        Finally
>            CnnStrat.Close()
>        End Try
>
> Thanks,

14 Sep 2006 8:30 AM

Erland Sommarskog

Greg P. (Gr***@discussions.microsoft.com) writes:

> I have created an insert statement by concatenation in VS2005 going to
> SQLSwerver 2005. If i copy the string out of my watch window and run it
> as a query on my Database it works fine. If I do an execute non-query
> on it I the the above error.
>
> I have adjusted the syntax by removing all of the variables I was using
> and I still get the same error. I added constants (never using a #1)
> and still get the same error. The code is as follows. I blow up on
> line ExecuteSQL = cmdExecuteSQL.ExecuteNonQuery(). So the connection
> opens, the command is assigned correctly so it must be the string right?
> Wrong the string works fine in a Query in Sql Server 05. I have no
> idea what the problem is?

The main problem at this point is that you don't give as the SQL string.

I have a hinch that what it might be, but without seeing your query string,
I don't want to wander into speculations.

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

14 Sep 2006 10:47 AM

Stu

I blame gremlins.

Invisible code, invisible causes :)

Greg P. wrote:

Show quote

> I have created an insert statement by concatenation in VS2005 going to
> SQLSwerver 2005. If i copy the string out of my watch window and run it as a
> query on my Database it works fine. If I do an execute non-query on it I the
> the above error.
>
> I have adjusted the syntax by removing all of the variables I was using and
> I still get the same error. I added constants (never using a #1) and still
> get the same error. The code is as follows. I blow up on line ExecuteSQL =
> cmdExecuteSQL.ExecuteNonQuery(). So the connection opens, the command is
> assigned correctly so it must be the string right? Wrong the string works
> fine in a Query in Sql Server 05. I have no idea what the problem is?
>
> Dim strSql as string = ...
> Dim strCnn As String = My.Settings.csStrategy.ToString
>         Dim CnnStrat As SqlConnection = New SqlConnection(strCnn)
>         Try
>             CnnStrat.Open()
>             Dim cmdExecuteSQL As SqlCommand = New SqlCommand(strSql, CnnStrat)
>             cmdExecuteSQL.CommandText = CommandType.Text
>
>             ExecuteSQL = cmdExecuteSQL.ExecuteNonQuery()
>         Catch ex As Exception
>             MsgBox(ex.ToString)
>         Finally
>             CnnStrat.Close()
>         End Try
>
> Thanks,

14 Sep 2006 10:53 AM

ML

Nasty. But to the point. :)

ML

---
http://milambda.blogspot.com/

14 Sep 2006 2:30 PM

glen

sounds like a PEBKAC error

--
glen

Show quote

"Stu" wrote:

> I blame gremlins.
>
> Invisible code, invisible causes :)
>
> Greg P. wrote:
> > I have created an insert statement by concatenation in VS2005 going to
> > SQLSwerver 2005. If i copy the string out of my watch window and run it as a
> > query on my Database it works fine. If I do an execute non-query on it I the
> > the above error.
> >
> > I have adjusted the syntax by removing all of the variables I was using and
> > I still get the same error. I added constants (never using a #1) and still
> > get the same error. The code is as follows. I blow up on line ExecuteSQL =
> > cmdExecuteSQL.ExecuteNonQuery(). So the connection opens, the command is
> > assigned correctly so it must be the string right? Wrong the string works
> > fine in a Query in Sql Server 05. I have no idea what the problem is?
> >
> > Dim strSql as string = ...
> > Dim strCnn As String = My.Settings.csStrategy.ToString
> >         Dim CnnStrat As SqlConnection = New SqlConnection(strCnn)
> >         Try
> >             CnnStrat.Open()
> >             Dim cmdExecuteSQL As SqlCommand = New SqlCommand(strSql, CnnStrat)
> >             cmdExecuteSQL.CommandText = CommandType.Text
> >
> >             ExecuteSQL = cmdExecuteSQL.ExecuteNonQuery()
> >         Catch ex As Exception
> >             MsgBox(ex.ToString)
> >         Finally
> >             CnnStrat.Close()
> >         End Try
> >
> > Thanks,
>
>

14 Sep 2006 5:37 PM

Stu

When I used to work phone help desk, we'd call it an ID-Ten-T error.

If you don't get it, replace the word in the middle with the number.
Ahh, great fun :)

But seriously, if the OP is still reading this, post the code, and I'm
sure you'll get lots of suggestions to help.

Stu

glen wrote:

Show quote

> sounds like a PEBKAC error
> --
> glen
>
>
> "Stu" wrote:
>
> > I blame gremlins.
> >
> > Invisible code, invisible causes :)
> >
> > Greg P. wrote:
> > > I have created an insert statement by concatenation in VS2005 going to
> > > SQLSwerver 2005. If i copy the string out of my watch window and run it as a
> > > query on my Database it works fine. If I do an execute non-query on it I the
> > > the above error.
> > >
> > > I have adjusted the syntax by removing all of the variables I was using and
> > > I still get the same error. I added constants (never using a #1) and still
> > > get the same error. The code is as follows. I blow up on line ExecuteSQL =
> > > cmdExecuteSQL.ExecuteNonQuery(). So the connection opens, the command is
> > > assigned correctly so it must be the string right? Wrong the string works
> > > fine in a Query in Sql Server 05. I have no idea what the problem is?
> > >
> > > Dim strSql as string = ...
> > > Dim strCnn As String = My.Settings.csStrategy.ToString
> > >         Dim CnnStrat As SqlConnection = New SqlConnection(strCnn)
> > >         Try
> > >             CnnStrat.Open()
> > >             Dim cmdExecuteSQL As SqlCommand = New SqlCommand(strSql, CnnStrat)
> > >             cmdExecuteSQL.CommandText = CommandType.Text
> > >
> > >             ExecuteSQL = cmdExecuteSQL.ExecuteNonQuery()
> > >         Catch ex As Exception
> > >             MsgBox(ex.ToString)
> > >         Finally
> > >             CnnStrat.Close()
> > >         End Try
> > >
> > > Thanks,
> >
> >