conn.bas

上传数据,希望对初学者有用!!!!!上传数据,希望对初学者有用

Attribute VB_Name = "conn"
Option Explicit
Public Cnn As ADODB.Connection
Public Cnn1 As ADODB.Connection
Public str As String '连接字符串
Public tInterval As Long '定时器时间间隔
Public s As Integer
Public arrData(300, 3) '存储最终的全部流量站数据
Public arrData1(300, 7) '存储最终的全部供水站数据
Public arrCount '存储数组行数
Public arrCount1 '存储数组行数

Public Function dbConn()
   On Error GoTo connErr
   Set Cnn = New ADODB.Connection '实例化连接
   Set Cnn1 = New ADODB.Connection '实例化连接
   Cnn.Open str
   Cnn1.Open str
   Call ReadData
   Exit Function
connErr:
   writeMsg ("函数dbConn运行错误 " + Err.Description)
End Function

  Public Function ReadData()
 ' On Error GoTo ExecuteSQL_Error
  Dim rst As New ADODB.Recordset '流量站记录
  Dim rst1 As New ADODB.Recordset '供水站记录
  Dim i
   
  Dim adoComm As New ADODB.Command
  Dim adoComm1 As New ADODB.Command
  
  With adoComm
    .ActiveConnection = Cnn
    .CommandType = 4                '4:存储过程
    .CommandText = "sp_getData"
   End With
  Set rst = adoComm.Execute()
  
   i = 1
   Do While Not rst.EOF '填充数据至数组
      arrData(i, 0) = rst!id '站号
      arrData(i, 1) = Format(rst!w, "######0.0######")  '水量
      arrData(i, 2) = Format(rst!p, "###0.0######") '压力
      arrData(i, 3) = Format(rst!a, "###0.0######") '电流
      rst.MoveNext
      i = i + 1
   Loop
    
   arrCount = i - 1 'arrCount:数组中数据行数
   rst.Close
   adoComm.ActiveConnection = Nothing
  
   
   
    With adoComm1
    .ActiveConnection = Cnn1
    .CommandType = 4                '4:存储过程
    .CommandText = "sp_getData1"
    End With
   
   Set rst1 = adoComm1.Execute()
  
   i = 1
   Do While Not rst1.EOF '填充数据至数组
      arrData1(i, 0) = rst1!id '站号
      'arrData1(i, 1) = Format(rst1!a, "######0.0######")
      arrData1(i, 1) = Abs(Round(rst1!a, 0))
      arrData1(i, 2) = Format(Round(rst1!b, 2), "######0.00")
      arrData1(i, 3) = Round(rst1!c, 0)
      arrData1(i, 4) = Round(rst1!d, 0)
      arrData1(i, 5) = rst1!e
      arrData1(i, 6) = rst1!f
      arrData1(i, 7) = rst1!g
      
      
      rst1.MoveNext
      i = i + 1
   Loop
    
   arrCount1 = i - 1 'arrCount:数组中数据行数
   rst1.Close
   
   Set rst = Nothing
   Set rst1 = Nothing
   Cnn.Close
   Cnn1.Close
   
   Call getData
   Exit Function

ExecuteSQL_Error:
  writeMsg ("函数ReadData运行错误 " + Err.Description)
  End Function
  Function findData(ByVal sid As String) As String
  '根据站号查找水量、压力、电流数据
  On Error GoTo findErr
  Dim arrTemp(2)
  Dim i As Integer
  arrTemp(0) = 0
  arrTemp(1) = 0
  arrTemp(2) = 0
  i = 1
  
  Do While i <= arrCount
  If sid = arrData(i, 0) Then
    arrTemp(0) = arrData(i, 1)
    arrTemp(1) = arrData(i, 2)
    arrTemp(2) = arrData(i, 3)
    Exit Do
  End If
  i = i + 1
  Loop
  findData = CStr(arrTemp(0)) + "|" + CStr(arrTemp(1)) + "|" + CStr(arrTemp(2))
  Exit Function
