Object reference not set to an instance of an object.

[zaimah]

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

[Teme64]

it shows conn = nothing

In Private Function GetRunNo() As Long you have

Finally
   conn = Nothing
   ds = Nothing
End Try

and in Private Sub UpdateRunNo(ByVal lastDateUsed As DateTime, ByVal lastRunNoUsed As Long) you have

Finally
  conn = Nothing
  comm = Nothing
End Try

Take conn = Nothing lines (or line) away.

[zaimah]

that was silly of me :-) TQ. but my problem for my DocID still not solve.. i try put all the data that i want for DocID in a msgbox just wanted to know whether the value is ok.. and it display the value, but why i cannot add the value to my table? when i try to add, the exception error msg is "String or binary data would be truncated.
The statement has been terminated."

[Teme64]

that was silly of me :-) TQ.

:)

You have lines

cmd.Parameters.Add(New SqlClient.SqlParameter("@DocID", SqlDbType.Char, 15))
cmd.Parameters("@DocID").Value = dateNow + CoopID.Text + GetRunNo().ToString("D4")

Change them

cmd.Parameters.Add(New SqlClient.SqlParameter("@DocID", SqlDbType.Char, 15))
Dim DocIDParam As String
DocIDParam = dateNow + CoopID.Text + GetRunNo().ToString("D4")
If DocIDParam.Length > 15 Then
  MessageBox.Show("DocID length=" & DocIDParam.Length, "Parameter Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
  ' Truncate param
  ' DocIDParam = DocIDParam.Substring(0, 15)
  ' Or exit sub
  ' Exit Sub 
Else
  cmd.Parameters("@DocID").Value = DocIDParam
End If

Now you'll get a message box if parameter length exceeds 15 characters and you can either exit or truncate parameter.

If you still get that same error, check from the DB, what is the maximum length of DocID field.