Type Mismatch Error using Like% in Stored Procedure

[mattdrinks]

I have a query in Access:

SELECT Lines.Part AS PartNumber, Product.Desc AS Description, Stock.Locn AS Location, CSng([Free]) AS TotalStock, CSng([SCode]) AS OastStock, CSng(CSng(Stock!Free)-CSng(Product!Scode)) AS ShopStock, Sum(CSng([Qty])) AS QtySold
FROM (Lines INNER JOIN Product ON Lines.Part = Product.KeyCode) INNER JOIN Stock ON Lines.Part = Stock.Part
GROUP BY Lines.Part, Product.Desc, Stock.Locn, CSng([Free]), CSng([SCode]), CSng(CSng(Stock!Free)-CSng(Product!Scode)), Lines.Today
HAVING (((Stock.Locn) Like "HOME*") AND ((Lines.Today) Like "DIC*"));

When I run it using OleDb in C# I get No Records returned, (There are records returned in Access). I have had this problem before and remebered that I needed to change the * to %. I have done this and created the new query in Access:

SELECT Lines.Part AS PartNumber, Product.Desc AS Description, Stock.Locn AS Location, CSng([Free]) AS TotalStock, CSng([SCode]) AS OastStock, CSng(CSng(Stock!Free)-CSng(Product!Scode)) AS ShopStock, Sum(CSng([Qty])) AS QtySold
FROM (Lines INNER JOIN Product ON Lines.Part = Product.KeyCode) INNER JOIN Stock ON Lines.Part = Stock.Part
GROUP BY Lines.Part, Product.Desc, Stock.Locn, CSng([Free]), CSng([SCode]), CSng(CSng(Stock!Free)-CSng(Product!Scode)), Lines.Today
HAVING (((Stock.Locn) Like "HOME%") AND ((Lines.Today) Like "DIC%"));

I run the following C# code:

using System;
using System.Data;
using System.Data.OleDb;
using System.Diagnostics;

namespace JERBusinessDocuments
{
	/// <summary>
	/// Holds data from a stock movement
	/// </summary>
	/// <remarks>
	/// Created by - Matt
	/// Created on - 30/04/2005 16:03:59
	/// </remarks>
	public class StockMovement : JERBusinessDocuments.Document
	{
		public static string LinkedDatabase;
		private string strProvider = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=";

		public StockMovement()
		{
			//
			// TODO: Add constructor logic here
			//
		}

		public void StockRequired(string Location)
		{
			//Set connection string to database and build SQL Statment
			string strConnection = strProvider + LinkedDatabase;
	

			//Connect to Lines database and execute query
		try
			{
			OleDbConnection LinkedConnection = new OleDbConnection(strConnection);

			OleDbCommand SoldCommand = new OleDbCommand("qryNEWStockHOME(ANSI)",LinkedConnection);

			SoldCommand.CommandType = CommandType.StoredProcedure;

			LinkedConnection.Open();
			OleDbDataReader myDataReader = SoldCommand.ExecuteReader();
			LinkedConnection.Close();

			OleDbDataAdapter SoldAdapter = new OleDbDataAdapter();
			SoldAdapter.SelectCommand = SoldCommand;

			DataSet SoldDataSet = new DataSet();
			SoldAdapter.Fill(SoldDataSet, "ItemsToReplenish");

			SoldDataSet.WriteXml(@"D:\Matt's Documents\VSXMLFile.xml");
			}
		
		catch (OleDbException ex)
			{
				for (int i = 0; i < ex.Errors.Count; i++)
				{
					Debug.Write ("Index #" + i.ToString() + "\n" +
						"Message: " + ex.Errors[i].Message + "\n" +
						"Native: " + ex.Errors[i].NativeError.ToString() + "\n" +
						"Source: " + ex.Errors[i].Source + "\n" +
						"SQL: " + ex.Errors[i].SQLState + "\n");
				}
			}
		}
	}
}

But get the following error:

Index #0
Message: Syntax error in parameters clause. Make sure the parameter exists and that you typed its value correctly.
Native: -230755778
Source: Microsoft JET Database Engine
SQL: 3746

Does anyone have any ideas?

Thanks Matt

 

[marinero]

Have you tried using single quotes?

 

[chiph]

Reformatted query to make it easier to read:

