Questions

SQL Statement in VB6 Code

+
0 Votes
Locked

SQL Statement in VB6 Code

belcredit
I thank all of you for your input. Unfortunately no suggestion workes.

I have created a query using VB6 Query builder which works when run from the Query builder but when placed in my code it produce the following error.

"syntax erroe in FROM CLAUSE"

Here is the Select statement which works in the Query builder.

SELECT tblInfo.fldSuit, tblPayments.fldSuit, tblInfo.fldRCR, tblPayments.fldPDate, tblPayments.fldPaid, tblPayments.fldBalance, tblPayments.fldCashier, tblPayments.fldRecNum, tblPayments.fldVchNum
FROM tblInfo INNER JOIN tblPayments ON tblInfo.fldSuit = tblPayments.fldSuit
WHERE tblPayments.fldSuit = Gsuit;

I know that the problem is in declairing "GSuit" as a string in the select statement. The syntax to do this is where I am falling down.


?To fill the Grid with data that matches the Value in cboPayCombo

Dim strSuit As String
strSuit = cboPayCombo.Text
strQuery = "SELECT tblInfo.fldSuit, tblPayments.fldSuit, tblInfo.fldRCR, tblPayments.fldPDate, tblPayments.fldPaid, tblPayments.fldBalance, tblPayments.fldCashier, tblPayments.fldRecNum, tblPayments.fldVchNum
FROM tblInfo INNER JOIN tblPayments ON tblInfo.fldSuit = tblPayments.fldSuit
WHERE tblPayments.fldSuit = Gsuit"

adoGrid.RecordSource = strQuery adoGrid.Refresh
dtgPayment.ReBind


Please if someone can show me the correct syntax I would be very happy. Thank you.

