Incorrect syntax near Create Sproc

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.SqlClient.SqlException

No Inner Exception or other useful information I can see.

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

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

END --sproc

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

Dim Conn2 As New SqlConnection(strSqlConn)
Dim sqlCmd As SqlCommand = New SqlCommand("spCreateRank1", Conn2)

sqlCmd.CommandText = "spCreateRank1"

sqlCmd.Parameters.Add(New SqlParameter("@outputTable", SqlDbType.VarChar,
32)).Value = strView

Dim iRows As Integer

If intRank = 1 Then

Try

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

iRows = sqlCmd.ExecuteNonQuery

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

Catch ex As SqlException

DisplaySqlExceptionInfo(ex)

End Try

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

Thanks for any advice!
 
Oops - I forgot to set CommandType. Now it works.

sqlCmd.CommandType = CommandType.StoredProcedure
 
Back
Top