473,320 Members | 1,846 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

How do I convert my dates to string in ADO

Apparently, I can't do:

Dim da2 As New OleDb.OleDbDataAdapter("Select PR,
Convert(varchar,getchar(),1),F1, F2, F5, Sum(F4) from temp
....

I am getting this error.

'undefined function "convert" in expression'

This is how I convert my dates to mm/dd/yy format in my Sql Stored
procedures but it doesn't seem to work for my DataAdapter.

What am I missing?

I am trying to convert todays date to this format.

Thanks,

Tom
Apr 2 '07 #1
6 2974
"tshad" <t@home.comwrote in message
news:uC*************@TK2MSFTNGP05.phx.gbl...
What am I missing?
FWIW, I *never* do this in the database layer... I always return the raw
data from SQL Server, and then format it the way I want it in the
presentation layer...
Apr 2 '07 #2
"Mark Rae" <ma**@markNOSPAMrae.comwrote in message
news:uR**************@TK2MSFTNGP02.phx.gbl...
"tshad" <t@home.comwrote in message
news:uC*************@TK2MSFTNGP05.phx.gbl...
>What am I missing?

FWIW, I *never* do this in the database layer... I always return the raw
data from SQL Server, and then format it the way I want it in the
presentation layer...
I would agree with you here but this is a report I am reading in and
formating using the DataAdapter in 5 different summary reports. I pass the
datatable to one buildCSV function that just takes the table and puts quotes
and commas around the fields (regardless to what they are). I don't want to
put the formatting for each table in the BuildCSV file. It is easier to use
Sql To do this.

But I need the convert function or something like it to convert it to fixed
sizes and to build things such as zero filled fields.

Thanks,

Tom
Apr 2 '07 #3
"tshad" <t@home.comwrote in message
news:ez**************@TK2MSFTNGP02.phx.gbl...
"Mark Rae" <ma**@markNOSPAMrae.comwrote in message
news:uR**************@TK2MSFTNGP02.phx.gbl...
>"tshad" <t@home.comwrote in message
news:uC*************@TK2MSFTNGP05.phx.gbl...
>>What am I missing?

FWIW, I *never* do this in the database layer... I always return the raw
data from SQL Server, and then format it the way I want it in the
presentation layer...

I would agree with you here but this is a report I am reading in and
formating using the DataAdapter in 5 different summary reports. I pass
the datatable to one buildCSV function that just takes the table and puts
quotes and commas around the fields (regardless to what they are). I
don't want to put the formatting for each table in the BuildCSV file. It
is easier to use Sql To do this.

But I need the convert function or something like it to convert it to
fixed sizes and to build things such as zero filled fields.
OK, let's back up a bit...

You mention Sql Stored procedures which leads me to think you are using
Microsoft SQL Server, but you are using OleDb... is that correct...? If so,
why are you not using the native .NET SQL Server data provider...?
Apr 3 '07 #4
"Mark Rae" <ma**@markNOSPAMrae.comwrote in message
news:e2**************@TK2MSFTNGP06.phx.gbl...
"tshad" <t@home.comwrote in message
news:ez**************@TK2MSFTNGP02.phx.gbl...
>"Mark Rae" <ma**@markNOSPAMrae.comwrote in message
news:uR**************@TK2MSFTNGP02.phx.gbl...
>>"tshad" <t@home.comwrote in message
news:uC*************@TK2MSFTNGP05.phx.gbl...

What am I missing?

FWIW, I *never* do this in the database layer... I always return the raw
data from SQL Server, and then format it the way I want it in the
presentation layer...

I would agree with you here but this is a report I am reading in and
formating using the DataAdapter in 5 different summary reports. I pass
the datatable to one buildCSV function that just takes the table and puts
quotes and commas around the fields (regardless to what they are). I
don't want to put the formatting for each table in the BuildCSV file. It
is easier to use Sql To do this.

But I need the convert function or something like it to convert it to
fixed sizes and to build things such as zero filled fields.

OK, let's back up a bit...

You mention Sql Stored procedures which leads me to think you are using
Microsoft SQL Server, but you are using OleDb... is that correct...? If
so, why are you not using the native .NET SQL Server data provider...?
Yes, sort of.

I am using Sql for most of my stuff. But in this case, I am reading in a
file from a .CSV file which happens to be a report. I then read this into a
DataSet. I then create about 5 reports all sorting and grouping to get the
different reports the client needs. I don't hit Sql at all here.

What I was saying about stored procedures was that in my other projects and
web pages this is how I always format my dates -
"Convert(varchar,getchar(),1)".

This is just a project I am working on currently that doesn't need Sql
Server but does need to do selects, sorts, grouping etc.

I was normally just taking the data from the report and writing it out to a
..csv file and had no problem there.

But this last report I need to create a fixed formatted line where the data
is just jammed next to each other. For example:

PR031507THIS IS A COMMENT 10850000000000000-1523

So I was using the - Convert(varchar,getchar(),1) - to get the date, and -
right("0000000000"+convert(varchar,amt),10) - to get the amount but left
fill with zeros and the length needs to be 10.

But if ADO.Net can't do this than I need to do it some other way. This
works fine in Sql Server.

Are you saying I can change it from OleDb.OleDbDataAdapter to SqlDb and that
would solve the problem?

I use the following connection strings:

Dim ConStr As String = _

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _

path & ";Extended Properties=""Text;HDR=No;FMT=Delimited\"""

Dim conn As New OleDb.OleDbConnection(ConStr)

Can I use the above with SqlClient to do the .csv reads?

Thanks,

Tom
Apr 3 '07 #5
"tshad" <t@home.comwrote in message
news:O1*************@TK2MSFTNGP06.phx.gbl...
>You mention Sql Stored procedures which leads me to think you are using
Microsoft SQL Server, but you are using OleDb... is that correct...? If
so, why are you not using the native .NET SQL Server data provider...?
Yes, sort of.

I am using Sql for most of my stuff. But in this case, I am reading in a
file from a .CSV file which happens to be a report. I then read this into
a DataSet. I then create about 5 reports all sorting and grouping to get
the different reports the client needs. I don't hit Sql at all here.

What I was saying about stored procedures was that in my other projects
and web pages this is how I always format my dates -
"Convert(varchar,getchar(),1)".
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
Ah...! So you're trying to use SQL Server T-SQL syntax against a Jet
database - this isn't going to work in this case because the Convert()
function doesn't exist in the flavour of SQL which Jet uses... Been a long
while since I worked with Jet, but I have a feeling that you might need to
use the Format() function...

If you actually have a copy of Access installed, I'd suggest opening a Jet
database in that and linking to your CSV file and then using the Query
builder to do what you want. Then you can inspect the Jet SQL that it has
created...
Apr 3 '07 #6
"Mark Rae" <ma**@markNOSPAMrae.comwrote in message
news:%2****************@TK2MSFTNGP02.phx.gbl...
"tshad" <t@home.comwrote in message
news:O1*************@TK2MSFTNGP06.phx.gbl...
>>You mention Sql Stored procedures which leads me to think you are using
Microsoft SQL Server, but you are using OleDb... is that correct...? If
so, why are you not using the native .NET SQL Server data provider...?
Yes, sort of.

I am using Sql for most of my stuff. But in this case, I am reading in a
file from a .CSV file which happens to be a report. I then read this
into a DataSet. I then create about 5 reports all sorting and grouping
to get the different reports the client needs. I don't hit Sql at all
here.

What I was saying about stored procedures was that in my other projects
and web pages this is how I always format my dates -
"Convert(varchar,getchar(),1)".
>"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _

Ah...! So you're trying to use SQL Server T-SQL syntax against a Jet
database - this isn't going to work in this case because the Convert()
function doesn't exist in the flavour of SQL which Jet uses... Been a long
while since I worked with Jet, but I have a feeling that you might need to
use the Format() function...

If you actually have a copy of Access installed, I'd suggest opening a Jet
database in that and linking to your CSV file and then using the Query
builder to do what you want. Then you can inspect the Jet SQL that it has
created...
Sounds like a great idea. I'll try that out. I didn't realize Convert
wasn't there.

Thanks,

Tom
Apr 11 '07 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

13
by: perplexed | last post by:
How do you convert a user inputted date to a unix timestamp before insterting it into your database? I have a form, with a textfield for a date that the user inputs in the format mm-dd-yyyy and...
19
by: Lauren Quantrell | last post by:
I have a stored procedure using Convert where the exact same Convert string works in the SELECT portion of the procedure but fails in the WHERE portion. The entire SP is listed below....
4
by: Mark | last post by:
Hi I have been trying to convert the week number to a range of dates that I can use. It should be fairly simple for you guru's out there but for us mere mortals it is beyond our grasp. I know...
14
by: Me | last post by:
Hi all I am getting a really bizzare error on when I convert a string into a datetime: The code is : DateTime dt1 = Convert.ToDateTime("10 Sep 2005"); Console.WriteLine(dt1.Year);
15
by: angellian | last post by:
Sorry to raise a stupid question but I tried many methods which did work. how can I conserve the initial zero when I try to convert STR(06) into string in SQL statment? It always gives me 6...
1
by: FAQ server | last post by:
----------------------------------------------------------------------- FAQ Topic - Why does 1+1 equal 11? or How do I convert a string to a number?...
4
by: perryclisbee via AccessMonster.com | last post by:
I have dates of service for several people that range all over each month. ie: patient had dates of service of: 7/3/2006, 7/24/2006 and 7/25/2006. I need to create a new field via a query that...
2
by: Kakishev | last post by:
I have a problem at how best to convert a Date into a text field and keep the format dd/mmm/yyyy (01-FEB-2007). The problem is that dates are imported from SQL into an access front end Database....
2
by: JEEtoP | last post by:
hoping someone can help, got a bit of homework I'm stuck on. It's as follows... There is a standard class called DateFormat which (among other things) lets you convert dates to various different...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.