asked on

Stored Procedure Not Working

I have a piece of code which runs a report based upon a stored procedure and filtered by a checkboxlist. When running the code on my local machine everything works fine, but when I uploaded it to a test server it no longer works. It throws the error "The IListSource does not contain any data sources". I have checked the Connection String and it looks good.

Any ideas would be appreciated. Here is my code below.

Imports System.Web.UI.DataVisualization.Charting
Imports System.Data
Imports System.Web.Security
Imports System.Data.Sql
Imports System.Data.SqlClient
Imports System.Web.UI.WebControls
Imports System.Collections
Imports System.Collections.Generic
Imports System.Configuration
Imports System.IO
Imports System.Web.UI

Partial Class grpAssRpt
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load

    End Sub

    Protected Sub Button4_Click(sender As Object, e As System.EventArgs) Handles Button4.Click



        'clear the current list
        ltChurchList.Value = String.Empty

        For Each item In grpMembers.Items
            If item.Selected Then
                ltChurchList.Value = ltChurchList.Value & item.Value & ","
            End If
        Next

        'Trim off the trailing ","
        If Len(ltChurchList.Value) > 0 Then
            ltChurchList.Value = "'" & Left(ltChurchList.Value, Len(ltChurchList.Value) - 1) & "'"
        End If

        GridView1.DataSource = getchurchData()
        GridView1.DataBind()

        Chart1.DataSource = getchurchDataPie()
        Chart1.DataBind()

    End Sub

    Protected Function getchurchData() As DataSet

        Dim con As New SqlConnection()
        con.ConnectionString = ConfigurationManager.ConnectionStrings("MyString").ConnectionString

        Dim cmd As New SqlCommand
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = con
        cmd.CommandTimeout = 10000

        cmd.Parameters.AddWithValue("@dStart", dStart.Text)
        cmd.Parameters.AddWithValue("@dEnd", dEnd.Text)
        cmd.Parameters.AddWithValue("@coc", (Session("coc").ToString))
        cmd.Parameters.AddWithValue("@churchList", ltChurchList.Value)

        cmd.CommandText = "assCatRptGrp"

        Dim a As SqlDataAdapter = New SqlDataAdapter(cmd)
        Dim ds As DataSet = New DataSet("churchlist")
        con.Open()


        Try
            a.Fill(ds)

        Catch ex As Exception
            If (Not con Is Nothing) Then
                con.Close()
            End If


        End Try

        Return ds

    End Function

    Protected Function getchurchDataPie() As DataSet

        Dim con As New SqlConnection()
        con.ConnectionString = ConfigurationManager.ConnectionStrings("CharityCheck_TennConnectionString").ConnectionString

        Dim cmd As New SqlCommand
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = con
        cmd.CommandTimeout = 10000

        cmd.Parameters.AddWithValue("@dStart", dStart.Text)
        cmd.Parameters.AddWithValue("@dEnd", dEnd.Text)
        cmd.Parameters.AddWithValue("@coc", (Session("CoC").ToString))
        cmd.Parameters.AddWithValue("@churchList", ltChurchList.Value)

        cmd.CommandText = "assCatRptGrpPie"

        Dim a As SqlDataAdapter = New SqlDataAdapter(cmd)
        Dim das As DataSet = New DataSet("churchlistPie")
        con.Open()


        Try
            a.Fill(das)

        Catch ex As Exception
            If (Not con Is Nothing) Then
                con.Close()
            End If
        End Try

        Return das

    End Function


End Class

Open in new window

Anthony Perkins

Please post the contents of your Stored Procedures assCatRptGrp and assCatRptGrpPie.

pposton

ASKER

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROC [dbo].[assCatRptGRP]

@coc varchar(15),
@dStart datetime = 'mm/dd/yyyy',
@dEnd datetime ='mm/dd/yyyy',
@churchList varchar (1000)


AS

SELECT CASE GROUPING(GiftCategory)
 WHEN 1 THEN 'TOTAL'
 ELSE [GiftCategory] END AS 'Gift Category',

sum(cast(
CASE WHEN (
activity.value) NOT LIKE '%[^0-9.]%' THEN (activity..value) END as NUMERIC)) as "Amount",count(giftCategory)AS "Count"

FROM activity join users on activity.username=users.username  JOIN UserCOC on UserId=users.id join dbo.fn_SplitString(@churchList,',') list ON list.StringValue = church


WHERE COC=@coc AND date >= @dStart AND date <= @dEnd

