locked
Running access query in vb.net RRS feed

  • Question

  • Hello,
    I have an access database and have a simple query in which i have 1 parameter.
    I want to run this query in vb.net and display the results in a datagrid or anything but in vb.net.
    I'm able to run the query but wht should i do to display the results of the query.
    the code i m using now is:

     

    Dim accApp As Object  
    accApp = GetObject("c:\mydb.mdb")  
     
    'this will run the query in the database  
    accApp.docmd.OpenQuery("myQuery")  
    accApp.Run("myQuery")   
     
    Wednesday, August 20, 2008 1:15 PM

Answers

  •  Hello farah123,

    I hope this helps!

    Dim
    Connection As OleDb.OleDbConnection = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\mydb.mdb;Jet OLEDB:Database Password=password")
    Dim Command As New OleDb.OleDbCommand
    Dim ds As New Dataset
    Dim da As New OleDb.OleDbDataAdapter
    Dim row As System.Data.DataRow
    Dim Count As Integer

    Connection.Open()  'open up a connection to the database
    Command.Connection = Connection

    da.SelectCommand = New OleDb.OleDbCommand("SELECT * FROM Table1", Connection)
    da.Fill(ds,
    "Table") 'Fill the dataset, ds, with the above SELECT statement
    Count = ds.Tables(
    "Table").Rows.Count

    While Count > -1 'loop through for each row, add it to the datagridview
    row = ds.Tables("Table").Rows.Item(Count)
    Datagridview.rows.add(row.Item(0))
    Count = Count - 1
    End While

    Connection.Close()

    • Edited by Sunrunner Wednesday, August 20, 2008 6:24 PM made a mistake
    • Proposed as answer by Sunrunner Wednesday, August 20, 2008 6:36 PM
    • Marked as answer by Martin Xie - MSFT Monday, August 25, 2008 5:19 AM
    • Edited by Martin Xie - MSFT Monday, August 25, 2008 5:25 AM Edit SELECT statement
    Wednesday, August 20, 2008 6:24 PM
  • farah123 said:
    I have an access database and have a simple query in which i have 1 parameter.
    I want to run this query in vb.net and display the results in a datagrid or anything but in vb.net.


    Thank you sunrunner for your friendly help.

    Hi farah,


    sunrunner has provided a good sample demonstrating how to execute query statement in VB.NET and binding query result to DataGridView.

    In addition, y
    ou can call/execute a Query stored in Access database file in VB.NET code like this:
    Imports System.Data.OleDb     
        
    Public Class Form1     
       
        Private Sub Button1_Click(ByVal sender As System.ObjectByVal e As System.EventArgs) Handles Button1.Click     
            Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=D:\myDB.mdb")     
            Dim cmd As New OleDbCommand     
            cmd.CommandType = CommandType.StoredProcedure     
            cmd.CommandText = "MyQueryName"   
            cmd.Parameters.Add("@Parameter1", OleDbType.VarChar).Value = "value1"  ' Add Parameter
            cmd.Connection = con     
            con.Open()     
            Dim da As OleDbDataAdapter = New OleDbDataAdapter(cmd)     
            Dim ds As DataSet = New DataSet()     
            da.Fill(ds, "Table1")     
            DataGridView1.DataSource = ds.Tables("Table1")  ' Binding to DataGridView   
            con.Close()     
        End Sub   
       
     End Class   

    Code sample: Parameterized query in MS Access database
    http://forums.msdn.microsoft.com/en/vbgeneral/thread/6ba7ec3d-fe34-44c7-8cdf-28984080fa17/


    You can create a Query and store it into Access database file with VB.NET code  like this:
    Imports System.Data.OleDb  
     
    Public Class Form1  
     
        Private Sub Button1_Click(ByVal sender As System.ObjectByVal e As System.EventArgs) Handles Button1.Click  
     
            Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=D:\myDB.mdb")  
     
            Dim cmd As OleDbCommand = New OleDbCommand("CREATE PROC MyQueryName AS SELECT * FROM Table1", con)  
            con.Open()  
            cmd.ExecuteNonQuery()  
            con.Close()  
     
        End Sub 
     
    End Class 


    Best regards,
    Martin Xie

    Monday, August 25, 2008 5:41 AM

