[visual-studio]通过 Windows 窗体的 SQL 查询

发布时间: 2014/3/1 6:03:35
注意事项: 本文中文内容可能为机器翻译,如要查看英文原文请点击上面连接.

我用 google 搜索这相当一段时间,但我只是不相信 '如何' 构成问题,因此,谷歌知道我的意思。我有我在 visual studio 中开发的应用程序。现在它是只是基本的 windows 窗体与搜索文本框中用户输入的数据和搜索按钮,搜索 sql 数据库并返回匹配的行。最终用户正在寻找一个基于输入的学生编号的学生。我与两个假学生有要用测试数据填充了 sql db。当我输入学生的第一个号码,单击搜索时,它正常工作。当我输入第二个数字时,工作正常再次。当我单击搜索与在框中没有数据,以及工作正常并引发我我自定义错误消息。这是当我输入一个无效的数字,另一个不在数据库中,那什么也没有发生。我刚刚成功地检索和清除文本框和将光标放在框中并给予其焦点 (因为它应该) 它仍显示上一条记录但不会告诉我它在数据库中找不到那个特定的号码。我似乎无法弄清楚如何或在哪里在我的代码来解决此问题。

背景: 我是自学编程语言中 (youtube 段,哈佛大学是免费的在线 ' 如何的 ',谷歌,读了许多书,等) 和掌握语法但我没有我要等的基本原理。我爱开发软件作为一种爱好,爱学习如何做到这一点。任何帮助是极为赞赏。只是温柔。:-)

我故意删除敏感信息,替换他们用 '?'。这里是我当前的代码:

    Private Sub SearchButton_Click(sender As System.Object, e As System.EventArgs) Handles SearchButton.Click

    If stunumtxtbox.Text = "" Then
        MsgBox("Please enter a student number.", MsgBoxStyle.Exclamation)
        stunumtxtbox.Select()
    Else
        Try
            con.ConnectionString = "Data Source=?\?;Initial Catalog=?;Persist Security Info=True;User ID=?;Password=?"
            con.Open()
        Catch se As SqlException
            MsgBox(se.Message)
        Finally

            Try
                Dim dt As New DataTable
                Dim ds As New DataSet
                Dim da As New SqlDataAdapter
                ds.Tables.Add(dt)
                da = New SqlDataAdapter("SELECT DISTINCT * FROM Student_Info WHERE studentId = '" & stunumtxtbox.Text & "'", con)
                da.Fill(dt)
                For Each DataRow In dt.Rows
                    If stunumtxtbox.Text = dt.Rows(0)("studentId").ToString Then
                        fnametxtbox.Text = dt.Rows(0)("firstName").ToString
                        mnametxtbox.Text = dt.Rows(0)("midleInitial").ToString
                        lnametxtbox.Text = dt.Rows(0)("lastName").ToString
                        addresstxtbox.Text = dt.Rows(0)("addressStreet").ToString
                        address2txtbox.Text = dt.Rows(0)("addressOption").ToString
                        citytxtbox.Text = dt.Rows(0)("addressCity").ToString
                        statetxtbox.Text = dt.Rows(0)("addressState").ToString
                        ziptxtbox.Text = dt.Rows(0)("addressZip").ToString
                        countrytxtbox.Text = dt.Rows(0)("addressCountry").ToString
                        celltxtbox.Text = dt.Rows(0)("contactcellphone").ToString
                        studentidtxtbox.Text = dt.Rows(0)("studentId").ToString
                    Else
                    End If
                Next
                con.Close()
            Catch se As SqlException
                MsgBox(se.Message)
            Finally
                stunumtxtbox.Clear()
                stunumtxtbox.Select()
                Try
                    StudentNameTextBox.Text = lnametxtbox.Text + "," + " " + fnametxtbox.Text + " " + mnametxtbox.Text
                Catch ex As Exception
                    MsgBox(ex.Message)
                Finally
                    fnamelabel.Visible = True
                    mnamelabel.Visible = True
                    lnamelabel.Visible = True
                    addressstreetlabel.Visible = True
                    address2label.Visible = True
                    addresscitylabel.Visible = True
                    addressstatelabel.Visible = True
                    addressziplabel.Visible = True
                    addresscountrylabel.Visible = True
                    celllabel.Visible = True
                    studentidlabel.Visible = True
                    editbutton.Enabled = True
                    editbutton.Visible = True
                End Try
            End Try
        End Try
    End If
