Error Converting Data Type Nvarchar To Bigint

Feb 25, 2008

I getting the above error can someone please help me solve it, here is the code:

public void InsertHost()
    {
        // TODO
        // - Call stored procedure to write to a log file writeToLog
        using (SqlConnection cn = new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"]))
        {
            SqlCommand cmd = new SqlCommand("writeToLog", cn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@pAction", "action");
            cmd.Parameters.AddWithValue("@pHostName", "Hostname");
            cmd.Parameters.AddWithValue("@pUserNUm", "requestorID");
            cn.Open();
            cmd.ExecuteNonQuery();
        }
    }

Here is the storedprocedure:

ALTER PROCEDURE dbo.writeToLog
(@pAction varchar(10), @pUserNUm bigint, @pHostName varchar(25))
AS INSERT INTO dbo.hostNameLog
                      (action, requestorID, HostName)
VALUES     (@pAction, @pUserNUm, @pHostName)

Here is the table:

HostName - varchar, action - varchar, requestorID - bigint

I can't seem to find the error.

View 7 Replies


ADVERTISEMENT

Transact SQL :: Converting Data Type Nvarchar To Bigint

Apr 17, 2015

This is a common error for SQL Server, but I got it in a uncommon way.I have a table called - tblIDNumber where there are two columns - IDN_Number [NVarchar(200)] and Temp [BigInt]

If I run,
SELECT  *
FROM    dbo.tblIDNumber
WHERE   IDN_IDNumberTypeStaticValue = 33
        AND IDN_Removed = 0
        AND CAST(IDN_Number AS BIGINT) = 1

SQL Server give me the error:
Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to bigint.

I first thought IDN_Number in type 33 has characters, but it doesn't, becasue the below query works!!!

UPDATE dbo.tblIDNumber
SET Temp = CAST(IDN_Number AS BIGINT)
WHERE  IDN_IDNumberTypeStaticValue = 33
        AND IDN_Removed = 0

To workaround, I ran the query,

UPDATE dbo.tblIDNumber
SET IDN_Number = '123'
WHERE  IDN_IDNumberTypeStaticValue = 33
        AND IDN_Removed = 0

and then I ran the first query, and SQL Server does NOT give me the same error - Msg 8114, Level 16, State 5, Line 1 Error converting data type nvarchar to bigint.

Second query approved there is nothing wrong from converting the value in IDN_Number to a BigInt, but the third query gave the hint that data might be the cause?????

finally, I found the root cause to be an index that the first query uses :

CREATE NONCLUSTERED INDEX [IX_tblIDNumber_Covering] ON [dbo].[tblIDNumber]
(
[IDN_Removed] ASC,
[IDNumberCode] ASC
)
INCLUDE ( [IDN_Number],
[IDN_Reference]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85) ON [PRIMARY]
GO

If I remove the index, the first query works without the error.

View 10 Replies View Related

Error Converting Data Type Varchar To Bigint.

Aug 25, 2005

I have the following query which has been giving me headaches for days :  SELECT SCCode [Service Catalog Code], FileName [File Name], FullName [Full Name], FileExtension [Extension], FileSize [Size],  Author [Author], CONVERT(VARCHAR(50),CAST(LastModified AS SMALLDATETIME), 120) [Last Modified],   CONVERT(VARCHAR(50),CAST(LastAccessed AS SMALLDATETIME), 120) [Last Accessed], TimesAccessed [Downloads],  UploadedBy [Uploaded By], CONVERT(VARCHAR(50),CAST(UploadedAt AS SMALLDATETIME), 120) [Uploaded At]  FROM #TempTable  WHERE id > @firstitem AND id < @lastitem  ORDER BY   CASE  WHEN @sortcolumn = 'Service Catalog Code' THEN SCCode    WHEN @sortcolumn = 'File Name' THEN FileName    WHEN @sortcolumn = 'Extension' THEN FileExtension    WHEN @sortcolumn = 'Size' THEN FileSize    WHEN @sortcolumn = 'Author' THEN Author    WHEN @sortcolumn = 'Last Modified' THEN CONVERT(VARCHAR(50),CAST(LastModified AS SMALLDATETIME), 120)    WHEN @sortcolumn = 'Last Accessed' THEN CONVERT(VARCHAR(50),CAST(LastAccessed AS SMALLDATETIME), 120)    WHEN @sortcolumn = 'Downloads' THEN TimesAccessed    WHEN @sortcolumn = 'Uploaded By' THEN UploadedBy    WHEN @sortcolumn = 'Uploaded At' THEN CONVERT(VARCHAR(50),CAST(UploadedAt AS SMALLDATETIME), 120)   END ASCWhen my @sortcolumn parameter is either FileSize (BIGINT) or TimesAccessed (BIGINT) then the query returns the data without any problem. However, if I use a different @sortcolumn value such as "Author" then I keep getting "Error converting data type varchar to bigint."A solution that seems to work is to drop CASE and use lots of IF statements instead. I don't prefer to do this because it requires me to repeat the same select statement for each and every possible @sortcolumn value!Does anyone know how to solve this? Help!

View 2 Replies View Related

Error Converting Data Type Varchar To Bigint

Oct 18, 2013

i have this data:

BookedIDBooked_Date
1141996362013-01-09 14:55:21.740

would like to get the output into txt format with delimited in between columns.

i tried this but getting this error: Error converting data type varchar to bigint.

select BookedID+'|'+Booked_Date from tableA

View 5 Replies View Related

Error Converting Data Type Varchar To Bigint

Oct 5, 2007

I have designed a SP where i need to update all the records for a table where ErrorId is not equal to the ones provided.In this stored procedure i am parsing and all the errorids delimited by ',' into a varchar variable which i would be using for updating the table.On the second last line i get the error mentioned in the subject line.any help would be appreciated.



ALTER PROCEDURE [dbo].[sp_ParseAndUpdateDetails]

@NozzleID int,

@ParserString varchar(MAX)

AS

BEGIN



DECLARE @NextPos int

DECLARE @LoopCond tinyint

DECLARE @PreviousPos int

DECLARE @FlgFirst bit

DECLARE @QueryCondition varchar(MAX)

SET @LoopCond=1

SET @NextPos =0

SET @FlgFirst=0

SET @QueryCondition=''

WHILE (@LoopCond=1)



BEGIN

--Retrieving the Position of the delimiter

SET @NextPos =@NextPos + 1

SET @NextPos = CHARINDEX(',',@ParserString, @NextPos)



--Retreiving the last substring

IF(@NextPos=0)

BEGIN

PRINT SUBSTRING(@ParserString,@PreviousPos + 1,(LEN(@ParserString)+1)- @PreviousPos)

SET @QueryCondition= @QueryCondition + ' AND ErrorId <> ' + CAST(SUBSTRING(@ParserString,@PreviousPos + 1,(LEN(@ParserString)+1)- @PreviousPos) AS bigint)

SET @PreviousPos = @NextPos

BREAK

END



--Retrieving the individual substrings

If @FlgFirst=0

--Retreiving the first substring

BEGIN

SET @FlgFirst=1

PRINT SUBSTRING(@ParserString,1, @NextPos-1)

SET @QueryCondition= @QueryCondition + CAST(SUBSTRING(@ParserString,1, @NextPos-1) AS bigint)

SET @PreviousPos = @NextPos

END

ELSE

--Retreiving the internmediate substrings

BEGIN

PRINT SUBSTRING(@ParserString,@PreviousPos + 1,(@NextPos-1)-@PreviousPos)

SET @QueryCondition= @QueryCondition + ' AND ErrorId <> ' + CAST(SUBSTRING(@ParserString,@PreviousPos + 1,(@NextPos-1)-@PreviousPos) AS bigint)

SET @PreviousPos = @NextPos

END

END

print 'ErrorId <>' + @QueryCondition

UPDATE [ESMS2_DBMS].[dbo].[ErrorDetails]

SET ErrorRectifyDateTime=GETDATE()

WHERE (NozzleId = @NozzleId) AND (ErrorRectifyDateTime IS NULL) AND (ErrorId <> @QueryCondition)

END

View 8 Replies View Related

Arithmetic Overflow Error Converting Expression To Data Type Bigint

Jan 9, 2007

I am attempting to setup a replication from SQL Server 2005 that will be read by SQL Server Compact Edition (beta). I'm having trouble getting the Publication Wizard to create the Publication. Sample table definition that I'm replicating:

USE dbPSMAssist_Development;
CREATE TABLE corporations (
id NUMERIC(19,0) IDENTITY(1964,1) NOT NULL PRIMARY KEY,
idWas NUMERIC(19,0) DEFAULT 0,
logIsActive BIT DEFAULT 1,
vchNmCorp VARCHAR(75) NOT NULL,

vchStrtAddr1 VARCHAR(60) NOT NULL,
vchNmCity VARCHAR(50) NOT NULL,
vchNmState VARCHAR(2) NOT NULL,
vchPostalCode VARCHAR(10) NOT NULL,
vchPhnPrimary VARCHAR(16) NOT NULL,
);
CREATE INDEX ix_corporations_nm ON corporations(vchNmCorp, id);
GO


When the wizard gets to the step where it is creating the publication, I get the following error message:


Arithmetic overflow error converting expression to data type bigint. Changed database context to 'dbPSMAssist_Development'. (Microsoft SQL Server, Error: 8115).

I can find no information on what this error is or why I am receiving the error. Any ideas on how to fix would be appreciated.

Thanks in advance ...

David L. Collison

Any day above ground is a good day.

View 3 Replies View Related

Strange Problem: Error Converting Data Type Varchar To Bigint.

Nov 30, 2007



The strange in this problem is that i got this problem suddenly and before all was running without any error.

The script that cause the error is in the trigger for insertion on a table, there is somewhere where i convert from varchar to bigint, but all seems fine to me i printed the varchar variable and the conversion looks valid, also i included several print statement in the script for debugging purposes and i feel everything is right and there shouldn't be any error.

I don't think anything changed in my db, so i wonder where this problem is comming from ?
this is the code:



Code Block
-- Private Sending start
declare @tmpmsg nvarchar(200)
set @tmpmsg = ltrim(rtrim(@message)) -- Remove leading and trailing blanks
if (left(@tmpmsg,4))='خاص' and len(@tmpmsg)>6
begin
declare @msg_error nvarchar(70)
declare @msg_error1 nvarchar(70)
declare @msg_error2 nvarchar(70)
declare @msg_error3 nvarchar(70)
declare @sender_id bigint
set @msg_error1 = 'الرجاء إرسال كلمة خاص متبوعة برقم ملÙ? الشخص المطلوب ثم الرسالة الشخصية'
set @msg_error2 = 'الرجاء تسجيل ملÙ?Ùƒ قبل إستخدام خدمة الرسائل الخاصة'
set @msg_error3 = 'إن ملÙ? الشخص المطلوب غير موجود'
-- The message should not go to chat filtration
update received_in set sent = 3 where reqid = @id
-- if the sender profile doesn't exist, send him a message indicating that.
SELECT @sender_id = profile_id FROM profiles where msisdn=@msisdn
if @sender_id is not null
begin
set @tmpmsg = ltrim(right(@tmpmsg, len(@tmpmsg)-3))
-- Now the message looks like '12345 this is a test message'
declare @profileid bigint
declare @strprofileid nvarchar(50)

declare @posSpace int
set @posSpace = charindex(' ', @tmpmsg)

if @posSpace = 0 -- Not found (the message format is like this 'pri 3432434 ' or 'pri dfsdffsd '
begin
set @msg_error = @msg_error1
-- Insert the message into the filtration screen to be fixed by a filtrator, the recipient_id will be set to 0
insert into mobile_chat (sender_id, recipient_id, message, datein, timein)
Values (@sender_id, 0, @tmpmsg, @thedate, @thetime)
end
else
begin
set @strprofileid = left(@tmpmsg, @posSpace-1)
-- first char separating profileid and util message
if isnumeric(@strprofileid) = 1
begin
set @profileid = @strprofileid
declare @utilmsg nvarchar(200)
set @utilmsg = ltrim(right(@tmpmsg, len(@tmpmsg)-@posSpace))

-- Right message format
print 'Profile id=' + ltrim(str(@profileid))
print 'Message=' + @utilmsg
declare @recmsisdn varchar(16)
select @recmsisdn = msisdn From profiles where profile_id=@profileid
-- if the receiver profile doens't exist, send him a message indicating that
if @recmsisdn is not null
begin
-- All fine: msg format, sender and receiver
if left(@recmsisdn,3)='000'
Insert into Inbox (sender_id, recipient_id, message, datein, timein)
Values (@sender_id, @profileid, @utilmsg, @thedate, @thetime)
else
insert into mobile_chat (sender_id, recipient_id, message, datein, timein)
Values (@sender_id, @profileid, @utilmsg, @thedate, @thetime)
end
else
begin
-- Insert the message into the filtration screen to be fixed by a filtrator, the recipient_id will be set to 0
insert into mobile_chat (sender_id, recipient_id, message, datein, timein)
Values (@sender_id, 0, @tmpmsg, @thedate, @thetime)
set @msg_error = @msg_error3
end
end
else
begin
set @msg_error = @msg_error1
end
end
end
else
-- The sender profile is not registered with us
set @msg_error = @msg_error2
if @msg_error is not null
begin
print @msg_error
if left(@msisdn, 3) = '966' -- We can only send replies to KSA mobiles
INSERT INTO cgi_sms_services.dbo.cgi_sms_in(msisdn, sms_code, message, reqport, smstype)
VALUES (@msisdn, '1000000002', @msg_error, 86367, 'wcsms')
end
end -- Private Sending End

View 3 Replies View Related

Error Converting Data Type Nvarchar To Int.

Mar 27, 2008

Hi i keep getting this error when i search based on Team name (dropdownlist) or coach name(textbox). However it works when i make the search based on the region id(dropdownlist) here is my stored procedure;ALTER PROCEDURE [dbo].[stream_FindTeam]
-- Add the parameters for the stored procedure here
@coachName varchar(100),
@TeamName varchar(100),@regionID INT
 
AS
SELECT TeamID, coachName FROM Teams WHERE coachName LIKE @coachName OR TeamName= @TeamNameOR regionID = @regionID;
 Here is my code;SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["streamConnectionString"].ConnectionString);
conn.Open();SqlCommand command = new SqlCommand("stream_FindTeam", conn);
command.CommandType = CommandType.StoredProcedure;command.Parameters.AddWithValue("@coachName", coachName.Text);
command.Parameters.AddWithValue("@TeamName", TeamList.SelectedValue);command.Parameters.AddWithValue("@regionID", Region.SelectedValue);SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
DataList1.DataSource = reader;
DataList1.DataBind();
conn.Close();

View 13 Replies View Related

Error Converting Data Type Nvarchar To Int

Feb 28, 2008

Hi,


I get "Error converting data type nvarchar to int." It is some kind of SQL exception. This is the code in my application.

SqlConnection sqlConnection1 = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);

SqlCommand cmd = new SqlCommand();



Object start;



string activityID = Session["activitygroupID"].ToString();

cmd.Parameters.AddWithValue("@ActivityGroupID", activityID);

cmd.CommandText = "get_startdate";

cmd.CommandType = CommandType.StoredProcedure;

cmd.Connection = sqlConnection1;

sqlConnection1.Open();



SqlDataReader reader = cmd.ExecuteReader(); //The code stops here!!!

start = reader["StartDate"];

sqlConnection1.Close();


The stored procedure on the SQL-server looks like so. StartDate is of DateTime type.


ALTER PROCEDURE [dbo].[get_startdate]

-- Add the parameters for the stored procedure here

@ActivityGroupID int

AS

BEGIN

SELECT StartDate

FROM dbo.CurrentGroup

WHERE ActivityGroupID = @ActivityGroupID

END


I appreciate any help!

View 4 Replies View Related

Error Converting Data Type Nvarchar To Datetime

Mar 23, 2006

Hi
I have an insert stored procedure with a parameter @eventDate date type date time.  I have written a web form that takes the textbox.text value and assigns to the @eventDate parameter using a querystring.  Howevr when I run the application I get a SQL Exception error converting data type nvarchar to datetime.
I have tried dssqlInsertEvent.InsertParameters.add("EventDate", convert.todatetime(txtEventDate.text)); wbut received another error.
Any assistance would be greatly appreciated.

View 4 Replies View Related

Error Converting Data Type Nvarchar To Numeric

Dec 12, 2014

I am using MS SQL Server 2008R2 along with VB 2010.The first question is: why is it even trying to convert anything to numeric? I have NO numeric data types. And I don't have any nvarchar data types either. I'm very confused.Doesn't nchar include any and all characters, in any combination? Should I change everything to text data type? Maybe something else? Some values are going to be blank. The Lab/Source Lots have numbers, letters and dashes.

My stored procedure:

ALTER PROCEDURE dbo.MChemsInsert
(
@LabLot nchar(10),
@Chem nchar(50),
@Source nchar(50),
@SourceLot nchar(10),

[code]....

View 2 Replies View Related

Error Converting Data Type Nvarchar To Datetime

Jun 2, 2008

Hi,

I'm pretty desperat after trying solving the above problem in several days. I hope somone here can help me :-)

I have this stored procedure. When I try to Execute the storedprocedure where I have a date as parameter I get the above error.

I checked (many times), that the input parameter is a datetime format (30-05-2008) and also I checked the tabel and it is datetime format. I checked the table to see if it was english datetime format - but all the datetimes that are listet are listed in danish datetime format DD-MM-YYYY.

In the stored procedure I try this:


fProjectSkuTimeStamp >= @ReducedTime

I can't figure out where it gets that nvarchar from?

My stored procedure look like this:


set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

set NOCOUNT ON

go

ALTER PROCEDURE [dbo].[GetProjectSkuList]

@fProjectFId AS INTEGER = null, --ProjektId

@ReducedTime AS DATETIME --Tidsrum fra



AS

BEGIN

SELECT tProjectSku.fProjectSkuPId, tProjectSku.fCompanyFId, tProjectSku.fProjectFId,

tProjectSku.fProjectSkuDate,

tProjectSku.fOurContactFId, tProjectSku.fOurContactF2Id, tProjectSku.fOrderFId,

tProjectSku.fProjectSkuTimeStamp,

tProjectSku.fProjectSkuNote1, tProjectSku.fProjectSkuNote2, tProjectSku.fContactFId,

tProjectSku.fStoreTransactionsFId,

tProjectSku.fProjectSkuSalesPrice, tCompany.fCompanyName, tProject.fProjectName,

tOurContact.fOurContactFirstName + ' ' + tOurContact.fOurContactLastName AS CreatedBy,

tOurContact_1.fOurContactFirstName + ' ' + tOurContact_1.fOurContactLastName AS Responsible,

tContact.fContactFirstName + ' ' + tContact.fContactLastName AS ContactFullName,

tStoreTransactions.fStoreTransactionsCostPrice,

tStoreTransactions.fStoreTransactionsSerialNo





FROM tOurContact AS tOurContact_1 RIGHT OUTER JOIN

tContact RIGHT OUTER JOIN

tProjectSku LEFT OUTER JOIN

tStoreTransactions ON tProjectSku.fStoreTransactionsFId = tStoreTransactions.fStoreTransactionsPId ON

tContact.fContactPId = tProjectSku.fContactFId LEFT OUTER JOIN

tOrder ON tProjectSku.fOrderFId = tOrder.fOrderPId ON tOurContact_1.fOurContactPId = tProjectSku.fOurContactF2Id LEFT OUTER JOIN

tOurContact ON tProjectSku.fOurContactFId = tOurContact.fOurContactPId LEFT OUTER JOIN

tProject ON tProjectSku.fProjectFId = tProject.fProjectPId LEFT OUTER JOIN

tCompany ON tProjectSku.fCompanyFId = tCompany.fCompanyPId



WHERE

(tProjectSku.fProjectFId = @fProjectFId) AND

--fProjectSkuTimeStamp >= CONVERT(datetime, @ReducedTime, 120)

fProjectSkuTimeStamp >= @ReducedTime

ORDER BY tProjectSku.fProjectSkuPId DESC

END


Kind regards,

simsen :-)

View 7 Replies View Related

Transact SQL :: Error Converting Data Type Nvarchar To Float

Sep 24, 2015

In the following code I want to compare 2 values: AccessVal and SQLVal. The values are stored as nvarchars, so I'm isolating the numeric values in a subquery. Notice I'm only selecting 1 row. The commented line, where I compare the values, is throwing the error.

SELECT QA_AutoID, AccessVal, SQLVal
,ROUND(ABS(CONVERT(float, AccessVal,1)),0) as AccessFloat
,ROUND(ABS(CONVERT(float, SQLVal,1)),0) as SQLFloat
FROM QA
WHERE QA_AutoID in (
SELECT TOP 1 QA_AutoID
FROM QA
WHERE ISNUMERIC(SQLVal) = 1 AND ISNUMERIC(AccessVal) = 1
)
--AND ROUND(ABS(CONVERT(float, AccessVal,1)),0) <> ROUND(ABS(CONVERT(float, SQLVal,1)),0)
ORDER BY ROUND(ABS(CONVERT(float, AccessVal,1)),0) DESC
,ROUND(ABS(CONVERT(float, SQLVal,1)),0) DESC

Here is the output with the comparison commented out...

Here's what I get with the comparison line activated:

I've tried converting to numeric, int and bigint instead of float. I've tried CAST instead of CONVERT. Nothing works.

View 13 Replies View Related

Getting Server Error Syntax Error Converting The Nvarchar Value 'Sonoma' To A Column Of Data Type Int.

Apr 20, 2007

Hi, all
I'm getting this error at runtime when my page tries to populate a datagrid. Here's the relevant code.
First, the user selects his choice from a dropdownlist, populated with a sqldatasource control on the aspx side:<asp:SqlDataSource ID="sqlDataSourceCompany" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT [PayrollCompanyID], [DisplayName] FROM [rsrc_PayrollCompany] ORDER BY [DisplayName]">
</asp:SqlDataSource>
 And the dropdown list's code:<asp:DropDownList ID="ddlPayrollCompany" runat="server" AutoPostBack="True" DataSourceID="sqlDataSourcePayrollCompany"
DataTextField="DisplayName" DataValueField="PayrollCompanyID">
</asp:DropDownList>
Then, I use the selectedindexchanged event to bind the data to the datagrid. Here's that code:
 1 Sub BindData()
2
3 Dim ds As New DataSet
4 Dim sda As SqlClient.SqlDataAdapter
5 Dim strSQL As String
6 Dim strCon As String
7
8 strSQL = "SELECT [SocialSecurityNumber], [Prefix], [FirstName], [LastName], [HireDate], [PayrollCostPercent], " & _
9 "[Phone], [BadgeNumber], [IsSupervisor], [SupervisorID], [IsUser], [IsScout] FROM [rsrc_Personnel] " & _
10 "WHERE ([PayrollCompanyID] = @PayrollCompanyID)"
11
12 strCon = "Data Source=DATASOURCE;Initial Catalog=DATABASE;User ID=USERID;Password=PASSWORD"
13
14 sda = New SqlClient.SqlDataAdapter(strSQL, strCon)
15
16 sda.SelectCommand.Parameters.Add(New SqlClient.SqlParameter("@PayrollCompanyID", Me.ddlPayrollCompany.SelectedItem.ToString()))
17
18 sda.Fill(ds, "rsrc_Personnel")
19
20 dgPersonnel.DataSource = ds.Tables("rsrc_Personnel")
21 dgPersonnel.DataBind()
22
23 End Sub
24

 
I'm assuming my problem lies in line 16 of the above code. I've tried SelectedItemIndex, SelectedItemValue too and get errors for those, as well.
What am I missing?
Thanks for anyone's help!
Cappela07

View 2 Replies View Related

Reporting Services :: Error Converting Data Type Nvarchar To Date

Apr 29, 2015

I am getting the above error when trying to add a dataset to my report. Here is my stored procedure below.

USE [HSIU_TEST]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [ssrs].[spKEMHWaitTimeCaseList]

[Code] .....

View 5 Replies View Related

Error Message Conversion Failed When Converting The Nvarchar Value To Data Type Int??

Jan 19, 2007

Hi,

I can't seem to fix the following error in my stored procedure.

Error Message: Conversion failed when converting the nvarchar value '1007-001' to data type int.

The line of code in my stored procedure that seems to be the problem is the following:

CASE WHEN [Order Details].[Job No] IS NULL THEN [Orders].[Order No] ELSE [Order Details].[Job No] END

Order No has a data type of INT and Job No has a data type NVARCHAR(8). In the above case statement i'm not trying to convert anything but just display a column depending on the out come of the case statement. If anyone knows how to get around this error you help would be very welcome.

View 2 Replies View Related

Error Conversion Failed When Converting The Nvarchar Value 'xxxxxx' To Data Type Int, For Return Value

Aug 24, 2007

ALTER procedure [dbo].[findConsultantMail]

(



@PerID numeric(18,0),

@perMail nvarchar(100) OUTPUT



)

as

SELECT @perMail=PerMail FROM Personel

WHERE (PerID =@PerID)

return @perMail


I want to get Email address from sql database.
But whenever I executed stored procedure I get an error message
"Conversion failed when converting the nvarchar value 'xxxxxx@xxxxxx' to data type int"
If I want some numeric ID it is worked.

I also change my SP like this but results same.


ALTER procedure [dbo].[findConsultantMail]

(



@PerID nvarchar(18),

@perMail nvarchar(100) OUTPUT



)

as

SELECT @perMail=PerMail FROM Personel

WHERE (PerID =cast(@PerID as numeric(18,0)))

return cast(@perMail as nvarchar(100))



How can I get a string value form stored procedure.

View 4 Replies View Related

SQL Server 2008 :: Arithmetic Overflow Error Converting Nvarchar To Data Type Numeric

Oct 13, 2012

When I run the following sql query:

"update table set price = price * 1.1 "..

I get the following error : "Msg 8115, Level 16, State 8, Line 1.. Arithmetic overflow error converting nvarchar to data type numeric. The statement has been terminated."

The table is set to nvarchar, and i am just trying to make the prices go up 10%.

View 9 Replies View Related

Converting Nvarchar Data Type To Money?

May 11, 2000

I am trying to do some amount calculations.
The amount was declared as nvarchar data type.

I did the following coding. I want to get 2 places right of decimal
but am getting only one place right of decimal.

SELECT

(CAST(EQ_TotQuoteAmnt AS Float(7,2)) -
CAST(EQ_InsFee AS Float(7,2)) - 150) as Inforce_Premium

FROM Quoted

Can someone please help me with the syntax?

Thanks in advance.

M. Khan

View 1 Replies View Related

Conversion Failed When Converting The Nvarchar Value '3,4' To Data Type Int.

Jun 1, 2008

i am getting this error when passing multiple checkbox values to next page..
 Error is Conversion failed when converting the nvarchar value '3,4' to data type int.
my code is below
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:mobiletimesConnectionString %>"
SelectCommand = "SELECT * FROM [device] WHERE [dev_id] IN (@chk1)">
<SelectParameters>
<asp:FormParameter FormField="chk1" Name="chk1" />
</SelectParameters>
</asp:SqlDataSource><asp:DataList ID="DataList1" runat="server" DataSourceID="SqlDataSource1"
BackColor="White" BorderColor="#CC9966" BorderStyle="None" BorderWidth="1px"
CellPadding="4" GridLines="Both">
<FooterStyle BackColor="#FFFFCC" ForeColor="#330099" />
<ItemStyle BackColor="White" ForeColor="#330099" />
<SelectedItemStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="#663399" />
<HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="#FFFFCC" />
<ItemTemplate><div class="overviewpdamain" >
<div class="overviewpdadevice">
<center>
<b><%#Eval("dev_name")%></b><br />
<b>
</b>
</center></div>
</div></ItemTemplate>
</asp:DataList>

View 6 Replies View Related

Problem With Converting The Nvarchar Value 'xxxxxID' To A Column Of Data Type Int.

Jan 23, 2007

Hi,
I have a webform with a drop-down listbox, binded to a table and a GridView, binded to another table. My goal is when I select a value (country in my case) in the drop-down listbox to change what is shown in the GridView, based on the ID that I get from the drop-down listbox.
Here is the code (the important parts...):
Select a Country : <asp:DropDownList ID="CountriesDropDownList" runat="server" DataSourceID="SqlDataSource2" DataTextField="Name" DataValueField="CountryID" Width="220px" AutoPostBack="True">
</asp:DropDownList>
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:MyConnectionString %>"
SelectCommand="SELECT [CountryID], [Name] FROM [DS_Country]">
</asp:SqlDataSource>
<asp:GridView ID="ProvincesGridView" runat="server" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False" DataKeyNames="ProvinceID,CountryID,Name,ShortName,Description" DataSourceID="SqlDataSource1" BackColor="White" BorderColor="#E7E7FF" BorderStyle="None" BorderWidth="1px" CellPadding="3" HorizontalAlign="Left" >
<Columns>
..............................
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DineSelectConnectionString %>"
DeleteCommand="DELETE FROM DS_Province WHERE ProvinceID=@ProvinceID"
SelectCommand="SELECT ProvinceID,CountryID,Name,ShortName,Description FROM DS_Province WHERE CountryID=@ddlCountryID"
UpdateCommand="UPDATE DS_Province SET CountryID=@CountryID,Name=@Name,ShortName=@ShortName,Description=@Description WHERE ProvinceID=@ddlCountryID"
InsertCommand="INSERT INTO DS_Province (CountryID,Name,ShortName,Description) VALUES (@CountryID,@Name,@ShortName,@Description)">
<SelectParameters>
<asp:ControlParameter Name="ddlCountryID" ControlID="CountriesDropDownList" PropertyName="DataValueField" />
</SelectParameters>
</asp:SqlDataSource>
 
When I run it I get this error "Syntax error converting the nvarchar value 'CountryID' to a column of data type int.". If I specify the Type="Int" in <asp:ControlParameter>..</> I get "Input string was not in a correct format. "
This is supposed to be easy, but I can't make it work ! Any suggestions will be more than welcome.
Thanks.

View 10 Replies View Related

Conversion Failed When Converting The Nvarchar Value Xxxxxxxx To Data Type Int

Sep 4, 2007

I am using Sql server 2005, Asp.net 2.0. In my application i have to store a mobile number in database, and in table it is declared as nvarchar(50) (datatype). In stored procedure as it is nvarchar(50). In my code , stored procedure is called in a function. In that while executing project error is coming as (cmd.ExecuteNonQuery)
Conversion failed when converting the nvarchar value xxxxxxxxx to data type int.
Kindly give your suggestions to solve this problem.

View 10 Replies View Related

SQL Server 2014 :: Converting Column Of Type Nvarchar To Datetime

Aug 19, 2015

I am trying to convert a column (nvarchar) to date time.

Here is an example of some values:

20110905
20110901
20111003

As expected, I received the following error: 'Conversion failed when converting date and/or time from character string.'

However, I am unsure how I should approach updating the whole column. I would like the format to be '103'.

View 9 Replies View Related

Transact SQL :: Error Converting VARCHAR To BIGINT When Executing Query

Jun 4, 2015

DECLARE @i BIGINT
SET @i = 20150315
DECLARE @S VARCHAR(MAX)
SET @S = ''
SELECT @S = @S + '
DECLARE @Count BIGINT
SET @Count = '+@i+' + 1

SELECT @Count'
EXEC(@S)

I am trying to execute the above query but it is throwing me an error.

Msg 8114, Level 16, State 5, Line 6
Error converting data type varchar to bigint.

View 16 Replies View Related

Error = Arithmetic Overflow Error Converting Expression To Data Type Smalldatetim

Mar 22, 2007

  $exception {"Arithmetic overflow error converting expression to data type smalldatetime.
The statement has been terminated."} System.Exception {System.Data.SqlClient.SqlException}
occurs
here is my code
protected void EmailSubmitBtn_Click(object sender, EventArgs e)
{
SqlDataSource NewsletterSqlDataSource = new SqlDataSource();
NewsletterSqlDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["NewsletterConnectionString"].ToString();
 
//Text version
NewsletterSqlDataSource.InsertCommandType = SqlDataSourceCommandType.Text;
NewsletterSqlDataSource.InsertCommand = "INSERT INTO NewsLetter (EmailAddress, IPAddress, DateTimeStamp) VALUES (@EmailAddress, @IPAddress, @DateTimeStamp)";
 
//storeprocedure version
//NewsletterSqlDataSource.InsertCommandType = SqlDataSourceCommandType.StoredProcedure;
//NewsletterSqlDataSource.InsertCommand = "EmailInsert";
NewsletterSqlDataSource.InsertParameters.Add("EmailAddress", EmailTb.Text);
NewsletterSqlDataSource.InsertParameters.Add("IPAddress", Request.UserHostAddress.ToString());
NewsletterSqlDataSource.InsertParameters.Add("DateTimeStamp", DateTime.Now.ToString());
int rowsAffected = 0;
try
{
rowsAffected = NewsletterSqlDataSource.Insert();
}
catch (Exception ex)
{
Server.Transfer("NewsletterProblem.aspx");
}
finally
{
NewsletterSqlDataSource = null;
}
if (rowsAffected != 1)
{
Server.Transfer("NewsletterProblem.aspx");
}
else
{
Server.Transfer("NewsletterSuccess.aspx");
}
 

View 3 Replies View Related

Stored Proc Error: Error Converting Character String To Smalldatetime Data Type

Feb 12, 2005

I am trying to create a page that adds users to a MS SQL database. In doing so, I have run into a couple errors that I can't seem to get past. I am hoping that I could get some assistance with them.

Error from SQL Debug:
---
Server: Msg 295, Level 16, State 3, Procedure AdminAddUser, Line 65
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting character string to smalldatetime data type.
---

Error from page execution:
---
Exception Details: System.Data.OleDb.OleDbException: Error converting data type varchar to numeric.

Source Error:

Line 77: cmd.Parameters.Add( "@zip", OleDbType.VarChar, 100 ).Value = Request.Form("userZip")
Line 78:
Line 79: cmd.ExecuteNonQuery()
---


Below is what I currently have for my stored procedure and the pertinent code from the page itself.

Stored Procedure:
---
CREATE PROCEDURE dbo.AdminAddUser( @username varchar(100),
@password varchar(100),
@email varchar(100),
@acct_type varchar(100),
@realname varchar(100),
@billname varchar(100),
@addr1 varchar(100),
@addr2 varchar(100),
@city varchar(100),
@state varchar(100),
@country varchar(100),
@zip varchar(100),
@memo varchar(100) )
AS
BEGIN TRAN

--
-- Returns 1 if successful
-- 2 if username already exists
-- 0 if there was an error adding the user
--

SET NOCOUNT ON
DECLARE @error int, @rowcount int

--
-- Make sure that there isn't already a user with this username
--

SELECT userID
FROM users
WHERE username=@username

SELECT @error = @@ERROR, @rowcount = @@ROWCOUNT

IF @error <> 0 OR @rowcount > 0 BEGIN
ROLLBACK TRAN
RETURN 2
END

--
-- Set expiration date
--

DECLARE @expDate AS smalldatetime

IF @acct_type = "new_1yr" BEGIN
SET @expDate = DATEADD( yyyy, 1, GETDATE() )
END

IF @acct_type = "new_life" BEGIN
SET @expDate = DATEADD( yyyy, 40, GETDATE() )
END

DECLARE @paidCopies AS decimal
SET @paidCopies = 5


--
-- Add this user to the database
--

IF @acct_type <> "new" BEGIN

INSERT INTO users (userName, userPassword, userEmail, userJoinDate,
userPaidCopies, userExpirationDate, userLastPaidDate,
userBname, userRealName, userAddr1, userAddr2, userCity,
userState, userCountry, userZip, userMemo )
VALUES (@username, @password, @email, GETDATE(), @realname, @billname,
@paidCopies, @expDate, GETDATE(), @addr1, @addr2, @city, @state, @country, @zip, @memo )

SELECT @error = @@ERROR, @rowcount = @@ROWCOUNT

IF @error <> 0 OR @rowcount < 1 BEGIN
ROLLBACK TRAN
RETURN 0
END

END

IF @acct_type = "new" BEGIN

INSERT INTO users (userName, userPassword, userEmail, userJoinDate,
userBname, userRealName, userAddr1, userAddr2, userCity,
userState, userCountry, userZip, userMemo )
VALUES (@username, @password, @email, GETDATE(), @realname, @billname,
@addr1, @addr2, @city, @state, @country, @zip, @memo )

SELECT @error = @@ERROR, @rowcount = @@ROWCOUNT

IF @error <> 0 OR @rowcount < 1 BEGIN
ROLLBACK TRAN
RETURN 0
END

END



COMMIT TRAN
RETURN 1
GO
---


Page Code:
---
Sub AddUser(Sender as Object, e as EventArgs)

Dim db_conn_str As String
Dim db_conn As OleDbConnection
Dim resultAs Int32
Dim cmdAs OleDbCommand


db_conn_str = "Provider=SQLOLEDB;Data Source=localhost;Initial Catalog=xxxxx; User ID=xxxxx; PWD=xxxxx;"
db_conn = New OleDbConnection( db_conn_str )
db_conn.Open()

cmd = new OleDbCommand( "AdminAddUser", db_conn )
cmd.CommandType = CommandType.StoredProcedure

cmd.Parameters.Add( "result", OleDbType.Integer ).Direction = ParameterDirection.ReturnValue

cmd.Parameters.Add( "@username", OleDbType.VarChar, 100 ).Value = Request.Form("userName")
cmd.Parameters.Add( "@password", OleDbType.VarChar, 100 ).Value = Request.Form("userPass")
cmd.Parameters.Add( "@email", OleDbType.VarChar, 100 ).Value = Request.Form("userEmail")
cmd.Parameters.Add( "@acct_type", OleDbType.VarChar, 100 ).Value = Request.Form("userEmail")
cmd.Parameters.Add( "@realname", OleDbType.VarChar, 100 ).Value = Request.Form("userRealName")
cmd.Parameters.Add( "@billname", OleDbType.VarChar, 100 ).Value = Request.Form("userBname")
cmd.Parameters.Add( "@addr1", OleDbType.VarChar, 100 ).Value = Request.Form("userAddr1")
cmd.Parameters.Add( "@addr2", OleDbType.VarChar, 100 ).Value = Request.Form("userAddr2")
cmd.Parameters.Add( "@city", OleDbType.VarChar, 100 ).Value = Request.Form("userCity")
cmd.Parameters.Add( "@state", OleDbType.VarChar, 100 ).Value = Request.Form("userState")
cmd.Parameters.Add( "@country", OleDbType.VarChar, 100 ).Value = Request.Form("userCountry")
cmd.Parameters.Add( "@memo", OleDbType.VarChar, 100 ).Value = Request.Form("userMemo")
cmd.Parameters.Add( "@zip", OleDbType.VarChar, 100 ).Value = Request.Form("userZip")

cmd.ExecuteNonQuery()

(...)

---

View 1 Replies View Related

Error Converting Data Type Varchar To Int.

Oct 9, 2007

can anyone see as to why I would get this error with the following SP?
 ALTER PROCEDURE [dbo].[SP]
@ID int = 0,
@emailFrom VARCHAR(50) = Null,
@emailDate VARCHAR(50) = Null,
@emailSubj VARCHAR(50) = Null,
@emailTxtBody VARCHAR(1000) = Null,
@emailHtmlBody VARCHAR(1000) = Null

AS
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @Notes VARCHAR (8000)
DECLARE @TicketID INT
DECLARE @emailBody VARCHAR (1000)
DECLARE @Length Int

SET @Notes = ''
SET @Length = LEN(@emailSubj)

-- insert a new entry
INSERT INTO PopEmail
( emailFrom, emailDate, emailSubj, emailTxtBody, emailHtmlBody )
VALUES
( @emailFrom, @emailDate, @emailSubj, @emailTxtBody, @emailHtmlBody )
-- get the new ID
SET @ID = @@identity

If ( @ID <> 0 ) AND ( ISNUMERIC(@emailSubj) = 1 )

Begin
IF @emailTxtBody IS NULL
BEGIN
Set @emailBody = @emailHtmlBody
PRINT '@emailHtmlBody: ' + @emailBody
END

ELSE

BEGIN
SET @emailBody = @emailTxtBody
PRINT '@emailTxtBody: ' + @emailBody
END

SET @TicketID = CAST( @emailSubj AS int )
SET @Notes = @emailFrom + ', ' + @emailDate + ', ' + @emailBody
Select @ID = ID From TicketDetails Where TicketDetails.TicketID = @TicketID

Exec differentSP @ID, @TicketID, @Notes

PRINT 'Subject: [' + @emailSubj + ']'
print 'length: ' + CAST(@Length as varchar (10))
Print 'emailSubj: ' + CAST( @emailSubj AS int )
PRINT 'ID: ' + Cast( @ID as varchar ( 10 ) )
PRINT 'TicketID: ' + Cast( @TicketID AS Varchar ( 10 ) )
PRINT 'Notes: ' + @Notes
PRINT 'ID: ' + Cast( @ID as varchar ( 10 ) )
END
ELSE

BEGIN
Print 'ID: ' + CAST(@ID AS VarChar(10))
PRINT 'ISNUMERIC: ' + CAST(ISNUMERIC(@emailSubj) AS VarChar (10))
PRINT 'Subject: [' + @emailSubj + ']'
END 

View 2 Replies View Related

Error Converting Data Type Varchar To Int

Mar 11, 2000

I have a table with a column (locationID) of int data type, I want to pass a list of locationID's to a stored procedure using the IN operator (e.g. WHERE LocationID IN (list of locationID's)). What can I do to achieve this????

My stored procedure looks like this:

CREATE PROCEDURE [sp_test]
@sLocIDs varchar(30)
AS
select count(distinct TimeZoneID) As timezones,
from dimLocation
WHERE LocationID IN (@sLocIDs)

When I test it:
exec sp_test
@sLocIDs = '1,2,10'
I get "Error converting data type varchar to int"

View 1 Replies View Related

Data Access :: Arithmetic Overflow Error Converting Expression To Data Type Int

Jul 24, 2015

When I execute the below stored procedure I get the error that "Arithmetic overflow error converting expression to data type int".

USE [FileSharing]
GO
/****** Object: StoredProcedure [dbo].[xlaAFSsp_reports] Script Date: 24.07.2015 17:04:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

[Code] .....

Msg 8115, Level 16, State 2, Procedure xlaAFSsp_reports, Line 25
Arithmetic overflow error converting expression to data type int.
The statement has been terminated.
(1 row(s) affected)

View 10 Replies View Related

System.Data.SqlClient.SqlException: Error Converting Data Type Numeric To Decimal.

Aug 31, 2004

Hi There,

I'm using C# to get a value for a DOUBLE precision variable, called "Length", from a textBox using the following line:

Length = Convert.ToDouble( txtLength.Text )

I'm also using the following lines to prepare my stored procedure call:

arParms[9] = new SqlParameter("@Length", SqlDbType.Decimal, 5);
arParms[9].Value = record.Length;

My stored procedure has the following parameter definition:

@Length decimal(9,3)

My problem is that if someone types a value bigger than 999999 in the textbox he will get for sure the following error:

System.Data.SqlClient.SqlException: Error converting data type numeric to decimal.

I don't know how to either make sql or C# to truncate the value or catch the exception to automatically assign 0 to the parameter.

Please Help.
Moshe

View 1 Replies View Related

System.Data.SqlClient.SqlException: Syntax Error Converting The Varchar Value 'V' To A Column Of Data Type Int

Aug 31, 2006

 I am using  a stored procedure which returns a value of charecter datatype 'V' to the calling program.I am getting an sql exception System.Data.SqlClient.SqlException: Syntax error converting the varchar value 'V' to a column of data type inti didnot define any int datatype in my tablethis is my codeSqlCommand com = new SqlCommand("StoredProcedure4", connection);com.CommandType = CommandType.StoredProcedure;  SqlParameter p1 = com.Parameters.Add("@uname", SqlDbType.NVarChar);SqlParameter p2 = com.Parameters.Add("@opwd", SqlDbType.NVarChar);SqlParameter p3 = com.Parameters.Add("@role", SqlDbType.NVarChar);p3.Direction = ParameterDirection.ReturnValue;p1.Value = username.Text.Trim();p2.Value = password.Text.Trim();com.ExecuteReader();lblerror2.Text = (string)(com.Parameters["@role"].Value); can your figure out what is the error ? Is it a coding error or error of the databse

View 3 Replies View Related

Error Converting Data Type Varchar To Datetime.

Feb 16, 2007

Hi,
It is not exactly what I stated in the subject - It's an outcome - exception thrown while executing non-query command.
I get this exception when I try to execute my stored procedure that takes datetime as one of its parameters.
I am using dataset designer to create table adapters and build queries. Then I simply use objectdatasource component that uses one of the table adapters and bind it to for example a detailsview control.
When I run this in debug mode and trace the objects everything looks perfect including these datetime parameters. It is sql server that throws the exception. I ran the sql profiler to see what exactly is going on, and I captured the command that is sent by ADO - it's broken into several lines right in the middle of my datetime parameters... this is the source of the problem. Everything is working fine when I take this command and execute it as a single line in the sql management studio.
Is there anything about ADO that I do not know?

View 2 Replies View Related

Error Converting Data Type DBTYPE_DBTIMESTAMP To Datetime

Jul 7, 2004

I'm getting the error Error converting data type DBTYPE_DBTIMESTAMP to datetime when I try to import a date field from Oracle to SQL Server.

SELECT CAST(cancel_dt as datetime) FROM OPENQUERY(orcldb, 'SELECT cancel_dt FROM tablename WHERE id= 12345')

The date in Oracle is 19-JUN-99, but it's coming over as 06/19/0999 and it throws the error because the year 999 is invalid in SQL Server. Any ideas on why Oracle or SQL server would convert the year to 0999 instead of 1999?
This is also happening on a value 24-SEP-07 coming over as 09/24/0207

Most other date values work, so I'd really like to get in depth into the process that occurs when converting to a SQL Server datetime.

View 7 Replies View Related







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