Object reference not set to an instance of an object

[tendaimare]

i have corrected my conn string i am now using sql connection but now when i run the code it gives me he error Object reference not set to an instance of an object.

'I put this code in the load event
ListBox1.Items.Add(CType(GetTables().ToString, String))

Public Shared Function GetTables() As String()
        Dim kon As SqlConnection
        Dim kom As New SqlCommand
        Dim i As Integer
        Dim str() As String
        kon = New SqlConnection("server=sql...,1444;uid=....;pwd=.....;database=.....")
        kon.Open()
        kom.Connection = kon
        Dim dTable As DataTable
        Try
            dTable = kon.GetSchema(SchemaType.Mapped, New Object() {Nothing, Nothing, Nothing, "TABLE"})
            For i = 0 To dTable.Rows.Count - 1
                ReDim Preserve str(i)
                str(i) = dTable.Rows(i).Item(2).ToString
            Next
            Return str
        Catch ex As Exception
            Return Nothing
        End Try

    End Function

[tendaimare]

someone told me that something was wrong with line 10 of that code but i still do not see what is wrong about it

[tendaimare]

i've tried instantiating it hasn't worked

Public Shared Function GetTables() As String()
        Dim kon As SqlConnection
        Dim kom As New SqlCommand
        Dim i As Integer
        Dim str() As String
        kon = New SqlConnection("server=.......;uid=.......;pwd=......;database=.....")
        kon.Open()
        kom.Connection = kon
        Dim dTable As New DataTable '<<< here
        Try
            dTable = kon.GetSchema(SchemaType.Mapped, New Object() {Nothing, Nothing, Nothing, "TABLE"})
            For i = 0 To dTable.Rows.Count - 1
                ReDim Preserve str(i)
                str(i) = dTable.Rows(i).Item(2).ToString
            Next
            Return str
        Catch ex As Exception
            Return Nothing
        End Try

    End Function

line 13. Your only initializing the array if the table has rows. If the table has no rows it will return nothing.

line 18. Returning nothing on error.

You can modify the function to return an empty string array on the above conditions or check the result from the function before using.

Not sure about this: ListBox1.Items.Add(CType(GetTables().ToString, String))

Seems like it should be:

dim s() as string = GetTables()
if s isnot nothing andalso s.count > 0 then
    ListBox1.Items.AddRange(s)
else
   'your function ran into an error or there where no rows
end if

[zinnqu]

Before you can use a datatable you need a dataset. A data set is used to define the data table. The Idea is to do something like this first

' SQL Command object initialized 
Dim cmd As New SqlCommand("select productname,unitprice,categoryid from products order by productname; select categoryid from categories order by categoryid", conn)

' SQL Data Adapter to fetch the records
' It executes the specified SQL Command
Dim adp As New SqlDataAdapter(cmd)

' DataSet to store the retrieved data records in server memory
Dim dset As New DataSet()

' SQL Data Adapter Fill method fills the dataset object with records
' retrieved from the database
adp.Fill(dset)

' Close the database connection if it is in open state
If conn.State = ConnectionState.Open Then
    conn.Close()
End If

Next Set the information that you have aquired to a data table

' DataTable tbl1 to store the first table having all the products
' fetched by executing the first SQL query.
Dim tbl1 As DataTable = dset.Tables(0)

Last set the whole shebang to a Listview to view the output

' Bind the information to a ListView
    ListView1.DataSource = tbl1
    ListView1.DataBind()

all infromation is then pushed into the grid and you can manipulate the information without issue.

[tendaimare]

thanx u guyz i value your comments and i've rated them.Currently i'm working on another assignment but once i'm done i'll try the code

Not sure how you rate your comments. It seems a little strange.

You Do Not Need a dataset to fill a data table.

Heres my orignial comment that also fixes your get schema.

Option Strict On
Imports System.Data.SqlClient

Public Class Form1

    Public Shared Function GetTables() As String()
        Dim Con As SqlConnection = New SqlConnection("Data Source.....")
        Dim dTable As DataTable = Nothing
        Dim str() As String = Nothing

        Try
            Con.Open()

            dTable = Con.GetSchema("TABLES")

            If dTable.Rows.Count > 0 Then
                ReDim str(dTable.Rows.Count - 1)
                For i = 0 To dTable.Rows.Count - 1
                    str(i) = dTable.Rows(i).Item(2).ToString
                Next
            End If

        Catch ex As Exception

        Finally
            Con.Dispose()
            If dTable IsNot Nothing Then dTable.Dispose()
        End Try

        Return str
    End Function

    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim s() As String = GetTables()
        If s IsNot Nothing AndAlso s.Count > 0 Then
            ListBox1.Items.AddRange(s)
        Else
            'your function ran into an error or there where no rows
        End If

    End Sub
End Class

Another thing about my first response that solved your object reference not set to an instance of an object that wasn't good enough for you:

line 18. Returning nothing on error.

Was the exact reason you were getting the object refence not set to an instance of an object.

You were adding nothing to the listbox.

My first comment solved that problem and a future problem if the db had no tables.

My second comment solved the problem of why you were getting the first problem to begin with. Your getSchema parameters are incorrect. Setting a break point in the catch statement could have easily directed you to that.

Hopefully these correct answers are helpful to you.

[tendaimare]

Thanx dude i greatly appreciate your help.