What is wrong with this?

[Stockton.S]

I am new to SQL and this statement is returning a "EXPECTED END OF STATEMENT" error, what is wrong with it!


strSQL = "SELECT dload_item" & " FROM dload_MAX" & " WHERE dload_MAX.dload_item = '" & DataCombo.Text "'"

I think that it is this bit ^^^

Cheers

Simon

[Stevie]

Try this (you missed out & before the last quotes]

strSQL = "SELECT dload_item" & " FROM dload_MAX" & " WHERE dload_MAX.dload_item = '" & DataCombo.Text & "'"

[HunterMcCray]

Originally posted by Stockton.S

I am new to SQL and this statement is returning a "EXPECTED END OF STATEMENT" error, what is wrong with it!


strSQL = "SELECT dload_item" & " FROM dload_MAX" & " WHERE dload_MAX.dload_item = '" & DataCombo.Text "'"

I think that it is this bit ^^^

Cheers

Simon

strSQL="SELECT dLoad_Max.dload_item From dload_Max " & "WHERE ((dload_Max.dload_item =) " & chr(34) & DataCombo.Text & chr(34) & ");"

All SQL statements in ACCESS should end with ";" It is much better to use "CHR(34)" than a single quote in the event a character in the string contains a single quote. You should always include the table name in your select statements, many times it will work without it, but just as many it will not. An easy way to write SQL statements is to use the query builder in ACCESS. Select the tables and fields, conditions and formats as if you were going to save the query in access. Test the query until you get what you want and then go to "View" and select "SQL". You can copy the SQL statement and paste it into your app replacing hard coded "where" values with program variables. Then simply close the Access temp file without saving it. Saves a lot of headaches.

Hunter

[Stockton.S]

However I belive that the ASCII code for a single quote is 39 and if you are going to use ascii codes then you may as well represent ; with ascii code 59 making the line of code as follows

strSQL = "SELECT uzdload.dload_item, uzdload.dload_descext" & " FROM uzdload" & " WHERE (uzdload.dload_item = " & Chr(39) & DataCombo.Text & Chr(39) & Chr(59)

What is the need for the additional brackets?

Simon

[Stevie]

What is wrong with this?

strSQL = "SELECT uzdload.dload_item, uzdload.dload_descext " & _
         "FROM uzdload " & _
         "WHERE uzdload.dload_item = '" & DataCombo.Text & "'"

Much easier to read.

Never heard that you must always use a semi-colon to terminate Access SQL.

[HunterMcCray]

Originally posted by Stockton.S

However I belive that the ASCII code for a single quote is 39 and if you are going to use ascii codes then you may as well represent ; with ascii code 59 making the line of code as follows

strSQL = "SELECT uzdload.dload_item, uzdload.dload_descext" & " FROM uzdload" & " WHERE (uzdload.dload_item = " & Chr(39) & DataCombo.Text & Chr(39) & Chr(59)

What is the need for the additional brackets?

Simon

Yes, the ASCII Code for a single quote is 39; however, the ASCII code for a double quote is 34. If any of your parameter text strings contain a single quote then your SQL statement will produce an error that will crash your program if you do not trap for it. Either quote is acceptable for SQL in ACCESS, the double quote taking precidence over the single.
The "Extra" Brackets are a programming convention I use because I often write code that builds SQL statements with multiple conditional WHERE and ORDER BY Statements. It is easier for me to write if I begin with the largest number of opening parentheses that might occur....Example:

Print txtSQL ' Would Print
.....WHERE (((Plu.MFG = 19) OR (Plu.Mfg=198)) AND (([Plu].[DESC] Like "*Light*") OR ([Plu].[DESC] Like "*Ultra*")) AND (Plu.Cat=7)).....

