Solved

Send Null to SQL Stored Proc with XML Parameters

Posted on 2009-07-07
8
311 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
  • 4
  • 3
8 Comments
 
LVL 9

Expert Comment

by:Rahul Goel
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
 

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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

707 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now