我有 5 個搜索 SQLite 資料庫的 Select 陳述句，搜索變數建立在
一個名為 frmBuildSearches 的表單上，并傳遞給一個帶有 DataGridView
的表單我試圖克服的問題是我需要為每個構建的搜索型別設定 5 個子例程
我想將 5 個搜索陳述句放在一個子例程中，當呼叫該子例程時，決定
將使用哪個搜索陳述句。這是我第一次使用“使用”陳述句
這是挑戰開始，經過大量反復試驗后停止
下面的代碼是一個子程式，其中 4 個搜索陳述句被注釋掉
問題如何嵌入搜索陳述句在一個帶有“使用”陳述句的子程式中？
Private Sub MoRangeYr()
Dim intID As Integer
Dim strDate As String
Dim strTxType As String
Dim strAmt As Decimal
Dim strCKNum As String
Dim strDesc As String
Dim strBal As Decimal
Dim rowCount As Integer
Dim maxRowCount As Integer
Dim emptyStr As String = " "
Using conn As New SQLiteConnection($"Data Source = '{gv_dbName}';Version=3;")
conn.Open()
'========================
'Designed Searches Below
'All
'Using cmd As SQLiteCommand = New SQLiteCommand($"SELECT * FROM TxData", conn)
'MoYr
'Using cmd As SQLiteCommand = New SQLiteCommand($"SELECT * FROM TxData WHERE txSearchMonth = '{gvFromMonth}' AND txYear = '{gvYear}' ", conn)
'TxMoYr
'Using cmd As SQLiteCommand = New SQLiteCommand($"SELECT * FROM TxData WHERE txType = '{gvTxType}' AND txSearchMonth = '{gvFromMonth}'AND txYear = '{gvYear}' ", conn)
'Year
'Using cmd As SQLiteCommand = New SQLiteCommand($"SELECT * FROM TxData WHERE txYear = '{gvYear}' ", conn)
'=========================
'MoRangeYr
Using cmd As SQLiteCommand = New SQLiteCommand($"SELECT * FROM TxData WHERE txSearchMonth = '{gvFromMonth}' AND txSearchMonth = '{gvToMonth}' AND txYear = '{gvYear}' ", conn)
Using rdr As SQLite.SQLiteDataReader = cmd.ExecuteReader
While rdr.Read()
intID = CInt((rdr("TID")))
strDate = rdr("txSortDate").ToString
strTxType = rdr("txType").ToString
strAmt = CDec(rdr("txAmount"))
strCKNum = rdr("txCKNum").ToString
strDesc = rdr("txDesc").ToString
strBal = CDec(rdr("txBalance"))
dgvTX.Columns(3).DefaultCellStyle.Format = "N"
dgvTX.Columns(6).DefaultCellStyle.Format = "N"
'dgvTX.Columns(6).DefaultCellStyle.Format = "C"'Adds the $ sign and commas
dgvTX.Rows.Add(intID, strDate, strTxType, strAmt, strCKNum, strDesc, strBal, emptyStr)
Dim dateToday = Date.Today
Dim lblDate As String = dateToday.ToString("MMM" & " " & "d" & " " & "yyyy")
lblAmt.Text = "Current Balance " & lblDate
tbBal.Text = "$ " & String.Format("{0:n}", strBal)
rowCount = rowCount 1
End While
dgvTX.Sort(dgvTX.Columns(0), ListSortDirection.Descending)
If rowCount <= 25 Then
maxRowCount = 25 - rowCount
For iA = 1 To maxRowCount
dgvTX.Rows.Add(" ")
Next
End If
rdr.Close()
End Using
End Using
conn.Close()
End Using
tbBal.Focus()
End Sub
此代碼決定使用哪個子路由
Private Sub frmViewTX_Load(sender As Object, e As EventArgs) Handles MyBase.Load
StyleDGV()
If gvTEST Is "All" Then
PopulateDGV()
ElseIf gvTEST Is "MoYr" Then
JustOne()
ElseIf gvTEST Is "Year" Then
YearOnly()
ElseIf gvTEST Is "TxMoYr" Then
TxMoYr()
ElseIf gvTEST Is "MoRangeYr" Then
MoRangeYr()
End If
'MoRangeYr
End Sub
uj5u.com熱心網友回復：
正如我已經說過的，確保使用引數化查詢！順便說一下，這個問題的基本答案是您可以CommandText在創建SQLiteCommand物件和實際運行命令之間更改屬性：
Private Sub MoRangeYr()
Using conn As New SQLiteConnection($"Data Source = '{gv_dbName}';Version=3;")
Using cmd As New SQLiteCommand("",conn)
If gvTEST = "ALL" Then
cmd.CommandText = "SELECT * FROM TxData"
Else If gvTEST = "MoYr" Then
cmd.CommandText = "SELECT * FROM TxData WHERE txSearchMonth = $gvFromMonth' AND txYear = $gvYear "
cmd.Parameters.AddWithValue("$gvFromMonth", gvFromMonth)
cmd.Parameters.AddWithValue("$gvYear", gvYear)
Else If gvTEST = "TxMoYr" Then
cmd.CommandText = "SELECT * FROM TxData WHERE txType = $gvTxType AND txSearchMonth = $gvFromMonth AND txYear = $gvYear "
cmd.Parameters.AddWithValue("$gvTxType", gvTxType)
cmd.Parameters.AddWithValue("$gvFromMonth", gvFromMonth)
cmd.Parameters.AddWithValue("$gvYear", gvYear)
Else If gvTEST = "MoRangeYr" Then
cmd.CommandText = "SELECT * FROM TxData WHERE txYear = $gvYear "
cmd.Parameters.AddWithValue("$gvYear", gvYear)
End If
conn.Open()
Using rdr As SQLite.SQLiteDataReader = cmd.ExecuteReader
dgvTX.DataSource = rdr
dgvTX.Columns(3).DefaultCellStyle.Format = "N"
dgvTX.Columns(6).DefaultCellStyle.Format = "N"
End Using
lblAmt.Text = $"Current Balance {DateTime.Today:MMMM d yyyy}"
' tbBal.Text = "${strBal:n}" ' This was wrong in the original code
dgvTX.Sort(dgvTX.Columns(0), ListSortDirection.Descending)
End Using
End Using
End Sub
您可能還想查看 DataBinding 這個結果。您正在撰寫大量額外的代碼來完成 GridView 知道如何為您完成的作業。
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/314898.html