Description:
Hi all ,
I have problem in MYSQL database , it always give me "unknown column "XXX" in 'field list' error. Eventhough the column are exists in my database.
This problem occurs when i deploy my application to the Window Pro PC which using MyODBC to connect to MYSQL database which in the Window 2000 PC.
Execute the VB code in W2K , it did not have the error message but it occurs when executed in the Window Professional PC.
Below is the VB code
Public Sub InitMYSQLDatabase()
Dim l_Database As String
Dim l_Server As String
Dim l_User As String
Dim l_Password As String
Set fileProperties = New clsNLBFileInit
l_Database = fileProperties.readSetting("MYSQL", "DATABASE")
l_Server = fileProperties.readSetting("MYSQL", "SERVER")
l_User = fileProperties.readSetting("MYSQL", "USER")
l_Password = fileProperties.readSetting("MYSQL", "PASSWORD")
On Error GoTo Handle
Set objConnection = New ADODB.Connection
With objConnection
.CursorLocation = adUseClient
.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" & _
"SERVER=" & l_Server & ";" & _
"DATABASE=" & l_Database & ";" & _
"USER=" & l_User & ";" & _
"PASSWORD=" & l_Password & ";"
.Open
End With
If objConnection.State = adStateOpen Then
DoEvents
'MsgBox "Connection was successfully opened...", vbInformation + vbOKOnly + vbSystemModal, "Successful"
End If
Handle:
If Err.Number = -2147467259 Then
Err.Raise vbObjectError, "InitMYSQLDatabase()", _
"No connection to the Database , the program will be close"
Err.Clear
End If
If Err.Number <> 0 Then
Err.Raise DATABASE_ERR
Err.Clear
End If
End Sub
Public Sub InsertCCLGateTable(m_branch_code As String, m_gate_id As String, m_alarm_count As Integer, _
m_visitor_count As Integer, is_newDay As Boolean)
Dim SQL As String
Dim cmdADODB As ADODB.Command
Dim rstByADODB As ADODB.Recordset
Dim branchCodeprm As ADODB.Parameter
Dim gateIdprm As ADODB.Parameter
Dim alarmCountprm As ADODB.Parameter
Dim visitorCountprm As ADODB.Parameter
Dim dateprm As ADODB.Parameter
On Error GoTo Handle
objConnection.BeginTrans
If is_newDay Then
SQL = "INSERT INTO gate_table(branch_code,gate_id,alarm_count,visitor_count,date) VALUES(?,?,?,?,?)"
Else
SQL = "UPDATE gate_table set branch_code = ? , gate_id = ?, alarm_count = ? , visitor_count = ? WHERE DATE = ?"
End If
Set cmdADODB = New ADODB.Command
cmdADODB.CommandText = SQL
cmdADODB.CommandType = adCmdText
'Call SelectMinRecord
'Notes:
'add "'" Single quote to solve MYSQL bug : unknown column errors
'for e.g : "'" & m_branch_code & "'"
'Cause : maybe OS platform , in W2k no need single quotes but WINpro need to
Set branchCodeprm = cmdADODB.CreateParameter("branch_code", adBSTR, adParamInput, 10, m_branch_code)
Set gateIdprm = cmdADODB.CreateParameter("gate_id", adBSTR, adParamInput, 10,m_gate_id)
Set alarmCountprm = cmdADODB.CreateParameter("alarm_count", adInteger, adParamInput, 1, m_alarm_count)
Set visitorCountprm = cmdADODB.CreateParameter("visitor_count", adInteger, adParamInput, 1, m_visitor_count)
Set dateprm = cmdADODB.CreateParameter("date", adDBDate, adParamInput, 1, Now)
cmdADODB.Parameters.Append branchCodeprm
cmdADODB.Parameters.Append gateIdprm
cmdADODB.Parameters.Append alarmCountprm
cmdADODB.Parameters.Append visitorCountprm
cmdADODB.Parameters.Append dateprm
Set cmdADODB.ActiveConnection = objConnection
Set rstByADODB = cmdADODB.Execute
If Not rstByADODB Is Nothing Then
If rstByADODB.State = adStateOpen Then
rstByADODB.Close
End If
Set rstByADODB = Nothing
End If
objConnection.CommitTrans
Handle:
If Err.Number = 3704 Then
objConnection.RollbackTrans
Err.Raise DATABASE_ERR
Err.Clear
End If
If Err.Number <> 0 Then
objConnection.RollbackTrans
Err.Raise DATABASE_ERR
Err.Clear
End If
'cleaning up
Set cmdADODB = Nothing
Set rstByADODB = Nothing
Cleaning branchCodeprm, gateIdprm, alarmCountprm, visitorCountprm, dateprm
End Sub
How to repeat:
This my table structure :
| gate_table | CREATE TABLE `gate_table` (
`rid` bigint(20) NOT NULL auto_increment,
`branch_code` varchar(10) default NULL,
`gate_id` varchar(10) default NULL,
`alarm_count` int(10) default NULL,
`visitor_count` int(10) default NULL,
`date` date default NULL,
PRIMARY KEY (`rid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
Description: Hi all , I have problem in MYSQL database , it always give me "unknown column "XXX" in 'field list' error. Eventhough the column are exists in my database. This problem occurs when i deploy my application to the Window Pro PC which using MyODBC to connect to MYSQL database which in the Window 2000 PC. Execute the VB code in W2K , it did not have the error message but it occurs when executed in the Window Professional PC. Below is the VB code Public Sub InitMYSQLDatabase() Dim l_Database As String Dim l_Server As String Dim l_User As String Dim l_Password As String Set fileProperties = New clsNLBFileInit l_Database = fileProperties.readSetting("MYSQL", "DATABASE") l_Server = fileProperties.readSetting("MYSQL", "SERVER") l_User = fileProperties.readSetting("MYSQL", "USER") l_Password = fileProperties.readSetting("MYSQL", "PASSWORD") On Error GoTo Handle Set objConnection = New ADODB.Connection With objConnection .CursorLocation = adUseClient .ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" & _ "SERVER=" & l_Server & ";" & _ "DATABASE=" & l_Database & ";" & _ "USER=" & l_User & ";" & _ "PASSWORD=" & l_Password & ";" .Open End With If objConnection.State = adStateOpen Then DoEvents 'MsgBox "Connection was successfully opened...", vbInformation + vbOKOnly + vbSystemModal, "Successful" End If Handle: If Err.Number = -2147467259 Then Err.Raise vbObjectError, "InitMYSQLDatabase()", _ "No connection to the Database , the program will be close" Err.Clear End If If Err.Number <> 0 Then Err.Raise DATABASE_ERR Err.Clear End If End Sub Public Sub InsertCCLGateTable(m_branch_code As String, m_gate_id As String, m_alarm_count As Integer, _ m_visitor_count As Integer, is_newDay As Boolean) Dim SQL As String Dim cmdADODB As ADODB.Command Dim rstByADODB As ADODB.Recordset Dim branchCodeprm As ADODB.Parameter Dim gateIdprm As ADODB.Parameter Dim alarmCountprm As ADODB.Parameter Dim visitorCountprm As ADODB.Parameter Dim dateprm As ADODB.Parameter On Error GoTo Handle objConnection.BeginTrans If is_newDay Then SQL = "INSERT INTO gate_table(branch_code,gate_id,alarm_count,visitor_count,date) VALUES(?,?,?,?,?)" Else SQL = "UPDATE gate_table set branch_code = ? , gate_id = ?, alarm_count = ? , visitor_count = ? WHERE DATE = ?" End If Set cmdADODB = New ADODB.Command cmdADODB.CommandText = SQL cmdADODB.CommandType = adCmdText 'Call SelectMinRecord 'Notes: 'add "'" Single quote to solve MYSQL bug : unknown column errors 'for e.g : "'" & m_branch_code & "'" 'Cause : maybe OS platform , in W2k no need single quotes but WINpro need to Set branchCodeprm = cmdADODB.CreateParameter("branch_code", adBSTR, adParamInput, 10, m_branch_code) Set gateIdprm = cmdADODB.CreateParameter("gate_id", adBSTR, adParamInput, 10,m_gate_id) Set alarmCountprm = cmdADODB.CreateParameter("alarm_count", adInteger, adParamInput, 1, m_alarm_count) Set visitorCountprm = cmdADODB.CreateParameter("visitor_count", adInteger, adParamInput, 1, m_visitor_count) Set dateprm = cmdADODB.CreateParameter("date", adDBDate, adParamInput, 1, Now) cmdADODB.Parameters.Append branchCodeprm cmdADODB.Parameters.Append gateIdprm cmdADODB.Parameters.Append alarmCountprm cmdADODB.Parameters.Append visitorCountprm cmdADODB.Parameters.Append dateprm Set cmdADODB.ActiveConnection = objConnection Set rstByADODB = cmdADODB.Execute If Not rstByADODB Is Nothing Then If rstByADODB.State = adStateOpen Then rstByADODB.Close End If Set rstByADODB = Nothing End If objConnection.CommitTrans Handle: If Err.Number = 3704 Then objConnection.RollbackTrans Err.Raise DATABASE_ERR Err.Clear End If If Err.Number <> 0 Then objConnection.RollbackTrans Err.Raise DATABASE_ERR Err.Clear End If 'cleaning up Set cmdADODB = Nothing Set rstByADODB = Nothing Cleaning branchCodeprm, gateIdprm, alarmCountprm, visitorCountprm, dateprm End Sub How to repeat: This my table structure : | gate_table | CREATE TABLE `gate_table` ( `rid` bigint(20) NOT NULL auto_increment, `branch_code` varchar(10) default NULL, `gate_id` varchar(10) default NULL, `alarm_count` int(10) default NULL, `visitor_count` int(10) default NULL, `date` date default NULL, PRIMARY KEY (`rid`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |