reuse sql connection and sqlcommand in vb.net windows application

Here is some declaration of variables:
 
Public Class Form1
        Inherits System.Windows.Forms.Form
        Dim logfilepath, MgtAnalysis As String
        Dim datafilepath As String
        Dim errorfilepath As String
        Dim logdate, logfilename, logmsg, prcsflag, feedday As String
        Dim logwriter As StreamWriter
        Dim errorflag As Boolean
        Dim connstrGRP As String
        Dim strsqlselect As String
        Dim BegMoSeq, EndYr, EndMo, EndMoSeq, FinalMoSeq As Integer
        Dim scBegMoSeq, scEndYr, scEndMo, scEndMoSeq, scFinalMoSeq, scUpdEndYrMo, scPA, scPA1 As New SqlCommand
        Dim mySqlConn As New SqlClient.SqlConnection
        Dim sdrPA1 As SqlClient.SqlDataReader
        Dim beginmonthseq, endmonthseq As Integer
        Dim rankcalcdone As Boolean   'IT0801
 
 
 
  Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            '********************************************************************************
            '***       Create/Write Log File Name                                         ***                                                                     
            '********************************************************************************
            Dim processerror As Boolean
 
            errorflag = False
            processerror = False
            rankcalcdone = False                                 'IT0801
            ' Get application config parameters
            Try
                logfilepath = System.Configuration.ConfigurationSettings.AppSettings("logfilepath").ToString
                datafilepath = System.Configuration.ConfigurationSettings.AppSettings("Datafilepath").ToString
                errorfilepath = System.Configuration.ConfigurationSettings.AppSettings("errorfilepath").ToString
                connstrGRP = System.Configuration.ConfigurationSettings.AppSettings("ConnectionString.connstrGRP").ToString()
                MgtAnalysis = System.Configuration.ConfigurationSettings.AppSettings("MgtAnalysis").ToString
 
                logdate = Date.Now.Year.ToString + Date.Now.Month.ToString + Date.Now.Day.ToString + Date.Now.Hour.ToString + Date.Now.Minute.ToString
                logfilename = logfilepath + "\" + "PresidentAwardMonth" + logdate + ".txt"
                logwriter = New StreamWriter(logfilename)
                logmsg = " ***** President Award Monthly Analysis Process Begins ***** " + Now.ToString & vbCrLf
                logging(logmsg, logwriter)
                logmsg = " " & vbCrLf
                logging(logmsg, logwriter)
            Catch ex As Exception
                errorflag = True
                logmsg = " "
                logmsg = "     Error in Reading App.config file  " + ex.Message + Date.Now.ToString & vbCrLf
                logging(logmsg, logwriter)
                processerror = True
            End Try
 
            If errorflag = True Then
                ' processerror()
            End If
        End Sub
 
(Note: Sub Processfiles is the "main" sub.  It is called from the click event of the button the user clicks when ready to launch the processing)
 
