tattoo

Can anyone help me with creating this Update command. I am linking two tables, Header and Detail, that works fine.

But I'm trying to update Column 3 in the Details table with the contents of Column 3 in the Header Table..

Here is my routine so far..

Code Snippet

Private Sub Update_Header_Details()

Dim Det_Count As Integer = 0

Dim Head_Count As Integer = 0

Dim i As Integer = 0

Dim j As Integer = 0

Dim Head_DS As DataSet = New DataSet("tbl_Accpac_InvHeader")Dim Head_DA As SqlDataAdapter = New SqlDataAdapter("Select * from tbl_Accpac_InvHeader",Del_Conn)

Head_Count = Head_DA.Fill(Head_DS, "tbl_Accpac_InvHeader")

Dim Detail_UDComm As SqlCommand = New SqlCommand()

Dim Det_Conn As SqlConnection = New SqlConnection

Det_Conn.ConnectionString = My.Settings.VIH_Conn

Detail_UDComm.Connection = Det_Conn

For i = 0 To Head_Count - 1

Dim Detail_DS As DataSet = New DataSet("tbl_Accpac_InvDetails")

Dim selCMD As SqlCommand = New SqlCommand

selCMD.CommandText = "Select * from tbl_Accpac_InvDetails Where Field8 = " + "'" +

Head_DS.Tables(0).Rows(i).Item(4) + "'"

Dim Detail_DA As SqlDataAdapter = New SqlDataAdapter(selCMD.CommandText, Del_Conn)

Dim Det_UDCMD As SqlCommand = New SqlCommand()

Det_UDCMD.CommandType = CommandType.Text

Detail_UDComm.CommandText = "Update tbl_Accpac_InvDetails SET Field2 WHERE Field2 = @Field2 "

Detail_UDComm.Parameters.Add("@Field2", SqlDbType.Char, 255, "Field2").Value = Head_DS.Tables(0).Rows(i).Item(2)

Detail_DA.UpdateCommand = Detail_UDComm

Det_Count = Detail_DA.Fill(Detail_DS, "tbl_Accpac_InvDetails")

For j = 0 To Det_Count - 1

Detail_DS.Tables(0).Rows(j).Item(2) = Head_DS.Tables(0).Rows(i).Item(2)

Next

Detail_DA.Update(Detail_DS, "tbl_Accpac_InvDetails")

Detail_UDComm.Parameters.Clear()

Detail_DA.Dispose()

Detail_DS.Dispose()

selCMD.Dispose()

Det_UDCMD.Dispose()

Next

End Sub

Thanks

tattoo



Re: Visual Basic General Need Help creating an Update command

Swade

Is there a reason you're not using TableAdapters






Re: Visual Basic General Need Help creating an Update command

tattoo

Hi Swade,

the only reason is Lack of Knowledge.

I have never used table adapters before.

It sounds like you think this is what I should be using





Re: Visual Basic General Need Help creating an Update command

Swade

Not sure what version of VS you're using, but I use 2005 and Tableadapters are the only way to go, in my opinion. Instead of the 20 lines of code you've got, it can be reduced to:

Code Snippet

tbl_Accpac_InvHeaderTABLEADAPTER.Update(column1value, column2value, etc.....)

That's basiclly the whole thing.

To get started using these, simply use the Data Source Wizard to pick which tables/columns you want from a data source (SQL Server, Oracle, Access, etc...). Then VS will create a .xsd dataset for you with a tableAdapter for each table you chose and a Fill() method. Fill basically works like a select command but will return a DataTable for you to bind to a DataGridView, etc....

You can add more methods (Insert, Update, Delete, scalar selects, etc....) You build all of your SQL in the designer then just call the method in code. 100% managed and a HUGE time saver.






Re: Visual Basic General Need Help creating an Update command

tattoo

OK, now I'm officialy confused....

I was under the impression that I had been using Datatables when I was creating My Dataset.

In my example above on the line where I define the Detail Dataset

Dim Detail_DS As DataSet = New DataSet("tbl_Accpac_InvDetails")

Isn't this telling the dataset that the DataTable I want to include in the dataset is "tbl_Accpac_InvDetails"

What I had originally tried to do was Drag an SQLDataAdapter from the toolbox onto my form and Include the approprate db tables then from the Properties window I tried to copy the SQLUpdateCommand Commandtext.

but ran into the problem of how to put my data into the parameter.





Re: Visual Basic General Need Help creating an Update command

tattoo

I got it to work. !

I created a regular SQL Statement and used ExecuteNonQuery()

Code Snippet

Private Sub Update_Header_Details()

Dim Head_Count As Integer = 0

Dim i As Integer = 0

Dim Head_DS As DataSet = New DataSet("tbl_Accpac_InvHeader")

Dim Head_DA As SqlDataAdapter = New SqlDataAdapter("Select * from tbl_Accpac_InvHeader", Del_Conn)

Head_Count = Head_DA.Fill(Head_DS, "tbl_Accpac_InvHeader")

Dim Det_Conn As SqlConnection = New SqlConnection(My.Settings.VIH_Conn)

Dim Detail_UDComm As SqlCommand = New SqlCommand

Detail_UDComm.Connection = Det_Conn

Det_Conn.Open()

For i = 0 To Head_Count - 1

Detail_UDComm.CommandText = ("Update tbl_Accpac_InvDetails SET Field3 = " + "'" + Head_DS.Tables(0).Rows(i).Item(2) + "'" & _

" WHERE Field8 = " + "'" + Head_DS.Tables(0).Rows(i).Item(4) + "'")

Detail_UDComm.ExecuteNonQuery()

Next

Det_Conn.Close()

End Sub

I'm still a little stumped about Datatables as I mentioned in my last reply in this thread.

Thanks for your comments...





Re: Visual Basic General Need Help creating an Update command

Swade

Well let me try to make this more granular as I'm not good at explaining things.

A DataSet is a local in-memory storage of data that is "populated" by data from outside sources, but it Oracle, SQL Server, XML, or in code inserts.

A DataTable is an item that holds all or a subset of the data in a DataSet. It does NOT do communication with any datasources.

A DataAdapter (from 2003) or a TableAdapter (2005) consist of a DataTable and the communication used to retreive/send data to a datasource. It will handle the difference between all of the queries. It basicallly does 99% of the coding that you just wrote yourself.

TableAdapters are not dropped from the Toolbox, they are created by the DataSource Wizard or in-code. Basically MS took all of the coding used to send/recieve data from a datasource and made it into a class (TableAdapters). So, while you CAN continue to write it all youself, it's much EASIER to use the built-in classes that come out of the box.

It's merely a preference of mine to avoid all of the coding that goes with datasource/database communication and storage, which is what's great about TableAdapters.






Re: Visual Basic General Need Help creating an Update command

tattoo

Thanks Swade, according to your explanation I'm on the right track.

I do realize the difference between coding my own and letting the wizard create them.

I find that when I have a complex Dataset containing multiple Tables, it is best to use the wizard.

When, as in my example, I am just looking for data from one table I tend to do it programmatically.

As you said, personal preference.

I guess I was getting mixed up with terminology.

Thanks again for your response.

tattoo