Database SQL Statement

  • Thread starter Thread starter Earl Partridge
  • Start date Start date
E

Earl Partridge

I just downloaded this VB Express. I need a little help in understanding the request
for database data, that is the format/syntax compared to an SQL statement.

In my SQL I would use a select statement... example

select LastName from Members where month(Birthday) = month(now)

How would this be coded in VB Express?

Earl
 
You can do it the exact same way or you can look into Stored
Procedures/SQL Functions. You could even go the route of LINQ.

As far as doing it the same way you're doing it you'll need the
SQLConnection, SQLCommand, and SQLDataReader objects.
or
You could go the route of datasets.

All in all there are tons of different ways to retrieve data from a
database just pick which one suits your style and application and
enjoy.
 
Earl Partridge said:
I just downloaded this VB Express. I need a little help in
understanding the request
for database data, that is the format/syntax compared to an SQL
statement.

In my SQL I would use a select statement... example

select LastName from Members where month(Birthday) = month(now)

How would this be coded in VB Express?
Click to expand...

At which point are you stuck? The SQL statement will be the same.

General docs:
http://msdn2.microsoft.com/en-us/library/wzabh8c4.aspx


Armin
 
I would like to stick with what I'm familiar with, but every time I try to
do a
"select xName from member where FName = "Dan" I get the response that
thinks I'm trying to do a Select Case.
Earl
 
I would like to stick with what I'm familiar with, but every time I try to
do a
"select xName from member where FName = "Dan" I get the response that
thinks I'm trying to do a Select Case.
Click to expand...

SQL is not VB.

You will need to add either System.Data.SQLClient or System.Data.ODBC
to your project. Then you will need to define and instantiate and Open
a Connection object. You then need to define and instantiate a Command
object linked to the open Connection. Then load the SQL command into
the Command object's CommandText property. You then need to set a
DataReader object to the return value of the Command's ExecuteReader
method. Then you would use the DataReader's Read method to return the
rows one at a time, and access the resultset values with the
DataReader.
 
After a bit of practice and experimenting, I do have the database open and
no errors in what I think is a LINQ statment... like
Dim q = From Member In BirthdaysDataSet.Member Select Member.MName

Now I'm trying to venture further to select specific records using Left$,
such as
"where left$(MName,3) = "Mik"

And also having trouble with getting a count of records.

Thanks.
Earl
 
Earl,

You cannot do that as your Select statement is a kind of impossible statement.

It should at least be something as if it was in Transact SQL as

\\\
Select LastName from Members where BirthDay > PreviousMonth And BirthDay < NextMonth
///

Or in VB something as

\\\
dim ar as new arraylist
for each person in persons
if person.birthday.month = now.month then
arrayList.Add(person)
end if
end for
//

Like natural languages you cannot create your own language.

As sample:

Zoals uzywane linque lei non peu make votre sprache.

All correct words in any language but beside Herfried for nobody probably readable in this group.

Cor
"Earl Partridge" <earlpNG@pearnet.com> schreef in bericht I just downloaded this VB Express. I need a little help in understanding the request
for database data, that is the format/syntax compared to an SQL statement.

In my SQL I would use a select statement... example

select LastName from Members where month(Birthday) = month(now)

How would this be coded in VB Express?

Earl
 
Earl Partridge said:
After a bit of practice and experimenting, I do have the database
open and no errors in what I think is a LINQ statment... like
Dim q = From Member In BirthdaysDataSet.Member Select Member.MName

Now I'm trying to venture further to select specific records using
Left$, such as
"where left$(MName,3) = "Mik"

And also having trouble with getting a count of records.
Click to expand...


Unfortunatelly, I don't know anything about LINQ yet, but I think SQLs
are put in an OleDBcommand/SQLCommand's CommandText property, then call
ExecuteReader.


Armin
 
Where I'm having trouble appears to be in the syntax. In your example...
if person.birthday.month = now.month then

Is person the table name or field name?

Not wanting or expecting anyone to write my code, but perhaps if I provide a sample
of my database, you might be better able to point me correctly.

DB Name Birthdays (In project as "BirthdaysDataSet")
Table Name: Member
Field Names: MName, Birthdate

Then using your example would I...
for each Member in Birthdays
if Member.Birthdate.month = now.month then
code
end if
end for

Earl
Earl,

Or in VB something as

\\\
dim ar as new arraylist
for each person in persons
if person.birthday.month = now.month then
arrayList.Add(person)
end if
end for
//

Cor
"Earl Partridge" <earlpNG@pearnet.com> schreef in bericht I just downloaded this VB Express. I need a little help in understanding the request
for database data, that is the format/syntax compared to an SQL statement.

In my SQL I would use a select statement... example

select LastName from Members where month(Birthday) = month(now)

How would this be coded in VB Express?

Earl
 
Earl,

In that case assuming that BirthdaysDataset is not the class but the
instanced dataset is is something like this strongly typed.

for each row as BirthdaysDataSet.MemberRow in BirthdaysDataSet.Member
if row.Birthdate.month = now.month then
code
end if
end for

Non strongly typed
for each row in BirthDaysDataSet.Tables("Member").Rows
if DirectCast(row("Birthdate"),DateTime).Month = now.month then
code
end if
end for


"Earl Partridge" <earlpNG@pearnet.com> schreef in bericht
Where I'm having trouble appears to be in the syntax. In your example...
if person.birthday.month = now.month then

Is person the table name or field name?

Not wanting or expecting anyone to write my code, but perhaps if I provide a
sample
of my database, you might be better able to point me correctly.

DB Name Birthdays (In project as "BirthdaysDataSet")
Table Name: Member
Field Names: MName, Birthdate

Then using your example would I...
for each Member in Birthdays
if Member.Birthdate.month = now.month then
code
end if
end for

Earl
Earl,

Or in VB something as

\\\
dim ar as new arraylist
for each person in persons
if person.birthday.month = now.month then
arrayList.Add(person)
end if
end for
//

Cor
"Earl Partridge" <earlpNG@pearnet.com> schreef in bericht
I just downloaded this VB Express. I need a little help in understanding
the request
for database data, that is the format/syntax compared to an SQL statement.

In my SQL I would use a select statement... example

select LastName from Members where month(Birthday) = month(now)

How would this be coded in VB Express?

Earl
 
Ahhh, it is that "row" thing. I got it to work with your first example.
Is "row" a reserved word? I don't anticipate doing much data manipulation
in
the VB.Net, simply retrieving and displaying data. I will continue to
manage the
database from MS Access. Could you point me to some helpful books on this
might help be understand better what I'm doing?
Earl
 
Earl,

This is a practical one.

Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)

There are not much books about VB and Jet (the official name for what you
call Access) anymore.

However SQL Express is as well free.

Cor
 
Back
Top