Results 1 to 18 of 18

Thread: Troubles with sql

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Jan 2006
    Posts
    269

    Troubles with sql

    EDIT: LOOK AT POST #17 FOR NEW QUESTION

    Hi. Im having some troubles using SQL code in VB. Im connecting to my DB without bound objects and by ADO.

    i have the vars: cnVar and rsVar.

    VB Code:
    1. Set cnVar = New ADODB.Connection
    2.    cnVar.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & app.path & "\MyDB"
    3.    cnVar.Open
    4.    Set rsVar = New ADODB.Recordset
    5.    rsVar.Open "tblClients", cnVar, adOpenKeyset, adLockPessimistic, adCmdTable

    Using this my recorset will include the whole table, i want to just include those records with the "Name" column starting with "A".

    Thanks.
    Last edited by Kanbei; Apr 13th, 2006 at 06:42 PM.

  2. #2
    Frenzied Member
    Join Date
    Feb 2003
    Location
    Argentina
    Posts
    1,950

    Re: Troubles with sql

    Use a query for the recordset instead of the whole table. An example from a table here in Access (the user enters part or all of the last name):
    VB Code:
    1. SELECT Demographics.Pat AS [Employee Number], Demographics.NAME
    2. FROM Demographics
    3. WHERE (((Demographics.NAME) Like ([Please enter the last name] & "*")));
    Last edited by salvelinus; Apr 13th, 2006 at 11:47 AM.
    Tengo mas preguntas que contestas

  3. #3
    I'm about to be a PowerPoster! Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,335

    Re: Troubles with sql

    VB Code:
    1. Dim sSQL As String
    2. Set cnVar = New ADODB.Connection
    3.    cnVar.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & app.path & "\MyDB"
    4.    cnVar.Open
    5.    Set rsVar = New ADODB.Recordset
    6.    sSQL = "SELECT * FROM  tblClients WHERE [name] LIKE '%A%' "
    7.    rsVar.Open sSQL, cnVar, adOpenKeyset, adLockPessimistic, adCmdTable

  4. #4
    Frenzied Member
    Join Date
    Feb 2003
    Location
    Argentina
    Posts
    1,950

    Re: Troubles with sql

    Hack, won't your sql select a name with upper case A anywhere, not just at the start?
    Tengo mas preguntas que contestas

  5. #5
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,425

    Re: Troubles with sql

    Yes Hack's will bring back anything with an A in the name and yours needs to use the percent sign (%) not the asterik (*) as the wild card. When using SQL you use % for any number of things and the underscore (_) for for any one character.
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  6. #6
    I'm about to be a PowerPoster! Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,335

    Re: Troubles with sql

    Quote Originally Posted by salvelinus
    Hack, won't your sql select a name with upper case A anywhere, not just at the start?
    It will select all names beginning with an upper case A in the Name field. I don't understand what you mean by "not just at the start".

    By the way, Name is not a good thing to call a field. It is a reserved word in therefore, needs to go in brackets. You should always avoid name your db fields with reserved words.

  7. #7
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,425

    Re: Troubles with sql

    Hack you have a wildcard before the A in the criteria %A% so the A can be anywhere in the string not just at the start (A%).
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  8. #8
    I'm about to be a PowerPoster! Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,335

    Re: Troubles with sql

    Quote Originally Posted by GaryMazzone
    Hack you have a wildcard before the A in the criteria %A% so the A can be anywhere in the string not just at the start (A%).
    Oops. Good eye.

    Gary is right. You only need the last % sign.

  9. #9
    Frenzied Member
    Join Date
    Feb 2003
    Location
    Argentina
    Posts
    1,950

    Re: Troubles with sql

    Quote Originally Posted by GaryMazzone
    Yes Hack's will bring back anything with an A in the name and yours needs to use the percent sign (%) not the asterik (*) as the wild card. When using SQL you use % for any number of things and the underscore (_) for for any one character.
    No, in Access (at least up to 2K), which is where I stated the query came from, the wildcard character is an asterisk for any number, the question mark for single characters. In SQL Server (and maybe other dbs, don't know) it's the percent sign.
    Last edited by salvelinus; Apr 13th, 2006 at 12:52 PM.
    Tengo mas preguntas que contestas

  10. #10
    I'm about to be a PowerPoster! Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,335

    Re: Troubles with sql

    Quote Originally Posted by salvelinus
    No, in Access (at least up to 2K), which is where I stated the query came from, the wildcard character is an asterisk for any number, the question mark for single characters. In SQL Server (and maybe other dbs, don't know) it's the percent sign.
    Correct. But the posting member did not specify what database they are using, but did specify SQL code so SQL Server was assumed.

  11. #11
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,425

    Re: Troubles with sql

    Acess uses the % and _ for wild cards in Version 97, 2000,XP and 2003 at least. They use them if you write the SQL and send it to the database with ADO it will use the standard SQL wildcards.
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  12. #12

    Thread Starter
    Hyperactive Member
    Join Date
    Jan 2006
    Posts
    269

    Re: Troubles with sql

    Thanks. BTW my column is not named "Name"... its just an example.
    But dont i have to use adCmdText instead of adCmdTable? Or what's that for?

    Also im getting an error saying: "Syntax error at the clause FROM" in the like "rsVar.Open..."
    (i get the error using both adCmdText and adCmdTable)

    If no idea of what this error could be then ill post all my code...

  13. #13
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,500

    Re: Troubles with sql

    In this case you should be using adCmdText (with an SQL statement), instead of adCmdTable (table name only).

    To know why you have a syntax error, we need to see it.

  14. #14

    Thread Starter
    Hyperactive Member
    Join Date
    Jan 2006
    Posts
    269

    Re: Troubles with sql

    great thanks.
    Ok so my code... I created some functions since my prog works will DB a lot.

    Both function work 100% OK at least when opening Tables.
    VB Code:
    1. 'THIS FUNCTION WILL OPEN THE DB AND OPEN A RECORDSET
    2. Public Sub LoadDB(cnVar As ADODB.Connection, rsVar As ADODB.Recordset, DBPath As String, ObjectType As MSAdodcLib.CommandTypeEnum, ObjectName As String)
    3.    Set cnVar = New ADODB.Connection
    4.    cnVar.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBPath
    5.    cnVar.Open
    6.    Set rsVar = New ADODB.Recordset
    7.    rsVar.Open ObjectName, cnVar, adOpenKeyset, adLockPessimistic, ObjectType
    8. End Sub
    9.  
    10. 'THIS FUNCTION WILL CLOSE THE RECORSET AND OPEN A NEW ONE WITHOUT  KILLING AND CRETIN NEW VARIABLES
    11. Public Sub ChangeDBObject(cnVar As ADODB.Connection, rsVar As ADODB.Recordset, ObjectType As MSAdodcLib.CommandTypeEnum, ObjectName As String)
    12.       rsVar.Close
    13.       rsVar.Open ObjectName, cnVar, adOpenKeyset, adLockPessimistic, ObjectType
    14. End Sub
    15.  
    16. 'HERE I HAVE AN UNLOADdb FUNCTION... BUT DOESNT MIND NOW :)


    VB Code:
    1. Private Sub Form_Load()
    2.    LoadDB ADOcn, ADOrs, App.Path & "\TablMensajes.mdb", adCmdTable, "tblConsorcios"
    3.  
    4. 'I do some stuff that doesnt matter with that data
    5.  
    6.    ChangeDBObject ADOcn, ADOrs, adCmdTable, "tblEmpresas"
    7.  
    8. 'I do more stuff with this other data.... doesnt matter either (works all fine)
    9.  
    10. 'And now its time to replace the data using a SQL condition...
    11.  
    12. Dim SQLstr As String
    13. SQLstr = "SELECT tblConsorcios.PISOS, tblConsorcios.DEPTOS From tblConsorcios Where tblConsorcios.CONS = '" & Consorcio & "'"
    14. ' "Consorcio" is a variable declared before
    15.  
    16. ChangeDBObject ADOcn, ADOrs, adCmdText, SQLstr 'Error is raised here
    17.  
    18. MsgBox ADOrs.Fields(1) 'To try if the data is loaded correctly
    Last edited by Kanbei; Apr 13th, 2006 at 04:27 PM.

  15. #15
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,947

    Re: Troubles with sql

    Quote Originally Posted by Hack
    Correct. But the posting member did not specify what database they are using, but did specify SQL code so SQL Server was assumed.
    I dunno.... the connection string was a dead giveaway to me...

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  16. #16
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,500

    Re: Troubles with sql

    Is the field CONS a numeric data type?

    If not, you need to delimit the value within the SQL statement.

    For text fields, use the ' character, eg:
    VB Code:
    1. ... Where tblConsorcios.CONS = '" & Consorcio & "'"
    Note that as you only have one table in your SQL statement, you don't need to put it before the field names.

  17. #17

    Thread Starter
    Hyperactive Member
    Join Date
    Jan 2006
    Posts
    269

    Re: Troubles with sql

    Thanks, i added the " ' ", but still the same error: "Syntax error at the clause FROM"

    EDIT: For some weird reason i closed and re opened and now works!!

    THANKS!!!!

    BTW instead of putting
    "ADOrs.Fields(0)" is there a way of calling a column by its name like "ADOrs.Fields("FLOOR)" or something... just in case i edit my DB
    Last edited by Kanbei; Apr 13th, 2006 at 06:42 PM.

  18. #18
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,500

    Re: Troubles with sql

    You were very close, instead of: ADOrs.Fields(0)

    It would be: ADOrs.Fields("FLOOR")



    By the way, I see no need for your LoadDB function if the recordsets are using the same database - as you can use the same connection object (opened at the start of the program) for all your recordsets. In any case, LoadDB isn't really the right name - LoadRecordset (or similar) would be more apt.

    The ChangeDBObject routine is a bit superfluous too - it only saves one line of code (but adds a bit extra execution time, as all subs/functions do).

Posting Permissions

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



Click Here to Expand Forum to Full Width