Go Back  Xtreme Visual Basic Talk > Legacy Visual Basic (VB 4/5/6) > Database and Reporting > Using functions in a SQL statement in VB6.0


Reply
 
Thread Tools Display Modes
  #1  
Old 08-01-2003, 12:18 AM
Cyber1000 Cyber1000 is offline
Newcomer
 
Join Date: Jul 2003
Location: Austria
Posts: 5
Smile Using functions in a SQL statement in VB6.0


Hi!
I have programmed a little program, which calculates my costs and my time for my Internet connection. I have used a simple textfile to store all the values. Now 3 years later this "database" gets bigger und uncomfortable. Now I want to change my program using an Access database. I have great experience in VB, Access and SQL, but now there is a problem:

I have a query in my mdb that lists all entries (every time I connected to the internet) and uses a function x (in the modules section of the mdb), which calculates the costs on every entry. (According to different weekdays, freetime, ...) In my mdb this works great, but I want to implement this in a visual basic program (in a datagrid) ==> Something like this.

Data1.Recordsource="SELECT Date_start, Time_start, Duration, x(Date_start; Duration) FROM List"
But there is a warning: Function x() not found
Now my question: Is it possible to use my function x (no matter if this function is in the mdb or my Visual basic file), which calculates the costs for each entry.
I know I can write data into a datagrid and then browsing this function trough each entry, but a SQL-Query including this function would be faster I think. There is no other way than using a function. To rewrite this function as SqL statement would be impossible (to complex)

I hope you can help me, thanks in advance,
Cyber
Reply With Quote
  #2  
Old 08-01-2003, 12:31 AM
PWNettle PWNettle is offline
Verbose Coder

Retired Moderator
* Guru *
 
Join Date: Dec 1999
Location: Phoenix, Arizona
Posts: 3,011
Default

You definitely cannot put a function from your Visual Basic code into an SQL select statement and execute it.

I'm not sure if you can use a SELECT statement that includes a custom function that's stored in a code module in the .mdb.

One thing that might work is to make a query in your .mdb out of your SQL SELECT and call that from your VB program rather than manually supplying the SELECT statement. Chances are that since the query is stored and executed on the .mdb side and doesn't have to be interpreted by DAO/ADO that this will work. You don't know what's happening to your SELECT statement when you manually form it and it's handled on the VB-side before it gets to your .mdb.

So, I'd recommend making a new query within your .mdb using your select statement (you can enter SQL view mode when designing a query to paste in SQL...and make sure your custom function is in a module in the .mdb):

SELECT Date_start, Time_start, Duration, x(Date_start; Duration) FROM List

...and then from the VB code call the query directly with:

Data1.Recordsource="thenameofthequerygoeshere"

or

Data1.Recordsource="SELECT * FROM thenameofthenewquery"

...whichever one you can get away with...ideally the shorter version.

Paul

PS - Normally I test to verify before I post something if I'm not sure about it but I'm not at liberty to do that at this time...so I hope this helps or inspires!
Reply With Quote
  #3  
Old 08-01-2003, 06:31 AM
Cyber1000 Cyber1000 is offline
Newcomer
 
Join Date: Jul 2003
Location: Austria
Posts: 5
Default

Hey, thanks for your fast reply!!!

I've tested your purposes before, because I had the same idea like you. The problem is, it doesn't work. Even if I have "thenameofthenewquery" in the mdb and set my Data1.Recordsource="thenameofthequerygoeshere"
There comes a message: RT Error 3085: Undefined function x in expression. I think, perhaps If you put a query as Datasource VB takes the whole SQL out of the mdb.
I have also tried to Generate a table with a Create Table Query and to use this generated table in this Query. But if you start this "Create Table Query" out of VB with db.Execute("thenameofthenewquery") you get the same error.
If you have any suggestions write back, if not perhaps I will find another workaround.
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

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 Off
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL error in Vb Code blong824 Database and Reporting 16 03-14-2004 05:07 PM
functions in sql statement in vb emata Database and Reporting 2 05-27-2003 11:07 AM
Problem with writing Twice Renidrag Web Programming 20 11-07-2002 02:52 PM
Creating Access Databases TomGuy Database and Reporting 8 07-10-2002 10:56 AM
Function with in SQL Statement kcwallace Database and Reporting 6 04-30-2002 09:22 AM

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
 
 
-->