怎樣把以下程序段的1變成i,
i的范圍是1到4;sql1 = "select *from Material where MaterialSkuno='" & MaterialSkuno1.Text & " '"
Set rs = TransactSQL(sql1)
If iflag = 1 Then
If rs.EOF = True Then
Else
num1 = rs.Fields("GoodMaterial")
sql1 = "Update Material set GoodMaterial='" & num1 + CInt(MaterialCount1.Text) & "',UpdateDate='" & Now & "' where MaterialSkuno='" & MaterialSkuno1.Text & " '"
TransactSQL (sql1)
End If
End If即需求循環執行4次;
i的范圍是1到4;sql1 = "select *from Material where MaterialSkuno='" & MaterialSkuno1.Text & " '"
Set rs = TransactSQL(sql1)
If iflag = 1 Then
If rs.EOF = True Then
Else
num1 = rs.Fields("GoodMaterial")
sql1 = "Update Material set GoodMaterial='" & num1 + CInt(MaterialCount1.Text) & "',UpdateDate='" & Now & "' where MaterialSkuno='" & MaterialSkuno1.Text & " '"
TransactSQL (sql1)
End If
End If即需求循環執行4次;
以下代碼繁瑣,
浪費系統資源;
Dim rs As ADODB.Recordset
Dim sql1 As String
Dim sql2 As String
Dim sql3 As String
Dim sql4 As String
Dim num1 As Integer '保存料號1原良品數
Dim num2 As Integer '保存料號2原良品數
Dim num3 As Integer '保存料號3原良品數
Dim num4 As Integer '保存料號4原良品數 sql1 = "select *from Material where MaterialSkuno='" & MaterialSkuno1.Text & " '"
Set rs = TransactSQL(sql1)
If iflag = 1 Then
If rs.EOF = True Then
Else
num1 = rs.Fields("GoodMaterial")
sql1 = "Update Material set GoodMaterial='" & num1 + CInt(MaterialCount1.Text) & "',UpdateDate='" & Now & "' where MaterialSkuno='" & MaterialSkuno1.Text & " '"
TransactSQL (sql1)
End If
End If
sql2 = "select *from Material where MaterialSkuno='" & MaterialSkuno2.Text & " '"
Set rs = TransactSQL(sql2)
If iflag = 1 Then
If rs.EOF = True Then
Else
num2 = rs.Fields("GoodMaterial")
sql2 = "Update Material set GoodMaterial='" & num2 + CInt(MaterialCount2.Text) & "',UpdateDate='" & Now & "' where MaterialSkuno='" & MaterialSkuno2.Text & " '"
TransactSQL (sql2)
End If
End If
sql3 = "select *from Material where MaterialSkuno='" & MaterialSkuno3.Text & " '"
Set rs = TransactSQL(sql3)
If iflag = 1 Then
If rs.EOF = True Then
Else
num3 = rs.Fields("GoodMaterial")
sql3 = "Update Material set GoodMaterial='" & num3 + CInt(MaterialCount3.Text) & "',UpdateDate='" & Now & "' where MaterialSkuno='" & MaterialSkuno3.Text & " '"
TransactSQL (sql3)
End If
End If
sql4 = "select *from Material where MaterialSkuno='" & MaterialSkuno4.Text & " '"
Set rs = TransactSQL(sql4)
If iflag = 1 Then
If rs.EOF = True Then
Else
num4 = rs.Fields("GoodMaterial")
sql4 = "Update Material set GoodMaterial='" & num4 + CInt(MaterialCount4.Text) & "',UpdateDate='" & Now & "' where MaterialSkuno='" & MaterialSkuno4.Text & " '"
TransactSQL (sql4)
End If
End If
sub Trans(sql as string)
Dim rs As new ADODB.Recordset
Dim num1 As Integer '保存料號1原良品數
Dim sql1 As String
Set rs = TransactSQL(sql)
If iflag = 1 Then
If not rs.EOF Then
num1 = rs.Fields("GoodMaterial")
sql1 = "Update Material set GoodMaterial='" & num1 + CInt(MaterialCount1.Text) & "',UpdateDate='" & Now & "' where MaterialSkuno='" & MaterialSkuno1.Text & " '"
TransactSQL (sql1)
End If
End If
end sub'调用
s(0) = "select *from Material where MaterialSkuno='" & MaterialSkuno1.Text & " '"
s(1) = "select *from Material where MaterialSkuno='" & MaterialSkuno2.Text & " '"
s(2) = "select *from Material where MaterialSkuno='" & MaterialSkuno3.Text & " '"
s(3) = "select *from Material where MaterialSkuno='" & MaterialSkuno4.Text & " '"
for i=0 to 3
Trans s(i)
next
Dim a(3) As String, b(3) As String, c(3) As String
a(0) = "select *from Material where MaterialSkuno='" & MaterialSkuno1.Text & " '"
a(1) = "select *from Material where MaterialSkuno='" & MaterialSkuno2.Text & " '"
a(2) = "select *from Material where MaterialSkuno='" & MaterialSkuno3.Text & " '"
a(3) = "select *from Material where MaterialSkuno='" & MaterialSkuno4.Text & " '" b(0) = MaterialCount1.Text
b(1) = MaterialCount2.Text
b(2) = MaterialCount3.Text
b(3) = MaterialCount4.Text
c(0) = MaterialSkuno1.Text
c(1) = MaterialSkuno2.Text
c(2) = MaterialSkuno3.Text
c(3) = MaterialSkuno4.Text
For i = 0 To 3
Set rs = TransactSQL(a(i))
If Not rs.EOF Then
a(i) = "Update Material set GoodMaterial='" & rs.Fields("GoodMaterial") + CInt(b(i)) & "',UpdateDate='" & Now & "' where MaterialSkuno='" & c(i) & " '"
TransactSQL (a(i))
End If
End If
Next
Dim a(3) As String, b(3) As String, c(3) As String
b(0) = MaterialCount1.Text
b(1) = MaterialCount2.Text
b(2) = MaterialCount3.Text
b(3) = MaterialCount4.Text
c(0) = MaterialSkuno1.Text
c(1) = MaterialSkuno2.Text
c(2) = MaterialSkuno3.Text
c(3) = MaterialSkuno4.Text
For i = 0 To 3
If Not rs.EOF Then
a(i) = "Update Material set GoodMaterial= GoodMaterial + " & CInt(b(i)) & " ,UpdateDate='" & Now & "' where MaterialSkuno='" & c(i) & " '"
TransactSQL (a(i))
End If
End If
Next i
Dim i As Long
Dim a(3) As String, b(3) As String, c(3) As String
b(0) = MaterialCount1.Text
b(1) = MaterialCount2.Text
b(2) = MaterialCount3.Text
b(3) = MaterialCount4.Text
c(0) = MaterialSkuno1.Text
c(1) = MaterialSkuno2.Text
c(2) = MaterialSkuno3.Text
c(3) = MaterialSkuno4.Text
For i = 0 To 3
If Not rs.EOF Then
a(i) = "Update Material set GoodMaterial= cstr(cint(GoodMaterial) + " & CInt(b(i)) & ") ,UpdateDate='" & Now & "' where MaterialSkuno='" & c(i) & " '"
TransactSQL (a(i))
End If
End If
Next i
for i = 1 to 4
strText = controls("MaterialCount" & i).text
sql1 = "select *from Material where MaterialSkuno='" & strText & " '"
Set rs = TransactSQL(sql1)
If iflag = 1 Then
If rs.EOF = True Then
Else
num1 = rs.Fields("GoodMaterial")
sql1 = "Update Material set GoodMaterial='" & num1 + CInt(strText) & "',UpdateDate='" & Now & "' where MaterialSkuno='" & strText & " '"
TransactSQL (sql1)
End If
End If
next
Dim a(3) As String, b(3) As String, c(3) As String
b(0) = MaterialCount1.Text
b(1) = MaterialCount2.Text
b(2) = MaterialCount3.Text
b(3) = MaterialCount4.Text
c(0) = MaterialSkuno1.Text
c(1) = MaterialSkuno2.Text
c(2) = MaterialSkuno3.Text
c(3) = MaterialSkuno4.Text
For i = 0 To 3
a(i) = "Update Material set GoodMaterial= GoodMaterial + " & CInt(b(i)) & " ,UpdateDate='" & Now & "' where MaterialSkuno='" & c(i) & " '"
TransactSQL (a(i))
Next i
Dim i As Long
Dim a(3) As String, b(3) As String, c(3) As String
b(0) = MaterialCount1.Text
b(1) = MaterialCount2.Text
b(2) = MaterialCount3.Text
b(3) = MaterialCount4.Text
c(0) = MaterialSkuno1.Text
c(1) = MaterialSkuno2.Text
c(2) = MaterialSkuno3.Text
c(3) = MaterialSkuno4.Text
For i = 0 To 3
a(i) = "Update Material set GoodMaterial= cstr(cint(GoodMaterial) + " & CInt(b(i)) & ") ,UpdateDate='" & Now & "' where MaterialSkuno='" & c(i) & " '"
TransactSQL (a(i))
Next i
Dim ctlMaterialCount As Control
Dim rs As ADODB.Recordset
Dim num As Integer
Dim sql As String
Dim i As LongFor i = 1 To 4
Set ctlMaterialSkuno = Me.Controls("MaterialSkuno" & i)
Set ctlMaterialCount = Me.Controls("MaterialCount" & i)
sql = "select * from Material where MaterialSkuno='" & ctlMaterialSkuno.Text & " '"
If iflag = 1 Then
If Not rs.EOF Then
num = rs.Fields("GoodMaterial")
sql = "Update Material set GoodMaterial='" & num1 + CInt(ctlMaterialCount.Text) & "'" & _
",UpdateDate='" & Now & "' where MaterialSkuno='" & ctlMaterialSkuno.Text & " '"
TransactSQL (sql)
End If
End If
Next
Dim strMaterialSkuno As string
Dim strMaterialCount As string
For i = 0 To 3
strMaterialSkuno = Me.Controls("MaterialSkuno" & i).text
strMaterialCount = Me.Controls("MaterialCount" & i).text
a(i) = "Update Material set GoodMaterial= GoodMaterial + " & CInt(strMaterialCount) & " ,UpdateDate='" & Now & "' where MaterialSkuno='" & c(i) & " '"
TransactSQL (a(i))
Next i
Dim strMaterialSkuno As string
Dim strMaterialCount As string
dim strSql as string
For i = 1 To 4
strMaterialSkuno = Me.Controls("MaterialSkuno" & i).text
strMaterialCount = Me.Controls("MaterialCount" & i).text
strSql = "Update Material set GoodMaterial= GoodMaterial + " & CInt(strMaterialCount) & " ,UpdateDate='" & Now & "' where MaterialSkuno='" & strMaterialSkuno & " '"
TransactSQL (strSql)
Next i
Dim i As Long
Dim strMaterialSkuno As string
Dim strMaterialCount As string
dim strSql as string
For i = 1 To 4
strMaterialSkuno = Me.Controls("MaterialSkuno" & i).text
strMaterialCount = Me.Controls("MaterialCount" & i).text
strSql = "Update Material set GoodMaterial= cstr(cint(GoodMaterial) + " & CInt(strMaterialCount) & " ,UpdateDate='" & Now & "' where MaterialSkuno='" & strMaterialSkuno & " '"
TransactSQL (strSql)
Next i
Dim strMaterialSkuno As string
Dim strMaterialCount As string
dim strSql as string
For i = 1 To 4
strMaterialSkuno = Me.Controls("MaterialSkuno" & i).text
strMaterialCount = Me.Controls("MaterialCount" & i).text
strSql = "Update Material set GoodMaterial= cstr(cint(GoodMaterial) + " & CInt(strMaterialCount) & ") ,UpdateDate='" & Now & "' where MaterialSkuno='" & strMaterialSkuno & " '"
TransactSQL (strSql)
Next i
需要根據"MaterialSkuno"查詢得到;
Code as follow:Dim rs As ADODB.Recordset
Dim i As Integer
Dim S(3) As String, C(3) As String, sql(3) As String, num(3) As Integer
S(0) = MaterialSkuno1.Text
S(1) = MaterialSkuno2.Text
S(2) = MaterialSkuno3.Text
S(3) = MaterialSkuno4.Text
C(0) = MaterialCount1.Text
C(1) = MaterialCount2.Text
C(2) = MaterialCount3.Text
C(3) = MaterialCount4.Text
For i = 0 To 3
sql(i) = "select *from Material where MaterialSkuno='" & S(i) & "'"
Set rs = TransactSQL(sql(i))
If iflag = 1 Then
If Not rs.EOF Then
num(i) = rs.Fields("GoodMaterial")
sql(i) = "Update Material set GoodMaterial='" & num(i) + CInt(C(i)) & "',UpdateDate='" & Now & "' where MaterialSkuno='" & S(i) & " '"
TransactSQL (sql(i))
End If
End If
Next i
你的知识不够,理解不了,完全可以直接用updata完成,我在update中已经把你的查询加进去了
恩,做个函数也行,把MaterialSkuno当参数传进去