CODEWITHSUNDEEP
vb.netsql-server-2012-express
Parth Akbari
Parth Akbari

Reputation: 651

fetching single value form database in vb.net

this my database table

id name

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

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

Upvotes: 0

Views: 10045

Answers (2)

suff trek
suff trek

Reputation: 39777

If you need to fetch just a single value - using DataAdapter and DataSet is overkill. Use SqlCommand.ExecuteScalar method and in your query instead of "SELECT * ..." do a "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.

UPDATE Here is your code modified to use 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_name and that you added TextBox called txt_name to your form.

Upvotes: 3

Steve
Steve

Reputation: 216292

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).

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

  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

Upvotes: 1

Related Questions