Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
tecknowledge1164
Starting Member
25 Posts |
 Posted - 2008-04-23 : 19:17:43
|
| I am using vb.net 2005 to convert data from a comma separated values file into a SQL 2005 database. Some of the records need to have the data from several records concatinated into one text field. Each record has a sort order and they need to be assembled in the correct order. The problem is that I need to order the records by the sort field and since this is text data the program does not treat it numerically. So if there are 12 rows it sorts as 1, 10, 11, 12, 2, 3 and so on. Is there any way to convert the field to its numeric equivalent in an embedded sql select command in Visual Basic? |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-04-23 : 22:10:08
|
| You can use sql's convert or cast function for this. |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2008-04-24 : 03:42:10
|
| 1 Always use proper datatype2 order by cast(col as int)MadhivananFailing to plan is Planning to fail |
|
|
|
tecknowledge1164
Starting Member
25 Posts |
Posted - 2008-04-24 : 10:49:18
|
quote:
Originally posted by rmiao
You can use sql's convert or cast function for this.
There seems to be an issue with reading from a text file for this When I try it I get the following error: ERROR[42000][Microsoft][ODBC Text Driver] Syntax Error(missing operator) in query expression 'CAST(SEQ As Int)'The fields from the text file that I need are DocNumber, Notes, and SEQ. The notes field has info spread out in several rows that needs to be gathered into one text field in the destination database. The SEQ field has the sequence numbers that need to be ordered by. The command I put in my vb code looks like this: sSql = "SELECT Notes, CAST(SEQ As Int) AS Sort FROM Text.csv WHERE DocNumber = " & sDocNum & " ORDER BY Sort". As you may have guessed, there is a schema.ini file that defines the text file fields. This one indicates that there are ColNameHeaders but I have tried changing this to define the columns and datatypes as text or numeric with no success. |
|
|
|
tecknowledge1164
Starting Member
25 Posts |
Posted - 2008-04-24 : 16:38:58
|
| Ok, I got it to work. What I did was execute a data reader on the sql as always, then looped through the data reader and inserted the rows into a data table, converting the sort field with the val function. Then I used a data view from the table ordered by the sort field. Just a few extra steps but it works. Thanks for the suggestions. |
|
|
|
|
|
|
|
|
Data Conversion