help with code vb .net 9


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Page 1 of 2 12 LastLast
Results 1 to 15 of 20

Thread: help with code vb .net 9

  1. #1
    Join Date
    Apr 2008
    Posts
    12

    help with code vb .net 9

    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

    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

  2. #2
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    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
    Code:
    'this 
    sql = "SELECT id FROM items WHERE id='" & i & "'"
    'should be
    sql = "SELECT id FROM items WHERE id = " & i
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  3. #3
    Join Date
    Apr 2008
    Posts
    12
    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

    Code:
    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

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

  4. #4
    Join Date
    Apr 2008
    Posts
    12
    i notice when i point to rs.open it tells me
    Code:
    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

  5. #5
    Join Date
    Apr 2008
    Posts
    28
    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

  6. #6
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    What kind of a database are you using?
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  7. #7
    Join Date
    Apr 2008
    Posts
    12
    mysql 5.0.37

  8. #8
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    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
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  9. #9
    Join Date
    Apr 2008
    Posts
    12
    when i put that in it underlines adOpenStatic and adLockOptimistic and says name is not declared

  10. #10
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    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)
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  11. #11
    Join Date
    Apr 2008
    Posts
    12
    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.

  12. #12
    Join Date
    Apr 2008
    Posts
    12
    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

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

  13. #13
    Join Date
    Apr 2008
    Posts
    12
    ok i got the connection working seems i was not using the right
    connection type here is new code and new error

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

  14. #14
    Join Date
    Apr 2008
    Posts
    12
    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

    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

  15. #15
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    So, what is the current error and what line is causing it?
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

Similar Threads

  1. Replies: 2
    Last Post: 07-12-2007, 11:41 AM
  2. Replies: 0
    Last Post: 11-19-2001, 05:08 PM
  3. The man who broke Mike's code....
    By Jeff Johnson in forum .NET
    Replies: 183
    Last Post: 07-30-2001, 11:51 AM
  4. Replies: 90
    Last Post: 04-17-2001, 12:45 AM
  5. Replies: 84
    Last Post: 01-29-2001, 01:12 PM

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