Reading one record from an Access DB

microsoft.public.dotnet.languages.vb

13 Jul 2006 12:09 AM

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

13 Jul 2006 3:43 AM

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.

13 Jul 2006 4:56 AM

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.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
Show quoteHide quote

"Steven Nagy" <learndot***@hotmail.com> wrote in message
news:1152762198.854360.264980@m73g2000cwd.googlegroups.com...
> DataAdapter is overkill for what you want.
> You want a Command object, in this case, OleDbCommand.
> You want to use the ExecuteScalar() method.
>

13 Jul 2006 5:19 AM

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
Show quoteHide quote

"Stephen Plotnick" <splotn***@groupcbf.com> wrote in message
news:vaedne4_Dc8ETyjZnZ2dnUVZ_v6dnZ2d@giganews.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.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
> "Steven Nagy" <learndot***@hotmail.com> wrote in message
> news:1152762198.854360.264980@m73g2000cwd.googlegroups.com...
>> DataAdapter is overkill for what you want.
>> You want a Command object, in this case, OleDbCommand.
>> You want to use the ExecuteScalar() method.
>>
>
>

13 Jul 2006 5:41 AM

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:
Show quoteHide quote

> 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" <splotn***@groupcbf.com> wrote in message
> news:vaedne4_Dc8ETyjZnZ2dnUVZ_v6dnZ2d@giganews.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.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
> > "Steven Nagy" <learndot***@hotmail.com> wrote in message
> > news:1152762198.854360.264980@m73g2000cwd.googlegroups.com...
> >> DataAdapter is overkill for what you want.
> >> You want a Command object, in this case, OleDbCommand.
> >> You want to use the ExecuteScalar() method.
> >>
> >
> >

13 Jul 2006 12:29 PM

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.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

Show quoteHide quote

"Steven Nagy" <learndot***@hotmail.com> wrote in message
news:1152769279.595953.327130@35g2000cwc.googlegroups.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" <splotn***@groupcbf.com> wrote in message
>> news:vaedne4_Dc8ETyjZnZ2dnUVZ_v6dnZ2d@giganews.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.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
>> > "Steven Nagy" <learndot***@hotmail.com> wrote in message
>> > news:1152762198.854360.264980@m73g2000cwd.googlegroups.com...
>> >> DataAdapter is overkill for what you want.
>> >> You want a Command object, in this case, OleDbCommand.
>> >> You want to use the ExecuteScalar() method.
>> >>
>> >
>> >
>

13 Jul 2006 1:12 PM

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.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

Stephen Plotnick wrote:
Show quoteHide quote

> 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
>
>
> "Steven Nagy" <learndot***@hotmail.com> wrote in message
> news:1152769279.595953.327130@35g2000cwc.googlegroups.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" <splotn***@groupcbf.com> wrote in message
> >> news:vaedne4_Dc8ETyjZnZ2dnUVZ_v6dnZ2d@giganews.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.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
> >> > "Steven Nagy" <learndot***@hotmail.com> wrote in message
> >> > news:1152762198.854360.264980@m73g2000cwd.googlegroups.com...
> >> >> DataAdapter is overkill for what you want.
> >> >> You want a Command object, in this case, OleDbCommand.
> >> >> You want to use the ExecuteScalar() method.
> >> >>
> >> >
> >> >
> >

13 Jul 2006 10:58 PM

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.

14 Jul 2006 3:01 AM

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
Show quoteHide quote

"Steven Nagy" <learndot***@hotmail.com> wrote in message
news:1152831527.580222.248840@p79g2000cwp.googlegroups.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.
>

14 Jul 2006 3:32 AM

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.

14 Jul 2006 3:37 AM

Stephen Plotnick

Thanks,
Steve
Show quoteHide quote

"Steven Nagy" <learndot***@hotmail.com> wrote in message
news:1152847938.400108.51380@m79g2000cwm.googlegroups.com...
> 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.
>