Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 332
  • Last Modified:

Pass Nulls to Stored Procedure

See screenshot of form.

Form has 4 textboxes, user will enter a From Date and To Date, then the other two textboxes are optional.  Once button is clicked, I call Stored Procedure and if the two fields don't have anything in them - should I pass Nulls?  The stored procedure works fine in SQL Server....but I get no results...  Simply want the results back and then bind to DGV.  

Any thoughts on whether I need to pass nulls?  If so, how?

Here is code:
Private Sub Populate_DGV()

        Dim DA_Slip_Summary_Info As New SqlDataAdapter("sp_Slip_Summary_Info", My.Settings.IEC_DB_Connection)
        DA_Slip_Summary_Info.SelectCommand.CommandType = CommandType.StoredProcedure

        With DA_Slip_Summary_Info.SelectCommand

            .Parameters.Add("@From_Date", SqlDbType.NVarChar)
            .Parameters("@From_Date").Value = Me.txt_From_Date.Text

            .Parameters.Add("@To_Date", SqlDbType.NVarChar)
            .Parameters("@To_Date").Value = Me.txt_To_Date.Text

            .Parameters.Add("@Job_Number", SqlDbType.NVarChar)
            .Parameters("@Job_Number").IsNullable = True
            If Me.txt_Job_Number.Text = "" Then
                .Parameters("@Job_Number").Value = DBNull.Value
            Else
                .Parameters("@Job_Number").Value = Me.txt_Job_Number.Text
            End If

            .Parameters.Add("@SWO_Number", SqlDbType.NVarChar)
            .Parameters("@SWO_Number").IsNullable = True
            If Me.txt_SWO_Number.Text = "" Then
                .Parameters("SWO_Number").Value = DBNull.Value
            Else
                .Parameters("SWO_Number").Value = Me.txt_SWO_Number.Text
            End If

        End With

        Dim DS_Slip_Summary_Info As New DataSet
        DA_Slip_Summary_Info.Fill(DS_Slip_Summary_Info)

        Me.DGV_Binding_Source.DataSource = DS_Slip_Summary_Info.Tables(0)
        Me.DGV_Slip_Search.DataSource = Me.DGV_Binding_Source

    End Sub

Open in new window

AllSlips.jpg
0
tobin46
Asked:
tobin46
  • 17
  • 10
  • 5
  • +3
1 Solution
 
tobin46Author Commented:
Here is Stored Procedure code just in case.....
ALTER PROCEDURE [dbo].[sp_Slip_Summary_Info] 
	@From_Date				varchar,
	@To_Date				varchar,
	@Job_Number				varchar,
	@SWO_Number				varchar
AS
BEGIN
	SET NOCOUNT ON;

    Select Slip_Info.Slip_Number,
	Slip_Info.Slip_Date,
	Slip_Info.Date_Added,
	Slip_Info.Job_Number,
	Slip_Info.SWO_ID, 
	IsNull(LaborTotal.Labor_Total,0) as 'Labor Total',
	IsNull(MaterialTotal.Material_Total,0) as 'Material Total',
	IsNull(EquipmentTotal.Equipment_Total,0) as 'Equipment Total',
	IsNull(SubcontractorTotal.Subcontractor_Total,0) as 'Subcontractor Total',
	IsNull((LaborTotal.Labor_Total+MaterialTotal.Material_Total+EquipmentTotal.Equipment_Total+SubcontractorTotal.Subcontractor_Total),0) as 'Slip Total'
	
From 
	Slip_Info
	Left Join (Select Labor.Slip_Number, 
			          cast(sum(Labor.Labor_Total_Cost)as numeric(10,2)) as Labor_Total 
			   from Labor
				INNER JOIN Slip_Info on Labor.Slip_Number = Slip_Info.Slip_Number
			   Group by Labor.Slip_Number) as LaborTotal on (Slip_Info.Slip_Number = LaborTotal.Slip_Number)

	Left Join (Select Material.Slip_Number,
					  cast(sum(Material.Material_Total_Cost)as numeric(10,2)) as Material_Total
			   from Material
				INNER JOIN Slip_Info on Material.Slip_Number = Slip_Info.Slip_Number
			   Group by Material.Slip_Number) as MaterialTotal on (Slip_Info.Slip_Number = MaterialTotal.Slip_Number)

	Left Join (Select Equipment.Slip_Number,
					  cast(sum(Equipment.Equipment_Unit_Total)as numeric(10,2)) as Equipment_Total
				from Equipment
			    INNER Join Slip_Info on Equipment.Slip_Number = Slip_Info.Slip_Number
				Group by Equipment.Slip_Number) as EquipmentTotal on (Slip_Info.Slip_Number = EquipmentTotal.Slip_Number)
	
