help with code vb .net 9

[spider661]

im trying to get code i used in vb with .net i got the new vs 2008 and i cant get anything working right. i got the database connection going its not giving me an error on connection but its giveing me an error trying to read info from the database. here is the code

Imports System.Data.Odbc

Module Module1
    Friend ProcType As Integer
    Friend eq_host As String
    Friend eq_user As String
    Friend eq_password As String
    Friend eq_dbname As String
    Friend sql As String
    Friend rs As ADODB.Recordset
    Friend conn As ADODB.Connection
    Friend r As Long
    Friend sql2 As String

    Public Function OPEN_ODBC_EQ_TABLE(ByVal rs As ADODB.Recordset, ByVal conn As ADODB.Connection)
        On Error GoTo erxx
        conn = New ADODB.Connection
        conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" _
                    & "SERVER=" & eq_host & ";" _
                    & "DATABASE=" & eq_dbname & ";" _
                    & "UID=" & eq_user & ";" _
                    & "PWD=" & eq_password & ";" _
                    & "OPTION=" & 1 + 2 + 8 + 32 + 2048 + 16384

        'eq_host, eq_user, eq_password, eq_dbname
        conn.CursorLocation = CursorLocationEnum.adUseClient : conn.Open()
        rs = New ADODB.Recordset
        Exit Function
erxx:
        MsgBox("MYSQL Connection FAILED!- Check db.ini and did you install my ODBC drivers?")
        On Error GoTo 0
        End
    End Function

    Function find_next_available_item_id()
        Dim new_id As Integer
        eq_host = "***.***.***.***"
        eq_user = "myuser"
        eq_password = "mypass"
        eq_dbname = "peq"

        Dim rs2 As New ADODB.Recordset

        Call OPEN_ODBC_EQ_TABLE(rs, conn)

        For i = 1001 To 65535
            sql = "SELECT id FROM items WHERE id='" & i & "'"
            rs2.Open(sql, conn, CursorTypeEnum.adOpenStatic, LockTypeEnum.adLockOptimistic)
            If rs2.RecordCount = 0 Then new_id = i : rs2.Close() : Exit For
            rs2.Close()
        Next i

        find_next_available_item_id = new_id


    End Function

the line im getting the error with is.
rs2.Open(sql, conn, CursorTypeEnum.adOpenStatic, LockTypeEnum.adLockOptimistic)

the error is
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

error code is -2146825287

i get the error anytime i call the function find_next_available_item_id

[Hack]

Welcome to DevX

i represents a numeric value, correct?

If so, then you need to lose the single quotes around your variable in your query

'this 
sql = "SELECT id FROM items WHERE id='" & i & "'"
'should be
sql = "SELECT id FROM items WHERE id = " & i

[spider661]

thanks for the welcome.

i tried it but it did not work same error.

the statement should be right if im not mistaken.
if i where to do the same in an sql query it would read out to

SELECT id FROM items WHERE id='1001'

thats how i would run it in the database itself.

but just in case was wrong i tried it to be sure and still no go.

the actuall line thats giveing the error is

rs2.Open(sql, conn, CursorTypeEnum.adOpenStatic, LockTypeEnum.adLockOptimistic)

although im not sure y but its the one thats highlighting and saying there is a prob.

[spider661]

i notice when i point to rs.open it tells me

