如何执行 rd.HasRow正确而不影响将照片上传到数据库?

Question

我正在尝试使用rd.HasRow方法验证输入的数据是否重复，然后再将其保存到数据库。

如果它是重复的，则应该弹出错误消息框而不是保存数据。

我应该如何执行它以及我用来将照片上传到数据库的代码？如果我注释这部分代码，输入的数据(不重复(可以保存到数据库中，但照片不会随之上传。

'i = cmd.ExecuteNonQuery()
'If i >= 1 Then
'MessageBox.Show("Profile successfully registered!", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information)
'Else
'MessageBox.Show("Error. Please try again later.", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information)
'End If

但是如果我不这样做，则不会保存用户输入的数据，并且会针对i=cmd.ExecuteNonQuery()弹出此错误消息：

System.InvalidOperationException："已经有一个与此命令关联的打开的 DataReader，必须先关闭它。

这是整体代码。

Private Sub button2_Click(sender As Object, e As EventArgs) Handles button2.Click
Dim con As New SqlConnection
Dim cmd As New SqlCommand
Dim rollno As String
Dim name As String
Dim gender As String
Dim address As String
Dim phoneno As Integer
Dim datereg As String
Dim faculty As String
Dim course As String
Dim semester As String
Dim i As Integer
Dim j As Integer
rollno = TextBox1.Text
name = TextBox2.Text
gender = ComboBox4.Text
address = TextBox3.Text
phoneno = TextBox4.Text
datereg = dateTimePicker1.Value
faculty = comboBox1.Text
course = comboBox2.Text
semester = comboBox3.Text
con.ConnectionString = "Data Source=LAPTOP-85ALBAVSSQLEXPRESS;Initial Catalog=Portal;Integrated Security=True"
cmd.Connection = con
con.Open()
'To validate whether duplication of typed in data by user occurs or not, if yes, error msg pop-up. If no, proceed and save the data into database
Dim rd As SqlDataReader
cmd.CommandText = "SELECT * FROM Profile WHERE RollNo= '" & TextBox1.Text & "' and Name='" & TextBox2.Text & "'"
rd = cmd.ExecuteReader()
If rd.HasRows Then
MessageBox.Show("User already registered! Please try again.", "Error", MessageBoxButtons.OK)
Else
cmd.CommandText = "INSERT INTO Profile VALUES ('" & rollno & "' , '" & name & "' , '" & gender & "' , '" & address & "' , '" & phoneno & "' , '" & datereg & "' , '" & faculty & "' , '" & course & "' , '" & semester & "')"
End If
'i = cmd.ExecuteNonQuery()
'If i >= 1 Then
'MessageBox.Show("Profile successfully registered!", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information)
'Else
'MessageBox.Show("Error. Please try again later.", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information)
'End If
con.Close()
con.Open()
'To save the uploaded photo to table Photo
Dim command As New SqlCommand("Insert into Photo (Img, Pid) Values (@Img, @Pid)", con)
command.Connection = con
Dim ms As New MemoryStream
pictureBox1.Image.Save(ms, pictureBox1.Image.RawFormat)
command.Parameters.Add("@Img", SqlDbType.Image).Value = ms.ToArray()
command.Parameters.Add("@Pid", SqlDbType.VarChar).Value = TextBox1.Text
j = cmd.ExecuteNonQuery()
If j >= 1 Then
MessageBox.Show("Profile successfully registered!", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information)
Else
MessageBox.Show("Error. Please try again later.", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information)
End If
End Sub

Answer 1

代码看起来有点混乱，至少根据我的经验，调试混乱的代码可能很困难。我们可以做一些事情来纠正这一点，我现在将尝试与您一起这样做。

首先，为控件指定有意义的名称。可以通过窗体上的设计来选择控件并更改Name属性来执行此操作。这将极大地帮助您通过代码引用它们。在这种情况下，它还将帮助您消除对变量的需求。

考虑使用：

有时，代码需要非托管资源，例如文件句柄、COM 包装器或 SQL 连接。Using 块保证在代码完成处理时处置一个或多个此类资源。这使得它们可供其他代码使用。

这将帮助您管理声明和资源，同时创建更清晰的代码图片。

我还会考虑将每个命令分解为自己Using块，以使您的代码更清晰。

将数据插入数据库时，请考虑使用 SQL 参数以避免 SQL 注入。

最后进入代码，让我们依次看一下每个Using块。

首先，我将首先在Using块中启动SqlConnection，然后我们可以为每个命令使用该连接：

Using con As New SqlConnection("Data Source=LAPTOP-85ALBAVSSQLEXPRESS;Initial Catalog=Portal;Integrated Security=True")
con.Open()
'Add the rest of the code here
End Using

检查记录是否存在：

在这里，考虑声明一个Boolean变量，我们用它来确定记录是否存在。

Dim recordExists As Boolean = False
Using cmd As New SqlCommand("SELECT * FROM Profile WHERE RollNo = @RollNo AND Name = @Name", con)
cmd.Parameters.Add("@RollNo", SqlDbType.[Type]).Value = txtRollNo.Text
cmd.Parameters.Add("@Name", SqlDbType.[Type]).Value = txtName.Text
Using reader As SqlDataReader = cmd.ExecuteReader()
recordExists = reader.HasRows
End Using
End Using

如果记录存在，则显示提示，如果不存在，则插入到数据库中：

If recordExists Then
MessageBox.Show("User already registered! Please try again.", "Error", MessageBoxButtons.OK)
Else
Using cmd As New SqlCommand("INSERT INTO Profile VALUES (@RollNo, @Name, @Gender, @Address, @PhoneNo, @DateReg, @Faculty, @Course, @Semester)", con)
cmd.Parameters.Add("@RollNo", SqlDbType.[Type]).Value = txtRollNo.Text
cmd.Parameters.Add("@Name", SqlDbType.[Type]).Value = txtName.Text
cmd.Parameters.Add("@Gender", SqlDbType.[Type]).Value = cboGender.Text
cmd.Parameters.Add("@Address", SqlDbType.[Type]).Value = txtAddress.Text
cmd.Parameters.Add("@PhoneNo", SqlDbType.[Type]).Value = txtPhoneNo.Text
cmd.Parameters.Add("@DateReg", SqlDbType.[Type]).Value = dtpDateReg.Value
cmd.Parameters.Add("@Faculty", SqlDbType.[Type]).Value = cboFaculty.Text
cmd.Parameters.Add("@Course", SqlDbType.[Type]).Value = cboCourse.Text
cmd.Parameters.Add("@Semester", SqlDbType.[Type]).Value = cboSemster.Text
If cmd.ExecuteNonQuery() > 0 Then
MessageBox.Show("Profile successfully registered!", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information)
Else
MessageBox.Show("Error. Please try again later.", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information)
End If
End Using
End If

插入图像：

Using cmd As New SqlCommand("INSERT INTO Photo (Img, Pid) VALUES (@Img, @Pid)", con)
Using ms As New MemoryStream()
pbxImage.Image.Save(ms, pbxImage.Image.RawFormat)
cmd.Parameters.Add("@Img", SqlDbType.Image).Value = ms.ToArray()
cmd.Parameters.Add("@Pid", SqlDbType.VarChar).Value = txtName.Text
End Using
cmd.ExecuteNonQuery()
End Using

请注意，我在不确定数据库中的数据类型的情况下使用了SqlDbType.[Type]。您需要将其替换为为每列指定的数据类型。

总之，您的代码将如下所示：

Using con As New SqlConnection("Data Source=LAPTOP-85ALBAVSSQLEXPRESS;Initial Catalog=Portal;Integrated Security=True")
con.Open()
Dim recordExists As Boolean = False
Using cmd As New SqlCommand("SELECT * FROM Profile WHERE RollNo = @RollNo AND Name = @Name", con)
cmd.Parameters.Add("@RollNo", SqlDbType.VarChar).Value = txtRollNo.Text
cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = txtName.Text
Using reader As SqlDataReader = cmd.ExecuteReader()
recordExists = reader.HasRows
End Using
End Using
If recordExists Then
MessageBox.Show("User already registered! Please try again.", "Error", MessageBoxButtons.OK)
Else
Using cmd As New SqlCommand("INSERT INTO Profile VALUES (@RollNo, @Name, @Gender, @Address, @PhoneNo, @DateReg, @Faculty, @Course, @Semester)", con)
cmd.Parameters.Add("@RollNo", SqlDbType.[Type]).Value = txtRollNo.Text
cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = txtName.Text
cmd.Parameters.Add("@Gender", SqlDbType.VarChar).Value = cboGender.Text
cmd.Parameters.Add("@Address", SqlDbType.VarChar).Value = txtAddress.Text
cmd.Parameters.Add("@PhoneNo", SqlDbType.VarChar).Value = txtPhoneNo.Text
cmd.Parameters.Add("@DateReg", SqlDbType.VarChar).Value = dtpDateReg.Value
cmd.Parameters.Add("@Faculty", SqlDbType.VarChar).Value = cboFaculty.Text
cmd.Parameters.Add("@Course", SqlDbType.VarChar).Value = cboCourse.Text
cmd.Parameters.Add("@Semester", SqlDbType.VarChar).Value = cboSemster.Text
con.Open()
If cmd.ExecuteNonQuery() > 0 Then
MessageBox.Show("Profile successfully registered!", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information)
Else
MessageBox.Show("Error. Please try again later.", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information)
End If
End Using
End If
Using cmd As New SqlCommand("INSERT INTO Photo (Img, Pid) VALUES (@Img, @Pid)", con)
Using ms As New MemoryStream()
pbxImage.Image.Save(ms, pbxImage.Image.RawFormat)
cmd.Parameters.Add("@Img", SqlDbType.Image).Value = ms.ToArray()
cmd.Parameters.Add("@Pid", SqlDbType.VarChar).Value = txtName.Text
End Using
con.Open()
cmd.ExecuteNonQuery()
End Using
End Using

^{这段代码未经测试，我没有环境，但它应该给你一些可以使用的东西。}

Answer 2

注释和解释。

Private Sub OPCode()
Dim i As Integer
Dim j As Integer
Dim rollno = TextBox1.Text
Dim name = TextBox2.Text
Dim gender = ComboBox4.Text
Dim address = TextBox3.Text
Dim phoneno = CInt(TextBox4.Text) 'Unless your phone numbers are very different
'than the phone numbers here, the likelyhood of a user entering just numbers is
'nil. Change this to a string and a VarChar in the database
Dim datereg = dateTimePicker1.Value
Dim faculty = comboBox1.Text
Dim course = ComboBox2.Text
Dim semester = ComboBox3.Text
'The Using block ensures that your connection is closed and disposed
'Pass your connection string to the constructor of the connection
Using con As New SqlConnection("Data Source=LAPTOP-85ALBAVSSQLEXPRESS;Initial Catalog=Portal;Integrated Security=True")
'Pass the Sql command text and connection to the Constructor of the command.
'NEVER, NEVER, NEVER allow user input to be passed directly to a database. Always use parameters.
Dim cmd As New SqlCommand("SELECT * FROM Profile WHERE RollNo= @RollNo and [Name]= @Name;", con)
cmd.Parameters.Add("@RollNo", SqlDbType.VarChar).Value = rollno
cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = name
con.Open()
Using rd As SqlDataReader = cmd.ExecuteReader()
'To validate whether duplication of typed in data by user occurs or not, if yes, error msg pop-up. If no, proceed and save the data into database
If rd.HasRows Then
MessageBox.Show("User already registered! Please try again.", "Error", MessageBoxButtons.OK)
'You don't want to go any further if the user is registered.
Exit Sub
End If
End Using
'Just use another new command variable to avoid confusion
'I think it is much better practice to list the fields.
Dim cmd2 As New SqlCommand("INSERT INTO Profile VALUES (@RollNo ,@Name,@Gender, @Address, @PhoneNo , @DateReg , @Faculty , @Course , @Semester);", con)
cmd2.Parameters.Add() 'etc.
i = cmd2.ExecuteNonQuery()
If i >= 1 Then
MessageBox.Show("Profile successfully registered!", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information)
Else
MessageBox.Show("Error. Please try again later.", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information)
Exit Sub
End If
'To save the uploaded photo to table Photo
Dim command3 As New SqlCommand("Insert into Photo (Img, Pid) Values (@Img, @Pid)", con)
command3.Connection = con
Dim ms As New MemoryStream
pictureBox1.Image.Save(ms, pictureBox1.Image.RawFormat)
command3.Parameters.Add("@Img", SqlDbType.Image).Value = ms.ToArray()
command3.Parameters.Add("@Pid", SqlDbType.VarChar).Value = TextBox1.Text
j = command3.ExecuteNonQuery()
End Using
If j >= 1 Then
MessageBox.Show("Profile successfully registered!", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information)
Else
MessageBox.Show("Error. Please try again later.", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information)
End If
End Sub