.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

data type with Varchar replacement

Posted By:      Posted Date: September 27, 2010    Points: 0   Category :Sql Server

Hi all

I was using derived column for  replacing null values with datatype into value 0 , varchar into '$$'. Now I would like to replace with data type Varbinary.Can any one suggest replacement with which value.

I am passing the '0' for datatype int and '$$' for datatype varchar. can any one suggest for datatype varbinary what value I have to pass?

View Complete Post

More Related Resource Links

Error converting data type varchar to numeric.


I create a form to record data using ASP.NET and SQL database...
when i run the project show error "Error converting data type varchar to numeric"

what's the problem actually.i try to change in datatype in database..
but still showing error...


Error converting data type varchar to numeric



I am trying to execute below query.



CAST('123,456,789.00' AS DECIMAL(11,2))

"IN" operator and error converting data type varchar to numeric



I hope someone can help me figure this out please...

I have this query that returns some student data - specifically the student's names, and some classes they're enroled in. What I'm trying to do is return multiple rows using the LIKE operator.

However when I run it and plug in the parameters I get the errror - "Error converting data type varchar to numeric". This error doesn't occur when there is only ONE studentId. Only when there are multiple studentIds. I know it's to do with the string of studentIds being passed, but I'm not sure how to fix it?

Should I be using varchar as the datatype to pass the studentIds? Or do I need to cast this as something else?

Any ideas, much appreciated.



	@StudentIds varchar(8000),
	@ProfileId numeric,
	@Fname varchar(50) OUTPUT,
	@Lname varchar(50) OUTPUT,
	@ClassName varchar (100) OUTPUT,
	@ClassName2 varchar (100) OUTPUT,
	@ClassName3 varchar (100) OUTPUT,
	@ClassName4 varchar (100) OUTPUT,
	@Class numeric OUTPUT,
	@Class2 numeric OUTPUT,
	@Class3 numeric OUTPUT,
	@Class4 numeric OUTPUT


@Fname = Fname, @Lname = Lname, @ClassName = C.ClassName, @ClassName2 = C2.ClassName, @ClassName3 = C3.ClassName, @ClassName4 = C4.ClassName,

Conversion failed when converting the varchar value 'YTD' to data type int.


I have a report that I need to show the four quarters plus a Year To Date (YTD). I am getting a Conversion failed when converting the varchar value 'YTD' to data type int error and need some help. See first code & output. I used this same for a 12 month & YTD report and it works fine - see second code and output- any help is appreciated

First code

DECLARE @BD Datetime
DECLARE @ED Datetime
DECLARE @RptMonth Datetime

SET @ID = '1038'
SET @BD = '01/01/2010'
SET @ED = '12/01/2010'
SET @RptMonth = '07/01/2010'

Datasource_ID int, Actual_YTD float

Error converting data type varchar to numeric in SQL SERVER

Hi frendz..
Demo Example is...

alter FUNCTION [dbo].[Return_Example] 
@a numeric(18,4),
@b numeric(18,4)
RETURNS numeric(18,4)
@division numeric(18,4),
@reminder numeric(18,4),
@Num2 varchar(100),
@c numeric(18,4)

set @division=floor(@a/@b)
set @reminder=@a%@b
----Here output will be a concatenated
set @Num2= cast((@division +'.'+ @reminder) as numeric(10,3)) 
set @c=cast(@Num2 as numeric(18,3))


Execute :

exec Return_Example 50 ,5

Syntax error converting varchar value to a column od data type int


Hi everyone,

I'm having a problem where I've created a stored procedure with a dynamic ORDER BY clause and it's returning a syntax error:

Server: Msg 245, Level 16, State 1, Procedure usp_OrderBy, Line 4

Syntax error converting the varchar value 'Closed' to a column of data type int.

Here is the code that I'm trying to execute. It works fine if I take out the ID line:

