pvg1975
asked on
Help with vb.net and mdb database
Hello guys,
I am learning VB.NET and I would like to open an access database. With your help, I figured out how to open the database:
Dim SQLStr As String
Dim conn As OleDbConnection
conn = New OleDbConnection("Provider= Microsoft. ACE.OLEDB. 12.0;Data Source='" & MiBase & "';Persist Security Info=False;")
conn.Open()
SQLStr = "SELECT * FROM tblContacts"
My questios are:
1) How do I navigate the record from the SQL code at SQLSTR? In visual basic, I used to do this:
set rs=conn.execute(SQLstr)
do until rs.eof
...
...
rs.movenext
loop
How do I do that in VB.NET, with the code I am using above?
2) Is there any way to count the number of records returned by SQLstr?
Thanks!
PS: Sorry for the points, I only have 85 left :)
I am learning VB.NET and I would like to open an access database. With your help, I figured out how to open the database:
Dim SQLStr As String
Dim conn As OleDbConnection
conn = New OleDbConnection("Provider=
conn.Open()
SQLStr = "SELECT * FROM tblContacts"
My questios are:
1) How do I navigate the record from the SQL code at SQLSTR? In visual basic, I used to do this:
set rs=conn.execute(SQLstr)
do until rs.eof
...
...
rs.movenext
loop
How do I do that in VB.NET, with the code I am using above?
2) Is there any way to count the number of records returned by SQLstr?
Thanks!
PS: Sorry for the points, I only have 85 left :)
Dim n as int32
Dim s As String
Dim rdr As OleDbDataReader
rdr = cmd.ExecuteReader
While rdr.Read
row = newTable.NewRow()
'data is collected from the reader. column by column
n= rdr.GetInt32(0) '0,1,2 - Index Column on Sql String
s1 = rdr.GetString(1)
s2 = rdr.GetString(2)
End While
HTH
Ashok
Dim s As String
Dim rdr As OleDbDataReader
rdr = cmd.ExecuteReader
While rdr.Read
row = newTable.NewRow()
'data is collected from the reader. column by column
n= rdr.GetInt32(0) '0,1,2 - Index Column on Sql String
s1 = rdr.GetString(1)
s2 = rdr.GetString(2)
End While
HTH
Ashok
Dim n as int32
Dim s As String
Dim rdr As OleDbDataReader
rdr = cmd.ExecuteReader
While rdr.Read
'data is collected from the reader. column by column
n= rdr.GetInt32(0) '0,1,2 - Index Column on Sql String
s1 = rdr.GetString(1)
s2 = rdr.GetString(2)
End While
HTH
Ashok
Dim s As String
Dim rdr As OleDbDataReader
rdr = cmd.ExecuteReader
While rdr.Read
'data is collected from the reader. column by column
n= rdr.GetInt32(0) '0,1,2 - Index Column on Sql String
s1 = rdr.GetString(1)
s2 = rdr.GetString(2)
End While
HTH
Ashok
ASKER
Hello guys,
I couldnt make any of the examples work. Here's my code:
Dim SQLStr As String
Dim conn As OleDbConnection
conn = New OleDbConnection("Provider= Microsoft. ACE.OLEDB. 12.0;Data Source='" & MiBase & "';Persist Security Info=False;")
SQLStr = "SELECT * FROM tblContacts"
conn.Open()
I couldnt make any of the examples work. Here's my code:
Dim SQLStr As String
Dim conn As OleDbConnection
conn = New OleDbConnection("Provider=
SQLStr = "SELECT * FROM tblContacts"
conn.Open()
Dim SQLStr As String
Dim conn As OleDbConnection
conn = New OleDbConnection("Provider= Microsoft. ACE.OLEDB. 12.0;Data Source='" & MiBase & "';Persist Security Info=False;")
SQLStr = "SELECT * FROM tblContacts"
conn.Open()
dim cmd as new OLEDBCommand(SQLStr,cmd)
Dim rdr As OleDbDataReader
rdr = cmd.ExecuteReader
int count = 0;
While rdr.Read
'data is collected from the reader. column by column
n= rdr.GetInt32(0) '0,1,2 - Index Column on Sql String
s1 = rdr.GetString(1)
s2 = rdr.GetString(2)
count++;
End While
HTH
Ashok
Dim conn As OleDbConnection
conn = New OleDbConnection("Provider=
SQLStr = "SELECT * FROM tblContacts"
conn.Open()
dim cmd as new OLEDBCommand(SQLStr,cmd)
Dim rdr As OleDbDataReader
rdr = cmd.ExecuteReader
int count = 0;
While rdr.Read
'data is collected from the reader. column by column
n= rdr.GetInt32(0) '0,1,2 - Index Column on Sql String
s1 = rdr.GetString(1)
s2 = rdr.GetString(2)
count++;
End While
HTH
Ashok
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Instead of using * in SELECT,
use actual field names separated by commas
SELECT Name, PhoneNo, NumOfFriends from tblContacts
This way
s1 = rdr.GetString(0)
s2 = rdr.GetString(1)
i1 = rdr.GetInt32(2)
First two fields as string and 3rd field is integer.
Ashok
use actual field names separated by commas
SELECT Name, PhoneNo, NumOfFriends from tblContacts
This way
s1 = rdr.GetString(0)
s2 = rdr.GetString(1)
i1 = rdr.GetInt32(2)
First two fields as string and 3rd field is integer.
Ashok
ASKER
Still not working.
I am getting errors everywhere
This is the code:
Dim SQLStr As String
Dim conn As OleDbConnection
Dim count As Integer
conn = New OleDbConnection("Provider= Microsoft. ACE.OLEDB. 12.0;Data Source='" & MiBase & "';Persist Security Info=False;")
SQLStr = "SELECT cid, name, address FROM tblContacts"
conn.Open()
Dim cmd As New OleDbCommand(SQLStr, conn)
Dim rdr As OleDbDataReader
Dim n
rdr = cmd.ExecuteReader
count = 0
While rdr.Read
'data is collected from the reader. column by column
n = rdr.GetInt32(0) '0,1,2 - Index Column on Sql String
s1 = rdr.GetString(1)
s2 = rdr.GetString(2)
count++;
End While
conn.Close()
The following lines shows errors at VB:
n = rdr.GetInt32(0)
s1 = rdr.GetString(1)
s2 = rdr.GetString(2)
count++;
I am getting errors everywhere
This is the code:
Dim SQLStr As String
Dim conn As OleDbConnection
Dim count As Integer
conn = New OleDbConnection("Provider=
SQLStr = "SELECT cid, name, address FROM tblContacts"
conn.Open()
Dim cmd As New OleDbCommand(SQLStr, conn)
Dim rdr As OleDbDataReader
Dim n
rdr = cmd.ExecuteReader
count = 0
While rdr.Read
'data is collected from the reader. column by column
n = rdr.GetInt32(0) '0,1,2 - Index Column on Sql String
s1 = rdr.GetString(1)
s2 = rdr.GetString(2)
count++;
End While
conn.Close()
The following lines shows errors at VB:
n = rdr.GetInt32(0)
s1 = rdr.GetString(1)
s2 = rdr.GetString(2)
count++;
Dim SQLStr As String
Dim conn As OleDbConnection
Dim count As Integer
conn = New OleDbConnection("Provider= Microsoft. ACE.OLEDB. 12.0;Data Source='" & MiBase & "';Persist Security Info=False;")
SQLStr = "SELECT cid, name, address FROM tblContacts"
conn.Open()
Dim cmd As New OleDbCommand(SQLStr, conn)
Dim rdr As OleDbDataReader
Dim n As Integer
Dim s1 As String
Dim s2 As String
rdr = cmd.ExecuteReader
count = 0
While rdr.Read
'data is collected from the reader. column by column
n = rdr.GetInt32(0) '0,1,2 - Index Column on Sql String
s1 = rdr.GetString(1)
s2 = rdr.GetString(2)
count++;
End While
conn.Close()
HTH
Ashok
Dim conn As OleDbConnection
Dim count As Integer
conn = New OleDbConnection("Provider=
SQLStr = "SELECT cid, name, address FROM tblContacts"
conn.Open()
Dim cmd As New OleDbCommand(SQLStr, conn)
Dim rdr As OleDbDataReader
Dim n As Integer
Dim s1 As String
Dim s2 As String
rdr = cmd.ExecuteReader
count = 0
While rdr.Read
'data is collected from the reader. column by column
n = rdr.GetInt32(0) '0,1,2 - Index Column on Sql String
s1 = rdr.GetString(1)
s2 = rdr.GetString(2)
count++;
End While
conn.Close()
HTH
Ashok
Using a DataReader is fast, but very low tech... but you might want to pull a disconnected DataTable instead. Then you can loop thru the rows in the table in a bit more civilized way.
Take a look at the following article (and the downloadable VB.Net example)
http://www.emmet-gray.com/Articles/BeginningADO.htm
Take a look at the following article (and the downloadable VB.Net example)
http://www.emmet-gray.com/Articles/BeginningADO.htm
For each dr as DataRow in dt
debug.writeline(dr("cid"))
debug.writeline(dr("name"))
debug.writeline(dr("address"))
Next
What happens when you replace the While loop with the following?
If you still get errors, then please specify what exactly the error message says, which will help us help you further.
Dabas
If you still get errors, then please specify what exactly the error message says, which will help us help you further.
Dabas
While rdr.Read
'data is collected from the reader. column by column
n = rdr("cid")
s1 = rdr("name")
s2 = rdr("address")
End While
Memory Objects: Dataset, DataTable....
Connection Oriented: Command (OledbCommand), DataReader (OleDbDataReader), DataAdapter...
Or you can use also old Vb6 style objects.
For the new Ado.Net Style, you can create a OleDbcommand, Assign to it Connection an SqlString.
Then you can Query the command to obtain a OleDbDataReader or Fill a DataTable by a OleDbDataAdapter.
For Oracle example:...
Dim rdr As OracleDataReader = cmd.ExecuteReader
Do Until rdr.Read = False
Dim n as int32 = rdr.GetInt32(0) '0,1,2 - Index Column on Sql String
Dim v as Object = rdr.GetValue(1)
Dim Dm = rdr.GetString(2)
....
Loop
DataTable Objects are a powerful option.
Dim Dt as new DataTAble, Da as new DataAdapter(Cmd)
Da.Fill(Dt)
Dt.PrimaryKey = new DataColumn(){Dt.Columns(0)
Dim R as DataRow = Dt.Find("KeyToFind")
Note. DataView is as powerful extension for DataTables.