How to append fields values and fill Datagrid? [RESOLVED]

[Zealot]

I've been requested to show in a VB.net Datagrid both "month" and "year" fields in the DB as only "Date" in the Datagrid.
Example:
Now in the DataBase:
Fields:|Month|Year|
Row: 12 |2002
Row: 11 |2001

Should be in Datagrid:
Fields: |Date |
Row: 12/2002
Row: 11/2001

etc.

Here's my big problem:
I also need to put in the datagrid more fields from different tables, wich means that I can't use the DataGridTableStyle and use the MappingName property, since it would only accept from one table (I may be wrong, of course ). So after I append the month and year into one field, I'm going to add the field "Account" from table "Accounts", wich is different than the "Month" and "Year" fields, since they are from the table "Maps".

I also thought about making the append process in an SQL Query, but my SQL knowledge is quite limited. But I'd like to know your oppinion, and if this is the best process I'd like to know how to call it into my Datagrid.

Thank you for every help you can give me. I really need it.

[Cander]

You can combine 2 fields in your SQL query. When you specify the fields:

Select monthfield + '/' + yearfield from table .................

[Zealot]

Hey thanks for your help, it almost worked, I had to use the ampersand instead of the plus.

SELECT [Maps].[month] & '/' & [Maps].[year] AS [Map date], ccAccounts.account
yadda yadda yadda

Now, how should I bind the query into the Datagrid?

[MrGTI]

Somehow i doubt yadda yadda yadda is proper SQL, even in Access.

[Cander]

Originally posted by MrGTI
Somehow i doubt yadda yadda yadda is proper SQL, even in Access.

sure it is!

You should look up some examples on openeing a query into a dataset then bind it to the Grid. I donmt have any examples on me currently. They are all on my old computer which I dont have access to currently.

[Zealot]

Ok. So... how long do I have to wait? 10 minutes?

I had done some searches before opening this thread, it's just that the words "query" and "bind" give a lot more unwanted results than one would like.

[Zealot]

BTW, in case I want to make a Dataset, wich reference must I add to my project in order to call an OleDbConnection and an OleDbDataAdapter? It seems that adding the reference "MS ActiveX Data Objects 2.7Library" a la VB 6 doesn't do the cut anymore!

[Cander]

i think it is the system.data.dll.. Hold on , Iwill find you some examples

[Cander]

Ok got one of my examples

' Codebehind for the catagories.ascx pagelet.

Imports System
Imports System.Data
Imports System.Data.OleDb
Imports System.Web.UI
Imports System.Web.UI.WebControls

Public Class Catagories
	' Declare the class and have it inherit the System.Web.UI.UserControl class. This is needed for 
	' accessing the ASP.NET control's Events/Properties/Methods
	Inherits UserControl
	
	Public rptCatagory As Repeater
	Dim myDS As New DataSet
	Dim myConn As OleDbConnection
	Dim myCommand As OleDbDataAdapter
	Dim strConnString As String = "Provider=Microsoft.Jet.OLEDB.4.0;data source=" & System.Web.HttpContext.Current.Server.MapPath("test.mdb")
	Dim strSQL As String
	
	Private Sub Page_Load(ByVal sender As Object, e As EventArgs)
		' Get Catagory listing from db
		strSQL = "SELECT c.ID, c.Catagory, c.PostCount, c.LastPostDate, c.Description, u.Username, u.ID AS UserID FROM Catagories AS c INNER JOIN Users AS u ON c.LastPostBy = u.ID"


		' Populate the Dataset from the SQL statement
		' Start Connection to ADO source
		myConn = New OleDbConnection (strConnString)
		' Run the Sql statement and get the Data into the DataAdapter
		myCommand = New OleDbDataAdapter(strSQL, myConn)
		' Fill the DataSet and name the table
		myCommand.Fill(myDS, "Catagory")
		myConn.Close()
		
		' Now set the DataGrid's source to the Dataset and bind it
		rptCatagory.DataSource = myDS.Tables("Catagory").DefaultView
		rptCatagory.DataBind()
	End Sub
End Class

[Zealot]

Hum, is it me or those Imports are only usefull for ASP.Net and not VB.Net?
Anyway, I still don't know wich references I need to add to use the OleDbDataAdapter.

[Cander]

imports are useful for all .NET tools

Right click on your project in Solution explorer > Add Reference

System.Data.Dll

[Zealot]

Are you sure I can use the DataBind () method?

[Cander]

sorry DataBind is or an ASP.NET datagrid. If it is the VB datagrid, rempove that line.

[Zealot]

Ok the outcome wasn't anything I expected. The user has to click on the plus sign in the grid, then click on a link, wich only then opens the grid, but I have an ugly bar above it saying "NewDataSet:". How did this happen? Was it something in filling the dataset?

Damn .Net format...

[Zealot]

Here's what I have:

[Zealot]

Here's my code:

Dim objConn As OleDbConnection
            Dim objComm As OleDbDataAdapter
            Dim strConn As String
            Dim strComm As String
            Dim objDataSet As New DataSet
 
            'Build the connection string
            strConn = "Provider=Microsoft.Jet.OLEDB.4.0;"
            strConn += "Data Source=C:\GMD\gmd.mdb;"
            strConn += "Persist Security Info=False"
 
            'Build the SQL string
            strComm = "SELECT ccColaboradores.colaborador, [Mapas].[mes] & '/' & [Mapas].[ano] AS [Data do mapa], ccContas.conta"
            strComm += " FROM ccContas, ccColaboradores INNER JOIN Mapas ON (ccColaboradores.colaborador = Mapas.colaborador) AND (ccColaboradores.mapa = Mapas.ID);"
 
 
            'Create the connection and command objects
            objConn = New OleDbConnection(strConn)
            objComm = New OleDbDataAdapter(strComm, objConn)
 
            'Fill the dataset with the results of the query
            objComm.Fill(objDataSet, "Mapas")

And here's the outcome when I click on the link in the upper picture:
See that bar saying NewDataSet? and the buttons in the top right of the Datagrid? I want none of that.

[Zealot]

[echos]Helooooooo-oooo-oooo[/echos]

[Zealot]

Geez no one can help me?

[Serge]

While binding the DataSet to the DataGrid, specify the table object rather then the dataSet itself.

YourDG.DataSource = YourDataSet.Tables(0)

The reason why you get the table name in the grid first is because DataGrid could handle hierarchical data.

Regards

Serge

[Zealot]

Serge thanks a lot that really does the trick!