form1.frm

一个关于餐观的定餐系统

         Top             =   360
         Width           =   1575
      End
      Begin VB.TextBox Text1 
         Height          =   270
         Index           =   0
         Left            =   1200
         TabIndex        =   6
         Top             =   360
         Width           =   1575
      End
      Begin VB.Label Label7 
         Caption         =   "元以下"
         Height          =   255
         Left            =   5880
         TabIndex        =   13
         Top             =   1560
         Width           =   615
      End
      Begin VB.Label Label6 
         Caption         =   "元以上"
         Height          =   255
         Left            =   3120
         TabIndex        =   12
         Top             =   1560
         Width           =   615
      End
      Begin VB.Label Label5 
         Caption         =   "所在城市:"
         Height          =   375
         Left            =   240
         TabIndex        =   5
         Top             =   960
         Width           =   855
      End
      Begin VB.Label Label4 
         Caption         =   "所在地址:"
         Height          =   375
         Left            =   3120
         TabIndex        =   4
         Top             =   960
         Width           =   855
      End
      Begin VB.Label Label3 
         Caption         =   "餐馆名称:"
         Height          =   255
         Left            =   240
         TabIndex        =   3
         Top             =   360
         Width           =   855
      End
      Begin VB.Label Label2 
         Caption         =   "人均消费:"
         Height          =   375
         Left            =   240
         TabIndex        =   2
         Top             =   1560
         Width           =   855
      End
      Begin VB.Label Label1 
         Caption         =   "菜系:"
         Height          =   255
         Left            =   3120
         TabIndex        =   1
         Top             =   360
         Width           =   855
      End
   End
End
Attribute VB_Name = "Form1"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Dim sqlstr As String
Dim conn As New ADODB.Connection
Dim rs_zrest As New ADODB.Recordset '主餐馆

Private Sub Command1_Click() '查询按钮
    Dim i As Integer
    Dim flag As Integer '1.用来判别是否填写查询条件 2.判断是否要截取字符串and
    cleardata
    flag = 0
    For i = 0 To 5
        If Text1(i) = "" Then flag = flag + 1
    Next
    If flag = 6 Then
        MsgBox "请输入查询条件!"
    Else
        If Val(Text1(4).Text) > Val(Text1(5).Text) Then
                MsgBox "对不起,您输入的人均消费有误,请重输!"
                Text1(4).SetFocus
                Exit Sub
        End If
        flag = 0
        sqlstr = "select r.restname,r.cuisine,c.restaddr,c.restregion,r.minpayment,r.midpayment,r.maxpayment,c.restid from restaurantinfo as r inner join contactinfo as c on r.restid=c.restid where "
        If Text1(0).Text <> "" Then
            sqlstr = sqlstr & "r.restname like '%" & Text1(0).Text & "%' and "
        End If
        
        If Text1(1).Text <> "" Then
            sqlstr = sqlstr & "r.cuisine like '%" & Text1(1).Text & "%' and "
        End If
        
        If Text1(2).Text <> "" Then
            sqlstr = sqlstr & "c.restregion like '%" & Text1(2).Text & "%' and "
        End If
        
        If Text1(3).Text <> "" Then
            sqlstr = sqlstr & "c.restaddr like '%" & Text1(3).Text & "%' and "
        End If
        
        If Text1(4).Text <> "" And Text1(5).Text <> "" Then
                sqlstr = sqlstr & "(r.minpayment>=" & Text1(4).Text & " and r.maxpayment<=" & Text1(5).Text & ")"
                flag = 1
        ElseIf Text1(4).Text <> "" Then
            sqlstr = sqlstr & "r.minpayment>=" & Text1(4).Text
            flag = 1
        ElseIf Text1(5).Text <> "" Then
            sqlstr = sqlstr & "r.maxpayment<=" & Text1(5).Text
            flag = 1
        End If
        
        If flag = 0 Then
            sqlstr = Left(sqlstr, Len(sqlstr) - 5)
        End If
        If conn.State = adStateOpen Then
            conn.Close
        End If
        If rs_zrest.State = adStateOpen Then
            rs_zrest.Close
        End If
        conn.ConnectionString = "driver={sql server};server=localhost;database=restaurant;uid=tsk;pwd=810504"
        conn.CursorLocation = adUseClient
        conn.Open
        rs_zrest.Open sqlstr, conn, adOpenDynamic, adLockOptimistic, adCmdText
        If Not rs_zrest.EOF Then
            getdata
            Command2.Enabled = True
            Command3.Enabled = True
            Label17.Enabled = True
        Else
            MsgBox "没有找到你所要的记录!"
        End If
    End If
End Sub

Private Sub Command2_Click() '上一家
rs_zrest.MovePrevious
    If Not rs_zrest.BOF Then
            getdata
            restid = rs_zrest(7)
            Command3.Enabled = True
            checkdata (1)
    Else
        rs_zrest.MoveNext
        Command2.Enabled = False
    End If
End Sub

Private Sub Command3_Click() '下一家
rs_zrest.MoveNext
    If Not rs_zrest.EOF Then
            getdata
            Command2.Enabled = True
            checkdata (0)
    Else
        rs_zrest.MovePrevious
        Command3.Enabled = False
    End If
End Sub

Private Sub Command4_Click() '关闭
    If MsgBox("是否真的要退出系统?", vbYesNo) = vbYes Then
        End
    End If
End Sub

Private Sub Form_Load()
    Form1.Top = (Screen.Height - Form1.Height) / 2
    Form1.Left = (Screen.Width - Form1.Width) / 2
    Label17.Enabled = False
    Command2.Enabled = False
    Command3.Enabled = False
End Sub


Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)
    If MsgBox("是否真的要退出系统?", vbYesNo) = vbYes Then
    Else
        Cancel = 1
    End If
End Sub

Private Sub Label17_Click()
    Form2.Show vbModal
End Sub

Private Sub Text1_KeyPress(Index As Integer, KeyAscii As Integer)
    If Index >= 0 And Index <= 3 Then
        If KeyAscii = Asc("'") Or KeyAscii = Asc("%") Or KeyAscii = Asc(",") Or KeyAscii = Asc("?") Or KeyAscii = Asc("_") Or KeyAscii = Asc("&") Or KeyAscii = Asc("*") Then
            KeyAscii = 0
            MsgBox "关键字不能为空,且字符串中不能包含*,&,%,_,',?特殊字符"
            Text1(Index).SetFocus
        End If
    End If
    If Index = 4 Or Index = 5 Then
        If (KeyAscii < 48 Or KeyAscii > 57) And KeyAscii <> 8 Then
            KeyAscii = 0
            MsgBox "请输入数字!"
            Text1(Index).SetFocus
        End If
    End If
End Sub

Private Sub getdata() '获得数据
    Dim i As Integer
    For i = 0 To 6
        Label16(i).Caption = rs_zrest(i)
    Next
    restid = rs_zrest(i)
End Sub
Private Sub cleardata() '清除数据
    Dim i As Integer
        For i = 0 To 6
            Label16(i).Caption = ""
        Next
        restid = -1
        Command2.Enabled = False
        Command3.Enabled = False
        Label17.Enabled = False
End Sub
Private Sub checkdata(a As Integer)
    If a = 0 Then
        rs_zrest.MoveNext
        If rs_zrest.EOF Then Command3.Enabled = False
        rs_zrest.MovePrevious
    Else
        rs_zrest.MovePrevious
        If rs_zrest.BOF Then Command2.Enabled = False
        rs_zrest.MoveNext
    End If
End Sub