Can anyone check my search code?
What seems to be the problem?

**ERROR: **Object variable or with block variable not set.

 Dim con As New SqlConnection(connectionString:="Data Source=APBIPHAP06; Initial Catalog=PMT; Integrated Security=SSPI")
        Dim ds As New DataSet()
        Dim da As New SqlDataAdapter()
        Dim query As String = "SELECT * FROM tblPM WHERE UsageDate BETWEEN @UsageDate1 AND @UsageDate2"

        Try
            Dim cmd As New SqlCommand(query, con)
            cmd.Parameters.AddWithValue("@UsageDate1", dtpSearchDateFrom.Value)
            cmd.Parameters.AddWithValue("@UsageDate2", dtpSearchDateTo.Value)

            da.SelectCommand = cmd
            da.Fill(ds, "tblPMData")

            With dgvPMData
                .DataSource = ds.Tables("tblPM")
                .DataMember = "tblPM"
                .DisplayMember = "UsageDate"
                .ValueMember = "UsageDate"
            End With

        Catch ex As Exception
            MessageBox.Show("Error while connecting to SQL Server." & ex.Message)
        Finally
            con.Close() 'Whether there is error or not. Close the connection.
        End Try

Thank you.

Recommended Answers

All 5 Replies

What line is giving the error? By the way, you may want to open the connection before you try to use it.

i just tried to open the connection before declaring cmd, error still prompted.
can you please check each lines? did i miss something? or put anything wrong?

Please post the new code and this time identify the line that is raising the error.

Error found on this line:

.DataSource = ds.Tables("tblPM")

Here's the new code

Dim con As New SqlConnection(connectionString:="Data Source=APBIPHAP06; Initial Catalog=PMT; Integrated Security=SSPI")
        Dim ds As New DataSet()
        Dim da As New SqlDataAdapter()
        Dim query As String = "SELECT * FROM tblPM WHERE UsageDate BETWEEN @UsageDate1 AND @UsageDate2"

        Try
            con.Open()
            Dim cmd As New SqlCommand(query, con)
            cmd.Parameters.AddWithValue("@UsageDate1", dtpSearchDateFrom.Value)
            cmd.Parameters.AddWithValue("@UsageDate2", dtpSearchDateTo.Value)

            da.SelectCommand = cmd
            da.Fill(ds, "tblPMData")

            With dgvPMData
                .DataSource = ds.Tables("tblPM")
                .DataMember = "tblPM"
                .DisplayMember = "UsageDate"
                .ValueMember = "UsageDate"
            End With

        Catch ex As Exception
            MessageBox.Show("Error while connecting to SQL Server." & ex.Message)
        Finally
            con.Close() 'Whether there is error or not. Close the connection.
        End Try

Reverend Jim,I already solved this one.
Just tried adding the form name before declaring the datasource.
Thank you for your time.
For other those who have the same problem, kindly check out below code.

Dim con As New SqlConnection(connectionString:="Data Source=APBIPHAP06; Initial Catalog=PMT; Integrated Security=SSPI")
        Dim ds As New DataSet()
        Dim da As New SqlDataAdapter()
        Dim query As String = "SELECT * FROM tblPM WHERE UsageDate BETWEEN @UsageDate1 AND @UsageDate2"
        Try
            con.Open()
            Dim cmd As New SqlCommand(query, con)
            cmd.Parameters.AddWithValue("@UsageDate1", dtpSearchDateFrom.Value)
            cmd.Parameters.AddWithValue("@UsageDate2", dtpSearchDateTo.Value)
            da.SelectCommand = cmd
            da.Fill(ds, "tblPMData")
            With dgvPMData
                frmPartsM.DataSource = ds.Tables("tblPM")
                frmPartsM.DataMember = "tblPM"
            End With
        Catch ex As Exception
            MessageBox.Show("Error while connecting to SQL Server." & ex.Message)
        Finally
            con.Close() 'Whether there is error or not. Close the connection.
        End Try
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.