[RESOLVED] Access Database to DataGridView
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:
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:
Code:
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
Re: Access Database to DataGridView
If I follow you correctly, this will work:
Code:
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)
Re: Access Database to DataGridView
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
Re: Access Database to DataGridView
Hi, i am facing error when i use Left() function in the Funtion Statement as below,
Code:
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:
Code:
Private myCheckBoxes As New List(Of CheckBox)
how can i handle left() in the situaton???
Thanks
Re: Access Database to DataGridView
Check the value of InStr(1, mycheckboxes(i).Text, "-") in the debug window.
Re: Access Database to DataGridView
Hi,
As per u i applied same code..as below , but in the debug the output is coming differently...plz check once my code ....
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
Re: Access Database to DataGridView
Actually i declared mycheckboxes.text property in the below:
Code:
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
Re: Access Database to DataGridView
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
Re: Access Database to DataGridView
Use Split to get what you want:
Code:
Dim a As String()
a = Split("M0157.01 - AMDROUT", "-")
Msgbox(a(0))'Prints "M0157.01 "
Msgbox(a(1))'Prints " AMDROUT""
Re: Access Database to DataGridView
Hi,
Thank u now the statement is working i applied like this ....below,
Code:
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
Re: Access Database to DataGridView
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.
Re: Access Database to DataGridView
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.
Code:
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
Re: [RESOLVED] Access Database to DataGridView
