pachjo

Could a guru of networking and databases please help me with this problems please? I need to know which of the following methods is better, ie less network pounding.... Situation: A sql server database running on a host 25 miles away. two maybe three queries need to be run to import very large data into a local c: drive database. Option 1: An MS Access database application with the required make table queries Option 2: A VB.NET application importing the data into a mdf file using sql server datasource Is my thoughts that option 2 would be more efficient and less network hungry? Thanks for any advice on offer.... Edit/Delete Message

I have made a button click handler to be generic to enable it to handle two buttons. One button saves a record to the transactions table and the other to the transactions_weekly table. Now if the record begin saved when running a call to the event handler for the transactions is of type "SHOPPING" the app prompts the user to ask do they want it also saved to the transactions_weekly table. If the answer is yes I believe I need to recall the event handler I am currently in? So to do this I create an object reference to the button on the form that would save a transaction_weekly record and then call the click event and pass it the reference object. Problem is the error appears saying that objSender is not initialised when an attempt is made to reference it? Am on on the right track or totally off beam? Private Sub btnSaveTransaction_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _ Handles btnSaveTransaction1.Click, btnSaveTransaction2.Click ' check to see if a valid entry has been made If Not ValidateEntry(sender) Then Exit Sub End If ' create a control object to access the name of the button making the call Dim ctlSender As Control = DirectCast(sender, System.Windows.Forms.Control) ' controls to make this sub generic Dim ctlDateTimePicker As New DateTimePicker, ctlComboBox As New ComboBox, ctlTextBox As New TextBox Dim ctlCheckBox1 As New CheckBox, ctlCheckBox2 As New CheckBox ' create an SQL command object to write changes to the database Dim cmdTransactions As New System.Data.SqlClient.SqlCommand ' string to pass table name to dynamic sql Dim strTableName As String = "" ' object to reference the save button on the transactions weekly page Dim objSender As Object = DirectCast(Me.Controls("btnSaveTransaction2"), Object) ' query callers name to setup the sql staement and controls Select Case ctlSender.Name Case "btnSaveTransaction1" strTableName = "transactions" cmdTransactions.CommandText = "INSERT INTO transactions (tran_date, tran_id, tran_amount, tran_balance, tran_save, " & _ "tran_withdrawal) VALUES (@TransDate, @TransDesc, @TranAmount, 0, @Saved, @WithDraw)" ctlDateTimePicker = Me.dtmTransDate1 ctlComboBox = Me.cboTransDescription1 ctlTextBox = Me.txtAmount1 ctlCheckBox1 = Me.chkSave ctlCheckBox2 = Me.chkWithDraw ' detailed monthly has these but detailed weekly does not cmdTransactions.Parameters.Add("@Saved", SqlDbType.Decimal).Value = ctlCheckBox1.Checked cmdTransactions.Parameters.Add("@WithDraw", SqlDbType.Decimal).Value = ctlCheckBox2.Checked ' when the transaction type is shopping ask do we want to save to transactions weekly also If ctlComboBox.Text = "SHOPPING" Then If MessageBox.Show("Do you want this transaction written to the weekly transaction page", _ "Copy Transaction", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2) _ = Windows.Forms.DialogResult.Yes Then ' set the fields on the transactions weekly page to the ones on the transaction page ' so when we call this event handler the values will be there as if the user had entered them Me.dtmTransDate2.Text = ctlDateTimePicker.Text Me.txtAmount2.Text = ctlTextBox.Text Me.cboTransDescription2.Text = ctlComboBox.Text ' simulate the user clicking the save button on the transactions weekly page btnSaveTransaction_Click(objSender, e) ' reset the transactions weekly fields Me.dtmTransDate2.Text = Now.ToString Me.txtAmount2.Text = "" End If End If Case "btnSaveTransaction2" strTableName = "transactions_weekly" cmdTransactions.CommandText = "INSERT INTO transactions_weekly (tran_date, tran_id, tran_amount, tran_balance) " & _ "VALUES (@TransDate, @TransDesc, @TranAmount, 0)" ctlDateTimePicker = Me.dtmTransDate2 ctlComboBox = Me.cboTransDescription2 ctlTextBox = Me.txtAmount2 End Select Try ' link the command to the database connection cmdTransactions.Connection = glb_cnMB2007 ' add the parameters to the command cmdTransactions.Parameters.Add("@TransDate", SqlDbType.DateTime).Value = ctlDateTimePicker.Text cmdTransactions.Parameters.Add("@TransDesc", SqlDbType.VarChar).Value = ctlComboBox.SelectedValue.ToString cmdTransactions.Parameters.Add("@TranAmount", SqlDbType.Decimal).Value = ctlTextBox.Text ' if the record was appended successfully then update the transaction display If cmdTransactions.ExecuteNonQuery() = 1 Then ' refresh the rolling balance in transaction table CalculateRollingBalance(strTableName) If Not RefreshTransactionLists(Me.tbcMain.SelectedIndex) Then Me.Controls("btnSaveTransaction" & Me.tbcMain.SelectedIndex).Enabled = False Me.Controls("btnClearTransaction" & Me.tbcMain.SelectedIndex).Enabled = False End If End If ' reset the transaction fields ctlDateTimePicker.Value = Now ctlTextBox.Text = "" ctlTextBox.Select() Catch objError As Exception ' tell user no connection made MessageBox.Show("Failed to save transaction to the database" & vbCrLf & vbCrLf & objError.Message, _ "Save Error", MessageBoxButtons.OK, MessageBoxIcon.Error) End Try End Sub