findErr:
  writeMsg ("函数findData执行错误 " + Err.Description)
  End Function

 Function findData1(ByVal sid As String) As String
  '根据站号查找水量、压力、电流数据
  On Error GoTo findErr
  Dim arrTemp(6)
  Dim i As Integer
  arrTemp(0) = 0
  arrTemp(1) = 0
  arrTemp(2) = 0
  arrTemp(3) = 0
  arrTemp(4) = 0
  arrTemp(5) = 0
  arrTemp(6) = 0
  
  i = 1
  
  Do While i <= arrCount1
  If sid = arrData1(i, 0) Then
    arrTemp(0) = arrData1(i, 1)
    arrTemp(1) = arrData1(i, 2)
    arrTemp(2) = arrData1(i, 3)
    arrTemp(3) = arrData1(i, 4)
    arrTemp(4) = arrData1(i, 5)
    arrTemp(5) = arrData1(i, 6)
    arrTemp(6) = arrData1(i, 7)
      
    Exit Do
  End If
  i = i + 1
  Loop
  findData1 = CStr(arrTemp(0)) + "|" + CStr(arrTemp(1)) + "|" + CStr(arrTemp(2)) + "|" + CStr(arrTemp(3)) + "|" + CStr(arrTemp(4)) + "|" + CStr(arrTemp(5)) + "|" + CStr(arrTemp(6))
  Exit Function
findErr:
  writeMsg ("函数findData1执行错误 " + Err.Description)
  End Function
Private Sub getData()
'2007.2.6 9:30 am 开始对站点重编号
'On Error GoTo getErr
On Error Resume Next
Dim sid As String
Dim tempArr

'以下流量站(北方区域)============================================

'杨店乡===
sid = "0906" '杨店 9.6
tempArr = Split(findData(sid), "|")
Form1.Text1(0).Text = tempArr(0)
Form1.Text1(1).Text = Abs(Abs(tempArr(1)))
Form1.Text1(2).Text = tempArr(2)

sid = "0b02" '张楼村 11.2
tempArr = Split(findData(sid), "|")
tempArr = Split(findData(sid), "|")
Form1.Text1(3).Text = tempArr(0)
Form1.Text1(4).Text = Abs(tempArr(1))
Form1.Text1(5).Text = tempArr(2)

sid = "1006" '王村 16.6
tempArr = Split(findData(sid), "|")
tempArr = Split(findData(sid), "|")
Form1.Text1(6).Text = tempArr(0)
Form1.Text1(7).Text = Abs(tempArr(1))
Form1.Text1(8).Text = tempArr(2)

sid = "0a05" '袁庄 10.5
tempArr = Split(findData(sid), "|")
Form1.Text1(9).Text = tempArr(0)
Form1.Text1(10).Text = Abs(tempArr(1))
Form1.Text1(11).Text = tempArr(2)

sid = "0102" '张保庄 1.2
tempArr = Split(findData(sid), "|")
Form1.Text1(12).Text = tempArr(0)
Form1.Text1(13).Text = Abs(tempArr(1))
Form1.Text1(14).Text = tempArr(2)

sid = "0a06" '大屯 10.6
tempArr = Split(findData(sid), "|")
Form1.Text1(15).Text = tempArr(0)
Form1.Text1(16).Text = Abs(tempArr(1))
Form1.Text1(17).Text = tempArr(2)

sid = "0301" '庙口 3.1
tempArr = Split(findData(sid), "|")
Form1.Text1(18).Text = tempArr(0)
Form1.Text1(19).Text = Abs(tempArr(1))
Form1.Text1(20).Text = tempArr(2)

sid = "0d05" '鲁村 13.5
tempArr = Split(findData(sid), "|")
Form1.Text1(21).Text = tempArr(0)
Form1.Text1(22).Text = Abs(tempArr(1))
Form1.Text1(23).Text = tempArr(2)

sid = "0b06" '王楼 11.6
tempArr = Split(findData(sid), "|")
Form1.Text1(24).Text = tempArr(0)
Form1.Text1(25).Text = Abs(tempArr(1))
Form1.Text1(26).Text = tempArr(2)

