Connection problem

[pachjo]

I am getting a tad confused as to why I get an error for the code below whereby the second block errors stating invalid object 'transactions'

[SIZE=2]taImportData = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlClient.SqlDataAdapter(strSQLString, glb_cnMM2007)
taImportData.Fill(dtImportData)
 
strSQLString = [/SIZE][SIZE=2][COLOR=#a31515]"SELECT MAX(tran_date) FROM transactions"
[/COLOR][/SIZE][SIZE=2]taTransactions = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlClient.SqlDataAdapter(strSQLString, glb_cnMM2007)[/SIZE]
[SIZE=2][SIZE=2]taTransactions.Fill(dtTransactions)
[/SIZE]
[/SIZE]

but if I do this it works?

[SIZE=2][SIZE=2]strSQLString = [/SIZE][SIZE=2][COLOR=#a31515]"SELECT MAX(tran_date) FROM transactions"
[/COLOR][/SIZE][SIZE=2]taTransactions = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlClient.SqlDataAdapter(strSQLString, glb_cnMM2007.ConnectionString)
taTransactions.Fill(dtTransactions)
[/SIZE][/SIZE]

glb_cnnMM2007 is a global connection variable that is set to my.settings.connectionstring from the main application form on opening.

How come the first block and all other blocks throughout the app accepts it on its own yet the transactions one differs?

The table transactions exists in the database!

 

[cjard]

you shouldnt be writing SqlDataAdapter in your code; the IDE writes that code for you.. i.e. you seem to be doing data access the proper modern way, having a My.Settings.ConnectionString and calling things taXXX implying tableadapter, but then you go and put SQL in your code, and use DataAdapters..

Read the DW2 link, section on Scalar Queries, and dont intermix old and new ways of data access.

Additionally, set option strict = on, because i suspect there are conversion-guess errors going on here

 

[pachjo]

Your post confuses me?

Why wouldI not write:

Dim daTransaction As SqlClient.SqlDataAdapter = Nothing
Dim dtTransactions As DataTable
 
daTransactions = [SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlClient.SqlDataAdapter(strSQLString, glb_cnMM2007)[/SIZE]

I don't know what you mean about intermixing and the IDE does it all for me?

I thought I was doing it the modern way?

My whole app is coded like this and this is the first hiccup I have experienced?

If you mean I should be plonking sqladapter objects on the form etc that would not work for me?

PS: I don't see a section on scalr queries?

 

[cjard]

Dim daTransaction As SqlClient.SqlDataAdapter = Nothing
Dim dtTransactions As DataTable
 
daTransactions = [SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlClient.SqlDataAdapter(strSQLString, glb_cnMM2007)[/SIZE]

Now that.. looks like the Old Way..

I don't know what you mean about intermixing and the IDE does it all for me?

I couldnt understand your use of "ta" as a prefix:

taTransactions.Fill(dtTransactions)

because ta is the usual abbreviation of TableAdapter, a .net 2.0 way of encapsulating data access logic, yet you go and assign it a 1.1 way of daa access in that you say "New SqlDataAdapter"

Confusing.

My whole app is coded like this and this is the first hiccup I have experienced?

I'm not saying it's wrong.. just, old.. Not very good OO either.

If you mean I should be plonking sqladapter objects on the form etc that would not work for me?

Plonking typed TableAdapters on a form, more like

PS: I don't see a section on scalr queries?

Gimme a sec

 

[cjard]

Scalar Queries:

http://msdn2.microsoft.com/en-us/library/eeb84awz(VS.80).aspx

 

[pachjo]

Well I am totally totally deflated and destroyed

I was under the impression I was using 2005 the right way!

My app is working fine and I thought I was near to completion only to find now it is coded old!

ta prefix .... my inability to pick the right one ... I know what it meant DOH!

I'm not saying it's wrong.. just, old.. Not very good OO either. Arse

I don't see a TableAdapter on my toolbox

This is embarrasing and soul destroying for me.

Can I email you the text of my initialze and global variable module so you can complete the ripping my heart out operation?

No I am not being arsey or anything....joking to save myself from crying!

I can email in text form, no .exe or anything like that, just text pasted into the email body.

?

 

[pachjo]

Even worse!!!

I am using the methods I have learned by reading "Teach Yourself Visual Basic 2005" by SAMS

 

[pachjo]

Even the book I have ADO.NET 2.0 mentions dataadapters and not tableadapters?

 

[cjard]

Well I am totally totally deflated and destroyed

I was under the impression I was using 2005 the right way!

My app is working fine and I thought I was near to completion only to find now it is coded old!

Well.. what the heck; it works and you have a good understanding of what is happening under the hood

I don't see a TableAdapter on my toolbox

That's because they are customised; they relate to a specific table in the db, and are generated by the IDE when you add one to a typed DataSet.. By writing just one SQL you get a huge amount of coding done for you, parameters set up, datatables formed etc.. It's wonderful

This is embarrasing and soul destroying for me.

I'm sorry!

Can I email you the text of my initialze and global variable module so you can complete the ripping my heart out operation?

If you feel it would help.. I mean, Modules still have their place, but in OO, it is limited so use them sparingly!

I can email in text form, no .exe or anything like that, just text pasted into the email body.

?

If you want a general commentary on your app, you can upload it in a passworded zip file and PM the password to anyone you like.. If uploading projects to the forum you must delete the BIN and OBJ folders before you upload, and also ensure that any large resources such as database files are reduced as much as possible e.g. Compact or remove unnecessary tables/data

 

[cjard]

I am using the methods I have learned by reading "Teach Yourself Visual Basic 2005" by SAMS

Oof.. boy did you ever get stung.. I dont buy SAMS books. Infact, I dont buy books. If i were to buy books then i would buy Wrox ones.. I know that their latest book on .NET 2.0 teaches the new tableadapter way


Just so you dont feel too bad, take a read of the "DataSet or DataReader" sticky in the ADO.NET forum.. Youll learn that tableadapter arent so new, they are just a very clever, cool dataadapter that holds lots of sqls and switches between them as it suits.. ergo your code isnt 100% different to the "modern" way, its just in different places (i.e. a bit scattered)


DataAdapter -> 2003 way
TableAdapter -> 2005 way

Maybe SAMS will give you a refund?!

 

[pachjo]

OK, here is the project....comments welcome.

I am extremely miffed by the SAMS sting!

I have started to work through the Data Walk Through examples in your link but am finding it alien and trying to get to grips with how I transpose this into my app.......without stuffing it completly!!

How or where do I plonk the zip file onto the forum?

Thanks

 

[pachjo]

Just read FAQ..

file should be here.....I hope

 

[pachjo]

wont let me upload from my hard drive?

Says invalid post?

 

[pachjo]

OK, lets try it this way...

Here is the way I have donw things, I hope I explain myself right and it makes sense.

I have a module where I declare global variables like so:

PS: yes I see I have used wrong prefix!

[SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Public[/COLOR][/SIZE][SIZE=2] glb_cnMM2007 [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlClient.SqlConnection
[/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Public[/COLOR][/SIZE][SIZE=2] glb_taTransactions [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] SqlClient.SqlDataAdapter
[/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Public[/COLOR][/SIZE][SIZE=2] glb_cbTransactions [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] SqlClient.SqlCommandBuilder
[/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Public[/COLOR][/SIZE][SIZE=2] glb_dtTransactions [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] DataTable
[/SIZE][SIZE=2][/SIZE]

My main form when loading calls an inialize function like so:

[SIZE=2]glb_cnMM2007.ConnectionString = [/SIZE][SIZE=2][COLOR=#0000ff]My[/COLOR][/SIZE][SIZE=2].Settings.MB2007ConnectionString
[/SIZE] 
[SIZE=2]glb_taTransactions = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlClient.SqlDataAdapter([/SIZE][SIZE=2][COLOR=#a31515]"SELECT t.id, t.tran_id, t.tran_date, td.tran_description, t.tran_amount, t.tran_balance, t.tran_save, t.tran_withdrawal, t.void "[/COLOR][/SIZE][SIZE=2] & _
[/SIZE][SIZE=2][COLOR=#a31515]"FROM transactions t INNER JOIN transaction_descriptions td ON t.tran_id = td.tran_id "[/COLOR][/SIZE][SIZE=2] & _
[/SIZE][SIZE=2][COLOR=#a31515]"WHERE t.tran_date BETWEEN @from_date AND @to_date AND t.void = 'False' ORDER BY t.tran_date, t.id"[/COLOR][/SIZE][SIZE=2], glb_cnMM2007)
[/SIZE][SIZE=2][COLOR=#008000][/COLOR][/SIZE] 
[SIZE=2][COLOR=#008000]' add date range parameters to the table adapter
[/COLOR][/SIZE][SIZE=2]glb_taTransactions.SelectCommand.Parameters.Add([/SIZE][SIZE=2][COLOR=#a31515]"@from_date"[/COLOR][/SIZE][SIZE=2], SqlDbType.DateTime).Value = [/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].dtmFromDate1.Value
glb_taTransactions.SelectCommand.Parameters.Add([/SIZE][SIZE=2][COLOR=#a31515]"@to_date"[/COLOR][/SIZE][SIZE=2], SqlDbType.DateTime).Value = [/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].dtmToDate1.Value.Date.ToString.Substring(0, 10) & [/SIZE][SIZE=2][COLOR=#a31515]" 23:59:59"
[/COLOR][/SIZE]

Then it calls a function to populate the listview on the form to display transaction within the date range like so:

Removed other case options for claritiy.....he says hopefully

[SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Function[/COLOR][/SIZE][SIZE=2] RefreshTransactionLists([/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] intTabPageNo [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Integer[/COLOR][/SIZE][SIZE=2]) [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Boolean
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] intRowPointer [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Integer[/COLOR][/SIZE][SIZE=2] = 0
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] dtTable [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] DataTable
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] taTableAdapter [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlClient.SqlDataAdapter
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] lstListView [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] ListView
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] objListViewTransactions [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] ListViewItem
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] strSQLString [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = [/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2].Empty
[/SIZE][SIZE=2][COLOR=#008000]' query the selected tabpage
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Select[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Case[/COLOR][/SIZE][SIZE=2] intTabPageNo
[/SIZE][SIZE=2][COLOR=#0000ff]Case[/COLOR][/SIZE][SIZE=2] 1
[/SIZE][SIZE=2][COLOR=#008000]' reference the required table, table adapter and listview
[/COLOR][/SIZE][SIZE=2]dtTable = glb_dtTransactions
taTableAdapter = glb_taTransactions
lstListView = [/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].lstTransactions
[/SIZE][SIZE=2]
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Select
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Try
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#008000]' flush table before refresh
[/COLOR][/SIZE][SIZE=2]dtTable.Clear()
[/SIZE][SIZE=2][COLOR=#008000]' load the results from the data adapters query into the data table 
[/COLOR][/SIZE][SIZE=2]taTableAdapter.Fill(dtTable)
[/SIZE][SIZE=2][COLOR=#008000]' only if we have records
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] dtTable.Rows.Count > 0 [/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#008000]' we need a listviewitem object to manually add items from the data table to the list view
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#008000]' we need a date object to allow converting the object from the data table to a short date
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] dtTranDate [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Date
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#008000]' clear the list view before refresh
[/COLOR][/SIZE][SIZE=2]lstListView.Items.Clear()
[/SIZE][SIZE=2][COLOR=#008000]' loop through the data table
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Do[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]While[/COLOR][/SIZE][SIZE=2] intRowPointer < dtTable.Rows.Count
[/SIZE][SIZE=2][COLOR=#008000]' store the tran date into the date object
[/COLOR][/SIZE][SIZE=2]dtTranDate = Convert.ToDateTime(dtTable.Rows(intRowPointer).Item([/SIZE][SIZE=2][COLOR=#a31515]"tran_date"[/COLOR][/SIZE][SIZE=2]))
[/SIZE][SIZE=2][COLOR=#008000]' add the parent item to the list, note use of .ToShortDateString to get a short date value
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#008000]
[/COLOR][/SIZE][SIZE=2]objListViewTransactions = lstListView.Items.Add(dtTranDate.ToShortDateString)
[/SIZE][SIZE=2][COLOR=#008000]' add the subitems on the same row as the parent above
[/COLOR][/SIZE][SIZE=2]objListViewTransactions.SubItems.Add(dtTable.Rows(intRowPointer).Item([/SIZE][SIZE=2][COLOR=#a31515]"tran_description"[/COLOR][/SIZE][SIZE=2]).ToString)
objListViewTransactions.SubItems.Add(dtTable.Rows(intRowPointer).Item([/SIZE][SIZE=2][COLOR=#a31515]"tran_amount"[/COLOR][/SIZE][SIZE=2]).ToString)
objListViewTransactions.SubItems.Add(dtTable.Rows(intRowPointer).Item([/SIZE][SIZE=2][COLOR=#a31515]"tran_balance"[/COLOR][/SIZE][SIZE=2]).ToString)
objListViewTransactions.SubItems.Add(dtTable.Rows(intRowPointer).Item([/SIZE][SIZE=2][COLOR=#a31515]"id"[/COLOR][/SIZE][SIZE=2]).ToString)
[/SIZE][SIZE=2][COLOR=#008000]' if negative change text colour to red to dipict debits, otherwise black for credits
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] Convert.ToDecimal(dtTable.Rows(intRowPointer).Item([/SIZE][SIZE=2][COLOR=#a31515]"tran_amount"[/COLOR][/SIZE][SIZE=2]).ToString) < 0 [/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2]objListViewTransactions.SubItems.Item(0).ForeColor = Color.Red
[/SIZE][SIZE=2][COLOR=#0000ff]Else
[/COLOR][/SIZE][SIZE=2]objListViewTransactions.SubItems.Item(0).ForeColor = Color.Black
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]If
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#008000]' if void change text colour to blue to dipict voids
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] dtTable.Rows(intRowPointer).Item([/SIZE][SIZE=2][COLOR=#a31515]"void"[/COLOR][/SIZE][SIZE=2]).Equals([/SIZE][SIZE=2][COLOR=#0000ff]True[/COLOR][/SIZE][SIZE=2]) [/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2]objListViewTransactions.SubItems.Item(0).ForeColor = Color.Blue
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]If
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#008000]' move to next row of data table
[/COLOR][/SIZE][SIZE=2]intRowPointer += 1
[/SIZE][SIZE=2][COLOR=#0000ff]Loop
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Else
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#008000]' no records so clear the listview of current data
[/COLOR][/SIZE][SIZE=2]lstListView.Items.Clear()
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]If
[/COLOR][/SIZE][SIZE=2]RefreshTransactionLists = [/SIZE][SIZE=2][COLOR=#0000ff]True
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Catch[/COLOR][/SIZE][SIZE=2] objError [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] Exception
[/SIZE][SIZE=2][COLOR=#008000]' change to the current tabpage in order to access its text property to inform user which tab is causing the problem
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].tbcMain.SelectedIndex = intTabPageNo
MessageBox.Show([/SIZE][SIZE=2][COLOR=#a31515]"Location: RefreshTransactionLists"[/COLOR][/SIZE][SIZE=2] & System.Environment.NewLine & System.Environment.NewLine & [/SIZE][SIZE=2][COLOR=#a31515]"The following error occurred whilst refreshing the "[/COLOR][/SIZE][SIZE=2] & [/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].tbcMain.SelectedTab.Text & [/SIZE][SIZE=2][COLOR=#a31515]" tab"[/COLOR][/SIZE][SIZE=2] & _
System.Environment.NewLine & System.Environment.NewLine & objError.Message & System.Environment.NewLine & System.Environment.NewLine & [/SIZE][SIZE=2][COLOR=#a31515]"If the problem persists contact support@........"[/COLOR][/SIZE][SIZE=2], _
[/SIZE][SIZE=2][COLOR=#a31515]"Data Load Error"[/COLOR][/SIZE][SIZE=2], MessageBoxButtons.OK, MessageBoxIcon.Error)
RefreshTransactionLists = [/SIZE][SIZE=2][COLOR=#0000ff]False
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Try
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Function
[/COLOR][/SIZE]

So know the form displays the last three months trnacastions, date range setting omited.

Then is a trnasaction is adding I do it like this:

omitted other case stuff again for clarity?

[SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Function[/COLOR][/SIZE][SIZE=2] ProcessTransaction([/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] strTransType [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2], [/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] intTabPage [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Integer[/COLOR][/SIZE][SIZE=2]) [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Boolean
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#008000]' controls to make this sub generic
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] ctlDateTimePicker [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] DateTimePicker, ctlComboBox [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] ComboBox, ctlTextBox [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] TextBox
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] ctlCheckBox1 [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] CheckBox, ctlCheckBox2 [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] CheckBox, ctlCheckBoxVoid [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] CheckBox
[/SIZE][SIZE=2][COLOR=#008000]' create an SQL command object to write changes to the database
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] cmdTransactions [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] System.Data.SqlClient.SqlCommand
[/SIZE][SIZE=2][COLOR=#008000]' string to pass table name to dynamic sql 
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] strTableName [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = [/SIZE][SIZE=2][COLOR=#a31515]""
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#008000]' query callers name to setup the sql staement and controls 
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Select[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Case[/COLOR][/SIZE][SIZE=2] strTransType
[/SIZE][SIZE=2][COLOR=#0000ff]Case[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#a31515]"monthly"
[/COLOR][/SIZE][SIZE=2]strTableName = [/SIZE][SIZE=2][COLOR=#a31515]"transactions"
[/COLOR][/SIZE][SIZE=2]cmdTransactions.CommandText = [/SIZE][SIZE=2][COLOR=#a31515]"INSERT INTO transactions (tran_date, tran_id, tran_amount, tran_balance, tran_save, "[/COLOR][/SIZE][SIZE=2] & _
[/SIZE][SIZE=2][COLOR=#a31515]"tran_withdrawal, void) VALUES (@TransDate, @TransDesc, @TranAmount, 0, @Saved, @WithDraw, 0)"
[/COLOR][/SIZE][SIZE=2]ctlDateTimePicker = [/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].dtmTransDate1
ctlComboBox = [/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].cboTransDescription1
ctlTextBox = [/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].txtAmount1
ctlCheckBox1 = [/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].chkSave
ctlCheckBox2 = [/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].chkWithDraw
ctlCheckBoxVoid = [/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].chkShowVoid1
[/SIZE][SIZE=2][COLOR=#008000]' detailed monthly has these but detailed weekly does not
[/COLOR][/SIZE][SIZE=2]cmdTransactions.Parameters.Add([/SIZE][SIZE=2][COLOR=#a31515]"@Saved"[/COLOR][/SIZE][SIZE=2], SqlDbType.Decimal).Value = ctlCheckBox1.Checked
cmdTransactions.Parameters.Add([/SIZE][SIZE=2][COLOR=#a31515]"@WithDraw"[/COLOR][/SIZE][SIZE=2], SqlDbType.Decimal).Value = ctlCheckBox2.Checked
[/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Select
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Try
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#008000]' link the command to the database connection
[/COLOR][/SIZE][SIZE=2]cmdTransactions.Connection = glb_cnMM2007
[/SIZE][SIZE=2][COLOR=#008000]' add the parameters to the command
[/COLOR][/SIZE][SIZE=2]cmdTransactions.Parameters.Add([/SIZE][SIZE=2][COLOR=#a31515]"@TransDate"[/COLOR][/SIZE][SIZE=2], SqlDbType.DateTime).Value = ctlDateTimePicker.Value
cmdTransactions.Parameters.Add([/SIZE][SIZE=2][COLOR=#a31515]"@TransDesc"[/COLOR][/SIZE][SIZE=2], SqlDbType.VarChar).Value = ctlComboBox.SelectedValue.ToString
cmdTransactions.Parameters.Add([/SIZE][SIZE=2][COLOR=#a31515]"@TranAmount"[/COLOR][/SIZE][SIZE=2], SqlDbType.Decimal).Value = ctlTextBox.Text
[/SIZE][SIZE=2][COLOR=#008000]' if the record was appended successfully then update the transaction display
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] cmdTransactions.ExecuteNonQuery().Equals(1) [/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#008000]' refresh the rolling balance in transaction table
[/COLOR][/SIZE][SIZE=2]CalculateRollingBalance(strTableName, ctlCheckBoxVoid.Checked)
RefreshTransactionLists(intTabPage)
ScrollListViews()
[/SIZE][SIZE=2][COLOR=#008000]' reset the transaction fields
[/COLOR][/SIZE][SIZE=2]ctlTextBox.Text = [/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2].Empty
ctlCheckBox1.Checked = [/SIZE][SIZE=2][COLOR=#0000ff]False
[/COLOR][/SIZE][SIZE=2]ctlCheckBox2.Checked = [/SIZE][SIZE=2][COLOR=#0000ff]False
[/COLOR][/SIZE][SIZE=2]ctlTextBox.Select()
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]If
[/COLOR][/SIZE][SIZE=2]ProcessTransaction = [/SIZE][SIZE=2][COLOR=#0000ff]True
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Catch[/COLOR][/SIZE][SIZE=2] objError [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] Exception
MessageBox.Show([/SIZE][SIZE=2][COLOR=#a31515]"Location: ProcessTransaction"[/COLOR][/SIZE][SIZE=2] & System.Environment.NewLine & System.Environment.NewLine & [/SIZE][SIZE=2][COLOR=#a31515]"The following error occurred whilst processing the transaction"[/COLOR][/SIZE][SIZE=2] & _
System.Environment.NewLine & System.Environment.NewLine & objError.Message & System.Environment.NewLine & System.Environment.NewLine & [/SIZE][SIZE=2][COLOR=#a31515]"If the problem persists contact support@........"[/COLOR][/SIZE][SIZE=2], _
[/SIZE][SIZE=2][COLOR=#a31515]"Save Error"[/COLOR][/SIZE][SIZE=2], MessageBoxButtons.OK, MessageBoxIcon.Error)
ProcessTransaction = [/SIZE][SIZE=2][COLOR=#0000ff]False
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Finally
[/COLOR][/SIZE][SIZE=2]cmdTransactions.Dispose()
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Try
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Function
[/COLOR][/SIZE]

To date all works OK.

My application successfully allows me to input, delete, void trnascations print them, modify them, backup and restore.

The last section that has ended my dream is when importing from a spreadsheet which started this particular thread off....

On my form I do not have any datasets, binding objects etc it is all done in code

I do in code bind some comboboxes to the global dataadpaters/datatables which again work exactly as expected.

 

[cjard]

how I transpose this into my app.......without stuffing it completly!!

If your app works, then for the love of god.. leave it alone, or at least back it up...

How or where do I plonk the zip file onto the forum?

Upload an attachment, as a zip file
DELETE THE BIN AND OBJ FOLDERS BEFORE YOU MAKE THE ZIP