Reading one record from an Access DB

  • Thread starter Thread starter Stephen Plotnick
  • Start date Start date
S

Stephen Plotnick

I've started some code but do not know how to get the anwser. Here is the
code:

I need the first four characters of data in a field (TextName) to get a name
from a DB and put the name back into TextName.

Dim conn As New
System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
source=C:\BMActivityReporting.mdb;Persist Security Info=False")

Dim sSQL As String = "select * from User Where UserNo=" & Mid(TextName.Text,
1, 4)

conn.Open()

Dim da As New System.Data.OleDb.OleDbDataAdapter(sSQL, conn)

Try

TextName.Text = da.



I do not know how to finish or if I'm headed in the right direction.



THanks,

Steve
 
DataAdapter is overkill for what you want.
You want a Command object, in this case, OleDbCommand.
You want to use the ExecuteScalar() method.
 
I think I set everything up ok.

Here is the string for the SQL statement take while watching in the
debugger:

"select * from User Where UserNo='0000'"

There is a record with UserNo='0000' (Text type) in a table User.

the da.ExecuteScalar() completes within a Try..

I do not know how to get the value for the exeption; there is an execption.

How do I get the value for the exception?

THanks,

Steve
 
OK, I'm exceited.

I figured out how to get the exception to a msgbox.

I'm getting an error in the FROM Clause.

Hopefully I figure that out:)

If someone has an idea let me know.

THanks,
Steve
 
Ok the 'ExecuteScalar' method only returns a single value.
But you are doing a SELECT * which means return all columns for that
row.
This also means that more than one value is being returned.
So you need firstly only select the column in question.

Also, post the error message here so we can see the problem.

And post your code too
 
I changed to get only one field Username and still get the following error:

Index #0
Message: Syntax error in FROM clause
NativeError: -526650802
Source: Miscrofosft JET Database Engine
SQLState: 3000

Dim conn As New
System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
source=C:\BMActivityReporting.mdb;Persist Security Info=False")

Dim sSQL As String = "select UserName FROM User WHERE UserNo='" &
Mid(TextName.Text, 1, 4) & "'"

Dim da As New System.Data.OleDb.OleDbCommand(sSQL, conn)

conn.Open()

Try

da.ExecuteScalar()

'StringResult = myCommand.ExecuteScalar ( );

' TextName.Text = da.

Catch ex As System.Data.OleDb.OleDbException

Dim errorMessages As String

Dim i As Integer

For i = 0 To ex.Errors.Count - 1

errorMessages += "Index #" & i.ToString() & ControlChars.Cr _

& "Message: " & ex.Errors(i).Message & ControlChars.Cr _

& "NativeError: " & ex.Errors(i).NativeError & ControlChars.Cr _

& "Source: " & ex.Errors(i).Source & ControlChars.Cr _

& "SQLState: " & ex.Errors(i).SQLState & ControlChars.Cr

Next i

MsgBox(errorMessages)

Finally

conn.Close()

End Try
 
Stephen,

Okay, I usually do SQL Server, so some of this is specific to that.

I would use a datareader.

Here is a function that I use for it.

Place the following function in a VB class called DHOleDB (Data
Handler).

Public Shared Function GetDataReader( _
ByVal strSQL as String, _
ByVal strCN as String) AS OleDbDataReader
Dim dr as OleDbDataReader
Dim cmd as New OleDbCommand
With cmd
.Connection = New OleDbConnection(strCN)
.Connection.Open()
.CommandText = strSQL
dr = .ExecuteReader(CommandBehavior.CloseConnection)
End With
Return dr
End Function

Call this using your connection string and SQL string using the
following code:

dim dr as OleDbDataReader
dr = DHOleDB.GetDataReader(your sql string, your connection string)
If dr.read then
..... Do what you want to here i.e.
StringResult = dr("UserNo")
End if
dr.close
 
Back
Top