sid = "0101" '崔庙 1.1
tempArr = Split(findData(sid), "|")
Form1.Text1(27).Text = tempArr(0)
Form1.Text1(28).Text = Abs(tempArr(1))
Form1.Text1(29).Text = tempArr(2)

sid = "1406" '张村 20.6
tempArr = Split(findData(sid), "|")
Form1.Text1(30).Text = tempArr(0)
Form1.Text1(31).Text = Abs(tempArr(1))
Form1.Text1(32).Text = tempArr(2)

sid = "1506" '刘古墩 21.6
tempArr = Split(findData(sid), "|")
Form1.Text1(33).Text = tempArr(0)
Form1.Text1(34).Text = Abs(tempArr(1))
Form1.Text1(35).Text = tempArr(2)


'苑庄镇===

sid = "010b" '前苑楼 1.11
Form1.Text2(0).Text = tempArr(0)
Form1.Text2(1).Text = Abs(tempArr(1))
Form1.Text2(2).Text = tempArr(2)

sid = "1206" '作里 18.6
tempArr = Split(findData(sid), "|")
Form1.Text2(3).Text = tempArr(0)
Form1.Text2(4).Text = Abs(tempArr(1))
Form1.Text2(5).Text = tempArr(2)

sid = "0205" '后苑楼 2.5
tempArr = Split(findData(sid), "|")
Form1.Text2(6).Text = tempArr(0)
Form1.Text2(7).Text = Abs(tempArr(1))
Form1.Text2(8).Text = tempArr(2)

sid = "0606" '苑庄 6.6
tempArr = Split(findData(sid), "|")
Form1.Text2(9).Text = tempArr(0)
Form1.Text2(10).Text = Abs(tempArr(1))
Form1.Text2(11).Text = tempArr(2)

sid = "1106" '毕庄 17.6
tempArr = Split(findData(sid), "|")
Form1.Text2(12).Text = tempArr(0)
Form1.Text2(13).Text = Abs(tempArr(1))
Form1.Text2(14).Text = tempArr(2)

sid = "1306" '前小秦 19.6
tempArr = Split(findData(sid), "|")
Form1.Text2(15).Text = tempArr(0)
Form1.Text2(16).Text = Abs(tempArr(1))
Form1.Text2(17).Text = tempArr(2)

sid = "0d06" '后小秦 13.6
tempArr = Split(findData(sid), "|")
Form1.Text2(18).Text = tempArr(0)
Form1.Text2(19).Text = Abs(tempArr(1))
Form1.Text2(20).Text = tempArr(2)

sid = "0c05" '高村 12.5
tempArr = Split(findData(sid), "|")
Form1.Text2(21).Text = tempArr(0)
Form1.Text2(22).Text = Abs(tempArr(1))
Form1.Text2(23).Text = tempArr(2)

sid = "090b" '郑楼 9.11
tempArr = Split(findData(sid), "|")
Form1.Text2(24).Text = tempArr(0)
Form1.Text2(25).Text = Abs(tempArr(1))
Form1.Text2(26).Text = tempArr(2)

sid = "020c" '马庄 2.12
tempArr = Split(findData(sid), "|")
Form1.Text2(27).Text = tempArr(0)
Form1.Text2(28).Text = Abs(tempArr(1))
Form1.Text2(29).Text = tempArr(2)

sid = "0a0b" '牧地 10.11
tempArr = Split(findData(sid), "|")
Form1.Text2(30).Text = tempArr(0)
Form1.Text2(31).Text = Abs(tempArr(1))
Form1.Text2(32).Text = tempArr(2)

sid = "070b" '朱村 7.11
tempArr = Split(findData(sid), "|")
Form1.Text2(33).Text = tempArr(0)
Form1.Text2(34).Text = Abs(tempArr(1))
Form1.Text2(35).Text = tempArr(2)

sid = "0501" '演马庄 5.1
tempArr = Split(findData(sid), "|")
Form1.Text2(36).Text = tempArr(0)
Form1.Text2(37).Text = Abs(tempArr(1))
Form1.Text2(38).Text = tempArr(2)