Produces a very different recordset than:
.....WHERE Plu.MFG = 19 OR Plu.MFG=198 AND ([Plu].[DESC] Like "*Light*" OR [Plu].[DESC] Like "*Ultra*" AND Plu.Cat=7 ....

The actual code that produces the above statement can handle a variable number of WHERE conditions for each of Five Fields And the sort order is also Conditional. In developing and debugging this rather complex SQL generator I found that containing each potential group of conditions within () made my life a great deal easier. Each segment of the WHERE clause is built in its own subroutine that can be used in other parts of the App. and then assembled with the other components of the SQL statement in a local routine that tests the various validity of each of the potential parameters. There is no downside to building in extra () as long as there is a closing ) for each opening (. If you are testing only for one or two fixed number of parameters then there are, as you pointed out, simpler solutions; the particular case above being one of them. I am sorry if my conventions seem odd or wasteful.


Hunter

[Stockton.S]

You seam to be in the know about all of this so maybe you could look at what follows for me:


I have an ADODC control on the form and I want to pass the strSQL string that you helped me write to sequal. When I was using the Data Enviroment I used the following code:

Set MAXData = DataEnviroment.Connection.Execute(strSQL, , adCmdText)

Which worked but how do I pass the same command to the ADODC control in order to recieve the same result?

Thanks for your help

Simon

[HunterMcCray]

Originally posted by Stockton.S
You seam to be in the know about all of this so maybe you could look at what follows for me:


I have an ADODC control on the form and I want to pass the strSQL string that you helped me write to sequal. When I was using the Data Enviroment I used the following code:

Set MAXData = DataEnviroment.Connection.Execute(strSQL, , adCmdText)

Which worked but how do I pass the same command to the ADODC control in order to recieve the same result?

Thanks for your help

Simon

Thanks for the cudos, not sure that I deserve them. I just have spent a lot of time writing SQL statements and statement generators for one app that I use in house in my business. I have only just loaded VB6 recently, and upgraded to ACCESS 2000 at the same time. I have spent most of my time with VB6 trying to fix all the stuff that won't work with the "UPGRADES". I have not even attempted the new (to me) Data Environment, but I would guess that the controls are similar. The easiest way should be:

ADOData1.RecordSource = txtSQL
ADOData1.Refresh

But I do not know this. I would suggest that if this does not work that you ask someone who has used the data environment. I am stuck in the stone ages and still lament the loss of statements like "Lprint Using FormatString$, data". Most of the code that I write is used in my business, and I rarely find it within myself to make major changes unless there is a problem.

Hunter

[monte96]

My suggestion would be to stop wasting time with the data control and learn ADO. It is easy to use, scalable, and connects to darn near any datasource.

Real world programs for the most part do not use data controls or databound controls on the forms.

[Stockton.S]

Simon

[HunterMcCray]

Originally posted by Stockton.S

I am new to SQL and this statement is returning a "EXPECTED END OF STATEMENT" error, what is wrong with it!


strSQL = "SELECT dload_item" & " FROM dload_MAX" & " WHERE dload_MAX.dload_item = '" & DataCombo.Text "'"

I think that it is this bit ^^^

Cheers

Simon

Sorry, after being so emphatic about the use of double quotes, I went and read up a bit on ADO (I am still using DAO). One of the important changes from DAO to ADO is that ADO requires the use of single quotes. I am terribly sorry if this confused you, in DAO I ran into many problems with queries whose search strings included single quotes (O'Neil for example). I use a fair number of text box objects to provide user input to narrow database filled lists. Because I and my employees use the application everyday we have learned to sort for certain customers and inventory items with as few key strokes as possible, and the single quote key is very commonly part of the search string. Example: In posting an invoice to a customer's account as the user types the customer's name into a text box the list of customers shrinks to those whose names are returned by WHERE CustList.CustName Like "*SearchText*". We have three customers who have single quotes in their names and "*o'* is a unique sort for Mr. O'Neal. In searching for rods we often use 8' to list all of the 8ft rods. The loss of this ability and the irritation to my employees would preclude any efforts on my part to move to ADO, but again I am sorry for the misdirection.

Hunter

[Stevie]

Here is a simple example which will open a connection via a specified Data Source Name, and open a read only recordset to retrieve some data.

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Dim strDSN As String
Dim strSQL As String
  
  strDSN = "Some DSN"

  Set cn = New ADODB.Connection
  cn.Open strDSN

  strSQL = "SELECT * " & _
           "FROM TableName " & _
           "WHERE SomeField = 'SomeCriteria'"

  Set rs = New ADODB.Recordset
  rs.CursorType = adOpenForwardOnly
  rs.Open strSQL, cn, , , adCmdText

  If Not rs.EOF Then

    Text1.Text = rs!SomeField
    Text2.Text = rs!AnotherField

  End If
  
  rs.Close
  Set rs = Nothing
  
  cn.Close
  Set cn = Nothing

Don't forget to add an ADO reference to your project.

Hope this helps.