'Constant expression is required' error

[ChefSausage]

I'm retrieving records from an Access database based criteria being passed from a search form, and in vs.net I'm getting the purple lines under the three form fields in my dynamic SQL string.

Const strSQL As String = "SELECT * FROM Client WHERE ClientName LIKE '%" & frmName.Text & "%' " & _
"AND ClientState LIKE '%" & frmState.Text & "%' AND ClientZip LIKE '%" & frmZip.Text & "%' " & _
"ORDER BY ClientName"

All other syntax is fine, but for frmName, frmState and frmZip, it's giving me a 'Constant expression is required' error. Why? I have it defined above in my .vb file with the 'Protected WithEvents....As System.Web.UI.WebControls.TextBox" and I have a previous template that uses a dynamic SQL string to update a table, yet doesn't give me this constant experssion issue. Ideas?

 

[BG12424]

You CANNOT make this a constant because a constant means that you cannot set dynamic value to it. Make is just a variable.


regards,
Brian

http://www.gainesconsulting.com

 

[chiph]

Besides the fact that concatenating SQL together is an entirely Bad Idea, try reformatting your SQL to make it more readable, plus use a StringBuilder:

Import System.Text

Dim sSQL As StringBuilder
sSQL = New StringBuilder()
sSQL.Append(" SELECT *")
sSQL.Append(" FROM Client")
sSQL.Append(" WHERE ClientName LIKE '%")
sSQL.Append(frmName.Text.ToString())
sSQL.Append("%' AND ClientState LIKE '%")
sSQL.Append("frmZip.Text.ToString())
sSQL.Append("%'")
sSQL.Append(" ORDER BY ClientName")

MyOleDbCommand.CommandText = sSQL.ToString()

The stringbuilder is more memory efficient than using a string, and spacing the formatting out like that makes it easier to maintain the code a year or two from now.

More about why concatenating SQL is bad:
1) It's slow, because the database SQL interpreter is unable to cache previous queries -- it's got to be parsed every time
2) It's insecure -- it opens you up to the SQL Injection attack (what happens if I enter ';DROP TABLE Client; as my ClientName in the user interface?)
3) Translates single-quotes and other odd characters for you

Try doing this instead:

Import System.Text

Dim sSQL As StringBuilder
sSQL = New StringBuilder()
sSQL.Append(" SELECT *")
sSQL.Append(" FROM Client")
sSQL.Append(" WHERE ClientName LIKE ?")
sSQL.Append("   AND ClientState LIKE ?")
sSQL.Append(" ORDER BY ClientName")

MyOleDbCommand.CommandText = sSQL.ToString()
Dim MyClientNameParm As New OleDbParameter("ClientName", OldEbType.VarChar, 30)
MyClientNameParm.Value = frmName.Text.ToString()
MyOleDbCommand.Parameters.Add(MyClientNameParm)
Dim MyClientNameParm As New OleDbParameter("ClientState", OldEbType.Char, 2)
MyClientZipParm.Value = frmZip.Text.ToString()
MyOleDbCommand.Parameters.Add(MyClientZipParm)

One other question - I noticed that your textboxes start with "frm" -- this naming convention usually indicates the variable is of type Form, not a textbox. Did you mean to type txtClientName instead?

Chip H.


If you want to get the best response to a question, please check out FAQ222-2244 first

 

[ChefSausage]

chiph, thank you for the syntax help. I did not know about the .Append feature, and while I could've done it as a paramterized query, I was having trouble with that as well.