First time for this Error Message?

BlakeMcKenna

Active member
Joined
Oct 27, 2008
Messages
38
Programming Experience
10+
I have a VB.Net app that generates quite a few Crystal Reports (that's the plan anyway). My problem is that I keep getting the attached error. This particular query uses 2 tables. I have some other reports (I just started developing them) that use only 1 table and they work fine. So, I'm thinking that it might have to do with the syntax or something but not positive. Below is my code. The first SubProcedure, rptSingleWorkOrder is the one I'm having problems with. The funny thing is, the code executes all the way through the procedure cleanly and doesn't take the Try Catch construct until after the procedure is called. The SubProcedures rptItemMasterListing and rptCustomerMasterListing both work fine.

VB.NET:
    Private Sub rptSingleWorkOrder()
        Try
            strSQL = "Select A.productName, " & _
                            "A.productDesc, " & _
                            "A.startDate, " & _
                            "A.endDate, " & _
                            "A.totalCost, " & _
                            "A.laborRate, " & _
                            "B.componentName, " & _
                            "B.wrkID, " & _
                            "B.reqdPieces, " & _
                            "B.totalCost, " & _
                            "B.specialInstruction, " & _
                            "B.imageLocation " & _
                       "From tblWorkOrderMst A, tblWorkOrderDet B " & _
                      "Where A.wrkID = '" & mWOID & "' AND " & _
                            "B.wrkID = '" & mWOID & "'"

            Dim cr As New rptWorkOrder
            Dim sel As New SqlCommand(strSQL, cnn)

            DA.SelectCommand = sel
            DA.Fill(DS)

            cr.RecordSelectionFormula = strSQL

            For i = 0 To cr.Database.Tables.Count - 1
                cr.Database.Tables(i).ApplyLogOnInfo(ConInfo)
            Next

            cr.SetDataSource(DS.Tables("tblWO"))
            crViewer.DisplayGroupTree = False
            crViewer.ShowGroupTreeButton = False
            crViewer.ReportSource = cr

        Catch ex As Exception
            dErr.ModuleInError = "frmReports"
            dErr.ErrorMsg = ex.InnerException.Message
            dErr.SubProcedure = "rptSingleWorkOrder()"
            dErr.DisplayErrMsg(dErr.ErrorMsg, dErr.ModuleInError, dErr.SubProcedure)
        End Try
    End Sub
    '
    '
    '
    '
    '
    '
    Public Sub rptItemMasterListing()
        Try
            strSQL = "Select * From tblItemMaster"

            Dim cr As New rptItemMasterListing
            Dim sel As New SqlCommand(strSQL, cnn)

            DA.SelectCommand = sel
            DA.Fill(DS, "tblItemMaster")

            cr.SetDataSource(DS.Tables("tblItemMaster"))
            crViewer.DisplayGroupTree = False
            crViewer.ShowGroupTreeButton = False
            crViewer.ReportSource = cr

        Catch ex As Exception
            dErr.ModuleInError = "frmReports"
            dErr.ErrorMsg = ex.Message
            dErr.SubProcedure = "rptItemMasterListing()"
            dErr.DisplayErrMsg(dErr.ErrorMsg, dErr.ModuleInError, dErr.SubProcedure)
        End Try
    End Sub
    '
    '
    '
    '
    '
    '
    Public Sub rptCustomerMasterList()
        Try
            strSQL = "Select * From tblCustomers"

            Dim cr As New rptCustomerMasterListing
            Dim sel As New SqlCommand(strSQL, cnn)

            DA.SelectCommand = sel
            DA.Fill(DS, "tblCustomers")

            cr.SetDataSource(DS.Tables("tblCustomers"))
            crViewer.DisplayGroupTree = False
            crViewer.ShowGroupTreeButton = False
            crViewer.ReportSource = cr

        Catch ex As Exception
            dErr.ModuleInError = "frmReports"
            dErr.ErrorMsg = ex.Message
            dErr.SubProcedure = "rptCustomerMasterList()"
            dErr.DisplayErrMsg(dErr.ErrorMsg, dErr.ModuleInError, dErr.SubProcedure)
        End Try
    End Sub

Thanks in advance,

Blake
 

Attachments

  • ErrorMsg.jpg
    ErrorMsg.jpg
    35.1 KB · Views: 36
You need to do an INNER JOIN in your SQL statement to connect the tables - do not use a field value. Try this:
VB.NET:
strSQL = "SELECT A.productName, " & _
                            "A.productDesc, " & _
                            "A.startDate, " & _
                            "A.endDate, " & _
                            "A.totalCost, " & _
                            "A.laborRate, " & _
                            "B.componentName, " & _
                            "B.wrkID, " & _
                            "B.reqdPieces, " & _
                            "B.totalCost, " & _
                            "B.specialInstruction, " & _
                            "B.imageLocation " & _
                       "FROM tblWorkOrderMst A INNER JOIN tblWorkOrderDet B " & _
                       " ON A.wrkID = B.wrkID " & _
                      "WHERE A.wrkID = '" & mWOID & "'"
 
I presume that ther error message changed to reflect the changed SQL statement?

I see that you have a Data Adapter and a Data Set but you never seem to use them.

On which line are you getting the error?
 
That's just it....my code, from the point of where I call the routine until the routine ends throws no exception. I don't even know where or how the error gets generated because I've stepped through the entire process. Kinda weird! As I mentioned, I've never seen this kind of error before.
 
Here is my code and yes, I believe I know what your saying. I've revised the code and now I'm getting the "Database Login" prompt (See attached screenshot). Also, I think the code that I have is more than what I need.

VB.NET:
    Private Sub rptSingleWorkOrder()
        Try
            strSQL = "Select A.productName, " & _
                            "A.productDesc, " & _
                            "A.startDate, " & _
                            "A.endDate, " & _
                            "A.totalCost, " & _
                            "A.laborRate, " & _
                            "A.wrkID, " & _
                            "B.componentName, " & _
                            "B.reqdPieces, " & _
                            "B.totalCost, " & _
                            "B.specialInstruction, " & _
                            "B.imageLocation " & _
                       "From tblWorkOrderMst A INNER JOIN tblWorkOrderDet B ON " & _
                            "A.wrkID = B.wrkID " & _
                      "Where A.wrkID = '" & mWOID & "'"

            Dim cr As New rptWorkOrder
            Dim sel As New SqlCommand(strSQL, cnn)

            DA.SelectCommand = sel
            DA.Fill(DS)

            cr.RecordSelectionFormula = "{tblWorkOrderMst.wrkID} = '" & mWOID & "'"

            For i = 0 To cr.Database.Tables.Count - 1
                cr.Database.Tables(i).ApplyLogOnInfo(ConInfo)
            Next

            cr.SetDataSource(DS.Tables(0, 1))
            crViewer.DisplayGroupTree = False
            crViewer.ShowGroupTreeButton = False
            crViewer.ReportSource = cr

        Catch ex As Exception
            Messagebox.Show(ex.Message)
        End Try
    End Sub
 

Attachments

  • ErrorMsg.jpg
    ErrorMsg.jpg
    14 KB · Views: 28
Back
Top