AnimeSuki Forum (
-   Tech Support (
-   -   SQL help (

RavenChild 2003-12-15 23:17

SQL help
For all the SQL users in this forum:

I have a SQL server and i was wondering about an app i made in vb6. I have all the drivers installed and i'm using the generic data control in vb6. it opens the database (anime.xls) fine but the order is screwed up. I have 48 entries in my database and it shows 1-16 perfectly but then goes to entry 38-48 and then back to 17-37. I have tried to use sql command but none will fix it. here is the code used to open the database:

ODBC;DSN=Excel Files;DBQ=C:\anime.xls;DefaultDir=C:\WINDOWS\syste m32;DriverId=790;FIL=excel 8.0;MaxBufferSize=2048;PageTimeout=5;UID=admin;

any help is greatly appreciated.

boneyjellyfish 2003-12-16 00:17

*stares* ... *rubs eyes*

Sorry, I'm not good with Visual Basic... I'm more of a PHP and JavaScript man.

ato 2003-12-16 02:13

First of all, define an index on whatever you are sorting and seraching on. It will really help when you get some data into the tables.

But to answer your question: YOu are guaranteed no certain order in the result set of an sql transaction unless you use "ORDER BY". In your case that would be something like "SELECT id, name FROM anime WHERE somethingsomething ORDER BY id".

Hope it helps :)

(But of course it is usually more productive to sort on date, name or something with an actual meaning rather than id...)

RavenChild 2003-12-16 07:54

thanks but i've tried runing this same database through .net which is very different and it still screws up. i used the order by query and it still screws up. i think there might be something wrong with my database so i'm going to redo it.

LynnieS 2003-12-16 10:45

You're ordering your anime collection by user/system-defined ID instead of, say, Series or Studio? So every time you add to your collection, you have to reorder everything?

What happens if you move your collection into, say, Access instead from Excel? The "order by" clause should not break and would only return the sorted data temporarily saved in the intermediate working table.

ato 2003-12-16 17:51

" still screws up." is a pretty bad description of your troubles ;) What is your actual error? You must consider that the interface you use against the DB is not really important, as long as you have designed your database layer in a fairly portable way. Which, incidently, is nearly always the right way of doing it. So just go for one interface just now (DAO, ADO or .NET - They will all get the job done), and tame the beast to do your bidding with SQL queries. That is something that'll work with nearly all RDBMs that you will come across these days.

This is a small link for you to read if you feel a small repetition on database design is in order. He does not treat normalization very thoroughly, but it's a fair primer anyway. (And the fruit of one minute with my buddy Google :) )

If you want to go with Access (the Jet RDBM) this could be of some help...And it will work for most other SQL dialects as well (though I highly recommend reading up on your specific manager as well).

Gambatte ne?

LynnieS 2003-12-16 18:20

Perhaps providing the SQL statement as well as the data would help also?

I'll be honest and say that I've never used Excel as a data source before, but rather as a front-end to display results returned from querying, say, 1+ Sybase (or DB2) databases, not to mention displays for price feeds and analytics engines.

If something like "select series, studio, volume, title from anime_collection order by series, volume" is giving unexpected results, I'm inclined to think that it's a data problem, rather than a ODBC bug. The latter, when it happens, generally blows up on me without returning anything.

Also, you mentioned that you used the "order by" clause, but what are the fields by which you're ordering? I had assumed that you were doing so by "id", but your original post has you trying to return the rows defined in the display order. What is the order by which the rows are displayed?

Shii 2003-12-16 18:26

Visual Basic... LOL :D

You could put that on your resume. "I have three years of experience in programming Visual Basic. I can also make a webpage with animated bouncing letters and a rainbow trail that follows the cursor."

LynnieS 2003-12-16 18:43


I don't think that we really need that here. VB, IMHO, is perfectly suitable as a development language depending on what you're trying to accomplish.

Animaniac 2003-12-16 22:59


Originally Posted by ashibaka
Visual Basic... LOL :D

You could put that on your resume. "I have three years of experience in programming Visual Basic. I can also make a webpage with animated bouncing letters and a rainbow trail that follows the cursor."

Very true.

I would suggest trying to use PHP with MySQL. PHP is very much like C, which any programmer should know, so it's a piece of cake to pick up.

ato 2003-12-17 02:12

Ummmm. And advocacy assists RavenChild in what way? Far be it from me to turn down some serious MS-smacking, but it's not really very productive, espacially in a help thread like this...

Anyways, how goes the programming RavenChild? I hope you have gotten your DB connection up and running according to your tastes. Just holler if you need some litterature pointers or something :)

RavenChild 2003-12-17 08:27


<vb code>
Public Sub Fixed(database As Integer)

' Eric Fixed his database by re-entering all the entries
' Go him!!!

database = 1

If database = 1 Then
MsgBox "Hurray! It works!!!", vbExclamation, "Hurray!!!"
MsgBox "F**k databases.... it still doesn't work...", , ":frustrated:"
End If

End Sub
</vb code>


Thanks to all who posted, if you can't understand the code above(I fixed it).


All times are GMT -5. The time now is 10:04.

Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2017, vBulletin Solutions Inc.