|
nz is an old Access/VB function that does not exist in SQL server. what you need is ISNULL()
Sum(ISNULL(Order.item_qty,0))
|
|
|
|
|
this is the query i tested with:
select sum(ISNULL(inventory_id,0)) from inventory
this is the error:
Wrong number of arguments used with function in query expression 'SUM(ISNULL(inventory_id,0)'
Share your knowledge it's one way to achieve immortality
http://www.rabihtawil.com
http://blog.rabihtawil.com
http://www.coderisland.com
|
|
|
|
|
Sorry, I use SQL Server, I have no idea what the syntax for Oracle is. However you are on the right track, SQL Server has Books On Line which is installed with every client, I imagine Oracle something similar.
|
|
|
|
|
Sorry maybe you misunderstood me prior...
Frontend: vb.net
Backend: MS access 2007
it seems that vb.net does not like ISNULL too...
Share your knowledge it's one way to achieve immortality
http://www.rabihtawil.com
http://blog.rabihtawil.com
http://www.coderisland.com
|
|
|
|
|
rabih.tawil wrote: it seems that vb.net does not like ISNULL too...
I don't think you understand your own code at all. VB is just passing 'ISNULL' to Access, it has no opinion on it at all. You need to read up on Access SQL, but Access is not that great, I would not be surprised to find it was missing some stuff.
Christian Graus
Driven to the arms of OSX by Vista.
Read my blog to find out how I've worked around bugs in Microsoft tools and frameworks.
|
|
|
|
|
Christian Graus wrote: I don't think you understand your own code at all
Look my friend, this is a 3 month project, i think i understand exactly what i'm doing, just because it has not come accross you before does not mean i don't know what i'm doing...
Christian Graus wrote: VB is just passing 'ISNULL' to Access
OH REALLY? wow great discovery!! now explain to me, why VB wont pass NZ function directly to access???
Christian Graus wrote: it has no opinion on it at all.
I am sorry to tell you my friend, NO you are wrong, the sql query u send will execute depends on the access driver.
Christian Graus wrote: ou need to read up on Access SQL
The query executes successfully in access.. no need to read anything about access.
Christian Graus wrote: but Access is not that great, I would not be surprised to find it was missing some stuff.
Humm, that is your opinion, remember MS access is not a small application made over night for a quick criticism even if one is an mvp. however, the customer wanted MS access so i give him access 
Share your knowledge it's one way to achieve immortality
http://www.rabihtawil.com
http://blog.rabihtawil.com
http://www.coderisland.com
|
|
|
|
|
rabih.tawil wrote: Look my friend, this is a 3 month project, i think i understand exactly what i'm doing, just because it has not come accross you before does not mean i don't know what i'm doing...
ROTFL. OK, perhaps you just meant the opposite of what you said. I apologise.
rabih.tawil wrote: OH REALLY? wow great discovery!! now explain to me, why VB wont pass NZ function directly to access???
Now it seems to me like you have no idea after all. VB IS going to pass your code directly to Access. What else would it do with them ?
rabih.tawil wrote: I am sorry to tell you my friend, NO you are wrong, the sql query u send will execute depends on the access driver.
Wrong. The generic driver will use ODBC ( I think ), but whatever it uses, it will NOT interpret the SQL, it will pass it to the database.
rabih.tawil wrote: Humm, that is your opinion, remember MS access is not a small application made over night for a quick criticism even if one is an mvp.
No, it's a POS that was developed a bit at a time, over time, with no thought to conformance to SQL standards.
rabih.tawil wrote: however, the customer wanted MS access so i give him access
Well, fine. So now you need to learn what the Access SQL dialect is like, and use it accordingly
I did a google, and Access does appear to support ISNULL.
Christian Graus
Driven to the arms of OSX by Vista.
Read my blog to find out how I've worked around bugs in Microsoft tools and frameworks.
|
|
|
|
|
From here webcheatsheet[^]
In Access, the IsNull function returns a boolean value that indicates whether or not an expression contains no valid data (Null).
The syntax for the IsNull function is:
IsNull ( expression )
In Access, the expression argument can be any numeric or string expression. The IsNull function returns True (-1) if the expression contains the Null value; otherwise, it returns False (0). Null is not the same as a zero-length string, which is often referred to as a null string.
Important: Using the IsNull function is the only way from within Access to determine whether or not an expression contains a null value.
Example
IsNull(null) returns TRUE
IsNull("WebCheatSheet") returns FALSE
IsNull(2006) returns FALSE
I don't speak Idiot - please talk slowly and clearly
'This space for rent'
Driven to the arms of Heineken by the wife
|
|
|
|
|
Andy_L_J wrote: IsNull("WebCheatSheet") returns FALSE
IsNull(2006) returns FALSE
LOL - looks like you got the same google hit I did.
Christian Graus
Driven to the arms of OSX by Vista.
Read my blog to find out how I've worked around bugs in Microsoft tools and frameworks.
|
|
|
|
|
VB.NET has nothing to do with the error. It is not compiling your SQL string. The Jet database engine (Access) is what is throwing the error.
|
|
|
|
|
You need to read the documentation. Isnull returns true or false, depending on if the value is null or not. It takes one arguement, not two. You should RTFM instead of just guessing.
Unfortunately, Nz appears to be the function that you should use in Access in place of Coalesce, which is what you're really trying to do. However, Nz evaluates if something is zero, not null. So, that could be why you had an error in the first place. I don't use access, I found all this out with a tiny bit of googling. What you need is an equivelant to the ? : syntax in C#, which I think is Iif in VB.
Christian Graus
Driven to the arms of OSX by Vista.
Read my blog to find out how I've worked around bugs in Microsoft tools and frameworks.
|
|
|
|
|
Christian Graus wrote: You should RTFM instead of just guessing.
hi Mr genius,
i didn't introduce isnull as an alternative to nz it was Mycroft Holmes, & it doesn't need manuals to understand what it does it looks like it returns Boolean Is??? NULL?? yes NO?? intellisense in vb.net anyway shows you that it is not a big research FYOFK
here is a small SCIENTIFIC DEMONSTRATION video for you to prove you WRONG that ms access is not throwing the exception, the query runs fine in access.
http://www.youtube.com/watch?v=6KhWMcSf_ys
UNLIKE SOME PEOPLE I KEEP MY MOUTH SHUT WHEN I DON'T KNOW ABOUT SOMETHING.
in case you don't believe it:
HERE IS THE SOLUTION DOWNLOAD IT & EXPERIMENT WITH IT YOURSELF, data.accdb is the access & i guess you know how to open a vb.net solution.
http://www.coderisland.com/query_testing.zip
now Please, if your scientific is going to be girl talk do not waist my time,
if your scientific, i challenge you.
Share your knowledge it's one way to achieve immortality
http://www.rabihtawil.com
http://blog.rabihtawil.com
http://www.coderisland.com
|
|
|
|
|
If this is a copy'n'paste version of your exact code, line for line, then you've got a problem. VB.NET doesn't (until 2010 anyway) do automatic line continuation. So, if you're seeing VB.NET throw up these errors, you have to reformat your SQL string like this:
my query1 = "SELECT Inventory.Inventory_Id, Inventory.Inventory_description, " & _
"Inventory.Inventory_stock_count, nz((SELECT Sum(Order.item_qty) AS SumOfitem_qty " & _
"FROM [Order] " & _
"WHERE Order.From_date<=Date() AND (Order.To_date)>=Date() AND Order.Item_ID=""I"" & " & _
"Inventory.Inventory_Id),0) AS CurrentlyOut, " & _
"[Inventory_stock_count]-Nz((SELECT Sum(Order.item_qty) AS SumOfitem_qtyFROM [Order] " & _
"WHERE Order.From_date<=Date() AND (Order.To_date)>=Date() AND Order.Item_ID=""I"" & " & _
"Inventory.Inventory_Id),0) AS NetHereFROM Inventory"
Learn how VB.NET handles string literals before you start bashing other people for their "lack of knowledge", Mr. Genius.
|
|
|
|
|
Dave Kreskowiak wrote: VB.NET doesn't (until 2010 anyway)
hahaha yes it does in vs 2010 correct your knowledge download it & try it 
your copy paste is irrelevant because i wrote it on one single line,
when i call you mr genius Mr mvp i am too humble to accept that title from you so keep it for yourself
Share your knowledge it's one way to achieve immortality
http://www.rabihtawil.com
http://blog.rabihtawil.com
http://www.coderisland.com
|
|
|
|
|
Say I wanted to create a small program that automatically rotated between websites every X seconds, where would I need to start? For instance, I create a simple form, it has 2 buttons. A start and a stop button. The stop button just quits the program completely, so I'm using the Me.Close() function for that, so I'm not worried about that. However, I want the start button to do something like.....open Google in Internet Explorer, and after say...45 seconds, rotate to Yahoo, and then another 45 seconds, rotate to MSN, and so forth and so on. Now, say I wanted the rotations to be less than 2 minutes, but more than 30 seconds, where would I start there? Would I use the Do/Loop functions?
|
|
|
|
|
This is trivial. Just use a timer.
Christian Graus
Driven to the arms of OSX by Vista.
Read my blog to find out how I've worked around bugs in Microsoft tools and frameworks.
|
|
|
|
|
So glad this is trivial to an expert like yourself. However, being a beginner, the question still begs to differ. So, I go to using a timer, that's fine, but where do I begin? How do I launch the websites and make them rotate? Which function do I do? Obviously it won't be a Do-<whatever> function. Where would you suggest I start? And since it's trivial, let's do it this way. I want to make the timers connect to and then jump through using proxy. I.E., when the program is first launched, I want it to connect to a predefined proxy server and run through all the websites one time. Then on it's return path (or loop if you will), I want it to connect to a second predefined proxy server and have it run through the websites like that, so forth and so on. Any suggestions for that?
|
|
|
|
|
Have a list of the sites you want to visit in an arraylist, have an integer that will increment from 0 to arraylist.count-1, then put on a timer and set the interval to 45 seconds, then on the timers .tick event navigate to the url stored in arraylist(integer you are incrementing), increment integer.
I will try to post some code when i get the chance
|
|
|
|
|
Ok so i got the code done quickly
Add a webbrowser control, a timer, and a button. Set the timer at the interval you want.
If you want a stop button, just add one with Me.Timer1.Stop, thats it.
Public Class Form1
Dim siteCount As Integer = 0
Dim aList As New ArrayList
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
aList.Add("http://www.jmbundy.blogspot.com/")
aList.Add("http://www.codeproject.com/Messages/3244826/Re-Looping-Website-Viewer.aspx")
aList.Add("http://www.google.com/webhp?sourceid=navclient&ie=UTF-8")
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Me.Timer1.Start()
End Sub
Private Sub Timer1_Tick(ByVal sender As Object, ByVal e As System.EventArgs) Handles Timer1.Tick
Me.WebBrowser1.Navigate(Me.aList(siteCount))
If siteCount + 1 <= Me.aList.Count - 1 Then
siteCount += 1
Else : siteCount = 0
End If
End Sub
End Class
|
|
|
|
|
This is a wonderful solution and is greatly appreciated. Now all I have to do is figure out how to make it jump through proxies. I'm wondering, could I use the GetStream() function to try and make it jump through proxies?
|
|
|
|
|
|
John, really great resources for reviewing! Thanks so much!
|
|
|
|
|
Hi,
Guys I need your guidance..
I have MdiForm with a progress bar. when I save on a child form, I want to have the progress bar to show the saving progress.
To be more clearer, when I click save_button on a child from, the bar should show progress and then reset after the saving method is done.
Thanks
|
|
|
|
|
Add a Statusbar to your MdiForm and show the progressbar on the statusbar when saving.
Tosch
|
|
|
|
|
Hi,
I have added statusbar to MdiForm also a progressBar..when I load a parent form from the MdiForm, on load event I also load the progressBar, it works, but when I save on a child form, I can't do it..
thanks advance
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
