在将数据保存到数据库之前,我想使用此代码来验证是否发生重复。我该如何关闭sqldatareader
?(正如错误告诉我的那样)
con.ConnectionString = "Data Source=PC85AAIEw\SQLEXPRESS;Initial Catalog=Student;Integrated Security=True"
cmd.Connection = con
con.Open()
Dim theQuery As String = "SELECT * FROM Profile WHERE RollNo=@RollNo AND Name=@Name"
Dim cmd1 As SqlCommand = New SqlCommand(theQuery, con)
cmd1.Parameters.AddWithValue("@RollNo", TextBox1.Text)
cmd1.Parameters.AddWithValue("@Name", TextBox2.Text)
Using reader As SqlDataReader = cmd1.ExecuteReader()
If reader.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 & "')"
MessageBox.Show("Profile has been successfully registered!", "Thank you", MessageBoxButtons.OK)
i = cmd.ExecuteNonQuery()
End If
End Using
con.Close()
所指的错误是因为您必须先完成数据读取器的执行,然后才能尝试在同一连接上执行另一个命令。
此外,您的代码存在一些问题:
AddWithValue
添加参数时不要使用,而是使用构造函数并指定sql数据类型。如果RollNo
是一个数字(如整数),那么您应该将该值作为整数传递给您的参数。我认为它是一个存储在varchar
.IDisposable
在Using
语句中实现的所有类型以确保资源始终被释放。(如果有人想挑剔,SqlCommand
在这种情况下不需要。)Dim recordExists As Boolean
Using con As SqlConnection = New SqlConnection("Data Source=PC85AAIEw\SQLEXPRESS;Initial Catalog=Student;Integrated Security=True")
Using cmd As SqlCommand = New SqlCommand("SELECT RollNo FROM Profile WHERE RollNo=@RollNo AND Name=@Name", con)
cmd.Parameters.Add("@RollNo", SqlDbType.VarChar).Value = TextBox1.Text
cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = TextBox2.Text
con.Open()
Using reader As SqlDataReader = cmd.ExecuteReader()
recordExists = reader.HasRows
End Using
End Using
End Using
If recordExists Then
MessageBox.Show("User already registered! Please try again.", "Error", MessageBoxButtons.OK)
Else
Using con As SqlConnection = New SqlConnection("Data Source=PC85AAIEw\SQLEXPRESS;Initial Catalog=Student;Integrated Security=True")
Using cmd As SqlCommand = New SqlCommand("INSERT INTO Profile (RollNo, Name) VALUES (@RollNo, @Name)", con)
cmd.Parameters.Add("@RollNo", SqlDbType.VarChar).Value = TextBox1.Text
cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = TextBox2.Text
con.Open()
cmd.ExecuteNonQuery()
MessageBox.Show("Profile has been successfully registered!", "Thank you", MessageBoxButtons.OK)
End Using
End Using
End If
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句