|
web
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Updating database in a loopFor every record in tblA where colB = 'abc', I want to update the value in colA. In VB6, using ADO I can loop thru the recordset,set the values of colA and call the Update method. How can I do this in VB.NET and SqlDataAdapter ? Thank you. m_cmdSQL = New SqlClient.SqlCommand With m_cmdSQL .Connection = adoCon .CommandText = sSQL End With m_daSQL = New SqlClient.SqlDataAdapter m_dsSQL = New DataSet m_daSQL.SelectCommand = "select * from tblA where [colB] = 'abc'" m_daSQL.Fill(m_dsSQL) lRow = 0 For Each aRow In m_dsSQL.Tables(0).Rows() m_dsSQL.Tables(0).Rows(lRow).Item("colA") = lrow --> this updates the value in memory, but not in the database lRow = lRow + 1 Next You'll need to create/configure the dataadapter's InsertCommand and its
CommandText property with your own update logic. Once you've done that you simply add: m_daSQL.Update to your code. Show quoteHide quote "fniles" <fni***@pfmail.com> wrote in message news:%23ZJe9TwHHHA.2112@TK2MSFTNGP03.phx.gbl... >I am using VB.NET 2003, SQL 2000, and SqlDataAdapter. > For every record in tblA where colB = 'abc', I want to update the value in > colA. > In VB6, using ADO I can loop thru the recordset,set the values of colA and > call the Update method. > How can I do this in VB.NET and SqlDataAdapter ? Thank you. > > m_cmdSQL = New SqlClient.SqlCommand > With m_cmdSQL > .Connection = adoCon > .CommandText = sSQL > End With > m_daSQL = New SqlClient.SqlDataAdapter > m_dsSQL = New DataSet > m_daSQL.SelectCommand = "select * from tblA where [colB] = 'abc'" > m_daSQL.Fill(m_dsSQL) > lRow = 0 > For Each aRow In m_dsSQL.Tables(0).Rows() > m_dsSQL.Tables(0).Rows(lRow).Item("colA") = lrow --> this > updates the value in memory, but not in the database > lRow = lRow + 1 > Next > > Thank you.
I am sorry, I am still confused. In my example, I Fill the dataset using CommandText "select * from tblA where [colB] = 'abc'", before I update it. If I need to use InsertCommand, can I use the same DataAdapter/DataSet ? Thanks m_cmdSQL = New SqlClient.SqlCommand With m_cmdSQL .Connection = adoCon .CommandText = sSQL End With m_daSQL = New SqlClient.SqlDataAdapter m_dsSQL = New DataSet m_daSQL.SelectCommand = "select * from tblA where [colB] = 'abc'" m_daSQL.Fill(m_dsSQL) lRow = 0 For Each aRow In m_dsSQL.Tables(0).Rows() sSQL = "update tblA set" sSQL = sSQL & (" colA = " & lrow) sSQL = sSQL & (" where [colB] = 'abc') m_daSQL.Update ---> how can I use the same m_daSQL that is already filled with the "select * from tblA where [colB] = 'abc'" ? lRow = lRow + 1 Next Show quoteHide quote "Scott M." <s-mar@nospam.nospam> wrote in message news:%23pT%23oZwHHHA.1248@TK2MSFTNGP03.phx.gbl... > You'll need to create/configure the dataadapter's InsertCommand and its > CommandText property with your own update logic. Once you've done that > you simply add: > > m_daSQL.Update > > to your code. > > "fniles" <fni***@pfmail.com> wrote in message > news:%23ZJe9TwHHHA.2112@TK2MSFTNGP03.phx.gbl... >>I am using VB.NET 2003, SQL 2000, and SqlDataAdapter. >> For every record in tblA where colB = 'abc', I want to update the value >> in colA. >> In VB6, using ADO I can loop thru the recordset,set the values of colA >> and call the Update method. >> How can I do this in VB.NET and SqlDataAdapter ? Thank you. >> >> m_cmdSQL = New SqlClient.SqlCommand >> With m_cmdSQL >> .Connection = adoCon >> .CommandText = sSQL >> End With >> m_daSQL = New SqlClient.SqlDataAdapter >> m_dsSQL = New DataSet >> m_daSQL.SelectCommand = "select * from tblA where [colB] = 'abc'" >> m_daSQL.Fill(m_dsSQL) >> lRow = 0 >> For Each aRow In m_dsSQL.Tables(0).Rows() >> m_dsSQL.Tables(0).Rows(lRow).Item("colA") = lrow --> this >> updates the value in memory, but not in the database >> lRow = lRow + 1 >> Next >> >> > > Your DataAdapter goes and gets a copy of the data you want (based on the
Select statement you set up in the DataAdapter's select command). The copy is placed in your dataset. You make changes to your dataset and then you call the DataAdapter's update method. But you must set up the UpdateCommand so that it does the update you want it to. By the way, the m_ prefix you are using is not a recommened naming convention and will cause more confusion than anything else. Also, don't use the prefix of "ado" for your connection name since you aren't using ado anyway, you're using ADO.NET. Here's your code again (modified for better naming conventions and correct coding): Diim con As New SqlClient.SqlClientConnection("connection string here") Dim da As New SqlClient.SqlDataAdapter("select * from tblA where [colB] = 'abc'", con) Dim updateCommand As New SqlClient.SqlClientCommand("UPDATE tblA blah, blah, blah") Dim ds As New DataSet da.UpdateCommand = updateCommand Try daSQL.Fill(ds, "tblA") Dim i As Integer For i = 0 To ds.Tables(0).Rows().Count -1 'Forget about update statements here, you are working with 'a disconnected DataSet now, so just make whatever changes 'you need to the DataRows that this loop iterates over If ds.Tables(0).Rows(i).Columns("colB") = "abc" Then ds.Tables(0).Rows(i).Columns("colA") = i End If Next da.Update() Catch ex As Exception 'handle exceptions here Finally con.close() 'not needed if the connection was closed to begin with End Try Show quoteHide quote "fniles" <fni***@pfmail.com> wrote in message news:uHVbakwHHHA.3312@TK2MSFTNGP03.phx.gbl... > Thank you. > I am sorry, I am still confused. > In my example, I Fill the dataset using CommandText "select * from tblA > where [colB] = 'abc'", before I update it. > If I need to use InsertCommand, can I use the same DataAdapter/DataSet ? > Thanks > > m_cmdSQL = New SqlClient.SqlCommand > With m_cmdSQL > .Connection = adoCon > .CommandText = sSQL > End With > m_daSQL = New SqlClient.SqlDataAdapter > m_dsSQL = New DataSet > m_daSQL.SelectCommand = "select * from tblA where [colB] = 'abc'" > m_daSQL.Fill(m_dsSQL) > lRow = 0 > For Each aRow In m_dsSQL.Tables(0).Rows() > sSQL = "update tblA set" > sSQL = sSQL & (" colA = " & lrow) > sSQL = sSQL & (" where [colB] = 'abc') > m_daSQL.Update ---> how can I use the same m_daSQL that is already filled > with the "select * from tblA where [colB] = 'abc'" ? > lRow = lRow + 1 > Next > > "Scott M." <s-mar@nospam.nospam> wrote in message > news:%23pT%23oZwHHHA.1248@TK2MSFTNGP03.phx.gbl... >> You'll need to create/configure the dataadapter's InsertCommand and its >> CommandText property with your own update logic. Once you've done that >> you simply add: >> >> m_daSQL.Update >> >> to your code. >> >> "fniles" <fni***@pfmail.com> wrote in message >> news:%23ZJe9TwHHHA.2112@TK2MSFTNGP03.phx.gbl... >>>I am using VB.NET 2003, SQL 2000, and SqlDataAdapter. >>> For every record in tblA where colB = 'abc', I want to update the value >>> in colA. >>> In VB6, using ADO I can loop thru the recordset,set the values of colA >>> and call the Update method. >>> How can I do this in VB.NET and SqlDataAdapter ? Thank you. >>> >>> m_cmdSQL = New SqlClient.SqlCommand >>> With m_cmdSQL >>> .Connection = adoCon >>> .CommandText = sSQL >>> End With >>> m_daSQL = New SqlClient.SqlDataAdapter >>> m_dsSQL = New DataSet >>> m_daSQL.SelectCommand = "select * from tblA where [colB] = 'abc'" >>> m_daSQL.Fill(m_dsSQL) >>> lRow = 0 >>> For Each aRow In m_dsSQL.Tables(0).Rows() >>> m_dsSQL.Tables(0).Rows(lRow).Item("colA") = lrow --> this >>> updates the value in memory, but not in the database >>> lRow = lRow + 1 >>> Next >>> >>> >> >> > > Thank you.
I have a few questions on your method: > Dim updateCommand As New SqlClient.SqlClientCommand("UPDATE tblA blah, Is the blah blah blah something like> blah, blah") "UPDATE tblA set ColA = " & lROW ? If that is the case, at the time I assigned it (before the For loop), I do not know the lRow yet. >da.Update() This requires a parameter, so should I set the parameter to ds (the DataSet) ? Thanks. Show quoteHide quote "Scott M." <s-mar@nospam.nospam> wrote in message news:u84QJrxHHHA.1064@TK2MSFTNGP04.phx.gbl... > Your DataAdapter goes and gets a copy of the data you want (based on the > Select statement you set up in the DataAdapter's select command). The > copy is placed in your dataset. You make changes to your dataset and then > you call the DataAdapter's update method. But you must set up the > UpdateCommand so that it does the update you want it to. > > By the way, the m_ prefix you are using is not a recommened naming > convention and will cause more confusion than anything else. Also, don't > use the prefix of "ado" for your connection name since you aren't using > ado anyway, you're using ADO.NET. > > Here's your code again (modified for better naming conventions and correct > coding): > > Diim con As New SqlClient.SqlClientConnection("connection string here") > Dim da As New SqlClient.SqlDataAdapter("select * from tblA where [colB] = > 'abc'", con) > Dim updateCommand As New SqlClient.SqlClientCommand("UPDATE tblA blah, > blah, blah") > Dim ds As New DataSet > > da.UpdateCommand = updateCommand > > Try > daSQL.Fill(ds, "tblA") > Dim i As Integer > For i = 0 To ds.Tables(0).Rows().Count -1 > 'Forget about update statements here, you are working with > 'a disconnected DataSet now, so just make whatever changes > 'you need to the DataRows that this loop iterates over > If ds.Tables(0).Rows(i).Columns("colB") = "abc" Then > ds.Tables(0).Rows(i).Columns("colA") = i > End If > Next > > da.Update() > > Catch ex As Exception > 'handle exceptions here > Finally > con.close() 'not needed if the connection was closed to begin with > End Try > > > > > > > > > > "fniles" <fni***@pfmail.com> wrote in message > news:uHVbakwHHHA.3312@TK2MSFTNGP03.phx.gbl... >> Thank you. >> I am sorry, I am still confused. >> In my example, I Fill the dataset using CommandText "select * from tblA >> where [colB] = 'abc'", before I update it. >> If I need to use InsertCommand, can I use the same DataAdapter/DataSet ? >> Thanks >> >> m_cmdSQL = New SqlClient.SqlCommand >> With m_cmdSQL >> .Connection = adoCon >> .CommandText = sSQL >> End With >> m_daSQL = New SqlClient.SqlDataAdapter >> m_dsSQL = New DataSet >> m_daSQL.SelectCommand = "select * from tblA where [colB] = 'abc'" >> m_daSQL.Fill(m_dsSQL) >> lRow = 0 >> For Each aRow In m_dsSQL.Tables(0).Rows() >> sSQL = "update tblA set" >> sSQL = sSQL & (" colA = " & lrow) >> sSQL = sSQL & (" where [colB] = 'abc') >> m_daSQL.Update ---> how can I use the same m_daSQL that is already filled >> with the "select * from tblA where [colB] = 'abc'" ? >> lRow = lRow + 1 >> Next >> >> "Scott M." <s-mar@nospam.nospam> wrote in message >> news:%23pT%23oZwHHHA.1248@TK2MSFTNGP03.phx.gbl... >>> You'll need to create/configure the dataadapter's InsertCommand and its >>> CommandText property with your own update logic. Once you've done that >>> you simply add: >>> >>> m_daSQL.Update >>> >>> to your code. >>> >>> "fniles" <fni***@pfmail.com> wrote in message >>> news:%23ZJe9TwHHHA.2112@TK2MSFTNGP03.phx.gbl... >>>>I am using VB.NET 2003, SQL 2000, and SqlDataAdapter. >>>> For every record in tblA where colB = 'abc', I want to update the value >>>> in colA. >>>> In VB6, using ADO I can loop thru the recordset,set the values of colA >>>> and call the Update method. >>>> How can I do this in VB.NET and SqlDataAdapter ? Thank you. >>>> >>>> m_cmdSQL = New SqlClient.SqlCommand >>>> With m_cmdSQL >>>> .Connection = adoCon >>>> .CommandText = sSQL >>>> End With >>>> m_daSQL = New SqlClient.SqlDataAdapter >>>> m_dsSQL = New DataSet >>>> m_daSQL.SelectCommand = "select * from tblA where [colB] = 'abc'" >>>> m_daSQL.Fill(m_dsSQL) >>>> lRow = 0 >>>> For Each aRow In m_dsSQL.Tables(0).Rows() >>>> m_dsSQL.Tables(0).Rows(lRow).Item("colA") = lrow --> this >>>> updates the value in memory, but not in the database >>>> lRow = lRow + 1 >>>> Next >>>> >>>> >>> >>> >> >> > > No, the update statement wouldn't be referring to rows of the DataSet, it
would be referring to column names in the DataSet and the fields those columns map to in your database. The update method should be passed the dataset as its argument. Show quoteHide quote "fniles" <fni***@pfmail.com> wrote in message news:%233o$gC5HHHA.1280@TK2MSFTNGP04.phx.gbl... > Thank you. > I have a few questions on your method: >> Dim updateCommand As New SqlClient.SqlClientCommand("UPDATE tblA blah, >> blah, blah") > Is the blah blah blah something like > "UPDATE tblA set ColA = " & lROW ? > If that is the case, at the time I assigned it (before the For loop), I do > not know the lRow yet. > >>da.Update() > This requires a parameter, so should I set the parameter to ds (the > DataSet) ? > > Thanks. > > "Scott M." <s-mar@nospam.nospam> wrote in message > news:u84QJrxHHHA.1064@TK2MSFTNGP04.phx.gbl... >> Your DataAdapter goes and gets a copy of the data you want (based on the >> Select statement you set up in the DataAdapter's select command). The >> copy is placed in your dataset. You make changes to your dataset and >> then you call the DataAdapter's update method. But you must set up the >> UpdateCommand so that it does the update you want it to. >> >> By the way, the m_ prefix you are using is not a recommened naming >> convention and will cause more confusion than anything else. Also, don't >> use the prefix of "ado" for your connection name since you aren't using >> ado anyway, you're using ADO.NET. >> >> Here's your code again (modified for better naming conventions and >> correct coding): >> >> Diim con As New SqlClient.SqlClientConnection("connection string here") >> Dim da As New SqlClient.SqlDataAdapter("select * from tblA where [colB] >> = 'abc'", con) >> Dim updateCommand As New SqlClient.SqlClientCommand("UPDATE tblA blah, >> blah, blah") >> Dim ds As New DataSet >> >> da.UpdateCommand = updateCommand >> >> Try >> daSQL.Fill(ds, "tblA") >> Dim i As Integer >> For i = 0 To ds.Tables(0).Rows().Count -1 >> 'Forget about update statements here, you are working with >> 'a disconnected DataSet now, so just make whatever changes >> 'you need to the DataRows that this loop iterates over >> If ds.Tables(0).Rows(i).Columns("colB") = "abc" Then >> ds.Tables(0).Rows(i).Columns("colA") = i >> End If >> Next >> >> da.Update() >> >> Catch ex As Exception >> 'handle exceptions here >> Finally >> con.close() 'not needed if the connection was closed to begin with >> End Try >> >> >> >> >> >> >> >> >> >> "fniles" <fni***@pfmail.com> wrote in message >> news:uHVbakwHHHA.3312@TK2MSFTNGP03.phx.gbl... >>> Thank you. >>> I am sorry, I am still confused. >>> In my example, I Fill the dataset using CommandText "select * from tblA >>> where [colB] = 'abc'", before I update it. >>> If I need to use InsertCommand, can I use the same DataAdapter/DataSet ? >>> Thanks >>> >>> m_cmdSQL = New SqlClient.SqlCommand >>> With m_cmdSQL >>> .Connection = adoCon >>> .CommandText = sSQL >>> End With >>> m_daSQL = New SqlClient.SqlDataAdapter >>> m_dsSQL = New DataSet >>> m_daSQL.SelectCommand = "select * from tblA where [colB] = 'abc'" >>> m_daSQL.Fill(m_dsSQL) >>> lRow = 0 >>> For Each aRow In m_dsSQL.Tables(0).Rows() >>> sSQL = "update tblA set" >>> sSQL = sSQL & (" colA = " & lrow) >>> sSQL = sSQL & (" where [colB] = 'abc') >>> m_daSQL.Update ---> how can I use the same m_daSQL that is already >>> filled with the "select * from tblA where [colB] = 'abc'" ? >>> lRow = lRow + 1 >>> Next >>> >>> "Scott M." <s-mar@nospam.nospam> wrote in message >>> news:%23pT%23oZwHHHA.1248@TK2MSFTNGP03.phx.gbl... >>>> You'll need to create/configure the dataadapter's InsertCommand and its >>>> CommandText property with your own update logic. Once you've done that >>>> you simply add: >>>> >>>> m_daSQL.Update >>>> >>>> to your code. >>>> >>>> "fniles" <fni***@pfmail.com> wrote in message >>>> news:%23ZJe9TwHHHA.2112@TK2MSFTNGP03.phx.gbl... >>>>>I am using VB.NET 2003, SQL 2000, and SqlDataAdapter. >>>>> For every record in tblA where colB = 'abc', I want to update the >>>>> value in colA. >>>>> In VB6, using ADO I can loop thru the recordset,set the values of colA >>>>> and call the Update method. >>>>> How can I do this in VB.NET and SqlDataAdapter ? Thank you. >>>>> >>>>> m_cmdSQL = New SqlClient.SqlCommand >>>>> With m_cmdSQL >>>>> .Connection = adoCon >>>>> .CommandText = sSQL >>>>> End With >>>>> m_daSQL = New SqlClient.SqlDataAdapter >>>>> m_dsSQL = New DataSet >>>>> m_daSQL.SelectCommand = "select * from tblA where [colB] = 'abc'" >>>>> m_daSQL.Fill(m_dsSQL) >>>>> lRow = 0 >>>>> For Each aRow In m_dsSQL.Tables(0).Rows() >>>>> m_dsSQL.Tables(0).Rows(lRow).Item("colA") = lrow --> this >>>>> updates the value in memory, but not in the database >>>>> lRow = lRow + 1 >>>>> Next >>>>> >>>>> >>>> >>>> >>> >>> >> >> > > Fniles,
In addition to Scott, You can in simple situations like this as well use the commandbuilder, that makes dynamicly the Insert, the Update and Delete commands for you. dim cmd as New SqlClient.Commandbuilder(m_daSQL) (_daSQL.Update(m_dsSQL) (Like Scott I don't see the use for the m_. This has only sense to distinct a variable private member where that would have the same name as a property) I hope this helps, Cor Show quoteHide quote "fniles" <fni***@pfmail.com> schreef in bericht news:uHVbakwHHHA.3312@TK2MSFTNGP03.phx.gbl... > Thank you. > I am sorry, I am still confused. > In my example, I Fill the dataset using CommandText "select * from tblA > where [colB] = 'abc'", before I update it. > If I need to use InsertCommand, can I use the same DataAdapter/DataSet ? > Thanks > > m_cmdSQL = New SqlClient.SqlCommand > With m_cmdSQL > .Connection = adoCon > .CommandText = sSQL > End With > m_daSQL = New SqlClient.SqlDataAdapter > m_dsSQL = New DataSet > m_daSQL.SelectCommand = "select * from tblA where [colB] = 'abc'" > m_daSQL.Fill(m_dsSQL) > lRow = 0 > For Each aRow In m_dsSQL.Tables(0).Rows() > sSQL = "update tblA set" > sSQL = sSQL & (" colA = " & lrow) > sSQL = sSQL & (" where [colB] = 'abc') > m_daSQL.Update ---> how can I use the same m_daSQL that is already filled > with the "select * from tblA where [colB] = 'abc'" ? > lRow = lRow + 1 > Next > > "Scott M." <s-mar@nospam.nospam> wrote in message > news:%23pT%23oZwHHHA.1248@TK2MSFTNGP03.phx.gbl... >> You'll need to create/configure the dataadapter's InsertCommand and its >> CommandText property with your own update logic. Once you've done that >> you simply add: >> >> m_daSQL.Update >> >> to your code. >> >> "fniles" <fni***@pfmail.com> wrote in message >> news:%23ZJe9TwHHHA.2112@TK2MSFTNGP03.phx.gbl... >>>I am using VB.NET 2003, SQL 2000, and SqlDataAdapter. >>> For every record in tblA where colB = 'abc', I want to update the value >>> in colA. >>> In VB6, using ADO I can loop thru the recordset,set the values of colA >>> and call the Update method. >>> How can I do this in VB.NET and SqlDataAdapter ? Thank you. >>> >>> m_cmdSQL = New SqlClient.SqlCommand >>> With m_cmdSQL >>> .Connection = adoCon >>> .CommandText = sSQL >>> End With >>> m_daSQL = New SqlClient.SqlDataAdapter >>> m_dsSQL = New DataSet >>> m_daSQL.SelectCommand = "select * from tblA where [colB] = 'abc'" >>> m_daSQL.Fill(m_dsSQL) >>> lRow = 0 >>> For Each aRow In m_dsSQL.Tables(0).Rows() >>> m_dsSQL.Tables(0).Rows(lRow).Item("colA") = lrow --> this >>> updates the value in memory, but not in the database >>> lRow = lRow + 1 >>> Next >>> >>> >> >> > > "fniles" <fni***@pfmail.com> wrote in Why don't you write a SQL statement:news:#ZJe9TwHHHA.2112@TK2MSFTNGP03.phx.gbl: > For every record in tblA where colB = 'abc', I want to update the > value in colA. Update tblA set colA = 'SomeValue' where colB = 'abc' fniles wrote:
<back posted/> For the specific update you want to perform (setting each colA to a distinct, zero-based index for all rows where colB = 'abc') , you don't even need to fetch the records from the DB; the following would suffice: <aircode> Dim SQL As String = "declare @val int; " _ & "set @val = -1; " _ & "update tblA Set @val = colA = @val + 1 where colB = 'abc'" 'Assuming a connection Con exists and is already open Dim Cmd As New SqlClient.SqlCommand(SQL, Con) Dim Count As Integer = Cmd.ExecuteNonQuery() </aircode> Now, if you want to use a more generic approach, then you must set up an update command, as pointed out by Scott. The update command is a SQL string that would update a specific row given a set of new values, passed as parameters. For instance, it could be: "Update tblA set colA = @colA where tblAID = @tblAID" As you can see, you'd need a way to inequivocally specify the row you need to update, probably using a primary key (named tblAID, here). I'm not sure if you can use a different set of columns than the ones used by the select query, I guess you'll have to do some testings. Just to give you a headstart in the matter, the following would perform the update using this approach: <aircode> 'Assuming an open connection Con Dim Adapter As New SqlClient.SqlDataAdapter 'the SELECT cmd Dim Cmd As New SqlClient.SqlCommand( _ "select tblAID, colA from tblA where colB = 'abc'", _ Con) Adapter.SelectCommand = Cmd 'the UPDATE cmd Cmd = New SqlClient.SqlCommand( _ "update tblA set colA=@colA where tblAID=@tblAID", _ Con) Cmd.Parameters.Add("@colA", SqlDbType.Int, 5, "colA") Cmd.Parameters.Add("@tblAID", SqlDbType.Int, 5, "tblAID") Adapter.UpdateCommand = Cmd 'retrieves the data Dim Ds As New DataSet Adapter.Fill(Ds) 'modify rows Dim Index As Integer = 0 For Each Row As DataRow In Ds.Tables(0).Rows Row("colA") = Index Index += 1 Next 'update the source table Adapter.Update(Ds) </aircode> HTH. Regards, Branco. Show quoteHide quote > I am using VB.NET 2003, SQL 2000, and SqlDataAdapter. > For every record in tblA where colB = 'abc', I want to update the value in > colA. > In VB6, using ADO I can loop thru the recordset,set the values of colA and > call the Update method. > How can I do this in VB.NET and SqlDataAdapter ? Thank you. > > m_cmdSQL = New SqlClient.SqlCommand > With m_cmdSQL > .Connection = adoCon > .CommandText = sSQL > End With > m_daSQL = New SqlClient.SqlDataAdapter > m_dsSQL = New DataSet > m_daSQL.SelectCommand = "select * from tblA where [colB] = 'abc'" > m_daSQL.Fill(m_dsSQL) > lRow = 0 > For Each aRow In m_dsSQL.Tables(0).Rows() > m_dsSQL.Tables(0).Rows(lRow).Item("colA") = lrow --> this > updates the value in memory, but not in the database > lRow = lRow + 1 > Next Thank you.
In your more generic approach, is this the correct way to set the value for tblAID ? Cmd = New SqlClient.SqlCommand( "update tblA set colA=@colA where tblAID=@tblAID", Con) Cmd.Parameters.Add("@colA", SqlDbType.Int, 5, "colA") Cmd.Parameters.Add("@tblAID", SqlDbType.Int, 5, "tblAID").VALUE = MyID Adapter.UpdateCommand = Cmd m_UpdateCmdSQL.Parameters.Add("@" & sParameterName, SqlDbType.VarChar, sParameterSize).Value = sParameter Show quoteHide quote "Branco Medeiros" <branco.medei***@gmail.com> wrote in message news:1166066293.448168.176570@t46g2000cwa.googlegroups.com... > fniles wrote: > <back posted/> > > For the specific update you want to perform (setting each colA to a > distinct, zero-based index for all rows where colB = 'abc') , you don't > even need to fetch the records from the DB; the following would > suffice: > > <aircode> > Dim SQL As String = "declare @val int; " _ > & "set @val = -1; " _ > & "update tblA Set @val = colA = @val + 1 where colB = 'abc'" > > 'Assuming a connection Con exists and is already open > Dim Cmd As New SqlClient.SqlCommand(SQL, Con) > Dim Count As Integer = Cmd.ExecuteNonQuery() > </aircode> > > Now, if you want to use a more generic approach, then you must set up > an update command, as pointed out by Scott. > > The update command is a SQL string that would update a specific row > given a set of new values, passed as parameters. For instance, it could > be: > > "Update tblA set colA = @colA where tblAID = @tblAID" > > As you can see, you'd need a way to inequivocally specify the row you > need to update, probably using a primary key (named tblAID, here). > > I'm not sure if you can use a different set of columns than the ones > used by the select query, I guess you'll have to do some testings. > > Just to give you a headstart in the matter, the following would perform > the update using this approach: > > <aircode> > 'Assuming an open connection Con > > Dim Adapter As New SqlClient.SqlDataAdapter > > 'the SELECT cmd > Dim Cmd As New SqlClient.SqlCommand( _ > "select tblAID, colA from tblA where colB = 'abc'", _ > Con) > Adapter.SelectCommand = Cmd > > 'the UPDATE cmd > Cmd = New SqlClient.SqlCommand( _ > "update tblA set colA=@colA where tblAID=@tblAID", _ > Con) > Cmd.Parameters.Add("@colA", SqlDbType.Int, 5, "colA") > Cmd.Parameters.Add("@tblAID", SqlDbType.Int, 5, "tblAID") > Adapter.UpdateCommand = Cmd > > 'retrieves the data > Dim Ds As New DataSet > Adapter.Fill(Ds) > > 'modify rows > Dim Index As Integer = 0 > For Each Row As DataRow In Ds.Tables(0).Rows > Row("colA") = Index > Index += 1 > Next > > 'update the source table > Adapter.Update(Ds) > </aircode> > > HTH. > > Regards, > > Branco. > > >> I am using VB.NET 2003, SQL 2000, and SqlDataAdapter. >> For every record in tblA where colB = 'abc', I want to update the value >> in >> colA. >> In VB6, using ADO I can loop thru the recordset,set the values of colA >> and >> call the Update method. >> How can I do this in VB.NET and SqlDataAdapter ? Thank you. >> >> m_cmdSQL = New SqlClient.SqlCommand >> With m_cmdSQL >> .Connection = adoCon >> .CommandText = sSQL >> End With >> m_daSQL = New SqlClient.SqlDataAdapter >> m_dsSQL = New DataSet >> m_daSQL.SelectCommand = "select * from tblA where [colB] = 'abc'" >> m_daSQL.Fill(m_dsSQL) >> lRow = 0 >> For Each aRow In m_dsSQL.Tables(0).Rows() >> m_dsSQL.Tables(0).Rows(lRow).Item("colA") = lrow --> this >> updates the value in memory, but not in the database >> lRow = lRow + 1 >> Next > fniles wrote:
> Thank you. Nope. You don't need to set the parameters values, the DataAdapter will> In your more generic approach, is this the correct way to set the value for > tblAID ? > Cmd = New SqlClient.SqlCommand( "update tblA set colA=@colA where > tblAID=@tblAID", Con) > Cmd.Parameters.Add("@colA", SqlDbType.Int, 5, "colA") > Cmd.Parameters.Add("@tblAID", SqlDbType.Int, 5, "tblAID").VALUE = MyID > Adapter.UpdateCommand = Cmd > > m_UpdateCmdSQL.Parameters.Add("@" & sParameterName, SqlDbType.VarChar, > sParameterSize).Value = sParameter do that for you for each modified row when you call the Update() method. I'm not sure if I understand you correctly. It seems you want to update also the field used as ID. If this is the case, then you must indicate so in the update string. Notice, however, that then you must request the filter (the "where" part) to use *the original ID value* when looking for the row: <aircode> 'Specify the original field value as key Cmd = New SqlClient.SqlCommand( _ "update tblA set colA=@colA, tblAID=@tblAID " _ & "where tblAID=@OriginalID", _ Con) Cmd.Parameters.Add("@colA", SqlDbType.Int, 5, "colA") Cmd.Parameters.Add("@tblAID", SqlDbType.Int, 5, "tblAID") 'uses the original value for the field Cmd.Parameters.Add( _ "@OriginalID", SqlDbType.Int, 5, "tblAID" _ ).SourceVersion = DataRowVersion.Original </aircode> HTH. Regards, Branco. Thank you.
Actually, this is what I am trying to do: Say I have a table Position like the following: Account MasterAccount Cash Profit 123 999 1000 10 345 999 2000 20 999 I am looping thru this table looking for MasterAccount that is not blank (in this case Account 123 and 345). For each of those records, I want to update cash for MasterAccount with cash from this Account. In the example, I want to update cash in Account 999 with cash from Account 123 and Account 345. The result will be for Account 999, cash = 3000. Because of this, I need to supply the Parameter value for MasterAccount and Account inside the loop. Is this correct or I don't have to set this Parameter values ? Also, is it correct in that my case I want to call the Update method inside the loop ? Thank you. sSQL = "update Position set cash = @Cash where masteraccount = @MasterAccount and Account = @Account" cmdSQL = New SqlClient.SqlCommand With cmdSQL .Connection = adoCon .CommandText = "select * from position where [masteraccount] <> ''" End With daSQL = New SqlClient.SqlDataAdapter dsSQL = New DataSet daSQL.SelectCommand = cmdSQL daSQL.Fill(dsSQL) 'Update Command UpdateCmdSQL = New SqlClient.SqlCommand UpdateCmdSQL.CommandText = sUpdateSQL UpdateCmdSQL.Connection = adoCon daSQL.UpdateCommand = UpdateCmdSQL UpdateCmdSQL.Parameters.Add("@Cash", SqlDbType.Money, 8, "Cash") UpdateCmdSQL.Parameters.Add("@MasterAccount", SqlDbType.VarChar, 50, "MasterAccount") UpdateCmdSQL.Parameters.Add("@Account", SqlDbType.VarChar, 10, "Account") lRow = 0 For Each aRow In rsMaster.Rows MasterAccount = dsSQL.Tables(0).Rows(lRow).Item("MasterAccount") Account = dsSQL.Tables(0).Rows(lRow).Item("account") 'Read Cash and profit from MasterAccount cmdSQLRead = New SqlClient.SqlCommand With cmdSQLRead .Connection = adoCon .CommandText = "select * from position where ACCOUNT = '" & MasterAccount & "'" End With drSQL = cmdSQLRead.ExecuteReader() Do While drSQL.Read dsSQL.Tables(0).Rows(lRow).Item("Cash") = dsSQL.Tables(0).Rows(lRow).Item("Cash") + drSQL.Item("cash") Loop '----------------------------DO I need the next 2 LINE OF CODES HERE ? ------------------------------------ UpdateCmdSQL.Parameters("@MasterAccount").Value = MasterAccount UpdateCmdSQL.Parameters("@Account").Value = Account '------------------------------------------------------------------------------------------------------------------- daSQL.Update(dsSQL) '-----> IS THIS CORRECT TO CALL the UPDATE method INSIDE the FOR LOOP ? lRow = lRow + 1 Next Show quoteHide quote "Branco Medeiros" <branco.medei***@gmail.com> wrote in message news:1166115202.619696.297280@f1g2000cwa.googlegroups.com... > fniles wrote: >> Thank you. >> In your more generic approach, is this the correct way to set the value >> for >> tblAID ? >> Cmd = New SqlClient.SqlCommand( "update tblA set colA=@colA where >> tblAID=@tblAID", Con) >> Cmd.Parameters.Add("@colA", SqlDbType.Int, 5, "colA") >> Cmd.Parameters.Add("@tblAID", SqlDbType.Int, 5, "tblAID").VALUE = MyID >> Adapter.UpdateCommand = Cmd >> >> m_UpdateCmdSQL.Parameters.Add("@" & sParameterName, SqlDbType.VarChar, >> sParameterSize).Value = sParameter > > Nope. You don't need to set the parameters values, the DataAdapter will > do that for you for each modified row when you call the Update() > method. > > I'm not sure if I understand you correctly. It seems you want to update > also the field used as ID. If this is the case, then you must indicate > so in the update string. Notice, however, that then you must request > the filter (the "where" part) to use *the original ID value* when > looking for the row: > > <aircode> > 'Specify the original field value as key > Cmd = New SqlClient.SqlCommand( _ > "update tblA set colA=@colA, tblAID=@tblAID " _ > & "where tblAID=@OriginalID", _ > Con) > > Cmd.Parameters.Add("@colA", SqlDbType.Int, 5, "colA") > Cmd.Parameters.Add("@tblAID", SqlDbType.Int, 5, "tblAID") > > 'uses the original value for the field > Cmd.Parameters.Add( _ > "@OriginalID", SqlDbType.Int, 5, "tblAID" _ > ).SourceVersion = DataRowVersion.Original > </aircode> > > HTH. > > Regards, > > Branco. > fniles wrote:
<snip> > Actually, this is what I am trying to do: <snip>> Say I have a table Position like the following: > Account MasterAccount Cash Profit > 123 999 1000 10 > 345 999 2000 20 > 999 > I am looping thru this table looking for MasterAccount that is not blank (in > this case Account 123 and 345). For each of those records, I want to update > cash for MasterAccount with cash from this Account. In the example, I want > to update cash in Account 999 with cash from Account 123 and Account 345. > The result will be for Account 999, cash = 3000. If this is the case, SQL is your friend (err... mostly): the following commands will update the master accounts for you (no need looping): <aircode> Dim SQL As String = "update Position " _ & "set Cash = Cash + b.Total " _ & "from Position as a " _ & "join ( " _ & "select MasterAccount, Total = Sum(Cash) " _ & " from Position where MasterAccount is not null " _ & " group by MasterAccount) as b " _ & "on a.Account = b.MasterAccount" 'Assuming an open connection in Con Dim Cmd As New SqlClient.SqlCommand(SQL, Con) Dim Count As Integer = Cmd.ExecuteNonQuery() </aircode> As with the majority of the code posted in foruns, don't run this on your production data =) HTH. Regards, Branco. Branco Medeiros wrote:
> As with the majority of the code posted in foruns, don't run this on I mean, not before you test it first, of course.> your production data =) B. Thank you again.
For future reference, if I want to do a similar thing from a program (not SQL), do I need the following 2 statements inside the FOR loop ? UpdateCmdSQL.Parameters("@MasterAccount").Value = MasterAccount UpdateCmdSQL.Parameters("@Account").Value = Account Also, I should call the Update method from outside the loop, shouldn't I ? Show quoteHide quote "Branco Medeiros" <branco.medei***@gmail.com> wrote in message news:1166128340.322665.24630@73g2000cwn.googlegroups.com... > fniles wrote: > <snip> >> Actually, this is what I am trying to do: >> Say I have a table Position like the following: >> Account MasterAccount Cash Profit >> 123 999 1000 10 >> 345 999 2000 20 >> 999 >> I am looping thru this table looking for MasterAccount that is not blank >> (in >> this case Account 123 and 345). For each of those records, I want to >> update >> cash for MasterAccount with cash from this Account. In the example, I >> want >> to update cash in Account 999 with cash from Account 123 and Account 345. >> The result will be for Account 999, cash = 3000. > <snip> > > If this is the case, SQL is your friend (err... mostly): the following > commands will update the master accounts for you (no need looping): > > <aircode> > Dim SQL As String = "update Position " _ > & "set Cash = Cash + b.Total " _ > & "from Position as a " _ > & "join ( " _ > & "select MasterAccount, Total = Sum(Cash) " _ > & " from Position where MasterAccount is not null " _ > & " group by MasterAccount) as b " _ > & "on a.Account = b.MasterAccount" > > 'Assuming an open connection in Con > Dim Cmd As New SqlClient.SqlCommand(SQL, Con) > Dim Count As Integer = Cmd.ExecuteNonQuery() > </aircode> > > As with the majority of the code posted in foruns, don't run this on > your production data =) > > HTH. > > Regards, > > Branco. > I think you have a fundamental misunderstanding of what's going on here.
You would only use a loop if you wanted to iterate over the rows in a DataSet. But, as you are iterating over the rows of a DataSet, you aren't concerned with update statements or command objects or parameters, you simply make manual changes to the columns of the row in the dataset that need new values. The DataAdapter does the "actual" database updating for you, but it must know how you want the update done, so you build an UpdateCommand and configure the command (with or without parameters as needed), so that when you call the Update() method of the DataAdapter, it knows how to take the manual changes you made to your DataSet and update the actual data in the database. Show quoteHide quote "fniles" <fni***@pfmail.com> wrote in message news:umi17v8HHHA.3312@TK2MSFTNGP03.phx.gbl... > Thank you again. > For future reference, if I want to do a similar thing from a program (not > SQL), > do I need the following 2 statements inside the FOR loop ? > UpdateCmdSQL.Parameters("@MasterAccount").Value = MasterAccount > UpdateCmdSQL.Parameters("@Account").Value = Account > > Also, I should call the Update method from outside the loop, shouldn't I ? > > "Branco Medeiros" <branco.medei***@gmail.com> wrote in message > news:1166128340.322665.24630@73g2000cwn.googlegroups.com... >> fniles wrote: >> <snip> >>> Actually, this is what I am trying to do: >>> Say I have a table Position like the following: >>> Account MasterAccount Cash Profit >>> 123 999 1000 10 >>> 345 999 2000 20 >>> 999 >>> I am looping thru this table looking for MasterAccount that is not blank >>> (in >>> this case Account 123 and 345). For each of those records, I want to >>> update >>> cash for MasterAccount with cash from this Account. In the example, I >>> want >>> to update cash in Account 999 with cash from Account 123 and Account >>> 345. >>> The result will be for Account 999, cash = 3000. >> <snip> >> >> If this is the case, SQL is your friend (err... mostly): the following >> commands will update the master accounts for you (no need looping): >> >> <aircode> >> Dim SQL As String = "update Position " _ >> & "set Cash = Cash + b.Total " _ >> & "from Position as a " _ >> & "join ( " _ >> & "select MasterAccount, Total = Sum(Cash) " _ >> & " from Position where MasterAccount is not null " _ >> & " group by MasterAccount) as b " _ >> & "on a.Account = b.MasterAccount" >> >> 'Assuming an open connection in Con >> Dim Cmd As New SqlClient.SqlCommand(SQL, Con) >> Dim Count As Integer = Cmd.ExecuteNonQuery() >> </aircode> >> >> As with the majority of the code posted in foruns, don't run this on >> your production data =) >> >> HTH. >> >> Regards, >> >> Branco. >> > > 
Other interesting topics
System.Threading.Timer does not tick
Q: DataColumn Expressions There is already an open DataReader associated with this Connection which must be closed first What is WebBrowser control called now? The below snippet does not work...anyone know how to reference a procedure? Looping through all Tables in a Database Form1.closing in VB2005??? Datagrid problem Getting an Object Properties value... Q: DataView with Table with large number of rows |
|||||||||||||||||||||||
