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'.