CODEWITHSUNDEEP
asp.netsqlvb.net
Crimius
Crimius

Reputation: 534

Addwithvalue causing syntax error

I have a stored procedure spSchedulerCheck that executes fine from SQL Server. It takes a single parameter, @jn. Trying to run this from VB/ASP.Net throws me a syntax error (which I'm catching).

VB Code:

Public Function ScheduleCheck()
    Dim sText As String
    Using cn As New System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("ProductionDatabaseConnectionString1").ConnectionString)
        cn.Open()
        Dim cmd As New System.Data.SqlClient.SqlCommand("SchedulerCheck", cn)
        cmd.Parameters.AddWithValue("@jn", Trim(Request.QueryString("jn").ToString()))
        Try
            Using reader As System.Data.SqlClient.SqlDataReader = cmd.ExecuteReader()
                If reader.Read() Then
                    sText = reader(0).ToString()
                End If
            End Using
        Catch ex As Exception
            Return ex.Message
        End Try

        If Not cn Is Nothing Then
            cn.Close()
        End If
    End Using
        Return sText
End Function

SP: 

USE [Production Database 2]
GO

/****** Object:  StoredProcedure [dbo].[SchedulerCheck]    Script Date: 12/11/2013 15:31:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[SchedulerCheck] 
    -- Add the parameters for the stored procedure here
    @jn as nvarchar(max)

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
declare @pn nvarchar(10);
set @pn = (select Proposalnumber from tblprj_management where jobnumber = @jn);

select ReturnCode = case
    when not exists (select * from tblprj_equipmentscope where jobnumber = @jn)
        then 'Could not find scope'
    when not exists (select * from tblprj_frcriteria where jobnumber = @jn and 'FR' in (select equipment from tblprj_equipmentscope where jobnumber = @jn)) 
        then 'Could not find FR Criteria entry'
    when not exists (select * from tblprj_wccriteria where jobnumber = @jn and 'WC' in (select equipment from tblprj_equipmentscope where jobnumber = @jn)) 
        then 'Could not find WC Criteria entry'
    when not exists (select * from tblprj_sctcriteria where jobnumber = @jn and 'SCT' in (select equipment from tblprj_equipmentscope where jobnumber = @jn)) 
        then 'Could not find SCT Criteria entry'
    when not exists (select * from tblprj_accriteria where jobnumber = @jn and 'AC' in (select equipment from tblprj_equipmentscope where jobnumber = @jn)) 
        then 'Could not find AC Criteria entry'
    when not exists (select * from tblprj_LFcriteria where jobnumber = @jn and 'LF' in (select equipment from tblprj_equipmentscope where jobnumber = @jn)) 
        then 'Could not find LF Criteria entry'
    when not exists (select * from tblprj_laborest where proposalnumber = @pn)
        and not exists (select * from tblprj_mfglaborest assy where proposalnumber = @pn)
        then 'Could not find labor estimates'
    else 'Success'
    end


END

GO

Seems to me like this should return one of the messages in the SP, not error out. In fact, running it from the SQL server indeed runs as expected. Some troubleshooting also shows that it runs as expected when I don't provide a Parameter, and only freaks out when I try to pass one. What am I doing that's causing the error?

EDIT: Error is Syntax Error near SchedulerCheck

Upvotes: 0

Views: 191

Answers (2)

Szymon
Szymon

Reputation: 43023

You need to set the CommandType of you command to be StoredProcedure. The default is Text.

cmd.CommandType = CommandType.StoredProcedure

Upvotes: 2

Steve
Steve

Reputation: 216313

When you call a stored procedure you need to change the property CommandType from its default of Text to StoredProcedure

  Dim cmd As New System.Data.SqlClient.SqlCommand("SchedulerCheck", cn)
  cmd.CommandType = CommandType.StoredProcedure

Otherwise the CommandText SchedulerCheck is interpreted as a normal sql text (like it was a SELECT/INSERT/UPDATE/DELETE). Of course this result in a Syntax Error.

The AddWithValue has nothing to do with the error message

Upvotes: 2

Related Questions