[RESOLVED] Access Database to DataGridView

[malatesh kumar]

Hi,
How can i send a table values into DataGridView??
I am using Access Database.
And one more thing i want to know that...
Before sending values into Datagridview i need to work 2 or 3 tables and that result should be keep one Temp Table , that TempTable i want send to DataGridView.
Note:
Here 2 or 3 tables work means i want to applay leftjoin between two tables and coming result should be keep one temptable and again i want to right join with temptable to other table.....like.......

This Concept i recently worked on VB, now i want to do work on VB.NET.
Is there any way to handel this task??
Any i how i am sending Code which i implemented same concept in VB.

IN THE FORM CODE:

Public Sub cmdinnerjoinok_Click()

On Error Resume Next
rsICCandBdgYearFilter.Open "drop table tblICCAnnBdgYearFilterTemp", cn, adOpenDynamic, adLockOptimistic

rsICCandYrFilter.Open "drop table tblICCYearExpFilterTemp", cn, adOpenDynamic, adLockOptimistic

rsSum.Open "drop table tblSumTemp", cn, adOpenDynamic, adLockOptimistic

rsLJ.Open "drop table tblAnnBgtExpLJTemp", cn, adOpenDynamic, adLockOptimistic

rsRJ.Open "drop table tblAnnBgtExpRJTemp", cn, adOpenDynamic, adLockOptimistic

rsUNION.Open "drop table tbluniontemp", cn, adOpenDynamic, adLockOptimistic

On Error GoTo 0
'set ICC And BdgYear Filter
Set rsICCandBdgYearFilter = New ADODB.Recordset
rsICCandBdgYearFilter.Open fnICCandBdgYearFilter, cn, adOpenDynamic, adLockOptimistic

'SET ICC AND YEAR FILTER
Set rsICCandYrFilter = New ADODB.Recordset
rsICCandYrFilter.Open fnICCandYearFilter, cn, adOpenDynamic, adLockOptimistic

Set rsSum = New ADODB.Recordset
rsSum.Open fnQSUM, cn, adOpenDynamic, adLockOptimistic

Set rsLJ = New ADODB.Recordset
    rsLJ.Open fnQLJ, cn, adOpenDynamic, adLockOptimistic

Set rsRJ = New ADODB.Recordset
    rsRJ.Open fnQRJ, cn, adOpenDynamic, adLockOptimistic

Set rsUNION = New ADODB.Recordset
    rsUNION.Open fnQUNION, cn, adOpenDynamic, adLockOptimistic

IN THE MODULE:

