Reading one record from an Access DB

[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.Ole Db.OleDbConnect ion("Provider=M icrosoft.Jet.OL EDB.4.0;Data
source=C:\BMAct ivityReporting. mdb;Persist Security Info=False")

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

conn.Open()

Dim da As New System.Data.Ole Db.OleDbDataAda pter(sSQL, conn)

Try

TextName.Text = da.

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

THanks,

Steve

[Steven Nagy]

DataAdapter is overkill for what you want.
You want a Command object, in this case, OleDbCommand.
You want to use the ExecuteScalar() method.

[Stephen Plotnick]

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.ExecuteScala r() 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
"Steven Nagy" <le*********@ho tmail.comwrote in message
news:11******** **************@ m73g2000cwd.goo glegroups.com.. .

DataAdapter is overkill for what you want.
You want a Command object, in this case, OleDbCommand.
You want to use the ExecuteScalar() method.

[Stephen Plotnick]

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
"Stephen Plotnick" <sp*******@grou pcbf.comwrote in message
news:va******** *************** *******@giganew s.com...

>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.ExecuteScala r() 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
"Steven Nagy" <le*********@ho tmail.comwrote in message
news:11******** **************@ m73g2000cwd.goo glegroups.com.. .

>DataAdapter is overkill for what you want.
You want a Command object, in this case, OleDbCommand.
You want to use the ExecuteScalar() method.

[Steven Nagy]

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

Stephen Plotnick wrote:

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
"Stephen Plotnick" <sp*******@grou pcbf.comwrote in message
news:va******** *************** *******@giganew s.com...

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.ExecuteScala r() 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
"Steven Nagy" <le*********@ho tmail.comwrote in message
news:11******** **************@ m73g2000cwd.goo glegroups.com.. .

DataAdapter is overkill for what you want.
You want a Command object, in this case, OleDbCommand.
You want to use the ExecuteScalar() method.

[Stephen Plotnick]

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.Ole Db.OleDbConnect ion("Provider=M icrosoft.Jet.OL EDB.4.0;Data
source=C:\BMAct ivityReporting. mdb;Persist Security Info=False")

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

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

conn.Open()

Try

da.ExecuteScala r()

'StringResult = myCommand.Execu teScalar ( );

' TextName.Text = da.

Catch ex As System.Data.Ole Db.OleDbExcepti on

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).Me ssage & ControlChars.Cr _

& "NativeErro r: " & ex.Errors(i).Na tiveError & ControlChars.Cr _

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

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

Next i

MsgBox(errorMes sages)

Finally

conn.Close()

End Try
"Steven Nagy" <le*********@ho tmail.comwrote in message
news:11******** **************@ 35g2000cwc.goog legroups.com...

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

Stephen Plotnick wrote:

>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
"Stephen Plotnick" <sp*******@grou pcbf.comwrote in message
news:va******* *************** ********@gigane ws.com...

>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.ExecuteScala r() 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
"Steven Nagy" <le*********@ho tmail.comwrote in message
news:11******** **************@ m73g2000cwd.goo glegroups.com.. .
DataAdapter is overkill for what you want.
You want a Command object, in this case, OleDbCommand.
You want to use the ExecuteScalar() method.

[raibeart]

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.Ope n()
.CommandText = strSQL
dr = .ExecuteReader( CommandBehavior .CloseConnectio n)
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.GetData Reader(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

Stephen Plotnick wrote:

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.Ole Db.OleDbConnect ion("Provider=M icrosoft.Jet.OL EDB.4.0;Data
source=C:\BMAct ivityReporting. mdb;Persist Security Info=False")

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

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

conn.Open()

Try

da.ExecuteScala r()

'StringResult = myCommand.Execu teScalar ( );

' TextName.Text = da.

Catch ex As System.Data.Ole Db.OleDbExcepti on

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).Me ssage & ControlChars.Cr _

& "NativeErro r: " & ex.Errors(i).Na tiveError & ControlChars.Cr _

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

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

Next i

MsgBox(errorMes sages)

Finally

conn.Close()

End Try
"Steven Nagy" <le*********@ho tmail.comwrote in message
news:11******** **************@ 35g2000cwc.goog legroups.com...

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

Stephen Plotnick wrote:

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
"Stephen Plotnick" <sp*******@grou pcbf.comwrote in message
news:va******** *************** *******@giganew s.com...
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.ExecuteScala r() 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
"Steven Nagy" <le*********@ho tmail.comwrote in message
news:11******** **************@ m73g2000cwd.goo glegroups.com.. .
DataAdapter is overkill for what you want.
You want a Command object, in this case, OleDbCommand.
You want to use the ExecuteScalar() method.

[Steven Nagy]

Ignore Raibert... Data Adapter is too much fluff for returning a single
value.

The syntax error indicates that there is an error in your SQL.
Double check that the column and table names are correct.
Also, put square brackets around the User part in the from clause.
Ie. [User]
In sql server, User is a reserved word. I am not sure if this is the
same in Access but it might help.

Also, the execute scalar method returns a value, so you should assign
its output to your textbox.text or whatever you want to do with it.

For debugging, you can also try running your query in access directly
to see if it works.
It might provide more helpful debug information.

[Stephen Plotnick]

Steve,

I changed the table name from User to UserTable and it works great.

I do like the concept from Raibert for developing an I/O module (class) for
the data base.

Short story; I've been programming in COBOL since finishing school in 1981
and decided to toy with VB.NET for a client's project I started a couple of
weeks ago. I was able to do a da.fill to a datagrid and pull data through a
dataviewrow to 8 screens and uddate 84 variables that can be changed. I was
amazed I accomplished this. Now when I wanted one field from one record I
was stumped, I thank you for getting me through that so easily.

In COBOL and ODBC I wrote I/O modules that did every SQL command possible.
I'm going to try this when I get a break.

Thanks for all the help,
Steve
"Steven Nagy" <le*********@ho tmail.comwrote in message
news:11******** **************@ p79g2000cwp.goo glegroups.com.. .

Ignore Raibert... Data Adapter is too much fluff for returning a single
value.

The syntax error indicates that there is an error in your SQL.
Double check that the column and table names are correct.
Also, put square brackets around the User part in the from clause.
Ie. [User]
In sql server, User is a reserved word. I am not sure if this is the
same in Access but it might help.

Also, the execute scalar method returns a value, so you should assign
its output to your textbox.text or whatever you want to do with it.

For debugging, you can also try running your query in access directly
to see if it works.
It might provide more helpful debug information.

[Steven Nagy]

Its definately a good idea but also consider some other tools to help
you with the process. Code generation is a very powerful way of
creating code that exhibits repetitive nature.

For example, say you have 50 tables in your database. You want to
create a nice module for each table that has basic Create, Retrieve,
Update, and Delete functionality in your VB.NET application. Code
generation tools such as CodeSmith can help with this. I wrote my own
code generater 3 or 4 years ago which I am now over hauling with
CodeSmith for creating my Data connection layer. Currently it generates
about 400 lines of code per table in useful methods for interacting
with that table. It even can do things like check for Foreign keys in a
table and create "Select" statements based on those foreign keys. This
is the power of code generation. It also creates a "Standard" for the
methods interacting with your tables. Eg. My tables 'Person' and
'Salary' both have had code generated for them from my code generator.
Now I have a 'Person' class that has a 'Create' method, and I have a
'Salary' class that also has a 'Create' method, but they each have
different argument lists.

Visual Studio even makes use of code generators. When you create some
visual elements in design mode, it uses an in-built code generater to
create the code for those controls. Or when you use dataAdapters to
create datasets in design mode, once again a class is created that
contains an xml description of your dataset. This is autogenerated as
well, and the code that goes with that xml is generated FROM the xml
also.

Also of interest when it comes to database programming, is the
Application Blocks that are downloadable as a part of the Enterprise
Library from Microsoft. Its not an officially supported Microsoft
product, but it is a download on their site. There is an Application
Block related to data access that will help make connecting to
databases easier and less code intensive. This might be a consideration
for you.

Hope this helps.