Sub Processfiles()
 
            logmsg = "Preparing the SQL connection for getting the data - Begins at " + Now.ToString & vbCrLf
            logging(logmsg, logwriter)
            logmsg = " " & vbCrLf
            logging(logmsg, logwriter)
            datafilepath = Trim(datafilepath) + "\"
            errorfilepath = Trim(errorfilepath) + "\"
 
            PrcsCalScore()
 
            ' if rank calc completed successfully, then call stored proc for revised ranking, to incorporate sales component
            If rankcalcdone = True Then                                                                  'IT0801
                PrcsRevisedRank()                                                                             'IT0801
            End If                                                                                                       'IT0801
 
            mySqlConn.Close()
            FileClose(1)
            Me.Dispose(True)
            Application.Exit()
 
        End Sub
 
 
 Sub PrcsCalScore()
            Dim mySqlConn As SqlConnection = New SqlConnection(connstrGRP)
            Dim selectCMD As SqlCommand = New SqlCommand(strsqlselect, mySqlConn)
            selectCMD.CommandTimeout = 1240
 
            mySqlConn.Open()
 
 
            ' TRUNCATE TABLE MONTH PRESIDENT AWARD RESULT
            selectCMD.CommandText = "Truncate Table MonthPresidentAwardResult "
           
            Dim custDA As SqlDataAdapter = New SqlDataAdapter
            custDA.SelectCommand = selectCMD
 
            Try
                selectCMD.ExecuteNonQuery()
                logmsg = " "
                logmsg = "     Truncated table MonthPresidentAwardResult  " + Date.Now.ToString & vbCrLf
                logging(logmsg, logwriter)
            Catch ex As Exception
                errorflag = True
                logmsg = " "
                logmsg = "     Error in truncating table MonthPresidentAwardResult  " + ex.Message + Date.Now.ToString & vbCrLf
                logging(logmsg, logwriter)
            End Try
 
            'Added 6/12/2009 to see if need to clear and Load PresidentAwardSales table with shopm2sale data
            'This will allow for one off processing
            'Be sure to comment this out if changes were made 
 
            Dim Answer As Integer
            Answer = MsgBox("Reload PresidentAwardSales Table?", vbYesNo + vbExclamation, "Load PresidentAwardSales")
 
            If Answer = 6 Then
                'Yes   
                scPA.CommandType = CommandType.StoredProcedure
                scPA.CommandText = "sp_Load_PresidentAwardSalesTable"
                Try
                    scPA.ExecuteNonQuery()
                    logmsg = " "
                    logmsg = "     Executed stored procedure sp_Load_PresidentAwardSalesTable  " + Date.Now.ToString & vbCrLf
                    logging(logmsg, logwriter)
                Catch ex As Exception
                    errorflag = True
                    logmsg = " "
                    logmsg = "     Error in Executing stored sp_Load_PresidentAwardSalesTable  " + ex.Message + Date.Now.ToString & vbCrLf
                    logging(logmsg, logwriter)
                End Try
 
            End If
 
            If Answer = 7 Then
                'No   
            End If
 
            BegMoSeq = beginmonthseq
            EndMoSeq = endmonthseq
 
            ' WRITE A RECORD FOR ALL SHOPS FROM SHOPINFO WITH NULL AS VALUE FOR SCORES, RANK
            scPA.CommandText = "Insert Into MonthPresidentAwardResult Select 0, 0, ShopNumber, Null, Null, " & _
             "Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, 1750 From ShopInfo"
            strsqlselect = scPA.CommandText
            Dim selectCMD2 As SqlCommand = New SqlCommand(strsqlselect, mySqlConn)
            selectCMD2.CommandTimeout = 30
            custDA.SelectCommand = selectCMD2
 
            Try
                selectCMD2.ExecuteNonQuery()
                logmsg = " "
                logmsg = "     Inserted shopinfo records into table MonthPresidentAwardResult  " + Date.Now.ToString & vbCrLf
                logging(logmsg, logwriter)
            Catch ex As Exception
                errorflag = True
                logmsg = " "
                logmsg = "     Error in Inserting shopinfo records into table MonthPresidentAwardResult CMD2  " + ex.Message + Date.Now.ToString & vbCrLf
                logging(logmsg, logwriter)
            End Try
 
            selectCMD2.CommandType = CommandType.StoredProcedure
            If MgtAnalysis = "Y" Then
                selectCMD2.CommandText = "sp_Build_PresidentAward_Data_cosmic"
 
            Else
                selectCMD2.CommandText = "sp_Build_PresidentAward_Data"
            End If
 
 
            selectCMD2.Parameters.Add("@BegMoSeq", SqlDbType.Int)
            selectCMD2.Parameters("@BegMoSeq").Value = BegMoSeq
            selectCMD2.Parameters("@BegMoSeq").Direction = ParameterDirection.Input
            selectCMD2.Parameters.Add("@EndMoSeq", SqlDbType.Int)
            selectCMD2.Parameters("@EndMoSeq").Value = EndMoSeq
            selectCMD2.Parameters("@EndMoSeq").Direction = ParameterDirection.Input
 
            Try
                selectCMD2.ExecuteNonQuery()
                logmsg = " "
                If MgtAnalysis = "Y" Then
                    logmsg = "     Executed stored procedure sp_build_presidentaward_data_cosmic  " + Date.Now.ToString & vbCrLf
                Else
                    logmsg = "     Executed stored procedure sp_build_presidentaward_data  " + Date.Now.ToString & vbCrLf
                End If
                logging(logmsg, logwriter)
            Catch ex As Exception
                errorflag = True
                logmsg = " "
                logmsg = "     Error in Executing stored procedure sp_build_presidentaward_data  " + ex.Message + Date.Now.ToString & vbCrLf
                logging(logmsg, logwriter)
            End Try
 
            ' CALCULATE RANK FOR EACH CRITERIA
 
            selectCMD.CommandText = "Select * From PresidentAwardItem"
            sdrPA1 = selectCMD.ExecuteReader
            If sdrPA1.HasRows Then
                While sdrPA1.Read
                    PrcsCreatePreAwdItemRank(sdrPA1("PreAwdItem"), sdrPA1("ScoreColName"), sdrPA1("MoCntColName"), _
                    sdrPA1("RankColName"), sdrPA1("RankSeq"))
                End While
                sdrPA1.Close()
                'GET OVERALL RANKING
                PrcsCreateOverallRank()
            End If
        End Sub
 
