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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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