All replies

  •  Hello farah123,

    I hope this helps!

    Dim
    Connection As OleDb.OleDbConnection = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\mydb.mdb;Jet OLEDB:Database Password=password")
    Dim Command As New OleDb.OleDbCommand
    Dim ds As New Dataset
    Dim da As New OleDb.OleDbDataAdapter
    Dim row As System.Data.DataRow
    Dim Count As Integer

    Connection.Open()  'open up a connection to the database
    Command.Connection = Connection

    da.SelectCommand = New OleDb.OleDbCommand("SELECT * FROM Table1", Connection)
    da.Fill(ds,
    "Table") 'Fill the dataset, ds, with the above SELECT statement
    Count = ds.Tables(
    "Table").Rows.Count

    While Count > -1 'loop through for each row, add it to the datagridview
    row = ds.Tables("Table").Rows.Item(Count)
    Datagridview.rows.add(row.Item(0))
    Count = Count - 1
    End While

    Connection.Close()

    • Edited by Sunrunner Wednesday, August 20, 2008 6:24 PM made a mistake
    • Proposed as answer by Sunrunner Wednesday, August 20, 2008 6:36 PM
    • Marked as answer by Martin Xie - MSFT Monday, August 25, 2008 5:19 AM
    • Edited by Martin Xie - MSFT Monday, August 25, 2008 5:25 AM Edit SELECT statement
    Wednesday, August 20, 2008 6:24 PM
  • farah123 said:
    I have an access database and have a simple query in which i have 1 parameter.
    I want to run this query in vb.net and display the results in a datagrid or anything but in vb.net.


    Thank you sunrunner for your friendly help.

    Hi farah,


    sunrunner has provided a good sample demonstrating how to execute query statement in VB.NET and binding query result to DataGridView.

    In addition, y
    ou can call/execute a Query stored in Access database file in VB.NET code like this:
    Imports System.Data.OleDb     
        
    Public Class Form1     
       
        Private Sub Button1_Click(ByVal sender As System.ObjectByVal e As System.EventArgs) Handles Button1.Click     
            Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=D:\myDB.mdb")     
            Dim cmd As New OleDbCommand     
            cmd.CommandType = CommandType.StoredProcedure     
            cmd.CommandText = "MyQueryName"   
            cmd.Parameters.Add("@Parameter1", OleDbType.VarChar).Value = "value1"  ' Add Parameter
            cmd.Connection = con     
            con.Open()     
            Dim da As OleDbDataAdapter = New OleDbDataAdapter(cmd)     
            Dim ds As DataSet = New DataSet()     
            da.Fill(ds, "Table1")     
            DataGridView1.DataSource = ds.Tables("Table1")  ' Binding to DataGridView   
            con.Close()     
        End Sub   
       
     End Class   

    Code sample: Parameterized query in MS Access database
    http://forums.msdn.microsoft.com/en/vbgeneral/thread/6ba7ec3d-fe34-44c7-8cdf-28984080fa17/


    You can create a Query and store it into Access database file with VB.NET code  like this:
    Imports System.Data.OleDb  
     
    Public Class Form1  
     
        Private Sub Button1_Click(ByVal sender As System.ObjectByVal e As System.EventArgs) Handles Button1.Click  
     
            Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=D:\myDB.mdb")  
     
            Dim cmd As OleDbCommand = New OleDbCommand("CREATE PROC MyQueryName AS SELECT * FROM Table1", con)  
            con.Open()  
            cmd.ExecuteNonQuery()  
            con.Close()  
     
        End Sub 
     
    End Class 


    Best regards,
    Martin Xie

    Monday, August 25, 2008 5:41 AM
  • hello martin..

    can u help me to store data from vb.net to access...right now i get the data from com port. and i want the data to be save in the access...below is the sample code...


    Public Class frmSerialPortExample
        '----------------------------------------------------------------------------------------------------------
        'Purpose:  Allow for data recieve event to update text box on different thread
        '----------------------------------------------------------------------------------------------------------
        Public Delegate Sub myDelegate()

        '----------------------------------------------------------------------------------------------------------
        'Purpose:  Close and exit
        '----------------------------------------------------------------------------------------------------------
        Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
            Me.Close()
        End Sub

        '----------------------------------------------------------------------------------------------------------
        'Purpose:  Load Form
        '----------------------------------------------------------------------------------------------------------
        Private Sub frmSerialPortExample_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
            For i As Integer = 0 To My.Computer.Ports.SerialPortNames.Count - 1
                cboCommPorts.Items.Add(My.Computer.Ports.SerialPortNames(i))
            Next
            cmdDisconnect.Enabled = False
        End Sub

        '----------------------------------------------------------------------------------------------------------
        'Purpose:  Connect to comm port
        '----------------------------------------------------------------------------------------------------------
        Private Sub cmdConnect_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdConnect.Click
            If SerialPort1.IsOpen Then
                SerialPort1.Close()
            End If
            Try
                With SerialPort1
                    .PortName = cboCommPorts.Text
                    .BaudRate = 9600
                    .Parity = IO.Ports.Parity.None
                    .DataBits = 8
                    .StopBits = IO.Ports.StopBits.One
                End With
                SerialPort1.Open()

                lblMessage.Text = SerialPort1.PortName & " connected"
                cmdDisconnect.Enabled = True
                cmdConnect.Enabled = False
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try
        End Sub

        '----------------------------------------------------------------------------------------------------------
        'Purpose:  Disconnect from comm port
        '----------------------------------------------------------------------------------------------------------
        Private Sub cmdDisconnect_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdDisconnect.Click
            Try
                SerialPort1.Close()
                lblMessage.Text = SerialPort1.PortName & " disconnected"
                cmdDisconnect.Enabled = False
                cmdConnect.Enabled = True
            Catch ex As Exception

            End Try
        End Sub

        '----------------------------------------------------------------------------------------------------------
        'Purpose:  Send message to selected comm port
        '----------------------------------------------------------------------------------------------------------
        Private Sub cmdSend_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSend.Click
            Try
                SerialPort1.Write(txtTextToSend.Text & vbCrLf)
                With txtDataRecieved
                    .SelectionColor = Color.Black
                    .AppendText(txtTextToSend.Text & vbCrLf)
                    .ScrollToCaret()
                End With
                txtTextToSend.Text = String.Empty
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try
        End Sub

        '----------------------------------------------------------------------------------------------------------
        'Purpose:  Update text box with data recieved from comm port
        '----------------------------------------------------------------------------------------------------------
        Public Sub updateTextBox()
            With txtDataRecieved
                .Font = New Font("Garamond", 12.0!, FontStyle.Bold)
                .SelectionColor = Color.Red
                .AppendText(SerialPort1.ReadExisting)
                .ScrollToCaret()
            End With
        End Sub

        '----------------------------------------------------------------------------------------------------------
        'Purpose:  Receive data
        '----------------------------------------------------------------------------------------------------------
        Private Sub SerialPort1_DataReceived(ByVal sender As Object, ByVal e As System.IO.Ports.SerialDataReceivedEventArgs) Handles SerialPort1.DataReceived
            txtDataRecieved.Invoke(New myDelegate(AddressOf updateTextBox), New Object() {})  ', New Object()
        End Sub

    End Class


    the string of data which i get from com port are like this ' $HUB0, 30.9C, 3.5, 000, N#'

    and i don't want to store the data into one cell...but separate it into 5 cell per data...

    can u help me please..because i need to submit my task to lecturer next week..
    Saturday, September 13, 2008 10:48 PM
  • jimmyeatworld said:

    can u help me to store data from vb.net to access...right now i get the data from com port. and i want the data to be save in the access...below is the sample code...

            With txtDataRecieved
               .AppendText(SerialPort1.ReadExisting)


    the string of data which i
    get from com port are like this ' $HUB0, 30.9C, 3.5, 000, N#'
    and i don't want to store the data into one cell...but separate it into 5 cell per data...


    Hi jimmyeatworld,


    Because the data you get from COM port SerialPort1.ReadExisting is delimited with comma (e.g. "$HUB0, 30.9C, 3.5, 000, N#" ), you can split it into a String Array, then add a record to database and store into 5 fields.


    I presume you created a MS Access  database file D:\myDB.mdb containing a table named Table1. Table1 has 5 fields of "Text" data type.
    Imports System.Data.OleDb  
     
    Public Class Form1  
     
        Private Sub Button1_Click(ByVal sender As System.ObjectByVal e As System.EventArgs) Handles Button1.Click  
            'Since the recieved data is delimited with comma, you can split it into a String Array.  
            Dim dataRecieved As String = "$HUB0, 30.9C, 3.5, 000, N#" 
            Dim dataArray As String() = dataRecieved.Split(",")  
     
            ' Add a record to database and store into 5 fields.   
            Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=D:\myDB.mdb")  
            Dim cmd As OleDbCommand  
     
            cmd = New OleDbCommand("Insert Into Table(Field1, Field2, Field3, Field4,Field5) Values(@data1, @data2, @data3, @data4, @data5)", con)  
     
            With cmd.Parameters  
                .Add("@data1", OleDbType.VarChar).Value = dataArray(0)  
                .Add("@data2", OleDbType.VarChar).Value = dataArray(1)  
                .Add("@data3", OleDbType.VarChar).Value = dataArray(2)  
                .Add("@data4", OleDbType.VarChar).Value = dataArray(3)  
                .Add("@data5", OleDbType.VarChar).Value = dataArray(4)  
            End With 
     
            con.Open()  
            cmd.ExecuteNonQuery()  
     
        End Sub 
     
    End Class 


    Tutorial: How to add a record to database
    http://social.msdn.microsoft.com/forums/en/vbgeneral/thread/ae6b1491-19f7-40e6-bdcf-c6b8b8114181/


    Best regards,
    Martin Xie

    Monday, September 15, 2008 5:42 AM
  • Hi Martin,

    Cud u possibly help?

    1. I got 25 GPS devices sending their co-ordinates in text format as a SMS. A GSM modem receives these sms and diverts them to a MS Access datatable(C:\Web\sms.mdb).

    2. I have created a table inthe database to assign names to the mobile numbers of the GPS devices. A simple join and a query gives me the name, number and text message.

    3. Another query extracts the DISTINCT sms basedon time stamp, so I get the latest sms for each GPS device. (Name,Lat, Long). Using mapwin.ocx, I have created an application to display rster and vector maps.

    4. I have attached the database to the application(VB 2008) and have a datagrid on my form to display the query(DISTINCT).

    5. I need to :-

    (a) Display these points on the map. Plot by using the Lat and Long values

    (b) Use the Name s a Label on the map, next to the icon.

    (c) Requery every 5 seconds and have the points moving on the map.(The old point disappears, and the new points appear. So its like a real time GPS tracking application, based on SMS.

    I would be very grateful, if you cud help !!!!

    Thankx !!

    Tuesday, April 24, 2012 11:25 AM