ping using vb.error ??

[markhorgan1]

i have tried altering some code i have to ping an ip address and store the result in an access db.i had it working when i had the ip hardcoded in as a string but I am now gettin an error.instead of having the ip address hardcoded in to the code i want it to be read in from the access db but im now getting this error:System.nullreferenceExceptionbject reference not set to an instance of an object at...button1.click event args e
i have underlined the piece of code i tried to change

Imports System.Data


Public Class Form1

    Dim Ping As Net.NetworkInformation.Ping
    Dim pReply As Net.NetworkInformation.PingReply
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    End Sub

   

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim ds As New DataSet
        Dim da As OleDb.OleDbDataAdapter
        Dim sql As String
        Dim con As New OleDb.OleDbConnection


        Try
            Ping = New Net.NetworkInformation.Ping

            pReply = Ping.Send(ds.Tables("monitor").Rows(0).Item(0))

            Label1.Text = pReply.Status.ToString




            If pReply.Status = Net.NetworkInformation.IPStatus.Success Then


                con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Documents and Settings\mhorgan\Desktop\VBtest.mdb"
                con.Open()

                sql = "SELECT * FROM tblone"
                da = New OleDb.OleDbDataAdapter(sql, con)
                da.Fill(ds, "monitor")


                Dim cb As New OleDb.OleDbCommandBuilder(da)

                ds.Tables("monitor").Rows(0).Item(1) = Label1.Text

                da.Update(ds, "monitor")







            Else
                Label1.BackColor = Color.Red
            End If

        Catch ex As Exception

            MessageBox.Show(ex.ToString())

        End Try
    End Sub


    Private Sub btnExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExit.Click
        Dim con As New OleDb.OleDbConnection
        con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Documents and Settings\mhorgan\Desktop\VBtest.mdb"
        con.Close()
        End

    End Sub
End Class

[mickey_pt]

You didn't need to open a new thread for this...

You are trying to access the data to early in the code, you have your fill method after the ds.Tables("monitor").Rows(0).Item(0)...

[markhorgan1]

sorry my bad ! new to the whole forum thing thought cos i marked it as resolved that i would have had to start a new one ! yeah that worked perfect thanks mickey !! got one more question tho ! my access db contains lots of entries how could i get this code to loop through the db and ping all the ip's and store the results in the column next to it ? also the number of ip's will vary so the looping process would have to be 'dynamic' if that makes any sense ??

Imports System.Data


Public Class Form1

    Dim Ping As Net.NetworkInformation.Ping
    Dim pReply As Net.NetworkInformation.PingReply


    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    End Sub

   Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim ds As New DataSet
        Dim da As OleDb.OleDbDataAdapter
        Dim sql As String
        Dim con As New OleDb.OleDbConnection

        con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Documents and Settings\mhorgan\Desktop\VBtest.mdb"
        con.Open()

        sql = "SELECT * FROM tblone"
        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(ds, "monitor")


        Try
            Ping = New Net.NetworkInformation.Ping

            pReply = Ping.Send(ds.Tables("monitor").Rows(0).Item(0))

            Label1.Text = pReply.Status.ToString


            Dim cb As New OleDb.OleDbCommandBuilder(da)

            ds.Tables("monitor").Rows(0).Item(1) = Label1.Text

            da.Update(ds, "monitor")

        Catch ex As Exception

            MessageBox.Show(ex.ToString())

        End Try

    End Sub


    Private Sub btnExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExit.Click
        Dim con As New OleDb.OleDbConnection
        con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Documents and Settings\mhorgan\Desktop\VBtest.mdb"
        con.Close()
        End

    End Sub
End Class

[mickey_pt]

You can do that using something like this:

Drag a new datagridview to your form, add two columns to the datagridview and then use this code

Dim dgvr As DataGridViewRow
 
        For i = 0 To ds.Tables("monitor").RowsCount - 1
            dgvr = New DataGridViewRow
            DataGridView1.Rows.Add(dgvr)
            DataGridView1.Rows.Item(i).Cells(0).Value = ds.Tables("monitor").Rows(i).Item(0)
            DataGridView1.Rows.Item(i).Cells(1).Value = pReply.Status
            If pReply.Status <> Net.NetworkInformation.IPStatus.Success Then
                DataGridView1.Rows.Item(i).Cells(1).Style.BackColor = Color.Red
            Else
                DataGridView1.Rows.Item(i).Cells(1).Style.BackColor = Color.Green
            End If
 
        Next

I'm leaving to lunch... good luck

EDIT - You must place the ping inside the for...

[markhorgan1]

how do i use this datagridview ? is it def in 2008 express edition ?-forgive the ignorance but ive avoided using any data connection wizards so far to try and better understand the code(something which i struggle to do!) !!

[mickey_pt]

I think it's but i don't use the express edition...

You don't need any real data connection, just go to the toolbox, in the data section, just drag and drop a datagridview to the form, then in the little arrow in the top right you can edit columns, click there and add two columns... you can uncheck the enable adding,deleting and editing.

[markhorgan1]

perfect found it anyways ! tried editing the code but im gettin the following error at run-time when i press button1:Rows cannot be programmatically added to the DataGridView's rows collection when the control is data-bound.
ive underlined the code thats throwing the error. any ideas ??

