Solved

SQL Convert Error

Posted on 2010-08-18
5
276 Views
Last Modified: 2013-11-27
I am using Visual Studio 2008

I am having problems with the SQL query for my datasource.

This the query in question:
SELECT        inc.Title AS Incident, inc.Description, inc.CustomerID, tech.Name AS TechName, prod.Name AS Product, cust.Name AS Customer, CONVERT([varchar](11),                       inc.DateOpened, 101) AS DateOpened, CONVERT([varchar](11), inc.DateClosed, 101) AS DateClosed
FROM            (((Incidents inc INNER JOIN
                         Technicians tech ON inc.TechID = tech.TechID) INNER JOIN
                         Products prod ON inc.ProductCode = prod.ProductCode) INNER JOIN
                         Customers cust ON inc.CustomerID = cust.CustomerID)


Error I receive:

Error Source: Microsoft JET Database Engine
Error Message: Undefined function 'CONVERT' in expression

I am using the same database that I use in SMSS and the query works fine.
0
Comment
Question by:usky1
5 Comments
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 400 total points
ID: 33470878
CONVERT is a SQL Server function.  For Access, use Format.  For, say, mm/dd/yyyy:Format(DateClosed, "mm/dd/yyyy")
0
 
LVL 3

Expert Comment

by:celdridgeMadman
ID: 33470883
I'm not sure what type of data source you are trying to connect to here, but it's possible it doesn't support the CONVERT function.

You say it works in SSMS, if that's the case is this a Microsoft SQL Server? And if so, probably best the change the Database Engine from JET to native SQL or something similar.
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33471015
If you are using Jet to connect to Sql server, there is something very wrong.
Check here for the connection string to use:

http://www.connectionstrings.com/sql-server-2005
0
 

Author Comment

by:usky1
ID: 33471086
I am trying some of you suggestions and will let you know what I got to work. Thanks for the input.
0
 

Author Closing Comment

by:usky1
ID: 33496710
matthewspatrick:

I miissed a binding that was set for Access instead of SQL.

Thanks
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
Delivering innovative fully-managed cloud services for mission-critical applications requires expertise in multiple areas plus vision and commitment. Meet a few of the people behind the quality services of Concerto.

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now