Solved

SQL Convert Error

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Batch file or script with password 22 44
C# Gridview 1 33
T-SQL Default value in Select? 5 27
Release Dynamically Allocated Memory in C# 3 26
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…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

777 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