Public Function fnICCandYearFilter()
Dim QStr4 As String
fnICCandYearFilter = ""
On Error Resume Next
For i = 1 To rcount
    If frmMultyTabs.Check4(i).Value = 1 Then
        fnICCandYearFilter = fnICCandYearFilter + " OR (tblCategoryExpenditures.ICC= """ & Left(frmMultyTabs.Check4(i).Caption, InStr(1, frmMultyTabs.Check4(i).Caption, "-") - 1) & """)"
        If frmMultyTabs.Controls("Option1" & i).Value = True Then
           fnICCandYearFilter = fnICCandYearFilter + " AND " & "(" & "TransactionDate between #" & ProjCurYrstart & "# AND #" & Date & "#" & ")"
    ElseIf frmMultyTabs.Controls("option2" & i).Value = True Then
           fnICCandYearFilter = fnICCandYearFilter + " AND " & "(" & "TransactionDate between #" & ProjPrevYrStart & "# AND #" & ProjPrevYrEnd & "#" & ")"
        End If
    End If
Next i
QStr4 = Mid(fnICCandYearFilter, 4)
fnICCandYearFilter = "Select *  into tblICCYearExpFilterTemp from tblCategoryExpenditures   where " & QStr4 & ""
Debug.Print fnICCandYearFilter
End Function

Public Function fnQSUM()
fnQSUM = "SELECT tblICCYearExpFilterTemp.CategoryNumber, tblICCYearExpFilterTemp.TransactionDate, tblICCYearExpFilterTemp.ICC, sum(tblICCYearExpFilterTemp.Expenditure) AS SumExp INTO tblSumTemp From tblICCYearExpFilterTemp  GROUP BY CategoryNumber, ICC,TransactionDate;"
End Function

Public Function fnQLJ()
fnQLJ = "SELECT tblICCAnnBdgYearFilterTemp.ICC, tblICCAnnBdgYearFilterTemp.CategoryNumber, tblICCAnnBdgYearFilterTemp.BudgetYear,tblICCAnnBdgYearFilterTemp.BudgetAmount, tblSumTemp.SumExp INTO tblAnnBgtExpLJTemp FROM tblICCAnnBdgYearFilterTemp LEFT JOIN tblSumTemp ON (tblICCAnnBdgYearFilterTemp.ICC = tblSumTemp.ICC) AND (tblICCAnnBdgYearFilterTemp.CategoryNumber = tblSumTemp.CategoryNumber);"
End Function

Public Function fnQRJ()
fnQRJ = "SELECT tblSumTemp.ICC, tblSumTemp.CategoryNumber, tblICCAnnBdgYearFilterTemp.BudgetYear,tblICCAnnBdgYearFilterTemp.BudgetAmount, tblSumTemp.SumExp INTO tblAnnBgtExpRJTemp FROM tblICCAnnBdgYearFilterTemp RIGHT JOIN tblSumTemp ON (tblICCAnnBdgYearFilterTemp.CategoryNumber = tblSumTemp.CategoryNumber) AND (tblICCAnnBdgYearFilterTemp.ICC = tblSumTemp.ICC);"
End Function

I hope you understand wht i want in VB.NET code....
Here in VB code i used Functions, i called Functions in the FORM and i declared the Functions in the Module. By using Funtions and Recordset i did job. But how can i handle in VB.NET?

THanks

[MarMan]

If I follow you correctly, this will work:

        Dim strSQL As String
        Dim cmd As OleDbCommand
        Dim DA As OleDbDataAdapter
        Dim DS As DataSet
        Dim Reader As OleDbDataReader = Nothing


        DA = New OleDbDataAdapter
        DS = New DataSet

        strSQL = "SELECT  *" & _
                " FROM tmpTable"
        cmd = New OleDbCommand(strSQL, DataAccess.ADOConnectionAccess)
        DA.SelectCommand = cmd

        DA.Fill(DS)

        dgvDisplay.DataSource = Nothing
        dgvDisplay.DataSource = DS.Tables(0)

[malatesh kumar]

Hi,
Sorry for given late replay..and wht u send the code is working..
But i want to work on Function mode also.
Example like as per the post#1,
The Recordset called "rsICCandYrFilter" .
In the rsICCandYrFilter i am calling Function called "fnICCandYearFilter()".
In this Function i am declaring few statements.
like this how can i do steps for above criteria???
As per ur code we are creating "strsql " here we are declaring only sql statements.
how can i declare the Function and access the Function??

Thanks Marman

[malatesh kumar]

Hi, i am facing error when i use Left() function in the Funtion Statement as below,

If mycheckboxes(i).checked = 1 Then
                'fnICCandBdgYearFilter = fnICCandBdgYearFilter + " OR (tblAnnualBudget.ICC= """ & Left(myCheckBoxes(i).Text, InStr(1, myCheckBoxes(i).Text, "-") - 1) & """)"
                fnICCandBdgYearFilter = fnICCandBdgYearFilter + " OR (tblAnnualBudget.ICC= """ & Left(mycheckboxes(i).text, InStr(1, mycheckboxes(i).Text, "-") - 1) & """)"
            End If

I declared mycheckboxes as below in my code:

Private myCheckBoxes As New List(Of CheckBox)

how can i handle left() in the situaton???
Thanks

[MarMan]

Check the value of InStr(1, mycheckboxes(i).Text, "-") in the debug window.

[malatesh kumar]

Hi,
As per u i applied same code..as below , but in the debug the output is coming differently...plz check once my code ....

Public Function fnICCandBdgYearFilter()
        Dim QStr3 As String
        fnICCandBdgYearFilter = ""

        For i = 0 To myCheckBoxes.Count - 1

            If myCheckBoxes(i).Checked = True Then
                Debug.Print(myCheckBoxes(i).Text)
                'fnICCandBdgYearFilter = fnICCandBdgYearFilter + " OR (tblAnnualBudget.ICC= """ & Left(myCheckBoxes(i).Text, InStr(1, myCheckBoxes(i).Text, "-") - 1) & """)"
                fnICCandBdgYearFilter = fnICCandBdgYearFilter + " OR (tblAnnualBudget.ICC= """ & InStr(1, myCheckBoxes(i).Text, "-")
            End If
            Debug.Print(fnICCandBdgYearFilter)
        Next i
        QStr3 = Mid(fnICCandBdgYearFilter, 4)
        fnICCandBdgYearFilter = "Select *  into tblICCAnnBdgYearFilterTemp from tblAnnualBudget   where  " & QStr3 & ""
        Debug.Print(fnICCandBdgYearFilter)

    End Function

