Web Development

Ask something
Question Locked

sql queries in vb 6

0 helpful
16 replies

by stogarepih

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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

16 replies

by tony hopkinson

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"

by robaaaaaa2

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

by stogarepih

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.

by richard.hogan

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

by joe.justice

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
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.

by jruby

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! */

by joe.justice

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.

by richard.hogan

You are absolutely correct.

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

by richard.hogan

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.

by tony hopkinson

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.

by richard.hogan

Try the following.

Dim strDisease As String
Dim strProvince As String
Dim dStartDate As Date
Dim dEndDate As Date

strDisease = "Cancer"
strProvince = "Tata"
dStartDate = #01/01/2004#
dEndDate = #12/31/2004#

sql1 = "SELECT SUM(Statistics.Passed_On) as sum1 "
sql1 = sql1 & "FROM Hospital, Statistics "
sql1 = sql1 & "WHERE "
sql1 = sql1 & "Hospital.hospital=Statistics.Hospital "
sql1 = sql1 & "and Hospital.Province='" & strProvince & "' "
sql1 = sql1 & "and disease='" & strDisease & "' "
sql1 = sql1 & "and date between #" & dStartDate & "# AND #" & dEndDate & "# "

When creating SQL statements like this make sure that after each line of your code you place a blank space before the closing speech mark. Other wise when the string is concatenated you end up with two words joined together with no spaces.

I hope that made sense :-)

Cheers

by support

what control in vb6 would you like to display your query?

by elmir.brdar

Hi there,

you have to have a database connection set up. here is an example where parameters are passed in from textboxes:

'Declaring the recordset
Dim rsLogOn As ADODB.Recordset

'Oracle Sql string
sSql = "SELECT user_id, " & _
" password_txt " & _
"FROM user_info " & _
"WHERE user_id = '" & txtUsername.Text & "'" & _
"AND password_txt = '" & txtPassword.Text & "'".

If dbDatabase.RunSelectQuery(sSql, True) Then
'Passing in the results in Recordset
Set rsLogOn = dbDatabase.GetRecordSet()

by jjbueyes

Try this code

I think the best solution is to have 4 box text, following the code that you initially send, you identify the
Province TextBox
Disease TextBox
Date1 TextBox
Date2 TextBox
so the query must be writed as
SQL1 = "SELECT SUM(Statistics.Passed_On) AS SUM1 "
SQL1 = SQL1 & "FROOM Hospital INNER JOIN Statistics ON Hospital.Hospital=Statistics.Hospital "
SQL1 = SQL1 & "WHERE Hospital.Province='" & Province_text.Text & "' "
SQL1 = SQL1 & "AND disease='" & Disease_text.Text & "' and date between #" & Date1_Text.Text & "# and #" & Date2_Text.Text & "#"

I hope this can help

Regards

This discussion is closed to new replies.