How to return multiple values from a query vb.net

Question

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...

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
– Damian Nuñez, Commented Apr 10, 2012 at 15:00
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
– Deblaton Jean-Philippe, Commented Apr 10, 2012 at 15:03
but how do I compare the first element and then the second and then the third — Damian Nuñez
– Damian Nuñez, Commented Apr 10, 2012 at 15:27

sacredfaith · Accepted Answer · 2012-04-10 15:28:37Z

0

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!

edited Apr 10, 2012 at 15:28

answered Apr 10, 2012 at 15:14

sacredfaith

8801 gold badge8 silver badges23 bronze badges

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

1 Comment

sacredfaith Over a year ago

Of course...if postgreSQL doesn't support this kind of syntax then I guess it won't be of much use. Perhaps this could lead you on to do something similar!

Chris Dunaway · Accepted Answer · 2012-04-11 14:04:32Z

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.

jaressloo · Accepted Answer · 2012-04-10 16:56:32Z

-1

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.

answered Apr 10, 2012 at 16:56

jaressloo

2103 silver badges14 bronze badges

3 Comments

Chris Dunaway Over a year ago

But you're still sending 4 queries to the database when the ExecuteReader method is called!

jaressloo Over a year ago

Yes, you're right. I meant to change the line that says comando.ExecuteReader.Item(0) = 0 To registro(0) = 0 or whatever the field is that you're looking for. Basically...once you run the ExecuteReader, just use the reader object (registro) instead of calling "ExecuteReader" 4 times after that. Hope that makes sense.

jaressloo Over a year ago

Mine, of course, is assuming that you are returning more than one record.

Collectives™ on Stack Overflow

How to return multiple values from a query vb.net

3 Answers 3

1 Comment

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related