MySQL and Query (Allias) RESOLVED

Printable View

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

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:
VB Code:
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:
VB Code:
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:
VB Code:
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 & " "
VB Code:
rs.Fields(0).Value = strValue & ""
it works fine, any ideas?? :confused:

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

May 29th, 2005, 10:05 PM
randem

Re: MySQL and Query (Allias)

What does the actual SQL statement look like?

Debug.Print strSQL
May 29th, 2005, 10:33 PM
kulitag

Re: MySQL and Query (Allias)

check the spelling of your fieldname, if it correspond to this " strValue & "" "
May 30th, 2005, 05:53 AM
Mark Gambo

Re: MySQL and Query (Allias)
Quote:

Originally Posted by randem

What does the actual SQL statement look like?

Debug.Print strSQL

Here is the actual SQL:
VB Code:
```
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.
May 30th, 2005, 12:03 PM
randem

Re: MySQL and Query (Allias)

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.
May 30th, 2005, 01:28 PM
Mark Gambo

Re: MySQL and Query (Allias)

Quote:

Originally Posted by 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.

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

Thanks!

Re: MySQL and Query (Allias)

Quote:
Originally Posted by Mark Gambo
Here is the actual SQL:
VB Code:
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:
VB Code:
strSQL = "SELECT " & [color="#FF0000"]strFieldName[/color] & " "
        strSQL = strSQL & "FROM " & strTableName & " "
 
        rs.AddNew
        strValue = Me.Text1.Text
        [color="#FF0000"]rs(strFieldName)[/color] = strValue & ""
        rs.Update