DataSource Controls :: Using Cast And Sum On VarChar Column?

Sep 29, 2010

I have a little problem developing a SQL Select Statement on a column that is formatted pretty badly. Firstly, the Database has already been created and filled with Data in a Test Environment, I'm not sure if the format is the same or has been updated in Production but since I'm only able to work with the Test Environment right now I'm stuck with using what I have. The Column in one Table shows a Snapshot of a clients account which is set to VarChar(50) but the actual data in the column is formatted like a money format (with a $ and decimal point so for example "$5,000.00" would be an entry in one Row).

What I'm trying to do is a create a WebService that will be used in a iPhone Application and one of the Methods needs to just give a quick display of the total value of all Clients with each specific company but I can't perform a SUM function on a VarChar column and I've tried to CAST and CONVERT the column into money, int, decimal and just about every format I can think of but I keep getting an error stating that I convert the data type varchar to which ever one I'm trying. I think it has something to do with the $ already listed in the Column, so I wanted to know what I could do to either remove the $ sign (like a Trim option) or if there is a better way of doing this that I can't think of. I know the best option would be to convert the column to the appropriate format but as I said, this was a previously designed system and I'm not sure if the Production area is the same (most likely not).

Here's what I've tried so far:

[Code]....

View 5 Replies


Similar Messages:

Forms Data Controls :: How To Convert A Varchar Column To Int And Sort In Grid View

Jul 21, 2010

I have a grid view with manual sorting. In some cells, the data are numeric and datetime. But due to some constrains, the data type on tables are varchar. The problem here is when I sorting those numeric data, it is treated as varchar.Eg: 1, 12, 13, 2, 23, 3, ...But I wanted the order to be: 1, 2, 3, 12, 13, 23, ...I have tried these method, but it doesnt works.

[Code]...

View 9 Replies

DataSource Controls :: Map Stringbuilder With Varchar?

Feb 12, 2010

Im developing a web application in asp.net where i have gridview with some text box controls in it.In runtime we are creating a new row dynamically by clicking add button .To insert data into the database.i used a stringbuilder instead of string becoz the data what i have to insert into database is more.but i am gettin the following error No mapping exists from object type System.Text.StringBuilder to a known managed provider native type. in database the datafield column datatype is varchar(max).

View 3 Replies

DataSource Controls :: How To Use Varchar And NVarchar

Mar 2, 2010

I want to use three language: English, German and italian. If i will use varchar type, will i face any problem or I have to use nVarchar.

View 3 Replies

DataSource Controls :: Difference Between Char And Varchar?

Apr 4, 2010

What are the basic difference between the char and varchar datatype ?

View 5 Replies

DataSource Controls :: Replace All Varchar To Nvarchar?

Apr 1, 2010

i want to convert all my database columns whose data type is char,varchar, nchar to nvarchar datatype. But the main problem is that some of my primary key, foreign key columns are also of varchar datatype. So when i try to change their datatypes to nvarchar then i receive the errors related to constraints of Primary Key, Unique Key and indexes. how to change the datatypes to nvarchar after droping the constraints and after changing all constraints will apply again. I also want that no data loss will occur.

View 5 Replies

DataSource Controls :: Padding In Varchar Database Field?

May 7, 2010

I am using:

href='<%# Eval("Directory")+eval("Agenda")%>

to provide a link to documents held in directory so directory contains [URL] or [URL] Agenda contains

00209-FebruaryMtgMin%5E_.pdf

If I declare Directory as VarChar(nnn) where nnn is large enough to accomodate the longer field, The statement <%# Eval("Directory")+eval("Agenda")%> returns

[URL]self.aspx/.Public/ 00209-FebruaryMtgMin%5E_.pdf

How can I get rid of the Pad characters?

View 3 Replies

DataSource Controls :: How To Convert Varchar To Date Format

Apr 21, 2010

In my database im storeing date as varchar. Now i want to display the table in grid view sorted by date.So how to convert varchar to datetime.

Im trying the following querry but its not working.

SELECT