GROUP BY GiftCategory WITH ROLLUP

Open in new window

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[assCatRptGrpPIE]


@coc varchar(15),
@dStart datetime = 'mm/dd/yyyy',
@dEnd datetime ='mm/dd/yyyy',
@churchList varchar (1000)


AS

SELECT CASE GROUPING(GiftCategory)
 WHEN 1 THEN 'TOTAL'
 ELSE [GiftCategory] END AS 'Gift Category',

sum(cast(
CASE WHEN (
activity.value) NOT LIKE '%[^0-9.]%' THEN (activity..value) END as NUMERIC)) as "Amount",count(giftCategory)AS "Count"

FROM activity join users on activity.username=users.username  JOIN UserCOC on UserId=users.id join dbo.fn_SplitString(@churchList,',') list ON list.StringValue = church


WHERE COC=@coc AND date >= @dStart AND date <= @dEnd

GROUP BY GiftCategory

Open in new window

Alan Warren

Hi pposton,
Maybe something like this:

 ' Create Instance of Connection and Command Object
        Using myConnection As New System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("CharityCheck_TennConnectionString").ConnectionString)
            Using myCommand As New System.Data.SqlClient.SqlCommand("assCatRptGrpPIE", myConnection)

                ' Mark the Command as a SPROC
                myCommand.CommandType = System.Data.CommandType.StoredProcedure

                ' Add parameters to the command object
                ' Populate the @dStart parameter
                Dim prmdStart As New System.Data.SqlClient.SqlParameter("@dStart", System.Data.SqlDbType.DateTime)
                prmdStart .Value = dStart.Text
                myCommand.Parameters.Add(prmdStart)

                ' Populate the @dEnd parameter
                Dim prmdEnd As New System.Data.SqlClient.SqlParameter("@dEnd", System.Data.SqlDbType.DateTime)
                prmdStart .Value = dEnd.Text
                myCommand.Parameters.Add(prmdEnd)

                ' Populate the @coc parameter
                Dim prmcoc As New System.Data.SqlClient.SqlParameter("@coc", System.Data.SqlDbType.VarChar,15)
                prmcoc .Value = (Session("CoC").ToString))
                myCommand.Parameters.Add(prmcoc)

                ' Populate the @churchList parameter
                Dim prmchurchList As New System.Data.SqlClient.SqlParameter("@churchList", System.Data.SqlDbType.VarChar,1000)
                prmchurchList .Value = ltChurchList.Value
                myCommand.Parameters.Add(prmchurchList)


                Try
                    ' Open the connection
                    myConnection.Open()
                    Dim a As System.Data.SqlClient.SqlDataAdapter = New System.Data.SqlClient.SqlDataAdapter
                    a.SelectCommand = myCommand
                    Dim das As System.Data.DataSet = New System.Data.DataSet("churchlistPie")
                    a.Fill(das)
                    Return das
                    
                Catch SQLexc As System.Data.SqlClient.SqlException
                    strErrMsg = "Error in  ~/path/yourpage.aspx.vb.Your_Sub()" & vbLf & "Insert Failed. Error Details are: " & vbLf & SQLexc.ToString()
                    GoTo ReportError
                End Try

            End Using
        End Using


ExitProcedure:
        
        Exit Function

ReportError:

        strErrMsg = "Error in ~/path/yourpage.aspx.vb.Your_Function()" _
          & vbCr & "Error number " & CStr(Err.Number) _
          & " was generated by " & Err.Source _
          & vbCr & vbCr & Err.Description

Open in new window

May need to convert those 2 datetime values from text to a date value

Also the default values for your datetime input params
dStart datetime = 'mm/dd/yyyy',
@dEnd datetime ='mm/dd/yyyy',

Perhaps:
dStart datetime =NULL,
@dEnd datetime =NULL,

Alan

ASKER CERTIFIED SOLUTION

Alan Warren

membership

This solution is only available to members.

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

Start Free Trial

pposton

ASKER

Alan,

I've tried replacing my code with yours but I get an error on the strErrMsg. If I try to "dim strErrMsg as String" it creates a different error. I'm pretty new to this stuff so I'm not real sure how to fix that.

Also, any ideas why the code works as is on my local machine but not on the server?

Phil

pposton

ASKER

Thanks for the help and getting me thinking. The issue was an extra "." in the stored procedure. (activity..value) Odd that it only threw the error when run on the sever and not on my local machine.