database queries

[xeviva]

I'm trying, unsucessfully mind you, to implement something that does the following in vb.net, connected to an access database this is my kind of (part psudo) code. any ideas about how i get this to work?

dim count as integer
dim r as integer
dim entrySel as arraylist
dim dbsize as double

count = 0
dbsize = result of "select count from table"

while count < dbsize
r = int(rnd()*dbsize)

if entrysel.contains(r)=false
then
entrysel.add(r)

use "select * from table where id = 'r' "
and add this result to the datagrid/dataset

count++
end if
end while


or something like that, basically capable of the same thing. select a user specified number of items from a database at random, with no repettionn.
(Sorry my spelling is so bad today)

thanks in advance

[Rube74]

Is there any particular place where you seem to be getting stuck.

[xeviva]

sql queries, i think my syntax is screwed to start with, and filling the data set in this manner

[Rube74]

Is there a specific error that you are getting... or are you just not getting any results?

[xeviva]

not sure how to do a query, that maybe returns 1 result, put it into a results set, then do another query, adding that result to the first one and so on.

I don't know how to code this properly at all. i can do the logic, but not the proper code.

Can you help me code it?

[Rube74]

Well, here is some code for querying data. This particular one should work with ODBC-based databases like Access.

----------------------------------------------------------------------------------

Shared Function QueryData(ByVal DBConnString As String, ByVal queryString As String) As System.Data.DataSet

Dim DBConn As New Data.OleDb.OleDbConnection(DBConnString)
Dim DBSelectCommand As New Data.OleDb.OleDbCommand(queryString, DBConn)
Dim ds As New Data.DataSet()
Dim adapter As New Data.OleDb.OleDbDataAdapter()

Try
DBConn.Open()
adapter.SelectCommand = DBSelectCommand
adapter.Fill(ds)
DBConn.Close()
Return ds
Catch ex As Data.OleDb.OleDbException
MsgBox(ex.ToString())
End Try

End Function

------------------------------------------------------------------------------

Basically, the idea is this:

First, you want to create a database connection object, a dataset object, and a database adapter object. The connection object handles the process of connecting to the database, the adapter handles the transfer of data between the program and the database, and the dataset holds your query results.

As you can see, the database connection object has a connection string associated with it. This string basically tells the connection where the database is... for example, for Access databases, you would pass the physical location of the database file along with the type of database you're connecting to.

Second, you create your database query command. For simple queries, this is where you would put your SQL code. As you can see, this takes the connection object and the query SQL code as arguments.

Finally, you run through the process of actually fill the dataset. You do this by opening the connection, setting the adapter's SelectCommand property to the database query command you created, running the adapters Fill method (using your dataset as an argument... this is what will get filled), and finally closing the database connection. You can also see that I put this in a Try-Catch statement because you can never tell what kind of database errors you might get (mostly related to incorrect SQL syntax, missing columns, missing records, and the like).

You should probably look at data adapters if you want to do more complicated things like adding tables to your datasets and handling updates and inserts.

[xeviva]

ok thanks so far,

what is the exact syntax for the sql queries? As every time I try to do anything other than select * from table, I fall into my catch block of code, so I'm assuming there's something wrong with the way I'm setting out the queries?

When I try to "select count from table" I get a responce of 0 where there are 50 entries in the database, why is this happening and how can I correct it?

[Rube74]

What kind of error are you getting from the Catch block?

[DdH]

Try

select count(*) from table
instat of
select count from table

Danny

[xeviva]

cheers DdH :)

[xeviva]

the error message out put by the catch block is huge! far too much to reproduce, but it seems to be a problem mostly with the data adapter. and with the sql queries second. I'll have to recheck the data adapter settings, maybe i've done something stupid somewhere.

I still can't get the correct result to select count(*) from table, it still tells me 0, where I know it should be 50. I can get the data adapter to preview the data, and it finds 50 records in the table, but counts 0?

[Rube74]

I would suggest you run through the code line by line using breakpoints and the de-bugger and see exactly where the code is failing. If it is only coming out of the catch block, then it's a database issue. Anywhere else, and it's a code issue.

As for the SQL... what happens when you run the same code outside of VB? Does the same query run okay in the database you're using?

[xeviva]

the way i write it in Access it works, I would write sql like that in oracle sql databases and it would be fine. I'm guessing that it has to be phrased differently to what i'm doing?


DataAdapter.SelectCommand.CommandText = "Select * From table Where Item ID = '2'"

[Rube74]

Try this in VB:

DataAdapter.SelectCommand.CommandText = "Select * From table Where Item ID = ""2"""

VB is very particular about quotes in SQL statements. I've had that issue myself.

[gjs368]

You also might tryDataAdapter.SelectCommand.CommandText = "Select * From table Where [Item ID] = '2'"The space in 'Item ID' could be giving you a problem

[Rube74]

Actually, I think both of those issues are right. Acces is very particular about spaces, as well.

[xeviva]

this piece of code

adapter.SelectCommand.CommandText = ("Select * From TrackList Where [Track ID] = ""2""")


gives this error

An unhandled exception of type 'System.NullReferenceException' occurred in radio station.exe

why?

there is an entry in the database with id 2, but only 1 of them.

[Rube74]

If the datatype of the ID is a number, maybe it would help to try it without the quotes. That's another possible problem, since quotes are usually associated with strings.

[xeviva]

still doesn't help i'm afraid.

Can anyone help me please?

[Rube74]

You usually only get that error message when you aren't getting any records comming into the dataset. Are you sure that your connection is valid?

[xeviva]

well,
preview data works within adapter properties works, and it produces the full list of records when i do select * from table.

Thats why I thought it must be the way I was putting down the SQL in my code. stupid ain't it? :eek:

[Rube74]

yeah, that says to me that the connection is fine and that the adapter should be working fine. Are you sure the dataset is set up right?

[xeviva]

Hi
As far as i know the data set is fine.
:confused:

[Rube74]

Try running the program through the debugger to see where the error is coming from. Your best bet is to probably set up a break point around the step where you begin creating the adapter and then run through the code line by line to see where errors are thrown. It might also help to see what your local variables look like. It's possible that your query might not look as you expect it to look.