I am trying to migrate our Portals database from SQL2000 to SQL2005, but I received "SQL Type Variant Data" error during the data migration with some database. Can anyone help me with this?
Thanks,
Jay
I have a user who uses the sql variant data type. Currently the database he uses is read only and he has noticed a lot of "garbage" in the column in the last few days. A dbcc shows no corruption and the user is following the rules of the sql variant data type as far as we know.
One last thing, he never had this problem on AS 2000.
config: :confused:
SQL 2000 SP3 on Windows Server 2003
Hi all,I have a table called PTRANS with few columns (see create script below).I have created a view on top that this table VwTransaction (See below)I can now run this query without a problem:select * from dbo.VwTransactionwhereAssetNumber = '101001' andTransactionDate <= '7/1/2003'But when I create an index on the PTRANS table using the command below:CREATE INDEX IDX_PTRANS_CHL# ON PTRANS(CHL#)The same query that ran fine before, fails with the error:Server: Msg 242, Level 16, State 3, Line 1The conversion of a char data type to a datetime data type resulted inan out-of-range datetime value.I can run the same query by commeting out the AssetNumber clause and itworks fine. I can also run the query commenting out the TransactionDatecolumn and it works fine. But when I have both the conditions in theWHERE clause, it gives me this error. Dropping the index solves theproblem.Can anyone tell me why an index would cause a query to fail?Thanks a lot in advance,AmirCREATE TABLE [PTRANS] ([CHL#] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[CHCENT] [numeric](2, 0) NOT NULL ,[CHYYMM] [numeric](4, 0) NOT NULL ,[CHDAY] [numeric](2, 0) NOT NULL ,[CHTC] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL) ON [PRIMARY]GOCREATE VIEW dbo.vwTransactionsASSELECT CONVERT(datetime, dbo.udf_AddDashes(REPLICATE('0', 2 -LEN(CHCENT)) + CONVERT(varchar, CHCENT) + REPLICATE('0', 4 -LEN(CHYYMM))+ CONVERT(varchar, CHYYMM) + REPLICATE('0', 2 -LEN(CHDAY)) + CONVERT(varchar, CHDAY)), 20) AS TransactionDate,CHL# AS AssetNumber,CHTC AS TransactionCodeFROM dbo.PTRANSWHERE (CHCENT <> 0) AND (CHTC <> 'RA')*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies View Related
OK I migrated a DTS package from MS SQL 2000 to MS SQL 2005 64-bit SSIS. OK so I fixed a problem with a Double Global Variable... Now I am stuck at a connection to Oracle and it returning data... Here is the error message:
[Execute SQL Task] Error: An error occurred while assigning a value to variable "Remedy_Count": "Unsupported data type on result set binding 0.".
My SQL Statement is this:
SELECT COUNT(*) AS Expr1
FROM DB.MYTABLE
When I run it from the Query Builder it returned this:
EXPR1 = 2983
Here are some details from the General Page on the Execute SQL Task
ResultSet - Single row
ConnectionType - OLE DB
Connection - Oracle Provider for OLE DB
SQLSourceType - Direct input
BypassPrepare - True
In the Result Set I have The following:
Result Name - Variable Name
0 - Remedy_Count
So... what can I do to fix this. I have the Remedy_Count set as a:
EvaluateAsExpression - False
Value - 2414
ValueType - Double
I have tried setting the ValueType to Object and other things... Same error... I read some where about how SSIS has issues when Oracle returns a Numeric type data. Can someone help me with this? Anything you all can tell me would be great.
update tblPact_2008_0307 set student_dob = '30/01/1996' where student_rcnumber = 1830when entering update date in format such as ddmmyyyyi know the sql query date format entered should be in mmddyyyy formatis there any way to change the date format entered to ddmmyyyy in sql query?
View 5 Replies View RelatedWhen 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)
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
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 RelatedI have one column in SQL Server 2005 of data type VARCHAR(4000).
I have imported sql Server 2005 database data into one mdb file.After importing a data into the mdb file, above column
data type converted into the memo type in the Access database.
now when I am trying to import a data from this MS Access File(db1.mdb) into the another SQL Server 2005 database, got the error of Unicode Converting a memo data type conversion in Export/Import data wizard.
Could you please let me know what is the reason?
I know that memo data type does not supported into the SQl Server 2005.
I am with SQL Server 2005 Standard Edition with SP2.
Please help me to understans this issue correctly?
After testing out the application i write on the local pc. I deploy it to the webserver to test it out. I get this error.
System.Data.SqlClient.SqlException: The conversion of a char data type to a
datetime data type resulted in an out-of-range datetime value.
Notes: all pages that have this error either has a repeater or datagrid which load data when page loading.
At first I thought the problem is with the date, but then I can see
that some other pages that has datagrid ( that has a date field) work
just fine.
anyone having this problem before?? hopefully you guys can help.
Thanks,
Hi,
The table in SQL has column Availability Decimal (8,8)
Code in c# using sqlbulkcopy trying to insert values like 0.0000, 0.9999, 29.999 into the field Availability
we tried the datatype float , but it is converting values to scientific expressions€¦(eg: 8E-05) and the values displayed in reports are scientifc expressions which is not expected
we need to store values as is
Error:
base {System.SystemException} = {"The given value of type SqlDecimal from the data source cannot be converted to type decimal of the specified target column."}
"System.InvalidOperationException: The given value of type SqlDecimal from the data source cannot be converted to type decimal of the specified target column. ---> System.InvalidOperationException: The given value of type SqlDecimal from the data source cannot be converted to type decimal of the specified target column. ---> System.ArgumentException: Parameter value '1.0000' is out of range.
--- End of inner exception stack trace ---
at System.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaData metadata)
--- End of inner exception stack trace ---
at System.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaData metadata)
at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternal()
at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServer(Int32 columnCount)
at System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table, DataRowState rowState)
at System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table)
at MS.Internal.MS
COM.AggregateRealTimeDataToSQL.SqlHelper.InsertDataIntoAppServerAvailPerMinute(String data, String appName, Int32 dateID, Int32 timeID) in C:\VSTS\MXPS Shared Services\RealTimeMonitoring\AggregateRealTimeDataToSQL\SQLHelper.cs:line 269"
Code in C#
SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnection, SqlBulkCopyOptions.Default);
DataRow dr;
DataTable dt = new DataTable();
DataColumn dc;
try
{
dc = dt.Columns.Add("Availability", typeof(decimal));
€¦.
dr["Availability"] = Convert.ToDecimal(s[2]); ------ I tried SqlDecimal
€¦€¦€¦.
}
bulkCopy.DestinationTableName = "dbo.[Tbl_Fact_App_Server_AvailPerMinute]";
bulkCopy.WriteToServer(dt);
thx
In the following code snippet, the stored procedure executes up to the
iRet = vciSqlCommand.ExecuteNonQuery();
command and fails with error:
System.Data.SqlClient.SqlException: Incorrect syntax near 'value'.
That would point to the
" = @data_Param where
section.
So what is the proper syntax?
Code Snippet
SqlParameter data_Param = new SqlParameter("@data_Param", SqlDbType.Variant);
SqlCommand vciSqlCommand = new SqlCommand();
switch ((vcidatatype)FieldDef.ivcidatatype)
{ // read in the binary data!!!!!!
case vcidatatype.eENGUNITS: data_Param.Value = Encoding.ASCII.GetString(r.ReadBytes(FieldDef.iLen)); break;
case vcidatatype.eLANADR: data_Param.Value = r.ReadInt16(); break;
case vcidatatype.eBYTESIZE: data_Param.Value = r.ReadByte(); break;
case vcidatatype.eFLOATSIZE: data_Param.Value = r.ReadSingle(); break;
case vcidatatype.eINT32SIZE: data_Param.Value = r.ReadUInt32(); break;
case vcidatatype.eFILLERBYTES: r.ReadBytes(FieldDef.iLen); break; // eat up empty bytes
default: FieldDef.ivcidatatype = (Int32)vcidatatype.eFILLERBYTES; break;
}
if (FieldDef.ivcidatatype != (Int32)vcidatatype.eFILLERBYTES)
{ // all but filler
vciSqlCommand.CommandText =
"update " + acPointType + "set " + FieldDef.sFldName +
" = @data_Param where VEC = " + acVECName +
" and name = " + acPointName;
vciSqlCommand.Connection = conn;
conn.Open();
iRet = vciSqlCommand.ExecuteNonQuery();
conn.Close();
I am trying to declare a global variable in a DTS package for passing the recordset to the next stage Active Script . After declaration of the Global Variable and selecting datatype of the variable as OTHER ( Variant ) , when I try to save the DTS Package changes , it throws a Type Mismatch Error .
:confused: Please help me out .
Thanx
Arnie .
Can anyone help on this?
In SQL 2005, I use a variant with XML type; it seems that its size is limited here. If I assign a big data to it, it appears not assigned at all. Here is the example:
Code Snippet
declare @xml xml
--cross two table to get many records returned, if nothing is abnormal, just cross more tables here
select * from master.sys.objects a, master.sys.objects b for xml RAW, ELEMENTS
set @xml=( select * from master.sys.objects a, master.sys.objects b for xml RAW, ELEMENTS )
select @xml
--no results here for var @xml
--use single table to get fewer records returned.
select * from master.sys.objects for xml RAW, ELEMENTS
set @xml=( select * from master.sys.objects for xml RAW, ELEMENTS )
select @xml
--results as normal.
Note:here I don't want to test SQL server, I just use XML type variant as oupput for a sproc, and come across the issue here.
Any reply will be appreciated!
I am doing migration from oracle11g to SQL server2012. Facing error while data migration from Oracle rowid dat typeto SQL uniqueidentifier using SSMA6.0
Errors: The given value of type String from the data source cannot be converted to type uniqueidentifier of the specified target column.
Refer above error.
Hi, I was wondering if there is a method (other than BULK INSERT) to insert a (C++) application level array into the database, I have a variant type array populated with values that I want to insert, perhaps using ADO objects in quick time!
Hi,
I can populate a dataTable with type double (C#) of say '1055.01' however when I save these to the CE3.5 database using a float(CE3.5) I lose the decimal portion. The 'offending' code is:
this.court0TableAdapter1.Update(this.mycourtsDataSet1.Court0);
this.mycourtsDataSet1.AcceptChanges();
this.court0TableAdapter1.Fill(this.mycourtsDataSet1.Court0);
This did not happen with VS2005/CE3.01.
I have tried changing all references to decimal (or money in CE3.5) without luck.
I'm beginning to think that string may be the way to go!!!!!!!
Can someone shed some light on my problem.
Thanks,
Later:
It's necessary to update the datatable adapter as the 3.01 and 3.5 CE are not compatible.
We have some columns in a table where the date is stored as 19980101 (YYYYMMDD). The data type for this column is NUMBER(8) in Oracle.
I need to copy rows from Oracle to SQL Server using SSIS. I used the Data Conversion transformation editor to change it to DT_DATE, but the rows are not being inserted to the destination.
On Error, If I fail the component, then the error is :
There was an error with input column "ORDER_DATE_CONV" (1191) on input "OLE DB Destination Input" (29). The column status returned was: "Conversion failed because the data value overflowed the specified type.".
Regards
RH
Hoping someone will have a solution for this error
Errors in the metadata manager. The data type of the '~CaseDetail ~MG-Fact Voic~6' measure must be the same as its source data type. This is because the aggregate function is not set to count or distinct count.
Is the problem due to the data type of the column used in the mining structure is Long, and the underlying field in the cube has a type of BigInt,or am I barking up the wrong tree?
I am trying to put the data from a field in my database into a row in a table using the SQLDataSource.Select statement. I am using the following code: FileBase.SelectCommand = "SELECT Username FROM Files WHERE Filename = '" & myFileInfo.FullName & "'" myDataRow("Username") = CType(FileBase.Select(New DataSourceSelectArguments()), String)But when I run the code, I get the following error:Server Error in '/YorZap' Application. Unable to cast object of type 'System.Data.DataView' to type 'System.String'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.InvalidCastException: Unable to cast object of type 'System.Data.DataView' to type 'System.String'.Source Error: Line 54: FileBase.SelectCommand = "SELECT Username FROM Files WHERE Filename = '" & myFileInfo.FullName & "'"
Line 55: 'myDataRow("Username") = CType(FileBase.Select(New DataSourceSelectArguments).GetEnumerator.Current, String)
Line 56: myDataRow("Username") = CType(FileBase.Select(New DataSourceSelectArguments()), String)
Line 57:
Line 58: filesTable.Rows.Add(myDataRow)Source File: D:YorZapdir_list_sort.aspx Line: 56 Stack Trace: [InvalidCastException: Unable to cast object of type 'System.Data.DataView' to type 'System.String'.]
ASP.dir_list_sort_aspx.BindFileDataToGrid(String strSortField) in D:YorZapdir_list_sort.aspx:56
ASP.dir_list_sort_aspx.Page_Load(Object sender, EventArgs e) in D:YorZapdir_list_sort.aspx:7
System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +13
System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +45
System.Web.UI.Control.OnLoad(EventArgs e) +80
System.Web.UI.Control.LoadRecursive() +49
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3743
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210 Please help me!
I am currently in the process of migrating data from Sybase to Sql server and would like to know how to test the data migrated.
As of now, we took one table data from both source and destination and compared it in Excel to check if the data migrated looks good (note, we used SSIS to migrate data). However, I would like to check if there are any other best & easy ways to apprach data validation post migration.
I have a cluster hosting multiple GP databases and a second for my data-warehouse I am playing around with (personal project).
I have scripts that pull data from all the DB's to input into the DW's tables(Customers,Reps,Hub....)
An example of my branch script :
select interidas BranchID,
cmpnynamas BranchDesc,
address1as BranchAddressLine1,
address2as BranchAddressLine2,
address3as BranchAddressLine3,
zipcodeas PostalCode
from dynamics..SY01500
Where interid in ('comp1', 'comp2', 'comp4', 'comp5')
what would be the best way to using these scripts pull the data to my testDW and not have duplicate data issues?
I was thinking of using a staging DB on the GP cluster and then building an import data package to run nightly. the issue i had was how do i avoid duplicate data ?
hello all .. I have a form that includes two textboxes (Date and Version) .. When I try to insert the record I get the following error message .. seems that something wrong with my coversion (Data type)"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.The statement has been terminated."
in my SQL database I have the date feild as datetime and the version as nvarchar(max)
this is the code in the vb page .. Can you please tell me how to solve this problem?Imports System.Data.SqlClient
Imports system.web.configuration
Partial Class Admin_emag_insert
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Record_DateTextBox.Text = DateTime.Now
End Sub
Protected Sub clearButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles clearButton.Click
Me.VersionTextBox.Text = ""
End Sub
Protected Sub addButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles addButton.Click
Dim objConnection As SqlConnection
Dim objDataCommand As SqlCommand
Dim ConnectionString As String
Dim record_date As Date
Dim version As String
Dim emagSQL As String
'save form values in variables
record_date = Record_DateTextBox.Text
version = VersionTextBox.Text
ConnectionString = WebConfigurationManager.ConnectionStrings("HRDBConnectionString").ConnectionString
'Create and open the connection
objConnection = New SqlConnection(ConnectionString)
objConnection.Open()
emagSQL = "Insert into E_Magazine (Record_Date, Version ) " & _
"values('" & record_date & "','" & version & "')"
'Create and execute the command
objDataCommand = New SqlCommand(emagSQL, objConnection)
objDataCommand.ExecuteNonQuery()
objConnection.Close()
AddMessage.Text = "A new emagazine was added successfully"
Me.VersionTextBox.Text = ""
End Sub
End Class
Hi,I would like to convert a dollar amount ($1,500) to represent Fifteenhundred dollars and 00/100 cents only for SQL reporting purposes. Isthis possible and can I incorporate the statement into an existingleft outer join query.Thanks in advance,Gavin
View 1 Replies View RelatedHi,
I have a user defined data type which is called DEmployeeName,
it's length is varchar(20), how do i change it into varchar(30) without droping it?
I'm using SQL server 2005.
Thanks in advance..
I have a field that is currently stored as the data type nvarchar(10), and all of the data in this field is in the format mm/dd/yyyy or NULL. I want to convert this field to the smalldatetime data type. Is this possible?
I've tried to use cast in the following way, (rsbirthday is the field name, panelists is the table), but to no avail.
SELECT rsbirthday CAST(rsbirthday AS smalldatetime)
FROM panelists
the error returned is "incorrect syntax near 'rsbirthday'.
I'm rather new to all things SQL, so I only have the vaguest idea of what I'm actually doing.
Thanks for the help!
$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");
}
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
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()
(...)
---
I have created a Foreach Loop container which generates a string variable which is a Select statement that in turn used in my OLE DB source in my Data Flow task. I have a 3 variables that I am using to create the Select statement. Two of them work fine but the 3rd gives me an error "Cannot convert varchar to numeric" after about the 5th or 6th loop which is odd as the variable is the same for each pass.
The SQL Task linked to the Foreach Loop is a query as follows
SELECT DISTINCT
CAST(FYr as varchar(4)) as FYr,
CAST(Acct1 as varchar(13)) as Acct1,
CAST(Acct2 as varchar(13)) as Acct2
FROM GL, AcctTbl
The resulting dataset looks like this (there is only one record in AcctTbl)
FYr Acct1 Acct2
2000 400.00 307.00
2001 400.00 307.00
2002 400.00 307.00
etc, which is exactly what I want.
I've created package scope string variables for sFYr, sAcct1 and sAcct2 as well as another variable qrySQL
The value for qrySQL string variable is something like this
"Select " + @User :: sFYr + " as FiscalYear, Account, " + @User :: sAcct2 + "Amount FROM tblGL WHERE Account < " + @User :: sAcct1
This is then used as my datasourse in my data flow task
When I run the package it goes through a half a dozen iterations ( there are about a dozen total rows to iterate) and successfully writes the results to my data destination table but then fails with the "cannot convert varchar to numeric" message.
It seems to be with the sAcct1 variable because if I use the same string for my qrySQL except I replace the sAcct1 variable with string (as shown below) the package completes successfully
"Select " + @User :: sFYr + " as FiscalYear, Account, " + @User :: sAcct2 + "Amount FROM tblGL WHERE Account < '400.00'
Does anyone have any ideas? Can I not use the < to compare string? The Account field that I'm comparing is a varchar(13) field. I've even tried casting the Account and sAcct1 variable as numeric in the qrySQL string and I'm getting the same failure after several iterations.
Any insight would really be appreciated. I've lost a bit of hair over this one.
Thanks in advance
Hi,I keep getting the error:System.Data.SqlClient.SqlException: Conversion failed when converting the varchar value '@qty' to data type int. When I initiate the insert and update.I tried adding a: Convert.ToInt32(TextBox1.Text), but it didn't work.. Could someone help?My code:private bool ExecuteUpdate(int quantity){ SqlConnection con = new SqlConnection(); con.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;Integrated Security=True;User Instance=True"; con.Open(); SqlCommand command = new SqlCommand(); command.Connection = con; TextBox TextBox1 = (TextBox)FormView1.FindControl("TextBox1"); Label labname = (Label)FormView1.FindControl("Label3"); Label labid = (Label)FormView1.FindControl("Label13"); command.CommandText = "UPDATE Items SET Quantityavailable = Quantityavailable - '@qty' WHERE productID=@productID"; command.Parameters.Add("@qty", TextBox1.Text); command.Parameters.Add("@productID", labid.Text); command.ExecuteNonQuery(); con.Close(); return true;} private bool ExecuteInsert(String quantity) { SqlConnection con = new SqlConnection(); con.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;Integrated Security=True;User Instance=True"; con.Open(); SqlCommand command = new SqlCommand(); command.Connection = con; TextBox TextBox1 = (TextBox)FormView1.FindControl("TextBox1"); Label labname = (Label)FormView1.FindControl("Label3"); Label labid = (Label)FormView1.FindControl("Label13"); command.CommandText = "INSERT INTO Transactions (Usersname,Itemid,itemname,Date,Qty) VALUES (@User,@productID,@Itemsname,@date,@qty)"; command.Parameters.Add("@User", System.Web.HttpContext.Current.User.Identity.Name); command.Parameters.Add("@Itemsname", labname.Text); command.Parameters.Add("@productID", labid.Text); command.Parameters.Add("@qty", Convert.ToInt32(TextBox1.Text)); command.Parameters.Add("@date", DateTime.Now.ToString()); command.ExecuteNonQuery(); con.Close(); return true; }protected void Button2_Click(object sender, EventArgs e){ TextBox TextBox1 = FormView1.FindControl("TextBox1") as TextBox; ExecuteUpdate(Int32.Parse(TextBox1.Text) );}protected void Button2_Command(object sender, CommandEventArgs e) { if (e.CommandName == "Update") { TextBox TextBox1 = FormView1.FindControl("TextBox1") as TextBox; ExecuteInsert(TextBox1.Text); } } Thanks so much if someone can!Jon
View 33 Replies View Relatedcan 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
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();