Odd problem with ADODB.Recordset

[vector9]

Private Sub cmdViewSeats_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdViewSeats.Click
        Dim strSeatType As String = cbxSeating.Text
        Dim intGame As Integer = Val(cbxGame.Text)
        Dim objRS As New ADODB.Recordset
        Dim objRS2 As New ADODB.Recordset
        Dim strSQL As String
        Dim strSQL2 As String
 
 
        strSQL = "SELECT Section FROM SectionPricing WHERE Description='" & cbxSeating.Text & "'"
        objRS.Open(strSQL, strConnect)
 
        Do Until objRS.EOF
            strSQL2 = "SELECT SeatNumber FROM Seats WHERE sections='" & objRS.Fields("Section").Value & "'"
            objRS2.Open(strSQL2, strConnect)
            objRS.MoveNext()
            Debug.WriteLine(objRS2.Fields("SeatNumber").Value)
        Loop
    End Sub

it breaks at objRS.Open(strSQL,strConnect)
strConnect is declared globaly as

Dim strConnect As String = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source=Metro Meteors.mdb"

and that is right because i use it more then once and the times before it work..... i just dont understand why its not working for this new module and its not making sense.....if it works before...why not again???? remember it breaks at that line so its not connecting.....and the SQL statement works for sure cuz i also use that before......

[Grimfort]

It would be mostly in your favour to actually post what the error is .

"if it works before...why not again????"

^^ Its called the effect of the Copy+Paste coder, just because it worked somewhere else dont mean it will work when you move it. Theres literally 1000s of reasons and ways even the most simplistic things can go wrong, which is why these forums have 45 thousand members.

[vector9]

An unhandled exception of type 'System.Runtime.InteropServices.COMException' occurred in Metro Meteors Ticket Sales.exe

Additional information: Unspecified error

[vector9]

when ever i make the strSQL a simple sql statement like "SELECT * FROM SectionPricing" it works......untill it reaches the next recordset opening......i guess its the way im creating the SQL....but what though...????

[vector9]

sorry but i didnt copy paste anything.... i just used the same reasoning from my sub above which works fine to populate a listbox from another RS.

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
 
        'On program load access the database and populate the seating combo box with the seating types
        Dim objRS As New ADODB.Recordset
 
        Dim strSQL As String
        'Set the connection string
        strConnect = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source=Metro Meteors.mdb"
        'Use SQL statement to access and read the seating types from the PriceSchedule Table
        strSQL = "SELECT SeatingType FROM PriceSchedule"
        'Fill the record set with the data
        objRS.Open(strSQL, strConnect)
        Do Until objRS.EOF
            'Populate the ComboBox with the results
            cbxSeating.Items.Add((objRS("SeatingType").Value))
            objRS.MoveNext()
        Loop
        Dim i As Integer
        For i = 1 To 10
            cbxGame.Items.Add(i)
        Next
        objRS.Close()
 
    End Sub

[Grimfort]

Well, if this one works

[code]
strSQL = "SELECT SeatingType FROM PriceSchedule"
[.code]

And this one doesnt

strSQL = "SELECT Section FROM SectionPricing WHERE Description='" & cbxSeating.Text & "'"

Then first of all, try replacing the textbox with a constant value that you KNOW exists, and check that the field name description is corrent and check that its the correct format and not a memo field, which you cant use for any kind of decent querys.

strSQL = "SELECT Section FROM SectionPricing WHERE Description='Test'"

[nswan]

try putting square brackets around your table name and field names. You might have chosen one that is an ado key word or something

strSQL = "SELECT [Section] FROM SectionPricing WHERE [Description]='Test'"

[vector9]

thanks nswan that did the trick. and my one objrs2 statement didnt work ....but that was do to a value type mismatch which i quickly took care of.