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]

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

 

[Steven Cheng]

Hi Ed,

As Lloyd mentioned, for ADO.NET, you can use SqlCommand to execute stored
procedure by setting Commandtype to "StoredProcedure" and supplying the
proper Parameters. You should not use plain text (concatenate in code) to
perform SP execution, that may involve SQL injection attack. Here are some
web article introducing how to call stored procedure through ADO.NET:

#How to call SQL Server stored procedures in ASP.NET by using Visual Basic
.NET
http://support.microsoft.com/kb/306574

#Calling Stored Procedures with ADO.NET
http://www.developer.com/db/article.php/10920_3438221_1

Also some additional reference how to configure the parameters for
SqlCommand(include Null value condition):

#Configuring Parameters (ADO.NET)
http://msdn2.microsoft.com/en-us/library/yy6y35y8.aspx

#Handling Null Values (ADO.NET)
http://msdn2.microsoft.com/en-us/library/ms172138.aspx

Hope this helps.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead


Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.



--------------------

From: "Ed Dror" <[email protected]>
Subject: Update based on SQ LDynamic Stored Proc Problem
Date: Thu, 20 Mar 2008 10:48:34 -0700

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