Function cek_code(ByVal pid As String) As Boolean

        Dim sql As String = "select count(P_Id) from Profit_margin where P_Id = '" & ComboBox2.Text & "'"
        Dim cmd As New SqlCommand(sql, myConnection)
        Dim dr As SqlDataReader
        Dim value As Boolean = False
        Try
            myConnection.Open()
            dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
            While dr.Read
                If dr(0) > 0 Then
                    value = True
                End If
            End While
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Critical, "ERROR")
        Finally
            myConnection.Close()
        End Try
        Return value
    End Function

and inthe Save button i used like

Public Sub Save()
        Dim CAT As String = ComboBox1.Text
        Dim pid As String = ComboBox2.Text
        Dim margin As String = TextBox3.Text
        myConnection = New SqlConnection(connectionstring)
        myConnection.Open()

        If cek_code(pid) = False Then
            myConnection = New SqlConnection(connectionstring)
            myConnection.Open()

            myCommand = New SqlCommand("Insert into Profit_margin (Profit_Margin,Date,Category,P_Id,Time) values ('" & margin & "','" & DateTimePicker1.Value & "','" & CAT & "','" & pid & "','" & TextBox1.Text & "'", myConnection)
            myCommand.ExecuteNonQuery()

            MessageBox.Show("New Profit is Added")

            myConnection.Close()
        Else
            myConnection = New SqlConnection(connectionstring)
            myConnection.Open()
            Dim update_product As SqlCommand = New SqlCommand("Update Profit_margin Set Profit_Margin='" & TextBox3.Text & "' where P_Id='" & ComboBox2.Text & "'", myConnection)
            update_product.ExecuteNonQuery()
            update_product.Cancel()


            MsgBox("duplicate code", MsgBoxStyle.Information, "Informasi")
        End If




        myConnection.Close()

    End Sub

in the savebutton i wrote the code like this

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click


        If (ComboBox1.SelectedItem = Nothing) Then
            MessageBox.Show("Please select category")
        ElseIf (ComboBox2.SelectedItem = Nothing) Then
            MessageBox.Show("Please select product")
        ElseIf (TextBox3.Text = Nothing) Then
            MessageBox.Show("Please departement")
        Else
        End If
        myConnection = New SqlConnection(connectionstring)
        myConnection.Open()
        myCommand = New SqlCommand("SELECT  * FROM Profit_margin ", myConnection)
        Dim dr As SqlDataReader = myCommand.ExecuteReader
        While dr.Read()
            nxtmajor = Val(dr(0).ToString) + 1
            ' nxtmajor = SQLdr("Value") + 1
        End While
        dr.Close()

        myConnection.Close()

        Save()

    End Sub

The error msg i gettin is:The connection is not closed
and
Incorrect syntax near '04:47:11'.

I see logic errors in the Save function.

You care calling myconnection.Open method on a connection that is already open. This is where you are getting the error: The connection is not closed. The code on Line 6 will do for the entire method since you close it in Line 32.

The other error, is you are saving the DateTimePicker.Value, which is a DateTime type into a Date type in the database. In this case, use the DateTimePicker's Value.Date property.

DateTimePicker1.Value.Date

Edited 4 Years Ago by Maligui: Spelling

Maligui
the error i'm getting is this :Incorrect syntax near 'CIS800 '
CIS800 -is a onr od product id............
these set of codes are not working properly
in save button

If cek_code(pid) = False Then

            myConnection = New SqlConnection(connectionstring)
            myConnection.Open()

            myCommand = New SqlCommand("Insert into Profit_margin (Profit_Margin,Date,Category,P_Id) values ('" & margin & "','" & DateTimePicker1.Value.Date & "','" & CAT & "','" & pid & "'", myConnection)
            myCommand.ExecuteNonQuery()

            MessageBox.Show("New Profit is Added")

            myConnection.Close()
        Else
This question has already been answered. Start a new discussion instead.