My Book is Incomplete Regarding SQL Commands ;-(

[pooba53]

The book I'm learning VB .NET from barely scratches the surface of
ADO.NET. I have a working application that has a database connection
(to Access), a data connection OlDbConnection1, a data adapter
OlDbDataAdpater1, and my text boxes are working as expected when bound
to the Access fields. The book has "Wizards" do all the "magic".

What my book does NOT cover is how to use the above items to generate
sql commands and capture the results. Sort of pitiful.

I know SQL and have used it with Linux, PHP, and MySQL. I just don't
understand the most common thing in the realm of getting information
from a database using VB .NET and VS 2003.

I do have a dataset created too called "Data1".

I already have everything set up, I just don't know the syntax to
create a select statement and capture the results!

Thanks for putting up with my dunce cap...
-Dan

 

[Jim Brandley]

David Sceppa wrote a book called "Microsoft ADO.NET". It was a big help to
me when I was getting started. He has lots of examples and pointers for
performance.

 

[William \(Bill\) Vaughn]

I agree, David's book is great. I wrote a book that maps to the original
Visual Studio but not to VS 2003.
Visual Studio (VB.NET) has code generators in each of its versions that
automatically generates the code (including the SELECT query) when you drag
and drop data tables or Data Source tables on the form. One approach I use
in my book is to dig into the generated code (click "show all files" in the
solution explorer). Here you will see a lot of code--used to
Create a Connection object and pass in a ConnectionString to address
your database.
Create a DataAdapter or TableAdapter (depending on the version) to
manage the database table you reference.
Create a set of Command objects--one each to Select, Insert, Update and
Delete rows (SelectCommand, InsertCommand etc.)
Use the DataAdapter or TableAdapter Fill method to execute the
SelectCommand, return a rowset and populate a DataTable
Bind the DataTable returned to the TextBox or DataGridView control(s) on
your form.
Build the UI needed to navigate and launch the Update method on the
DataAdapter that posts changes to the database (and call the InsertCommand,
DeleteCommand and UpdateCommand).

Yep, my latest book can help too, but mostly if you're targeting SQL Server
(any version).
There are also walkthroughs in the documentation that can help as well.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

 

[pooba53]

I appreciate the book suggestions. Thanks.

What I really need is some sample code that addresses my original
question. I've spent enough $$$ on books presently and they were all
supposed to be wonderful ;-)

-Dan

 

[RobinS]

Apparently you bought the wrong ones. ;-)

Robin S.
---------------------------

 

[Shawn B.]

What I really need is some sample code that addresses my original

question. I've spent enough $$$ on books presently and they were all
supposed to be wonderful ;-)

I have over 800 programming books on my shelf and a fai share of them are
..NET related. I usually try to pick the worthy books and leave all others
alone. That said, I don't have a single ADO.NET book on my shelf because I
have yet to find a book that covers some detail that I couldn't already
figure out from the documentation or a very cursory google search using the
right keywords (its harder and harder these days to get relevance in
google). In fact, I have very few client database books at all, because for
some strange reason, it is the only topic I can think of that really isn't
well suited for print.

Google has been far more useful to me than any book on the subject, but
there are plenty of people I work with that will dissagree with me. The
standard disclaimer applies: your mileage may vary. Yet for most other
topics, printed text has been far more useful than Google (in general but
not totally).


Thanks,
Shawn

 

[pooba53]

I think my post was misinterpreted. I'm not complaining about the book
or looking for a new one. I'm looking for a code sample (which should
be VERY straightforward for someone who is not a noob) which addresses
my question.

I'll go see if I can figure it out on my own...which so far has not
worked too well.

-Dan

 

[pooba53]

LOL

Yeah, I went to my local library today and picked up "Visual
Basic .NET in 21 Day". I normally do not like the 21 days books, but
low and behold there was a swell example and explanation of how to
access and manipulate a DataSet once created.

My DataSet is "DsMyMonthlyIncome1"

Dim objTable As DataTable
objTable = DsMyMonthlyIncome1.Tables("Budget Items")

Dim objRow As DataRow
For Each objRow In objTable.Rows
If objRow.Item("FirstName") = "" Or
objRow.Item("LastName") = "" Or objRow.Item("Address1") = "" Or
objRow.Item("City") = "" Or objRow.Item("State") = "" Or
objRow.Item("Zip") = "" Or objRow.Item("Phone") = "" Or
objRow.Item("Email") = "" Then
MsgBox("Please enter your personal information under
File->Client Information before continuing.")
MenuItem6.PerformClick()
End If
Next

MenuItem6 brings up a registration window with bound textboxes that
save information to my Access db.

Hope this helps someone struggling ;-)

-Dan

 

[William \(Bill\) Vaughn]

Ah, this example seems hard to maintain. Consider that the "items"
correspond to the data table columns and are exposed as an array. This can
also be walked like the Rows collection. Best Practice says that you should
avoid hard-coding column names for a variety of reasons. First, as I said,
it's hard to maintain. If the schema changes you have to revisit the code,
recompile and redeploy. Next, it's far (far) slower as in two orders of
magnitude slower.

I've tossed together another approach as shown below. Yes, the DataTable
should be declared more globally so it can be accessed above the sub, but
you get the idea... Note that it's wise to test for NULL values before
trying to test for a value. That's because we don't store placeholders in
datatable values. If we don't know the value, we store a NULL. Of course,
column values might also be numbers or other types that aren't necessarily
strings and would not have a "" as a placeholder.


Private Sub BuildCommand()
Try
cmd = New SqlCommand( _
"SELECT PubName,Address,City FROM Publishers " _
& " WHERE State = @StateWanted", cn)
cmd.Parameters.Add("@StateWanted", _
SqlDbType.Char, 2).Value = "CA"
Dim dr As SqlDataReader

dr = cmd.ExecuteReader
Dim tb As New DataTable
tb.Load(dr)
For Each row As DataRow In tb.Rows
For Each col As Object In row.ItemArray
If col Is DBNull.Value OrElse col.ToString = "" Then
PromptForChanges()
End If
Next
Next
Catch exsql As SqlException
MessageBox.Show(exsql.ToString)
Catch ex As Exception
Debug.Assert(False, ex.ToString)
Finally
cn.Close()
End Try
End Sub


--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

 

[RobinS]

Bill,

That is a nice, short, elegant piece of code. Thanks for posting it.

Robin S.
-----------------------------