SELECT Lines.Part AS PartNumber, 
	Product.Desc AS Description, 
	Stock.Locn AS Location, 
	CSng([Free]) AS TotalStock, 
	CSng([SCode]) AS OastStock, 
	CSng(CSng(Stock!Free)-CSng(Product!Scode)) AS ShopStock, 
	Sum(CSng([Qty])) AS QtySold
FROM (Lines 
	INNER JOIN Product 
		ON Lines.Part = Product.KeyCode) 
	INNER JOIN Stock 
		ON Lines.Part = Stock.Part
GROUP BY Lines.Part, 
	Product.Desc, 
	Stock.Locn, 
	CSng([Free]), 
	CSng([SCode]), 
	CSng(CSng(Stock!Free)-CSng(Product!Scode)), 
	Lines.Today
HAVING (((Stock.Locn) Like "HOME%") 
	AND ((Lines.Today) Like "DIC%"));

Just an idea - is the CSng() function supported in Access SQL? Because it's not part of the ANSI standard.

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first

 

[mattdrinks]

Thanks for your help guys, I tried using single quotes instead of double ones and still get the same error. I also removed the Csng() function as you suggested Chip and still get an error. I have solved the problem by running the query as a text command instead of a procudure and and this seems to solve the problem.

final code:

using System;
using System.Data;
using System.Data.OleDb;
using System.Xml;
using System.Diagnostics;

namespace JERBusinessDocuments
{
	/// <summary>
	/// Holds data from a stock movement
	/// </summary>
	/// <remarks>
	/// Created by - Matt
	/// Created on - 30/04/2005 16:03:59
	/// </remarks>
	public class StockMovement : JERBusinessDocuments.Document
	{
		public static string LinkedDatabase;
		private string strProvider = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=";

		public StockMovement()
		{
			//
			// TODO: Add constructor logic here
			//
		}

		public void StockRequired(string Location)
		{
			//Set connection string to database and build SQL Statment
			string strConnection = strProvider + LinkedDatabase;
			string strSQL = 
"SELECT Lines.Part AS PartNumber,
  Product.Desc AS Description, Stock.Locn AS Location, 
  Stock.Free AS TotalStock, Product.SCode AS OastStock, 
  Stock!Free-Product!Scode AS ShopStock, 
  Sum(Lines.Qty) AS QtySold " + 
"FROM (Lines INNER JOIN Product ON Lines.Part = Product.KeyCode) 
INNER JOIN Stock ON Lines.Part = Stock.Part " +
"WHERE (((Stock.Locn) Like 'HOME%') AND ((Lines.Today) Like 'DIC%')) " +
"GROUP BY Lines.Part, Product.Desc, Stock.Locn, Stock.Free, Product.SCode, Stock!Free-Product!Scode;";

			//Connect to Lines database and execute query
		try
			{
			OleDbConnection LinkedConnection = new OleDbConnection(strConnection);

			OleDbCommand SoldCommand = new OleDbCommand(strSQL,LinkedConnection);

			OleDbDataAdapter SoldAdapter = new OleDbDataAdapter();
			SoldAdapter.SelectCommand = SoldCommand;

			DataSet SoldDataSet = new DataSet("StockReplenishment");
			SoldDataSet.ReadXmlSchema(@"D:\Matt's Documents\VSXMLFileMODIFIED.xsd");
			SoldAdapter.Fill(SoldDataSet, "ItemToReplenish");

			SoldDataSet.WriteXml(@"D:\Matt's Documents\VSXMLFile.xml");
			SoldDataSet.WriteXmlSchema(@"D:\Matt's Documents\VSXMLFile.xsd");
			}
		
			//Error catching
		catch (OleDbException ex)
			{
				for (int i = 0; i < ex.Errors.Count; i++)
				{
					Debug.Write ("Index #" + i.ToString() + "\n" +
						"Message: " + ex.Errors[i].Message + "\n" +
						"Native: " + ex.Errors[i].NativeError.ToString() + "\n" +
						"Source: " + ex.Errors[i].Source + "\n" +
						"SQL: " + ex.Errors[i].SQLState + "\n");
				}
			}
		}
	}
}

The SQL has changed slightly, it is cut and pasted from an Access query, that executes fine in Access and works fine as above, but causes an error when i try and run it as a stored procedure from C#!

Well atleast it's working now, I'll have another look at making work with a stored procdure when I have some spare time.

Thanks for you help,
Matt

 

[chiph]

I missed that -- unless your SQL was in a stored procedure, it must be run as a text command.

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first