数据库操作与查询技巧-优快云博客

导入函数调用模块

Imports System.Data

Imports System.Data.OleDb

Imports System.Text

Imports System.IO

添加删除更新模块 此模块直接运行，不需要返回表(记录)

Public Sub exenonselect(ByVal sql As String)

Try

Dim constr As String = "provider=microsoft.jet.oledb.4.0;data source=" & Application.StartupPath & "\xwgl.mdb;jet oledb:database password=lihua"

Dim con As OleDbConnection = New OleDbConnection(constr)

con.Open()

Dim cmdexe As OleDbCommand = New OleDbCommand(sql, con)

cmdexe.ExecuteNonQuery()

con.Close()

cmdexe.Dispose()

con.Dispose()

Catch ex As Exception

MessageBox.Show("数据链接失败，请与开发商联系", MessageBoxButtons.OK, MessageBoxIcon.Information)

Application.Exit()

End Try

End Sub

查询模块 此模块用来查询，返回查询信息

Public Function exeselect(ByVal sql As String) As DataTable

Try

Dim constr As String = "provider=microsoft.jet.oledb.4.0;data source=" & Application.StartupPath & "\xwgl.mdb;jet oledb:database password=lihua"

Dim con As OleDbConnection = New OleDbConnection(constr)

con.Open()

Dim da As OleDbDataAdapter = New OleDbDataAdapter(sql, con)

Dim dt As New DataTable

da.Fill(dt)

con.Close()

con.Dispose()

Return dt

dt.Dispose()

da.Dispose()

Catch ex As Exception

MessageBox.Show("数据链接失败，请与开发商联系" , MessageBoxButtons.OK, MessageBoxIcon.Information)

Application.Exit()

Return New DataTable

End Try

End Function

加密模块

'自行加密

Public Function lhjm(ByVal s As String, ByVal pwds As String) As String

Dim charsingle As String = String.Empty

Dim charnum As Integer

Dim charkey As String = String.Empty

Dim s1 As String = String.Empty

Dim i As Long

For i = 1 To Len(s)

charsingle = Mid(s, i, 1)

charnum = Asc(charsingle)

If charnum <> 0 And charnum <> 8 And charnum <> 9 And charnum <> 127 Then

'加密

charkey = Mid(pwds, (i Mod Len(pwds)) + 1, 1)

charnum = Asc(charsingle) Xor (Asc(charkey) And &H7F)

If charnum <> 0 And charnum <> 8 And charnum <> 9 And charnum <> 127 Then

s1 += Chr(charnum)

Else

s1 += charsingle

End If

Else

s1 += charsingle

End If

lhjm = s1

End Function

'MD5加密

Public Function pwdjm(ByVal s As String) As String

Try

Dim a() As Byte = (New System.Text.UnicodeEncoding).GetBytes(s)

Dim b() As Byte = CType(System.Security.Cryptography.CryptoConfig.CreateFromName("md5"), System.Security.Cryptography.HashAlgorithm).ComputeHash(a)

Dim s1 As String = String.Empty

For i As Integer = 0 To b.Length - 1 Step 2

s1 += Hex(b(i)).ToString

Return s1

Catch ex As Exception

MessageBox.Show("数据链接失败，请与开发商联系", MessageBoxButtons.OK, MessageBoxIcon.Information)

Application.Exit()

Return String.Empty

End Try

End Function

获取数据库中的表到列表框中

Private Sub bmb_bind()

Dim i As Integer

Dim con As OleDbConnection = New OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=" & Application.StartupPath & "\xwgl.mdb;jet oledb:database password=lihua")

con.Open()

Dim dt As DataTable = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "table"})

Me.ComboBox1.Items.Clear()

For i = 0 To dt.Rows.Count - 1

If dt.Rows(i).Item(2).ToString.Substring(0, 2) = "xy" Then

Me.ComboBox1.Items.Add(dt.Rows(i).Item(2).ToString.Substring(2, 4) + "年学员信息报名管理表")

End If

dt.Dispose()

con.Close()

con.Dispose()

End Sub

获取当前年的表

Private Sub studfrm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

bmb_bind()

'判断当前年的表是否存在

Dim i As Integer

Dim bj As String = 0

For i = 0 To Me.ComboBox1.Items.Count - 1

If Year(Now()).ToString = Me.ComboBox1.Items(i).ToString.Substring(0, 4) Then

bj = 1

Exit For

End If

'如果不存在，则自动创建

If bj = 0 Then

Try

Dim dt1 As New DataTable

dt1 = lihua.exeselect("select * into [xy" & Trim(Str(Year(Now()))) & "] from [xueyuan]")

dt1.Dispose()

bmb_bind()

Catch ex As Exception

Exit Sub

End Try

End If

Me.ComboBox1.Text = Trim(Str(Year(Now()))) + "年学员信息报名管理表"

End Sub

自定义编号

Dim ybh As String = String.Empty '已存在的编号

Dim i As Integer

For i = 0 To Me.DataGridView1.Rows.Count - 1

ybh += Me.DataGridView1.Item(1, i).Value & "*"

Dim bh As String = ""