Imports System.Data


Public Class Form1

    Dim Ping As Net.NetworkInformation.Ping
    Dim pReply As Net.NetworkInformation.PingReply


    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'VBtestDataSet.tblone' table. You can move, or remove it, as needed.
        Me.TbloneTableAdapter.Fill(Me.VBtestDataSet.tblone)

    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim ds As New DataSet
        Dim da As OleDb.OleDbDataAdapter
        Dim sql As String
        Dim con As New OleDb.OleDbConnection

        con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Documents and Settings\mhorgan\Desktop\VBtest.mdb"
        con.Open()

        sql = "SELECT * FROM tblone"
        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(ds, "monitor")


        Dim dgvr As DataGridViewRow
        For i = 0 To ds.Tables("monitor").RowsCount - 1           dgvr = New DataGridViewRow
            DataGridView1.Rows.Add(dgvr)
            DataGridView1.Rows.Item(i).Cells(0).Value = ds.Tables("monitor").Rows(i).Item(0)
            DataGridView1.Rows.Item(i).Cells(1).Value = pReply.Status

            Try
                Ping = New Net.NetworkInformation.Ping

                pReply = Ping.Send(ds.Tables("monitor").Rows(0).Item(0))

                Label1.Text = pReply.Status.ToString


                Dim cb As New OleDb.OleDbCommandBuilder(da)

                ds.Tables("monitor").Rows(0).Item(1) = Label1.Text

                da.Update(ds, "monitor")

            Catch ex As Exception

                MessageBox.Show(ex.ToString())

            End Try


            If pReply.Status <> Net.NetworkInformation.IPStatus.Success Then


                datagridview1.Rows.Item(i).Cells(1).Style.BackColor = Color.Red
            Else
                datagridview1.Rows.Item(i).Cells(1).Style.BackColor = Color.Green
            End If
        Next




      

    End Sub


    Private Sub btnExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExit.Click
        Dim con As New OleDb.OleDbConnection
        con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Documents and Settings\mhorgan\Desktop\VBtest.mdb"
        con.Close()
        End

    End Sub

    Private Sub TbloneBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TbloneBindingNavigatorSaveItem.Click
        Me.Validate()
        Me.TbloneBindingSource.EndEdit()
        Me.TableAdapterManager.UpdateAll(Me.VBtestDataSet)

    End Sub
End Class

[mickey_pt]

You added a datasource to your project...


In that arrow that i told you before, in choose datasource just put none...


Then in this code remove this in form load

Me.TbloneTableAdapter.Fill(Me.VBtestDataSet.tblone)

If you wish to clean the form and code

Go to the solution explorer and remove VBtestDataSet

In the form remove the toolbar that you have in the top (arrows,save,plus,...), and all components that you have in the bottom (data related, table adapter, binding source, dataset,...). Remove also this code

Private Sub TbloneBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TbloneBindingNavigatorSaveItem.Click
        Me.Validate()
        Me.TbloneBindingSource.EndEdit()
        Me.TableAdapterManager.UpdateAll(Me.VBtestDataSet)
 
    End Sub

And the order in your code isn't the correct one...

Try this version:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim ds As New DataSet
        Dim da As OleDb.OleDbDataAdapter
        Dim sql As String
        Dim con As New OleDb.OleDbConnection
 
        con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Documents and Settings\mhorgan\Desktop\VBtest.mdb"
        con.Open()
        sql = "SELECT * FROM tblone"
        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(ds, "monitor")
 
 
        Dim cb As New OleDb.OleDbCommandBuilder(da)
        Ping = New Net.NetworkInformation.Ping
 
        Dim dgvr As DataGridViewRow
        For i = 0 To ds.Tables("monitor").RowsCount - 1
            dgvr = New DataGridViewRow 'Creates a new row
            DataGridView1.Rows.Add(dgvr) 'Add the row
 
            Try
                pReply = Ping.Send(ds.Tables("monitor").Rows(i).Item(0)) 'Sends the ping to the current item
 
                DataGridView1.Rows.Item(i).Cells(0).Value = ds.Tables("monitor").Rows(i).Item(0) 'Updates the value of the new Item
                DataGridView1.Rows.Item(i).Cells(1).Value = pReply.Status 'The value of the status returned
 
                If pReply.Status <> Net.NetworkInformation.IPStatus.Success Then 'Sets the color of the cell
                    DataGridView1.Rows.Item(i).Cells(1).Style.BackColor = Color.Red
                Else
                    DataGridView1.Rows.Item(i).Cells(1).Style.BackColor = Color.Green
                End If
 
 
                ds.Tables("monitor").Rows(i).Item(1) = pReply.Status 'Write in the original datatable
                da.Update(ds, "monitor") 'Updates the database
 
            Catch ex As Exception
                MessageBox.Show(ex.ToString())
            End Try
        Next
    End Sub

[markhorgan1]

thanks ! i added those to colums in the dataviewgrid but i didnt bind them to any data was this correct thing to do ? I made all the changes but still getting one error:

