Running access query in vb.net

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")

Answer 1

 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()

Answer 2

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, you 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.Object, ByVal 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.Object, ByVal 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

Answer 3

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..

Answer 4

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.Object, ByVal 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

Answer 5

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 !!