[Name],
convert(datetime
,103,[sdate]),
[place], [country],
FROM [table]

View 10 Replies

DataSource Controls :: Specify Size For A Nvarchar(max) Or Varchar(max) Type Of Parameter In ADO.Net?

Mar 4, 2010

I have an output parameter called '@packIds', that I create in ADO.Net using the code below:

SqlParameter packIdPara = new SqlParameter("@packIds", null);
packIdPara.SqlDbType = SqlDbType.VarChar;
packIdPara.Direction = ParameterDirection.Output;
sqlCmd.Parameters.Add(packIdPara);

How will I specify that the parameter '@packIds' is of max size? I cannot find any code for this.

[Code]....

View 3 Replies

DataSource Controls :: Filtering Date Stored As Varchar In Table

Mar 9, 2010

I save dates as a string in a varchar data field named "TilDato" in a SQLServer-express table. I want to filter the records smaller than a specific date from a variable in the script. I try the following statement, but it does not seems function logically.

objCmd = new
SqlCommand("SELECT * FROM Reservering WHERE @laanedato<=TilDato",myConn2);
objCmd.Parameters.Add("@laanedato", laanedato);
objRdr = objCmd.ExecuteReader();

View 2 Replies

DataSource Controls :: Insert DBNull Value Into Varchar 1 Null Field

Aug 12, 2010

My database includes a table with sever filds which are defined as (varchar(1),null), I tried using DBNull.Value, null, ' ' but cannot get a null inserted in that field of the database,

[Code]....

View 4 Replies

DataSource Controls :: Display Varchar Values In Descending Order Like MM-YY Format

Jan 25, 2010

I have a column of type varchar that lists the values in this format:

SEP-09
SEP-08
OCT-09
OCT-08
NOV-09
NOV-08
MAY-09
MAR-09
JUN-09
JUL-09
JAN-10
JAN-09

I need to sort this list by descending order treated as actual date. I tried converting it to date and then tried to extract the year and month out but I keep receiving conversion errors. Does anyone know how to get this in the descending order to it shows up like this:

JAN-10
NOV-09
OCT-09
SEP-09
JUL-09
NOV-08
OCT-08
SEP-08
JUN-09
MAY-09
MAR-09
JAN-09

This is the closest I got but it keeps sorting the list alphabetically:

select distinct periodname, Convert(varchar,periodname,112) from periodtableorder by Convert(varchar,periodname,112) desc

View 2 Replies

DataSource Controls :: Editing Text Enconding Problem With Varchar() Type?

Apr 13, 2010

when I edit varchar field and save it to Sql Server, edited non-english text look as ??? symbols.I tried to correct this as

in web.config:
<globalization
fileEncoding="windows-1251"

[code]...

View 3 Replies

Trying To Insert '00' Into A Varchar Column Of Database?

Mar 13, 2010

Not sure if this should be in SQL or .NET but as I'm doing this in asp.net I figured here..I'm trying to insert '00' into a varchar column of my database, when I do this its actually in the database as '0'... if I do '05' or '15' or '30' then its fine.

View 5 Replies

DataSource Controls :: LINQ VB.net Specified Cast Is Not Valid?

Jun 29, 2010

just needing a 2nd set of eyes looking at this to make sure the error isn't something else other than my LINQ code here. Here's the function class itself:

[Code]....

For some reason it's flagging the 2nd line in the GetJacketByPolicyID function saying the specified cast is not valid. So I'm guessing it's something I'm doing wrong. I'm sure the tblPolicy/tblPolicies class works right since I can create a new instance of a tblPolicy and set a few variables by hand and return it, so that's not it. I've also checked the datarow I'm fetching and there's no null values in the record, so that shouldn't be it either.

View 2 Replies

DataSource Controls :: Data Types Text And Varchar Are Incompatible In The Equal To Operator?

Jul 4, 2010

I get the following error: The data types text and varchar are incompatible in the equal to operator.here's my code (PageName is a string Parameter) :

SqlConnection sql = SQLconnection();
en();
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "Select title, text, postdate, sticky from articles where pagename = @pagename;";
[code]...

