CODEWITHSUNDEEP
sqlvb.netms-access
user3476579
user3476579

Reputation: 13

Extract data from Query

Can you please teach me on how to extract data from SQL query access database and place it into a Variable holder? I've been struggling for a week already and i've been really trying hard, please advise Thanks

This is what i have done so far :

Imports System.Data.OleDb
Public Class ModifyForm
    Dim connstring As String = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source= c:\Databse\Company_db.accdb"
    Dim conn As New OleDbConnection
    Private Sub MainMenuButton_Click(sender As Object, e As EventArgs) Handles MainMenuButton.Click
        Me.Close()
        Form1.Show()

    End Sub

    Private Sub CloseButton_Click(sender As Object, e As EventArgs) Handles CloseButton.Click
        Me.Close()
        Form1.Close()

    End Sub

    Private Sub DeleteBut_Click(sender As Object, e As EventArgs) Handles DeleteBut.Click

    End Sub

    Private Sub ModifyForm_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the "Company_dbDataSet1.tbl_empinfo" table. You can move, or remove it, as needed.'
        Me.Tbl_empinfoTableAdapter.Fill(Me.Company_dbDataSet1.tbl_empinfo)

    End Sub
    Private Sub eNumText_SelectedIndexChanged(sender As Object, e As EventArgs) Handles eNumText.SelectedIndexChanged

        Dim empNum As String
        Dim empFname As String
        Dim empLname As String
        Dim empDept As String
        Dim empStat As String
        Dim empYears As String

        empNum = eNumText.Text
        empFname = empFnameText.Text
        empLname = empLnameText.Text
        empDept = DeptText.Text
        empStat = StatText.Text
        empYears = yearstext.Text

        MsgBox(empNum)
        Dim conn As New OleDbConnection
        conn.Open()


        Dim SqlQuerry As String = "SELECT * FROM tbl_empinfo WHERE EmpID like empNum"
        Dim SqlCommand As New OleDbCommand
        Dim SqlAdapter As New OleDbDataAdapter
        Dim Table As New DataTable

        With SqlCommand
            .CommandText = SqlQuerry
            .Connection = conn

        End With

        With SqlAdapter
            .SelectCommand = SqlCommand
            .Fill(Table)


        End With

        DataGridView1.Rows.Clear()
        For i = 0 To Table.Rows.Count - 1
            With DataGridView1
                .Rows.Add(Table.Rows(i)("EmpID"), (Table.Rows(i)("FirstName")))
            End With

        Next

        'Dim dbSource = "Data Source= C:\Databse\Company_db.accdb"'


        conn.Close()

    End Sub

Upvotes: 1

Views: 228

Answers (1)

user3175748
user3175748

Reputation:

Forgetting about the extra code, as was mentioned already, all you need to do is declare your variables:

Dim DBID As Integer  ' Or whatever type you're using for the ID field
Dim DBFirstName As String

Then inside the For/Next loop, just use:

DBID = Table.Rows(i)("EmpID")
DBFirstName = Table.Rows(i)("FirstName")

That is only if you're going to use the variables within the for/next loop, otherwise you'll be overwriting them for every record. If you need to store all the values, you'll have to use arrays instead.

Upvotes: 1

Related Questions