CREATE PROCEDURE [Task_User].[usp_OrderBy]
  @orderBy varchar(100)
  SELECT ID, Status, PostedBy, Assigned, Subject
  FROM mainTable
    WHEN @orderBy = 'ID' THEN ID --When I take this out, it works
    WHEN @orderBy = 'Status' THEN Status
    WHEN @orderBy = 'PostedBy' THEN PostedBy
    WHEN @orderBy = 'Assigned' THEN Assigned
    WHEN @orderBy = 'Subject' THEN Subject
Any ideas as to what I can do to fix it?

Error converting data type varchar to numeric.


I have the trivial problem ... I can not do SQL Insert:

 SqlDataSource1.InsertCommand = "insert into TABLE (ID, number) values('" & ID.Text & "', '0,21')"

Then I get the message: System.Data.SqlClient.SqlExceptio

The type 'System.Data.Linq.DataContext' is defined in an assembly that is not referenced. You must



This is frustrating, and I don't know how to solve it.

I have a strange problem. I am adding a LinqDatasource object, and set the context:

        protected void LinqDataSource_ContextCreating(object sender, LinqDataSourceContextEventArgs e)
            e.ObjectInstance = new KaruselaDataContext(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);

Then I get this error:
The type 'System.Data.Linq.DataContext' is defined in an assembly that is not referenced. You must add a reference to assembly 'System.Data.Linq, Version=, Culture=neutral, PublicKeyToken=b77a5c561934e089'.


On web.config I already have this:
<add assembly="System.Data.Linq, Version=, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>

NOTE: The datacontext is in a different project (DLL proect) where I added a refernce to System.Data.Linq.

is there another way adding a refernce to a web project? or only though teh web.config?


XML in Yukon: New Version Showcases Native XML Type and Advanced Data Handling


The next version of Microsoft SQL Server, code-named "Yukon," represents quite a few steps forward in the evolution of XML integration. Yukon supports native storage of XML data using the XML data type, which makes it possible to run native queries on XML data using the emerging industry standard XQuery language. Data integrity of the XML data type can be enforced through schema validation and XML-based check constraints, and special indexes can be defined that help speed up queries. In addition, Yukon has the built-in ability to expose its data through Web services. This article discusses these and other XML features of Yukon.

Bob Beauchemin

MSDN Magazine February 2004

override error message (The value 'test' is not valid for Age) when input incorrect data type for in


Hello everyone,

I've tried to override error message when input incorrect data type in input field on HTML form. For example I have the model like this.

public class Person
public string FirstName {get;set;}
public int Age {get;set;}


For view, I put text input for Age to get it value.

When type some string in Age text box like 'test' and press submit button. I got this error message

The value 'xxx' is not valid for Age

However, I want to change this message and try many way. There 's nothing effect this message value.

Please could you help me to solve this problem.

WSS 3.0 Content Type - Lost data for Titles


We are running WSS 3.0 SP2 on server 2003.  Recently, we lost the "Titles" for all content types for documents.  Our system Content Type for Document is set up to require this field to be completed on all documents that is uploaded.  Our system would automatically enter in by default the name of the document in the "Title" field it no longer does this for the user.  The user must now enter in manually the "Title".  How do I get it to enter in the "Title" using the document file name automatically?

Below is the way the setting page for Site Content Type: Document looks like.  (I tried to recreate it since I could place a screen shot in the box)

Site Content Type Information
Name:  Document
Descriptiong:  Create a new document.
Parent:  Item
Group:   Document Content Types

Name, Description, and group
Advanced settings
Workflow settings
Delete this site content type

Name                 Type                     &nb

chnage data of input type with readonly attribute


I have webform where i have different input type as text controls with a readonly attribute. I have option to edit the data so through javascript i remove the readonly attribute through javascript, here's the code 
function EditBillAddress()

Errors in the back-end database access module. OLE DB reported an overflow of the data type for colu

