473,836 Members | 1,539 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

How do I convert my dates to string in ADO

Apparently, I can't do:

Dim da2 As New OleDb.OleDbData Adapter("Select PR,
Convert(varchar ,getchar(),1),F 1, 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 3017
"tshad" <t@home.comwrot e in message
news:uC******** *****@TK2MSFTNG P05.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**@markNOSPA Mrae.comwrote in message
news:uR******** ******@TK2MSFTN GP02.phx.gbl...
"tshad" <t@home.comwrot e in message
news:uC******** *****@TK2MSFTNG P05.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.comwrot e in message
news:ez******** ******@TK2MSFTN GP02.phx.gbl...
"Mark Rae" <ma**@markNOSPA Mrae.comwrote in message
news:uR******** ******@TK2MSFTN GP02.phx.gbl...
>"tshad" <t@home.comwrot e in message
news:uC******* ******@TK2MSFTN GP05.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**@markNOSPA Mrae.comwrote in message
news:e2******** ******@TK2MSFTN GP06.phx.gbl...
"tshad" <t@home.comwrot e in message
news:ez******** ******@TK2MSFTN GP02.phx.gbl...
>"Mark Rae" <ma**@markNOSPA Mrae.comwrote in message
news:uR******* *******@TK2MSFT NGP02.phx.gbl.. .
>>"tshad" <t@home.comwrot e in message
news:uC****** *******@TK2MSFT NGP05.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
presentatio n 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(varcha r,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 108500000000000 00-1523

So I was using the - Convert(varchar ,getchar(),1) - to get the date, and -
right("00000000 00"+convert(var char,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.OleDbData Adapter to SqlDb and that
would solve the problem?

I use the following connection strings:

Dim ConStr As String = _

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

path & ";Extended Properties=""Te xt;HDR=No;FMT=D elimited\"""

Dim conn As New OleDb.OleDbConn ection(ConStr)

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

Thanks,

Tom
Apr 3 '07 #5
"tshad" <t@home.comwrot e in message
news:O1******** *****@TK2MSFTNG P06.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(varcha r,getchar(),1)" .
"Provider=Micro soft.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**@markNOSPA Mrae.comwrote in message
news:%2******** ********@TK2MSF TNGP02.phx.gbl. ..
"tshad" <t@home.comwrot e in message
news:O1******** *****@TK2MSFTNG P06.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(varch ar,getchar(),1) ".
>"Provider=Micr osoft.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
9315
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 three dropdow boxes for hours, minutes, and AM/PM. All of these need to be considered together and converted to one Unix Timestamp and then inserted to the MYSQL date field. The type of field is INT (11) so that I can instead of the standard...
19
7303
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. Specifically, I have a problem with this portion in the WHERE clause: DATEADD(Day,tblMyEventTableName.ReminderDays, @DateNow) Between CONVERT(smalldatetime,str(DATEPART(Month, @DateNow)+1) + '/' + str(DATEPART(Day, tblMyEventTableName.TaskDateTime)) + '/'...
4
22700
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 that there are different start days of the week so I would presume any function would provide that facility. Hope you can help Mark
14
7980
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
8077
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 instead of 06. Thanks a lot.
1
282
by: FAQ server | last post by:
----------------------------------------------------------------------- FAQ Topic - Why does 1+1 equal 11? or How do I convert a string to a number? ----------------------------------------------------------------------- Javascript variables are loosely typed: the conversion between a string and a number happens automatically. Since plus (+) is also used as in string concatenation, `` '1' + 1 '' is equal to `` '11' '': the String...
4
39357
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 will convert each of the records of these service dates to the first date of that month, with results showing: 7/1/2006, 7/1/2006, 7/1/2006. How would you place an expression on a query that will convert any given date to the first day of the month...
2
21135
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. When the dates are imported they are in the format dd/mm/yyyy. However on the reports the dates are in the in the format dd/mmm/yyyy. I now need to allow the fields to accept an asterix (*) as a prefix to the date to show dates that have not...
2
3526
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 output formats. It has a subclass SimpleDateFormat which you may also use). Use it to write a method called convert which returns a String in the form dd/mm/yy: when passed a GregorianCalendar with a specific date public String convert...
0
9671
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10852
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10255
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9382
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
6980
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5651
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5829
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4459
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4021
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.