We help IT Professionals succeed at work.

Send Null to SQL Stored Proc with XML Parameters

miguel_j
miguel_j asked
on
Medium Priority
358 Views
Last Modified: 2012-06-27
Hi

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
vb
 
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>" & _
             "</id></Product>"
 
 
Dim ProductDimensionParameter As New Parameter()
ProductDimensionParameter.Name = "ProductDimensions"
ProductDimensionParameter.DefaultValue = xmlProdDimWebString
ProductDimensionParameter.Direction = Data.ParameterDirection.Input
ProductDimensionParameter.DbType = Data.DbType.Xml
SQLDS.InsertParameters.Add(ProductDimensionParameter)
SQLDS.Insert()
 
 
sql
 
@ProdDimensions XML
 
 
as
 
 
DECLARE @ProductId INT --testing
SET @ProductId = 1 --testing
 
 
INSERT INTO Inventory.Dimensions
(ProductId, Width, Height, Depth, Diameter, LabelName)
SELECT	@ProductId,
		ParamValues.ID.query('Width').value('.','FLOAT'),
		ParamValues.ID.query('Height').value('.','FLOAT'),
		ParamValues.ID.query('Depth').value('.','FLOAT'),
		ParamValues.ID.query('Diameter').value('.','FLOAT'),
		ParamValues.ID.query('Label').value('.','VARCHAR(30)')
		FROM @ProdDimensions.nodes('/Product/id') as ParamValues(ID)

Open in new window

Comment
Watch Question

Rahul Goel ITILSenior Consultant - Deloitte
CERTIFIED EXPERT

Commented:
Use like this
(ProductLabel.Text.ToString() == string.Empty ? DbNull.Value : ProductLabel.Text.ToString())

Author

Commented:
Thanks Rahu..

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

How do i check for a null on the stored procedure
CERTIFIED EXPERT
Top Expert 2012

Commented:
If you do not include the Xml element, then the value will be NULL and it will be added as NULL to the table.

Author

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

e.g

isNull(ParamValues.ID.query('Height').value('.','FLOAT'), Null)
CERTIFIED EXPERT
Top Expert 2012

Commented:
>>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.
CERTIFIED EXPERT
Top Expert 2012

Commented:
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 =
    '<Product>
      <id>
          <Label>Label Description</Label>
          <Width>1234.56</Width>
          <Depth>3456.78</Depth>
          <Diameter>4567.89</Diameter>
      </id>
    </Product>'

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)')

CERTIFIED EXPERT
Top Expert 2012
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

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
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.