Design suggestions please?

[SteveInBeloit]

Hi,
I would like some suggestions on how to design this project.

I have loaded two data tables from Excel spreadsheets into one DataSet. DataTable1 contains debit card numbers. DataTable2 contains the transactions, meaning the debit card numbers along with amount and date.

I need to pull out all of the transactions from DT2 where the debit card number is in DT1. Then I need to display that information, and allow for a report, both detail (all transactions) and summary (amounts summed by debit card number).

What should I do?
My thoughts: Somehow compare the two DTs and create third DT on the fly that has all the transactions from DT2 where the number was in DT1. Then base a dataGridView on DT3 for the user to view. Then create a report based on DT3.
Sounds pretty straight forward. Anything I should do differently?

Or, how about compare the two, and delete any row out of DT2 where the debit card number is not in DT1, then sort them by debit card number and date, and then just use what is left of DT2.

I am mainly asking these questions because I have never compared dataTables or deleted out of them, and don't want to design something that is not doable. Also, I have based reports off of tables in the datasource tab, but not off of DataTables.

Thanks,
Steve

 

[zekeman]

The whole idea of using database tables is that unlike flat files, its much
easier to select everything for a particular key from tables. You can also use
'Joins' inner and outer to connect two tables that share a key.

Good Luck

 

[HeavenCore]

ok, writing this of top of my head so may be buggy but perhaps a table called Transactions with

id
date
amount
user_fk

then a carduser table

id
firstname
lastname
cardnumber
etc

Then you could do selects like

Select carduser.firstname, Transactions.date, Transactions.amount FROM carduser INNER JOIN Transactions ON Transactions.user_fk = carduser.id

then add WHERE date = @date etc for your needs

then bind a datagrid to this datascource, although i know vb.net's databinding can strugle generating interts for join-based adapters.

 

[Arg81]

surely your card number is your unique ID in your card table? (DataTable 1)

If so, then go to your dataset designer, and create a datarelation from DataTable 1 to DataTable 2 on the card no.

Now you got this, you can browse your "parent" rows (cards) and in a grid view the child rows (transactions).

Bind the grid to the relationship in your datasources explorer (click the + on DataTable1, you'll see the relationship table within here, just drag it onto the grid on the form).

 

[SteveInBeloit]

So looks like most lean towards using a DB instead of just loading the DataTables from excel. I will put an ACCESS mdb behind it so I can create a couple of tables.

Do I read the excel sheet and put it right into the tables, or do I fill a DataTable from Excel and then insert to the tables?

Thanks

 

[Arg81]

are you programmatically creating the dataTables or have you set them up "Typed" - i.e you created them in the Designer?

If it's the latter, you can create the relationship easily, if it's the former you can still do the relationship in code, albeit it's slightly more difficult but you can create the relationship in code and bind the grid to that relationship in the code.

 

[SteveInBeloit]

Creating the programatically. All I have is two excel spreadsheets. I loaded them into tables. I was trying to determine if I should go ahead and just move them to a DB and then process from there, or leave them in the DataTables and try to process from there. Since then, I have tried to see how to create reports based on the DataTables. Seems a little tough. I think I want the data in a DB, which I can easily process and report from there.

Now I am trying to determine how to get the data from Excel to the Access mdb tables. I can read them into DataTables (programmatically), but don't know if that is the best method, can I read/write them from Excel directly into the Access mdb?

Thanks

 

[HeavenCore]

access can import spreadsheets and csv files quite well,

Make sure the Microsoft Excel data is in list (list: A series of rows that contains related data or a series of rows that you designate to function as a datasheet by using the Create List command.) format: each column has a label in the first row and contains similar facts, and there are no blank rows or columns within the list.
Close the Excel workbook that contains the data you want to use in Microsoft Access.
In Access, open the database where you want to copy the Excel data.
On the Access File menu, point to Get External Data, and then click Import.
In the Import dialog box, click Microsoft Excel in the Files of type box.
In the Look in list, locate the file you want to import, and then double-click the file.
Follow the directions in the Import Spreadsheet Wizard.

 

[SteveInBeloit]

HI,
I need to do it from VB .NET, not Access. I need to have the VB app find the Excel spreadsheet, and then import it to an ACCESS mdb table that is in the DataSource tab of my project.
Steve

 

[cjard]

Just use the Jet driver to connect to the Excel file.. Not much point in using Excel to read excel data into a datatable then using jet to upload the data into Access then using Jet to read the data.. If it can get it on its own in the first place

http://www.connectionstrings.com/?carrier=excel

 

[SteveInBeloit]

cJard,
Thanks. I will try that next. I have already loaded the DataTable, and for practice, wanted to try to insert that data to a MSACCESS table.

I have been running this code, it executes, but no data is in the table.

Dim myConnection As New OleDbConnection(My.Settings.ExcelTablesConnectionString)
Dim cmd As New OleDbCommand( _
"Insert INTO tblTransactions(CardNum, Name) Values (?,?)", myConnection)
cmd.Parameters.Add("pCardNo", OleDbType.BigInt)
cmd.Parameters.Add("pName", OleDbType.Char)
myConnection.Open()

Dim i As Integer
For i = 1 To dsXLData.Tables("Source").Rows.Count - 1
cmd.Parameters("pCardNo").Value = 1234567
cmd.Parameters("pName").Value = "Steve"
cmd.ExecuteNonQuery()
Next
myConnection.Dispose()

I have been reading your DNU link seeing if I can find anything. From that I set "Copy if Newer"

Other problem is I don't quite know how to pull the exact column out of the DT for each row in my loop.

Steve

 

[cjard]

Using a .NET app as a middle man is simplest if you have two datasets that look identical but one is targeted at the Excel file, the other is targeted at the MDB. You .Fill() data into one and then .Merge() it into the other, then .Update() the other

 

[SteveInBeloit]

Well, mine will not be identical at all. Just thought I could make the above code work with a PQ and Insert to the table since I have already started it. I have it working.

I chose to connect to the mdb in it's original location, not put a copy in the project. Is there any drawbacks to just accessing it at it's original location?

 

[cjard]

Is there any drawbacks to just accessing it at it's original location?

You change it forever, for everyone?