Sub PrcsCreateOverallRank()
            Dim scRankData As New SqlCommand
            Dim scUpdResultTable As New SqlCommand
            Dim ShopNum, MoCnt, RankCnt1, RankCnt2 As Integer
            Dim Score, ScoreHolder As Double
            Dim sdrRankData As SqlDataReader
            Dim custDA As SqlDataAdapter = New SqlDataAdapter
            Dim mySqlConn2 As SqlConnection = New SqlConnection(connstrGRP)
            Dim mySqlConn9 As SqlConnection = New SqlConnection(connstrGRP)
            mySqlConn2.Open()
            mySqlConn9.Open()
 
            scUpdResultTable.CommandText = "Update MonthPresidentAwardResult Set RankAvg = " & _
            "([CSIRank] + [FOTCRank] + [CustRetentionRank] + [CustComplaintRank]) / 4 " & _
            "Where CSIRank Is Not Null and FOTCRank Is Not Null and " & _
            "CustRetentionRank Is Not Null and CustComplaintRank Is Not Null"
            Dim strsqlselect1 As String = scUpdResultTable.CommandText
            Dim selectCMD12 As SqlCommand = New SqlCommand(strsqlselect1, mySqlConn2)
            selectCMD12.CommandTimeout = 30
            custDA.SelectCommand = selectCMD12
            Try
                selectCMD12.ExecuteNonQuery()
 
                RankCnt1 = 1
                RankCnt2 = 0
                ScoreHolder = 10000000.0 'Pick a number which no score will equal to as start
 
                scRankData.CommandText = "Select ShopNum, RankAvg From MonthPresidentAwardResult " & _
                "Where RankAvg is not Null Order By RankAvg"
                strsqlselect1 = scRankData.CommandText
                Dim selectCMD13 As SqlCommand = New SqlCommand(strsqlselect1, mySqlConn2)
                selectCMD13.CommandTimeout = 30
                custDA.SelectCommand = selectCMD13
                Try
                    sdrRankData = selectCMD13.ExecuteReader
                    While sdrRankData.Read
                        If sdrRankData("RankAvg") = ScoreHolder Then
                            RankCnt2 += 1
                        Else
                            RankCnt1 += RankCnt2
                            RankCnt2 = 1
                        End If
 
                        scUpdResultTable.CommandText = "Update MonthPresidentAwardResult Set " & _
                        "OverallRank = " & RankCnt1 & ", QualifyForCompetition = 'Yes' " & _
                        "Where MonthPresidentAwardResult.ShopNum = " & sdrRankData("ShopNum")
                        strsqlselect1 = scUpdResultTable.CommandText
                        Dim selectCMD14 As SqlCommand = New SqlCommand(strsqlselect1, mySqlConn9)
                        selectCMD14.CommandTimeout = 30
                        custDA.SelectCommand = selectCMD14
                        Try
                            selectCMD14.ExecuteNonQuery()
                            ScoreHolder = sdrRankData("RankAvg")
                        Catch ex As Exception
                            errorflag = True
                            logmsg = " "
                            logmsg = "     Error in updating MonthPresidentAwardResultSet with overall rank CMD14  " + ex.Message + Date.Now.ToString & vbCrLf
                            logging(logmsg, logwriter)
                        End Try
 
                    End While
                    sdrRankData.Close()
                    logmsg = " "
                    logmsg = "     Successfully updated Overall Rank  " + Date.Now.ToString & vbCrLf
                    logging(logmsg, logwriter)
                Catch ex As Exception
                    errorflag = True
                    logmsg = " "
                    logmsg = "     Error in obtaining records from MonthPresidentAwardResult CMD 13 in PrcsCreateOverallRank  " + ex.Message + Date.Now.ToString & vbCrLf
                    logging(logmsg, logwriter)
                End Try
 
                scUpdResultTable.CommandText = "Update MonthPresidentAwardResult Set QualifyForCompetition = 'No' " & _
                "Where RankAvg Is Null"
                strsqlselect1 = scUpdResultTable.CommandText
                Dim selectCMD15 As SqlCommand = New SqlCommand(strsqlselect1, mySqlConn9)
                selectCMD15.CommandTimeout = 30
                custDA.SelectCommand = selectCMD15
                Try
                    selectCMD15.ExecuteNonQuery()
                    rankcalcdone = True                                                                                      'IT0801
                Catch ex As Exception
                    errorflag = True
                    logmsg = " "
                    logmsg = "     Error in updating MonthPresidentAwardResult set qualify for competition where rankavg is null CMD15 in PrcsCreateOverallRankroutine  " + ex.Message + Date.Now.ToString & vbCrLf
                    logging(logmsg, logwriter)
                End Try
            Catch ex As Exception
                errorflag = True
                logmsg = " "
                logmsg = "     Error in PrcsCreateOverallRankroutine CMD 12  " + ex.Message + Date.Now.ToString & vbCrLf
                logging(logmsg, logwriter)
 
            End Try
            mySqlConn2.Close()
            mySqlConn9.Close()
        End Sub

                                  

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
124:
125:
126:
127:
128:
129:
130:
131:
132:
133:
134:
135:
136:
137:
138:
139:
140:
141:
142:
143:
144:
145:
146:
147:
148:
149:
150:
151:
152:
153:
154:
155:
156:
157:
158:
159:
160:
161:
162:
163:
164:
165:
166:
167:
168:
169:
170:
171:
172:
173:
174:
175:
176:
177:
178:
179:
180:
181:
182:
183:
184:
185:
186:
187:
188:
189:
190:
191:
192:
193:
194:
195:
196:
197:
198:
199:
200:
201:
202:
203:
204:
205:
206:
207:
208:
209:
210:
211:
212:
213:
214:
215:
216:
217:
218:
219:
220:
221:
222:
223:
224:
225:
226:
227:
228:
229:
230:
231:
232:
233:
234:
235:
236:
237:
238:
239:
240:
241:
242:
243:
244:
245:
246:
247:
248:
249:
250:
251:
252:
253:
254:
255:
256:
257:
258:
259:
260:
261:
262:
263:
264:
265:
266:
267:
268:
269:
270:
271:
272:
273:
274:
275:
276:
277:
278:
279:
280:
281:
282:
283:
284:
285:
286:
287:
288:
289:
290:
291:
292:
293:
294:
295:
296:
297:
298:
299:
300:
301:
302:
303:
304:
305:
306: