Select Statement

  • Thread starter Thread starter Carlo B
  • Start date Start date
C

Carlo B

Hi
I need to use a listbox to display authors from the authors table in the
standard BIBLIO.MDB. I haven't even got to this point yet and the code
below is bombing out on the line 'dataAdapter.Fill(dt)'.
If I only run 'Select * from Titles' then it works fine. Even if I try
'Select Titles.title from .....' it bombs.
Please help
Thanks
Carlo



Dim title, publisher As String
title = txtTitle.Text
Dim dt As New DataTable()
Dim connstr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" &
"Data Source = BIBLIO.MDB"
Dim sqlstr As String = "Select * from Titles inner join 'Title
Author' on Titles.ISBN = 'Title Author'.ISBN inner join Authors on Title
Author.Au_ID = Authors.Au_ID where isbn = '0-0038326-7-8'"
Dim dataAdapter As New OleDb.OleDbDataAdapter(sqlstr, connstr)
dataAdapter.Fill(dt)
dataAdapter.Dispose()
lstDisplay.DataSource = dt
lstDisplay.DisplayMember = "Title"
 
Carlo:

I'd highly recommend against using joins like this. Assuming that syntax is
correct, you are still begging for trouble. You have a field with spaces in
it and you'll need to use [FirstPart SecondPart] instead of single quotes.
The reasons for both have been belabored in the various ngs, but in sum,
using joins like this is very inefficient, makes dataadapter updates
nightmarish at best and makes is very costly. Having field names like that
invites disaster. At some point in the future, either you or some other
/new developer will forget to use the [] and teh query will bomb. time will
be spent tracking it down, time will be spent fixing it and time will be
spent distributing it. No matter how careful you are, the odds are against
you and it will probably cause at least one problem. Depending no the
circumstances, the client might not care, or they might decide it's the
straw that breaks the camels back. Either way it's a lot of risk just to
keep a bad habit.

Here's a discussion of using the datarelation instead of using Joins.
http://www.knowdotnet.com/articles/datarelation.html


Finally, do yourself one other favor and get rid of the concatenated dynamic
sql. This has also been beat into the ground over and over but in short,
it's very error prone, inefficient, a huge security risk and no matter how
careful you are, you'll get a last name like O'Brien or some other thing
with a ' in it and you'll have to find it, fix it, redistribute the fix, all
the while causing the customer unnecessary inconvenience. Another thing
that has a lot of downside just to keep a bad habit in place.

Don't mean to lecture, but these things are 'bad' precisely b/c they cause
problems like the one you are experiencing. You may be experiencing
something else too, but the space issue is almost surely one of the problem.

If you have any questions, let me know.

Cheers,

Bill
 
Hi Carlo,

I like your approach, in this way you get a fine nice dataset.

However I am not a SQL man. So I am not sure if it is because you are using
a datatable instead of a dataset or in the select. Probably your error is in
the select, you can see that better when you build a try and catch block
around your fill. (I have changed the code beneat for it and maybe because
you are using a dataset with that code you will not get an error at all)

Dim title, publisher As String
title = txtTitle.Text
Dim ds As New DataSet()
Dim connstr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" &
"Data Source = BIBLIO.MDB"
Dim sqlstr As String = "Select * from Titles inner join 'Title
Author' on Titles.ISBN = 'Title Author'.ISBN inner join Authors on Title
Author.Au_ID = Authors.Au_ID where isbn = '0-0038326-7-8'"
Dim dataAdapter As New OleDb.OleDbDataAdapter(sqlstr, connstr)
Try
dataAdapter.Fill(ds)
Catch sqlExc As SqlException
MessageBox.Show(sqlExc.ToString)
Catch exc As Exception
MessageBox.Show(exc.Message)
End Try

' dataAdapter.Dispose()

What is the deeper reason of that dispose? As far as I can see see you have
created the adapter global, so now you can use it only time, while there
will be no harm at all as far as I know by not disposing it.


lstDisplay.DataSource = ds.tables(0)
lstDisplay.DisplayMember = "Title"

I hope this will help?

Cor
 
Hi Cor:

I was just wondering what your experience has been with the join? I've
found it very problematic b/c update logic is next to impossible (I may just
not know how to do it) and you end up pulling over redundant data which is a
performance hindrance. Using a datarelation instead has always worked for
me b/c I can pull over less overall data, update logic is simple and I can
still take advantage of my constraints.

Have you been able to get around this? Particularly, what does update logic
look like? If this is doable I'd definitely like to know how.

Thanks again,

Bill
 
TO me it looks like the error is here

Dim sqlstr As String = "Select * from Titles inner join 'Title Author' ...


Single quotes are used in SQL to offset a string, so the SQL is thinking you
have a string literal here instead of a table name. try replacing with []
instead. I may be off track, but thats my giut instict. Also, the select *
will return all data from all joined tables. if you only want from title,
should make it Titles.*

Dim sqlstr As String = "Select Titles.* from Titles inner join [Title
Author] ...
 
Thanks for all the tips. The more info the better!
I'm just starting out and the text book I'm using has a chapter using
'joins' etc and I'm trying to go through the questions at the end of
each chapter based on the chapter.
Thanks
 
Hi Bill and Carlo

I started to tell that I do not like SQL so I have already long time the
same approach as you show.
However I do not like it, there is to much data in my opinion taken into the
dataset.

What I did not know whas this
http://support.microsoft.com/default.aspx?scid=kb;en-us;318646

It says: (read further after it, because I made it almost complete)
----------------------------------------------------------------------------
-------------------
If you use the Query Builder tool as part of the Data Adapter Configuration
Wizard for the OLEDBDataAdapter object, and you connect to a Microsoft
Access database with the Microsoft Jet OLEDB provider, query syntax that
involves more than one table join may be generated incorrectly. This will
result in an error that resembles the following (table and column names are
for illustration purposes only):

Syntax error (missing operator) in query expression 'Employees.EmployeeID =
Orders.EmployeeID INNER JOIN [Order Details] ON Orders.OrderID = [Order
Details].OrderID INNER JOIN Customers ON Customers.CustomerID =
Orders.CustomerID
----------------------------------------------------------------------------
--------------------
I did try it because I was also curious how this could be done and than I
came in what is beneath(every try deleted). I think when Bill sees this I do
not have to tell him how to create the update etc from the tables. .

\\\
Dim Sql As String = "SELECT * from A, B Where " & _
"A.n = B.n AND A.n = 10"
Dim Conn As New OleDbConnection(connString)
Dim da As New OleDbDataAdapter(Sql, Conn)
da.Fill(ds, "A")
da.Fill(ds, "B")
Conn.Close()
'In this sample the datarelation is in my opinion not needed
'However without that A.n = 10 than it is needed
'------------------------------
Dim drlA As New DataRelation _
("AA", ds.Tables("A").Columns("A.n"), _
ds.Tables("B").Columns("B.n"))
ds.Relations.Add(drlA)
Dim dv As New DataView(ds.Tables("A"))
DataGrid1.DataSource = dv
DataGrid1.Expand(-1)
////
'Without datarelation just change dv in ds

I hope this makes things clear also for you both?

Cor
 
Back
Top