I need to be able to run the following SQL Statements at the same time from an ASP.NET application. The query is built on the fly and passed to the MYSQL Database. When I execute each individually in MYSQL Query Browser it runs fine but when I try the web app I get an error:
The Error:
My Query:
The Error:
Code:
Event Type: Error
Description:
The description for Event ID ( 0 ) in Source ( WSInterop ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: Reference Num: 94315-3132
Application Name: NA
Message:
OdbcException - SearchForVehicle
ERROR [42000] [MySQL][ODBC 5.1 Driver][mysqld-5.0.51b-community-nt]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ';
Create TABLE tmp_Veh (ROWNUM INT AUTO_INCREMENT' at line 1
Stack Trace: at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader)
at System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Odbc.OdbcCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable)
at CTS.BASE.DBObject.RunDBCommandFillDataSet(String commandText, CommandType commandType, DbParameter[] parameters, String tableName, DataSet dataSet)
at CTS.DBOBJECT.MARIANNA.BaseQuery`1.FillDataTable(String key, DataSet ds, DataTable dt, T desc, String UserName, String userID)
at CTS.DBOBJECT.MARIANNA.VehicleSearch.SearchForVehicle(VehicleDescription oVeh, Int32 startRow, Int32 endRow, String UserID, String UserName)
at CTS.DBOBJECT.MARIANNA.DBLogic.SearchForVehicle(VehicleDescription oVehicle, String UserID, String UserName, Int32 StartRow, Int32 EndRow, String PageID, String PageDir)
at CTS.WS.DATASEARCH.BIZLogic.SearchForVehicle(VehicleDescription oVeh, RequestorSecurityLevel RequestorInfo, UsernameToken uToken, Int32 pageNum, String pageID, String pageDir)
at CTS.WS.DATASEARCH.SEARCH.SearchForVehicle(String VTag, String VTagState, String VVIN, String VYear, String VMake, String VModel, String VColor, RequestorSecurityLevel RequestorInfo, Int32 pageNum, String pageID, String pageDir)
.My Query:
Code:
Create TABLE tmp_Veh (ROWNUM INT AUTO_INCREMENT, TblSource cHAR(20), UNIQUEKEY INT,
VIN CHAR(100), VYEAR varchar(10), MAKE CHAR(20), VModelText CHAR(20),
TAG CHAR(20), STATE CHAR(3), COLORSOLID CHAR(20), ORIGINATINGORG CHAR(3), TotalCount INT, PRIMARY KEY(ROWNUM));
LOCK TABLE tmp_Veh WRITE;
INSERT INTO tmp_Veh(TblSource, UNIQUEKEY, VIN, VYEAR, MAKE, VModelText, TAG, STATE, COLORSOLID, ORIGINATINGORG, TotalCount)
SELECT 'OFFENSE' as TblSource
, v.Primary as UNIQUEKEY
, v.VIN as VIN
, v.YEAR as VYEAR
, v.MAKE as MAKE
, v.Model as VModelText
, v.License as TAG
, v.Lic_Stat as STATE
, v.Top_clr as COLORSOLID
, ' ' as ORIGINATINGORG
, 1 as TotalCount
FROM Vehicle v inner join incident i on v.CN = i.CN
INNER JOIN charges c on v.CN = c.CN
WHERE UPPER(v.License) = 'ABC123'
UNION
SELECT 'TRAFFIC' as TblSource
, v.Primary as UNIQUEKEY
, v.VIN as VIN
, v.YEAR as VYEAR
, v.MAKE as MAKE
, v.Model as VModelText
, v.License as TAG
, v.Lic_Stat as STATE
, v.Top_clr as COLORSOLID
, ' ' as ORIGINATINGORG
, 1 as TotalCount
FROM Vehicle v inner join incident i on v.CN = i.CN
INNER JOIN accident c on v.CN = c.ComplaintNumber
WHERE LTRIM(v.License) = 'ABC123'
UNION
SELECT 'UCM' as TblSource
, v.Primary as UNIQUEKEY
, v.VIN as VIN
, v.YEAR as VYEAR
, v.MAKE as MAKE
, v.Model as VModelText
, v.License as TAG
, v.Lic_Stat as STATE
, v.Top_clr as COLORSOLID
, ' ' as ORIGINATINGORG
, 1 as TotalCount
FROM Vehicle v inner join incident i on v.CN = i.CN
INNER JOIN ticket t on i.CN = t.CaseNumber
WHERE LTRIM(v.License) = 'ABC123' ;
Select * from tmp_Veh where RowNum > 0 and RowNum < 10;
DROP TABLE tmp_Veh;
UNLOCK TABLES;[\code]