Left Join (Select Subcontractor.Slip_Number,
					  cast(sum(Subcontractor.Subcontractor_Total)as numeric(10,2)) as Subcontractor_Total
				from Subcontractor
				INNER JOIN Slip_Info on Subcontractor.Slip_Number = Slip_Info.Slip_Number
				Group by Subcontractor.Slip_Number) as SubcontractorTotal on (Slip_Info.Slip_Number = SubcontractorTotal.Slip_Number)
	
Where
	Slip_Info.Slip_Date >= Convert(@From_Date, datetime
	AND Slip_Info.Slip_Date <= @To_Date
	AND Slip_Info.SWO_ID = @SWO_Number
	AND slip_Info.Job_Number = @Job_Number

END

Open in new window

0
 
Imran Javed ZiaConsultant Software Engineer - .NET ArchitectCommented:
hi,

please add checks in sp_Slip_Summary_Info and check @Job_Number is not null as following

Where field = (Case When @Job_Number is null then field else @Job_Number end)

thanks
0
 
tobin46Author Commented:
@IJZ - Could you provide sample in code I provided....not sure I follow...
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
CodeCruiserCommented:
Pass DBNull.Value from the VB.NET code and use below stored procedure



ALTER PROCEDURE [dbo].[sp_Slip_Summary_Info] 
	@From_Date				varchar,
	@To_Date				varchar,
	@Job_Number				varchar,
	@SWO_Number				varchar
AS
BEGIN
	SET NOCOUNT ON;

    Select Slip_Info.Slip_Number,
	Slip_Info.Slip_Date,
	Slip_Info.Date_Added,
	Slip_Info.Job_Number,
	Slip_Info.SWO_ID, 
	IsNull(LaborTotal.Labor_Total,0) as 'Labor Total',
	IsNull(MaterialTotal.Material_Total,0) as 'Material Total',
	IsNull(EquipmentTotal.Equipment_Total,0) as 'Equipment Total',
	IsNull(SubcontractorTotal.Subcontractor_Total,0) as 'Subcontractor Total',
	IsNull((LaborTotal.Labor_Total+MaterialTotal.Material_Total+EquipmentTotal.Equipment_Total+SubcontractorTotal.Subcontractor_Total),0) as 'Slip Total'
	
From 
	Slip_Info
	Left Join (Select Labor.Slip_Number, 
			          cast(sum(Labor.Labor_Total_Cost)as numeric(10,2)) as Labor_Total 
			   from Labor
				INNER JOIN Slip_Info on Labor.Slip_Number = Slip_Info.Slip_Number
			   Group by Labor.Slip_Number) as LaborTotal on (Slip_Info.Slip_Number = LaborTotal.Slip_Number)

	Left Join (Select Material.Slip_Number,
					  cast(sum(Material.Material_Total_Cost)as numeric(10,2)) as Material_Total
			   from Material
				INNER JOIN Slip_Info on Material.Slip_Number = Slip_Info.Slip_Number
			   Group by Material.Slip_Number) as MaterialTotal on (Slip_Info.Slip_Number = MaterialTotal.Slip_Number)

	Left Join (Select Equipment.Slip_Number,
					  cast(sum(Equipment.Equipment_Unit_Total)as numeric(10,2)) as Equipment_Total
				from Equipment
			    INNER Join Slip_Info on Equipment.Slip_Number = Slip_Info.Slip_Number
				Group by Equipment.Slip_Number) as EquipmentTotal on (Slip_Info.Slip_Number = EquipmentTotal.Slip_Number)
	
Left Join (Select Subcontractor.Slip_Number,
					  cast(sum(Subcontractor.Subcontractor_Total)as numeric(10,2)) as Subcontractor_Total
				from Subcontractor
				INNER JOIN Slip_Info on Subcontractor.Slip_Number = Slip_Info.Slip_Number
				Group by Subcontractor.Slip_Number) as SubcontractorTotal on (Slip_Info.Slip_Number = SubcontractorTotal.Slip_Number)
	
Where
	Slip_Info.Slip_Date >= Convert(@From_Date, datetime
	AND Slip_Info.Slip_Date <= @To_Date
	AND Slip_Info.SWO_ID = Case IsNull(@SWO_Number, 0) When 0 Then Slip_Info.swo_id Else @swo_number END 
	AND slip_Info.Job_Number = Case IsNull(@Job_Number, 0) When 0 Then slip_info.job_number Else @job_number End

END

Open in new window

0
 
tobin46Author Commented:
@Cruiser....I'm beginning to think I have bigger problems.  I've modified my code and still no results in the DGV.....  Does the Vb.net code appear correct?
0
 
CodeCruiserCommented:
I see problem here

Slip_Info.Slip_Date >= Convert(@From_Date, datetime
      AND Slip_Info.Slip_Date <= @To_Date

Try

Slip_Info.Slip_Date >= Convert(Date, @From_Date)
      AND Slip_Info.Slip_Date <= Convert(Date, @To_Date)
0
 
Anthony PerkinsCommented:
Is there any reason you are passing in an nvarchar to a parameter that is a varchar?  Also, you should get in the habit of specifying a length to the parameter, as in varchar(35) or whatever.
0
 
sarabhaiCommented:
what result set you needed when From Date and To Date parameter are Null.
0
 
tobin46Author Commented:
I want the user to enter as many parameters as they need....From Data and To Date will be mandatory, but Job Number and SWO Number are not.  The Stored procedure has 4 parameters...and most likely, Job Number or Swo Number or both will be left null.

When I run the SP manually, I have the option to select the check box to pass a null.
0
 
sarabhaiCommented:
Did you want how the null value handle in store procedure or at vb.net coding?
0
 
tobin46Author Commented:
At vb.net coding....

I removed this section of code from vb.net....and removed all parameters and where clause from SP and the datagridview populated with all results....

It's something with the parameters section thats causing the SP to not return results...
With DA_Slip_Summary_Info.SelectCommand

            .Parameters.Add("@From_Date", SqlDbType.VarChar)
            .Parameters("@From_Date").Value = "04/28/2011"

            .Parameters.Add("@To_Date", SqlDbType.VarChar)
            .Parameters("@To_Date").Value = "05/02/2011"

            .Parameters.Add("@Job_Number", SqlDbType.VarChar)
            .Parameters("@Job_Number").IsNullable = True
            If Me.txt_Job_Number.Text = "" Then
                .Parameters("@Job_Number").Value = DBNull.Value
            Else
                .Parameters("@Job_Number").Value = Me.txt_Job_Number.Text
            End If

            .Parameters.Add("@SWO_Number", SqlDbType.VarChar)
            .Parameters("@SWO_Number").IsNullable = True
            If Me.txt_SWO_Number.Text = "" Then
                .Parameters("SWO_Number").Value = DBNull.Value
            Else
                .Parameters("SWO_Number").Value = Me.txt_SWO_Number.Text
            End If

        End With

Open in new window

0
 
sarabhaiCommented:
With DA_Slip_Summary_Info.SelectCommand

            .Parameters.Add("@From_Date", SqlDbType.Date)
            .Parameters("@From_Date").Value = "04/28/2011"
0
 
sarabhaiCommented:
your parameter is in Date or DateTime that should match to store procedure.
if don't mind show the procedure.
0
 
CodeCruiserCommented:
If you pass null from VB code without modifying the SP as described in my comment http:#35508805, you are essentially applying a filter like below

AND Slip_Info.SWO_ID = Null

so it will only return rows with SWO_ID is Null!
0
 
Alpesh PatelAssistant ConsultantCommented:
In Store procedure set default value to NUL

@parameter varchar(10) = NULL


Please make sure SP have Select statement at last
0
 
tobin46Author Commented:
Ok...not working.  Here is the code I'm using at present.

Vb.net code to run procedure and populate DGV.....

Then Stored Procedure....

Private Sub Populate_DGV()

        Dim DA_Slip_Summary_Info As New SqlDataAdapter("sp_Slip_Summary_Info", My.Settings.IEC_DB_Connection)
        DA_Slip_Summary_Info.SelectCommand.CommandType = CommandType.StoredProcedure

        With DA_Slip_Summary_Info.SelectCommand

            .Parameters.Add("@From_Date", SqlDbType.VarChar)
            .Parameters("@From_Date").Value = "04/28/2011"

            .Parameters.Add("@To_Date", SqlDbType.VarChar)
            .Parameters("@To_Date").Value = "05/02/2011"

            .Parameters.Add("@Job_Number", SqlDbType.VarChar)
            .Parameters("@Job_Number").IsNullable = True
            If Me.txt_Job_Number.Text = "" Then
                .Parameters("@Job_Number").Value = DBNull.Value
            Else
                .Parameters("@Job_Number").Value = Me.txt_Job_Number.Text
            End If

            .Parameters.Add("@SWO_Number", SqlDbType.VarChar)
            .Parameters("@SWO_Number").IsNullable = True
            If Me.txt_SWO_Number.Text = "" Then
                .Parameters("SWO_Number").Value = DBNull.Value
            Else
                .Parameters("SWO_Number").Value = Me.txt_SWO_Number.Text
            End If

        End With

        DA_Slip_Summary_Info.Fill(dt_Slip_Summary_Info)

        Me.DGV_Binding_Source.DataSource = dt_Slip_Summary_Info
        Me.DGV_Slip_Search.DataSource = Me.DGV_Binding_Source

    End Sub

Open in new window

USE [IEC]
GO
/****** Object:  StoredProcedure [dbo].[sp_Slip_Summary_Info]    Script Date: 05/03/2011 10:45:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Toby George
-- Create date: May 2, 2011
-- Description:	This will provide a summary row of each slip
-- =============================================
ALTER PROCEDURE [dbo].[sp_Slip_Summary_Info] 
	@From_Date				varchar(10),
	@To_Date				varchar(10),
	@Job_Number				varchar = Null,
	@SWO_Number				varchar = Null
AS
BEGIN
	SET NOCOUNT ON;

    Select Slip_Info.Slip_Number,
	Slip_Info.Slip_Date,
	Slip_Info.Date_Added,
	Slip_Info.Job_Number,
	Slip_Info.SWO_ID, 
	IsNull(LaborTotal.Labor_Total,0) as 'Labor Total',
	IsNull(MaterialTotal.Material_Total,0) as 'Material Total',
	IsNull(EquipmentTotal.Equipment_Total,0) as 'Equipment Total',
	IsNull(SubcontractorTotal.Subcontractor_Total,0) as 'Subcontractor Total',
	IsNull(LaborTotal.Labor_Total,0)+IsNull(MaterialTotal.Material_Total,0)+IsNull(EquipmentTotal.Equipment_Total,0)+IsNull(SubcontractorTotal.Subcontractor_Total,0) as 'Slip Total'
	
From 
	Slip_Info
	Left Join (Select Labor.Slip_Number, 
			          cast(sum(Labor.Labor_Total_Cost)as numeric(10,2)) as Labor_Total 
			   from Labor
				INNER JOIN Slip_Info on Labor.Slip_Number = Slip_Info.Slip_Number
			   Group by Labor.Slip_Number) as LaborTotal on (Slip_Info.Slip_Number = LaborTotal.Slip_Number)

	Left Join (Select Material.Slip_Number,
					  cast(sum(Material.Material_Total_Cost)as numeric(10,2)) as Material_Total
			   from Material
				INNER JOIN Slip_Info on Material.Slip_Number = Slip_Info.Slip_Number
			   Group by Material.Slip_Number) as MaterialTotal on (Slip_Info.Slip_Number = MaterialTotal.Slip_Number)

	Left Join (Select Equipment.Slip_Number,
					  cast(sum(Equipment.Equipment_Unit_Total)as numeric(10,2)) as Equipment_Total
				from Equipment
			    INNER Join Slip_Info on Equipment.Slip_Number = Slip_Info.Slip_Number
				Group by Equipment.Slip_Number) as EquipmentTotal on (Slip_Info.Slip_Number = EquipmentTotal.Slip_Number)
	
Left Join (Select Subcontractor.Slip_Number,
					  cast(sum(Subcontractor.Subcontractor_Total)as numeric(10,2)) as Subcontractor_Total
				from Subcontractor
				INNER JOIN Slip_Info on Subcontractor.Slip_Number = Slip_Info.Slip_Number
				Group by Subcontractor.Slip_Number) as SubcontractorTotal on (Slip_Info.Slip_Number = SubcontractorTotal.Slip_Number)
	
Where
	Slip_Info.Slip_Date >= @From_Date
	AND Slip_Info.Slip_Date <= @To_Date
	AND Slip_Info.SWO_ID = Case IsNull(@SWO_Number, 0) When 0 Then Slip_Info.swo_id Else @swo_number END 
	AND slip_Info.Job_Number = Case IsNull(@Job_Number, 0) When 0 Then slip_info.job_number Else @job_number End



END

Open in new window

0
 
tobin46Author Commented:
@Cruiser - with the code you suggested, when I enter a value for Parameter @Job_Number, I receive the following error....

Msg 245, Level 16, State 1, Procedure sp_Slip_Summary_Info, Line 15
Conversion failed when converting the varchar value 'J' to data type int.

Parameters:
USE [IEC]
GO

DECLARE      @return_value int

EXEC      @return_value = [dbo].[sp_Slip_Summary_Info]
            @From_Date = N'4/27/2011',
            @To_Date = N'5/2/2011',
            @Job_Number = N'JN65',
            @SWO_Number = NULL

SELECT      'Return Value' = @return_value

GO
0
 
Anthony PerkinsCommented:
Let me try this once again in the simplest terms possible:
If you do not define the length of a varchar variable, bad things will happen to you.

But don't take my word for it, see for yourself (fix the obvious typo in xCREATE):
xCREATE PROCEDURE usp_Test 
	@Param	varchar = Null

AS

PRINT LEN(@Param)

GO
EXEC usp_test 'This is a very long parameter, just to show the futility of not defining the length in a varchar variable.'
GO
DROP PROCEDURE usp_test

Open in new window

0
 
tobin46Author Commented:
@aceperkins - I've ran the procedure and get "1" in the Messages pane.  What's the point?  

I've defined the lengths per your suggestion....:
ALTER PROCEDURE [dbo].[sp_Slip_Summary_Info]
      @From_Date                        varchar(10),
      @To_Date                        varchar(10),
      @Job_Number                  varchar = Null,
      @SWO_Number                  varchar = Null

Job_Number and SWO_Number are variable length and I've defined them as varchar(50) in the database table.  Should I include the '50' in the paramenters as well???
0
 
CodeCruiserCommented:
Change the following


Where
      Slip_Info.Slip_Date >= @From_Date
      AND Slip_Info.Slip_Date <= @To_Date
      AND Slip_Info.SWO_ID = Case IsNull(@SWO_Number, 0) When 0 Then Slip_Info.swo_id Else @swo_number END
      AND slip_Info.Job_Number = Case IsNull(@Job_Number, 0) When 0 Then slip_info.job_number Else @job_number End


to




Where
	Slip_Info.Slip_Date >= @From_Date
	AND Slip_Info.Slip_Date <= @To_Date
	AND Slip_Info.SWO_ID = Case Len(IsNull(@SWO_Number, '')) When 0 Then Slip_Info.swo_id Else @swo_number END 
	AND slip_Info.Job_Number = Case Len(IsNull(@Job_Number, '')) When 0 Then slip_info.job_number Else @job_number End

Open in new window

0
 
Anthony PerkinsCommented:
>>I've ran the procedure and get "1" in the Messages pane.  What's the point?  <<
Surely you are not serious?  If so, I give up.

Good luck.
0
 
Anthony PerkinsCommented:
>>Should I include the '50' in the paramenters as well??? <<
Ok, let me try one more time:  Yes!  And a thousand times yes.  If you define the parameter as varchar than that is the same as varchar(1).  Hence the 1 in your "Message pane" as in the length of the value passed in.  Do you really want to pass in a value with only one byte every time?  I suspect not.

Please say you understand now.
0
 
tobin46Author Commented:
@acperkins: Surely, I wasn't serious.  I asked what's the point because I felt like you were trying to make me feel stupid.  I provide Varchar lengths, it was an oversight.  Please excuse yourself from the question and please make it a habit to not participate in any questions I ask from this point forward.

I'm in a very frustrating situation where my father invested money with a professional to develop a simple invoicing app specific to his line of business.  This person "Stiffed" him and we are in need.  This isn't for fun for me.  

The question is how can you pass a null to a stored procedure expecting 4 parameters?  Either help or save your comments.  
0
 
tobin46Author Commented:
@acperkins: Again, I researched your point and fully understand now.  I appreciate your help and will put that knowledge to work going forward.  
0
 
Anthony PerkinsCommented:
>> Either help or save your comments.<<
Fair enough.

Good luck.
0
 
tobin46Author Commented:
VERY CLOSE!!!!!!
It is working for all parameters except SWO_Number for some reason.  I am able to search by From and To Date ONLY, and also From, To and Job Number, but when I throw in SWO Number, I'm not getting any results....Any thoughts, all looks right....

Here is the code and stored procedure that is currently working....

I set defaults on "optional" parameters in SP as = Null, and if the text boxes are empty, I don't pass any value.  
Private Sub Populate_DGV()

        Me.DS_Slip_Summary_Info.Clear()
        Me.dt_Slip_Summary_Info.Clear()

        Dim DA_Slip_Summary_Info As New SqlDataAdapter("sp_Slip_Summary_Info", My.Settings.IEC_DB_Connection)
        DA_Slip_Summary_Info.SelectCommand.CommandType = CommandType.StoredProcedure

        With DA_Slip_Summary_Info.SelectCommand

            .Parameters.Add("@From_Date", SqlDbType.VarChar, 10)
            .Parameters("@From_Date").Value = Me.txt_From_Date.Text

            .Parameters.Add("@To_Date", SqlDbType.VarChar, 10)
            .Parameters("@To_Date").Value = Me.txt_To_Date.Text

            Me.Job_Number = Me.txt_Job_Number.Text
            If Me.Job_Number = Nothing Then
                .Parameters.Add("@Job_Number", SqlDbType.VarChar, 50)
            Else
                .Parameters.Add("@Job_Number", SqlDbType.VarChar, 50)
                .Parameters("@Job_Number").Value = Me.txt_Job_Number.Text
            End If

            Me.SWO_Number = Me.txt_SWO_Number.Text
            If Me.SWO_Number = Nothing Then
                .Parameters.Add("@SWO_Number", SqlDbType.VarChar, 50)
            Else
                .Parameters.Add("@SWO_Number", SqlDbType.VarChar, 50)
                .Parameters("SWO_Number").Value = Me.txt_SWO_Number.Text
            End If

        End With

        DA_Slip_Summary_Info.Fill(dt_Slip_Summary_Info)

        DA_Slip_Summary_Info.SelectCommand = Nothing

        Me.DGV_Binding_Source.DataSource = dt_Slip_Summary_Info
        Me.DGV_Slip_Search.DataSource = Me.DGV_Binding_Source

    End Sub

Open in new window

ALTER PROCEDURE [dbo].[sp_Slip_Summary_Info] 
	@From_Date				varchar(10),
	@To_Date				varchar(10),
	@Job_Number				varchar(50)=Null,
	@SWO_Number				varchar(50)=Null
	
AS
BEGIN
	SET NOCOUNT ON;

    Select Slip_Info.Slip_Number,
	Slip_Info.Slip_Date,
	Slip_Info.Date_Added,
	Slip_Info.Job_Number,
	Slip_Info.SWO_ID, 
	IsNull(LaborTotal.Labor_Total,0) as 'Labor Total',
	IsNull(MaterialTotal.Material_Total,0) as 'Material Total',
	IsNull(EquipmentTotal.Equipment_Total,0) as 'Equipment Total',
	IsNull(SubcontractorTotal.Subcontractor_Total,0) as 'Subcontractor Total',
	IsNull(LaborTotal.Labor_Total,0)+IsNull(MaterialTotal.Material_Total,0)+IsNull(EquipmentTotal.Equipment_Total,0)+IsNull(SubcontractorTotal.Subcontractor_Total,0) as 'Slip Total'
	
From 
	Slip_Info
	Left Join (Select Labor.Slip_Number, 
			          cast(sum(Labor.Labor_Total_Cost)as numeric(10,2)) as Labor_Total 
			   from Labor
				INNER JOIN Slip_Info on Labor.Slip_Number = Slip_Info.Slip_Number
			   Group by Labor.Slip_Number) as LaborTotal on (Slip_Info.Slip_Number = LaborTotal.Slip_Number)

	Left Join (Select Material.Slip_Number,
					  cast(sum(Material.Material_Total_Cost)as numeric(10,2)) as Material_Total
			   from Material
				INNER JOIN Slip_Info on Material.Slip_Number = Slip_Info.Slip_Number
			   Group by Material.Slip_Number) as MaterialTotal on (Slip_Info.Slip_Number = MaterialTotal.Slip_Number)

	Left Join (Select Equipment.Slip_Number,
					  cast(sum(Equipment.Equipment_Unit_Total)as numeric(10,2)) as Equipment_Total
				from Equipment
			    INNER Join Slip_Info on Equipment.Slip_Number = Slip_Info.Slip_Number
				Group by Equipment.Slip_Number) as EquipmentTotal on (Slip_Info.Slip_Number = EquipmentTotal.Slip_Number)
	
Left Join (Select Subcontractor.Slip_Number,
					  cast(sum(Subcontractor.Subcontractor_Total)as numeric(10,2)) as Subcontractor_Total
				from Subcontractor
				INNER JOIN Slip_Info on Subcontractor.Slip_Number = Slip_Info.Slip_Number
				Group by Subcontractor.Slip_Number) as SubcontractorTotal on (Slip_Info.Slip_Number = SubcontractorTotal.Slip_Number)
	
Where
	Slip_Info.Slip_Date >= @From_Date
	AND Slip_Info.Slip_Date <= @To_Date
	AND slip_Info.Job_Number = IsNull(@Job_Number, Slip_Info.Job_Number)
	AND Slip_Info.SWO_ID = IsNull(@SWO_Number, Slip_Info.SWO_ID)

Order by Slip_Info.Slip_Number
END

Open in new window

0
 
CodeCruiserCommented:
Try to implement my suggestion http:#35517478
0
 
tobin46Author Commented:
@Cruiser: It works with your solution as well.....BUT still won't return anything for SWO_Number....strangest thing.
0
 
CodeCruiserCommented:
And you are sure that it SHOULD return some rows?
0
 
tobin46Author Commented:
Definitely should return rows.  When I enter From Data: 4/28/2011 and To Date 5/2/2011 I get 10 rows.  There is one record with Job_Number = JN65, so I enter JN65 in the Job Number textbox and one row returns....but anytime I enter a value in the SWO Number Textbox (despite knowing what to expect in return), I get zero records.....
0
 
CodeCruiserCommented:
>There is one record with Job_Number = JN65
Do you use both Job_Number and SWO_Number at same time or one at a time?
0
 
tobin46Author Commented:
The user could use both at same time or one at a time...

Here are some test cases I've done thus far.

Test Case 1:
Action: Enter From Date AND To Date - click Search
Response: 10 rows returned  (I then scan list and pick out a Job Number and SWO Number to search for in Test Cases 2 and 3)

Test Case 2:
Pre-requisite: I leave the previously entered dates as-is in the textboxes
Action: Enter Job Number identified from previous results (this way I know row exists) - click search
Response:  The record with the Job Number is then returned (1 row returned)

Test Case 3:
Pre-Requisite: Clear out Job Number and search for data range - original results returned
Action: Enter SWO Number (identified earlier) and click search
Results: No results are returned
0
 
CodeCruiserCommented:
Are you using this

AND Slip_Info.SWO_ID = Case Len(IsNull(@SWO_Number, '')) When 0 Then Slip_Info.swo_id Else @swo_number END

?

Is SWO_ID of type Varchar in DB?
0
 
tobin46Author Commented:
Yes I am using the above syntax.

Yes, SWO_ID is type varchar(50) in DB.
0
 
CodeCruiserCommented:
Does it work if you comment out the job number condition?
0
 
tobin46Author Commented:
No.  
0
 
tobin46Author Commented:
Got it working!!!!!  Thanks Cruiser!  I sincerely appreciate your effort, I really can't tell you how much so.

0
 
CodeCruiserCommented:
Glad to help :-)
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

  • 17
  • 10
  • 5
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now