Getting an error when processing a dimension, Errors in the back-end database access module. OLE DB reported an overflow of the data type for column 0. Errors in the OLAP storage engine: An error occurred while the 'xxxxxx' attribute of the 'Dim XXXXXX' dimension from the 'XXXXXXX' database was being processed. Content of this dimension is very similar to one that is processing no problem. The content of the dimension is one field only of type date.  The original content was imported through SSIS from a DB2 database on an iseries machine.  Thought a first the issue was because of the data contains some null values but the dimension the process OK also has some null  values.  The dimension that processes ok is also a single field of type date. Any help greatly appreciated, Roscoe

System.Security.SecurityException: Request for the permission of type 'System.Data.SqlClient.SqlCli

I have created a windows library control that accesses a local sql database I tried the following strings for connecting Dim connectionString As String = "Data Source=localhost\SQLEXPRESS;Initial Catalog=TimeSheet;Trusted_Connection = true" Dim connectionString As String = "Data Source=localhost\SQLEXPRESS;Initial Catalog=TimeSheet;Integrated Security=SSPI"   I am not running the webpage in a virtual directory but in C:\Inetpub\wwwroot\usercontrol and I have a simple index.html that tries to read from an sql db but throws the error System.Security.SecurityException: Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.   at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)   at System.Security.PermissionSet.Demand()   at System.Data.Common.DbConnectionOptions.DemandPermission()   at System.Data.SqlClient.SqlConnection.PermissionDemand()   at System.Data.SqlClient.SqlConnectionFactory.PermissionDemand(DbConnection outerConnection)   at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, etc etc  The action that failed was:DemandThe type of the first permission that failed was:System.Data.SqlClient.SqlClientPermissionThe Zone of the assembly that fa

Conversion Failed when Converting the nvarchar value 'N' to data type int.

Hi.. I am facing a strange situation. when i executing the following query it is returning the error SELECT  s.row_id as shipment,    SUM(CAST(la5.attr_value AS INT)) AS Cases     FROM    shipment s WITH(NOLOCK)    INNER JOIN shipping_shift ss WITH(NOLOCK) ON s.shipping_shift = ss.row_id    INNER JOIN shift WITH(NOLOCK) ON ss.shift_id = shift.shift_id    INNER JOIN shipment_lot sl WITH(NOLOCK) ON s.po_id = sl.po_id AND s.cust_id = sl.cust_id AND          s.so_line_no = sl.so_line_no and s.ship_date_local = sl.ship_date_local     INNER JOIN lot_attr la5 WITH(NOLOCK) ON sl.lot_no = la5.lot_no AND sl.item_id = la5.item_id AND la5.attr_id = (SELECT attr_id FROM attr WITH(NOLOCK) WHERE attr_desc = 'CaseQty' AND attr_grp = 3)  WHERE s.spare2 = 'RELEASED'       AND shift.shift_desc = 'c'   AND DAY(ss.production_date) = DAY('2010-08-09 00:00:00.000')    AND MONTH(ss.production_date) = MONTH('2010-08-09 00:00:00.000')    AND YEAR(ss.production_date) = YEAR('2010-08-09 00:00:00.000') GROUP BY s.row_id  Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the nvarchar value 'N' to data type int. The column  type of attr_value of lot_attr table is a USER D

User Defined Data Type - using SMO *** NEED HELP ! ***

Hi,Is it possible to script out the User Defined Data Types via SMO in .NET and specifically VB.Net?  I have not been able to find some examples on how to do this.  If you have some experience with this, pleasepost some examples or provide links where I can find this information.Thank you in advance.-Sydney

Data Source View Gives incorrect Error--Different Data Type

Hi, I'm creating a report model (2008 R2).  I am trying to create a relationship between two tables.  One FKs to another--same name and type (tinyint) columns.  But the Data Source View editor doesn't allow this, giving the message "...source and destination column have different data types".  But the columns are the same types.  I ahd this once before  acouple of years ago and don't remember how I got around it. Does anyong have ideas?  
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend