id="info"

Question

Locked

sql queries in vb 6

By stogarepih ·
can someone assist .i am trying to write a query which get input from a text box and query two related tables in access. my code is as below but i get an error message

sql1 = "SELECT sum (Statistics.Passed_On) as sum1 From"
sq1 = "sql1 & Hospital, Statistics where"
sql1 = "sql1 & Hospital.hospital=Statistics.Hospital & and & Hospital.Province='Tata' & and & disease='Cancer' & and & date between #1/1/2004# and #12/31/2004#"

i want the user to input the parameters from the textboxes

This conversation is currently closed to new comments.

16 total posts (Page 1 of 2)   01 | 02   Next
| Thread display: Collapse - | Expand +

All Answers

Collapse -

Same mistake as the last question you posted

by Tony Hopkinson In reply to sql queries in vb 6

sql1 = sql1 & "...."

& is a VB operator that concatenates two strings so it goes outside the double quotes

Str1 = "You can use &"
Str1 = Str1 & " to join strings together"

means Str1 will contain
"You can use & to join strings together"

Collapse -

read up on string handling

by rob In reply to sql queries in vb 6

as someone else points out, you are a bit confused over handling strings.

Your code should look something like:

sql1 = "SELECT sum (Statistics.Passed_On) as sum1 From "
sq1 = sql1 & "Hospital, Statistics where "
sql1 = sql1 & "Hospital.hospital=Statistics.Hospital and Hospital.Province='"
sql1 = sql1 & textbox1.text & "' and disease='"
sql1=sql1 & textbox2.text & "' and date between #"
sql1 = sql1 & textdate1.text & "# and #"
sql1 = sql1 & textdate2.text & "#"

Also think about using a DateTimePicker control for your date inputs - then you can control the date format better

hth

Collapse -

sql and vb6

by stogarepih In reply to read up on string handlin ...

thank you but suppose i want to say
select sum(statistics.passed_on) as sum 1 ,hospitals from.....i get an error message saying
"You tied to execute a query that does not include the specified expression "stn_name' as part of aggregate function.

Collapse -

Group By Clause

by richard.hogan In reply to sql and vb6

Hi

If you are using aggregate functions in a SQL statement you need to use a group by clause.

e.g.
SELECT SUM(statistics.passed_on) as sum 1
FROM [SomeTable]
GROUP BY statistics.passed_on

Collapse -

Not quite...

by joe.justice In reply to Group By Clause

You have given him an incorrect example. The "SUM(statistics.passed_on)" is already an aggregation. It is not what is throwing his error and it is not necessary to do a "group by" clause for it. It is the other columns in his select clause which are not being aggregating that is causing him problems. They are the ones which need to be aggregated.

-joe j.

Collapse -

Not Quite...

by richard.hogan In reply to Not quite...

You are absolutely correct.

Goes to show that you should always double check what you have written:-)

Collapse -

Take some time to study SQL

by joe.justice In reply to sql and vb6

Hey, Stogarepih.

It seems you may benefit from learning more about SQL before you continue. It will be better than posting to a forum to get others to write your code for you.

But, in an effort to help you in your immediate situation...

Your SQL above has a couple of problems. 1) you cannot have a separate label for your summatino item. Instead of,

select sum(statistics.passed_on) as sum 1

you should have,

select sum(statistics.passed_on) as sum_1

SQL will read the "sum 1" with the space as separate items and throw an error.

When you do aggregate processes in a SQL statement, you need to make sure all your returned items are in some form of aggregation. If not in the "select" part, then you need to add them in a "group by" clause at the end, or some other type of aggregation method.

To summarize, a correct example of your statement above may be something like,

select sum(statistics.passed_on) as sum_1, hospitals
from <whatever_table>
group by hospitals

Since you are aggregating the "statistics.passed_on" item, you need to aggregate every other column you are selecting in the statement.

-joe j.

Collapse -

Separate Labels

by richard.hogan In reply to Take some time to study S ...

By the way you can have 'separate labels' you just have to enclose them in square brackets e.g. [sum 1].

I wouldn't necessarily recommend formatting a result set like this, but it is something that can be done if required.

Collapse -

Or Double quotes

by Tony Hopkinson In reply to Separate Labels

but then you have to escape them when building the string up in VB, which is of course why MS SQL takes []

Using keywords or column names with illegal characters should be avoided unless absolutely necessary which it rarely is.

Collapse -

Debugging tip

by jruby In reply to sql queries in vb 6

I keep a non-visible textbox in my applications - each SQL query that I build gets dumped into that textbox. During debugging, I'll make the textbox visible and observe the queries as they are built and executed. Seeing exactly what you're submitting to the SQL server can be a real boon for the development process.


Just be careful when using this - a textbox, even a non-visible one, with a few megabytes of data can have serious performance issues!


Jim


/* If you think the problem's bad now, wait until I solve it! */

Back to Web Development Forum
16 total posts (Page 1 of 2)   01 | 02   Next

Software Forums