AnimeSuki Forums

Register Forum Rules FAQ Members List Social Groups Search Today's Posts Mark Forums Read

Go Back   AnimeSuki Forum > Support > Tech Support

Notices

Reply
 
Thread Tools
Old 2003-12-15, 23:17   Link #1
RavenChild
エリック
 
 
Join Date: Nov 2003
Location: my closet, deep and dark
Age: 35
Send a message via AIM to RavenChild Send a message via MSN to RavenChild
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.
__________________
************* RavenChild **************
RavenChild is offline   Reply With Quote
Old 2003-12-16, 00:17   Link #2
boneyjellyfish
Evangelist of the Kazoo
 
 
Join Date: Apr 2003
Location: AnimeSuki Forums
*stares* ... *rubs eyes*

Sorry, I'm not good with Visual Basic... I'm more of a PHP and JavaScript man.
boneyjellyfish is offline   Reply With Quote
Old 2003-12-16, 02:13   Link #3
ato
Oups...
 
Join Date: Jan 2003
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...)

Last edited by ato; 2003-12-16 at 17:35.
ato is offline   Reply With Quote
Old 2003-12-16, 07:54   Link #4
RavenChild
エリック
 
 
Join Date: Nov 2003
Location: my closet, deep and dark
Age: 35
Send a message via AIM to RavenChild Send a message via MSN to RavenChild
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.
__________________
************* RavenChild **************
RavenChild is offline   Reply With Quote
Old 2003-12-16, 10:45   Link #5
LynnieS
Senior Member
 
 
Join Date: Mar 2003
Location: China
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.
LynnieS is offline   Reply With Quote
Old 2003-12-16, 17:51   Link #6
ato
Oups...
 
Join Date: Jan 2003
"...it 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?
ato is offline   Reply With Quote
Old 2003-12-16, 18:20   Link #7
LynnieS
Senior Member
 
 
Join Date: Mar 2003
Location: China
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?
LynnieS is offline   Reply With Quote
Old 2003-12-16, 18:26   Link #8
Shii
Afflicted by the vanities
*Fansubber
 
 
Join Date: Feb 2003
Location: Fish-shape Paumanok
Age: 36
Visual Basic... LOL

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."
__________________
Learn to define and spell moé
Shii (formerly known as ashibaka)
Shii is offline   Reply With Quote
Old 2003-12-16, 18:43   Link #9
LynnieS
Senior Member
 
 
Join Date: Mar 2003
Location: China


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.
LynnieS is offline   Reply With Quote
Old 2003-12-16, 22:59   Link #10
Animaniac
On leave?
 
Join Date: Apr 2003
Location: Cambridge, MA
Quote:
Originally Posted by ashibaka
Visual Basic... LOL

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.
Animaniac is offline   Reply With Quote
Old 2003-12-17, 02:12   Link #11
ato
Oups...
 
Join Date: Jan 2003
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
ato is offline   Reply With Quote
Old 2003-12-17, 08:27   Link #12
RavenChild
エリック
 
 
Join Date: Nov 2003
Location: my closet, deep and dark
Age: 35
Send a message via AIM to RavenChild Send a message via MSN to RavenChild
Hurray!!!!

<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!!!"
End
Else
MsgBox "F**k databases.... it still doesn't work...", , ""
End If

End Sub
</vb code>

<edit>

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

</edit>
__________________
************* RavenChild **************
RavenChild is offline   Reply With Quote
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


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


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
We use Silk.