I have the following code:
Imports System.Data
Imports System.Data.OleDb
Partial Class Dummy
Inherits System.Web.UI.Page
Dim r As OleDbDataReader
Dim con As OleDb.OleDbConnection
Dim cmd As OleDbCommand
Dim cmd1 As OleDbCommand
Dim prev_ob As New List(Of Int64)
Dim cur_ob As Integer
Dim i As Integer = 0
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
con = New OleDb.OleDbConnection("provider=SQLOLEDB;data source=PC;initial catalog=DB1;integrated security=SSPI")
cmd = New OleDbCommand("select single_column from table1 where date_reqd=(SELECT CONVERT(VARCHAR(10),DATEADD(DD, DATEDIFF(DD, 0, GETDATE()), -1),120))", con)
con.Open()
r = cmd.ExecuteReader
While r.Read
prev_ob.Add(Val(r.Item(0)))
End While
cmd = New OleDbCommand("select column1, column2, date_reqd from table1 where date_reqd=(select CONVERT(varchar(10), GETDATE(),120))", con)
r = cmd.ExecuteReader
While r.Read
For i As Integer = 0 To prev_ob.Count 开发者_Python百科- 1
cur_ob = Val(prev_ob(i)) + Val(r.Item(0))
cmd1 = New OleDbCommand("update table1 set column4='" & cur_ob & "' where column2='" & r.Item(1) & "' and date_reqd='" & r.Item(2) & "'", con)
cmd1.ExecuteNonQuery()
i += 1
Exit For
Next
End While
con.Close()
End Sub
End Class
The problem I'm facing is that the update happens correctly only for the first of many values. All the other values are calculated and consequently, updated incorrectly in my table. I am almost certain that the looping is what is causing the problem but have been unable to find a way around it. Please help me correct it.
It looks like this should be a single UPDATE statement. Unfortunately, it's tricky to tell without seeing your actual table structure. First, write a select statement like this (I'm hoping date_reqd is actually a datetime column also):
SELECT
*
FROM
table1 t1a
inner join
table1 t1b
on
t1a.date_reqd = DATEADD(day,DATEDIFF(day,0,CURRENT_TIMESTAMP),-1) and
t1b.date_reqd = DATEADD(day,DATEDIFF(day,0,CURRENT_TIMESTAMP),0) and
/* You need other conditions here if there are multiple rows for the same dates
- I'm guessing there are since you're trying to write a loop */
Once you have this query working, remove the first two lines (SELECT *
), and replace them with:
UPDATE
t1b
SET
column4 = t1a.single_column + t1b.column1
And you should be done.
I got it myself! :) All I needed to do was:
Dim i as Integer=0
While r.Read
cur_ob = Val(prev_ob(i)) + Val(r.Item(0))
cmd1 = New OleDbCommand("update table1 set column4='" & cur_ob & "' where column2='" & r.Item(1) & "' and date_reqd='" & r.Item(2) & "'", con)
cmd1.ExecuteNonQuery()
cur_ob = 0
i += 1
End While
And it worked! Thanks for your answers/comments and @Damien_The_Unbeliever, I changed my primary queries to include an ORDER BY
clause. Thanks for the tip! :)
精彩评论