System.IndexOutOfRangeException:There is no row at position 5.
at System.Data.RBTree`1.getnodeByIndex(Int32 user Index).......
.....vbline 35 - maybe something to do with there being only 5 entries in the db ??

Imports System.Data


Public Class Form1

    Dim Ping As Net.NetworkInformation.Ping
    Dim pReply As Net.NetworkInformation.PingReply


    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim i As Integer
        Dim ds As New DataSet
        Dim da As OleDb.OleDbDataAdapter
        Dim sql As String
        Dim con As New OleDb.OleDbConnection

        con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Documents and Settings\mhorgan\Desktop\VBtest.mdb"
        con.Open()

        sql = "SELECT * FROM tblone"
        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(ds, "monitor")

        Dim cb As New OleDb.OleDbCommandBuilder(da)
        Ping = New Net.NetworkInformation.Ping

        Dim dgvr As DataGridViewRow
        For i = 0 To ds.Tables("monitor").Rows.Count - 1
        Next
        dgvr = New DataGridViewRow 'Creates a new row            
        DataGridView1.Rows.Add(dgvr) 'Add the row             

        Try
       
            pReply = Ping.Send(ds.Tables("monitor").Rows(i).Item(0)) 'Sends the ping to the current item                

            DataGridView1.Rows.Item(i).Cells(0).Value = ds.Tables("monitor").Rows(i).Item(0) 'Updates the value of the new Item                

            DataGridView1.Rows.Item(i).Cells(1).Value = pReply.Status 'The value of the status returned                 

            If pReply.Status <> Net.NetworkInformation.IPStatus.Success Then 'Sets the color of the cell         

                DataGridView1.Rows.Item(i).Cells(1).Style.BackColor = Color.Red
            Else
                DataGridView1.Rows.Item(i).Cells(1).Style.BackColor = Color.Green
            End If

            ds.Tables("monitor").Rows(i).Item(1) = pReply.Status 'Write in the original datatable
            da.Update(ds, "monitor") 'Updates the database
        Catch ex As Exception
            MessageBox.Show(ex.ToString())
        End Try
        'Next
    End Sub


    Private Sub btnExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExit.Click
        Dim con As New OleDb.OleDbConnection
        con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Documents and Settings\mhorgan\Desktop\VBtest.mdb"
        con.Close()
        End

    End Sub


End Class

[mickey_pt]

Replace this:

For i = 0 To ds.Tables("monitor").Rows.Count - 1

for this

For i = 0 To ds.Tables("monitor").Rows.Count - 2

and check what happens

[markhorgan1]

another error im afraid:

System.ArguementOutOfRangeException: Index was out of range.Must be non negative and less than the size of the collection.
Parameter name: index
at System.Collections.ArrayList.get_Item(Int32 index)

i declared i as an integer(which wasnt in your code) as it wasnt recognising i,might that have somethin to do with it?

Imports System.Data


Public Class Form1

    Dim Ping As Net.NetworkInformation.Ping
    Dim pReply As Net.NetworkInformation.PingReply


    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim i As Integer
        Dim ds As New DataSet
        Dim da As OleDb.OleDbDataAdapter
        Dim sql As String
        Dim con As New OleDb.OleDbConnection

        con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Documents and Settings\mhorgan\Desktop\VBtest.mdb"
        con.Open()

        sql = "SELECT * FROM tblone"
        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(ds, "monitor")

        Dim cb As New OleDb.OleDbCommandBuilder(da)
        Ping = New Net.NetworkInformation.Ping

        Dim dgvr As DataGridViewRow
        For i = 0 To ds.Tables("monitor").Rows.Count - 2
        Next
        dgvr = New DataGridViewRow 'Creates a new row            
        DataGridView1.Rows.Add(dgvr) 'Add the row             

        Try
       
            pReply = Ping.Send(ds.Tables("monitor").Rows(i).Item(0)) 'Sends the ping to the current item                

            DataGridView1.Rows.Item(i).Cells(0).Value = ds.Tables("monitor").Rows(i).Item(0) 'Updates the value of the new Item                

            DataGridView1.Rows.Item(i).Cells(1).Value = pReply.Status 'The value of the status returned                 

            If pReply.Status <> Net.NetworkInformation.IPStatus.Success Then 'Sets the color of the cell         

                DataGridView1.Rows.Item(i).Cells(1).Style.BackColor = Color.Red
            Else
                DataGridView1.Rows.Item(i).Cells(1).Style.BackColor = Color.Green
            End If

            ds.Tables("monitor").Rows(i).Item(1) = pReply.Status 'Write in the original datatable
            da.Update(ds, "monitor") 'Updates the database
        Catch ex As Exception
            MessageBox.Show(ex.ToString())
        End Try
        'Next
    End Sub


    Private Sub btnExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExit.Click
        Dim con As New OleDb.OleDbConnection
        con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Documents and Settings\mhorgan\Desktop\VBtest.mdb"
        con.Close()
        End

    End Sub


End Class

[mickey_pt]

Here works fine...

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim i As Integer
        Dim ds As New DataSet
        Dim da As OleDb.OleDbDataAdapter
        Dim sql As String
        Dim con As New OleDb.OleDbConnection
 
        con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Documents and Settings\mhorgan\Desktop\VBtest.mdb"
        con.Open()
 
        sql = "SELECT * FROM tblone"
        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(ds, "monitor")
 
        Dim cb As New OleDb.OleDbCommandBuilder(da)
        Ping = New Net.NetworkInformation.Ping
 
        Dim dgvr As DataGridViewRow
        For i = 0 To ds.Tables("monitor").Rows.Count - 2
        dgvr = New DataGridViewRow 'Creates a new row            
        DataGridView1.Rows.Add(dgvr) 'Add the row             
 
        Try
       
            pReply = Ping.Send(ds.Tables("monitor").Rows(i).Item(0)) 'Sends the ping to the current item                
 
            DataGridView1.Rows.Item(i).Cells(0).Value = ds.Tables("monitor").Rows(i).Item(0) 'Updates the value of the new Item                
 
            DataGridView1.Rows.Item(i).Cells(1).Value = pReply.Status 'The value of the status returned                 
 
            If pReply.Status <> Net.NetworkInformation.IPStatus.Success Then 'Sets the color of the cell         
 
                DataGridView1.Rows.Item(i).Cells(1).Style.BackColor = Color.Red
            Else
                DataGridView1.Rows.Item(i).Cells(1).Style.BackColor = Color.Green
            End If
 
            ds.Tables("monitor").Rows(i).Item(1) = pReply.Status 'Write in the original datatable
            da.Update(ds, "monitor") 'Updates the database
        Catch ex As Exception
            MessageBox.Show(ex.ToString())
        End Try
        Next
    End Sub

Just replace the button click with this code.... You have added a next after the for... and commented the correct next...

[markhorgan1]

ha your a genius ! congrats you've earned the rest of the day off !!

[mickey_pt]

By the way the for should be till -1 and not -2, check your database if the last field has all values...

[markhorgan1]

okay for the next part i tried using the code on a copy of the actual table im using ,i changed tblone to tblSwitch and ip and status are now the 6th and 7th column (item(s)5,6) but im getting an error because of the other columns in the table ? is there a way to get the code to ignore these other columns ? and also to read in and output the results so that the table is in the same order as it was before the vb app runs ?

error: System.Data.oledb.oledbException:syntax error (missing operator) in query expression '((switchid=?) and ((?=1 and Switchname is null)....... and it continues to name out the headings of the rest of the columns in the table

Imports System.Data


Public Class Form1

    Dim Ping As Net.NetworkInformation.Ping
    Dim pReply As Net.NetworkInformation.PingReply


    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim i As Integer
        Dim ds As New DataSet
        Dim da As OleDb.OleDbDataAdapter
        Dim sql As String
        Dim con As New OleDb.OleDbConnection

        con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Documents and Settings\mhorgan\Desktop\VBtest.mdb"
        con.Open()

        sql = "SELECT * FROM tblSwitch"
        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(ds, "monitor")

        Dim cb As New OleDb.OleDbCommandBuilder(da)
        Ping = New Net.NetworkInformation.Ping

        Dim dgvr As DataGridViewRow
        For i = 0 To ds.Tables("monitor").Rows.Count - 1

            dgvr = New DataGridViewRow 'Creates a new row            
            DataGridView1.Rows.Add(dgvr) 'Add the row             

            Try

                pReply = Ping.Send(ds.Tables("monitor").Rows(i).Item(5)) 'Sends the ping to the current item                

                DataGridView1.Rows.Item(i).Cells(0).Value = ds.Tables("monitor").Rows(i).Item(5) 'Updates the value of the new Item                

                DataGridView1.Rows.Item(i).Cells(1).Value = pReply.Status 'The value of the status returned                 

                If pReply.Status <> Net.NetworkInformation.IPStatus.Success Then 'Sets the color of the cell         

                    DataGridView1.Rows.Item(i).Cells(1).Style.BackColor = Color.Red
                Else
                    DataGridView1.Rows.Item(i).Cells(1).Style.BackColor = Color.Green
                End If

                ds.Tables("monitor").Rows(i).Item(6) = pReply.Status 'Write in the original datatable
                da.Update(ds, "monitor") 'Updates the database
            Catch ex As Exception
                MessageBox.Show(ex.ToString())
            End Try
        Next
    End Sub


    Private Sub btnExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExit.Click
        Dim con As New OleDb.OleDbConnection
        con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Documents and Settings\mhorgan\Desktop\VBtest.mdb"
        con.Close()
        End

    End Sub

End Class

any ideas whats wrong ?

[mickey_pt]

I think that you must set some values in the new table, like you said, now you have more columns, and you have to set the values for that columns...

[markhorgan1]

so what your saying is i have to set values for the columns in vb even though the columns arent in the datagrid view or arent referenced in the code ?

[mickey_pt]

yes, because those columns are required for the table...

[markhorgan1]

im a little confused , is this something along the right lines or am i way off ??

dim switchID as integer
ds.Tables("monitor").Rows(0).Item(0) = switchID

sorry for answering my own thread but im pretty sure this would only set switchID to an integer value no ?

[mickey_pt]

You can do something like that, but don't forget to set all values in the row...

[chris128]

Im confused, you are just trying to update ONE column in the database when you find out if the ping is successful right? If that is the case then you shouldn't have to specify values for all of the other columns... or am I missing something and you are trying to add a new row rather than update an existing one?

[markhorgan1]

yeah thats the idea chris, read in the ip from one column (item5) and update the result into the next column (item6) im a bit confused aswell as to why the code cant just ignore the other colums ?

[mickey_pt]

I thought that to, but the error tells that is missing all the values:

in query expression '((switchid=?) and ((?=1 and Switchname is null)....... and it continues to name out the headings of the rest of the columns in the table

[chris128]

To be honest I found it hard to understand and had problems like this when I started trying to work with databases and in the end I found it much easier to understand and work with if I didnt use the DataSets and DataAdapters etc. So I just update the database manually using an SqlCommand (guessing its OledbCommand for you with Access though), so I would just read in the IPs and the unique ID of each row from the database, send the pings and then update each row in the database manually with the result. The SqlCommand statement would look like this:

Dim updatecommand As New SqlCommand("UPDATE MyTable SET ResultColumn=@Result WHERE ID=@ID", MySqlConnection)

obviously thats a rough example but you get the idea (@result and @ID are parameters, I just havent bothered to write that bit of code that adds them as its just a brief example)

[markhorgan1]

the code above(#15) still reads in the two columns into the datagrid viewer and pings the ip address but it just doesnt write the result to the db . that make any sense ?

[chris128]

OK here's something that works fine for me, it updates the relevant row with the result from each ping. This is my preferred method of doing things like this but I am not saying it is any better or worse than the existing way you are doing it, I just know it works.

Private Sub PingBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles PingBtn.Click
 
        'Create our connection to the Access database
        Dim AccessConnection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\test\pingDB.mdb;User Id=admin;Password=;")
        'Create the command that will be used to select all of the data in the IP and ID columns
        Dim SelectCommand As New OleDbCommand("SELECT IP,ID FROM PingTable", AccessConnection)
 
        Try
            'Open the connection to the database
            AccessConnection.Open()
 
            'Use a DataReader to access the data that the SELECT command returns
            Dim Reader As OleDbDataReader = SelectCommand.ExecuteReader
 
            'Start to loop through the results
            'This loop will be restarted for each row in the database
            Do While Reader.Read
 
                Dim PingSend As New Net.NetworkInformation.Ping
 
                'As you can see we use the DataReader to get the value of the
                'column named IP in the current row that we are looping through and we 
                'send a ping to that
                Dim PingResult As Net.NetworkInformation.PingReply = PingSend.Send(Reader("IP"))
                Dim UpdateCommand As OleDbCommand 'will use this in a minute
 
                'Check the result of the ping and update the database accordingly
                If PingResult.Status = Net.NetworkInformation.IPStatus.Success Then
                    'Create the command object that will update the table and set the
                    'command text to set the Result column to True
                    UpdateCommand = New OleDbCommand("UPDATE PingTable SET Result=True WHERE ID=@ID", AccessConnection)
                    'Write to richtextbox on the form
                    logbox.AppendText("Reply from " & Reader("IP").ToString & vbNewLine)
                Else
                    'Create the command object that will update the table and set the
                    'command text to set the Result column to False
                    UpdateCommand = New OleDbCommand("UPDATE PingTable SET Result=False WHERE ID=@ID", AccessConnection)
                    'Write to richtextbox on the form
                    logbox.AppendText(Reader("IP").ToString & " did not respond" & vbNewLine)
                End If
 
                'Add the parameter @ID that is referenced in the update command string
                'We set this to the value of the ID column in the row that the DataReader
                'is currently looping through
                UpdateCommand.Parameters.Add("@ID", OleDbType.Integer).Value = Reader("ID")
 
                'Execure the update command. This is the point where the Result column is
                'actually updated
                UpdateCommand.ExecuteNonQuery()
 
                'Start the loop again for the next row in the database
            Loop
        Catch ex As Exception
            'Very basic error handling
            logbox.AppendText("ERROR: " & ex.Message & vbNewLine)
        Finally
            'Make sure we close the connection
            
                AccessConnection.Close()
            
        End Try
 
    End Sub

It is by no means perfect and you wont be able to just copy and paste it because I have probably got different column names to you and will also have different database path etc. It also freezes while it is executing the pings because it is not using a background thread etc but its just an example. Also, the object you see in there named "logbox" is a richtextbox on my form that I just used to output something on the screen so that I didnt have to keep checking the database each time I ran it etc Hope it helps and feel free to ask any questions.

[chris128]

I've updated my last post to add comments to the code so that should help you see what its doing

[mickey_pt]

Just for testing...

Add a new datagridview to your form... resize it if you have to...

Then in the for cycle just add this after this lines

sql = "SELECT * FROM tblSwitch"
        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(ds, "monitor")

Datagridview2.datasource=ds.tables("monitor")

And comment the da.Update line, and check what changes do you see in the datagridview2 after running the code...

*EDIT* - If you want you can try this, or use the method of chris128

[markhorgan1]

tried using your method chris but got this error in the logbox:
ERROR: IErrorInfo.GetDescription failed with E_FAIL(0x80004005).

i did have to make a few changes to the code also to make it compatible
ie OleDbCommand => OleDb.OleDbCommand

Imports System.Data


Public Class Form1

    Dim Ping As Net.NetworkInformation.Ping
    Dim pReply As Net.NetworkInformation.PingReply


    Private Sub PingBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles PingBtn.Click

        'Create our connection to the Access database       
        Dim AccessConnection As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\mhorgan\Desktop\VBtest.mdb;User Id=admin;Password=;")
        'Create the command that will be used to select all of the data in the IP and ID columns        
        Dim SelectCommand As New OleDb.OleDbCommand("SELECT SwitchID,SwitchName,ComId,Make/Model,Port Type(s),IP,Result,Number of ports FROM tblSwitch", AccessConnection)

        Try

            'Open the connection to the database        
            AccessConnection.Open()
            'Use a DataReader to access the data that the SELECT command returns           
            Dim Reader As OleDb.OleDbDataReader = SelectCommand.ExecuteReader
            'Start to loop through the results            
            'This loop will be restarted for each row in the database
            Do While Reader.Read
                Dim PingSend As New Net.NetworkInformation.Ping
                Dim PingResult As Net.NetworkInformation.PingReply = PingSend.Send(Reader("IP"))
                Dim UpdateCommand As OleDb.OleDbCommand
                If PingResult.Status = Net.NetworkInformation.IPStatus.Success Then

                    UpdateCommand = New OleDb.OleDbCommand("UPDATE PingTable SET Result=True WHERE ID=@ID", AccessConnection)
                    logbox.AppendText("Reply from " & Reader("IP").ToString & vbNewLine)
                Else
                    UpdateCommand = New OleDb.OleDbCommand("UPDATE PingTable SET Result=False WHERE ID=@ID", AccessConnection)
                    logbox.AppendText(Reader("IP").ToString & " did not respond" & vbNewLine)
                End If

                UpdateCommand.Parameters.Add("@ID", OleDb.OleDbType.Integer).Value = Reader("ID")
                UpdateCommand.ExecuteNonQuery()

            Loop
        Catch ex As Exception  'Very basic error handling           
            logbox.AppendText("ERROR: " & ex.Message & vbNewLine)
        Finally 'Make sure we close the connection

            If AccessConnection.State = ConnectionState.Open Then


                AccessConnection.Close()
            End If
        End Try
    End Sub








    Private Sub btnExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExit.Click
        Dim con As New OleDb.OleDbConnection
        con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Documents and Settings\mhorgan\Desktop\VBtest.mdb"
        con.Close()
        End
    End Sub

End Class

[markhorgan1]

will also try your method mickey !

[mickey_pt]

My method it's only to see what changes the data table suffers in the process...

Doesn't really solve your problem, but if all rows in the data table have values, then calling the update method at end of the cycle it must do the job.

[markhorgan1]

i added the second datagridview (columns 3 and 4) but wasnt clear on the parts of the code you wanted me to edit can you show below please

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim i As Integer
        Dim ds As New DataSet
        Dim da As OleDb.OleDbDataAdapter
        Dim sql As String
        Dim con As New OleDb.OleDbConnection

        con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Documents and Settings\mhorgan\Desktop\VBtest.mdb"
        con.Open()

        sql = "SELECT * FROM tblone"
        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(ds, "monitor")

        Dim cb As New OleDb.OleDbCommandBuilder(da)
        Ping = New Net.NetworkInformation.Ping

        Dim dgvr As DataGridViewRow
        For i = 0 To ds.Tables("monitor").Rows.Count - 1

            dgvr = New DataGridViewRow 'Creates a new row            
            DataGridView1.Rows.Add(dgvr) 'Add the row             

            Try

                pReply = Ping.Send(ds.Tables("monitor").Rows(i).Item(0)) 'Sends the ping to the current item                

                DataGridView1.Rows.Item(i).Cells(0).Value = ds.Tables("monitor").Rows(i).Item(0) 'Updates the value of the new Item                

                DataGridView1.Rows.Item(i).Cells(1).Value = pReply.Status 'The value of the status returned                 

                If pReply.Status <> Net.NetworkInformation.IPStatus.Success Then 'Sets the color of the cell         

                    DataGridView1.Rows.Item(i).Cells(1).Style.BackColor = Color.Red
                Else
                    DataGridView1.Rows.Item(i).Cells(1).Style.BackColor = Color.Green
                End If

                ds.Tables("monitor").Rows(i).Item(1) = pReply.Status 'Write in the original datatable
                da.Update(ds, "monitor") 'Updates the database
            Catch ex As Exception
                MessageBox.Show(ex.ToString())
            End Try
        Next
    End Sub

[markhorgan1]

okay ive tried yet another approach,working off the code in post#12, i changed the sql command from select * from tblSwitch to just Select IP,Status from tblSwitch. and ive moved the two columns(IP,Status) so that they are the first two columns in the table (items 0 and 1) but im still getting the following error:

System.InvalidOperationExceptionynamic SQL generation for the updatecommand is not supported against a selectcommand that does not return any key column information....

any ideas how to solve this error ?? - code is below

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim i As Integer
        Dim ds As New DataSet
        Dim da As OleDb.OleDbDataAdapter
        Dim sql As String
        Dim con As New OleDb.OleDbConnection

        con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Documents and Settings\mhorgan\Desktop\VBtest.mdb"
        con.Open()

        sql = "SELECT IP,Status FROM tblSwitch"
        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(ds, "monitor")

        Dim cb As New OleDb.OleDbCommandBuilder(da)
        Ping = New Net.NetworkInformation.Ping

        Dim dgvr As DataGridViewRow
        For i = 0 To ds.Tables("monitor").Rows.Count - 1

            dgvr = New DataGridViewRow 'Creates a new row            
            DataGridView1.Rows.Add(dgvr) 'Add the row             

            Try

                pReply = Ping.Send(ds.Tables("monitor").Rows(i).Item(0)) 'Sends the ping to the current item                

                DataGridView1.Rows.Item(i).Cells(0).Value = ds.Tables("monitor").Rows(i).Item(0) 'Updates the value of the new Item                

                DataGridView1.Rows.Item(i).Cells(1).Value = pReply.Status 'The value of the status returned                 

                If pReply.Status <> Net.NetworkInformation.IPStatus.Success Then 'Sets the color of the cell         

                    DataGridView1.Rows.Item(i).Cells(1).Style.BackColor = Color.Red
                Else
                    DataGridView1.Rows.Item(i).Cells(1).Style.BackColor = Color.Green
                End If

                ds.Tables("monitor").Rows(i).Item(1) = pReply.Status 'Write in the original datatable
                da.Update(ds, "monitor") 'Updates the database
            Catch ex As Exception
                MessageBox.Show(ex.ToString())
            End Try
        Next
    End Sub

[mickey_pt]

The datagridview2 doesnt have any columns, let it empty...

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim i As Integer
        Dim ds As New DataSet
        Dim da As OleDb.OleDbDataAdapter
        Dim sql As String
        Dim con As New OleDb.OleDbConnection
 
        con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Documents and Settings\mhorgan\Desktop\VBtest.mdb"
        con.Open()
 
        sql = "SELECT * FROM tblone"
        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(ds, "monitor")
        DataGridView2.DataSource = ds.Tables("monitor")
 
        Dim cb As New OleDb.OleDbCommandBuilder(da)
        Ping = New Net.NetworkInformation.Ping
 
        Dim dgvr As DataGridViewRow
        For i = 0 To ds.Tables("monitor").Rows.Count - 1
 
            dgvr = New DataGridViewRow 'Creates a new row            
            DataGridView1.Rows.Add(dgvr) 'Add the row             
 
            Try
                pReply = Ping.Send(ds.Tables("monitor").Rows(i).Item(0)) 'Sends the ping to the current item                
                DataGridView1.Rows.Item(i).Cells(0).Value = ds.Tables("monitor").Rows(i).Item(0) 'Updates the value of the new Item                
                DataGridView1.Rows.Item(i).Cells(1).Value = pReply.Status 'The value of the status returned                 
                If pReply.Status <> Net.NetworkInformation.IPStatus.Success Then 'Sets the color of the cell         
                    DataGridView1.Rows.Item(i).Cells(1).Style.BackColor = Color.Red
                Else
                    DataGridView1.Rows.Item(i).Cells(1).Style.BackColor = Color.Green
                End If
 
                ds.Tables("monitor").Rows(i).Item(1) = pReply.Status 'Write in the original datatable
                'da.Update(ds, "monitor") 'Updates the database
            Catch ex As Exception
                MessageBox.Show(ex.ToString())
            End Try
        Next
    End Sub

If you can't solve this,and if you don't mind attach your project and your db and i take a look to it... and correct the code...

[markhorgan1]

perfect thanks ! can i attach straight to the forum ? cant see how !! if not i can email it to you its only 111k when zipped !

[chris128]

tried using your method chris but got this error in the logbox:
ERROR: IErrorInfo.GetDescription failed with E_FAIL(0x80004005).

i did have to make a few changes to the code also to make it compatible
ie OleDbCommand => OleDb.OleDbCommand

Imports System.Data


Public Class Form1

    Dim Ping As Net.NetworkInformation.Ping
    Dim pReply As Net.NetworkInformation.PingReply


    Private Sub PingBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles PingBtn.Click

        'Create our connection to the Access database       
        Dim AccessConnection As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\mhorgan\Desktop\VBtest.mdb;User Id=admin;Password=;")
        'Create the command that will be used to select all of the data in the IP and ID columns        
        Dim SelectCommand As New OleDb.OleDbCommand("SELECT SwitchID,SwitchName,ComId,Make/Model,Port Type(s),IP,Result,Number of ports FROM tblSwitch", AccessConnection)

        Try

            'Open the connection to the database        
            AccessConnection.Open()
            'Use a DataReader to access the data that the SELECT command returns           
            Dim Reader As OleDb.OleDbDataReader = SelectCommand.ExecuteReader
            'Start to loop through the results            
            'This loop will be restarted for each row in the database
            Do While Reader.Read
                Dim PingSend As New Net.NetworkInformation.Ping
                Dim PingResult As Net.NetworkInformation.PingReply = PingSend.Send(Reader("IP"))
                Dim UpdateCommand As OleDb.OleDbCommand
                If PingResult.Status = Net.NetworkInformation.IPStatus.Success Then

                    UpdateCommand = New OleDb.OleDbCommand("UPDATE PingTable SET Result=True WHERE ID=@ID", AccessConnection)
                    logbox.AppendText("Reply from " & Reader("IP").ToString & vbNewLine)
                Else
                    UpdateCommand = New OleDb.OleDbCommand("UPDATE PingTable SET Result=False WHERE ID=@ID", AccessConnection)
                    logbox.AppendText(Reader("IP").ToString & " did not respond" & vbNewLine)
                End If

                UpdateCommand.Parameters.Add("@ID", OleDb.OleDbType.Integer).Value = Reader("ID")
                UpdateCommand.ExecuteNonQuery()

            Loop
        Catch ex As Exception  'Very basic error handling           
            logbox.AppendText("ERROR: " & ex.Message & vbNewLine)
        Finally 'Make sure we close the connection

            If AccessConnection.State = ConnectionState.Open Then


                AccessConnection.Close()
            End If
        End Try
    End Sub








    Private Sub btnExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExit.Click
        Dim con As New OleDb.OleDbConnection
        con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Documents and Settings\mhorgan\Desktop\VBtest.mdb"
        con.Close()
        End
    End Sub

End Class

Well you have not altered my code properly to run with your table
You haven't updated the UpdateCommand text, so it is still trying to update a table called PingTable. Also your select command is retrieving unnecessary columns (and as some of these have spaces and special characters in then your command is probably not working correctly anyway). You only need to select the ID and IP columns as they are the only ones that the Reader tries to use.

Oh and this bit:

Dim con As New OleDb.OleDbConnection
        con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Documents and Settings\mhorgan\Desktop\VBtest.mdb"
        con.Close()

Is totally pointless I'm afraid as all it is doing is creating a new connection and then closing it. Besides the fact that the connection is not ever Opened, this is also pointless because it is not affecting the connection that you used in the rest of the code.

[mickey_pt]

The problem was in your database, the name of the columns can't have spaces and some special character like the '/' that you have....

Remove all the spaces, and the bars and use this code...

[markhorgan1]

i have this code:

Public Class Form1
    Dim Ping As Net.NetworkInformation.Ping
    Dim pReply As Net.NetworkInformation.PingReply



    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim i As Integer
        Dim ds As New DataSet
        Dim da As OleDb.OleDbDataAdapter
        Dim sql As String
        Dim con As New OleDb.OleDbConnection
       

        con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Documents and Settings\mhorgan\Desktop\Network Map.mdb"
        con.Open()

        sql = "SELECT * FROM tblSwitch"
        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(ds, "monitor")

        Dim cb As New OleDb.OleDbCommandBuilder(da)
        Ping = New Net.NetworkInformation.Ping

        Dim dgvr As DataGridViewRow
        For i = 0 To ds.Tables("monitor").Rows.Count - 1

            ' Set up the progress bar's properties
            ProgressBar1.Minimum = 0
            ProgressBar1.Maximum = 100
            ProgressBar1.Value = 0



            dgvr = New DataGridViewRow 'Creates a new row            
            DataGridView1.Rows.Add(dgvr) 'Add the row             

            Try

                pReply = Ping.Send(ds.Tables("monitor").Rows(i).Item(5)) 'Sends the ping to the current item                

                DataGridView1.Rows.Item(i).Cells(0).Value = ds.Tables("monitor").Rows(i).Item(5) 'Updates the value of the new Item                

                DataGridView1.Rows.Item(i).Cells(1).Value = pReply.Status 'The value of the status returned                 

                If pReply.Status <> Net.NetworkInformation.IPStatus.Success Then 'Sets the color of the cell         

                    DataGridView1.Rows.Item(i).Cells(1).Style.BackColor = Color.Red
                Else
                    DataGridView1.Rows.Item(i).Cells(1).Style.BackColor = Color.Green
                End If

                ds.Tables("monitor").Rows(i).Item(6) = pReply.Status 'Write in the original datatable
                da.Update(ds, "monitor") 'Updates the database
            Catch ex As Exception
                MessageBox.Show(ex.ToString())
            End Try
        Next
    End Sub


    Private Sub btnExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExit.Click
        Dim con As New OleDb.OleDbConnection
        con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Documents and Settings\mhorgan\Desktop\Network Map.mdb"
        con.Close()
        End

    End Sub

  
End Class

to ping a list of ip's in a db , it works perfectly on a small sample db but when i switch it over to the db i intend to use it on it throws the error below

the db im using has over 400 ip's so i think the problem is that vb cant hold enough of the results of the pings before it commits them to the db/displays them in the datagridview.

getting this error: The CLR has been unable to transition from COM context 0x33adf80 to COM context 0x33ae0f0 for 60 seconds. The thread that owns the destination context/apartment is most likely either doing a non pumping wait or processing a very long running operation without pumping Windows messages. This situation generally has a negative performance impact and may even lead to the application becoming non responsive or memory usage accumulating continually over time. To avoid this problem, all single threaded apartment (STA) threads should use pumping wait primitives (such as CoWaitForMultipleHandles) and routinely pump messages during long running operations.

anyone know how these CoWaitForMultipleHandles work ? would appreciate any help !

[mickey_pt]

That error happens because the application stay's long time without responses...

You have two options, create a new thread that do the job in the background. Or you can simple put Application.doEvents(), to the application don't freeze...

[chris128]

The best thing to do would be to put the ping work in a background thread but you may find that a bit complicated if you are new to VB.NET in general. Sticking Application.DoEvents() in the start of the loop will probably get rid of the error you are getting but you will still find that your program appears to freeze for a few seconds while it sends each ping.

PS you might want to create a new thread for problems like this instead of just adding on to this already quite long thread each time you have a problem with this same program