DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: ADO Recordsets

  1. #1
    Rick Steiner Guest

    ADO Recordsets


    I am trying to return fields from an ado recordset based on the value of the
    controlling textbox.

    SubroutineX()
    '
    Dim strSQL As String
    Dim Min, Max As String
    '
    strSQL = "SELECT Min, Max FROM PayBands WHERE PayBand = " & txtPayBand2.Text
    '
    datPayBandsRS.Recordset.Open strSQL
    '
    txtSalary2 = "$" & datPayBandsRS.Recordset("Min") & " - $" & datPayBandsRS.Recordset("Max")
    '
    End Sub

    I get errors like "Object variable or WITH block variable not set" or Methods
    not defined. I know this should not be this hard. I am pretty sure I have
    set up the ADO properties correctly.

    Anyone have any suggestions?
    Thanks

  2. #2
    Roy Guest

    Re: ADO Recordsets

    There could be a few reasons for that:
    1. datPayBandsRS isn't defined as ADODB.Recordset
    2. datPayBandsRS isn't SET to New ADODB.Recordset as follows:
    Set datPayBandsRS = New ADODB.Recordset
    Try posting ALL code related to this problem.

    Roy

    "Rick Steiner" <steinerrw@yahoo.com> wrote in message news:3ce2738a$1@10.1.10.29...
    >
    > I am trying to return fields from an ado recordset based on the value of the
    > controlling textbox.
    >
    > SubroutineX()
    > '
    > Dim strSQL As String
    > Dim Min, Max As String
    > '
    > strSQL = "SELECT Min, Max FROM PayBands WHERE PayBand = " & txtPayBand2.Text
    > '
    > datPayBandsRS.Recordset.Open strSQL
    > '
    > txtSalary2 = "$" & datPayBandsRS.Recordset("Min") & " - $" & datPayBandsRS.Recordset("Max")
    > '
    > End Sub
    >
    > I get errors like "Object variable or WITH block variable not set" or Methods
    > not defined. I know this should not be this hard. I am pretty sure I have
    > set up the ADO properties correctly.
    >
    > Anyone have any suggestions?
    > Thanks




  3. #3
    Dennis Guest

    Re: ADO Recordsets


    If the PayBand value is a string, you have to enclose your literal with single
    quotes.

    strSQL = "SELECT Min, Max FROM PayBands WHERE PayBand = '" & PayBand2.Text
    & "'"

    Is that the case?

    "Roy" <iroy55@hotmail.com> wrote:
    >There could be a few reasons for that:
    >1. datPayBandsRS isn't defined as ADODB.Recordset
    >2. datPayBandsRS isn't SET to New ADODB.Recordset as follows:
    > Set datPayBandsRS = New ADODB.Recordset
    >Try posting ALL code related to this problem.
    >
    >Roy
    >
    >"Rick Steiner" <steinerrw@yahoo.com> wrote in message news:3ce2738a$1@10.1.10.29...
    >>
    >> I am trying to return fields from an ado recordset based on the value

    of the
    >> controlling textbox.
    >>
    >> SubroutineX()
    >> '
    >> Dim strSQL As String
    >> Dim Min, Max As String
    >> '
    >> strSQL = "SELECT Min, Max FROM PayBands WHERE PayBand = " & txtPayBand2.Text
    >> '
    >> datPayBandsRS.Recordset.Open strSQL
    >> '
    >> txtSalary2 = "$" & datPayBandsRS.Recordset("Min") & " - $" & datPayBandsRS.Recordset("Max")
    >> '
    >> End Sub
    >>
    >> I get errors like "Object variable or WITH block variable not set" or

    Methods
    >> not defined. I know this should not be this hard. I am pretty sure I have
    >> set up the ADO properties correctly.
    >>
    >> Anyone have any suggestions?
    >> Thanks

    >
    >



  4. #4
    Jim Keller Guest

    Re: ADO Recordsets


    Rick try this instead of what you originally posted. The changes are as follows:
    dimensioned and set object for recordset, assumed field names are min and
    max respectively, if not, replace field names in line that sets value of
    txtSalary2. You may need to qualify your reference to txtPayBand2 if the
    underlying field type does not support implicit conversion.

    HTH,

    Jim

    SubroutineX()
    '
    Dim strSQL As String
    'Dim Min, Max As String - Don't need this if the field names are "min" and
    "max"
    Dim datPayBandsRS as New ADODB.Recordset 'This takes care of the SET statement
    too.
    '
    strSQL = "SELECT Min, Max FROM PayBands WHERE PayBand = " & txtPayBand2.Text
    '
    datPayBandsRS.Recordset.Open strSQL
    '
    txtSalary2 = "$" & datPayBandsRS.fields("Min") & " - $" & datPayBandsRS.fields("Max")
    '
    End Sub



    "Dennis" <vb.@127.0.0.1> wrote:
    >
    >If the PayBand value is a string, you have to enclose your literal with

    single
    >quotes.
    >
    >strSQL = "SELECT Min, Max FROM PayBands WHERE PayBand = '" & PayBand2.Text
    >& "'"
    >
    >Is that the case?
    >
    >"Roy" <iroy55@hotmail.com> wrote:
    >>There could be a few reasons for that:
    >>1. datPayBandsRS isn't defined as ADODB.Recordset
    >>2. datPayBandsRS isn't SET to New ADODB.Recordset as follows:
    >> Set datPayBandsRS = New ADODB.Recordset
    >>Try posting ALL code related to this problem.
    >>
    >>Roy
    >>
    >>"Rick Steiner" <steinerrw@yahoo.com> wrote in message news:3ce2738a$1@10.1.10.29...
    >>>
    >>> I am trying to return fields from an ado recordset based on the value

    >of the
    >>> controlling textbox.
    >>>
    >>> SubroutineX()
    >>> '
    >>> Dim strSQL As String
    >>> Dim Min, Max As String
    >>> '
    >>> strSQL = "SELECT Min, Max FROM PayBands WHERE PayBand = " & txtPayBand2.Text
    >>> '
    >>> datPayBandsRS.Recordset.Open strSQL
    >>> '
    >>> txtSalary2 = "$" & datPayBandsRS.Recordset("Min") & " - $" & datPayBandsRS.Recordset("Max")
    >>> '
    >>> End Sub
    >>>
    >>> I get errors like "Object variable or WITH block variable not set" or

    >Methods
    >>> not defined. I know this should not be this hard. I am pretty sure I

    have
    >>> set up the ADO properties correctly.
    >>>
    >>> Anyone have any suggestions?
    >>> Thanks

    >>
    >>

    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center