在 vb.net 中获取单值表单数据库

fetching single value form database in vb.net

提问by Parth Akbari

this my database table

这是我的数据库表

id name

身名称

1   abc
2    xyz
it i enter 1 then respective value "abc" display in different text box???

1 abc
2 xyz
我输入 1 然后相应的值“abc”显示在不同的文本框中？？？

Private Sub butsea_Click(sender As Object, e As EventArgs) Handles butsea.Click

    Dim dset As New DataSet
    Dim da As SqlDataAdapter
    Dim myCmd As New SqlCommand

    Try
   myConn.ConnectionString = "Data Source=THEONE\PARTH;Initial Catalog=testdatabase;Integrated Security=True;"
        myConn.Open()

        Dim avalue As String = (InputBox("Input Student Id", "Search Student")).ToString
        txt_id.Text = avalue
        da = New SqlDataAdapter("SELECT * FROM studentdetails where student_id= '" & txt_id.Text & "", myConn)
        If dset.Tables(0).Rows.Count > 0 Then
           'what should i write here
        Else
            MsgBox("No Record Found")
        End If

    Catch ex As Exception
        MsgBox(ex.Message)
    Finally
        myConn.Close()
    End Try

End Sub

回答by Yuriy Galanter

If you need to fetch just a single value - using DataAdapter and DataSet is overkill. Use SqlCommand.ExecuteScalarmethod and in your query instead of "SELECT * ..." do a "SELECT YOURFIELD ... ".

如果您只需要获取一个值 - 使用 DataAdapter 和 DataSet 就太过分了。使用SqlCommand.ExecuteScalar方法并在您的查询中而不是“SELECT * ...”执行“SELECT YOURFIELD ...”。

this way you don't have to jump thru hoops building a dataset, checking number of rows etc. and just check returned value for Nothing instead.

这样您就不必费力地构建数据集、检查行数等，而只需检查返回值的 Nothing 即可。

UPDATEHere is your code modified to use ExecuteScalar

更新这是您修改为使用 ExecuteScalar 的代码

Private Sub butsea_Click(sender As Object, e As EventArgs)  Handles butsea.Click

        Dim myCmd As SqlCommand
        Dim myConn As New SqlConnection
        Dim oResult As Object

        Try
            myConn.ConnectionString = "Data Source=THEONE\PARTH;Initial Catalog=testdatabase;Integrated Security=True;"
            myConn.Open()

            Dim avalue As String = (InputBox("Input Student Id", "Search Student")).ToString
            txt_id.Text = avalue

            myCmd = New SqlCommand("SELECT student_name FROM studentdetails where student_id= '" & txt_id.Text & "'", myConn)
            oResult = myCmd.ExecuteScalar()

            If oResult IsNot Nothing Then
                txt_name.text = oResult.ToString
            Else
                MsgBox("No Record Found")
            End If

        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            myConn.Close()
        End Try
End Sub

This code assumes that database field that u need to display is called student_nameand that you added TextBox called txt_nameto your form.

此代码假定student_name您需要显示的数据库字段已被调用，并且您已将 TextBox 添加txt_name到表单中。

回答by Steve

You need to add other textboxes for every field you want to show.
(For example a textbox called txtStudentName could be used for the name, and so on for other fields present in the table studentdetails).

您需要为要显示的每个字段添加其他文本框。
（例如，一个名为 txtStudentName 的文本框可用于名称，等等用于表中的其他字段studentdetails）。

The correct way to query your database (leaving out other methods like using a SqlDataReader) should be the following

查询数据库的正确方法（不考虑使用 SqlDataReader 等其他方法）应该如下

  Dim dset As New DataSet
  Dim da As SqlDataAdapter

Try
    myConn.ConnectionString = "Data Source=THEONE\PARTH;Initial Catalog=testdatabase;Integrated Security=True;"
    myConn.Open()
    Dim avalue As String = (InputBox("Input Student Id", "Search Student")).ToString
    txt_id.Text = avalue

    ' prepare the adapter with a commandtext. Do not use string concatenation from user input'
    da = New SqlDataAdapter("SELECT * FROM studentdetails where student_id=@id", myConn)
    da.SelectCommand.Parameters.AddWithValue("@id", txt_id.Text)

    ' Fill the dataset'
    da.Fill(dset)
    If dset.Tables(0).Rows.Count > 0 Then
        ' Supposing you have a field for the studentname in the first column of the returned
        ' datatable rows(rowindex)(columnindex)
        txtStudentName.Txt = dset.Tables(0).Rows(0)(0).ToString()
        .....
        ' Set the text property of other textboxes for other fields to show'
    Else
        MsgBox("No Record Found")
    End If