'军屯乡===

sid = "0503" '南陶 5.3
tempArr = Split(findData(sid), "|")
Form1.Text3(0).Text = tempArr(0)
Form1.Text3(1).Text = Abs(tempArr(1))
Form1.Text3(2).Text = tempArr(2)

sid = "0304" '任庄 3.4
tempArr = Split(findData(sid), "|")
Form1.Text3(3).Text = tempArr(0)
Form1.Text3(4).Text = Abs(tempArr(1))
Form1.Text3(5).Text = tempArr(2)

sid = "0f06" '军屯 15.6
tempArr = Split(findData(sid), "|")
Form1.Text3(6).Text = tempArr(0)
Form1.Text3(7).Text = Abs(tempArr(1))
Form1.Text3(8).Text = tempArr(2)

sid = "0905" '魏杨 9.5
tempArr = Split(findData(sid), "|")
Form1.Text3(9).Text = tempArr(0)
Form1.Text3(10).Text = Abs(tempArr(1))
Form1.Text3(11).Text = tempArr(2)

sid = "0902" '白店 9.2  注:以前的版本中没有
tempArr = Split(findData(sid), "|")
Form1.Text3(12).Text = tempArr(0)
Form1.Text3(13).Text = Abs(tempArr(1))
Form1.Text3(14).Text = tempArr(2)



'白石乡===  注:以前的版本中写的石军屯乡
sid = "080a" '路庄 8.10
tempArr = Split(findData(sid), "|")
Form1.Text4(0).Text = tempArr(0)
Form1.Text4(1).Text = Abs(tempArr(1))
Form1.Text4(2).Text = tempArr(2)

sid = "0208" '崔河 2.8
tempArr = Split(findData(sid), "|")
Form1.Text4(3).Text = tempArr(0)
Form1.Text4(4).Text = Abs(tempArr(1))
Form1.Text4(5).Text = tempArr(2)

sid = "050c" '褚庄 5.12
tempArr = Split(findData(sid), "|")
Form1.Text4(6).Text = tempArr(0)
Form1.Text4(7).Text = Abs(tempArr(1))
Form1.Text4(8).Text = tempArr(2)


'郭楼镇 ====
sid = "010a" '陈海1.10
tempArr = Split(findData(sid), "|")
Form1.Text5(0).Text = tempArr(0)
Form1.Text5(1).Text = Abs(tempArr(1))
Form1.Text5(2).Text = tempArr(2)

sid = "1409" '孙庄 20.9
tempArr = Split(findData(sid), "|")
Form1.Text5(3).Text = tempArr(0)
Form1.Text5(4).Text = Abs(tempArr(1))
Form1.Text5(5).Text = tempArr(2)

sid = "1509" '水口 21.9
tempArr = Split(findData(sid), "|")
Form1.Text5(6).Text = tempArr(0)
Form1.Text5(7).Text = Abs(tempArr(1))
Form1.Text5(8).Text = tempArr(2)

sid = "030b" '西海子 3.11
tempArr = Split(findData(sid), "|")
Form1.Text5(9).Text = tempArr(0)
Form1.Text5(10).Text = Abs(tempArr(1))
Form1.Text5(11).Text = tempArr(2)

sid = "0a09" '张庄 10.9
tempArr = Split(findData(sid), "|")
Form1.Text5(12).Text = tempArr(0)
Form1.Text5(13).Text = Abs(tempArr(1))
Form1.Text5(14).Text = tempArr(2)

sid = "0307" '陈楼 3.7
tempArr = Split(findData(sid), "|")
Form1.Text5(15).Text = tempArr(0)
Form1.Text5(16).Text = Abs(tempArr(1))
Form1.Text5(17).Text = tempArr(2)

sid = "0207" '营墙 2.7
tempArr = Split(findData(sid), "|")
Form1.Text5(18).Text = tempArr(0)
Form1.Text5(19).Text = Abs(tempArr(1))
Form1.Text5(20).Text = tempArr(2)

