3 Replies - 2318 Views - Last Post: 28 September 2011 - 08:12 AM Rate Topic: -----

#1 Madiha Ahmed  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 21
  • Joined: 01-July 11

Updating Ms Access using VB.net

Posted 25 September 2011 - 05:20 AM

I have a program that discover the network..and give IP addresses of the active computers..now I want to connect my program with MS access database..in my database I added the IP addresses..but I want to update my database everytime I debug my program..i want my database to check if IP address is already in it if yes then check its status and depending on the status update the database(0 in the case of off and 1 in case of active)..and if IP address is not already present then add that in the database..and do the same with every IP addresse that my Network Monitor program has discovered ..m having problem in updating my databse and check if IP addresse are already present
Here is the Code
Imports Microsoft.Win32
Imports System.Net.NetworkInformation
Imports System.Threading
Imports System.IO
Imports System.Runtime.InteropServices
Imports System.Data

Public Class NetworkDiscovery
    Dim con As New OleDb.OleDbConnection
    Dim dbProvider As String
    Dim dbSource As String
    Dim ds As New DataSet
    Dim da As OleDb.OleDbDataAdapter
    Dim sql As String

    Dim ObjListCpm As AllNetworkComputers
    Dim objsysInfo As New SystemInfo()

    Private Sub NetMonitor_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Wgroup = objsysInfo.GetDomain()
        ObjListCpm = New AllNetworkComputers(Wgroup, TVComputers, PnlComps)
        ObjListCpm.ListComputers()
        Timer1.Enabled = True
    End Sub

    Private Sub Timer1_Tick(ByVal sender As Object, ByVal e As System.EventArgs) Handles Timer1.Tick
        For Each Nc As ComputersSpecifications In PnlComps.Controls
            ObjListCpm = New AllNetworkComputers(Nc.MachineName, chkSts(Nc.MachineName), PnlComps)
            ObjListCpm.CheckStatus()
        Next
    End Sub

    Private Sub ScanNetworkToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ScanNetworkToolStripMenuItem.Click
        Wgroup = objsysInfo.GetDomain()
        ObjListCpm = New AllNetworkComputers(Wgroup, TVComputers, PnlComps)
        ObjListCpm.ListComputers()
    End Sub

    Private Sub ExitToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ExitToolStripMenuItem.Click
        End
    End Sub

    Private Sub TVComputers_MouseClick(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles TVComputers.MouseClick
        Dim SelItm As String = TVComputers.SelectedNode.Text
        If e.Button = Windows.Forms.MouseButtons.Right Then
            TVComputers.ContextMenuStrip = CMSCompTv
            CMSCompTv.Show(e.X, e.Y)

            Dim SelNode As TreeNode
            SelNode = TVComputers.GetNodeAt(e.X, e.Y)
            TVComputers.SelectedNode = SelNode
        End If
    End Sub

    Private Sub CheckStateNowToolStripMenuItem1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CheckStateNowToolStripMenuItem1.Click
        ObjListCpm = New AllNetworkComputers(TVComputers.SelectedNode.Text, chkSts(TVComputers.SelectedNode.Text), PnlComps)
        ObjListCpm.CheckStatus()
    End Sub

    Private Sub CheckAllServersNowToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CheckAllServersNowToolStripMenuItem.Click
        For Each Nc As ComputersSpecifications In PnlComps.Controls
            ObjListCpm = New AllNetworkComputers(Nc.MachineName, chkSts(Nc.MachineName), PnlComps)
            ObjListCpm.CheckStatus()
        Next
    End Sub

    Private Sub CheckStateNowToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CheckStateNowToolStripMenuItem.Click
        ObjListCpm = New AllNetworkComputers(TVComputers.SelectedNode.Text, chkSts(TVComputers.SelectedNode.Text), PnlComps)
        ObjListCpm.CheckStatus()
    End Sub

    Private Function chkSts(ByVal Comp As String) As String
        Dim Status As String = ""
        For Each Nc As ComputersSpecifications In PnlComps.Controls
            If Nc.MachineName = Comp Then
                If Nc.Status = ComputersSpecifications.UserStatus.Offline Then
                    Status = "OffLine"
                Else
                    Status = "Online"
                End If
            End If
        Next
        Return Status
    End Function


    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        dbProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"
        dbSource = "Data Source = C:\Documents and Settings\jj\Desktop\IPAddress.mdb"

        con.ConnectionString = dbProvider & dbSource
        con.Open()
        sql = "SELECT * FROM tblIP"
        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(ds, "IPAddress")
        con.Close()
    End Sub
End Class


Is This A Good Question/Topic? 0
  • +

Replies To: Updating Ms Access using VB.net

#2 Ionut  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 385
  • View blog
  • Posts: 1,057
  • Joined: 17-July 10

Re: Updating Ms Access using VB.net

Posted 25 September 2011 - 05:43 AM

The problem is that you don't have any update statements defined.
If you want to check if there is an IP in database
Select Count(*) from tblIP where IpColumn = @value


Use add this statement to an OleDbCommand object and call .ExecuteScalar method. That will return the number of records that match the condition. If 0, insert
INSERT INTO tblIp VALUES(@value1, @value2, ...)


If 1, update
Update tblIp Set Column1 =@Value1
where ColumnIp = @IpValue


Add these to OleDbCommand object and call .ExecuteNonQuery

Pay attention that @Value1 ,@ IpValue etc are parameters and you have to assign OleDbParameter objects to your commands.
OleDBCommand.Parameters.AddWithValue("@Value1", <value>)


Was This Post Helpful? 1
  • +
  • -

#3 Madiha Ahmed  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 21
  • Joined: 01-July 11

Re: Updating Ms Access using VB.net

Posted 28 September 2011 - 07:48 AM

Thanks for the reply..But I DON'T want to check If there is IP addresses present in the database or not..I want to check if the IP addresses that are returned from my Network monitor program are present in the database or not..if yes then check their status (o in case of OFF and 1 in case of ON)and if not present then add the IP address in the database and do this whole process for all the IP addresses which returned as on output of my Network discover program..

This post has been edited by Madiha Ahmed: 28 September 2011 - 07:54 AM

Was This Post Helpful? 0
  • +
  • -

#4 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9265
  • View blog
  • Posts: 34,756
  • Joined: 12-June 08

Re: Updating Ms Access using VB.net

Posted 28 September 2011 - 08:12 AM

Isn't this:

Quote

Thanks for the reply..But I DON'T want to check If there is IP addresses present in the database or not..


... the same thing as this?

Quote

I want to check if the IP addresses that are returned from my Network monitor program are present in the database or not..



Your steps are:
for each IP address you have.
query the database (SELECT statement) if that IP address exists in the table.
if so UPDATE the status
if not INSERT the ip address
repeat for loop.

Ionut's response is correct for the querying the table if the IP address exists or not.. though one could bicker about count(*) vs count(column_id) vs select 1 for the execute scalar.. ;)
Was This Post Helpful? 1
  • +
  • -

Page 1 of 1