CODEWITHSUNDEEP
vb.net
user2650277
user2650277

Reputation: 6729

Storing multiple values from SQL Server statement

Consider the following code

 Dim cmd As New SqlCommand("SELECT * FROM [UserDetail].[User] where UserName = @username and UserPass = @password", con)
 cmd.Parameters.AddWithValue("@username", login_username.Text)
 cmd.Parameters.AddWithValue("@password", hash_pass)

 Dim da As New SqlDataAdapter(cmd)

Suppose that there exist a column name status I want to store the result of status in a variable.

P.S I am a beginner with VB.NET so please bear with me

Upvotes: 0

Views: 458

Answers (2)

Vivek S.
Vivek S.

Reputation: 21885

If status returns a single cell value(single row in status column) then you can use following method

Dim cmd As New SqlCommand("SELECT * FROM [UserDetail].[User] where UserName = @username and UserPass = @password", con)
cmd.Parameters.AddWithValue("@username", login_username.Text)
cmd.Parameters.AddWithValue("@password", hash_pass)
Dim status As String
status = IIf(IsDBNull(cmd.ExecuteScalar), "Not Available", cmd.ExecuteScalar)
        'IsDBNull is used tocheck whether cmd.ExecuteScalar is null or what
        'IIF() is used to handle null here, You can assign custom value for status variable if
        'select returns null value
        'If you dont need to use them its possible to write
        ' status = cmd.ExecuteScalar

See more about ExecuteScalar()

Upvotes: 0

haraman
haraman

Reputation: 2742

You can execute the statement on a SqlDataReader or fill DataTable. An example with SqlDataReader is

Dim reader As SqlDataReader
Dim cmd As New SqlCommand("SELECT * FROM [UserDetail].[User] where UserName =@username and UserPass=@password", con)
cmd.Parameters.AddWithValue("@username", login_username.Text)
cmd.Parameters.AddWithValue("@password", hash_pass)
reader = cmd.ExecuteReader()
Dim strStatus as String = ""
If reader.HasRows Then
    reader.Read()
    strStatus = reader.Item("status").ToString
End If

Here is the DataTable version

Dim cmd As New SqlCommand("SELECT * FROM [UserDetail].[User] where UserName =@username and UserPass=@password", con)
cmd.Parameters.AddWithValue("@username", login_username.Text)
cmd.Parameters.AddWithValue("@password", hash_pass)
Dim da As SqlDataAdapter = New SqlDataAdapter()
Dim dt As DataTable = New DataTable("TableA")
da.SelectCommand = cmd
da.Fill(dt)
Dim strStatus as String = ""
'you can process the DataTable in a for/for each loop or process a single row as follows
If dt.Rows.Count > 0 Then
    strStatus = dt.Rows(0).Item("status").ToString()
End If

Upvotes: 1

Related Questions