Update based on SQ LDynamic Stored Proc Problem

[Ed Dror]

Hi there,
I'm using Visual Studio 2005 with SQL Server 2005 ASP.NET 2.0 VB

I have a Catalog table and I created a form with textboxs and Submit button
to call stored procedure to updated existing records
My stored procedure looks like this (Dynamic Stored Procedure)

USE [Catalog]
GO
/****** Object: StoredProcedure [dbo].[usp_UpdateCatalog_GN]
Script Date: 03/20/2008 08:03:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[usp_UpdateCatalog_GN]
@GN_ID int,
@Vendor_ID int = null,
@Brand nvarchar(50) = null,
@Model nvarchar(50) = null,
@Product_Description nvarchar(50) = null,
@Notes nvarchar(200) = null,
@Features nvarchar(200) = null,
@BldrRefNum nvarchar(50) = null,
@CrtdUser nvarchar(50)
AS
BEGIN
SET NOCOUNT ON
UPDATE [dbo].[Catalog_GN]
SET
Vendor_ID = COALESCE(@Vendor_ID, Vendor_ID),
Brand = COALESCE(@Brand, Brand),
Model = COALESCE(@Model, Model),
Product_Description = COALESCE(@Product_Description, Product_Description),
Notes = COALESCE(@Notes, Notes),
Features = COALESCE(@Features, Features),
BldrRefNum = COALESCE(@BldrRefNum, BldrRefNum),
CrtdUser = @CrtdUser
Where
[GN_ID] = @GN_ID
END

My UpdateCatalog.VB look like this

Public Function UpdateCatalogGN() As Integer
Dim con As New SqlConnection(conString)
Try
Dim insertString As String = "Execute usp_UpdateCatalog_GN '" &
txtGNID.Text & "','" & txtVendor.SelectedItem.Value & "','" & txtBrand.Text
& "','" & txtModel.Text & "','" & txtProduct.Text & "','" & txtNotes.Text &
"','" & txtFeature.Text & "','" & txtBldrRefNum.Text & "','" &
txtCrtdUser.Text & "'"
Dim cmd As New SqlCommand(insertString, con)
con.Open()
cmd.ExecuteNonQuery()
con.Close()
Catch ex As Exception
ErrorMessage.Text = ex.Message.ToString
End Try
End Function

My Submit Button look like this

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Button1.Click
Try
Call UpdateCatalogGN()
Response.Redirect("EditCatalogGN.aspx")
Catch ex As Exception
ErrorMessage.Text = ex.Message.ToString
End Try
End Sub

Now when I'm updating lets say Vendor and Notes only all the rest of the
records become empty!

My question is how to set the text box on load event to a null to avoid
inserting empty string to the recirds that I don't want to update

Instead of this (wont work - This is what asp.net page do)
EXEC [dbo].[usp_UpdateCatalog_GN] '39','3','Test 456','','','','','','Ed
Dror'

somthing like this (worked!)
EXEC [dbo].[usp_UpdateCatalog_GN] '39','3','Test
456',Null,Null,Null,Null,Null,'Ed Dror'

How to send a null value if the textbox is empty?

Thanks,
Ed Dror

[Lloyd Sheen]

"Ed Dror" <ed*@andrewlauren.comwrote in message
news:%2***************@TK2MSFTNGP05.phx.gbl...

Hi there,
I'm using Visual Studio 2005 with SQL Server 2005 ASP.NET 2.0 VB

I have a Catalog table and I created a form with textboxs and Submit
button to call stored procedure to updated existing records
My stored procedure looks like this (Dynamic Stored Procedure)

USE [Catalog]
GO
/****** Object: StoredProcedure [dbo].[usp_UpdateCatalog_GN]
Script Date: 03/20/2008 08:03:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[usp_UpdateCatalog_GN]
@GN_ID int,
@Vendor_ID int = null,
@Brand nvarchar(50) = null,
@Model nvarchar(50) = null,
@Product_Description nvarchar(50) = null,
@Notes nvarchar(200) = null,
@Features nvarchar(200) = null,
@BldrRefNum nvarchar(50) = null,
@CrtdUser nvarchar(50)
AS
BEGIN
SET NOCOUNT ON
UPDATE [dbo].[Catalog_GN]
SET
Vendor_ID = COALESCE(@Vendor_ID, Vendor_ID),
Brand = COALESCE(@Brand, Brand),
Model = COALESCE(@Model, Model),
Product_Description = COALESCE(@Product_Description, Product_Description),
Notes = COALESCE(@Notes, Notes),
Features = COALESCE(@Features, Features),
BldrRefNum = COALESCE(@BldrRefNum, BldrRefNum),
CrtdUser = @CrtdUser
Where
[GN_ID] = @GN_ID
END

My UpdateCatalog.VB look like this

Public Function UpdateCatalogGN() As Integer
Dim con As New SqlConnection(conString)
Try
Dim insertString As String = "Execute usp_UpdateCatalog_GN '" &
txtGNID.Text & "','" & txtVendor.SelectedItem.Value & "','" &
txtBrand.Text & "','" & txtModel.Text & "','" & txtProduct.Text & "','" &
txtNotes.Text & "','" & txtFeature.Text & "','" & txtBldrRefNum.Text &
"','" & txtCrtdUser.Text & "'"
Dim cmd As New SqlCommand(insertString, con)
con.Open()
cmd.ExecuteNonQuery()
con.Close()
Catch ex As Exception
ErrorMessage.Text = ex.Message.ToString
End Try
End Function

My Submit Button look like this

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Button1.Click
Try
Call UpdateCatalogGN()
Response.Redirect("EditCatalogGN.aspx")
Catch ex As Exception
ErrorMessage.Text = ex.Message.ToString
End Try
End Sub

Now when I'm updating lets say Vendor and Notes only all the rest of the
records become empty!

My question is how to set the text box on load event to a null to avoid
inserting empty string to the recirds that I don't want to update

Instead of this (wont work - This is what asp.net page do)
EXEC [dbo].[usp_UpdateCatalog_GN] '39','3','Test 456','','','','','','Ed
Dror'

somthing like this (worked!)
EXEC [dbo].[usp_UpdateCatalog_GN] '39','3','Test
456',Null,Null,Null,Null,Null,'Ed Dror'

How to send a null value if the textbox is empty?

Thanks,
Ed Dror

I think you need to read up on SQLParameters. What you are doing is ripe
for SQL Injection.

Basically you create a SQLParameter for each input parameter for the SP
provide each parameter with a value and add the SQLParameter to the
SQLCommand.

LS