|
Hi
I have an access database with two tables.
First table is called custDetails with fields:
CustID and CustName
Second table is called UsageHistory with fields:
CustID, UsagePeriod, DlyAmount, MnthlyAmount, Total
How do i show the whole of UsageHistory table and just the custName field from CustDetails table on a datagrid using a dataview?
I know how to create a connection to the database and execute the SQL statement at the start, and how to bind the datagrid to the dataview at the end. It just seems to be the middle bits that I'm unsure of that's stuffing me up  ie - datasets, dataadapters and datatables. I can get my form to display the table usageHistory and all its fields on the datagrid. But when i try to add in the field custName to the datagrid that's when i have problems.
Anyhelp or any new block of code would be greatly appreciated.
Dim ds As DataSet = New DataSet()
Dim dv As New DataView()
Dim da As OleDbDataAdapter
Dim strConnection As String = _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\ClientInfo.mdb; Persist
Security Info=False"
Dim cn As OleDbConnection = New OleDbConnection(strConnection)
'Open a database connection.
cn.Open()
Dim strSql As String
strSql = "SELECT UsageHistory.*, CustDetails.CustName " & _
"FROM CustDetails INNER JOIN UsageHistory ON CustDetails.CustID " & _
"= UsageHistory.CustID;"
da = New OleDbDataAdapter(strSql, cn)
************This is the part that i'm unsure of*****
da.TableMappings.Add(?????)
' Fill the DataSet.
ds = New DataSet()
da.Fill(ds)
Dim dt As DataTable = ds.Tables.Add(???????)
*****************************************************
cn.Close()
'Make a DataView for the data.
dv = New DataView(dt)
'Bind the DataGrid control to the DataView.
dgUsage.DataSource = dv
|
|
|
|
|
I think you're trying to mix the way the data is displayed with what you are retrieving from the database. All you really need to do is set up the columns properly in your data grid and then simply bind the DataTable to the DataGrid as its data source. You don't need a DataView in this case.
On your DataGrid in the aspx page, set the AutoGenerateColumns property to false and then specify the columns you want to display in the Columns property. Only supply the columns that you want. So let's say that these are the columns your join returned:
CustID, CustName, UsagePeriod, DlyAmount, MnthlyAmount, Total
,but all you want to show are these:
CustName, UsagePeriod, DlyAmount, MnthlyAmount, Total
Just specify these columns in the Columns property in the properties panel (there's a button that opens a dialog where you add the columns).
Then your code would just look like this (this is C# code, but I think you get the idea):
DataSet dataSet = new DataSet();
dataAdapter.Fill( dataSet,"TableName");
DataTable dataTable = dataSet.Tables[0];
dgUsage.DataSource = dataTable;
dgUsage.DataBind();
In this case the entire data set that you got from the database is still avialable. You are just diplaying the pertinent columns as specified in your Columns property.
You may have heard of separating your presentation from your data before, well this is an example of how to do so. Your DataSet, DataTable, etc. should never be aware or care about how you are displaying something. Just tell your DataGrid what to do with the data it has been handed by specifying the columns to use.
A DataView is more for filtering records (rows), not columns. If I understand you correctly, you want to display all but one of the columns from the join. Is that correct?
Hope this makes sense. Let me know if you need clarification. If I've missed something, let me know.
-Matt
------------------------------------------
The 3 great virtues of a programmer:
Laziness, Impatience, and Hubris.
--Larry Wall
|
|
|
|
|
Hi Matt
Thank you very much for your response. I am new to programming (three months) so any help i can get is always appreciated. I am building a windows application and could not find the autogeneratecolumns or the columns property in the properies panel which you mentioned. I was however able to implement your code and my datagrid is now showing the information that i wanted which is a big improvement 
Regarding the dataview, the reason why i wanted this is so that i can allow users to filter the data either by Customer Name or usage period. I will try to get the dataview working, failing that, its just a case of generating a new Sql statement to give me the filtered information that i want.
I also noticed that the amounts and totals are displaying with more than two decimal points and showing some null field in the datagrid. I would like to do some cosmetic touch ups so that the amounts only show two decimal places. Do you know if the formatting should be done on the datagrid level or is it the datatable where the field formatting is done?
Thanks once again for your earlier response.
|
|
|
|
|
I didn't realize you were building a windows application. I thought you were building an ASP .NET app, so I can see how some of the information I provided could be confusing. I really haven't done a whole lot of windows app .NET programming yet. Sorry for misleading you.
As far as data formatting goes, I would do it in either the SQL statement or at the DataGrid. It just depends on what you are doing with the data. If all you want is to get your decimal places right, I would just do it at the datagrid, however, this requires that you use a Data Formatting Expression in the grid. This is something that gets applied to each item in a particular column. Just do a search for that phrase on google or google groups. These get set up in the columns dialog on the columns parameter (as least for ASP .NET programming).
Good luck and I'm glad it was at least a little bit helpful.
-Matt
p.s. It looks like you're doing *very* well for having only been programming for three months. I know you're not using C#, but I would recommend that you check out the "Programming C#" book from oreilly. It is very good and worthwhile. I read it pretty much cover to cover. It's the first technical book I've ever done that with.  Most of the code you can port to VB .NET with some minor changes. I prefer the syntax of C# myself, but to each his own.
------------------------------------------
The 3 great virtues of a programmer:
Laziness, Impatience, and Hubris.
--Larry Wall
|
|
|
|
|
Hey no problems. Beleive it or not you helped me with the most crucial part which was showing the joined tables in a datagrid. I spent all day the other day trying all these complex methods retrieved from my google searches when you were right all along in saying that i was trying to mix the way the data is displayed from what was retrieved.
Your 5 lines of code has saved me from what would have surely been a day of frustration and hair tearing. 
With the 5 lines of code I was able to make good progress on the GUI today with the datagrid. Tommorrow i will get on to formatting the fields. There are some time fields as well which i didn't mentioned that have appeared in an erroneous date format and i'll need to reformat them back to display time.
And thanks for the suggestion on the book Its been a steep learning curve the last three months and i'm always on the look out on how i can learn the basics and the foundation knowledge. I have the book "How to program in VB.NET" by Dietel and Dietel. I tried to cram the whole book from cover to cover but could only get to chapter 8. Its a pretty huge and detailed book and really tough going as you get further into the book. But it was enough to give me my jump start.
Once again thank you for taking the time to respond to my question which is much appreciated. 
|
|
|
|
|
I have a project in which I am having to reverse engineer a product that uses an Access database. The application inserts records with an ID such as A000001. Each time a new record is added, this is incremented by one according to some alogorithm. Here's how it gets incremented:
A000001...A000009
A00000A...A00000Z
A00000a...A00000z
First of all, this scheme is wacked out. I've never seen such a thing before (Obfuscation maybe?). As far as I can tell, they have made it so that this identity column allows duplicates. This would have to be the case since you get both A00000A and A00000a as separate records. Text fields in Access don't account for case-sensitivity. So my question is, when I have to do a join on this column with a column in another table, how can I do it so that the strings are compared using case? Is this possible? When you have the clause 'Table1 INNER JOIN Table2 ON Table1.ItemID = Table2.ItemID' in a select statement, Access sees it as equal even when the strings are A00000A and A00000a respectively. Any ideas?
Thanks for your help.
-Matt
------------------------------------------
The 3 great virtues of a programmer:
Laziness, Impatience, and Hubris.
--Larry Wall
|
|
|
|
|
|
Hi, all:
I am using Datagrid control in my WinForm application. I have two questions regarding the usage of the datagrid control.
1. In MS Access Database, you can specify a column "Lookup" to another table for available values ( show up as List when clicked on the cell ). Can I do the same thing in Datagrid control?
2. I'd like the user to update the data in the datagrid, but not add a new row. I will programatically add new rows through Dataset. Is this possible?
Thanks for your help in advance.
Dion
|
|
|
|
|
1. In MS Access Database, you can specify a column "Lookup" to another table for available values ( show up as List when clicked on the cell ). Can I do the same thing in Datagrid control?
1. You can do this, but it's not automatic. You have to use a template column with a dropdownlist that has been populated with the lookup values. See answer to number 2 below. The O'Reilly book I mention shows how to do this as well.
2. I'd like the user to update the data in the datagrid, but not add a new row. I will programatically add new rows through Dataset. Is this possible?
2. This is possible. There's a good tutorial on how to do in-place editing in the "Programming ASP .NET" book from O'Reilly & Associates. You can get the source code from here. Once you've downloaded the source zip file, look at the filss ex-13-06, ex-13-07, and ex-13-08. You'll have to change the names and such to see it work, but the code is there and is complete. I've tested it myself. Meanwhile, you may want to just go pick up the Programming ASP .NET book. It'll be money well spent.
-Matt
------------------------------------------
The 3 great virtues of a programmer:
Laziness, Impatience, and Hubris.
--Larry Wall
|
|
|
|
|
Hi,
In our office we have a network of 3 computers. We have a db issue where we nee to access a single db on one of the 3 computers. Which would be the best solution:
1) Implementing SQL Server on the PC with the db
2) Writing an app to cache the db data and hand it over to clients that requests it (reads and writes)
3) Connecting to the db directly through the network connection
For interest the db we are using in Access.
Any information would be greatly apprecated.
Regards,
Rich
"Programming today is a race between software engineers striving to build bigger and better idiot-proff programs, and the Universe trying to produce bigger and better idiots. So far the Universe is winning." -- Rich Cook
|
|
|
|
|
Connecting to the db directly through the network connection
i think this is the best one
3 clients don't need SQL server , and you can directly access the MS Access db from the clients.
|
|
|
|
|
Hiya am getting a syntax error on using CREATE TABLE in SQL and an Access database:
// open the database
database.Open( NULL,false,false,sDsn );
database.ExecuteSQL("CREATE TABLE OFFICES (OfficeID TEXT(4) OfficeName TEXT(10) ) ");
Does anyone know what is wrong with the ExecuteSQL line..
Thanks
grahamoj.
|
|
|
|
|
Hiya again, I found out my problem. Missing comma i.e
database.ExecuteSQL("CREATE TABLE OFFICES (OfficeID TEXT(4),OfficeName TEXT(10) ) ");
So now what I need to be able to do is make the text field 600 chars. So tried these:
database.ExecuteSQL("CREATE TABLE OFFICES (OfficeID TEXT(4) OfficeName TEXT(600) ) ");
and
database.ExecuteSQL("CREATE TABLE OFFICES (OfficeID TEXT(4) OfficeName MEMO(600) ) ");
but it won't create the table because text fields are too long..
any ideas on how to do this??
grahamoj.
|
|
|
|
|
For SQL-Server the maximum length for TEXT is 2,147,483,647 (and even VARCHAR can store up to 8000 characters), so I don't think 600 should be the problem. Maybe it's just the missing comma again?
--edit--
BTW: For 600 characters you shouldn't use TEXT but VARCHAR because of better performance. TEXT is designed for storing large textfiles, so in the table there is only stored a pointer to the actual data.
--edit--
--
karl
|
|
|
|
|
grahamoj wrote:
database.ExecuteSQL("CREATE TABLE OFFICES (OfficeID TEXT(4) OfficeName MEMO(600) ) ");
Don't specify a length for a memo, i.e. use:
database.ExecuteSQL("CREATE TABLE OFFICES (OfficeID TEXT(4), OfficeName MEMO ) ");
Dave.
|
|
|
|
|
|
I have a SELECT statement written for Oracle which I need to convert to work on Access, SQL, and Oracle. Could some kind soul please let me know what the SQLCommand would be to convert from:
SELECT Description,<br />
Decode(KQues, 'Y', NULL, To_Char(Add_Months(Warning_Deadline, 12*(Period_ID - 7)),'dd/mm/yyyy')),<br />
Decode(KQues, 'Y', NULL, To_Char(Add_Months(Statutory_Deadline, 12* (Period_ID - 7)),'dd/mm/yyyy')),<br />
Claim_Made,<br />
To_Char(Date_Claim_Made,'dd/mm/yyyy'),<br />
Signed_Off_By,<br />
KQues,<br />
Parent_ID<br />
FROM<br />
Client_Claims,<br />
Standard_Claims<br />
WHERE<br />
Client_ID = n AND<br />
Standard_Claims.Claim_ID = Client_Claims.Claim_ID
You will be doing me a big favour in converting this.
|
|
|
|
|
this should work for MSSQL
SELECT Description,
Case KQues
When 'Y' Then Null
Else Convert(Varchar(12),AddDate(month,12*(Period_ID - 7),Warning_Deadline),113)
End,
Case KQues
When 'Y' Then Null
Else Convert(Varchar(12),AddDate(month,12*(Period_ID - 7),Statutory_Deadline),113)
End,
Claim_Made,
Convert(Varchar(12),Date_Claim_Made,113)
Signed_Off_By,
KQues,
Parent_ID
FROM Client_Claims,
Standard_Claims
WHERE Client_ID = n
AND Standard_Claims.Claim_ID = Client_Claims.Claim_ID
|
|
|
|
|
I will give it a go. Cheers 
|
|
|
|
|
Hiya I am writing a program with SQL and an Access Database. I need to set the lenght of the text field (a column) to 600. Can I do this from my program??
I have tried setting it in the database in the design view but it will only allow a maximum of 255 chars. But it would be better to be able to set it from the program.
I am using CDatabase and CRecordset and its an ODBC database..
Thanks
grahamoj.
|
|
|
|
|
In access, create the field as a memo with no length e.g.
create table blah<br />
(<br />
field1 memo<br />
)
Dave.
|
|
|
|
|
Does anyone know of a product that will create XML bassed documentation for a SQL database like you can get with C# or by using NDoc?
Paul Watson wrote:
"At the end of the day it is what you produce that counts, not how many doctorates you have on the wall."
George Carlin wrote:
"Don't sweat the petty things, and don't pet the sweaty things."
|
|
|
|
|
I have problem with MS SQL Server during installation
firstly there was no problem with Standard Edition, then I uninstalled that and tried to install Enterprise Edition
and faced the following problem:
at the end of the installation the following message appears "... refer to sqlstd.log file"
now I have the same problem with Standard Edition
(I tried to install SQL Server on another computer, the same problem)
thanx.
|
|
|
|
|
ok .. can you tell us what is written in the log file..
it may explain the problem..
also what OS do you use ?
|
|
|
|
|
Hi, All:
I am writing my FIRST Windows Form Application in .NET to edit my MS Access database. In this Access DB, I have a table with two columns: EquipmentID and EquipmentDescription. Some other tables use the ID to refer the equipment. This application supports multiple users at the same time. That mean that it could be more than one user add new equipmment. I use in-memory DataSet to get user's input and then call Insert/Delete/Update methods. I am debating that whether I should use Access AutoNumber for the ID field, or I should just use DataReader to get the Max ID used, then Add 1 to create my new ID. The AutoNumber makes things simpler, but if I try to merge to DB later, I will have a lot of headaches. If I assign my own ID, I might have conflict with other users and fail the INSERT.
So what is the typical way to achieve this, I know a lot of people have done this, please give me some ideas. Thanks in advance.
Dion
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