Public Sub Open([Source As Object],[ActiveConnection As Object], [Cursourtype BLA BLA BLA

in the tool tip pop up there.

just thinking is that the problem am i calling sql (a string) rather then some object? do i need to call it a diff way?

all im trying to do is find the next available id in a database table named items
between 1001 and 65535 is there a diff way i can do it mabie? im not really stuck on doing it this was as long as it gets done.

so if you cant get this working im willing to try a diff way :P

thanks

[rollete]

Since you just wanted to retrieved a record,why not try to use different CursorType or LockType instead?

rs2.Open sql, conn, CursorTypeEnum.adOpenForwardOnly, LockTypeEnum.adLockReadOnly

[Hack]

What kind of a database are you using?

[spider661]

mysql 5.0.37

[Hack]

Then I can't try and duplicate your problem as I do not have access to any MySQL databases.

But, try just this: rs2.Open sql, conn, adOpenStatic, adLockOptimistic

[spider661]

when i put that in it underlines adOpenStatic and adLockOptimistic and says name is not declared

[Hack]

Oh, right. That is my bad. You are not using VB6, you are using VB.NET

In that case, you really shouldn't use ADO at all, but, rather, make the switch to ADO.NET

Here is a good start.

(And, once again, sorry 'bout that)

[spider661]

ok im fine with doing so. BUT im a big learner of examples rather then reading a bunch of stuff. don't get me wrong i will read it but being dislescic (spell check) it is hard for me to comprehend sometimes when i read alot of information.

would someone be willing to show me some examples of mabie using it to read and insert data that would do me wonders

the only examples im finding on ado.net is how to take the read information and put in into a datagrid but thats not taking a single line and displaying it in a text box for example or anything close to what im trying to do here..

anyways what this all boils down to is if anyone is willing could you show me and example of how to use it..

a insert/update and retrieve function would be the best but ill live with jsut a basic sample and an explanation of what its doing.

i want to learn not be given the info so if you do post a function for me please include some comments at lest showing whats happening please.

thanks for the help so far its been a treat talking with you guys.

[spider661]

ok i obliviously have no clue what im doing.
i read up on ado.net (witch btw seems to be used more for websites more then programs)
then i ran through a crap load of code on planet-source-code.com and finally found one i could somewhat understand what was happening

Link

after that i tried to get the function i was working on to work using that example.

this is what i came up with

Imports System.Data.SqlClient

Module Module1
    Friend ProcType As Integer
    Friend eq_host As String
    Friend eq_user As String
    Friend eq_password As String
    Friend eq_dbname As String

 Function find_next_available_item_id()
        Dim new_id As Integer
        eq_host = "192.***.*.***"
        eq_user = "user"
        eq_password = "pass"
        eq_dbname = "db"

        Dim Conn As New SqlConnection
        Dim dr As SqlDataReader
        Conn.ConnectionString = ("Server=" & eq_host & ";Database=" & eq_dbname & ";UID=" & eq_user & ";PWD=" & eq_password & ";")
        Conn.Open()
        dr = New SqlCommand("Select id from items", Conn).ExecuteReader
        Do While dr.Read
            For i = 1001 To 65535
                If dr.Item(i) = -1 Then
                    new_id = i
                    dr.Close()
                    Conn.Close()
                    Exit For
                End If
            Next i
        Loop

        find_next_available_item_id = new_id
    End Function

but this gets all kinds of errors one being cant connect to database if im reading it right.

anyways here is my attempt now lets see if anyone can work from that helping me getting it working or fixed.

here is the error
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

on line Conn.Open()

but im sure there will be more in the output part.

[spider661]

ok i got the connection working seems i was not using the right
connection type here is new code and new error

Imports System.Data.Odbc

Module Module1
    Friend ProcType As Integer
    Friend eq_host As String
    Friend eq_user As String
    Friend eq_password As String
    Friend eq_dbname As String

    Function find_next_available_item_id()
        Dim new_id As Integer
        eq_host = "192.***.*.***"
        eq_user = "user"
        eq_password = "pass"
        eq_dbname = "db"

        Dim Conn As New OdbcConnection
        Dim dr As OdbcDataReader
        Conn.ConnectionString = ("DRIVER={MySQL ODBC 3.51 Driver};Server=" & eq_host & ";Database=" & eq_dbname & ";UID=" & eq_user & ";PWD=" & eq_password & ";")
        Conn.Open()
        dr = New OdbcCommand("Select id from items", Conn).ExecuteReader
        Do While dr.Read
            For i = 1001 To 65535
                If dr.Item(i) = -1 Then
                    new_id = i
                    dr.Close()
                    Conn.Close()
                    Exit For
                End If
            Next i
        Loop

        find_next_available_item_id = new_id
    End Function

Index was outside the bounds of the array. If dr.Item(i) = -1 Then

[spider661]

lol ok guys forget EVERYTHING sayed before im so close now i can taste it.

i had it working listing the ids into a list box now im trying to make it find the id like i was trying to do origanaly and i think i almost got it but getting an error now

Index was outside the bounds of the array.
on line If dr.Item(i).ToString = "" Then

here is the new code

Imports System.Data.Odbc

Module Module1
    Friend ProcType As Integer
    Friend eq_host As String
    Friend eq_user As String
    Friend eq_password As String
    Friend eq_dbname As String

    Function find_next_available_item_id()
        Dim new_id As Integer
        eq_host = "192.***.*.***"
        eq_user = "user"
        eq_password = "pass"
        eq_dbname = "db"

        Dim Conn As New OdbcConnection
        Dim dr As OdbcDataReader
        Conn.ConnectionString = ("DRIVER={MySQL ODBC 3.51 Driver};Server=" & eq_host & ";Database=" & eq_dbname & ";UID=" & eq_user & ";PWD=" & eq_password & ";")
        Conn.Open()
        dr = New OdbcCommand("Select id from items", Conn).ExecuteReader
        Do While dr.Read
            For i = 0 To 65535
                If dr.Item(i).ToString = "" Then
                    new_id = i
                    dr.Close()
                    Conn.Close()
                    Exit For
                Else
                    FrmEffects.LstEffects.Items.Add(dr.Item(i))
                End If
            Next i

        Loop
        dr.Close()
        Conn.Close()
        FrmEffects.TextBox1.Text = new_id
        find_next_available_item_id = new_id
    End Function

[Hack]

So, what is the current error and what line is causing it?