Skip to content

SQL Update With Parameters Using a SQL Query or Stored Procedure

January 2, 2012
    '###########################################
    '      SQL Update VB Code
    '###########################################
    'With SQL Query
    Public Shared Function UpdateData(ByVal Parameter00 As String, ByVal Parameter01 As String) As Boolean
        UpdateData = True
        Try
            Using cmd As New SqlCommand("UPDATE tblData SET Parameter00 = @Parameter00 WHERE Parameter01 = @Parameter01", New SqlConnection(Conn))
                cmd.Parameters.AddWithValue("@Parameter00", Parameter00)
                cmd.Parameters.AddWithValue("@Parameter01", Parameter01)
                cmd.Connection.Open()
                cmd.ExecuteNonQuery()
                cmd.Connection.Dispose()
            End Using
            UpdateData = True
        Catch ex As Exception
            NotifyAdmin(String.Format("Computer: {0}</br>Application: {1}</br>Function/Sub: {2}</br>Error: {3}", Environment.MachineName, Application.ProductName, GetCurrentMethod.Name, ex))
        End Try
    End Function

    'With Stored Procedure
    Public Shared Function UpdateData(ByVal Parameter00 As String, ByVal Parameter01 As String) As Boolean
        UpdateData = True
        Try
            Using cmd As New SqlCommand("spUpdateData", New SqlConnection(Conn)) With {.CommandType = CommandType.StoredProcedure}
                cmd.Parameters.AddWithValue("@Parameter00", Parameter00)
                cmd.Parameters.AddWithValue("@Parameter01", Parameter01)
                cmd.Connection.Open()
                cmd.ExecuteNonQuery()
                cmd.Connection.Dispose()
            End Using
            UpdateData = True
        Catch ex As Exception
            NotifyAdmin(String.Format("Computer: {0}</br>Application: {1}</br>Function/Sub: {2}</br>Error: {3}", Environment.MachineName, Application.ProductName, GetCurrentMethod.Name, ex))
        End Try
    End Function
    

--###########################################
--      SQL Update SQL Code
--###########################################
CREATE PROCEDURE spUpdateData
    @Parameter00 VARCHAR(50) ,
    @Parameter01 VARCHAR(50)
AS 
    BEGIN            
        UPDATE  tblData
        SET     Parameter00 = @Parameter00
        WHERE   Parameter01 = @Parameter01
    END                               
GO
    --
Advertisements

From → .Net, SQL

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: