i have been google it for answers but still cannot find the main problem.. when i debug, this error msg coming from exception section, but before that, when i put my cursor at conn (Button1_Click) to see the value it shows conn = nothing. But conn in other function got values.. i have declare conn as
Public in a module. Public conn = New SqlClient.SqlConnection("Data Source=KOMPZAI;User ID= sydm;Password=sydm;Initial Catalog=SPGAlatihan;Persist Security Info=True;")
i can see all the values but cannot add to the table.
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim dateNow As String
dateNow = DateTime.Now.ToString("yyMMdd")
If CoopID.Text.Length > 0 And CoopName.Text.Length > 0 And RPerson.Text.Length > 0 And Problem.Text.Length > 0 And Solution.Text.Length > 0 And CSName.Text.Length > 0 And Status.Text.Length > 0 Then
Try
Dim strSQL As String = "INSERT INTO CSLog (CoopID, CoopName, CoopName1, RPerson, Phone, Problem, Solution, CSName, Status) VALUES (@CoopID, @CoopName, @CoopName1, @RPerson, @Phone, @Problem, @Solution, @CSName, @Status)"
Dim cmd As New System.Data.SqlClient.SqlCommand(strSQL, conn)
cmd.Parameters.Add(New SqlClient.SqlParameter("@DocID", SqlDbType.Char, 15))
cmd.Parameters("@DocID").Value = dateNow + CoopID.Text + GetRunNo().ToString("D4")
cmd.Parameters.Add(New SqlClient.SqlParameter("@CoopID", SqlDbType.Char, 5))
cmd.Parameters("@CoopID").Value = CoopID.Text
cmd.Parameters.Add(New SqlClient.SqlParameter("@CoopName", SqlDbType.NVarChar, 50))
cmd.Parameters("@CoopName").Value = CoopName.Text
cmd.Parameters.Add(New SqlClient.SqlParameter("@CoopName1", SqlDbType.NVarChar, 50))
cmd.Parameters("@CoopName1").Value = CoopName1.Text
cmd.Parameters.Add(New SqlClient.SqlParameter("@RPerson", SqlDbType.Char, 20))
cmd.Parameters("@RPerson").Value = RPerson.Text
cmd.Parameters.Add(New SqlClient.SqlParameter("@Phone", SqlDbType.Char, 15))
cmd.Parameters("@Phone").Value = Phone.Text
cmd.Parameters.Add(New SqlClient.SqlParameter("@Problem", SqlDbType.NVarChar, 100))
cmd.Parameters("@Problem").Value = Problem.Text
cmd.Parameters.Add(New SqlClient.SqlParameter("@Solution", SqlDbType.NVarChar, 100))
cmd.Parameters("@Solution").Value = Solution.Text
cmd.Parameters.Add(New SqlClient.SqlParameter("@CSName", SqlDbType.Char, 20))
cmd.Parameters("@CSName").Value = CSName.Text
cmd.Parameters.Add(New SqlClient.SqlParameter("@Status", SqlDbType.Char, 10))
cmd.Parameters("@Status").Value = Status.Text
cmd.Connection.Open()
cmd.ExecuteNonQuery()
cmd.Connection.Close()
conn.Close()
Catch ex As Exception
Label6.Text = "PLEASE FILL IN ALL THE FIELD LISTED."
Label6.Visible = True
Finally
Label6.Text = "Cussecfully Saved ."
Label6.Visible = True
Clear()
End Try
End If
End Sub
Private Function GetRunNo() As Long
Dim ds As DataSet = Nothing
Try
Dim sql = "SELECT TOP 1 tarikh1, run_no from runNo"
' Dim comm = New SqlClient.SqlDataAdapter(sql, conn)
Dim comm = New SqlClient.SqlCommand(sql, conn)
Dim da = New SqlClient.SqlDataAdapter(comm)
ds = New DataSet
da.Fill(ds, "runNo")
Dim lastDateUsed As Date = ds.Tables(0).Rows(0).Item("tarikh1")
Dim lastRunNoUsed As Long = ds.Tables(0).Rows(0).Item("run_no")
If DateTime.Now.Date > lastDateUsed.Date Then
' Chnage date to today's date
lastDateUsed = DateTime.Now.Date
' reset runNo to 1
lastRunNoUsed = 1
Else
' Increment runNo
lastRunNoUsed = lastRunNoUsed + 1
End If
' Update database
UpdateRunNo(lastDateUsed, lastRunNoUsed)
Return lastRunNoUsed
comm = Nothing
Catch ex As Exception
Label5.Visible = True
Label5.Text = "Error. " & ex.Message
Finally
conn = Nothing
ds = Nothing
End Try
End Function
Private Sub UpdateRunNo(ByVal lastDateUsed As DateTime, ByVal lastRunNoUsed As Long)
Dim comm As SqlClient.SqlCommand = Nothing
Try
Dim sql = "UPDATE runNo SET tarikh1 = @tarikh1, run_no = @run_no"
comm = New SqlClient.SqlCommand(sql, conn)
comm.Parameters.Add(New SqlClient.SqlParameter("@tarikh1", SqlDbType.DateTime))
comm.Parameters("@tarikh1").Value = lastDateUsed
comm.Parameters.Add(New SqlClient.SqlParameter("@run_no", SqlDbType.BigInt))
comm.Parameters("@run_no").Value = lastRunNoUsed
comm.ExecuteNonQuery()
comm.Connection.Close()
Catch ex As Exception
Label5.Visible = True
Label5.Text = "Error. " & ex.Message
Finally
conn = Nothing
comm = Nothing
End Try
End Sub