如何对现有数据进行多次检查? [英] How do I use multiple checking for existing data ?
问题描述
我已经为使用VB.net和SQL 2008 express的院系做了注册页面。用户需要输入其详细信息才能注册。但是我想在INSERT进入SQL数据库之前检查现有数据。
但我收到错误。错误:DataReader已经使用Command打开,需要先关闭。
我正在添加代码。请检查并纠正我。
谢谢你的代码项目。
我试过的:
尝试
尝试
尝试
OpenConnection()
query = SELECT * from [faculty] WHERE id ='& TextBox1.Text& '
command = 新 SqlCommand(查询,连接)
reader = command.ExecuteReader
如果 reader.HasRows 那么
Label25.Text = *徽章ID已经存在,请尝试另一种。
Label25.ForeColor = Color.Red
Else
Label25.Text = * Mendatory字段不能留空。
Label25.ForeColor = Color.Black
结束 如果
c onnection.Close()
Catch ex As 异常
MsgBox(ex.Message) )
最后
command.Dispose()
结束 尝试
OpenConnection()
query = SELECT * from [faculty] WHERE username ='& TextBox10.Text& '
command = 新 SqlCommand(查询,连接)
reader = command.ExecuteReader
如果 reader.HasRows 那么
Label25.Text = *用户名已经注册,请尝试另一个。
Label25.ForeColor = Color.Red
Else
Label25.Text = * Mendatory字段不能留空。
Label25.ForeColor = Color.Black
结束 如果
connection.Close()
Catch ex As 异常
MsgBox(ex.Message)
最后
command.Dispose()
结束 尝试
尝试
OpenConnection()
Dim defText 作为 字符串 = 未修改 .ToString
Dim ms 作为 新 System.IO.MemoryStream
Dim 命令正如 新 SqlComma nd( INSERT into [faculty](id,name,dob,sex,addr,city,state,pin,country ,NAT,手机,电子邮件,部门,子,用户名,密码,安全,答案,形象,真实,创建,修改)值(@ ID,@名,@ DOB,@性别,@地址,@城市,@状态, @销@国@ NAT,@手机,@电子邮件@部门,@子,@用户名,@密码,@安全,@答案,@头像,@正宗,@创建,@修改) ,连接)
PictureBox1.Image.Save(ms,PictureBox1.Image.RawFormat)
command.Parameters.Add( @ id,SqlDbType.VarChar).Value = TextBox1.Text
command.Parameters.Add( < span class =code-string> @ name,SqlDbType.VarChar).Value = TextBox2.Text
command.Parameters.Add( @ dob,SqlDbType。日期)。值= D ateTimePicker1.Value。日期
Dim 性别 As 字符串
Dim 值 As 整数
gender = ComboBox1.Text
如果 gender = < span class =code-string> 男性 然后
值= 1
ElseIf gender = 女性 然后
value = 2
ElseIf gender = 其他 然后
value = 3
结束 如果
command.Parameters.Add( @ sex,SqlDbType.Int).Value = value
command.Parameters.Add( @ addr,SqlDbType.VarChar).Value = TextBox3.Text
command.Parameters.Add( @ city,SqlDbType.VarChar) .Value = TextBox4.Text
command.Parameters.Add( @ state,SqlDbType .VarChar)。Value = TextBox5.Text
command.Parameters.Add( @pin,SqlDbType.Int).Value = TextBox14.Text
command.Parameters.Add( @ country,SqlDbType.VarChar).Value = TextBox6.Text
command.Parameters.Add( @ nat,SqlDbType.VarChar).Value = TextBox7.Text
command.Parameters.Add( @ mobile,SqlDbType.VarChar).Value = TextBox8.Text
command.Parameters.Add(< span class =code-string> @ email,SqlDbType.VarChar).Value = TextBox9.Text
command.Parameters。添加( @ dept,SqlDbType.VarChar).Value = ComboBox3.Text
命令.Parameters.Add( @ sub,SqlDbType.VarChar).Value = ComboBox4.Text
command.Parameters.Add( @ username,SqlDbType.VarChar).Value = TextBox10.Text
command.Parameters.AddWithValue( @ password,加密(TextBox11.Text.Trim()))
Dim question 作为 字符串
Dim data 作为 整数
question = ComboBox2.Text
如果 question = 您的昵称是什么? 然后
data = 5
ElseIf question = 您的宠物名称是什么? 然后
data = 4
ElseIf question = 您的出生地是什么? 然后
data = 3
ElseIf question = 你的中间名是什么? 然后
data = 2
ElseIf question = 你的童年英雄是谁? 然后
data = 1
结束 如果
command.Parameters.Add( @ security,SqlDbType.Int).Value = data
command.Parameters.AddWithValue( @ answer,加密(TextBox13.Text.Trim()))
command.Parameters.Add( @ created,SqlDbType.VarChar).Value = 日期 .Today
command.Parameters.Add( @ modified,SqlDbType.VarChar) .Value = defText
command.Parameters.Add( @ avatar,SqlDbType.Image).Value = ms.ToArray
command.Parameters.Add( @ authentic,SqlDbType.Int).Value = 1
如果 command.ExecuteNonQuery()= 1 然后
MsgBox( 已成功注册新教师。)
其他
MsgBox( 我们'无法接受注册。请检查您提交的表单。)
结束 如果
connection.Close()
Catch ex As 例外
Label25 .Text =( *个人资料图片不能留空。)
最后
command.Dispose()
结束 尝试
Catch ex 作为例外
MsgBox(ex.Message) )
结束 尝试
完成后你必须关闭阅读器使用它:
reader = command.ExecuteReader
' 在这里使用读者......
'
' 完成工作后关闭阅读器
reader.Close()
另请参阅 SqlDataReader.Close方法(System.Data.SqlClient) [ ^ ]:
Quote:当您使用SqlDataReader将关联的SqlConnection用于任何其他目的时,必须显式调用Close方法。
当你打开一个DataReader,它设置建立SQL Server中的结果集的链接,并且当您使用Read函数时,每行仅被提取到您的计算机。
如果不关闭命令,则SQL Server保留结果集打开,并且不会接受进一步的请求,因为它不知道你不再查看早期的请求。
此外,SqlConnections ,SqlCommands和SqlDataAdapters是稀缺资源,你应该在完成它们后立即关闭和处理它们。一种方法是在你的尝试
的Finally
块中执行此操作,但更好的方法是在使用
块,当它们超出范围时自动处理项目:
使用con作为新的SqlConnection(strConnect)
con.Open()
使用cmd作为新的SqlCommand(SELECT iD,description FROM myTable,con)
使用reader As SqlDataReader = cmd.ExecuteReader()
虽然读者。 Read()
Dim id__1 As Integer = CInt(reader(iD))
Dim desc As String = DirectCast(reader(description),String)
Console.WriteLine( ID:{0}& vbLf&{1},iD,desc)
结束时
结束使用
结束使用
结束使用
Well I have made a registration page for faculties using VB.net and SQL 2008 express. Where users needs to enter their details to register. But I'm trying to check for existing data before INSERT into SQL database.
But I'm getting error. Error: "DataReader already opened with Command which needs to close first".
I'm adding the code. Please do check and correct me.
Thankyou codeproject.
What I have tried:
Try
Try
Try
OpenConnection()
query = "SELECT * from [faculty] WHERE id='" & TextBox1.Text & "'"
command = New SqlCommand(query, connection)
reader = command.ExecuteReader
If reader.HasRows Then
Label25.Text = "* Badge ID already exists, try another."
Label25.ForeColor = Color.Red
Else
Label25.Text = "* Mendatory fields cannot be left blank."
Label25.ForeColor = Color.Black
End If
connection.Close()
Catch ex As Exception
MsgBox(ex.Message)
Finally
command.Dispose()
End Try
OpenConnection()
query = "SELECT * from [faculty] WHERE username='" & TextBox10.Text & "'"
command = New SqlCommand(query, connection)
reader = command.ExecuteReader
If reader.HasRows Then
Label25.Text = "* Username already registered, try another."
Label25.ForeColor = Color.Red
Else
Label25.Text = "* Mendatory fields cannot be left blank."
Label25.ForeColor = Color.Black
End If
connection.Close()
Catch ex As Exception
MsgBox(ex.Message)
Finally
command.Dispose()
End Try
Try
OpenConnection()
Dim defText As String = "Not Modified".ToString
Dim ms As New System.IO.MemoryStream
Dim command As New SqlCommand("INSERT into [faculty](id,name,dob,sex,addr,city,state,pin,country,nat,mobile,email,dept,sub,username,password,security,answer,avatar,authentic,created,modified)values(@id,@name,@dob,@sex,@addr,@city,@state,@pin,@country,@nat,@mobile,@email,@dept,@sub,@username,@password,@security,@answer,@avatar,@authentic,@created,@modified)", connection)
PictureBox1.Image.Save(ms, PictureBox1.Image.RawFormat)
command.Parameters.Add("@id", SqlDbType.VarChar).Value = TextBox1.Text
command.Parameters.Add("@name", SqlDbType.VarChar).Value = TextBox2.Text
command.Parameters.Add("@dob", SqlDbType.Date).Value = DateTimePicker1.Value.Date
Dim gender As String
Dim value As Integer
gender = ComboBox1.Text
If gender = "Male" Then
value = 1
ElseIf gender = "Female" Then
value = 2
ElseIf gender = "Others" Then
value = 3
End If
command.Parameters.Add("@sex", SqlDbType.Int).Value = value
command.Parameters.Add("@addr", SqlDbType.VarChar).Value = TextBox3.Text
command.Parameters.Add("@city", SqlDbType.VarChar).Value = TextBox4.Text
command.Parameters.Add("@state", SqlDbType.VarChar).Value = TextBox5.Text
command.Parameters.Add("@pin", SqlDbType.Int).Value = TextBox14.Text
command.Parameters.Add("@country", SqlDbType.VarChar).Value = TextBox6.Text
command.Parameters.Add("@nat", SqlDbType.VarChar).Value = TextBox7.Text
command.Parameters.Add("@mobile", SqlDbType.VarChar).Value = TextBox8.Text
command.Parameters.Add("@email", SqlDbType.VarChar).Value = TextBox9.Text
command.Parameters.Add("@dept", SqlDbType.VarChar).Value = ComboBox3.Text
command.Parameters.Add("@sub", SqlDbType.VarChar).Value = ComboBox4.Text
command.Parameters.Add("@username", SqlDbType.VarChar).Value = TextBox10.Text
command.Parameters.AddWithValue("@password", Encrypt(TextBox11.Text.Trim()))
Dim question As String
Dim data As Integer
question = ComboBox2.Text
If question = "Whats your nickname ?" Then
data = 5
ElseIf question = "Whats your pet name ?" Then
data = 4
ElseIf question = "Whats your birth place ?" Then
data = 3
ElseIf question = "Whats your middle name ?" Then
data = 2
ElseIf question = "Whos your childhood hero ?" Then
data = 1
End If
command.Parameters.Add("@security", SqlDbType.Int).Value = data
command.Parameters.AddWithValue("@answer", Encrypt(TextBox13.Text.Trim()))
command.Parameters.Add("@created", SqlDbType.VarChar).Value = Date.Today
command.Parameters.Add("@modified", SqlDbType.VarChar).Value = defText
command.Parameters.Add("@avatar", SqlDbType.Image).Value = ms.ToArray
command.Parameters.Add("@authentic", SqlDbType.Int).Value = 1
If command.ExecuteNonQuery() = 1 Then
MsgBox("A new faculty has been successfully registered.")
Else
MsgBox("We're unable to accept the registration. Please check the form that you've submitted.")
End If
connection.Close()
Catch ex As Exception
Label25.Text = ("* Profile image cannot be left blank.")
Finally
command.Dispose()
End Try
Catch ex As Exception
MsgBox(ex.Message)
End Try
You have to close the reader when finished using it:
reader = command.ExecuteReader ' Use reader here ... ' ' Close reader when the job is done reader.Close()
See also the SqlDataReader.Close Method (System.Data.SqlClient)[^]:
Quote:You must explicitly call the Close method when you are through using the SqlDataReader to use the associated SqlConnection for any other purpose.
When you open a DataReader, it sets up a "link" to a result set in SQL Server, and each row is only fetched to your computer when you use the Read function.
If you do not close the Command, then SQL Server keeps the result set open, and will not accept further requests because it doesn't know that you aren't "looking" at the earlier one any more.
Besides, SqlConnections, SqlCommands, and SqlDataAdapters are scarce resources, and you really should be Closing and Disposing of them as soon as you are finished with them. One way is to do this in aFinally
block of yourTry
, but a much better way is creating them within aUsing
block which automatically Disposes of items when they go out of scope:
Using con As New SqlConnection(strConnect) con.Open() Using cmd As New SqlCommand("SELECT iD, description FROM myTable", con) Using reader As SqlDataReader = cmd.ExecuteReader() While reader.Read() Dim id__1 As Integer = CInt(reader("iD")) Dim desc As String = DirectCast(reader("description"), String) Console.WriteLine("ID: {0}" & vbLf & " {1}", iD, desc) End While End Using End Using End Using
这篇关于如何对现有数据进行多次检查?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!