CODEWITHSUNDEEP
sqlvb.netstored-procedures
indofraiser
indofraiser

Reputation: 1024

Stored Procedure field variable

Aim: To change the field being updated based on an incoming value from the .aspx.vb code

Issue: I believe that the above Aim works, however I am getting an error on the PropertyID (which is alphanumeric) as it says Invalid column name 'S7753' in this case I was updating PropertyID S7753.

.aspx.vb code:

command.CommandText = "spActionUpdateOldestDate"
        command.CommandType = CommandType.StoredProcedure

        Dim vCheck As String = Session.Item("PropertyID").ToString & "-" & Session.Item("SafeGuardingDate").ToString & "-" & Session.Item("ActionsFieldName").ToString

        command.Parameters.AddWithValue("@PropertyID", Session.Item("PropertyID").ToString)
        command.Parameters.AddWithValue("@SafeGuardingDate", Session.Item("SafeGuardingDate").ToString)
        command.Parameters.AddWithValue("@ActionsFieldName", Session.Item("ActionsFieldName").ToString)

        command.ExecuteNonQuery()

        command.Parameters.Clear()

Stored Procedure

    USE [DB]

GO
/****** Object:  StoredProcedure [dbo].[spActionUpdateOldestDate]    Script Date: 04/02/2014 14:24:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spActionUpdateOldestDate] 

-- spActionUpdateOldestDate '1234','date','field'
    -- Add the parameters for the stored procedure here
            @PropertyID nvarchar(50)            
            ,@SafeGuardingDate nvarchar(MAX) 
             ,@ActionsFieldName varchar(MAX)

AS
BEGIN

-- add selection for courseID etc.. here

-- print 'UPDATE [TblActionsOldest] SET ' + @ActionsFieldName + ' = ''' + @SafeGuardingDate + ''' WHERE PropertyID = ''' + @PropertyID+ ''''
Execute ('UPDATE [TblActionsOldest] SET ' + @ActionsFieldName + ' = ''' + @SafeGuardingDate + ''' WHERE PropertyID = ''' + @PropertyID+ '''')

Upvotes: 0

Views: 80

Answers (2)

indofraiser
indofraiser

Reputation: 1024

Answer at the top, it was syntax for the PropertyID string.

Upvotes: 0

King of kings
King of kings

Reputation: 695

add this line before you add the parameters 

 SqlCommandBuilder.DeriveParameters(command)

Upvotes: 1

Related Questions