Connection Problems

[PeterM]

Hi All,

I am writing a console application that reads in the details of a supplier price list from an SQL table, then reads the price list itself.

It then reads the products table and searches for products in the suppliers price list, if it gets a hit, it does some calulations on cost and retail pricing, writes any difference to a log file and updated the products table with the new prices for that product.

At least, thats what it should do!

I changed over from pervasive PSQL to MS SQL 2000 I believe (Licensed with SBS 2003 Premium).

The problem I am having is that I can read the price list file specs in from the SQL table, then it will read the price list file itself (XLS in this case) but when it comes to searching the products table in the same SQL database it gives me connection issues.

OK, here's the logic,

In a Globals module, I define the SQL connection string, then using the same string, I create 2 connections, one called PLConnection, the other called DBConnection.

In the Main module, I use the PL (Price List) connection in the standard way to read the price lists table to see which of the suppliers have price lists defined and what the columns in their particular file represent (they are all slightly different)

It then goes away and checks to see if the price list file exists on the server using the file name it just read from the Price Lists table.

If the price list is found, it then opens and reads it by whatever method is suitable to that particular file type (again specified in the price lists table) in this case XLS.

It then does a case select and chooses the right method of processing the price list dependant on file type, so it reads the price list in from the spreadsheet in this case.

It then searches for each product in the price list in the products table and if it finds a match, it processes the prices. There are tens of thousands of products in these supplier price lists, not all of which are in the products table, so it has to search each one until it finds a match.

