Re: My Book is Incomplete Regarding SQL Commands ;-(

Bill,

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

Robin S.
-----------------------------
"William (Bill) Vaughn" <billvaRemoveThis@xxxxxxxxx> wrote in message
news:%23rJTdMKbHHA.5052@xxxxxxxxxxxxxxxxxxxxxxx
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)
-----------------------------------------------------------------------------------------------------------------------

"pooba53" <pooba53@xxxxxxxxx> wrote in message
news:1174527401.591072.280420@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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

On Mar 21, 12:56 am, "RobinS" <Rob...@xxxxxxxxxxxxxxx> wrote:
Apparently you bought the wrong ones. ;-)

Robin S.
---------------------------"pooba53" <poob...@xxxxxxxxx> wrote in
message

news:1174446783.089017.248070@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

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

On Mar 20, 5:59 pm, "William \(Bill\) Vaughn"
<billvaRemoveT...@xxxxxxxxxx> wrote:
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 Speakerwww.betav.com/blog/billvawww.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.
__________________________________
Visitwww.hitchhikerguides.nettoget 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)
-----------------------------------------------------------------------------------------------------------------------

"Jim Brandley" <Jim.Brand...@xxxxxxxxxxxxxxxxxx> wrote in message

news:eVSeorzaHHA.2300@xxxxxxxxxxxxxxxxxxxxxxx

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.

"pooba53" <poob...@xxxxxxxxx> wrote in message
news:1174426969.138413.75590@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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






.

Relevant Pages

  • Re: My Book is Incomplete Regarding SQL Commands ;-(
    ... Hitchhiker's Guide to Visual Studio and SQL Server ... Create a Connection object and pass in a ConnectionString to ... manage the database table you reference. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: My Book is Incomplete Regarding SQL Commands ;-(
    ... Visual Studio but not to VS 2003. ... Create a Connection object and pass in a ConnectionString to address ... manage the database table you reference. ... Yep, my latest book can help too, but mostly if you're targeting SQL Server ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: MS Access DAO -> ADO.NET Migration
    ... full SQL Server and I see the logic you explained in a multi user ... allow two users to access the same database file Read/Write at any given ... The book was a pleasure to read after the gibberish that Microsoft 'puts ... Hitchhiker's Guide to Visual Studio and SQL Server ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: MS Access DAO -> ADO.NET Migration
    ... The SQLCe engine requires the 2.0 Framework. ... Hitchhiker's Guide to Visual Studio and SQL Server ... However, it does build the SSCE database, just some of the definitions are ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: How secure is Access 2007
    ... I'm neither an advocate of top posting or bottom posting. ... Note that if the back end is a server database such as ... SQL Server, it appears to be free from this weakness. ... "Mining" the mid-tier database password from the front end. ...
    (microsoft.public.access.security)