Results 1 to 12 of 12
  1. #1
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL Builder (VB6)

    Hello

    Using adodc1 & datagrid1 I am trying to populate the datagrid with an SQL.
    I struggle with SQL statements from time to time but form design and basic code I am comfortable with.

    Does anyone know of a free SQL Builder where I can use the output for my projects ?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL Builder (VB6)

    Hi Dave,

    Where are the data stored? If you use an Access database or if you can link to your data in an Access database, you could design a query interactively in Access, then switch to SQL view and copy the SQL statement generated by Access.

  3. #3
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Builder (VB6)

    Hello Hans

    The datasource is ODBC not Access.
    I've only dabbled with ADO - SQL, never quite got the hang of it.

    In case you can assist, I need several fields from the table EST, something like: ( Using Pidgeon SQL)
    adodc1.recordsource = Select EST_NO, SUP_No, REG, STAT, STAT_DTE from tblEST where STAT = "C" sort STAT_DTE asc ??

    What would the best method to populate the datagrid?

    Regards

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL Builder (VB6)

    You can link to tables via ODBC in Access.

    In your example, the code would be
    <code>
    Dim strSQL As String
    strSQL = "SELECT EST_NO, SUP_No, REG, STAT, STAT_DTE FROM tblEST " & _
    "WHERE STAT = 'C' ORDER BY STAT_DTE"
    adodc1.RecordSource = strSQL
    </code>
    Note the use of single quotes in the where-clause to prevent confusion with the double quotes around the entire SQL string.

  5. #5
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Builder (VB6)

    The code I'm using is now:

    Private Sub Form_Load()
    Adodc2.ConnectionString = "DSN=MM;DBQ=s:shopdata;SERVER=NotTheServer"
    Dim strSQL As String
    strSQL = "SELECT EST_NO, SUP_No, REG, STA, C_DTE, INS_NME, ASS, EST_NME FROM EST " & _
    "WHERE STA = 'C' ORDER BY C_DTE"
    Adodc2.RecordSource = strSQL
    Adodc2.Refresh
    End Sub

    A couple of things.

    1/ Before the form loads i get [Flex/odbc]Expected Lexical Element Not Found<Keyword>
    Which I think is looking for a column name that doesn't exist.

    2/ The first record in the first column is Null where it shouldn't be, is this because it is trying to allow edits to that record?

    Thanks

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL Builder (VB6)

    I'm sorry, I can't help you with these questions, I have no idea...

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    [quote name='D Willett' post='760720' date='Feb 23 2009, 03:57 PM']The code I'm using is now:

    Private Sub Form_Load()
    Adodc2.ConnectionString = "DSN=MM;DBQ=s:shopdata;SERVER=NotTheServer"
    Dim strSQL As String
    strSQL = "SELECT EST_NO, SUP_No, REG, STA, C_DTE, INS_NME, ASS, EST_NME FROM EST " & _
    "WHERE STA = 'C' ORDER BY C_DTE"
    Adodc2.RecordSource = strSQL
    Adodc2.Refresh
    End Sub

    A couple of things.

    1/ Before the form loads i get [Flex/odbc]Expected Lexical Element Not Found<Keyword>
    Which I think is looking for a column name that doesn't exist.

    2/ The first record in the first column is Null where it shouldn't be, is this because it is trying to allow edits to that record?

    Thanks[/quote]

    Since you are trying to use ADO, your SQL string needs to match the database engine specs. What database are you using - SQL Server, Oracle, mySQL? What version are you using? SQL Server 2005 and 2008 have a pretty decent graphical query design tool (not as good as Access but still good) that will generate the SQL String for you. I don't use the other products, but presume they also have something.
    Wendell

  8. #8
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Hans

    Is it possible to advise on an updated version of the SQL ? I need the last two characters of the field EST_NME .

    strSQL = "SELECT EST_NO, SUP_No, REG, STA, C_DTE, INS_NME, ASS, EST_NME, CAN_DES FROM EST " & _
    "WHERE STA = 'C' ORDER BY right((EST_NME)-2)"

    Regards.

    Oh, and well done to all in the new design of the site.. Very nice ...

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='D Willett' post='762149' date='27-Feb-09 12:44']Oh, and well done to all in the new design of the site.. Very nice ...[/quote]
    Thanks!

    Try using Right(EST_NME, 2)

  10. #10
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Hans, I overlooked I need to Trim the field of spaces too.


    Trim(Right(EST_NME,2))

    Is this correct?

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='D Willett' post='762165' date='27-Feb-09 13:48'][/quote]
    It depends on the contents of EST_NME and on what you want.

    Let's look at an example: say that EST_NME contains the string "ABCD " (note the space after the D).
    Right(EST_NME,2) would evaluate to "D " and Trim(Right(EST_NME,2)) to "D".
    On the other hand, Trim(EST_NME) would evaluate to "ABCD" and Right(Trim(EST_NME),2) to "CD".

    Another example: EST_NME = "ABC D".
    Trim(Right(EST_NME,2)) would be "D" again, and Right(Trim(EST_NME),2) would be " D".

  12. #12
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you Hans,
    A clear and precise explanation I can save for future reference.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •