-
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
-
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
-
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.
-
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
-
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
-
What kind of a database are you using?
-
-
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
-
when i put that in it underlines adOpenStatic and adLockOptimistic and says name is not declared
-
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)
-
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.
-
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.
-
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
-
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
-
So, what is the current error and what line is causing it?
Similar Threads
-
By skipperben in forum ASP.NET
Replies: 2
Last Post: 07-12-2007, 11:41 AM
-
By AutomatedQA in forum dotnet.announcements
Replies: 0
Last Post: 11-19-2001, 05:08 PM
-
By Jeff Johnson in forum .NET
Replies: 183
Last Post: 07-30-2001, 11:51 AM
-
Replies: 90
Last Post: 04-17-2001, 12:45 AM
-
By Jon Ogden in forum .NET
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
-
Forum Rules
|
Development Centers
-- Android Development Center
-- Cloud Development Project Center
-- HTML5 Development Center
-- Windows Mobile Development Center
|