Oledb????

[Fia]

Hi
I'm trying to use Oledb in Visual Basic .Net, but it doesn't work so well.
Before in Visual Basic 6 I had the code below that worked.

Dim tblSökaOrd As DAO.Recordset
dim DB As Database
Set DB = Worksp.OpenDatabase(App.Path & "\recept.mdb", False, False)
SQL = "select köttid from kött where köttid like '" & PubStr & "*'order by
köttid ;"
Set tblSökaOrd = DB.OpenRecordset(SQL, dbOpenSnapshot)
If tblSökaOrd.RecordCount <> 0 Then
'This code happens
end if

In Visual Basic .Net I have tried this code, but it doesnt work
Dim sökaOrdCom As New OleDbCommand(), SQL As String
Dim sökaOrdRead As OleDbDataReader
Dim field As New OleDbParameter()
SQL = "select köttid from kött where köttid like ? order by köttid ;"
sökaOrdCom.CommandText = SQL
sökaOrdCom.Parameters.Add(field)
sökaOrdCom.Parameters("köttid").Value = "'" & PubStr & "*'"
sökaOrdCom.Connection = DBConn
sökaOrdRead = sökaOrdCom.ExecuteReader
Do While sökaOrdRead.Read
recCount += 1
Loop
sökaOrdRead.Close()
If recCount <> 0 Then
' This code never happens
end if

I don't get any records from the while loop but I know I have records. I
have read in the help files that when you have a query with filter (where)
you should do someting like this, I think.
Can anyone help me please.
I have another question also, I don't understand When and Why I have to use
the keyword New. In the declaration for sökaOrdCom I have to use New but
when I declarate sökaOrdRead I don't have to use New. And also in Visual
Basic 6 we had the property RecordCount, do we have to use a while loop and
increment an integer to get the querie's recordcount?

Greatful for answers
Fia

 

[Ken Tucker [MVP]]

Hi,

Couple of things first remember with ado.net % does what * did with
jet. You dont need to create a new datareader because executereader returns
a datareader. Your sql statement needs to be changed. Here is a working
example.

Dim conn As OleDbConnection

Dim strConn As String

Dim drCustomer As OleDbDataReader

Dim cmd As OleDbCommand

Dim ds As New DataSet

strConn = "Provider = Microsoft.Jet.OLEDB.4.0;"

strConn &= "Data Source = C:\Northwind.mdb;"

conn = New OleDbConnection(strConn)

cmd = New OleDbCommand("Select * from Customers where CustomerID Like
@MyId", conn)

conn.Open()

cmd.Parameters.Add("@MyId", "A%")

Trace.WriteLine(cmd.CommandText)

drCustomer = cmd.ExecuteReader

Do While drCustomer.Read

Trace.WriteLine(drCustomer.Item("CustomerID").ToString)

Loop

conn.Close()



Ken

--------------------

Hi
I'm trying to use Oledb in Visual Basic .Net, but it doesn't work so well.
Before in Visual Basic 6 I had the code below that worked.

Dim tblSökaOrd As DAO.Recordset
dim DB As Database
Set DB = Worksp.OpenDatabase(App.Path & "\recept.mdb", False, False)
SQL = "select köttid from kött where köttid like '" & PubStr & "*'order by
köttid ;"
Set tblSökaOrd = DB.OpenRecordset(SQL, dbOpenSnapshot)
If tblSökaOrd.RecordCount <> 0 Then
'This code happens
end if

In Visual Basic .Net I have tried this code, but it doesnt work
Dim sökaOrdCom As New OleDbCommand(), SQL As String
Dim sökaOrdRead As OleDbDataReader
Dim field As New OleDbParameter()
SQL = "select köttid from kött where köttid like ? order by köttid ;"
sökaOrdCom.CommandText = SQL
sökaOrdCom.Parameters.Add(field)
sökaOrdCom.Parameters("köttid").Value = "'" & PubStr & "*'"
sökaOrdCom.Connection = DBConn
sökaOrdRead = sökaOrdCom.ExecuteReader
Do While sökaOrdRead.Read
recCount += 1
Loop
sökaOrdRead.Close()
If recCount <> 0 Then
' This code never happens
end if

I don't get any records from the while loop but I know I have records. I
have read in the help files that when you have a query with filter (where)
you should do someting like this, I think.
Can anyone help me please.
I have another question also, I don't understand When and Why I have to use
the keyword New. In the declaration for sökaOrdCom I have to use New but
when I declarate sökaOrdRead I don't have to use New. And also in Visual
Basic 6 we had the property RecordCount, do we have to use a while loop and
increment an integer to get the querie's recordcount?

Greatful for answers
Fia