本文介绍了需要先检索数据，然后将新条目更新到数据库的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我需要先从数据库中检索数据，然后使用新条目更新表，以下是我的代码，但是出现错误:
I need to retrieve data from the database first and then update the table with the new entry, following is my code but I am having an error:
我知道我需要通过注释 dr1.close 来打开数据读取器，但是一旦这样做，我就会遇到另一个异常:
I know I need to open the datareader by commenting dr1.close, but as soon as I did that I face an another exception:
Imports System.IO
Imports System.Data.Sql
Imports System.Data.SqlClient
Imports System.Data
Partial Class Officer_Module_GST_id_password
Inherits System.Web.UI.Page
Dim sscript As String
Dim sms As New smsgw
Dim mail As New MailSender
Dim cmd As New SqlCommand
Dim ds As New DataSet
Dim dr As SqlDataReader
Dim objconn As New connectioncls
Dim name As String
Dim pid As String
Dim pwd As String
Dim email_sent As Integer
Dim email_status As String
Dim mobile As String
Dim message As String
Dim subject As String
Dim email As String
Dim mtext As String
Protected Sub validatedeal_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles validatedeal.Click
containertwo.Visible = True
txt_subject.Text = "Communication of the Provisional Identification Number and Password"
txt_mail.Text = "Instance mail"
End Sub
Protected Sub btnsendmail_Click(ByVal sender As Object, ByVal e As System.EventArgs)
objconn.openconn()
cmd.Connection = objconn.conn
cmd.CommandText = "Select Trade_name,provissional_id,access_token,reg_mobile_no,reg_email_id,email_status,isnull(no_of_email_sent,0) from Provisional_details"
Dim dr1 As SqlDataReader = cmd.ExecuteReader()
While (dr1.Read())
name = dr1(0).ToString()
pid = dr1(1).ToString()
pwd = dr1(2).ToString()
mobile = dr1(3).ToString()
email = dr1(4).ToString()
email_status = dr1(5).ToString()
email_sent = dr1(6).ToString()
subject = "subject to instance"
mtext = "new instance email"
message = "new instance message"
Try
MailSender.SendEmail("riteshbhatt93" + "@gmail.com", "rock_on", email, subject, mtext, System.Web.Mail.MailFormat.Text, "")
Try
Call sms.SendSMSUsingNICGW(mobile, message)
Catch
sscript = "<script language=javascript>alert('Message not sent!!')</script>"
Page.ClientScript.RegisterStartupScript(Me.GetType(), "Empty", sscript)
sscript = Nothing
Exit Try
Finally
End Try
Try
Call sms.SendSMSUsingMGOVGW(mobile, message)
Catch
sscript = "<script language=javascript>alert('Message not sent!!')</script>"
Page.ClientScript.RegisterStartupScript(Me.GetType(), "Empty", sscript)
sscript = Nothing
Exit Try
Finally
End Try
Catch
Dim cmd1 As New SqlCommand
cmd1.Connection = objconn.conn
cmd1.Parameters.AddWithValue("@mobile", mobile)
cmd1.Parameters.AddWithValue("@Email_status", "NO")
cmd1.CommandText = "Update Provisional_details set Email_sent = @Email_status where reg_mob_no = @mobile"
cmd1.ExecuteNonQuery()
cmd1.Parameters.Clear()
Exit Sub
Finally
End Try
dr1.Close()
Dim cmd2 As New SqlCommand
cmd2.Connection = objconn.conn
cmd2.Parameters.AddWithValue("@mobile", mobile)
cmd2.Parameters.AddWithValue("@Email_status", "YES")
cmd2.Parameters.AddWithValue("@emailsent", email_sent + 1)
cmd2.CommandText = "Update Provisional_details set email_status = @Email_status,no_of_email_sent = @emailsent where reg_mobile_no = @mobile"
cmd2.ExecuteNonQuery()
cmd2.Parameters.Clear()
End While
sscript = "<script language=javascript>alert('Your Mail has been sent to all applied dealers!!')</script>"
Page.ClientScript.RegisterStartupScript(Me.GetType(), "Empty", sscript)
sscript = Nothing
End Sub
End Class
推荐答案
我已经将其简化为重要的方法(就像您在发布问题时应该做的那样).方法中有更新，使用注释来注释正在发生的事情.
I've pared this down to just the method that matters (like you should have done when posting the question). Updates are in the method, using comments to annotate what's going on.
Protected Sub btnsendmail_Click(ByVal sender As Object, ByVal e As System.EventArgs)
'Best practice in .Net is use a brand new connection instance for most DB calls. Really.
'Don't try to be clever and re-use one connection. Just use the same string.
Dim connString As String = "Connection String Here"
'Using block will guarantee connection closes properly, even if an exception is thrown
Using cn As New SqlConnection(connString), _
cmd As New SqlCommand("Select Trade_name,provissional_id,access_token,reg_mobile_no,reg_email_id,email_status,isnull(no_of_email_sent,0) from Provisional_details", cn), _
cn2 As New SqlConnection(connString), _
cmd2 As New SqlCommand("Update Provisional_details set email_status = @Email_status,no_of_email_sent = @emailsent where reg_mobile_no = @mobile", cn2)
'Define your parameters as early as possible, and be explicit about parameter types and lengths
' This will avoid potentially crippling performance gotchas
cmd2.Parameters.Add("@mobile", SqlDbType.NVarChar, 14)
cmd2.Parameters.Add("@Email_status", SqlDbType.VarChar, 5)
cmd2.Parameters.Add("@emailsent", SqlDbType.Int)
'Separate SQL statements in a tight loop like this is one of the few places to re-use a connection object...
' Even here, it should be a BIG RED FLAG that there's a better way to handle this whole process that avoids multiple calls to the DB.
' For example, it might be better to assume success, Update the DB accordingly in the original statement, and then only write failures back when needed
cn2.Open()
cn.Open()
dr1 As SqlDataReader = cmd.ExecuteReader()
While (dr1.Read())
'Best practice in .Net are to declare these variables in the method where you use them
Dim name As String = dr1(0).ToString()
Dim pid As String = dr1(1).ToString()
Dim pwd As String = dr1(2).ToString() 'You're not really storing passwords in plain-text are you? Please say, "No".
Dim mobile As String = dr1(3).ToString()
Dim email As String = dr1(4).ToString()
Dim email_status As String = dr1(5).ToString()
Dim email_sent As Integer = dr1.GetInt32(6) 'It's a number. You do math on it later. Get the INTEGER value
Dim subject As String = "subject to instance"
Dim mtext As String = "new instance email"
Dim message As String = "new instance message"
cmd2.Parameters("@mobile").Value = mobile
Try
MailSender.SendEmail("riteshbhatt93" + "@gmail.com", "rock_on", email, subject, mtext, System.Web.Mail.MailFormat.Text, "")
Try
' Also... the "Call" keyword is a vb6-era relic that has no purpose any more. Don't use it
sms.SendSMSUsingMGOVGW(mobile, message)
Catch
Page.ClientScript.RegisterStartupScript(Me.GetType(), "Empty", "<script language=javascript>alert('Message not sent!!')</script>")
'Don't set values to "Nothing" to free them in VB.Net.
'It doesn't help the way it used to in vb6/vbscript, and can actually be HARMFUL in rare cases in .Net
End Try
' Do you really mean to try both gateways, even if the first succeeds?
' Because that's what the original code is doing.
Try
sms.SendSMSUsingNICGW(mobile, message)
Catch
Page.ClientScript.RegisterStartupScript(Me.GetType(), "Empty", "<script language=javascript>alert('Message not sent!!')</script>")
'No need to call Exit Try here,
' and no need for an empty Finally section
End Try
Catch
cmd2.Parameters("@emailsent") = email_sent
cmd2.Parameters("@Email_status") = "NO"
cmd2.ExecuteNonQuery()
End Try
cmd2.Parameters("@Email_status").Value = "YES"
cmd2.Parameters("@emailsent").Value = email_sent + 1
cmd2.ExecuteNonQuery()
End While
End Using
End Sub
这篇关于需要先检索数据，然后将新条目更新到数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！