Thanks, been away for a few days!! Yes weird that I can't see why I should have to do it this way, but it works perfect so I am happy

Hi, thanks. I am finding it easier to do it all by code instead of dragging a dataset onto my form.

I have set a combobox as follows: 'set the bindings for the transaction description combobox Me.cboTransDescription.DataSource = glb_dbTransactionDescBinding Me.cboTransDescription.DisplayMember = "tran_description" Me.cboTransDescription.ValueMember = "tran_id" This successfully populates the combobox and also allows me to add items which appear in the combobox. I want to be able to delete the selected item in the combobox and found on this site that one should use a bindingsource to do so which I did and it does get removed from the combobox but I have found that neither adding or deleting affects the actual database. Here is how I setup the bindingsource: glb_taTransactionDesc.Fill(glb_dtTransactionDesc) glb_dbTransactionDescBinding.DataSource = glb_dtTransactionDesc and here is how I delete a record from the combobox but does not affect the database: glb_dbTransactionDescBinding.Remove(glb_dbTransactionDescBinding.Current) glb_taTransactionDesc.Update(glb_dtTransactionDesc) Have I lost the plot and missed something important? Thanks

Not sure why I should have to do it this way, but if it works dont knock it? I removed all the guff from the design view and coded the lot and it works? I created a tableadapter, commandbuilder and datatable and linked the combobox to this. Now when I do the update it all works a treat ;)

Hi, I think problems I am having with datasets may be down to my misunderstanding the above topic? When I place a dataset on my form in designe view I get the option to created a typed or untyped dataset. How do I create a new dataset that has all the required auto genreated selcect, update, delete commands built in when I have no other datasets created? I have no choice to create an untyped one and then all my prblems start as they have no commands in place? I see a tableadapter appears but I dont have a property accessible to set any of the required commands?

here is the project with the .exe files removed. when you click on the detailed monthly tab the password is set to 123 for now. the problem is when you try to save a new transaction description that should be reflected in the combo box above. I tried to email it to you also but it bounces back... Butters.zip

if you can tell me how to post it, or I can zip the project up and email to you? It's doing my nut in! I deleted the stuff and recreated it all to no avail!!

Yeah, I copy/pasted the line. What I am trying now is deleting all the dataset, binding and table adapter stuff from design view and recreating from scratch just in case there is some corruption somewhere?? Will let you know what happens.......

No still no joy! I have tried the fill outside the try statement and no update to the dataset takes place!!! I am totally totally stumped now! I don't know what else to try?

The sql for the select command is: SELECT tran_description, tran_id FROM dbo.transaction_descriptions ORDER BY tran_description

Yes out of curiosity the dataset, databinding and table adapter were created by VS in desgin view. So how do I set the insert command for example? When I try to access it in code I get the option .insert but it does not give me the option to assign a value to it?

Yes it does run and I have done the step through with a messagebox to show the count, but here are the results: Before execution of If cmdTransactionDescription.ExecuteNonQuery() = 1 Then there are 8 rows in the transaction_descriptions table in the dataset The line Me.taTransactiondescriptions.Fill(Me.MB2007DataSet.transaction_descriptions) runs deep into VS generated code that I do not understand and returns without any error. After that the row count is still 8 After I close the program and look at the database there are 9 rows. So I know that the database using the command method works but the dataset, tableadapter bit is not doing anything????

Hi, sorry that was an oversight on my part not uncommenting line you mention. It does not work. You will see I have the .clear line above which I also tried to see if that would work. When this code runs the row is added to the database and the combobox blinks as is it has been accessed by the code but the new line does not appear. It only appears if I close the program and run it again! So it does look like from what you were saying that I was on the right path which pleases me, but still have a situation where either the database gets updated and the combobox does not or the database does not get updated but the combobox does? Yoinks!

How my problem started out was that I had successfully updated the database using a command object but atr the combobox is linked to a databinding object to a table adapter to a dataset I was trying to get the dataset - tableadapter - databinding to refresh so the combobox would update but no joy? I think I am getting my wires crossed on which method uses what etc etc??:confused:

HI, well if you mean look at the taTransactionDescriptions in form design all I get for its properties there are: ApplicationSettings Name ClearBeforeFill GenerateMember Modifiers I had previoulsy manually created a command object to insert the new row and that sussessfully updated the database but would not update the combobox no matter what I tried? This writes to the database but does not update combo? ' create an SQL command object to write changes to the database Dim cmdTransactionDescription As New System.Data.SqlClient.SqlCommand Dim strSQLString As String ' create the SQL statement using user input from detailed monthly tabpage strSQLString = "INSERT INTO transaction_descriptions (tran_description) VALUES ('" & Me.txtNewTransactionDescription.Text & "')" Try ' link the command to the database connection cmdTransactionDescription.Connection = glb_cnMB2007 ' set the commands commandtext using the SQL statement cmdTransactionDescription.CommandText = strSQLString ' if the record was appended successfully then update the transaction display If cmdTransactionDescription.ExecuteNonQuery() = 1 Then Me.btnSaveTransaction.Enabled = False Me.btnClearTransaction.Enabled = False 'try to fill the data adapter with the transaction description table data 'Me.MB2007DataSet.Clear() 'Me.taTransactiondescriptions.Fill(Me.MB2007DataSet.transaction_descriptions) End If Me.txtNewTransactionDescription.Clear() Me.txtNewTransactionDescription.Select() Catch objError As Exception ' tell user no connection made MessageBox.Show("Failed to save transaction description to the database" & vbCrLf & vbCrLf & objError.Message, "Save Error", MessageBoxButtons.OK, MessageBoxIcon.Error) Finally cmdTransactionDescription.Dispose() End Try

Hi, I get an error saying no overload resolution failed because no accessible update can be called with these arguments. The way I am trying to add the new row is like this: Try ' create a row from the dataset data table Dim drDataRow As DataRow = Me.MB2007DataSet.transaction_descriptions.NewRow() ' set the transaction description using the users input drDataRow("tran_description") = Me.txtNewTransactionDescription.Text ' add the row to the table Me.MB2007DataSet.transaction_descriptions.Rows.Add(drDataRow) ' commit changes to database Me.taTransactiondescriptions.Update(Me.MB2007DataSet.transaction_descriptions.GetChanges()) ' clear the user input field and set focus Me.txtNewTransactionDescription.Clear() Me.txtNewTransactionDescription.Select() Catch objError As Exception ' tell user no connection made MessageBox.Show("Failed to save transaction description to the database" & vbCrLf & vbCrLf & objError.Message, "Save Error", MessageBoxButtons.OK, MessageBoxIcon.Error) End Try

I have tried the following to try to persist the data but no joy? 'causes no errors or persist Me.taTransactiondescriptions.Update(Me.MB2007DataSet.transaction_descriptions) ' causes no errors or persist Me.taTransactiondescriptions.Update(Me.MB2007DataSet) ' overridereason causes error as it is not found? Me.taTransactiondescriptions.Update(overridereason.select())

On the properties for my dataset I have this: daTransactiondescriptions Butters.MB2007DataSetTableAdapters.transaction_descriptionsTableAdapter So I guess it is my naming it with da that has caused the confusion?

Hi, so are you saying I should be using tableadpaters instead of dataadapters? I am definitely using 2005 and am a tad knocked back that a book I bought to get upto speed on 2005 from 2003 has lead me a merry dance :confused:

Hi, thanks....now I am even more confused! I am using Visual Studio .NET 2005? The data adapter rather than table adapter? This appeared by itself and also the coding part of this issue I learned in SAMS Teach Yourself VB 2005 in 24 hours and it used data adapters? I somewhat lost now?

Hi, I am getting a tad confused with the methods used to populate and repopulate of a combobox. Here is the story: I have a combobox on a form that is setup as follows: datasource = MB2007DataSetBindingSource displaymember = tran_description valuemember = tran_id MB2007DataSetBindingSource is setup as follows: datasource = MB2007DataSet datamember = transaction_descriptions And there is also a data adapter named daTransactiondescriptions When the form loads this line is executed: Me.daTransactiondescriptions.Fill(Me.MB2007DataSet.transaction_descriptions) Now this works a treat and as I would expect. However the problem arises when I write data to the table. I guess I am missing something here, but this is my code: Try ' create a row from the dataset data table Dim drDataRow As DataRow = Me.MB2007DataSet.transaction_descriptions.NewRow() ' set the transaction description using the users input drDataRow("tran_description") = Me.txtNewTransactionDescription.Text ' add the row to the table Me.MB2007DataSet.transaction_descriptions.Rows.Add(drDataRow) ' commit changes to database Me.daTransactiondescriptions.Update(Me.MB2007DataSet.transaction_descriptions) ' clear the user input field and set focus Me.txtNewTransactionDescription.Clear() Me.txtNewTransactionDescription.Select() Catch objError As Exception ' tell user no connection made MessageBox.Show("Failed to save transaction description to the database" & vbCrLf & vbCrLf & objError.Message, "Save Error", MessageBoxButtons.OK, MessageBoxIcon.Error) End Try this code successfully adds the new item to the combo box but I seem to be failing in getting it to commit the change to the database as when I open up the table after closing the apoplication the new entry has not been written? Any tips please? Thnx