Member Avatar for සශික

I got dannie web help and make best effort to get good idea. Finally I make this code. but
This code okay for search data and load it to text boxes. but again I change some values and click update button It say "connection alredy open."
this is search code :

query = "SELECT * FROM mcs.custormer WHERE custormer_id ='" & txtcustormeride.Text & "'"
        Dim cmd As MySqlCommand = New MySqlCommand(query, con)
        Dim dr As MySqlDataReader
        Try
            con.Open()
            dr = cmd.ExecuteReader()
            While (dr.Read())
                Me.txtfirstnamee.Text = Convert.ToString(dr("first_name"))
                Me.txtlastnamee.Text = Convert.ToString(dr("last_name"))
                Me.txtnicnoe.Text = Convert.ToString(dr("nic_no"))
                Me.txtcaddress1e.Text = Convert.ToString(dr("c_address1"))
                Me.txtcaddress2e.Text = Convert.ToString(dr("c_address2"))
                Me.txtcaddress3e.Text = Convert.ToString(dr("c_address3"))
                Me.txtctelephonenoe.Text = Convert.ToString(dr("c_telephoneno"))
                'Me.txtmembershipdatee.Text = Convert.ToString(dr("membership_date"))
                Me.txtbusinessnamee.Text = Convert.ToString(dr("business_name"))
                Me.txtgnamee.Text = Convert.ToString(dr("g_name"))
                Me.txtgnicnoe.Text = Convert.ToString(dr("g_nicno"))
                Me.txtgaddress1e.Text = Convert.ToString(dr("g_address1"))
                Me.txtgaddress2e.Text = Convert.ToString(dr("g_address2"))
                Me.txtgaddress3e.Text = Convert.ToString(dr("g_address3"))
                Me.txtgtelephonenoe.Text = Convert.ToString(dr("g_telephoneNO"))
            End While
            'MsgBox("Please Try With Correct Custormer ID")
            con.Close()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

and this is update code :

query = "UPDATE mcs.custormer SET  first_name=@first_name, last_name=@last_name,nic_no=@nic_no,c_address1=@c_address1,c_address2=@c_address2,c_address3=@c_address3,c_telephoneno=@c_telephoneno,membership_date=@membership_date,business_name=@business_name,g_name=@g_name,g_nicno=@g_nicno,g_address1=@g_address1,g_address2=@g_address2,g_address3=@g_address3,g_telephoneNO=@g_telephoneNO  where custormer_id ='" & txtcustormeride.Text & "'"
        Dim cmd As MySqlCommand = New MySqlCommand(query, con)
        cmd.Parameters.AddWithValue("@custormer_id", Convert.ToInt32(txtcustormeride.Text))
        cmd.Parameters.AddWithValue("@first_name", Convert.ToString(txtfirstnamee.Text))
        cmd.Parameters.AddWithValue("@last_name", Convert.ToString(txtlastnamee.Text))
        cmd.Parameters.AddWithValue("@nic_no", Convert.ToString(txtnicnoe.Text))
        cmd.Parameters.AddWithValue("@c_address1", Convert.ToString(txtcaddress1e.Text))
        cmd.Parameters.AddWithValue("@c_address2", Convert.ToString(txtcaddress2e.Text))
        cmd.Parameters.AddWithValue("@c_address3", Convert.ToString(txtcaddress3e.Text))
        cmd.Parameters.AddWithValue("@c_telephoneNO", Convert.ToString(txtctelephonenoe.Text))
        cmd.Parameters.AddWithValue("@membership_date", Convert.ToDateTime(dtpmembershipdatee.Value))
        cmd.Parameters.AddWithValue("@business_name", Convert.ToString(txtbusinessnamee.Text))
        cmd.Parameters.AddWithValue("@g_name", Convert.ToString(txtgnamee.Text))
        cmd.Parameters.AddWithValue("@g_nicno", Convert.ToString(txtgnicnoe.Text))
        cmd.Parameters.AddWithValue("@g_address1", Convert.ToString(txtgaddress1e.Text))
        cmd.Parameters.AddWithValue("@g_address2", Convert.ToString(txtgaddress2e.Text))
        cmd.Parameters.AddWithValue("@g_address3", Convert.ToString(txtgaddress3e.Text))
        cmd.Parameters.AddWithValue("@g_telephoneNO", Convert.ToString(txtgtelephonenoe.Text))
        Try
            con.Open()
            cmd.ExecuteNonQuery()
            txtcustormerid.Clear()
            txtfirstname.Clear()
            txtlastname.Clear()
            txtnicno.Clear()
            txtcaddress1.Clear()
            txtcaddress2.Clear()
            txtcaddress3.Clear()
            txtctelephoneno.Clear()
            txtbusinessname.Clear()
            txtgname.Clear()
            txtgaddress1.Clear()
            txtgaddress2.Clear()
            txtgaddress3.Clear()
            txtgtelephoneno.Clear()
            txtgnicno.Clear()
            MessageBox.Show("Current Custormer's Information Upadted Sucsessfully !")
            con.Close()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

Recommended Answers

All 3 Replies

you need to use two diffrenet connectoin one for the select and one for the update

Member Avatar for සශික

thank you sir :)

You do NOT need two different connections. However, if you get an error that is caught by a Catch then your connection will stay open and that may be the cause of the error. You should add a Finally clause and include a con.Close() in there (but only if the connection is open), or you can add some code after the End Try like

if connection is open then
    close it
end if
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.