0

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
Improve this question

2 Answers 2

Reset to default
3

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

I've updated my answer with code example. It could be further improved, e.g. it is not recommended to add query parameter to string inline (Google "SQL Injection") so, as @Steve answer suggests you can use SqlParameter to add parameter to query.
yep it working,now if i add another column and i want to access that value.then i can use same method OR i need to writing different approach ???@Yuriy Galante
Your post mentioned Single value only, but if you want to retrieve multiple columns, you need to look ExecuteReader instead.
1

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
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.