View 2 Replies

Conversion Of The Varchar Value '8342427452' Overflowed An Int Column?

Nov 7, 2010

Codes bigint
Model nvarchar(50)
Customer nvarchar(250)
ShipDate date Checked
DeliveryNoteNumber int Checked
ItemCode nvarchar(50) Checked
Description nvarchar(250) Checke

View 2 Replies

Conversion Of The Varchar Value '0000033878000010001' Overflowed An Int Column?

Mar 10, 2010

error: The conversion of the varchar value '0000033878000010001' overflowed an int column. Maximum integer value exceeded.I am trying to call ('0000033878000010001')this value from stored procedure.I pass 3 parameters and it should generate this value and insert it in one table.I am able to do this with (000010001) this part of the value.ut i also need constant(0000033878) value append to the above codeif i try to do so i am getting("The conversion of the varchar value '0000033878000010001' overflowed an int column. Maximum integer value exceeded." )this error at this

View 5 Replies

SQL Server :: Sort Varchar Column With Number?

Dec 4, 2010

i have a column in my table of type varchar (50)

beek1
beek10
beek3
beek6
beek61
beek2

i want to sort in my sql querry like this

beek1
beek2
beek3
beek6
beek10
beek61

View 5 Replies

SQL Server :: Sort A Column Varchar (50) That Contains Number?

Dec 4, 2010

how to write Sql querry to sort a column of type varchar (50) that contains number

a1
a2
a3
a11
a21

instead of

a1
a11
a2
a21
a3

View 9 Replies

DataSource Controls :: Split / Cast A String In Sql For Comparison?

Feb 8, 2010

comparing integer values which are actually in string (varchar(9)) right now...I'm trying to do the following in a stored procedure..

SET @MinProjectYear = SELECT ProjYear FROM P_A WHERE ID = @ID
SET @MaxProjectYear = SELECT ProjYear FROM P_A WHERE UserId = @UserID AND...
IF EXISTS(SELECT 1 FROM P_A WHERE MinProjectYear <= MaxProjectYear)
return true

The MinProjectYear and MaxProjectYear are varchar(9) values stored as "2007-2008", "2009-2010", etc.
How do I split and compare the years?? i.e. check if 2007-2008 <= 2009-2010 ??

View 6 Replies

DataSource Controls :: Session Variable Causes Cast Error?

Jun 23, 2010

I get a casting error on line #8 below which reads "db.UserID = Session("UserID")". What would be the correct way to cast this? The datatype of the DB field is GUID.

[Code]....

View 4 Replies

DataSource Controls :: Object Can Not Be Cast From Dbnull To The Other Types?

May 24, 2010

I have a webpage, i have a usercontrol in it , its name ispersonal1, i have tow Radiobutton in this usercontrol, the id of one of them ismale and the other one is female, it is obviously that each time we fill one of these radiobuttons and the other one will be null,in my database the type of these fields are bit , and they have allow null, i defined a property for my usercontrol like this:

public bool male
{
get

[code]...

View 2 Replies

DataSource Controls :: Invalid Cast Exception When Trying To Utilize A SQL MAX Function Within C Sharp Code?

Mar 30, 2010

The error that I get from this code states that the cast is invalid and that the number must be less than infinity. [Code]....

By the way, I tried to paste my code using the icon from the toolbar but it didn't work. Firefox problem maybe?

View 3 Replies

DataSource Controls :: Unable To Cast Object Of Type 'System.Data.Linq.DataQuery?

Mar 31, 2010

I wanted to delete the record from data but I receive this error "Unable to cast object of type 'System.Data.Linq.DataQuery`1[training_eval.Course]' to type 'training_eval.Course'.

Dim eval_sure = From ev In db.Evaluates Where ev.course_id = indexcourse Select ev.course_id
Dim del_course = From c In db.Courses Where c.Course_id = indexcourse
db.Courses.DeleteOnSubmit(del_course)
db.SubmitChanges()

View 4 Replies







Copyrights 2005-15 www.BigResource.com, All rights reserved