How to get a Single Record's Data from SQL server

11 Nov 2005 4:55 PM

Kirk

I currently have a VB.NET application that gets record information from
a SQL database. When it finds this information, it is placed into a
dataset. I know I will only get one record because I am querying based
on unique record ID, but I still have to get the first item in the
dataset (because the dataset doesn't "know" that it only has one item):

myDataAdapter.Fill(myDataSet)
If myDataSet.Tables(0).Rows.Count < 1 Then 'No records were found
      MsgBox("A record with ID# " & gCurRecord & " could not be
found.", MsgBoxStyle.Critical, "RECORD NOT FOUND")
      Exit Sub
Else 'Records were found..
      With Me
           .txtID.Text = myDataSet.Tables(0).Rows(0).Item("ID")

I then populate a form with information from the one record in the
dataset.

I remember back in my VB6 days that there was a "seek" command that
would find one specific record, not an entire dataset. I have seen
some discussions of methods that are like this, but they all envolve
updating a single record. I don't want to update a record, I just want
to get all the data for a single record. Is there a method for doing
this?

I would greatly appreciate any suggestions or sample code that anyone
might be able to give me.
Thanx!

11 Nov 2005 5:10 PM

Tony Rogerson

Use a SQLDataReader instead....

Dim oDBConn As New
SqlConnection(ConfigurationSettings.AppSettings("DBConnection"))

oDBConn.Open()

Dim cmdSQL As SqlClient.SqlCommand

Dim drSQL As SqlDataReader

cmdSQL = New SqlCommand("ukug3_selGetMemberGroup_Details", oDBConn)

cmdSQL.CommandType = CommandType.StoredProcedure

cmdSQL.Connection = oDBConn

cmdSQL.Parameters.Add(New SqlParameter("@member_group_id",
Me.MemberGroupId))

drSQL = cmdSQL.ExecuteReader

drSQL.Read()

lblMemberGroup.Text = drSQL("member_group_name")

oDBConn.Close()

oDBConn.Dispose()

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials

Show quote

"Kirk" <lok***@hotmail.com> wrote in message
news:1131728112.571724.162410@g14g2000cwa.googlegroups.com...
>I currently have a VB.NET application that gets record information from
> a SQL database. When it finds this information, it is placed into a
> dataset. I know I will only get one record because I am querying based
> on unique record ID, but I still have to get the first item in the
> dataset (because the dataset doesn't "know" that it only has one item):
>
> myDataAdapter.Fill(myDataSet)
> If myDataSet.Tables(0).Rows.Count < 1 Then 'No records were found
>      MsgBox("A record with ID# " & gCurRecord & " could not be
> found.", MsgBoxStyle.Critical, "RECORD NOT FOUND")
>      Exit Sub
> Else 'Records were found..
>      With Me
>           .txtID.Text = myDataSet.Tables(0).Rows(0).Item("ID")
>
> I then populate a form with information from the one record in the
> dataset.
>
> I remember back in my VB6 days that there was a "seek" command that
> would find one specific record, not an entire dataset. I have seen
> some discussions of methods that are like this, but they all envolve
> updating a single record. I don't want to update a record, I just want
> to get all the data for a single record. Is there a method for doing
> this?
>
> I would greatly appreciate any suggestions or sample code that anyone
> might be able to give me.
> Thanx!
>

11 Nov 2005 8:32 PM

Kirk

Tony,

That worked well for me. I am not using a stored procedure, but I was
able to modify it to return my one record.

Thanks for putting me on the right track!