Solved

Send Null to SQL Stored Proc with XML Parameters

Posted on 2009-07-07
8
315 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

0
Comment
Question by:miguel_j
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 9

Expert Comment

by:Rahul Goel ITIL
ID: 24801213
Use like this
(ProductLabel.Text.ToString() == string.Empty ? DbNull.Value : ProductLabel.Text.ToString())
0
 

Author Comment

by:miguel_j
ID: 24802984
Thanks Rahu..

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

How do i check for a null on the stored procedure
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24810729
If you do not include the Xml element, then the value will be NULL and it will be added as NULL to the table.
0
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 

Author Comment

by:miguel_j
ID: 24811117
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)
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24813957
>>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.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24814075
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)')

0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 24814145
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.
0
 

Author Closing Comment

by:miguel_j
ID: 31600980
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
0

Featured Post

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
The Confluence of Individual Knowledge and the Collective Intelligence At this writing (summer 2013) the term API (http://dictionary.reference.com/browse/API?s=t) has made its way into the popular lexicon of the English language.  A few years ago, …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question