Solved

SQL Convert Error

Posted on 2010-08-18
5
278 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
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…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

839 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