none
Passing a query return value to a variable. RRS feed

  • Question

  • I thought this would be easy, but apparently for me it isn't.

    I have a query that counts the number of filtered rows and returns that number:

    SELECT COUNT(*) 
    FROM lkpMBUser
    WHERE (strUserDept = ?) AND (blnActive = TRUE)

    I have tested this query and it does return the number users after the filter is applied.  So I know that part works

    I had thought that I could pass the value to a variable using the following code:

    intRowCount = CInt(Me.LkpMBUserTableAdapter.qryReturnCount, glbstrDepartment)

    However, it does not work and I get see the following statement in the tool tip:

    qryReturnCount(strUserDept As String) As Integer?

    Now since I already know that the query works then I am obviously, to me, miscalling the query.  How should this be done?


    gwboolean

    Monday, February 20, 2017 7:23 PM

Answers

  • Neda,

    So there is no way that I can simply make variable = to a query call to return a value?

    Basically I get the idea that what I was trying to do is BS and I need to go back to the drawing board?

    I think I understand what you did and will try it out and see how it goes. 

    Thanks.


    gwboolean


    • Edited by gwboolean Tuesday, February 21, 2017 5:26 AM
    • Marked as answer by gwboolean Tuesday, February 21, 2017 6:15 AM
    Tuesday, February 21, 2017 5:26 AM
  • Try this too:

    intRowCount = If(Me.LkpMBUserTableAdapter.qryReturnCount(glbstrDepartment), 0)

    • Marked as answer by gwboolean Tuesday, February 21, 2017 6:15 AM
    Tuesday, February 21, 2017 5:57 AM

All replies

  • Hi gwboolean,

    Thank you for posting here.

    Based on my understanding, you'd like to query the total number of conditions from the database, and  assign the total number to a variable. I would suggest that you could use sqlCommand.ExecuteScalar() to do this.

    Check and refer the following code snippet:

    Dim constr As String
    Dim count As Integer = 0
    Dim con As New sqlConnection(constr)
    Dim sql As String = "select count(*) from 1kpMBUser where strUserDept=? and blnActive=true"
    Dim cmd As New sqlcommand(sql, con)
    
    Try
                    con.open()
                    count = CInt(cmd.ExecuteScalar())
    Catch generatedExceptionName As Exception
                    Throw
    Finally
                    con.close()
    End Try
    

    Hope it is helpful to you.

    Best Regards,

    Neda Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, February 21, 2017 4:45 AM
    Moderator
  • Neda,

    So there is no way that I can simply make variable = to a query call to return a value?

    Basically I get the idea that what I was trying to do is BS and I need to go back to the drawing board?

    I think I understand what you did and will try it out and see how it goes. 

    Thanks.


    gwboolean


    • Edited by gwboolean Tuesday, February 21, 2017 5:26 AM
    • Marked as answer by gwboolean Tuesday, February 21, 2017 6:15 AM
    Tuesday, February 21, 2017 5:26 AM
  • Neda,

    OK, I am working what you coded into my routine.

    Can you clarify for me the sqlConnection and what should go there?

    Can you clarify for me the sqlcommand and what should go there?

    I think I understand everything else you have there.


    gwboolean

    Tuesday, February 21, 2017 5:40 AM
  • Try this too:

    intRowCount = If(Me.LkpMBUserTableAdapter.qryReturnCount(glbstrDepartment), 0)

    • Marked as answer by gwboolean Tuesday, February 21, 2017 6:15 AM
    Tuesday, February 21, 2017 5:57 AM
  • Thanks Viorel, that works perfectly.  It also fits what I was trying to do.

    gwboolean

    Tuesday, February 21, 2017 6:15 AM
  • Hi gwboolean,

    From sqlConnection class and sqlCommand class, you can know that sqlConnection is used to open the SQL Server database and sqlCommand is used to query data.

    I find this video that may help you to know database.

    Best Regards,

    Neda Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, February 21, 2017 6:32 AM
    Moderator