I'm having a little trouble with a program that i've written in Visual Basic 6 that doesn't want to work well with .NET. Its a connection to an Oracle database that pulls a count of providers from the database and spits out a file showing number of providers by state. Here is the code from VB6 that works perfectly:
Option Explicit
Dim OracleConn As ADODB.Connection
Dim connstring As String 'connection for the update/insert
Dim UDConnString As String 'Connection for the recordset
Dim OracleRS As New ADODB.Recordset
Private Sub Command1_Click()
connstring = "DSN=ServerDB; uid=kevin; pwd=avalon"
Set OracleConn = New ADODB.Connection
With OracleConn
.ConnectionString = connstring
.CursorLocation = adUseClient
.CommandTimeout = 300
.Open
End With
Dim products As String, provtype As String
'Begin if statements for product code
If chkWC.Value = 1 And chkGH.Value = 0 And chkAuto.Value = 0 Then
products = "'W' OR product_code = 'F' OR product_code = 'E' OR product_code = 'A')"
End If
If chkGH.Value = 1 And chkWC.Value = 0 And chkAuto.Value = 0 Then
products = "'B' OR product_code = 'A' OR product_code = 'D' OR product_code = 'E' "
products = products + "OR product_code = 'I')"
End If
If chkAuto.Value = 1 And chkWC.Value = 0 And chkGH.Value = 0 Then
products = "'C' OR product_code = 'D' OR product_code = 'F' OR product_code = 'A')"
End If
If chkWC.Value = 1 And chkGH.Value = 1 And chkAuto.Value = 0 Then
products = "'A' OR product_code = 'B' OR product_code = 'D' OR product_code = 'E' "
products = products + "OR product_code = 'F' OR product_code = 'I' OR product_code = 'W')"
End If
If chkGH.Value = 1 And chkAuto.Value = 1 And chkWC.Value = 0 Then
products = "'A' OR product_code = 'B' OR product_code = 'C' OR product_code = 'D' "
products = products + "OR product_code = 'E' OR product_code = 'F' OR product_code = 'I')"
End If
If chkWC.Value = 1 And chkAuto.Value = 1 And chkGH.Value = 0 Then
products = "'A' OR product_code = 'C' OR product_code = 'D' OR product_code = 'E' "
products = products + "OR product_code = 'F' OR product_code = 'W')"
End If
If chkWC.Value = 1 And chkGH.Value = 1 And chkAuto.Value = 1 Then
products = "'A' OR product_code = 'B' OR product_code = 'C' OR product_code = 'D' "
products = products + "OR product_code = 'E' OR product_code = 'F' "
products = products + "OR product_code = 'I' OR product_code = 'W')"
End If
'Begin If statements for provider_type
If chkPhysician.Value = 1 And chkAncillary.Value = 0 And chkHospital.Value = 0 Then
provtype = "3)"
End If
If chkPhysician.Value = 0 And chkAncillary.Value = 1 And chkHospital.Value = 0 Then
provtype = "2)"
End If
If chkPhysician.Value = 0 And chkAncillary.Value = 0 And chkHospital.Value = 1 Then
provtype = "1)"
End If
If chkPhysician.Value = 1 And chkAncillary.Value = 1 And chkHospital.Value = 0 Then
provtype = "3 OR provider_type = 2)"
End If
If chkPhysician.Value = 0 And chkAncillary.Value = 1 And chkHospital.Value = 1 Then
provtype = "2 OR provider_type = 1)"
End If
If chkPhysician.Value = 1 And chkAncillary.Value = 0 And chkHospital.Value = 1 Then
provtype = "3 OR provider_type = 1)"
End If
If chkPhysician.Value = 1 And chkAncillary.Value = 1 And chkHospital.Value = 1 Then
provtype = "3 OR provider_type = 2 OR provider_type = 1)"
End If
Command1.Enabled = False
Open "C:\" + txtFileLocation.Text + ".txt" For Output As #1
Dim state(0 To 104) As String, count As Integer, Qry As String
Set OracleRS.ActiveConnection = OracleConn
OracleRS.Open ("Select Unique State from main_phs")
count = 0
OracleRS.MoveFirst
'While Not OracleRS.EOF
While count < 104
If OracleRS!state <> "" Then
state(count) = OracleRS!state
count = count + 1
OracleRS.MoveNext
End If
Wend
OracleRS.Close
count = 0
While count < UBound(state)
Set OracleRS.ActiveConnection = OracleConn
If state(count) <> "" Then
Qry = "Select * from Main_phs "
Qry = Qry + "WHERE status = 'A' AND state = '" + state(count) + "' "
Qry = Qry + "AND (provider_type = " + provtype + " AND network_code = 1 "
Qry = Qry + "AND (product_code = " + products + ";"
OracleRS.Open (Qry)
Print #1, state(count); "|"; CStr(OracleRS.RecordCount)
count = count + 1
OracleRS.Close
End If
Wend
OracleConn.Close
Close #1
Command1.Enabled = True
End Sub
Now when i try this is .NET i get all kinds of problems, most of which i've already fixed. Here is the updated code in .NET:
Option Explicit On
Public Class Form1
Inherits System.Windows.Forms.Form
#Region " Windows Form Designer generated code "
Public Sub New()
MyBase.New()
'This call is required by the Windows Form Designer.
InitializeComponent()
'Add any initialization after the InitializeComponent() call
End Sub
'Form overrides dispose to clean up the component list.
Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
If disposing Then
If Not (components Is Nothing) Then
components.Dispose()
End If
End If
MyBase.Dispose(disposing)
End Sub
'Required by the Windows Form Designer
Private components As System.ComponentModel.IContainer
'NOTE: The following procedure is required by the Windows Form Designer
'It can be modified using the Windows Form Designer.
'Do not modify it using the code editor.
Friend WithEvents Command1 As System.Windows.Forms.Button
Friend WithEvents txtFileLocation As System.Windows.Forms.TextBox
Friend WithEvents chkPhysician As System.Windows.Forms.CheckBox
Friend WithEvents chkAncillary As System.Windows.Forms.CheckBox
Friend WithEvents chkHospital As System.Windows.Forms.CheckBox
Friend WithEvents chkWC As System.Windows.Forms.CheckBox
Friend WithEvents chkGH As System.Windows.Forms.CheckBox
Friend WithEvents Label1 As System.Windows.Forms.Label
Friend WithEvents chkAuto As System.Windows.Forms.CheckBox
Friend WithEvents PictureBox1 As System.Windows.Forms.PictureBox
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
Dim resources As System.Resources.ResourceManager = New System.Resources.ResourceManager(GetType(Form1))
Me.Command1 = New System.Windows.Forms.Button()
Me.txtFileLocation = New System.Windows.Forms.TextBox()
Me.chkPhysician = New System.Windows.Forms.CheckBox()
Me.chkAncillary = New System.Windows.Forms.CheckBox()
Me.chkHospital = New System.Windows.Forms.CheckBox()
Me.chkWC = New System.Windows.Forms.CheckBox()
Me.chkGH = New System.Windows.Forms.CheckBox()
Me.chkAuto = New System.Windows.Forms.CheckBox()
Me.Label1 = New System.Windows.Forms.Label()
Me.PictureBox1 = New System.Windows.Forms.PictureBox()
Me.SuspendLayout()
'
'Command1
'
Me.Command1.BackColor = System.Drawing.SystemColors.GrayText
Me.Command1.ForeColor = System.Drawing.SystemColors.ActiveCaptionText
Me.Command1.Location = New System.Drawing.Point(176, 160)
Me.Command1.Name = "Command1"
Me.Command1.TabIndex = 0
Me.Command1.Text = "Run"
'
'txtFileLocation
'
Me.txtFileLocation.Location = New System.Drawing.Point(144, 16)
Me.txtFileLocation.Name = "txtFileLocation"
Me.txtFileLocation.Size = New System.Drawing.Size(168, 20)
Me.txtFileLocation.TabIndex = 1
Me.txtFileLocation.Text = ""
'
'chkPhysician
'
Me.chkPhysician.Location = New System.Drawing.Point(40, 64)
Me.chkPhysician.Name = "chkPhysician"
Me.chkPhysician.TabIndex = 2
Me.chkPhysician.Text = "Physician"
'
'chkAncillary
'
Me.chkAncillary.Location = New System.Drawing.Point(40, 96)
Me.chkAncillary.Name = "chkAncillary"
Me.chkAncillary.TabIndex = 3
Me.chkAncillary.Text = "Facility"
'
'chkHospital
'
Me.chkHospital.Location = New System.Drawing.Point(40, 128)
Me.chkHospital.Name = "chkHospital"
Me.chkHospital.TabIndex = 4
Me.chkHospital.Text = "Hospital"
'
'chkWC
'
Me.chkWC.Location = New System.Drawing.Point(296, 64)
Me.chkWC.Name = "chkWC"
Me.chkWC.TabIndex = 5
Me.chkWC.Text = "Workers' Comp"
'
'chkGH
'
Me.chkGH.Location = New System.Drawing.Point(296, 96)
Me.chkGH.Name = "chkGH"
Me.chkGH.TabIndex = 6
Me.chkGH.Text = "Group Health"
'
'chkAuto
'
Me.chkAuto.Location = New System.Drawing.Point(296, 128)
Me.chkAuto.Name = "chkAuto"
Me.chkAuto.TabIndex = 7
Me.chkAuto.Text = "Auto"
'
'Label1
'
Me.Label1.Location = New System.Drawing.Point(40, 16)
Me.Label1.Name = "Label1"
Me.Label1.TabIndex = 8
Me.Label1.Text = "Enter Filename"
'
'PictureBox1
'
Me.PictureBox1.Image = CType(resources.GetObject("PictureBox1.Image"), System.Drawing.Bitmap)
Me.PictureBox1.Location = New System.Drawing.Point(-280, 200)
Me.PictureBox1.Name = "PictureBox1"
Me.PictureBox1.Size = New System.Drawing.Size(712, 48)
Me.PictureBox1.TabIndex = 9
Me.PictureBox1.TabStop = False
'
'Form1
'
Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
Me.BackColor = System.Drawing.SystemColors.InactiveCaption
Me.ClientSize = New System.Drawing.Size(432, 246)
Me.Controls.AddRange(New System.Windows.Forms.Control() {Me.PictureBox1, Me.Label1, Me.chkAuto, Me.chkGH, Me.chkWC, Me.chkHospital, Me.chkAncillary, Me.chkPhysician, Me.txtFileLocation, Me.Command1})
Me.Icon = CType(resources.GetObject("$this.Icon"), System.Drawing.Icon)
Me.Name = "Form1"
Me.Text = "Query Provider Count By State"
Me.ResumeLayout(False)
End Sub
#End Region
Dim OracleConn As ADODB.Connection
Dim connstring As String 'connection for the update/insert
Dim UDConnString As String 'Connection for the recordset
Dim OracleRS As New ADODB.Recordset()
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Command1.Click
connstring = "DSN=ServerDB; uid=kevin; pwd=avalon"
OracleConn = New ADODB.Connection()
With OracleConn
.ConnectionString = connstring
'.CursorLocation = adUseClient
.CommandTimeout = 300
.Open()
End With
Dim products As String, provtype As String
'Begin if statements for product code
If chkWC.Checked = True And chkGH.Checked = False And chkAuto.Checked = False Then
products = "'W' OR product_code = 'F' OR product_code = 'E' OR product_code = 'A')"
End If
If chkGH.Checked = True And chkWC.Checked = False And chkAuto.Checked = False Then
products = "'B' OR product_code = 'A' OR product_code = 'D' OR product_code = 'E' "
products = products + "OR product_code = 'I')"
End If
If chkAuto.Checked = True And chkWC.Checked = False And chkGH.Checked = False Then
products = "'C' OR product_code = 'D' OR product_code = 'F' OR product_code = 'A')"
End If
If chkWC.Checked = True And chkGH.Checked = True And chkAuto.Checked = False Then
products = "'A' OR product_code = 'B' OR product_code = 'D' OR product_code = 'E' "
products = products + "OR product_code = 'F' OR product_code = 'I' OR product_code = 'W')"
End If
If chkGH.Checked = True And chkAuto.Checked = True And chkWC.Checked = False Then
products = "'A' OR product_code = 'B' OR product_code = 'C' OR product_code = 'D' "
products = products + "OR product_code = 'E' OR product_code = 'F' OR product_code = 'I')"
End If
If chkWC.Checked = True And chkAuto.Checked = True And chkGH.Checked = False Then
products = "'A' OR product_code = 'C' OR product_code = 'D' OR product_code = 'E' "
products = products + "OR product_code = 'F' OR product_code = 'W')"
End If
If chkWC.Checked = True And chkGH.Checked = True And chkAuto.Checked = True Then
products = "'A' OR product_code = 'B' OR product_code = 'C' OR product_code = 'D' "
products = products + "OR product_code = 'E' OR product_code = 'F' "
products = products + "OR product_code = 'I' OR product_code = 'W')"
End If
'Begin If statements for provider_type
If chkPhysician.Checked = True And chkAncillary.Checked = False And chkHospital.Checked = False Then
provtype = "3)"
End If
If chkPhysician.Checked = False And chkAncillary.Checked = True And chkHospital.Checked = False Then
provtype = "2)"
End If
If chkPhysician.Checked = False And chkAncillary.Checked = False And chkHospital.Checked = True Then
provtype = "1)"
End If
If chkPhysician.Checked = True And chkAncillary.Checked = True And chkHospital.Checked = False Then
provtype = "3 OR provider_type = 2)"
End If
If chkPhysician.Checked = False And chkAncillary.Checked = True And chkHospital.Checked = True Then
provtype = "2 OR provider_type = 1)"
End If
If chkPhysician.Checked = True And chkAncillary.Checked = False And chkHospital.Checked = True Then
provtype = "3 OR provider_type = 1)"
End If
If chkPhysician.Checked = True And chkAncillary.Checked = True And chkHospital.Checked = True Then
provtype = "3 OR provider_type = 2 OR provider_type = 1)"
End If
Command1.Enabled = False
'OpenFile("C:\" + txtFileLocation.Text + ".txt", 1)
FileOpen(1, "C:\" + txtFileLocation.Text + ".txt", OpenMode.Append)
Dim state(104) As Object, bar As Object
Dim count As Integer
Dim Qry As Object, test As String
OracleRS.ActiveConnection = OracleConn
OracleRS.Open("Select Unique State from main_phs")
count = 0
OracleRS.MoveFirst()
'While Not OracleRS.EOF
While count < 104
'If Not System.DBNull(OracleRS(state)) Then
If Not OracleRS("state") Is "" Then
state(count) = OracleRS("state")
count = count + 1
OracleRS.MoveNext()
End If
End While
OracleRS.Close()
count = 0
While count < UBound(state)
OracleRS.ActiveConnection = OracleConn
If Not state(count) Is "" Then
test = state(count)
bar = "|"
Qry = "Select * from Main_phs " & _
"WHERE status = 'A' AND state = '" + state(count) + "' " & _
"AND (provider_type = " + provtype + " AND network_code = 1 " & _
"AND (product_code = " + products + ";"
OracleRS.Open(Qry)
Write(1, CObj(OracleRS("state")) + bar + CStr(OracleRS.RecordCount))
count = count + 1
OracleRS.Close()
End If
End While
OracleConn.Close()
FileClose(1)
Command1.Enabled = True
End Sub
End Class
Most everything works now, but it is not likeing the fact that i had to change the state variable to be an object as opposed to a string in VB6. Changing it to an object was the only way i could get it to compile, but when i run it an error occurs at the select statement. I take out the part with the state object variable and it doesn't error out. But then i get another error when it is trying to write out the file and use the state variable again. I'm just a little frustrated so any ideas would be great and appriciated. Sorry for dropping in so much code.
Option Explicit
Dim OracleConn As ADODB.Connection
Dim connstring As String 'connection for the update/insert
Dim UDConnString As String 'Connection for the recordset
Dim OracleRS As New ADODB.Recordset
Private Sub Command1_Click()
connstring = "DSN=ServerDB; uid=kevin; pwd=avalon"
Set OracleConn = New ADODB.Connection
With OracleConn
.ConnectionString = connstring
.CursorLocation = adUseClient
.CommandTimeout = 300
.Open
End With
Dim products As String, provtype As String
'Begin if statements for product code
If chkWC.Value = 1 And chkGH.Value = 0 And chkAuto.Value = 0 Then
products = "'W' OR product_code = 'F' OR product_code = 'E' OR product_code = 'A')"
End If
If chkGH.Value = 1 And chkWC.Value = 0 And chkAuto.Value = 0 Then
products = "'B' OR product_code = 'A' OR product_code = 'D' OR product_code = 'E' "
products = products + "OR product_code = 'I')"
End If
If chkAuto.Value = 1 And chkWC.Value = 0 And chkGH.Value = 0 Then
products = "'C' OR product_code = 'D' OR product_code = 'F' OR product_code = 'A')"
End If
If chkWC.Value = 1 And chkGH.Value = 1 And chkAuto.Value = 0 Then
products = "'A' OR product_code = 'B' OR product_code = 'D' OR product_code = 'E' "
products = products + "OR product_code = 'F' OR product_code = 'I' OR product_code = 'W')"
End If
If chkGH.Value = 1 And chkAuto.Value = 1 And chkWC.Value = 0 Then
products = "'A' OR product_code = 'B' OR product_code = 'C' OR product_code = 'D' "
products = products + "OR product_code = 'E' OR product_code = 'F' OR product_code = 'I')"
End If
If chkWC.Value = 1 And chkAuto.Value = 1 And chkGH.Value = 0 Then
products = "'A' OR product_code = 'C' OR product_code = 'D' OR product_code = 'E' "
products = products + "OR product_code = 'F' OR product_code = 'W')"
End If
If chkWC.Value = 1 And chkGH.Value = 1 And chkAuto.Value = 1 Then
products = "'A' OR product_code = 'B' OR product_code = 'C' OR product_code = 'D' "
products = products + "OR product_code = 'E' OR product_code = 'F' "
products = products + "OR product_code = 'I' OR product_code = 'W')"
End If
'Begin If statements for provider_type
If chkPhysician.Value = 1 And chkAncillary.Value = 0 And chkHospital.Value = 0 Then
provtype = "3)"
End If
If chkPhysician.Value = 0 And chkAncillary.Value = 1 And chkHospital.Value = 0 Then
provtype = "2)"
End If
If chkPhysician.Value = 0 And chkAncillary.Value = 0 And chkHospital.Value = 1 Then
provtype = "1)"
End If
If chkPhysician.Value = 1 And chkAncillary.Value = 1 And chkHospital.Value = 0 Then
provtype = "3 OR provider_type = 2)"
End If
If chkPhysician.Value = 0 And chkAncillary.Value = 1 And chkHospital.Value = 1 Then
provtype = "2 OR provider_type = 1)"
End If
If chkPhysician.Value = 1 And chkAncillary.Value = 0 And chkHospital.Value = 1 Then
provtype = "3 OR provider_type = 1)"
End If
If chkPhysician.Value = 1 And chkAncillary.Value = 1 And chkHospital.Value = 1 Then
provtype = "3 OR provider_type = 2 OR provider_type = 1)"
End If
Command1.Enabled = False
Open "C:\" + txtFileLocation.Text + ".txt" For Output As #1
Dim state(0 To 104) As String, count As Integer, Qry As String
Set OracleRS.ActiveConnection = OracleConn
OracleRS.Open ("Select Unique State from main_phs")
count = 0
OracleRS.MoveFirst
'While Not OracleRS.EOF
While count < 104
If OracleRS!state <> "" Then
state(count) = OracleRS!state
count = count + 1
OracleRS.MoveNext
End If
Wend
OracleRS.Close
count = 0
While count < UBound(state)
Set OracleRS.ActiveConnection = OracleConn
If state(count) <> "" Then
Qry = "Select * from Main_phs "
Qry = Qry + "WHERE status = 'A' AND state = '" + state(count) + "' "
Qry = Qry + "AND (provider_type = " + provtype + " AND network_code = 1 "
Qry = Qry + "AND (product_code = " + products + ";"
OracleRS.Open (Qry)
Print #1, state(count); "|"; CStr(OracleRS.RecordCount)
count = count + 1
OracleRS.Close
End If
Wend
OracleConn.Close
Close #1
Command1.Enabled = True
End Sub
Now when i try this is .NET i get all kinds of problems, most of which i've already fixed. Here is the updated code in .NET:
Option Explicit On
Public Class Form1
Inherits System.Windows.Forms.Form
#Region " Windows Form Designer generated code "
Public Sub New()
MyBase.New()
'This call is required by the Windows Form Designer.
InitializeComponent()
'Add any initialization after the InitializeComponent() call
End Sub
'Form overrides dispose to clean up the component list.
Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
If disposing Then
If Not (components Is Nothing) Then
components.Dispose()
End If
End If
MyBase.Dispose(disposing)
End Sub
'Required by the Windows Form Designer
Private components As System.ComponentModel.IContainer
'NOTE: The following procedure is required by the Windows Form Designer
'It can be modified using the Windows Form Designer.
'Do not modify it using the code editor.
Friend WithEvents Command1 As System.Windows.Forms.Button
Friend WithEvents txtFileLocation As System.Windows.Forms.TextBox
Friend WithEvents chkPhysician As System.Windows.Forms.CheckBox
Friend WithEvents chkAncillary As System.Windows.Forms.CheckBox
Friend WithEvents chkHospital As System.Windows.Forms.CheckBox
Friend WithEvents chkWC As System.Windows.Forms.CheckBox
Friend WithEvents chkGH As System.Windows.Forms.CheckBox
Friend WithEvents Label1 As System.Windows.Forms.Label
Friend WithEvents chkAuto As System.Windows.Forms.CheckBox
Friend WithEvents PictureBox1 As System.Windows.Forms.PictureBox
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
Dim resources As System.Resources.ResourceManager = New System.Resources.ResourceManager(GetType(Form1))
Me.Command1 = New System.Windows.Forms.Button()
Me.txtFileLocation = New System.Windows.Forms.TextBox()
Me.chkPhysician = New System.Windows.Forms.CheckBox()
Me.chkAncillary = New System.Windows.Forms.CheckBox()
Me.chkHospital = New System.Windows.Forms.CheckBox()
Me.chkWC = New System.Windows.Forms.CheckBox()
Me.chkGH = New System.Windows.Forms.CheckBox()
Me.chkAuto = New System.Windows.Forms.CheckBox()
Me.Label1 = New System.Windows.Forms.Label()
Me.PictureBox1 = New System.Windows.Forms.PictureBox()
Me.SuspendLayout()
'
'Command1
'
Me.Command1.BackColor = System.Drawing.SystemColors.GrayText
Me.Command1.ForeColor = System.Drawing.SystemColors.ActiveCaptionText
Me.Command1.Location = New System.Drawing.Point(176, 160)
Me.Command1.Name = "Command1"
Me.Command1.TabIndex = 0
Me.Command1.Text = "Run"
'
'txtFileLocation
'
Me.txtFileLocation.Location = New System.Drawing.Point(144, 16)
Me.txtFileLocation.Name = "txtFileLocation"
Me.txtFileLocation.Size = New System.Drawing.Size(168, 20)
Me.txtFileLocation.TabIndex = 1
Me.txtFileLocation.Text = ""
'
'chkPhysician
'
Me.chkPhysician.Location = New System.Drawing.Point(40, 64)
Me.chkPhysician.Name = "chkPhysician"
Me.chkPhysician.TabIndex = 2
Me.chkPhysician.Text = "Physician"
'
'chkAncillary
'
Me.chkAncillary.Location = New System.Drawing.Point(40, 96)
Me.chkAncillary.Name = "chkAncillary"
Me.chkAncillary.TabIndex = 3
Me.chkAncillary.Text = "Facility"
'
'chkHospital
'
Me.chkHospital.Location = New System.Drawing.Point(40, 128)
Me.chkHospital.Name = "chkHospital"
Me.chkHospital.TabIndex = 4
Me.chkHospital.Text = "Hospital"
'
'chkWC
'
Me.chkWC.Location = New System.Drawing.Point(296, 64)
Me.chkWC.Name = "chkWC"
Me.chkWC.TabIndex = 5
Me.chkWC.Text = "Workers' Comp"
'
'chkGH
'
Me.chkGH.Location = New System.Drawing.Point(296, 96)
Me.chkGH.Name = "chkGH"
Me.chkGH.TabIndex = 6
Me.chkGH.Text = "Group Health"
'
'chkAuto
'
Me.chkAuto.Location = New System.Drawing.Point(296, 128)
Me.chkAuto.Name = "chkAuto"
Me.chkAuto.TabIndex = 7
Me.chkAuto.Text = "Auto"
'
'Label1
'
Me.Label1.Location = New System.Drawing.Point(40, 16)
Me.Label1.Name = "Label1"
Me.Label1.TabIndex = 8
Me.Label1.Text = "Enter Filename"
'
'PictureBox1
'
Me.PictureBox1.Image = CType(resources.GetObject("PictureBox1.Image"), System.Drawing.Bitmap)
Me.PictureBox1.Location = New System.Drawing.Point(-280, 200)
Me.PictureBox1.Name = "PictureBox1"
Me.PictureBox1.Size = New System.Drawing.Size(712, 48)
Me.PictureBox1.TabIndex = 9
Me.PictureBox1.TabStop = False
'
'Form1
'
Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
Me.BackColor = System.Drawing.SystemColors.InactiveCaption
Me.ClientSize = New System.Drawing.Size(432, 246)
Me.Controls.AddRange(New System.Windows.Forms.Control() {Me.PictureBox1, Me.Label1, Me.chkAuto, Me.chkGH, Me.chkWC, Me.chkHospital, Me.chkAncillary, Me.chkPhysician, Me.txtFileLocation, Me.Command1})
Me.Icon = CType(resources.GetObject("$this.Icon"), System.Drawing.Icon)
Me.Name = "Form1"
Me.Text = "Query Provider Count By State"
Me.ResumeLayout(False)
End Sub
#End Region
Dim OracleConn As ADODB.Connection
Dim connstring As String 'connection for the update/insert
Dim UDConnString As String 'Connection for the recordset
Dim OracleRS As New ADODB.Recordset()
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Command1.Click
connstring = "DSN=ServerDB; uid=kevin; pwd=avalon"
OracleConn = New ADODB.Connection()
With OracleConn
.ConnectionString = connstring
'.CursorLocation = adUseClient
.CommandTimeout = 300
.Open()
End With
Dim products As String, provtype As String
'Begin if statements for product code
If chkWC.Checked = True And chkGH.Checked = False And chkAuto.Checked = False Then
products = "'W' OR product_code = 'F' OR product_code = 'E' OR product_code = 'A')"
End If
If chkGH.Checked = True And chkWC.Checked = False And chkAuto.Checked = False Then
products = "'B' OR product_code = 'A' OR product_code = 'D' OR product_code = 'E' "
products = products + "OR product_code = 'I')"
End If
If chkAuto.Checked = True And chkWC.Checked = False And chkGH.Checked = False Then
products = "'C' OR product_code = 'D' OR product_code = 'F' OR product_code = 'A')"
End If
If chkWC.Checked = True And chkGH.Checked = True And chkAuto.Checked = False Then
products = "'A' OR product_code = 'B' OR product_code = 'D' OR product_code = 'E' "
products = products + "OR product_code = 'F' OR product_code = 'I' OR product_code = 'W')"
End If
If chkGH.Checked = True And chkAuto.Checked = True And chkWC.Checked = False Then
products = "'A' OR product_code = 'B' OR product_code = 'C' OR product_code = 'D' "
products = products + "OR product_code = 'E' OR product_code = 'F' OR product_code = 'I')"
End If
If chkWC.Checked = True And chkAuto.Checked = True And chkGH.Checked = False Then
products = "'A' OR product_code = 'C' OR product_code = 'D' OR product_code = 'E' "
products = products + "OR product_code = 'F' OR product_code = 'W')"
End If
If chkWC.Checked = True And chkGH.Checked = True And chkAuto.Checked = True Then
products = "'A' OR product_code = 'B' OR product_code = 'C' OR product_code = 'D' "
products = products + "OR product_code = 'E' OR product_code = 'F' "
products = products + "OR product_code = 'I' OR product_code = 'W')"
End If
'Begin If statements for provider_type
If chkPhysician.Checked = True And chkAncillary.Checked = False And chkHospital.Checked = False Then
provtype = "3)"
End If
If chkPhysician.Checked = False And chkAncillary.Checked = True And chkHospital.Checked = False Then
provtype = "2)"
End If
If chkPhysician.Checked = False And chkAncillary.Checked = False And chkHospital.Checked = True Then
provtype = "1)"
End If
If chkPhysician.Checked = True And chkAncillary.Checked = True And chkHospital.Checked = False Then
provtype = "3 OR provider_type = 2)"
End If
If chkPhysician.Checked = False And chkAncillary.Checked = True And chkHospital.Checked = True Then
provtype = "2 OR provider_type = 1)"
End If
If chkPhysician.Checked = True And chkAncillary.Checked = False And chkHospital.Checked = True Then
provtype = "3 OR provider_type = 1)"
End If
If chkPhysician.Checked = True And chkAncillary.Checked = True And chkHospital.Checked = True Then
provtype = "3 OR provider_type = 2 OR provider_type = 1)"
End If
Command1.Enabled = False
'OpenFile("C:\" + txtFileLocation.Text + ".txt", 1)
FileOpen(1, "C:\" + txtFileLocation.Text + ".txt", OpenMode.Append)
Dim state(104) As Object, bar As Object
Dim count As Integer
Dim Qry As Object, test As String
OracleRS.ActiveConnection = OracleConn
OracleRS.Open("Select Unique State from main_phs")
count = 0
OracleRS.MoveFirst()
'While Not OracleRS.EOF
While count < 104
'If Not System.DBNull(OracleRS(state)) Then
If Not OracleRS("state") Is "" Then
state(count) = OracleRS("state")
count = count + 1
OracleRS.MoveNext()
End If
End While
OracleRS.Close()
count = 0
While count < UBound(state)
OracleRS.ActiveConnection = OracleConn
If Not state(count) Is "" Then
test = state(count)
bar = "|"
Qry = "Select * from Main_phs " & _
"WHERE status = 'A' AND state = '" + state(count) + "' " & _
"AND (provider_type = " + provtype + " AND network_code = 1 " & _
"AND (product_code = " + products + ";"
OracleRS.Open(Qry)
Write(1, CObj(OracleRS("state")) + bar + CStr(OracleRS.RecordCount))
count = count + 1
OracleRS.Close()
End If
End While
OracleConn.Close()
FileClose(1)
Command1.Enabled = True
End Sub
End Class
Most everything works now, but it is not likeing the fact that i had to change the state variable to be an object as opposed to a string in VB6. Changing it to an object was the only way i could get it to compile, but when i run it an error occurs at the select statement. I take out the part with the state object variable and it doesn't error out. But then i get another error when it is trying to write out the file and use the state variable again. I'm just a little frustrated so any ideas would be great and appriciated. Sorry for dropping in so much code.
