Dynamic Sql not populating asp.net datatable

I am new to using Dynamic Sqls and in my first attempt I have a stored procedure listed below that is dynamically building my sql string. When I execute the stored procedure with Sql Manager I get results back. But when i run it from my application the Datatable is not populated. Can someone take a look to make sure I am not missing anything within my procedure to return the results...

@StartDate Datetime,
@EndDate DateTime,
@RoleID int,
@tx nvarchar(50) = NULL,
@docid nvarchar(5)= NULL

	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.

   /* Variable Declaration */
    Declare @SQLQuery AS NVarchar(4000)
    Declare @ParamDefinition AS NVarchar(2000) 

    /* Build the Transact-SQL String with the input parameters */ 
    Set @SQLQuery = 'Select * FROM   CMSC.Orders o
   INNER JOIN   CMSC.WorkItems wi ON o.Orderid = wi.Orderid
   INNER JOIN   CMSC.WorkItemStatusDef wis on wis.StatusCode = wi.StatusCode
   INNER JOIN   CMSC.Inventory i on o.InvID = i.InvID
	 where (1=1) ' 

    /* check for the condition and build the WHERE clause accordingly */
	If (@StartDate Is Not Null) AND (@EndDate Is Not Null)
         Set @SQLQuery = @SQLQuery + ' And (o.OrderDate
         BETWEEN @StartDate AND @EndDate)'
	If @RoleID Is Not Null
         Set @SQLQuery = @SQLQuery + ' And (o.RoleID = @RoleID)'

    If @tx Is Not Null
         Set @SQLQuery = @SQLQuery + ' And (o.DocNum = @tx)'
	If @docid Is Not Null
         Set @SQLQuery = @SQLQuery + ' And (o.DocID = @docid)'
	Set @ParamDefinition = '@StartDate DateTime,
                @EndDate DateTime,
				@RoleID int,
                @tx nvarchar(50),
				@docid nvarchar(5)'

    /* Execute the Transact-SQL String with all parameter value's 
       Using sp_executesql Command */
    Execute sp_Executesql     @SQLQuery, 

Open in new window

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DStarkeyAuthor Commented:
Here is a copy of my function that calls the stored procedure. I use this type of function for all my procedures and all have have worked fine until now.

     Public Function ClientQuery(ByVal roleid As Integer, ByVal startdate As Date, ByVal enddate As Date, ByVal tx As String, _
                                 ByVal docid As String) As DataTable
        'Dim _string As String = ""

            Dim connString As String = _
            'Create a SqlConnection instance
            Using myConnection As New SqlConnection(connString)

                If myConnection.State = ConnectionState.Closed Then


                End If
                'Create a SqlCommand instance
                Dim myCommand As New SqlCommand("CMSC.GetClientQuery", myConnection)

                myCommand.CommandType = CommandType.StoredProcedure
                myCommand.Parameters.Add("@RoleID", SqlDbType.VarChar).Value = roleid
                myCommand.Parameters.Add("@StartDate", SqlDbType.VarChar).Value = startdate
                myCommand.Parameters.Add("@EndDate", SqlDbType.VarChar).Value = enddate
                myCommand.Parameters.Add("@tx", SqlDbType.VarChar).Value = tx
                myCommand.Parameters.Add("@docid", SqlDbType.VarChar).Value = docid

                'Get back a DataTable
                Dim myDataTable As New DataTable

                'Create a SqlDataAdapter instance
                Dim myAdapter As New SqlDataAdapter(myCommand)

                ClientSearchResults = myDataTable

                'Close the connection

            End Using
        Catch ex As Exception
            lblError = ex.Message
        End Try
    End Function

Open in new window

Lee SavidgeCommented:
Run SQL profiler when you run your application to see what the application is actually sending to the SQL Database. See if that matches what you manually type in to get results. I suspect you have not defined a parameter correctly in your application if the SQL works fine.

DStarkeyAuthor Commented:
Thansk for the sugestion I never used the profiler before. Very handy...

Here is what the Profiler shows

exec CMSC.GetClientQuery @RoleID='76',@StartDate='1/1/2010 12:00:00 AM',@EndDate='4/6/2010 12:00:00 AM',@tx='',@docid=''

I was able to extract the sql and found out that if I remove @tx and @docid from the line above, results come back. Also if I add NULL to the last two parameters I get the same resutls.

exec CMSC.GetClientQuery @RoleID='76',@StartDate='1/1/2010 12:00:00 AM',@EndDate='4/6/2010 12:00:00 AM', @tx=NULL, @docid=NULL

How can I get the results to pass NULL instead of ' ' , Any ideas?  Or do I need to look at doing something different within the stored procedure?

The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Lee SavidgeCommented:
When you declare you variables in the calling application, set them to null so when the SQL is constructed a null is passed.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DStarkeyAuthor Commented:
Thanks for the help...
Lee SavidgeCommented:
You're welcome.


It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.