And in the Debug :

M0157.01 - AMDROUT
OR (tblAnnualBudget.ICC= "10
Select * into tblICCAnnBdgYearFilterTemp from tblAnnualBudget where (tblAnnualBudget.ICC= "10
But i want to get the output like ,
Select * into tblICCAnnBdgYearFilterTemp from tblAnnualBudget where (tblAnnualBudget.ICC= "M0157.01" )

how can i change the code if i want to get this output???
Thanks

[malatesh kumar]

Actually i declared mycheckboxes.text property in the below:

 For X As Integer = 0 To myCheckBoxes.Count - 1
            myCheckBoxes(X).Text = ds.Tables("tblprojects").Rows(X).Item("ICC") & " - " & ds.Tables("tblprojects").Rows(X).Item("projectname")

        Next

when i added Left() function then the error shows like:
ERROR:

Error 1 'Public Property Left() As Integer' has no parameters and its return type cannot be indexed. C:\Documents and Settings\Malatesh\My Documents\Visual Studio 2008\Projects\Dynamic Controls\test1\Form1.vb 136 98 test1
i think u understand this post...
Thanks

[malatesh kumar]

Hi,
I have small doubt , in the post#6 i used one of the table called "tblAnnualBudget". This table is coming from db.But i didn`t mentioned or called anywhaere in the code. is it necessary before using this table we need to call some where???
Clearly wht i am doing is i used one of the table in my db is "tblProjects".OK
i am getting values one of the column from this table and these values are set to text of dynamic check boxes(mycheckboxes).OK.
The another table called "tblAnnualBudget" having same values in the one of the column called "Icc". ok
Now in the Function i am comparing text values(mycheckboxes) and table "tblAnnualbudget".ICC values.
I will give one example like
In the Mycheckbox text value is "M0157.01 - AMDROUT",
and in the "tblAnnualbudget.ICC have "M0157.01 ".
So i want to remove the part "- AMDROUT" then i will go successfully next statement.
I hope u understand...
Thanks

[MarMan]

Use Split to get what you want:

        Dim a As String()

        a = Split("M0157.01 - AMDROUT", "-")
        Msgbox(a(0))'Prints "M0157.01 "
        Msgbox(a(1))'Prints " AMDROUT""

[malatesh kumar]

Hi,
Thank u now the statement is working i applied like this ....below,

If myCheckBoxes(i).Checked = True Then
                Debug.Print(Me.myCheckBoxes(i).Text)
                'fnICCandBdgYearFilter = fnICCandBdgYearFilter + " OR (tblAnnualBudget.ICC= """ & Left(me.myCheckBoxes(i).Text, InStr(1, me.myCheckBoxes(i).Text, "-") - 1) & """)"
                Dim a As String()
                a = Split(Me.myCheckBoxes(i).Text, "-")

                fnICCandBdgYearFilter = fnICCandBdgYearFilter + " OR (tblAnnualBudget.icc= """ & (a(0)) & """)"
            End If
            Debug.Print(fnICCandBdgYearFilter)
        Next i
        QStr3 = Mid(fnICCandBdgYearFilter, 4)
        fnICCandBdgYearFilter = "Select *  into tblICCAnnBdgYearFilterTemp from tblAnnualBudget   where  " & QStr3 & ""        
Debug.Print(fnICCandBdgYearFilter)

The Red color code indicates,
Here i want to send the data into one temp table called "tblICCAnnBdgYearFilterTemp" .
Is this right procedure to send the data into temp table or any other way we create temp table???
B`se the above red color code is not creating temp table in my DB, after debug ......
Plz give suggesstions regarding to this
Thanks

[MarMan]

What is fnICCandBdgYearFilter ?

In ADO.NET you must call ExecuteNonQuery, I don't know what the equivalent is in ADO, but it doesn't seem like you are calling anything that can run a query. Look into running an action query with ADO and apply that method. Your SQL looks OK.

[malatesh kumar]

Hi,
Now i am very sufficient to send data from access db to Datagrid..
as i mentioned my vb code in the post#1, this i convertedd into vb.net as below....i am sending some part which i developed.

Private Sub btnOK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOK.Click
        conn.Open(cn1, "", "", -1)
 rsICCandBdgYearFilter.Open("drop table tblICCAnnBdgYearFilterTemp", conn)
        rsICCandYrFilter.Open("drop table tblICCYearExpFilterTemp", conn)
        rsSum.Open("drop table tblSumTemp", conn)
 rsICCandBdgYearFilter = New ADODB.Recordset
        rsICCandBdgYearFilter.Open(fnICCandBdgYearFilter, conn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockBatchOptimistic, 1)

        rsICCandYrFilter = New ADODB.Recordset
        rsICCandYrFilter.Open(fnICCandYearFilter, conn)

        rsSum = New ADODB.Recordset
        rsSum.Open(fnQSUM, conn)
Public Function fnICCandBdgYearFilter()
        Dim QStr3 As String

        'Dim b As String
        fnICCandBdgYearFilter = ""

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

            If myCheckBoxes(i).Checked = True Then

                'fnICCandBdgYearFilter = fnICCandBdgYearFilter + " OR (tblAnnualBudget.ICC= """ & Left(me.myCheckBoxes(i).Text, InStr(1, me.myCheckBoxes(i).Text, "-") - 1) & """)"

                a = Split(Me.myCheckBoxes(i).Text, "-")
                a(0) = Replace(a(0), " ", "")

                fnICCandBdgYearFilter = fnICCandBdgYearFilter + " OR (tblAnnualBudget.icc= """ & (a(0)) & """)"
            End If
            Debug.Print(fnICCandBdgYearFilter)
        Next i
        QStr3 = Mid(fnICCandBdgYearFilter, 4)
        fnICCandBdgYearFilter = "Select *  into tblICCAnnBdgYearFilterTemp from tblAnnualBudget   where  " & QStr3 & ""
        Debug.Print(fnICCandBdgYearFilter)

    End Function

    Public Function fnICCandYearFilter()
        Dim QStr4 As String
        fnICCandYearFilter = ""
        For i = 0 To ds.Tables("tblProjects").Rows.Count - 1
            If myCheckBoxes(i).Checked = True Then
                a = Split(Me.myCheckBoxes(i).Text, "-")
                a(0) = Replace(a(0), " ", "")
                fnICCandYearFilter = fnICCandYearFilter + "OR (tblCategoryExpenditures.ICC= """ & (a(0)) & """)"
            End If
        Next i
        QStr4 = Mid(fnICCandYearFilter, 4)
        fnICCandYearFilter = "Select *  into tblICCYearExpFilterTemp from tblCategoryExpenditures   where " & QStr4 & ""
        Debug.Print(fnICCandYearFilter)
    End Function

    Public Function fnQSUM()
        fnQSUM = "SELECT tblICCYearExpFilterTemp.CategoryNumber, tblICCYearExpFilterTemp.TransactionDate, tblICCYearExpFilterTemp.ICC, sum(tblICCYearExpFilterTemp.Expenditure) AS SumExp INTO tblSumTemp From tblICCYearExpFilterTemp  GROUP BY CategoryNumber, ICC,TransactionDate;"
    End Function
End Class

Thanks Marman
Now i able to create temp table and i am able to apply joins between temptables and finally i am able to see that result into datagrid.
Thanks

[MarMan]

Glad to help!