全局模块。 ADODB连接和SQL Server
问题描述:
请帮助我。我在这里呆了好几个星期。我不知道如何解决它。全局模块。 ADODB连接和SQL Server
这里是我的连接代码:
Imports System.Text.RegularExpressions
Module globalmodule
Public conn As New ADODB.Connection
Public rs As New ADODB.Recordset
Public rss As New ADODB.Recordset
Public trs As New ADODB.Recordset
Public sql As String
Public Function opendb()
If conn.State = 1 Then conn.Close()
conn.Open("Provider=SQLOLEDB.1;Data Source=ACER;Initial Catalog=dbEmployee;Integrated Security=True;")
Return 0
End Function
Function EmailAddressCheck(ByVal emailAddress As String) As Boolean
Dim pattern As String = "^[a-zA-Z][\w\.-]*[a-zA-Z0-9]@[a-zA-Z0-9][\w\.-]*[a-zA-Z0-9]\.[a-zA-Z][a-zA-Z\.]*[a-zA-Z]$"
Dim emailAddressMatch As Match = Regex.Match(emailAddress, pattern)
If emailAddressMatch.Success Then
EmailAddressCheck = True
Else
EmailAddressCheck = False
End If
If EmailAddressCheck = False Then
MsgBox("Entervalid E-mail ID")
End If
End Function
Public empid As String
End Module
表1这是关系到全球的模块形式。
下面的代码:
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
opendb()
End Sub
Private Sub btnlogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnlogin.Click
If cmbutype.Text = "Employee" Then
sql = "select * from employee where empcode='" & txtuname.Text & "' and password='" & txtupass.Text & "'"
If rs.State = 1 Then
rs.Close()
rs.Open(sql, conn)
If rs.EOF = False Then
MDIMain.MasterToolStripMenuItem.Visible = False
MDIMain.EmployeeToolStripMenuItem.Visible = False
MDIMain.SearchToolStripMenuItem.Visible = False
MDIMain.LeaveToolStripMenuItem.Visible = False
MDIMain.EarnToolStripMenuItem.Visible = False
MDIMain.DeductionToolStripMenuItem.Visible = False
MDIMain.events.Visible = False
empid = txtuname.Text
' MsgBox("login sucess")
MDIMain.Show()
Me.Hide()
End If
Else
sql = "select * from login where utypt='" & cmbutype.Text & "' and uname='" & txtuname.Text & "'"
If rs.State = 1 Then
rs.Close()
rs.Open(sql, conn)
If rs.EOF = False Then
sql = "select * from login where utypt='" & cmbutype.Text & "' and uname='" & txtuname.Text & "' and upass='" & txtupass.Text & "'"
If rs.State = 1 Then
rs.Close()
rs.Open(sql, conn)
If rs.EOF = False Then
' MsgBox("login sucess")
MDIMain.Show()
Me.Hide()
Else
MsgBox("Incorrect password ")
End If
Else
MsgBox("login failed")
End If
End If
End Sub
我得到约adodb.connection错误是命名空间中的歧义“ADODB”和adodb.recordsets是命名空间中的暧昧“ADODB”
答
我会从此功能开始:
Public Function opendb()
If conn.State = 1 Then conn.Close()
conn.Open("Provider=SQLOLEDB.1;Data Source=ACER;Initial Catalog=dbEmployee;Integrated Security=True;")
Return 0
End Function
VB.Net不是VBScript/VB6。 VB.Net中的所有函数应具有返回类型。此外,Sql Server的最佳实践是不要一遍又一遍地重复使用相同的连接对象。这打破了驱动程序进行有效连接池的能力。所以你希望功能看起来更像这样:
'Using ADO.Net objects here because I'm more familiar, and the old ADO objects are really only for backwards compatibility with old code anyway
Public Function opendb() As SqlConnection
'ADO.Net connection string may be slightly different
Dim result As New SqlConnection("Provider=SQLOLEDB.1;Data Source=ACER;Initial Catalog=dbEmployee;Integrated Security=True;")
result.Open()
Return result
End Function
现在让我们来看看登录代码。现在,我将留下一个巨大的安全问题,以纯文本形式存储密码(不要这么做!),而是专注于sql注入问题和基本连接。
Private Sub btnlogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnlogin.Click
Dim sql As String = ""
If cmbutype.Text = "Employee" Then
sql = "select * from employee where empcode= @Username AND password= @password"
Else
sql = "select * from login where utypt= @utype and uname= @username"
End If
Using cn As SqlConnection = opendb(), _
cmd As New SqlCommand(sql, cn)
'Guessing at column types/lengths for all of these parameters
cmd.Parameters.Add("@username", SqlDbType.NVarChar, 50).Value = txtuname.Text
cmd.Parameters.Add("@password", SqlDbType.NVarChar, 64).Value = txtupass.Text
cmd.Parameters.Add("@utype", SqlDbType.VarChar, 15).Value = cmbutype.Text
cn.Open()
Dim rdr = cmd.ExecuteReader()
If Not rdr.Read() Then
MsgBox("Login Failed")
Exit Sub
End If
If cmbutype.Text <> "Employee" AndAlso rdr("upass").ToString() <> txtupass.Text Then
MsgBox("Password Incorrect")
Exit Sub
End If
End Using
' MsgBox("login sucess")
If cmbutype.Text = "Employee" Then
MDIMain.MasterToolStripMenuItem.Visible = False
MDIMain.EmployeeToolStripMenuItem.Visible = False
MDIMain.SearchToolStripMenuItem.Visible = False
MDIMain.LeaveToolStripMenuItem.Visible = False
MDIMain.EarnToolStripMenuItem.Visible = False
MDIMain.DeductionToolStripMenuItem.Visible = False
MDIMain.events.Visible = False
empid = txtuname.Text
End If
MDIMain.Show()
Me.Hide()
End Sub
说明我是能够大大简化代码(嵌套少,并结合一些逻辑的),因为ADO.Net可以让你比SQL命令文本实际使用提供更多的查询参数。旧的ADODB不能这样做,因为它只使用位置参数。
Gah。 sql注入安全漏洞,它烧伤我们! –
此外,请打开Option Infer或Option Strict –
如何打开? – Newbee