Insert check items of cecklistbox into database...

Question

4.00/5 (1 vote)

See more:

I am developing a project employee management system. I have a form Attendance which will be used to take attendance. I want to insert those employee records into the database which a checked . But I am getting error. Also suggest an alternative way to take the attendance.

Code:
-----------------------------------------------------------------

VB

Private Sub btnmark_Click(sender As Object, e As EventArgs) Handles btnmark.Click
        Try
            con.Open()
            com = New SqlCommand()
            com.Connection = con
            com.CommandText = "mark_attendance"
            com.CommandType = CommandType.StoredProcedure
         
            For Each employee In CheckedListBox1.CheckedItems
                getDetails()
                com.Parameters.AddWithValue("@empid", CheckedListBox1.GetItemText(employee))
                com.Parameters.AddWithValue("@empname", empname)
                com.Parameters.AddWithValue("@dept", dept)
                com.Parameters.AddWithValue("@date", System.DateTime.Today)
            Next
            com.ExecuteNonQuery()
            MsgBox("Attendance Marked...!", MsgBoxStyle.Information)
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            con.Close()
        End Try
    End Sub

    Private Sub btnexit_Click(sender As Object, e As EventArgs) Handles btnexit.Click
        Me.Close()
    End Sub
    Private Sub getDetails()
        Try
            con.Open()
            com = New SqlCommand("SELECT empname,dept from emp_master where empid=@empid", con)
            com.Parameters.AddWithValue("@empid", CheckedListBox1.SelectedItem)
            dr = com.ExecuteReader
            If dr.HasRows Then
                While dr.Read
                    dept = dr("dept")
                    empname = dr("empname")
                End While
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            dr.Close()
            con.Close()
        End Try
    End Sub
End Class

-----------------------------------------------------------
Error:
-----------------------------------------------------------
1). The connection was not closed. The connection's current state is open.
2). ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.

Posted 9-Apr-15 7:10am

A94

Add a Solution

Comments

Thanks7872 9-Apr-15 13:19pm

Refer ADO.NET documentation. You need to have look at some of it's basics.

Herman<T>.Instance 9-Apr-15 13:36pm

Where/What line does the exception occur?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Answer 1 · 2015-04-09T08:40:00

You're sharing the same connection object across multiple methods, which is a bad idea.

When you call the getDetails method, the shared connection object is already open, so the con.Open call throws an exception. The Finally block then closes the connection, so your ExecuteNonQuery call fails as well.

The best solution would be to update your mark_attendance stored procedure to read the name and department for the passed-in employee ID.

Otherwise, you'll need to execute two queries for each checked item:

VB.NET

Private Sub btnmark_Click(sender As Object, e As EventArgs) Handles btnmark.Click
   Using connection As New SqlConnection("YOUR CONNECTION STRING HERE")
      Using markCommand As New SqlCommand("mark_attendance", connection)
         Using getDetailsCommand As New SqlCommand("SELECT empname,dept from emp_master where empid = @empid", connection)
            
            markCommand.CommandType = CommandType.StoredProcedure
            connection.Open()
            
            For Each employee In CheckedListBox1.CheckedItems
               Dim empid As String = CheckedListBox1.GetItemText(employee)
               Dim empname, dept As String
               
               getDetailsCommand.Parameters.Clear()
               getDetailsCommand.Parameters.AddWithValue("@empid", empid)
               Using dr As SqlDataReader = getDetailsCommand.ExecuteReader()
                  If dr.Read() Then
                     empname = CStr(dr("empname"))
                     dept = CStr(dr("dept"))
                  End If
               End Using
               
               markCommand.Parameters.Clear()
               markCommand.Parameters.AddWithValue("@empid", empid)
               markCommand.Parameters.AddWithValue("@empname", empname)
               markCommand.Parameters.AddWithValue("@dept", dept)
               markCommand.Parameters.AddWithValue("@date", DateTime.Today)
               markCommand.ExecuteNonQuery()
            Next
            
            MsgBox("Attendance Marked...!", MsgBoxStyle.Information)
         End Using
      End Using
   End Using
End Sub