T-SQL conversion (1 Viewer)

[stanhook]

Hi,

I am pretty new to T-SQL but I have this on an old ASP page:

sqlDataDict = "SELECT tblDataDict.TableName, tblDataDict.FieldName, tblDataDict.Description"
sqlDataDict = sqlDataDict & " FROM tblDataDict"

And then down the page there are a bunch of these, each one requesting different data:

rsDataDict.Filter = "[TableName] like 'tblPD' AND [FieldName] like 'Site'"
response.write(rsDataDict.Fields("Description").Value)

rsDataDict.Filter = "[TableName] like 'tblPD' AND [FieldName] like 'PD'"
response.write(rsDataDict.Fields("Description").Value)

Is there a MySQL equivalent or can someone tell me what is is saying? I think it is looking for a table named tblPD with a column call Site. But I am not totally sure because I cannot find a MySQL equivalent.

Thanks for the help!
Stan

 

[GPGeorge]

I don't know what the MySQL equivalent of the SQL string would be, or if it would even be different.

The first two code lines are generating a SQL string, much like you would do in VBA:

"SELECT tblDataDict.TableName, tblDataDict.FieldName, tblDataDict.Description FROM tblDataDict"

And then that SQL String is assigned to the variable called sqlDataDict, again, much like you would do in VBA.

You've left out some lines of code; those would be where that SQL string is used to open a recordset call rsDataDict. The contents of that recordset will be all of the record from tblDataDict. Again, similar to creating a recordset in VBA.

You are right about the filters.

The last two sets of lines apply filters to the recordset (again, how that is opened is not part of the code pasted here).

And finally the two response.write lines output to the screen the contents of a field called "Description", based on the two different filters. Guessing from the names involved, the first filter selects the record in the tblDataDict recordset where the field called "TableName" has a value of "tblPD" and a field called "FieldName" has a value of "Site and outputs the corresponding "Description". The second filter does the same thing for the other field, "PD".

I can't imagine that the SQL required for a MySQL table would be all that different, given the simplicity of the SQL statement itself.

What errors do you encounter when you try to use a MySQL table with the same name as this one, i.e."tblDataDict", and the same field names in that table, with the same values for those fields?

It occurs to me that the problem might actually be elsewhere. It might be in the lines you didn't include in your question. It might be the lines where the recordset is opened against the MySQL table.

 

[sonic8]

I am pretty new to T-SQL but I have this on an old ASP page:
[....]
Is there a MySQL equivalent or can someone tell me what is is saying?

These sentences are somewhat contradicting each other: T-SQL is the SQL dialect of Microsoft SQL Server, while MySQL is an entirely different DBMS.

However, this is irrelevant for your question. The lines of code you are asking about are filtering a local object (most likely an ADODB.Recordset) containing the records from the database. The syntax rules for that object apply and are most likely agnostic of the backend database. - If it is an ADODB.Recordset, the documentation of Filter-Property explains how it works.

 

[TexanInParis]

LIKE filters with no wildcards are like asking for:

[TableName] = 'tblPD' AND [FieldName] = 'PD'

LIKE is a whole lot more inefficient than plain comparison, so the code should be using comparison.

And I see nothing that would upset T-SQL or MySQL or, for that matter, PostgreSQL.

 

[Pat Hartman]

This is not an Access question so I will move it to a more appropriate forum.

 

[stanhook]

@Pat Hartman Thanks for moving this. I am new here and didn't even realize there was a forum for these questions.

Thank you all for your responses and help!!

From the information you all provided I was able to come up with this for my WHERE which worked:

WHERE  tablename like 'tblPD' AND fieldname like 'FeEast'";

The naming wouldn't work for MySQL and I had to change TABLE_NAME and FIELD_NAME to the above to work. The answers here helped me figure that out.

Thanks again, this forum is always very helpful!

 

[Pat Hartman]

NEVER use Like when you really mean =.

Like is a string operation and is only used when you have a partial value and so is ALWAYS used with one or more wildcards.

Where FirstName Like "Pat*" --- The asterisk says to bring back any string that starts with Pat so it returns Patricia, Patrick, Patty, Patti, Patsky, etc

You have full values so you would use =.

It also makes no sense for the table name to be an argument in a query. UNLESS you are using a system table but you have taken the code out of context so it is not possible to tell. So, make sure the code actually works since it is illogical.