Update based on SQ LDynamic Stored Proc Problem

**Lloyd Sheen** · Mar 20 '08, 06:25 PM

Re: Update based on SQ LDynamic Stored Proc Problem

"Ed Dror" <edd@andrewlaur en.comwrote in message
news:%23BcLFJri IHA.944@TK2MSFT NGP05.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_UpdateCatal og_GN]
Script Date: 03/20/2008 08:03:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFI ER ON
GO
ALTER PROC [dbo].[usp_UpdateCatal og_GN]
@GN_ID int,
@Vendor_ID int = null,
@Brand nvarchar(50) = null,
@Model nvarchar(50) = null,
@Product_Descri ption 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(@Vendo r_ID, Vendor_ID),
Brand = COALESCE(@Brand , Brand),
Model = COALESCE(@Model , Model),
Product_Descrip tion = COALESCE(@Produ ct_Description, Product_Descrip tion),
Notes = COALESCE(@Notes , Notes),
Features = COALESCE(@Featu res, Features),
BldrRefNum = COALESCE(@BldrR efNum, BldrRefNum),
CrtdUser = @CrtdUser
Where
[GN_ID] = @GN_ID
END
>
My UpdateCatalog.V B look like this
>
Public Function UpdateCatalogGN () As Integer
Dim con As New SqlConnection(c onString)
Try
Dim insertString As String = "Execute usp_UpdateCatal og_GN '" &
txtGNID.Text & "','" & txtVendor.Selec tedItem.Value & "','" &
txtBrand.Text & "','" & txtModel.Text & "','" & txtProduct.Text & "','" &
txtNotes.Text & "','" & txtFeature.Text & "','" & txtBldrRefNum.T ext &
"','" & txtCrtdUser.Tex t & "'"
Dim cmd As New SqlCommand(inse rtString, con)
con.Open()
cmd.ExecuteNonQ uery()
con.Close()
Catch ex As Exception
ErrorMessage.Te xt = ex.Message.ToSt ring
End Try
End Function
>
My Submit Button look like this
>
Protected Sub Button1_Click(B yVal sender As Object, ByVal e As
System.EventArg s) Handles Button1.Click
Try
Call UpdateCatalogGN ()
Response.Redire ct("EditCatalog GN.aspx")
Catch ex As Exception
ErrorMessage.Te xt = ex.Message.ToSt ring
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_UpdateCatal og_GN] '39','3','Test 456','','','',' ','','Ed
Dror'
>
somthing like this (worked!)
EXEC [dbo].[usp_UpdateCatal og_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

Update based on SQ LDynamic Stored Proc Problem

Update based on SQ LDynamic Stored Proc Problem

Comment