在我使用vb连接sql server 7,0的数据库weight
weight表有四个字段,model(型号)case_no(壳编号)material(材质)weight(单重)
model case_no material weight
00-110 C-4-946707F SS 0.0112
00-110 B-4-946707F SS 0.006
00-110 C-4-946961F SS 0.0112
00-110 B-4-946961F SS 0.006
00-110 C-4-S82922FT SS 0.0112
00-110 B-4-S82922FT SS 0.006
00-110 C-4-R10362F SS 0.0112
00-110 B-4-R10362F SS 0.006
00-111 C-4-038835F SS 0.0158
00-111 B-4-038835F SS 0.007
00-111 C-4-S82906F SS 0.0158
00-111 B-4-S82906F SS 0.007
00-111 C-4-038835FT SS 0.0158
00-111 B-4-038835FT SS 0.007
00-242 C-4-039173F BS 0.022
00-242 B-4-039173F SS 0.007
00-242 C-4-039556F BS 0.022
00-242 B-4-039556F SS 0.007
00-242 C-4-039173Z BS 0.022
00-242 B-4-039173Z SS 0.007
00-242 C-4-039556FT BS 0.022
00-242 B-4-039556FT SS 0.007
相同型号(model)如果case_no以C开头,其weight值都相同
相同型号(model)如果case_no以B开头,其weight值都相同
如上所示。
假如
model case_no material weight
00-110 C-4-946707F SS 0.0112
00-110 B-4-946707F SS 0.006
00-110 C-4-946961F SS ?=0.0112
00-110 B-4-946961F SS ?=0.006
00-110 C-4-S82922FT SS ?=0.0112
00-110 B-4-S82922FT SS ?=0.006
00-110 C-4-R10362F SS ?=0.0112
00-110 B-4-R10362F SS ?=0.006
00-111 C-4-038835F SS ?=0.0158
00-111 B-4-038835F SS ?=0.007
00-111 C-4-S82906F SS ?=0.0158
00-111 B-4-S82906F SS ?=0.007
00-111 C-4-038835FT SS ?=0.0158
00-111 B-4-038835FT SS ?=0.007
00-242 C-4-039173F BS ?=0.022
00-242 B-4-039173F SS ?=0.007
00-242 C-4-039556F BS ?=0.022
00-242 B-4-039556F SS ?=0.007
00-242 C-4-039173Z BS ?=0.022
00-242 B-4-039173Z SS ?=0.007
00-242 C-4-039556FT BS ?=0.022
00-242 B-4-039556FT SS ?=0.007
假如带?的地方为空或者为零,怎样自动填充为该型号已有的值,因同型号(model)中case_no以C开头weight只存在两种值,一种就是不为零的值,一种就是空值或零值,
怎样根据前面的条件自动把空值或零值自动填充为不为零的值。
条件:相同型号(model)如果case_no以C开头,其weight值都相同
相同型号(model)如果case_no以B开头,其weight值都相同
谢字!VB界面中我使用的true dbgrid第三方控件
输入一个型号MODEL假如为00-110,然后点查询按钮,TRUE DBGRID控件显示
case_no material weight
C-4-946707F SS 0.0112
B-4-946707F SS 0.006
C-4-946961F SS ?=0.0112
B-4-946961F SS ?=0.006
C-4-S82922FT SS ?=0.0112
B-4-S82922FT SS ?=0.006
B-4-R10362F SS ?=0.006
保存--》新增--》即可修改成功。?为需要自动填充的地方,说明weight不为空或不为零的值不一定在第一行。
weight表有四个字段,model(型号)case_no(壳编号)material(材质)weight(单重)
model case_no material weight
00-110 C-4-946707F SS 0.0112
00-110 B-4-946707F SS 0.006
00-110 C-4-946961F SS 0.0112
00-110 B-4-946961F SS 0.006
00-110 C-4-S82922FT SS 0.0112
00-110 B-4-S82922FT SS 0.006
00-110 C-4-R10362F SS 0.0112
00-110 B-4-R10362F SS 0.006
00-111 C-4-038835F SS 0.0158
00-111 B-4-038835F SS 0.007
00-111 C-4-S82906F SS 0.0158
00-111 B-4-S82906F SS 0.007
00-111 C-4-038835FT SS 0.0158
00-111 B-4-038835FT SS 0.007
00-242 C-4-039173F BS 0.022
00-242 B-4-039173F SS 0.007
00-242 C-4-039556F BS 0.022
00-242 B-4-039556F SS 0.007
00-242 C-4-039173Z BS 0.022
00-242 B-4-039173Z SS 0.007
00-242 C-4-039556FT BS 0.022
00-242 B-4-039556FT SS 0.007
相同型号(model)如果case_no以C开头,其weight值都相同
相同型号(model)如果case_no以B开头,其weight值都相同
如上所示。
假如
model case_no material weight
00-110 C-4-946707F SS 0.0112
00-110 B-4-946707F SS 0.006
00-110 C-4-946961F SS ?=0.0112
00-110 B-4-946961F SS ?=0.006
00-110 C-4-S82922FT SS ?=0.0112
00-110 B-4-S82922FT SS ?=0.006
00-110 C-4-R10362F SS ?=0.0112
00-110 B-4-R10362F SS ?=0.006
00-111 C-4-038835F SS ?=0.0158
00-111 B-4-038835F SS ?=0.007
00-111 C-4-S82906F SS ?=0.0158
00-111 B-4-S82906F SS ?=0.007
00-111 C-4-038835FT SS ?=0.0158
00-111 B-4-038835FT SS ?=0.007
00-242 C-4-039173F BS ?=0.022
00-242 B-4-039173F SS ?=0.007
00-242 C-4-039556F BS ?=0.022
00-242 B-4-039556F SS ?=0.007
00-242 C-4-039173Z BS ?=0.022
00-242 B-4-039173Z SS ?=0.007
00-242 C-4-039556FT BS ?=0.022
00-242 B-4-039556FT SS ?=0.007
假如带?的地方为空或者为零,怎样自动填充为该型号已有的值,因同型号(model)中case_no以C开头weight只存在两种值,一种就是不为零的值,一种就是空值或零值,
怎样根据前面的条件自动把空值或零值自动填充为不为零的值。
条件:相同型号(model)如果case_no以C开头,其weight值都相同
相同型号(model)如果case_no以B开头,其weight值都相同
谢字!VB界面中我使用的true dbgrid第三方控件
输入一个型号MODEL假如为00-110,然后点查询按钮,TRUE DBGRID控件显示
case_no material weight
C-4-946707F SS 0.0112
B-4-946707F SS 0.006
C-4-946961F SS ?=0.0112
B-4-946961F SS ?=0.006
C-4-S82922FT SS ?=0.0112
B-4-S82922FT SS ?=0.006
B-4-R10362F SS ?=0.006
保存--》新增--》即可修改成功。?为需要自动填充的地方,说明weight不为空或不为零的值不一定在第一行。
Option Explicit
Public mConnString As StringPrivate Sub Command1_Click()
Dim mRst As New ADODB.Recordset
Dim mCon As New ADODB.Connection
mCon.Open mConnString
mRst.CursorLocation = adUseClient
mCon.CursorLocation = adUseClient
mRst.Open "Select Distinct model From weight", mConnString, adOpenStatic, adLockOptimistic, adCmdText
Do Until mRst.EOF
mCon.Execute ("Update weight Set weight = (Select Distinct weight From weight Where model = '" & mRst("model") & "' And SubString(case_no, 1, 1) = '" & "B'" & " And weight > 0 ) Where model = '" & mRst("model") & "' And SubString(case_no, 1, 1) = '" & "B' And (weight = 0 Or weight Is Null)")
mCon.Execute ("Update weight Set weight = (Select Distinct weight From weight Where model = '" & mRst("model") & "' And SubString(case_no, 1, 1) = '" & "C'" & " And weight > 0 ) Where model = '" & mRst("model") & "' And SubString(case_no, 1, 1) = '" & "C' And (weight = 0 Or weight Is Null)")
mRst.MoveNext
Loop
Set mRst = Nothing
Set mCon = Nothing
MsgBox "Update Finish"
End SubPrivate Sub Form_Load()
mConnString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=weight;Data Source=Server"
End Sub
Option Explicit
Private strSQL As String
Private strCon As String
Private pXData As New XArrayDB
Private cnCost As New ADODB.Connection
Private cnSao As New ADODB.Connection
Private cnSQL As New ADODB.Connection
Private rsCost As New ADODB.Recordset
Private rsSao As New ADODB.Recordset
Private rsSql As New ADODB.Recordset
Private Sub Form_Load()
Dim pValueItem As New ValueItem
cnSQL.ConnectionString = "DSN=BnsWeight;UID=BnsWeight;PWD=weight"
cnSQL.Open
strCon = "driver=Microsoft Visual FoxPro Driver;UID=;SourceType=DBC;SourceDB=\\Filntserver\vms1.0"
cnCost.ConnectionString = strCon & "\cost.dbc"
cnCost.Open
cnSao.ConnectionString = strCon & "\sao001.dbc"
cnSao.Open
With TDBGrid1
.Columns(1).ValueItems.Clear
With .Columns(1)
.ValueItems.Add pValueItem
.ValueItems(0).Value = "AL"
.ValueItems.Add pValueItem
.ValueItems(1).Value = "BS"
.ValueItems.Add pValueItem
.ValueItems(2).Value = "SS"
.ValueItems.Add pValueItem
.ValueItems(3).Value = "TI"
End With
Set .Array = pXData
.ReBind
End With
End Sub
Private Sub InitTDBGrid()
If pXData.Count(1) > 0 Then pXData.DeleteRows 0, pXData.Count(1)
Set TDBGrid1.Array = pXData
TDBGrid1.ReBind
End SubPrivate Sub LoadTDBGrid()
Dim i As Integer
Dim j As Integer
Dim tempData As Variant
Dim pstrMaterial As String
Dim psngWeight As Single
Dim psngSml_total As Single
Screen.MousePointer = vbHourglass
j = 0
If pXData.Count(1) > 0 Then pXData.DeleteRows 0, pXData.Count(1)
strSQL = "select case_no,sml_total from fil_pri where model='" _
& Trim(txtModel.Text) & "' and left(case_no,2)='4-'"
rsCost.Open strSQL, cnCost, adOpenKeyset, adLockReadOnly
If Not rsCost.EOF Then
i = 0
ReDim tempData(2 * rsCost.RecordCount - 1, 1)
For j = 0 To 2 * rsCost.RecordCount - 1 Step 2
tempData(j, 0) = "C-" & Trim(rsCost.Fields("case_no"))
tempData(j, 1) = rsCost.Fields("sml_total") - 5
tempData(j + 1, 0) = "B-" & Trim(rsCost.Fields("case_no"))
tempData(j + 1, 1) = 5
rsCost.MoveNext
Next j
pXData.ReDim 0, 2 * rsCost.RecordCount - 1, 0, 3
End If
rsCost.Close
If j > 0 Then
For j = 0 To UBound(tempData)
strSQL = "select material,weight,sml_total from weight" _
& " where model='" & Trim(txtModel.Text) _
& "' and case_no='" & tempData(j, 0) & "'"
rsSql.Open strSQL, cnSQL, adOpenKeyset, adLockOptimistic
If Not rsSql.EOF Then
pstrMaterial = rsSql.Fields("material")
psngWeight = rsSql.Fields("weight")
psngSml_total = rsSql.Fields("sml_total")
Else
strSQL = "select distinct material from sao001 where model='" & Trim(txtModel.Text) & "'"
rsSao.Open strSQL, cnSao, adOpenKeyset, adLockReadOnly
If Not rsSao.EOF Then
If Left(tempData(j, 0), 2) = "B-" Then
Select Case Trim(rsSao.Fields("material"))
Case "AL", "BS"
pstrMaterial = "SS"
Case Else
pstrMaterial = Trim(rsSao.Fields("material"))
End Select
Else
pstrMaterial = Trim(rsSao.Fields("material"))
End If
Else
pstrMaterial = ""
End If
rsSao.Close
psngWeight = 0
psngSml_total = tempData(j, 1)
End If
rsSql.Close
pXData(j, 0) = tempData(j, 0)
pXData(j, 1) = pstrMaterial
pXData(j, 2) = psngWeight
pXData(j, 3) = psngSml_total
Next j
End If
Set TDBGrid1.Array = pXData
TDBGrid1.MoveFirst
TDBGrid1.ReBind
Screen.MousePointer = vbDefault
End SubPrivate Function Text_Check() As Boolean
Text_Check = False
If Trim(txtModel.Text) = "" Then
MsgBox "請首先輸入Model!", vbInformation + vbOKOnly, "提示"
txtModel.SetFocus
Exit Function
End If
Text_Check = True
End Function
Private Sub cmdAdd_Click()
txtModel.Text = ""
txtModel.SetFocus
InitTDBGrid
End SubPrivate Sub cmdDelete_Click()
If Not Text_Check Then Exit Sub
If MsgBox("請確認是否刪除該筆記錄?", vbQuestion + vbYesNo, "提示") = vbNo Then Exit Sub
strSQL = "delete from weight where model='" & Trim(txtModel.Text) & "'"
cnSQL.Execute strSQL
strSQL = "delete from case_weight where model='" & Trim(txtModel.Text) & "'"
cnSQL.Execute strSQL
cmdAdd_Click
End Sub
If Not Text_Check Then Exit Sub
LoadTDBGrid
End SubPrivate Sub cmdSave_Click()
Dim iCount As Integer
If Not Text_Check Then Exit Sub
If pXData.Count(1) < 1 Then
MsgBox "無資料可以保存!", vbCritical + vbOKOnly, "提示"
Exit Sub
End If
TDBGrid1.MoveFirst
TDBGrid1.MoveLast
For iCount = 0 To pXData.Count(1) - 1
strSQL = "IF EXISTS(select * from weight where model='" _
& Trim(txtModel.Text) & "' and case_no='" & pXData(iCount, 0) & "')" _
& " Update weight set material='" & pXData(iCount, 1) _
& "',weight=" & pXData.Value(iCount, 2) & ",sml_total=" _
& pXData.Value(iCount, 3) & " where model='" & Trim(txtModel.Text) _
& "' and case_no='" & pXData(iCount, 0) & "'" _
& " ELSE" _
& " Insert weight Values('" _
& Trim(txtModel.Text) & "','" _
& pXData(iCount, 0) & "','" _
& pXData(iCount, 1) & "'," _
& pXData(iCount, 2) & "," _
& pXData(iCount, 3) & ")"
cnSQL.Execute strSQL
Next iCount
For iCount = 0 To pXData.Count(1) - 1 Step 2
If Left(pXData(iCount, 0), 2) = "C-" Then
strSQL = "IF EXISTS(Select * from case_weight where model='" _
& Trim(txtModel.Text) & "' and case_no='" _
& Right(pXData(iCount, 0), Len(pXData(iCount, 0)) - 2) & "')" _
& " Update case_weight Set material='" & pXData(iCount, 1) & "'," _
& " weight=" & Val(pXData.Value(iCount, 2)) + Val(pXData.Value(iCount + 1, 2)) & "," _
& " sml_total=" & Val(pXData.Value(iCount, 3)) + Val(pXData.Value(iCount + 1, 3)) _
& " where model='" & Trim(txtModel.Text) & "' and case_no='" _
& Right(pXData(iCount, 0), Len(pXData(iCount, 0)) - 2) & "'" _
& " ELSE" _
& " Insert case_weight Values('" _
& Trim(txtModel.Text) & "','" _
& Right(pXData(iCount, 0), Len(pXData(iCount, 0)) - 2) & "','" _
& pXData(iCount, 1) & "'," _
& Val(pXData.Value(iCount, 2)) + Val(pXData.Value(iCount + 1, 2)) & "," _
& Val(pXData.Value(iCount, 3)) + Val(pXData.Value(iCount + 1, 3)) & ")"
cnSQL.Execute strSQL
End If
Next iCount
cmdAdd_Click
End SubPrivate Sub TDBGrid1_KeyDown(KeyCode As Integer, Shift As Integer)
With TDBGrid1
If .Col = 1 Then
If KeyCode = vbKeyDelete Then KeyCode = 0
End If
End With
End SubPrivate Sub TDBGrid1_KeyPress(KeyAscii As Integer)
With TDBGrid1
If KeyAscii = vbKeyReturn Then
If .Col = 1 Then
.Col = .Col + 1
Else
If .Book < pXData.Count(1) - 1 Then
.Row = .Row + 1
If .Col = 3 Then
.Col = 1
Else
.Col = 2
End If
End If
End If
Exit Sub
End If
If .Col = 1 Then
KeyAscii = 0
Else
If KeyAscii = vbKeyBack Then Exit Sub
Select Case KeyAscii
Case 48 To 57, 46
KeyAscii = KeyAscii
Case Else
KeyAscii = 0
Exit Sub
End Select
If .SelLength > 0 Then Exit Sub
If Len(.Columns(.Col).Text) > 14 Then KeyAscii = 0
End If
End With
End SubPrivate Sub txtModel_Change()
InitTDBGrid
End SubPrivate Sub txtModel_GotFocus()
Clipboard.Clear
txtModel.SelStart = 0
txtModel.SelLength = LenB(StrConv(txtModel.Text, vbFromUnicode))
End SubPrivate Sub txtModel_KeyPress(KeyAscii As Integer)
If KeyAscii = vbKeyReturn Then
SendKeys "{Tab}"
Exit Sub
End If
If KeyAscii = 39 Then KeyAscii = 0
End Sub
Private Sub cmdExit_Click()
Unload Me
End Sub
Private Sub Form_Unload(Cancel As Integer)
Set cnSQL = Nothing
Set cnCost = Nothing
Set cnSao = Nothing
Set pXData = Nothing
End Sub