End Sub

解决方法 1:

@DonA 和 @Michael 对我的帮助谢谢你!!使用您的信息和几个更多谷歌,我设计了下面的代码和非常高兴的说它的工作。:-)

Private Sub SearchButton_Click(sender As System.Object, e As System.EventArgs) Handles SearchButton.Click

    If stunumtxtbox.Text = "" Then
        MsgBox("Please enter a student number.", MsgBoxStyle.Exclamation)
        stunumtxtbox.Select()
    Else
        Try
            Using connection As New SqlConnection("Data Source=?;Initial Catalog=?;Persist Security Info=True;User ID=?;Password=?")
                connection.Open()
                Dim dt As New DataTable
                Dim ds As New DataSet
                Dim da As New SqlDataAdapter
                ds.Tables.Add(dt)
                da = New SqlDataAdapter("SELECT DISTINCT * FROM Student_Info WHERE studentId = '" & stunumtxtbox.Text & "'", connection)
                Dim count = da.Fill(dt)

                If count = 0 Then
                    MsgBox("Student ID not found.", MsgBoxStyle.Critical)
                Else
                    fnamelabel.Visible = True
                    mnamelabel.Visible = True
                    lnamelabel.Visible = True
                    addressstreetlabel.Visible = True
                    address2label.Visible = True
                    addresscitylabel.Visible = True
                    addressstatelabel.Visible = True
                    addressziplabel.Visible = True
                    addresscountrylabel.Visible = True
                    celllabel.Visible = True
                    studentidlabel.Visible = True
                    EditStudentToolStripMenuItem.Enabled = True

                    fnametxtbox.Enabled = False
                    fnametxtbox.Visible = True
                    fnametxtbox.BorderStyle = BorderStyle.FixedSingle
                    fnametxtbox.TabStop = True
                    fnametxtbox.TabIndex = 1
                    fnametxtbox.BackColor = Color.White
                    fnametxtbox.ForeColor = Color.Black

                    mnametxtbox.Enabled = False
                    mnametxtbox.Visible = True
                    mnametxtbox.BorderStyle = BorderStyle.FixedSingle
                    mnametxtbox.TabStop = True
                    mnametxtbox.TabIndex = 2
                    mnametxtbox.BackColor = Color.White
                    mnametxtbox.ForeColor = Color.Black

                    lnametxtbox.Enabled = False
                    lnamelabel.Visible = True
                    lnametxtbox.BorderStyle = BorderStyle.FixedSingle
                    lnametxtbox.TabStop = True
                    lnametxtbox.TabIndex = 3
                    lnametxtbox.BackColor = Color.White
                    lnametxtbox.ForeColor = Color.Black

                    addresstxtbox.Enabled = False
                    addresstxtbox.Visible = True
                    addresstxtbox.BorderStyle = BorderStyle.FixedSingle
                    addresstxtbox.TabStop = True
                    addresstxtbox.TabIndex = 4
                    addresstxtbox.BackColor = Color.White
                    addresstxtbox.ForeColor = Color.Black

                    address2txtbox.Enabled = False
                    address2txtbox.Visible = True
                    address2txtbox.BorderStyle = BorderStyle.FixedSingle
                    address2txtbox.TabStop = True
                    address2txtbox.TabIndex = 5
                    address2txtbox.BackColor = Color.White
                    address2txtbox.ForeColor = Color.Black

                    citytxtbox.Enabled = False
                    citytxtbox.Visible = True
                    citytxtbox.BorderStyle = BorderStyle.FixedSingle
                    citytxtbox.TabStop = True
                    citytxtbox.TabIndex = 6
                    citytxtbox.BackColor = Color.White
                    citytxtbox.ForeColor = Color.Black

                    statetxtbox.Enabled = False
                    statetxtbox.Visible = True
                    statetxtbox.BorderStyle = BorderStyle.FixedSingle
                    statetxtbox.TabStop = True
                    statetxtbox.TabIndex = 7
                    statetxtbox.BackColor = Color.White
                    statetxtbox.ForeColor = Color.Black

                    ziptxtbox.Enabled = False
                    ziptxtbox.Visible = True
                    ziptxtbox.BorderStyle = BorderStyle.FixedSingle
                    ziptxtbox.TabStop = True
                    ziptxtbox.TabIndex = 8
                    ziptxtbox.BackColor = Color.White
                    ziptxtbox.ForeColor = Color.Black

                    countrytxtbox.Enabled = False
                    countrytxtbox.Visible = True
                    countrytxtbox.BorderStyle = BorderStyle.FixedSingle
                    countrytxtbox.TabStop = True
                    countrytxtbox.TabIndex = 9
                    countrytxtbox.BackColor = Color.White
                    countrytxtbox.ForeColor = Color.Black

                    celltxtbox.Enabled = False
                    celltxtbox.Visible = True
                    celltxtbox.BorderStyle = BorderStyle.FixedSingle
                    celltxtbox.TabStop = True
                    celltxtbox.TabIndex = 10
                    celltxtbox.BackColor = Color.White
                    celltxtbox.ForeColor = Color.Black

                    studentidtxtbox.Enabled = False
                    studentidtxtbox.Visible = True
                    studentidtxtbox.BorderStyle = BorderStyle.FixedSingle
                    studentidtxtbox.TabStop = True
                    studentidtxtbox.TabIndex = 11
                    studentidtxtbox.BackColor = Color.White
                    studentidtxtbox.ForeColor = Color.Black

                    fnamelabel.Visible = True
                    mnamelabel.Visible = True
                    lnamelabel.Visible = True
                    addressstreetlabel.Visible = True
                    address2label.Visible = True
                    addresscitylabel.Visible = True
                    addressstatelabel.Visible = True
                    addressziplabel.Visible = True
                    addresscountrylabel.Visible = True
                    celllabel.Visible = True
                    studentidlabel.Visible = True
                End If

                For Each DataRow In dt.Rows
                    If stunumtxtbox.Text = dt.Rows(0)("studentId").ToString Then
                        fnametxtbox.Text = dt.Rows(0)("firstName").ToString
                        mnametxtbox.Text = dt.Rows(0)("midleInitial").ToString
                        lnametxtbox.Text = dt.Rows(0)("lastName").ToString
                        addresstxtbox.Text = dt.Rows(0)("addressStreet").ToString
                        address2txtbox.Text = dt.Rows(0)("addressOption").ToString
                        citytxtbox.Text = dt.Rows(0)("addressCity").ToString
                        statetxtbox.Text = dt.Rows(0)("addressState").ToString
                        ziptxtbox.Text = dt.Rows(0)("addressZip").ToString
                        countrytxtbox.Text = dt.Rows(0)("addressCountry").ToString
                        celltxtbox.Text = dt.Rows(0)("contactcellphone").ToString
                        studentidtxtbox.Text = dt.Rows(0)("studentId").ToString
                        StudentNameTextBox.Text = lnametxtbox.Text + "," + " " + fnametxtbox.Text + " " + mnametxtbox.Text
                    Else
                        fnamelabel.Visible = False
                        mnamelabel.Visible = False
                        lnamelabel.Visible = False
                        addressstreetlabel.Visible = False
                        address2label.Visible = False
                        addresscitylabel.Visible = False
                        addressstatelabel.Visible = False
                        addressziplabel.Visible = False
                        addresscountrylabel.Visible = False
                        celllabel.Visible = False
                        studentidlabel.Visible = False
                    End If
                Next
            End Using
        Catch se As SqlException
            MsgBox(se.Message)
        Finally
        End Try
    End If

    stunumtxtbox.Clear()
    stunumtxtbox.Select()

End Sub
官方微信
官方QQ群
31647020