MySQL and Query (Allias) ***RESOLVED***

[Mark Gambo]

Can someone tell me why I am getting an error for the the following sql statement when I execute it against a MySQL Database. This sql statement works great with MSSQL and Access:

Dim strFieldName as String
Dim strTableName as String
 
strFieldName = "MyFieldName"
strTableName = "MyTableName"
 
strSQL = "SELECT " & strFieldName & " as FieldName " '<==Aliasing MyFieldName as fieldname
strSQL = strSQL & "FROM " & strTableName & " "

then I attempt to Add a new record into the table:

rs.AddNew
        strValue = Me.Text1.Text
        rs!FieldName = strValue & ""
        rs.Update  '<====This is the line I get the error on

I get the following error message: Error -214721900 (Unknown column 'fieldname' in 'field list)

Am I to assume that MySQL doesn't allow you to allias fieldnames?

If I change the fieldname to the following:

Dim strFieldName as String
Dim strTableName as String
 
strFieldName = "MyFieldName"
strTableName = "MyTableName"
 
strSQL = "SELECT " & strFieldName & " " 'as FieldName " <==Aliasing MyFieldName as fieldname
strSQL = strSQL & "FROM " & strTableName & " "

rs.Fields(0).Value = strValue & ""

it works fine, any ideas??

P.S. I am using mysql-4.1.12-win32 version

[randem]

What does the actual SQL statement look like?

Debug.Print strSQL

[kulitag]

check the spelling of your fieldname, if it correspond to this " strValue & "" "

[Mark Gambo]

What does the actual SQL statement look like?

Debug.Print strSQL

Here is the actual SQL:

strSQL = "SELECT MyFieldName as FieldName FROM MyTableName"

I don't think it is a problem with the SQL because like I said in my first post this same sql statement when executed against a MS SQL and Access Databases it works fine.

[randem]

Mark Gambo,

That is an incorrect assumption. Reserved words in one database are not necessarily reserved in another. Try making it look like this:

strSQL = "SELECT MyFieldName as [FieldName] FROM MyTableName"

or just change the name of the alias.

[Mark Gambo]

Mark Gambo,

That is an incorrect assumption. Reserved words in one database are not necessarily reserved in another. Try making it look like this:

strSQL = "SELECT MyFieldName as [FieldName] FROM MyTableName"

or just change the name of the alias.

That is a good idea, I will give it a try.

Thanks!

[Mark Gambo]

Here is the actual SQL:

strSQL = "SELECT MyFieldName as FieldName FROM MyTableName"

I don't think it is a problem with the SQL because like I said in my first post this same sql statement when executed against a MS SQL and Access Databases it works fine.

Well I figure it out:

strSQL = "SELECT " & [color="#FF0000"]strFieldName[/color] & " "
        strSQL = strSQL & "FROM " & strTableName & " "
 
        rs.AddNew
        strValue = Me.Text1.Text
        [color="#FF0000"]rs(strFieldName)[/color] = strValue & ""
        rs.Update