For i = 1 To 9999

If i < 10 Then

bh = "000" + Trim(Str(i))

ElseIf i < 100 Then

bh = "00" + Trim(Str(i))

ElseIf i < 1000 Then

bh = "0" + Trim(Str(i))

Else

bh = Trim(Str(i))

End If

bh = Me.ComboBox1.Text.ToString.Substring(0, 4) & "" & bh & ""

If InStr(ybh, bh) = 0 Then

Exit For

End If

Me.lbbmh.Text = "" & bh & ""

Exit Sub

End If

文本框控制输入

If (e.KeyChar < Chr(48) Or e.KeyChar > Chr(57)) And e.KeyChar <> Chr(13) And e.KeyChar <> Chr(8) Then

e.KeyChar = Nothing

End If

动态变换欠费

If Me.btmod.Text = "保存(&S)" Then

Me.tbqf.Text = Str(Val(Me.DataGridView1.Item(9, Me.DataGridView1.CurrentCell.RowIndex).Value) - Val(Me.tbjf.Text))

If Me.tbqf.Text < 0 Then

MessageBox.Show("欠费不能小于0，请输入正确的格式")

Me.tbjf.Text = ""

Me.tbjf.Focus()

End If

多条件查询

If Me.tbselval.Text = "" Then

MessageBox.Show("查询的值不能为空")

Me.tbselval.Focus()

Exit Sub

End If

If (Me.cbselyj.Text = "报名时间" Or Me.cbselyj.Text = "修改时间") And Me.cbsellx.Text <> "按月查询" Then

If IsDate(Me.tbselval.Text) = False Then

MessageBox.Show("请输入正确的日期格式: 如2010-6-12")

Me.tbselval.Focus()

Me.tbselval.SelectAll()

Exit Sub

End If

If Me.cbsellx.Text = "按月查询" Then

If Val(Me.tbselval.Text) < 1 Or Val(Me.tbselval.Text) > 12 Then

MessageBox.Show("请输入正确的月份，1-12 之间")

Me.tbselval.Focus()

Me.tbselval.SelectAll()

Exit Sub

End If

Dim s As String = ""

获取选择的依据

Select Case Me.cbselyj.Text

Case "报名时间", "修改时间"

If Me.cbsellx.Text = "按月查询" Then

s = "month(" & Me.cbselyj.Text & ")"

Else

s = Me.cbselyj.Text

End If

Case Else

s = Me.cbselyj.Text

End Select

获取选择的类型

Select Case Me.cbsellx.Text

Case "包含"

s = s + " like '%" & Me.tbselval.Text & "%'"

Case "不包含"

s = s + " not like '%" & Me.tbselval.Text & "%'"

Case "等于"

If Me.cbselyj.Text = "性别" Then

s = s + "= '" & Me.tbselval.Text & "'"

ElseIf Me.cbselyj.Text = "报名时间" Or Me.cbselyj.Text = "修改时间" Then

s = s + "= #" & Me.tbselval.Text & "#"

ElseIf Me.cbselyj.Text = "欠费" Then

s = s + "=" & Me.tbselval.Text

End If

Case "按月查询"

s = s + "=" & Me.tbselval.Text

Case "大于"

If Me.cbselyj.Text = "报名时间" Or Me.cbselyj.Text = "修改时间" Then

s = s + ">#" & Me.tbselval.Text & "#"

ElseIf Me.cbselyj.Text = "欠费" Then

s = s + ">" & Me.tbselval.Text

End If

Case "大于等于"

If Me.cbselyj.Text = "报名时间" Or Me.cbselyj.Text = "修改时间" Then

s = s + ">=#" & Me.tbselval.Text & "#"

ElseIf Me.cbselyj.Text = "欠费" Then

s = s + ">=" & Me.tbselval.Text

End If

Case "小于"

If Me.cbselyj.Text = "报名时间" Or Me.cbselyj.Text = "修改时间" Then

s = s + "<#" & Me.tbselval.Text & "#"

ElseIf Me.cbselyj.Text = "欠费" Then

s = s + "<" & Me.tbselval.Text

End If

Case "小于等于"

If Me.cbselyj.Text = "报名时间" Or Me.cbselyj.Text = "修改时间" Then

s = s + "<=#" & Me.tbselval.Text & "#"

ElseIf Me.cbselyj.Text = "欠费" Then

s = s + "<=" & Me.tbselval.Text

End If

End Select

根据添加条件来进行判断

tjnum = tjnum + 1

s1 += "条件：" & Trim(Str(tjnum)) & " " & Me.cbselyj.Text & " " & Me.cbsellx.Text & " " & Me.tbselval.Text + vbNewLine

如果条件数量大于1的话即多条记录

If tjnum > 1 Then

cxtj = cxtj + IIf(Me.cborand.Text = "并且", " and ", " or ")

End If

cxtj += s

cxtj = "(" & cxtj & ")"

Me.tbwb.Text = s1 + vbNewLine + cxtj '文本显示 date_bd()

转载于:https://www.cnblogs.com/dmh365/archive/2011/04/18/2019587.html

VB.net项目源码