This is the point it is getting stuck on, where, and having already opened one of the two defined connections to read the price list table (there may be multiple price lists defined so it has to read one, process it and then read another one, so the connection is left open.

In opening the second definded connection, when I do a DBConnection.open() it errors telling me the connection is already open, yet when I comment out that line, the ExecuteReader statement tells me there is no open connection!

I guess this is something to do with connection pooling, but I have searched and searched on the web and cant find anything different to what Im doing now.

Any help greatly appreciated!

 

[PeterM]

To add some clarity to this problem, here is the code in the various modules;

Globals:

 '************************ MS SQL VARIABLES ******************************'
    Public SQLMultiActiveResults As Boolean = True

    Public SQLConnectionString As String = "Data Source=pmasvr1;Initial Catalog=PMAERP;User Id=User;Password=Password;"
    Public SQLPLConnection As SqlConnection = New SqlConnection(SQLConnectionString)
    Public SQLDBConnection As SqlConnection = New SqlConnection(SQLConnectionString)
    
    Public SQLPLQuery As String
    Public SQLDBQuery As String
    Public SQLPLReader As SqlDataReader
    Public SQLDBReader As SqlDataReader

Here is the Main module code;

 Sub Main()

        ' Set the run date and time
        Call SetRunDate()

        ' Create the logfile and write the header
        Call CreateLogFileHeader()


        ' Read in the price lists file and find any list available for each supplier
        SQLPLQuery = "Select * from SupplierPriceLists"
        Dim sqlplcomm As New SqlCommand(SQLPLQuery, SQLPLConnection)

        Try
            SQLPLConnection.Open()

        Catch ex As SqlException
            Console.WriteLine(ex)
            Console.WriteLine("The above is the exception, Press any key to continue")
            Console.ReadKey()
        End Try

        SQLPLReader = sqlplcomm.ExecuteReader
        If SQLPLReader.HasRows() Then
            Do While SQLPLReader.Read()


                ' Map the sqlreader to the proper fields
                Call BuildPLStructure()


                ' Check if the pricelist file exists
                PriceListExist = FileExists(PriceListFile.FileName)

                If PriceListExist = True Then

                    ' Write the pricelist to the console
                    LogFile.WriteLine("Price List found for supplier: " & PriceListFile.SupplierCode)
                    Console.WriteLine("Price List found for supplier: " & PriceListFile.SupplierCode)
                    Console.ReadKey()
                    Call WritePriceListToConsole()

                    ' Find the file type and pass it to the correct process
                    Select Case PriceListFile.FileType
                        Case "XLS"
                            LogFile.WriteLine("Price List is type - XLS, XLSX")
                            Console.WriteLine("Price List is type - XLS, XLSX")
                            Call ReadXLS()

                        Case "CSV"
                            LogFile.WriteLine("Price List is type - CSV")
                            Console.WriteLine("Price List is type - CSV")
                            Call ReadCSV()

                        Case "PDF"
                            LogFile.WriteLine("Price List is type - PDF")
                            Console.WriteLine("Price List is type - PDF")
                            Call ReadPDF()

                        Case Else
                            LogFile.WriteLine("Price List file:- " & PriceListFile.FileName & " Is an unknown type")
                            Console.WriteLine("Price List file:- " & PriceListFile.FileName & " Is an unknown type")
                    End Select

                End If

                ' Loop round and read the next pricelist from the database
            Loop
        End If
        ' Write the final stats to the logfile and end the program
        SQLPLReader.Close()
        SQLPLConnection.Close()
        SQLDBReader.Close()
        SQLDBConnection.Close()
        LogFile.Close()
    End Sub

Here is the Read XLS module (The only one we are interested in)

Public Sub ReadXLS()

        Dim i As Integer
        ExcelApp = New Microsoft.Office.Interop.Excel.Application
        ExcelWkBook = ExcelApp.Workbooks.Open(PriceListFile.FileName)

        ExcelWkSheet = ExcelWkBook.Worksheets(PriceListFile.Worksheet)

        'If PriceListFile.HasHeader = "1" Then
        'i = 2
        ' Else
        ' i = 1
        ' End If

        ' Set the number of header rows
        i = PriceListFile.HasHeader + 1




        ' Loop through the spreadsheet, read each row and map the output to the relevant fields
        For i = i To ExcelWkSheet.UsedRange.Rows.Count

            Select Case PriceListFile.ColumnA
                Case "ProductCode"
                    PLProductCode = ExcelWkSheet.Cells(i, 1).value
                Case "ProductName"
                    PLProductName = ExcelWkSheet.Cells(i, 1).value
                Case "SellingPrice_RRP"
                    PLSellingPrice_RRP = ExcelWkSheet.Cells(i, 1).value
                Case "CostPrice_Standard"
                    PLCostPrice_Standard = ExcelWkSheet.Cells(i, 1).value
                Case "UnitPackDescription"
                    PLUnitPackDescription = ExcelWkSheet.Cells(i, 1).value
                Case "ANACode"
                    PLANACode = ExcelWkSheet.Cells(i, 1).value
                Case "UnitPackQuantity"
                    PLUnitPackQuantity = ExcelWkSheet.Cells(i, 1).value
            End Select

            Select Case PriceListFile.ColumnB
                Case "ProductCode"
                    PLProductCode = ExcelWkSheet.Cells(i, 2).value
                Case "ProductName"
                    PLProductName = ExcelWkSheet.Cells(i, 2).value
                Case "SellingPrice_RRP"
                    PLSellingPrice_RRP = ExcelWkSheet.Cells(i, 2).value
                Case "CostPrice_Standard"
                    PLCostPrice_Standard = ExcelWkSheet.Cells(i, 2).value
                Case "UnitPackDescription"
                    PLUnitPackDescription = ExcelWkSheet.Cells(i, 2).value
                Case "ANACode"
                    PLANACode = ExcelWkSheet.Cells(i, 2).value
                Case "UnitPackQuantity"
                    PLUnitPackQuantity = ExcelWkSheet.Cells(i, 2).value
            End Select

            Select Case PriceListFile.ColumnC
                Case "ProductCode"
                    PLProductCode = ExcelWkSheet.Cells(i, 3).value
                Case "ProductName"
                    PLProductName = ExcelWkSheet.Cells(i, 3).value
                Case "SellingPrice_RRP"
                    PLSellingPrice_RRP = ExcelWkSheet.Cells(i, 3).value
                Case "CostPrice_Standard"
                    PLCostPrice_Standard = ExcelWkSheet.Cells(i, 3).value
                Case "UnitPackDescription"
                    PLUnitPackDescription = ExcelWkSheet.Cells(i, 3).value
                Case "ANACode"
                    PLANACode = ExcelWkSheet.Cells(i, 3).value
                Case "UnitPackQuantity"
                    PLUnitPackQuantity = ExcelWkSheet.Cells(i, 3).value
            End Select

            Select Case PriceListFile.ColumnD
                Case "ProductCode"
                    PLProductCode = ExcelWkSheet.Cells(i, 4).value
                Case "ProductName"
                    PLProductName = ExcelWkSheet.Cells(i, 4).value
                Case "SellingPrice_RRP"
                    PLSellingPrice_RRP = ExcelWkSheet.Cells(i, 4).value
                Case "CostPrice_Standard"
                    PLCostPrice_Standard = ExcelWkSheet.Cells(i, 4).value
                Case "UnitPackDescription"
                    PLUnitPackDescription = ExcelWkSheet.Cells(i, 4).value
                Case "ANACode"
                    PLANACode = ExcelWkSheet.Cells(i, 4).value
                Case "UnitPackQuantity"
                    PLUnitPackQuantity = ExcelWkSheet.Cells(i, 4).value
            End Select

            Select Case PriceListFile.ColumnE
                Case "ProductCode"
                    PLProductCode = ExcelWkSheet.Cells(i, 5).value
                Case "ProductName"
                    PLProductName = ExcelWkSheet.Cells(i, 5).value
                Case "SellingPrice_RRP"
                    PLSellingPrice_RRP = ExcelWkSheet.Cells(i, 5).value
                Case "CostPrice_Standard"
                    PLCostPrice_Standard = ExcelWkSheet.Cells(i, 5).value
                Case "UnitPackDescription"
                    PLUnitPackDescription = ExcelWkSheet.Cells(i, 5).value
                Case "ANACode"
                    PLANACode = ExcelWkSheet.Cells(i, 5).value
                Case "UnitPackQuantity"
                    PLUnitPackQuantity = ExcelWkSheet.Cells(i, 5).value
            End Select

            Select Case PriceListFile.ColumnF
                Case "ProductCode"
                    PLProductCode = ExcelWkSheet.Cells(i, 6).value
                Case "ProductName"
                    PLProductName = ExcelWkSheet.Cells(i, 6).value
                Case "SellingPrice_RRP"
                    PLSellingPrice_RRP = ExcelWkSheet.Cells(i, 6).value
                Case "CostPrice_Standard"
                    PLCostPrice_Standard = ExcelWkSheet.Cells(i, 6).value
                Case "UnitPackDescription"
                    PLUnitPackDescription = ExcelWkSheet.Cells(i, 6).value
                Case "ANACode"
                    PLANACode = ExcelWkSheet.Cells(i, 6).value
                Case "UnitPackQuantity"
                    PLUnitPackQuantity = ExcelWkSheet.Cells(i, 6).value
            End Select

            Select Case PriceListFile.ColumnG
                Case "ProductCode"
                    PLProductCode = ExcelWkSheet.Cells(i, 7).value
                Case "ProductName"
                    PLProductName = ExcelWkSheet.Cells(i, 7).value
                Case "SellingPrice_RRP"
                    PLSellingPrice_RRP = ExcelWkSheet.Cells(i, 7).value
                Case "CostPrice_Standard"
                    PLCostPrice_Standard = ExcelWkSheet.Cells(i, 7).value
                Case "UnitPackDescription"
                    PLUnitPackDescription = ExcelWkSheet.Cells(i, 7).value
                Case "ANACode"
                    PLANACode = ExcelWkSheet.Cells(i, 7).value
                Case "UnitPackQuantity"
                    PLUnitPackQuantity = ExcelWkSheet.Cells(i, 7).value
            End Select

            Select Case PriceListFile.ColumnH
                Case "ProductCode"
                    PLProductCode = ExcelWkSheet.Cells(i, 8).value
                Case "ProductName"
                    PLProductName = ExcelWkSheet.Cells(i, 8).value
                Case "SellingPrice_RRP"
                    PLSellingPrice_RRP = ExcelWkSheet.Cells(i, 8).value
                Case "CostPrice_Standard"
                    PLCostPrice_Standard = ExcelWkSheet.Cells(i, 8).value
                Case "UnitPackDescription"
                    PLUnitPackDescription = ExcelWkSheet.Cells(i, 8).value
                Case "ANACode"
                    PLANACode = ExcelWkSheet.Cells(i, 8).value
                Case "UnitPackQuantity"
                    PLUnitPackQuantity = ExcelWkSheet.Cells(i, 8).value
            End Select

            Select Case PriceListFile.ColumnI
                Case "ProductCode"
                    PLProductCode = ExcelWkSheet.Cells(i, 9).value
                Case "ProductName"
                    PLProductName = ExcelWkSheet.Cells(i, 9).value
                Case "SellingPrice_RRP"
                    PLSellingPrice_RRP = ExcelWkSheet.Cells(i, 9).value
                Case "CostPrice_Standard"
                    PLCostPrice_Standard = ExcelWkSheet.Cells(i, 9).value
                Case "UnitPackDescription"
                    PLUnitPackDescription = ExcelWkSheet.Cells(i, 9).value
                Case "ANACode"
                    PLANACode = ExcelWkSheet.Cells(i, 9).value
                Case "UnitPackQuantity"
                    PLUnitPackQuantity = ExcelWkSheet.Cells(i, 9).value
            End Select

            Select Case PriceListFile.ColumnJ
                Case "ProductCode"
                    PLProductCode = ExcelWkSheet.Cells(i, 10).value
                Case "ProductName"
                    PLProductName = ExcelWkSheet.Cells(i, 10).value
                Case "SellingPrice_RRP"
                    PLSellingPrice_RRP = ExcelWkSheet.Cells(i, 10).value
                Case "CostPrice_Standard"
                    PLCostPrice_Standard = ExcelWkSheet.Cells(i, 10).value
                Case "UnitPackDescription"
                    PLUnitPackDescription = ExcelWkSheet.Cells(i, 10).value
                Case "ANACode"
                    PLANACode = ExcelWkSheet.Cells(i, 10).value
                Case "UnitPackQuantity"
                    PLUnitPackQuantity = ExcelWkSheet.Cells(i, 10).value
            End Select

            ' Write the pricelist details to the console for debugging
            Call WritePLToConsole()

            ' Call the process to check if the product is in our database
            Call CheckPMADB()

            ' Loop round and read the next row in the spreadsheet
        Next i

        ' Once finished, close the spreadsheet
        ExcelWkBook.Close()
        ExcelApp.Quit()

    End Sub

And here is the CheckPMADB module that it is throwing the error on the statement SQLDBConnection.Open()
;

Public Sub CheckPMADB()

        ' Search our database for the current product from the supplier pricelist.
        SQLDBQuery = "SELECT ProductCode, ProductName, SellingPrice_RRP, CostPrice_Standard, UnitPackDescription," & _
        "  ANACode, UnitPackQuantity, SellingPrice_Retail, SellingPrice1, PriceExVAT, VATRate, VATAmount, PriceIncVAT" & _
        " FROM Products" & _
        " WHERE ProductCode = '" & PLProductCode & "'"

        Console.WriteLine("PL Connection is: " & SQLPLConnection.State.ToString())
        Console.WriteLine("DB Connection is: " & SQLDBConnection.State.ToString())
        'Console.WriteLine(SQLConn.State.ToString())
        Console.ReadKey()

        SQLDBConnection.Open()

        Dim sqldbcomm As New SqlCommand(SQLDBQuery, SQLDBConnection)

        SQLDBReader = sqldbcomm.ExecuteReader
        While SQLDBReader.Read()

            ' Having found a matching product, we now need to map the fields
            DBProductCode = SQLDBReader(0).ToString()
            DBProductName = SQLDBReader(1).ToString()
            DBSellingPrice_RRP = DecimalNull2Zero(SQLDBReader(2))
            DBCostPrice_Standard = DecimalNull2Zero(SQLDBReader(3))
            DBUnitPackDescription = SQLDBReader(4).ToString()
            DBANACode = SQLDBReader(5).ToString()
            DBUnitPackQuantity = DecimalNull2Zero(SQLDBReader(6))
            DBSellingPrice_retail = DecimalNull2Zero(SQLDBReader(7))
            DBSellingPrice1 = DecimalNull2Zero(SQLDBReader(8))
            DBPriceExVAT = DecimalNull2Zero(SQLDBReader(9))
            DBVATRate = DecimalNull2Zero(SQLDBReader(10))
            DBVATAmount = DecimalNull2Zero(SQLDBReader(11))
            DBPriceIncVAT = DecimalNull2Zero(SQLDBReader(12))


            ' Now process the prices and do the calculations
            Call PricingCalculations()






            Console.WriteLine()
            Console.WriteLine("Product Record Found for Product: " & DBProductCode)
            Console.WriteLine(PLProductCode & " " & DBProductCode)
            Console.WriteLine(PLProductName & " " & DBProductName)
            Console.WriteLine(PLSellingPrice_RRP & " " & DBSellingPrice_RRP)
            Console.WriteLine(PLCostPrice_Standard & " " & DBCostPrice_Standard)
            Console.WriteLine(PLUnitPackDescription & " " & DBUnitPackDescription)
            Console.WriteLine(PLANACode & " " & DBANACode)
            Console.WriteLine(PLUnitPackQuantity & " " & DBUnitPackQuantity)
            Console.WriteLine(SQLDBReader(7) & " " & DBArray(7))
            Console.WriteLine(SQLDBReader(8) & " " & DBArray(8))
            Console.WriteLine(SQLDBReader(9))
            Console.WriteLine(SQLDBReader(10))
            Console.WriteLine(SQLDBReader(11))
            Console.WriteLine(SQLDBReader(12))
            Console.WriteLine("Press Any Key To Continue!")
            Console.ReadKey()

            ' Close the connection
            SQLDBReader.Close()


        End While

        

    End Sub

There are one or two other processes, like the price calculations and updating the products table with the new prices where necessary which arent shown here, but the core problem here is in reading the data.

Once again, any help greatly appreciated!