Printable View
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
Try this (you missed out & before the last quotes]
Code:strSQL = "SELECT dload_item" & " FROM dload_MAX" & " WHERE dload_MAX.dload_item = '" & DataCombo.Text & "'"
strSQL="SELECT dLoad_Max.dload_item From dload_Max " & "WHERE ((dload_Max.dload_item =) " & chr(34) & DataCombo.Text & chr(34) & ");"Quote:
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
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
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
What is wrong with this?
Much easier to read.Code:strSQL = "SELECT uzdload.dload_item, uzdload.dload_descext " & _
"FROM uzdload " & _
"WHERE uzdload.dload_item = '" & DataCombo.Text & "'"
Never heard that you must always use a semi-colon to terminate Access SQL.
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.Quote:
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
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
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:Quote:
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
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
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.
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.Quote:
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
Hunter
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.
Don't forget to add an ADO reference to your project.Code: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
Hope this helps. :)