Incorrect syntax near Create Sproc

[=?Utf-8?B?QmlsIENsaWNr?=]

I wrote a stored procedure that runs fine when I execute from SQL 2005
Management Studio, and runs ok when I step into it from VS2005. Both return a
value of 0, drop, create & populate the table.

But when I run it inline in the vb.net code, I get an error:

+ ex {"Incorrect syntax near 'spCreateRank1' ."}
System.Data.Sql Client.SqlExcep tion

No Inner Exception or other useful information I can see.

*************** *************** *************** *********
-- start sproc
USE [dbname]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFI ER ON
GO
ALTER PROCEDURE [dbo].[spCreateRank1]
@outputTable varchar(32)
AS
BEGIN
SET NOCOUNT ON
-- Drop table if exists
IF OBJECT_ID(N'tbl Rank1', N'U') IS NOT NULL
DROP TABLE [dbo].[tblRank1]

-- Copy certain fields from all rows of output_ table into tblRank1 table
EXEC( 'SELECT firstname,lastn ame,city,[state],case_number,co untyname
INTO tblRank1 FROM output_' + @outputTable )

END --sproc

*************** *************** *************** ************
'vb.net code

Dim Conn2 As New SqlConnection(s trSqlConn)
Dim sqlCmd As SqlCommand = New SqlCommand("spC reateRank1", Conn2)

sqlCmd.CommandT ext = "spCreateRa nk1"

sqlCmd.Paramete rs.Add(New SqlParameter("@ outputTable", SqlDbType.VarCh ar,
32)).Value = strView

Dim iRows As Integer

If intRank = 1 Then

Try

If Conn2.State = ConnectionState .Closed Then Conn2.Open()

iRows = sqlCmd.ExecuteN onQuery

If Conn2.State = ConnectionState .Open Then Conn2.Close()

Catch ex As SqlException

DisplaySqlExcep tionInfo(ex)

End Try

*************** *************** *************** ************

Thanks for any advice!

[=?Utf-8?B?QmlsIENsaWNr?=]

RE: Incorrect syntax near Create Sproc

Oops - I forgot to set CommandType. Now it works.

sqlCmd.CommandT ype = CommandType.Sto redProcedure

"Bil Click" wrote:

I wrote a stored procedure that runs fine when I execute from SQL 2005
Management Studio, and runs ok when I step into it from VS2005. Both return a
value of 0, drop, create & populate the table.
>
But when I run it inline in the vb.net code, I get an error:
>
+ ex {"Incorrect syntax near 'spCreateRank1' ."}
System.Data.Sql Client.SqlExcep tion
>
No Inner Exception or other useful information I can see.
>
*************** *************** *************** *********
-- start sproc
USE [dbname]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFI ER ON
GO
ALTER PROCEDURE [dbo].[spCreateRank1]
@outputTable varchar(32)
AS
BEGIN
SET NOCOUNT ON
-- Drop table if exists
IF OBJECT_ID(N'tbl Rank1', N'U') IS NOT NULL
DROP TABLE [dbo].[tblRank1]
>
-- Copy certain fields from all rows of output_ table into tblRank1 table
EXEC( 'SELECT firstname,lastn ame,city,[state],case_number,co untyname
INTO tblRank1 FROM output_' + @outputTable )
>
END --sproc
>
*************** *************** *************** ************
'vb.net code
>
Dim Conn2 As New SqlConnection(s trSqlConn)
Dim sqlCmd As SqlCommand = New SqlCommand("spC reateRank1", Conn2)
>
sqlCmd.CommandT ext = "spCreateRa nk1"
>
sqlCmd.Paramete rs.Add(New SqlParameter("@ outputTable", SqlDbType.VarCh ar,
32)).Value = strView
>
Dim iRows As Integer
>
If intRank = 1 Then
>
Try
>
If Conn2.State = ConnectionState .Closed Then Conn2.Open()
>
iRows = sqlCmd.ExecuteN onQuery
>
If Conn2.State = ConnectionState .Open Then Conn2.Close()
>
Catch ex As SqlException
>
DisplaySqlExcep tionInfo(ex)
>
End Try
>
*************** *************** *************** ************
>
Thanks for any advice!
>
>