Private Sub cmd2_Click()
Dim s As String
Dim p As String
Inet1.Cancel
Inet1.protocol = icHTTP
Inet1.url = "http://money.finance.sina.com.cn/corp/go.php/vFD_BalanceSheet/stockid/600827/ctrl/part/displaytype/4.phtml"
s = ""
s = Inet1.OpenURL
'Debug.Print s
Dim i As Long, j As Long
Dim s1 As String, s2 As String
Dim hb As Double, fz As Double, zgb As Double
i = InStr(s, "货币资金")
s1 = Right(s, Len(s) - i - 16)
j = InStr(s1, "</td><td>")
hb = Left(s1, j - 1)
i = InStr(s, "长期负债合计")
s1 = Right(s, Len(s) - i - 18)
j = InStr(s1, "</td><td>")
fz = Left(s1, j - 1)
i = InStr(s, "股本净额")
s1 = Right(s, Len(s) - i - 16)
j = InStr(s1, "</td><td>")
zgb = Left(s1, j - 1)
Dim sql As String
Dim Conn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\stock.mdb;Persist Security Info=False;"
Adodc1.ConnectionString = "provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\stock.mdb"
Adodc1.RecordSource = "select * from 股票"
Adodc1.Refresh
sql = "update 股票 set [货币资金]=" & hb & ",[长期负债]=" & fz & ",[总股本]=" & zgb & " where [代码]='000010' "
Conn.Execute sql
Adodc1.Recordset.Fields("货币资金") = hb
Adodc1.Recordset.Fields("长期负债") = fz
Adodc1.Recordset.Fields("总股本") = zgb
Adodc1.Recordset.Update
Call Cmd3_Click
End SubPrivate Sub Cmd3_Click()
Form1.Refresh
Adodc1.Refresh
Set Grid.DataSource = Adodc1
End Sub
我用上面的代码绑定GRID ,想实现数据的即时更新,但是有一个问题,就是每次第一行也会出现相同的数据怎么办啊,请高手解答下。
注释掉所有adodb语句,也就是conn部分然后在Adodc1.Recordset.Update下面加
adodc1.Adodc1.Refresh试试。
sql = "update 股票 set [货币资金]=" & hb & ",[长期负债]=" & fz & ",[总股本]=" & zgb & " where [代码]='000010' "
Conn.Execute sql
注释掉后,我上面这句代码用不起来了啊,那我要实现的功能就实现不了了
Adodc1.Recordset.Fields("货币资金") = hb
Adodc1.Recordset.Fields("长期负债") = fz
Adodc1.Recordset.Fields("总股本") = zgb绑定后,实时刷新了,但是第一行也会出现相同的数据。
Adodc1.RecordSource = "select * from 股票"
Adodc1.Refresh
sql = "update 股票 set [货币资金]=" & hb & ",[长期负债]=" & fz & ",[总股本]=" & zgb & " where [代码]='000010' "
Conn.Execute sql
Adodc1.Recordset.Fields("货币资金") = hb
Adodc1.Recordset.Fields("长期负债") = fz
Adodc1.Recordset.Fields("总股本") = zgb
Adodc1.Recordset.Update很显然conn.execute的时候会把[代码]='000010'的记录的[货币资金]、[长期负债]、[总股本]三个字段的内容给修改了。
Adodc1.Update的时候还会再次把Adodc查询出来的第一条记录的[货币资金]、[长期负债]、[总股本]的内容给修改了。
所以第一行会出现相同的数据。
Dim s As String
Dim p As String
Inet1.Cancel
Inet1.protocol = icHTTP
Inet1.url = "http://money.finance.sina.com.cn/corp/go.php/vFD_BalanceSheet/stockid/600827/ctrl/part/displaytype/4.phtml"
s = ""
s = Inet1.OpenURL
'Debug.Print s
Dim i As Long, j As Long
Dim s1 As String, s2 As String
Dim hb As Double, fz As Double, zgb As Double
i = InStr(s, "货币资金")
s1 = Right(s, Len(s) - i - 16)
j = InStr(s1, "</td><td>")
hb = Left(s1, j - 1)
i = InStr(s, "长期负债合计")
s1 = Right(s, Len(s) - i - 18)
j = InStr(s1, "</td><td>")
fz = Left(s1, j - 1)
i = InStr(s, "股本净额")
s1 = Right(s, Len(s) - i - 16)
j = InStr(s1, "</td><td>")
zgb = Left(s1, j - 1)
Dim sql As String
Dim Conn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\stock.mdb;Persist Security Info=False;"
sql = "update 股票 set [货币资金]=" & hb & ",[长期负债]=" & fz & ",[总股本]=" & zgb & " where [代码]='000010' "
Conn.Execute sqldim tmprow as long
for tmprow=1 to Grid.Rows-1
if Grid.textmatrix(tmprow,a)="000010" then 'a为[代码]字段对应的列索引
Grid.textmatrix(tmprow,b)=hb 'b为[货币资金]字段对应的列索引
Grid.textmatrix(tmprow,c)=fz 'c为[长期负债]字段对应的列索引
Grid.textmatrix(tmprow,d)=zgb 'd为[总股本]字段对应的列索引
end if
next tmprow
End Sub
更新了数据库以后直接修改Grid表格里的数据,不用去查数据库,效率会更高一点。a,b,c,d的值你用Grid里对应列的索引号替代。
Dim sql As String
Dim Conn As New ADODB.Connection
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\stock.mdb;Persist Security Info=False;"
sql = "Update 股票 set 净现金价值总= (货币资金-长期负债)/总股本 where 总股本>0 "
Conn.Execute sql
Call Cmd3_Click
End Sub
Private Sub cmd2_Click()
Dim s As String
Dim p As String
Inet1.Cancel
Inet1.protocol = icHTTP
Inet1.url = "http://money.finance.sina.com.cn/corp/go.php/vFD_BalanceSheet/stockid/600827/ctrl/part/displaytype/4.phtml"
s = ""
s = Inet1.OpenURL
'Debug.Print s
Dim i As Long, j As Long
Dim s1 As String, s2 As String
Dim hb As Double, fz As Double, zgb As Double
i = InStr(s, "货币资金")
s1 = Right(s, Len(s) - i - 16)
j = InStr(s1, "</td><td>")
hb = Left(s1, j - 1)
i = InStr(s, "长期负债合计")
s1 = Right(s, Len(s) - i - 18)
j = InStr(s1, "</td><td>")
fz = Left(s1, j - 1)
i = InStr(s, "股本净额")
s1 = Right(s, Len(s) - i - 16)
j = InStr(s1, "</td><td>")
zgb = Left(s1, j - 1)
Dim sql As String
Dim Conn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\stock.mdb;Persist Security Info=False;"
Adodc1.ConnectionString = "provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\stock.mdb"
Adodc1.RecordSource = "select * from 股票"
Adodc1.Refresh
sql = "update 股票 set [货币资金]=" & hb & ",[长期负债]=" & fz & ",[总股本]=" & zgb & " where [代码]='000010' "
Conn.Execute sql
Adodc1.Recordset.Fields("货币资金") = hb
Adodc1.Recordset.Fields("长期负债") = fz
Adodc1.Recordset.Fields("总股本") = zgb
Adodc1.Recordset.Update
Call Cmd3_Click
End SubPrivate Sub Cmd3_Click()
Form1.Refresh
Adodc1.Refresh
Set Grid.DataSource = Adodc1
End Sub怎么办啊,还有我更新的数据很多,有几千条,这样是修改GRID效率高,还是直接刷新数据库效率高啊
sql = "update 股票 set [货币资金]=" & hb & ",[长期负债]=" & fz & ",[总股本]=" & zgb & ",净现金价值总="& (hb-fz)/zgb &" where [代码]='000010' "
else
sql = "update 股票 set [货币资金]=" & hb & ",[长期负债]=" & fz & ",[总股本]=" & zgb & " where [代码]='000010' "
end if
Conn.Execute sqldim tmprow as long
for tmprow=1 to Grid.Rows-1
if Grid.textmatrix(tmprow,a)="000010" then 'a为[代码]字段对应的列索引
Grid.textmatrix(tmprow,b)=hb 'b为[货币资金]字段对应的列索引
Grid.textmatrix(tmprow,c)=fz 'c为[长期负债]字段对应的列索引
Grid.textmatrix(tmprow,d)=zgb 'd为[总股本]字段对应的列索引 if zgb<>0 then
Grid.textmatrix(tmprow,e)=(hb-fz)/zgb 'e为对应[净现金价值总]字段的索引
end if
end if
next tmprow
不就实现计算了,真笨!
adodc能刷新是因为你在Cmd3_Click()里做了,
adodb不能刷新是因为你没做,你可以如下试试,一样能刷新:
set rs = Conn.Execute(sql)
Set Grid.DataSource = rs用其中一个就好,不要把一个简单的问题复杂化…………
Dim sql As String
Dim Conn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Conn.CursorLocation = adUseClient'你要用客户端游标才行,加上这句试试
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\stock.mdb;Persist Security Info=False;"
Dim Conn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Conn.CursorLocation = adUseClient '你要用客户端游标才行,加上这句试试
If Conn.State = 1 Then cnn.Close
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\stock.mdb;Persist Security Info=False;"
sql = "update 股票 set [货币资金]=" & hb & ",[长期负债]=" & fz & ",[总股本]=" & zgb & " where [代码]='000010' "
If rst.State = 1 Then rst.Close
rs.Open mySql, cnn, adOpenStatic, adLockPessimistic
Set Grid.DataSource = rs