Questions

sql queries in vb 6

Tags:
+
0 Votes
Locked

sql queries in vb 6

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
  • +
    0 Votes
    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"

    +
    0 Votes
    rob

    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

    +
    0 Votes
    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.

    +
    0 Votes
    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

    +
    0 Votes
    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.

    +
    0 Votes
    richard.hogan

    You are absolutely correct.

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

    +
    0 Votes
    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 <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.

    +
    0 Votes
    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.

    +
    0 Votes
    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.

    +
    0 Votes
    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! */

    +
    0 Votes
    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

    +
    0 Votes

    ???

    support

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

    +
    0 Votes
    stogarepih

    i would want to display this on a datagrid

    +
    0 Votes
    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()

    +
    0 Votes
    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

  • +
    0 Votes
    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"

    +
    0 Votes
    rob

    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

    +
    0 Votes
    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.

    +
    0 Votes
    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

    +
    0 Votes
    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.

    +
    0 Votes
    richard.hogan

    You are absolutely correct.

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

    +
    0 Votes
    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 <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.

    +
    0 Votes
    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.

    +
    0 Votes
    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.

    +
    0 Votes
    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! */

    +
    0 Votes
    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

    +
    0 Votes

    ???

    support

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

    +
    0 Votes
    stogarepih

    i would want to display this on a datagrid

    +
    0 Votes
    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()

    +
    0 Votes
    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