You can contact me direct at belcredit@yahoo.com
  • +
    0 Votes
    john.a.wills

    I suspect it is actually the WHERE clause that is wrong.

    +
    0 Votes
    Wayne M.

    Strip your SQL statement down to something that works and add pieces back until it fails. This will allow you to diagnose the problem.

    +
    0 Votes
    saheer85

    hi,

    got a problem about creating reports in VB.
    i no the 'select * from tablename;'

    but i want to display details of only one record. what should be the syntax??

    please post a reply because i'm totally bloked

    +
    0 Votes
    rob

    Select * from tablename will return all records in the table.

    "Select * from Tablename WHERE myfield = mycriteria" will return only records which match.

    +
    0 Votes
    rob

    i.e.:
    From tblInfo INNER JOIN tblPayments ON tblInfo.fldSuit=tblPayments.fldSuit WHERE tblPayments.fldSuit = '" & strSuit & "'"

    +
    0 Votes
    msi77

    1. What data type of tblPayments.fldSuit is?
    2. Try strSuit = cboPayCombo.Value

    http://www.sql-ex.ru/

    +
    0 Votes
    hlanai

    Assuming the link between the two tables tblInfo and tblPayments is fldSuit, you have to use this field to join the two tables on the FROM clause as:

    From tblInfo inner join tblPayments on tblInfo.fldSuit = tblPayments.fldSuit

    It's only that I am not sure what error message you are getting but try this.

    Hope it helps.

    +
    0 Votes
    navanax

    When you get done building the query string, display it (or put a breakpoint in and copy it's contents) and check it. It's not only easier to spot problems with quotation marks (unbalanced, missing, mismatched) but you can also paste the string into your favorite query client and "play" with it there until you have the correct query, then go back to make the fixes in your code.

    +
    0 Votes
    simonmlarsen

    If not ignore my post.

    If it is you are approaching this so wrong it is not funny.

    For starters that style of join syntax is deprecated.

    Secondly take advantage of the performance sql has rather than messing about with ad hoc statements.

    Thirdly don't create strings, security nightmare.

    try this.

    From query analyser or SSMS (depending on version).

    create proc pr_sWhatever @strSuit varchar (8000) = Null
    as
    set nocount on

    SELECT i.fldSuit, i.fldRCR, p.fldPDate, p.fldPaid, p.fldBalance,
    p.fldCashier, p.fldRecNum, p.fldVchNum, p.fldSuit
    From tblInfo i join
    tblPayments p
    on i.fldSuit = p.fldSuit
    Where p.fldSuit = @strSuit
    go

    I have put a varchar 8000, check what you actually need there.

    then your string is

    strQuery = "pr_sWhatever " & strSuit

    except of course this is not ado style code.

    You probably want to create an ado command object and declare the parameter and set it.

    bam! performance and security all in one quick hit.

    +
    0 Votes
    kerry.millen

    The style of join syntax simonlarsen is referring to has in fact been deprecated in favor of join syntax. That being said, no DBMS I'm aware of has discontinued support for the old syntax and although the new join syntax promises increase efficiency and speed, this primarily applies to very large data sets. Additionally, the old syntax is more human readable.

    +
    0 Votes
    kerry.millen

    Dim strSuit As String
    strSuit = cboPayCombo.Text

    "SELECT
    tblInfo.fldSuit,
    tblInfo.fldRCR,
    tblPayments.fldPDate,
    tblPayments.fldPaid,
    tblPayments.fldBalance,
    tblPayments.fldCashier,
    tblPayments.fldRecNum,
    tblPayments.fldVchNum,
    tblPayments.fldSuit
    FROM
    tblInfo,
    tblPayments

    WHERE
    tblInfo.fldSuit=tblPayments.fldSuit
    AND
    tblPayments.fldSuit = '" & strSuit & "'"

    The last part of your WHERE clause is doing this:
    tblPayments.fldSuit = 'strSuit'"

    Since strSuit is not a literal string but a string variable, shouldn't you be doing this?

    tblPayments.fldSuit=strSuit"

  • +
    0 Votes
    john.a.wills

    I suspect it is actually the WHERE clause that is wrong.

    +
    0 Votes
    Wayne M.

    Strip your SQL statement down to something that works and add pieces back until it fails. This will allow you to diagnose the problem.

    +
    0 Votes
    saheer85

    hi,

    got a problem about creating reports in VB.
    i no the 'select * from tablename;'

    but i want to display details of only one record. what should be the syntax??

    please post a reply because i'm totally bloked

    +
    0 Votes
    rob

    Select * from tablename will return all records in the table.

    "Select * from Tablename WHERE myfield = mycriteria" will return only records which match.

    +
    0 Votes
    rob

    i.e.:
    From tblInfo INNER JOIN tblPayments ON tblInfo.fldSuit=tblPayments.fldSuit WHERE tblPayments.fldSuit = '" & strSuit & "'"

    +
    0 Votes
    msi77

    1. What data type of tblPayments.fldSuit is?
    2. Try strSuit = cboPayCombo.Value

    http://www.sql-ex.ru/

    +
    0 Votes
    hlanai

    Assuming the link between the two tables tblInfo and tblPayments is fldSuit, you have to use this field to join the two tables on the FROM clause as:

    From tblInfo inner join tblPayments on tblInfo.fldSuit = tblPayments.fldSuit

    It's only that I am not sure what error message you are getting but try this.

    Hope it helps.

    +
    0 Votes
    navanax

    When you get done building the query string, display it (or put a breakpoint in and copy it's contents) and check it. It's not only easier to spot problems with quotation marks (unbalanced, missing, mismatched) but you can also paste the string into your favorite query client and "play" with it there until you have the correct query, then go back to make the fixes in your code.

    +
    0 Votes
    simonmlarsen

    If not ignore my post.

    If it is you are approaching this so wrong it is not funny.

    For starters that style of join syntax is deprecated.

    Secondly take advantage of the performance sql has rather than messing about with ad hoc statements.

    Thirdly don't create strings, security nightmare.

    try this.

    From query analyser or SSMS (depending on version).

    create proc pr_sWhatever @strSuit varchar (8000) = Null
    as
    set nocount on

    SELECT i.fldSuit, i.fldRCR, p.fldPDate, p.fldPaid, p.fldBalance,
    p.fldCashier, p.fldRecNum, p.fldVchNum, p.fldSuit
    From tblInfo i join
    tblPayments p
    on i.fldSuit = p.fldSuit
    Where p.fldSuit = @strSuit
    go

    I have put a varchar 8000, check what you actually need there.

    then your string is

    strQuery = "pr_sWhatever " & strSuit

    except of course this is not ado style code.

    You probably want to create an ado command object and declare the parameter and set it.

    bam! performance and security all in one quick hit.

    +
    0 Votes
    kerry.millen

    The style of join syntax simonlarsen is referring to has in fact been deprecated in favor of join syntax. That being said, no DBMS I'm aware of has discontinued support for the old syntax and although the new join syntax promises increase efficiency and speed, this primarily applies to very large data sets. Additionally, the old syntax is more human readable.

    +
    0 Votes
    kerry.millen

    Dim strSuit As String
    strSuit = cboPayCombo.Text

    "SELECT
    tblInfo.fldSuit,
    tblInfo.fldRCR,
    tblPayments.fldPDate,
    tblPayments.fldPaid,
    tblPayments.fldBalance,
    tblPayments.fldCashier,
    tblPayments.fldRecNum,
    tblPayments.fldVchNum,
    tblPayments.fldSuit
    FROM
    tblInfo,
    tblPayments

    WHERE
    tblInfo.fldSuit=tblPayments.fldSuit
    AND
    tblPayments.fldSuit = '" & strSuit & "'"

    The last part of your WHERE clause is doing this:
    tblPayments.fldSuit = 'strSuit'"

    Since strSuit is not a literal string but a string variable, shouldn't you be doing this?

    tblPayments.fldSuit=strSuit"