Error when calling stored procedure

I am trying to execute a store procedure from ASP/VB but it fails with the message:

Incorrect syntax near 'InitProject'.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Incorrect syntax near 'InitProject'.

Source Error:

Line 24: cmd.Parameters("@.ProjectId").Value = 3Line 25: cn.Open()Line 26: cmd.ExecuteNonQuery()Line 27: cn.Close()Line 28: End Sub

Here is my code:

'Execute the InitProject stored procedure
'Create the connection from the string in the web.config file
Dim cn As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("SMARTConnectionString").ConnectionString)
'I want to execute InitProject stored procedure
Dim cmd As SqlCommand = New SqlCommand("InitProject", cn)
'With the parameter @.ProjectId = 3
cmd.Parameters.Add(New SqlParameter("@.ProjectId", Data.SqlDbType.Int))
cmd.Parameters("@.ProjectId").Direction = Data.ParameterDirection.Input
cmd.Parameters("@.ProjectId").Value = 3
cn.Open()
'But this fails
cmd.ExecuteNonQuery()
cn.Close()

And my stored procedure is defined as:

[dbo].[InitProject] @.ProjectId int
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
insert into MATERIAL ( PROJECT_ID, SECTION_ID, CATEGORY_ID, ROOM_ID, ITEM_ID )
select @.ProjectId, SECTION_ID, CATEGORY_ID, ROOM_ID, ITEM_ID
from MATERIAL_TEMPLATE
END

The store procedure works fine when I do

exec InitProject 3

in sql query.

I have been away from .NET but try putting commandtype = storedproc. I dont remember the syntax but you could figure it out. It would go before adding the parameters.|||

Like this:

Dim cmd As SqlCommand = New SqlCommand("InitProject", cn)

cmd.CommandType = CommandType.StoredProcedure

.....

|||

Thank you very much for your help. It works like this:

'I want to execute InitProject stored procedure
Dim cmd As SqlCommand = New SqlCommand("InitProject", cn)
cmd.CommandType = Data.CommandType.StoredProcedure
'With the parameter @.ProjectId = 3
cmd.Parameters.Add(New SqlParameter("@.ProjectId", Data.SqlDbType.Int))
cmd.Parameters("@.ProjectId").Direction = Data.ParameterDirection.Input
cmd.Parameters("@.ProjectId").Value = 3
cn.Open()
'Now it works
cmd.ExecuteNonQuery()
cn.Close()

I don't understand why I need to prefix withData. when others don't need.

|||

Hi

Hierarchy : System.Data.CommandType.StoredProcedure

You just import namespace System so you need to prefix with Data

If you import namespace System.Data you don't need that.

Hope it helps.

Error traping in DTS

I am using a DTS for my purpose and I am calling that DTS execution in a stored procedure(using master..xp_cmdshell dtsrun/F).I like to store the error meassage or error description in the Stored Procedure if it fails due to any reason.How can I do that?call the dtsrun also with /L<log file path >|||Will you kindly give me a brief of that please?|||dtsrun gets some arguments the /L <file path > switch "tells" dtsrun where to log it executed steps results
for more info search books online for "dtsrun Utility" there you can find all info about dtsrun.