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: 607
  • Last Modified:

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

0
pposton
Asked:
pposton
  • 3
  • 2
1 Solution
 
Anthony PerkinsCommented:
Please post the contents of your Stored Procedures assCatRptGrp and assCatRptGrpPie.
0
 
ppostonPresident/OwnerAuthor Commented:
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

0
 
Alan WarrenCommented:
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
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
Alan WarrenCommented:
Hi pposton,

IListSource does not contain any data sources
http://stackoverflow.com/questions/695792/ilistsource-does-not-contain-any-data-sources

They suggest an empty recordset causes this error and that you should check If das.Tables.Count > 0 Then: return das else: return nothing

Alan
0
 
ppostonPresident/OwnerAuthor Commented:
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
0
 
ppostonPresident/OwnerAuthor Commented:
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.
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now