需要先检索数据，然后将新条目更新到数据库 [英] Needs to retrieve data first and then update the new entry into DB

问题描述

我需要先从数据库中检索数据，然后使用新条目更新表，以下是我的代码，但是出现错误:

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:

关闭阅读器后，无效的调用Read的尝试."

"Invalid attempt to call Read when reader is closed."

我知道我需要通过注释 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:

已经有与此命令关联的打开的数据读取器，必须首先关闭.vb.net"

"there is already an open datareader associated with this command which must be closed first. vb.net"

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

这篇关于需要先检索数据，然后将新条目更新到数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！