How to return multiple values from a query vb.net

0
votes

one question how to store or return multiple queries result values into multiple variables.. I'm using a query that return 4 columns but how to.. individual store those results into 4 separate variables.. here is my code

Private Sub FrmAlumnos_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load txtCurrentUser.Text = Login.txtUser.Text

    Dim strsql As String
    strsql = "SELECT ""Agregar"", ""Modificar"", ""Eliminar"", ""Imprimir"" FROM ""Seguridad"".""GrupoPantallas"" WHERE ""IdGrupo"" = (SELECT ""IdGrupo"" FROM ""Seguridad"".""Users"" WHERE ""IdUsers"" = '" _
        + Me.txtCurrentUser.Text + "') AND ""IdPantalla"" = '" + Me.Name + "'"
    Try
        Using conexion As New Devart.Data.PostgreSql.PgSqlConnection(My.Settings.CNX_Principal)
            Dim comando As New Devart.Data.PostgreSql.PgSqlCommand(strsql, conexion)
            conexion.Open()
            Dim registro As Devart.Data.PostgreSql.PgSqlDataReader = comando.ExecuteReader
            If comando.ExecuteReader.Item(0) = 0 Then
                btnNew.Visible = False
            End If
            If comando.ExecuteReader.Item(1) = 0 Then
                btnEdit.Visible = False
            End If
            If comando.ExecuteReader.Item(2) = 0 Then
                btnDelete.Visible = False
            End If
            If comando.ExecuteReader.Item(3) = 0 Then
                btnPrint.Visible = False
            End If
        End Using

    Catch ex As Exception

    End Try
End Sub

I'm Using PostgreSQL just for you to know...

3
vb.netpostgresql
Why don't you make 4 queries? - Deblaton Jean-Philippe
Yes right know I have 4 queries one for each, but isn't there a way to do it in just one? thnx for reply - Damian Nuñez
1 query = 1 object. So, if you only want 1 query, you create an object, and then, you "split" this object into 4 new objects. - Deblaton Jean-Philippe
ExecuteReader returns a data reader. Just use that. - Chris Dunaway
but how do I compare the first element and then the second and then the third - Damian Nuñez

3 Answers

0
votes

I think you might find a DataSet to be useful here. Something like:

Dim ds As New DataSet
Dim com As New SqlCommand
com.Connection = <yourconnectionstring>
com.CommandType = CommandType.Text
com.CommandText = "YOURSQLSTUFF"
Dim da As New DataAdapter
da.SelectCommand = com

da.Fill(ds)
ds.Tables(0).TableName = "FirstTable"
ds.Tables(0).PrimaryKey = New DataColumn() {ds.Tables(0).Columns("primaryKeyOfFirstTable")
ds.Tables(1).TableName = "SecondTable"
ds.Tables(1).PrimaryKey = New DataColumn() {ds.Tables(1).Columns("primaryKeyOfSecondTable")

Hope that helps!

-sf

EDIT: After some more searching, I found this link, which might help you out! It's postgreSQL specific!

0
votes

You need to use the Read method of the DataReader:

Dim strsql As String
strsql = "SELECT ""Agregar"", ""Modificar"", ""Eliminar"", ""Imprimir"" FROM ""Seguridad"".""GrupoPantallas"" WHERE ""IdGrupo"" = (SELECT ""IdGrupo"" FROM ""Seguridad"".""Users"" WHERE ""IdUsers"" = '" _
    + Me.txtCurrentUser.Text + "') AND ""IdPantalla"" = '" + Me.Name + "'"
Try
    Using conexion As New Devart.Data.PostgreSql.PgSqlConnection(My.Settings.CNX_Principal)
        Dim comando As New Devart.Data.PostgreSql.PgSqlCommand(strsql, conexion)
        conexion.Open()
        Using registro As Devart.Data.PostgreSql.PgSqlDataReader = comando.ExecuteReader()
            //Assuming that there is only a single row returned
            If registro.Read()
                btnNew.Visible = registro.GetBoolean(0)
                btnEdit.Visible = registro.GetBoolean(1)
                btnDelete.Visible = registro.GetBoolean(2)
                btnPrint.Visible = registro.GetBoolean(3)
            End While
        End Using
    End Using
Catch ex As Exception

End Try

You should also look into using parameters. It would make the code a little cleaner than using a concatenated string and would stop sql injection attacks.

-1
votes
Dim strsql As String
strsql = "SELECT ""Agregar"", ""Modificar"", ""Eliminar"", ""Imprimir"" FROM ""Seguridad"".""GrupoPantallas"" WHERE ""IdGrupo"" = (SELECT ""IdGrupo"" FROM ""Seguridad"".""Users"" WHERE ""IdUsers"" = '" _
    + Me.txtCurrentUser.Text + "') AND ""IdPantalla"" = '" + Me.Name + "'"
Try
    Using conexion As New Devart.Data.PostgreSql.PgSqlConnection(My.Settings.CNX_Principal)
        Dim comando As New Devart.Data.PostgreSql.PgSqlCommand(strsql, conexion)
        conexion.Open()
        Dim registro As Devart.Data.PostgreSql.PgSqlDataReader = comando.ExecuteReader
        //This is the loop that you missed
        While registro.Read()
            If comando.ExecuteReader.Item(0) = 0 Then
                btnNew.Visible = False
            End If
            If comando.ExecuteReader.Item(1) = 0 Then
                btnEdit.Visible = False
            End If
            If comando.ExecuteReader.Item(2) = 0 Then
                btnDelete.Visible = False
            End If
            If comando.ExecuteReader.Item(3) = 0 Then
                btnPrint.Visible = False
            End If
        End While
    End Using

Catch ex As Exception

End Try

I'm not sure if this is what you're trying to do, but all you have to do is loop through the DataReader as shown above.