sid = "0a08" '万庄 10.8
tempArr = Split(findData(sid), "|")
Form1.Text5(21).Text = tempArr(0)
Form1.Text5(22).Text = Abs(tempArr(1))
Form1.Text5(23).Text = tempArr(2)

sid = "0107" '董赵庄 1.7
tempArr = Split(findData(sid), "|")
Form1.Text5(24).Text = tempArr(0)
Form1.Text5(25).Text = Abs(tempArr(1))
Form1.Text5(26).Text = tempArr(2)

sid = "0c0b" '小赵庄 12.11
tempArr = Split(findData(sid), "|")
Form1.Text5(27).Text = tempArr(0)
Form1.Text5(28).Text = Abs(tempArr(1))
Form1.Text5(29).Text = tempArr(2)

sid = "050b" '大古墩 5.11
tempArr = Split(findData(sid), "|")
Form1.Text5(30).Text = tempArr(0)
Form1.Text5(31).Text = Abs(tempArr(1))
Form1.Text5(32).Text = tempArr(2)


'寅寺镇===
sid = "0403" '西寅寺 4.3
tempArr = Split(findData(sid), "|")
Form1.Text6(0).Text = tempArr(0)
Form1.Text6(1).Text = Abs(tempArr(1))
Form1.Text6(2).Text = tempArr(2)

sid = "0506" '罗汉寺 5.6
tempArr = Split(findData(sid), "|")
Form1.Text6(3).Text = tempArr(0)
Form1.Text6(4).Text = Abs(tempArr(1))
Form1.Text6(5).Text = tempArr(2)

sid = "0f09" ' 腰楼 15.9
tempArr = Split(findData(sid), "|")
Form1.Text6(6).Text = tempArr(0)
Form1.Text6(7).Text = Abs(tempArr(1))
Form1.Text6(8).Text = tempArr(2)

sid = "0b0b" '前沙 11.11
tempArr = Split(findData(sid), "|")
Form1.Text6(9).Text = tempArr(0)
Form1.Text6(10).Text = Abs(tempArr(1))
Form1.Text6(11).Text = tempArr(2)

sid = "0405" '后林 4.5
tempArr = Split(findData(sid), "|")
Form1.Text6(12).Text = tempArr(0)
Form1.Text6(13).Text = Abs(tempArr(1))
Form1.Text6(14).Text = tempArr(2)

sid = "0d09" '西草 13.9
tempArr = Split(findData(sid), "|")
Form1.Text6(15).Text = tempArr(0)
Form1.Text6(16).Text = Abs(tempArr(1))
Form1.Text6(17).Text = tempArr(2)

sid = "0b09" '东草 11.9
tempArr = Split(findData(sid), "|")
Form1.Text6(18).Text = tempArr(0)
Form1.Text6(19).Text = Abs(tempArr(1))
Form1.Text6(20).Text = tempArr(2)

sid = "060a" '信庄 6.10
tempArr = Split(findData(sid), "|")
Form1.Text6(21).Text = tempArr(0)
Form1.Text6(22).Text = Abs(tempArr(1))
Form1.Text6(23).Text = tempArr(2)

sid = "1706" '尚庄 23.6
tempArr = Split(findData(sid), "|")
Form1.Text6(24).Text = tempArr(0)
Form1.Text6(25).Text = Abs(tempArr(1))
Form1.Text6(26).Text = tempArr(2)

sid = "0909" '小楼 9.9
tempArr = Split(findData(sid), "|")
Form1.Text6(27).Text = tempArr(0)
Form1.Text6(28).Text = Abs(tempArr(1))
Form1.Text6(29).Text = tempArr(2)

sid = "0708" '东李 7.8
tempArr = Split(findData(sid), "|")
Form1.Text6(30).Text = tempArr(0)
Form1.Text6(31).Text = Abs(tempArr(1))
Form1.Text6(32).Text = tempArr(2)

sid = "0a01" '马口 10.1
tempArr = Split(findData(sid), "|")
Form1.Text6(33).Text = tempArr(0)