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


ADVERTISEMENT

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

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

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

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

Error Converting Data Type Varchar To Datetime

Dec 4, 2004

hi
i want to pass a datetime variable from a asp page to MSSQL through a stored procedure, & i get this error:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
[Microsoft][ODBC SQL Server Driver][SQL Server]Error converting data type varchar to datetime.

this is the code that i use in page:

strsql="sp_inventory @timee=' " & RecievedDate & " ' "

also i use this one:
but i still get this error

strsql="sp_inventory @timee=' " & cdate(RecievedDate) & " ' "


this is maybe useful that i get this errot on some PCs, not all the computeres.
sometimes work well, & sth not work.

View 2 Replies View Related

Error Converting Data Type Varchar To Datetime.

Dec 22, 2004

Here is the stored procedure
the data type for the column b_trn_tentdte is decimal(8,0).

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER Procedure Prc_New_cus_wo_prev_posted
@b_trn_tendte_Beg_Date datetime
,@b_trn_tendte_End_Date datetime
@b_trn_tendte_Beg_Date1 datetime
,@b_trn_tendte_End_Date2 datetime

as

declare @Beg_tentdte datetime
declare @End_tentdte datetime
declare @Beg_tentdte1 datetime
declare @Beg_tentdte2 datetime

set @Beg_tentdte = convert(varchar(10), @b_trn_tendte_Beg_Date, 112)
set @End_tentdte = convert(varchar(10), @b_trn_tendte_End_Date, 112)
set @Beg_tentdte1 = convert(varchar(10), @b_trn_tendte_Beg_Date1, 112)
set @Beg_tentdte2 = convert(varchar(10), @b_trn_tendte_End_Date2, 112)


/* this query looks for customers with posted
transactions but none prior to the date range
in question */

Select distinct b_cus_cname
from
bar_cus_db_rec
,bar_trn_db_rec b
where b.b_trn_instid = ''
and b.b_trn_instid = b_cus_instid
and b.b_trn_actid = b_cus_cusid
and convert(varchar(10), b.b_trn_tentdte) between @Beg_tentdte and @End_tentdte
and not exists (select c.b_trn_actid
from bar_trn_db_rec c
where c.b_trn_instid = b.b_trn_instid
--and c.b_trn_tentdte between 19970901 and 20030229
and convert(varchar(10), c.b_trn_tentdte) between @Beg_tentdte1 and @Beg_tentdte2
and c.b_trn_actid = b.b_trn_actid)

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

every time I run the query, I get this error message:
Error converting data type varchar to datetime.

thanks for your assistance.

View 5 Replies View Related

Help With Error Converting Data Type Varchar To Float

Oct 17, 2007

Error converting data type varchar to float.


I am getting this error. Any one there to guide me

View 4 Replies View Related

Error Converting Data Type Varchar To Datetime

Jun 21, 2006

An application i am using creates an error.

Using SQL-profiler i catch this statement:

exec sp_executesql N'insert into RECORDING_SCHEDULE (programme_oid, capture_source_oid, filename, status, recording_group, recording_type, manual_channel_oid,
manual_start_time, manual_end_time, quality_level, pre_pad_minutes, post_pad_minutes) values (@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10,@P11,@P1 2)',N'@P1 int,@P2 int,@P3
nvarchar(19),@P4 int,@P5 int,@P6 int,@P7 int,@P8 datetime,@P9 datetime,@P10 int,@P11 int,@P12 int',177748,2,N'Lilla röda traktorn',4,40,5,16,''2006-06-21
17:00:00:000'',''2006-06-21 20:00:00:000'',1,1,2


After removing the double ' around the date and time parts i get the following error when running in QA:
Msg 8114, Level 16, State 5, Line 0
Error converting data type varchar to datetime.

If a remove the time portion it works.

The DDL for the table is:

CREATE TABLE [dbo].[RECORDING_SCHEDULE](
[oid] [int] IDENTITY(1,1) NOT NULL,
[programme_oid] [int] NULL,
[capture_source_oid] [int] NULL,
[status] [smallint] NOT NULL,
[filename] [varchar](255) NULL,
[recording_type] [int] NULL,
[recording_group] [int] NULL,
[manual_start_time] [datetime] NULL,
[manual_end_time] [datetime] NULL,
[manual_channel_oid] [int] NULL,
[quality_level] [int] NULL CONSTRAINT [DF__RECORDING__quali__38996AB5] DEFAULT ((0)),
[pre_pad_minutes] [int] NULL CONSTRAINT [DF__RECORDING__pre_p__398D8EEE] DEFAULT ((0)),
[post_pad_minutes] [int] NULL CONSTRAINT [DF__RECORDING__post___3A81B327] DEFAULT ((0)),
CONSTRAINT [aaaaaRECORDING_SCHEDULE_PK] PRIMARY KEY NONCLUSTERED

What am I missing?

View 2 Replies View Related

Error Converting Data Type Varchar To Numeric

Dec 10, 2014

I have a sql code that I am having some difficulty with. All I'm trying to do is get each department and sum the amount by month based on the date in the table. I am getting "error converting data type varchar to numeric".

Code:
select DEPARTMENT,
CASE
WHEN ((DATE >= '06/01/2014') AND (DATE <= '06/30/2014')) THEN (cast(sum(Amount) as decimal(10,2)))
ELSE '' END AS 'JUNE',
CASE
WHEN ((DATE >= '07/01/2014') AND (DATE <= '07/30/2014')) THEN (cast(sum(Amount) as decimal(10,2)))
ELSE '' END AS 'JULY'
from L27_PHAROS_DETAIL_DATA
WHERE Department = 'TECHNOLOGY SERVICES'
GROUP BY DEPARTMENT, DATE
ORDER BY DEPARTMENT

View 5 Replies View Related







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