CODEWITHSUNDEEP
sqlsql-servervb.net
aspguy
aspguy

Reputation:

SQL read data from table in vb

I'm trying to get a single field back from the data. (I am searching by a primary key so I should get 0 or 1 answer). Please help. The table that I am querying has one entry with user = someone, input to several columns with the ans column having "a good answer"

Code:

Dim reader As SqlDataReader
Dim par As SqlParameter
Dim result As String
   
Dim sqlconn As SqlConnection
sqlconn = New SqlConnection("....")
sqlconn.Open()

Dim sqlcmd As SqlCommand
sqlcmd = New SqlCommand("Select Ans From Test Where User = @auser", sqlconn)

par = New SqlParameter
par.ParameterName = "auser"
par.Value = Textbox1.Text

sqlcmd.Parameters.Add(par)
  
reader = sqlcmd.ExecuteReader()

result = reader.GetString(0)

''//output to label
label1.Text = result

Upvotes: 1

Views: 6789

Answers (2)

John MacIntyre
John MacIntyre

Reputation: 13021

You need to read the data reader first to place it on the first row.

So instead of 

reader = sqlcmd.ExecuteReader()    
result = reader.GetString(0)

You'd insert the Read() method like so:

reader = sqlcmd.ExecuteReader()    
if reader.Read() then '' <<<<< newly inserted code
    result = reader.GetString(0)
end if

Upvotes: 4

Joel Coehoorn
Joel Coehoorn

Reputation: 415600

''// using statement will guarantee the object is closed and disposed
''// even if an exception occurs
Using sqlconn As New SqlConnection("...."), _
      sqlcmd As  New SqlCommand("Select Ans From Test Where User = @auser", sqlconn)

    ''// you can create, add, and set the value for a parameter all on one line
    sqlcmd.Parameters.Add("@auser", SqlDbType.VarChar, 50).Value = Textbox1.Text

    ''//wait as long as possible to open the connection
    sqlconn.Open() 

    ''// if you're only getting the first column of the first row, use execute scalar
    label1.Text = CString(sqlcmd.ExecuteScalar())

End Using

Upvotes: 0

Related Questions