Start Free Trial

asked on

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

ASKER

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

Imran Javed Zia

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

ASKER

@IJZ - Could you provide sample in code I provided....not sure I follow...

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

ASKER

@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?

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)

Anthony Perkins

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.

what result set you needed when From Date and To Date parameter are Null.

ASKER

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.

Did you want how the null value handle in store procedure or at vb.net coding?

ASKER

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

With DA_Slip_Summary_Info.SelectCommand

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

your parameter is in Date or DateTime that should match to store procedure.
if don't mind show the procedure.

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!

In Store procedure set default value to NUL

@parameter varchar(10) = NULL

Please make sure SP have Select statement at last

ASKER

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

ASKER

@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

Anthony Perkins

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

ASKER

@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???

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Anthony Perkins

>>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.

Anthony Perkins

>>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.

ASKER

@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.

ASKER

@acperkins: Again, I researched your point and fully understand now. I appreciate your help and will put that knowledge to work going forward.

Anthony Perkins

>> Either help or save your comments.<<
Fair enough.

Good luck.

ASKER

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

Try to implement my suggestion http:#35517478

ASKER

@Cruiser: It works with your solution as well.....BUT still won't return anything for SWO_Number....strangest thing.

And you are sure that it SHOULD return some rows?

ASKER

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.....

>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?

ASKER

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

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?

ASKER

Yes I am using the above syntax.

Yes, SWO_ID is type varchar(50) in DB.

Does it work if you comment out the job number condition?

ASKER

No.

ASKER

Got it working!!!!! Thanks Cruiser! I sincerely appreciate your effort, I really can't tell you how much so.

Glad to help :-)