How do I convert my dates to string in ADO

[tshad]

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

 

[Mark Rae]

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...

 

[tshad]

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

 

[Mark Rae]

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...?

 

[tshad]

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

 

[Mark Rae]

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...

 

[tshad]

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