• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 329
  • Last Modified:

Send Null to SQL Stored Proc with XML Parameters


I have a web form where the user will enter some information in a textbox.
The user may leave the texbox blank

Problem lies is how do i check for null (empty string value) when i send the string to the stored procedure.... i want to convert these to nulls at add the null to my table
Dim xmlProdDimWebString = ""
Dim ProductLabel As TextBox = MyForm.FindControl("ProductLabelTextBox")
Dim ProductWidth As TextBox = MyForm.FindControl("ProductWidthTextBox")
Dim ProductHeight As TextBox = MyForm.FindControl("ProductHeightTextBox")
Dim ProductDepth As TextBox = MyForm.FindControl("ProductDimDepthTextBox")
Dim ProductDiameter As TextBox = MyForm.FindControl("ProductDimDiameterTextBox")
xmlProdDimWebString = xmlProdDimWebString + "<Product><id>" & _
             "<Label>" & ProductLabel.Text & "</Label>" & _
             "<Width>" & ProductWidth.Text & "</Width>" & _
             "<Height>" & ProductHeight.Text & "</Height>" & _
             "<Depth>" & ProducDepth.Text & "</Depth>" & _
             "<Diameter>" & ProductDiameter.Text & "</Diameter>" & _
Dim ProductDimensionParameter As New Parameter()
ProductDimensionParameter.Name = "ProductDimensions"
ProductDimensionParameter.DefaultValue = xmlProdDimWebString
ProductDimensionParameter.Direction = Data.ParameterDirection.Input
ProductDimensionParameter.DbType = Data.DbType.Xml
@ProdDimensions XML
DECLARE @ProductId INT --testing
SET @ProductId = 1 --testing
INSERT INTO Inventory.Dimensions
(ProductId, Width, Height, Depth, Diameter, LabelName)
SELECT	@ProductId,
		FROM @ProdDimensions.nodes('/Product/id') as ParamValues(ID)

Open in new window

  • 4
  • 3
1 Solution
Rahul Goel ITILSenior Consultant - DeloitteCommented:
Use like this
(ProductLabel.Text.ToString() == string.Empty ? DbNull.Value : ProductLabel.Text.ToString())
miguel_jAuthor Commented:
Thanks Rahu..

It still adds a 0 into the table field...

How do i check for a null on the stored procedure
Anthony PerkinsCommented:
If you do not include the Xml element, then the value will be NULL and it will be added as NULL to the table.
7 new features that'll make your work life better

It’s our mission to create a product that solves the huge challenges you face at work every day. In case you missed it, here are 7 delightful things we've added recently to monday to make it even more awesome.

miguel_jAuthor Commented:
i removed the element from the xmlstring but it still adds a 0

is there a way to check from empty string or null at the storedproc


isNull(ParamValues.ID.query('Height').value('.','FLOAT'), Null)
Anthony PerkinsCommented:
>>i removed the element from the xmlstring but it still adds a 0<<
You are right.  As a workaround consider using OPENXML() which does not have that problem.
Anthony PerkinsCommented:
Alternatively you could try it this way and it will give you the Null you need (Height is not included):

DECLARE @ProdDimensions XML

SET @ProdDimensions =
          <Label>Label Description</Label>

SELECT      @ProdDimensions.value('(/Product/id/Width)[1]','FLOAT'),
      @ProdDimensions.value('(/Product/id/Height)[1]', 'FLOAT'),
      @ProdDimensions.value('(/Product/id/Depth)[1]', 'FLOAT'),
      @ProdDimensions.value('(/Product/id/Diameter)[1]', 'FLOAT'),
      @ProdDimensions.value('(/Product/id/Label)[1]', 'VARCHAR(30)')

Anthony PerkinsCommented:
Or if you still want to use the nodes method then this will produce the right results:

SELECT      ParamValues.ID.value('(Width)[1]', 'FLOAT'),
      ParamValues.ID.value('(Height)[1]', 'FLOAT'),
      ParamValues.ID.value('(Depth)[1]', 'FLOAT'),
      ParamValues.ID.value('(Diameter)[1]', 'FLOAT'),
      ParamValues.ID.value('(Label)[1]', 'VARCHAR(30)')
FROM      @ProdDimensions.nodes('/Product/id') as ParamValues(ID)
Totally unrelated, unless you have to, do not use float, instead use numeric.
miguel_jAuthor Commented:
Thank You acperkins,
Ive worked with your input... Im only sending the parameters that I need and have adapted your sql method to parse my string.

ps. Ive also removed the use of float and accepted decimal or numeric
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now