I'm trying to submit my form into the database but I'm getting this error on the cmd.ExecuteNonQuery() section:
Conversion failed when converting datetime from character string.
I have three date fields (in bold below) and in the database they are set up as datetime (8).
What am I missing:
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click Dim conn As Data.SqlClient.SqlConnection = New Data.SqlClient.SqlConnection("Data Source=cmssolutions.db.9373013.hostedresource.com; Initial Catalog=cmssolutions; User ID=CMSSolutions; Password=CMSMedical1") If BMItxt.Text = "" Then BMItxt.Text = "01/01/1900"
how I can get around the following error with the following code?
[Code]....
Error: Msg 8169, Level 16, State 2, Line 1 Conversion failed when converting from a character string to uniqueidentifier. As you can probably tell, the datatypes are: m.UserId = uniqueidentifier l.System_User_ID = varchar(256) I was hoping the casting of one to the other would solve the issue, but it doesn't. How can I re-code this? I see from other forums it would probably be best to set all these fields to uniqueidentifier, but would prefer not to at this stage as it will involve changing quite a bit throughout the whole site!
I am trying to retrieve xml from a small function in my .cshtml page. Its throwing me an error. This however, runs fine in a console/form environment. It takes 3 parameters and 1 exception.
[Code]....
Within my GetXML function it simply applies the parameters to a store procedure. The store procedure returns me my object. From there I can do all the fun stuff in .cshtml. When I manually enter the values from within SQL Management Studio, I do in fact get the right result. Again, when I create a Window Form application - I too get the right result! When it comes down to .cshtml or .aspx pages - I get the conversion failed. I have already tried casting the store procedure uniqueidentifier parameter and it still gave me that error.
Using the following quires, for select and Update,. Select qry is working fine ,but update qry throws 'conversion failed when converting datetime from character string' .
select * from table1 where CreatedDt >= convert(datetime,'12/23/2009',101) and createdDt < dateadd(d,1,convert(datetime,'12/23/2009',101))
Update Table1 set status = 'y' where CreatedDt>= convert(datetime,'12/23/2009',101) and CreatedDt < dateadd(d,1,convert(datetime,'12/23/2009',101)
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER PROCEDURE [dbo].[SP_ORDER_LIST] @EmailId nvarchar(50) AS begin declare @Id int set @Id = (select SellerId from cart_seller_master where EmailId = @EmailId ) select CTD.OrderId,CTD.Product_code, CPM.product_name,CTD.Quantity,CTD.Orderdate, case when convert(datetime,isnull(CTD.DispatchedDate,null)) is null then 'Not Dispatched' end DispatchedDate , case when convert(datetime,isnull(CTD.DeliveryDate,null)) is null then 'Not Dispatched' end DeliveryDate from cart_Product_master as CPM left outer join cart_Transaction_details as CTD on ctd.product_code=cpm.product_code where CPM.SellerId = 1 group by CTD.OrderId,CTD.Product_code, CPM.product_name,CTD.Quantity,CTD.Orderdate, CTD.DispatchedDate,CTD.DeliveryDate end
I have a database call that is comparing UserID column of two tables. The UserID is a uniqueidentifier. The page errors on the r.read() line with Conversion failed when converting from a character string to uniqueidentifier. I'm not sure how to code this to avoid the error.
cmd.CommandText = "SELECT a.CenterName FROM Centers a INNER JOIN UserInfo u ON a.CenterID = u.CenterID WHERE u.UserID='{ + SessionManager.UserID + }'"; r = cmd.ExecuteReader(); r.Read();
I am trying to match a username to a userid which are in 2 different sql tables. Here is my select statement:
SELECT aspnet_Users.UserName FROM aspnet_Users INNER JOIN aspnet_starterkits_Projects ON aspnet_Users.UserName = aspnet_starterkits_Projects.ProjectManagerId
When I run this i get an error message: "Conversion failed when converting from a character string to uniqueidentifier" Is there anyway around this? Or a different select statement etc?
I am getting an error when attempting to call a stored proc from my asp.net page.
e.Message = "Conversion failed when converting character string to smalldatetime data type."
The stored proc is being called by:
The asp.net code that is calling the stored proc is:
//Display search results in GridView; SqlConnection con = new SqlConnection(strConn); //string sqlItemSearch = "usp_Item_Search"; SqlCommand cmdItemSearch = new SqlCommand(sqlItemSearch, con);
I am developing a C# VS 2008 / SQL Server 2005 Express website application. I have tried some of the fixes for this problem but my call stack differs from others. And these fixes did not fix my problem. What steps can I take to troubleshoot this?
System.Data.SqlClient.SqlException was caught Message="Conversion failed when converting datetime from character string." Source=".Net SqlClient Data Provider" ErrorCode=-2146232060 LineNumber=10 Number=241 Procedure="AppendDataCT" Server="\\.\pipe\772EF469-84F1-43\tsql\query" State=1 StackTrace: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at ADONET_namespace.ADONET_methods.AppendDataCT(DataTable dt, Dictionary`2 dic) in c:Documents and SettingsAdminMy DocumentsVisual Studio 2008WebSitesJerryApp_CodeADONET methods.cs:line 102 And here is the related code. When I debugged this code, "dic" only looped through the 3 column names, but did not look into row values which are stored in "dt", the Data Table. public static string AppendDataCT(DataTable dt, Dictionary<string, string> dic) { if (dic.Count != 3) throw new ArgumentOutOfRangeException("dic can only have 3 parameters"); string connString = ConfigurationManager.ConnectionStrings["AW3_string"].ConnectionString; string errorMsg; try { using (SqlConnection conn2 = new SqlConnection(connString)) { using (SqlCommand cmd = conn2.CreateCommand()) { cmd.CommandText = "dbo.AppendDataCT"; cmd.CommandType = CommandType.StoredProcedure; cmd.Connection = conn2; foreach (string s in dic.Keys) { SqlParameter p = cmd.Parameters.AddWithValue(s, dic[s]); p.SqlDbType = SqlDbType.VarChar; } conn2.Open(); cmd.ExecuteNonQuery(); conn2.Close(); errorMsg = "The Person.ContactType table was successfully updated!"; } } }
we are using transactional replication on an sql 2008 server. we try now to add a table to the replication that only consists of 1 culomn of type date and we insert the dates to the table. when we then create the replication we are getting an error on the creation of the snapshot. here is the create table with the insert dates:
USE [Product] GO /****** Object: Table [TimeSlices].[BaseDate] Script Date: 05/03/2010 15:52:24 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TimeSlices].[BaseDate]') AND type in (N'U')) DROP TABLE [TimeSlices].[BaseDate] GO USE [Product] GO /****** Object: Table [TimeSlices].[BaseDate] Script Date: 05/03/2010 15:52:24 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [TimeSlices].[BaseDate]( [TargetDate] [date] NOT NULL, CONSTRAINT [PK_BaseDate] PRIMARY KEY CLUSTERED ( [TargetDate] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO INSERT INTO [TimeSlices].[BaseDate] SELECT TargetDate FROM [TimeSlices].UDF_GetTravelDateRange(GETDATE()-2 , GETDATE()+1000) attached is the UDF: [Code].... this is the add article: use [Product] exec sp_addarticle @publication = N'Product$(QuoteNumber)', @article = N'BaseDate', @source_owner = N'TimeSlices', @source_object = N'BaseDate', @type = N'logbased', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'manual', @destination_table = N'BaseDate', @destination_owner = N'TimeSlices', @vertical_partition = N'false', @ins_cmd = N'CALL sp_MSins_TimeSlicesBaseDate', @del_cmd = N'CALL sp_MSdel_TimeSlicesBaseDate', @upd_cmd = N'SCALL sp_MSupd_TimeSlicesBaseDate' GO
this is the error we get:
Message: Query for data failed
Stack: at Microsoft.SqlServer.Replication.Snapshot.SqlServer.NativeBcpOutProvider.ThrowNativeBcpOutException(CConnection* pNativeConnectionWrapper) at Microsoft.SqlServer.Replication.Snapshot.SqlServer.NativeBcpOutProvider.BcpOut(String strBcpObjectName, String strBcpObjectOwner, String strBaseBcpObjectName, Boolean fUnicodeConversion, String strDataFile, String strLoadOrderingHint, String strWhereClause, Boolean useTableLockHint, Int32 bcpFileFormatVersion) at Microsoft.SqlServer.Replication.Snapshot.SqlServer.BcpOutThreadProvider.DoWork(WorkItem workItem) at Microsoft.SqlServer.Replication.WorkerThread.NonExceptionBasedAgentThreadProc() at Microsoft.SqlServer.Replication.AgentCore.BaseAgentThread.AgentThreadProcWrapper() (Source: MSSQLServer, Error number: 0) Message: Conversion failed when converting date and/or time from character string. Stack: (Source: MSSQLServer, Error number: 241)
why we are getting the conversion error on the snapshot creation?
I am reading a dataset/datatable where I have a a varchar field (25) that represents date and time in following format (this is orginally a db timestamp format): 2010-05-03 08:48:00.000 Prior to binding the datatable to a gridview I need to show date time as: 5/3/2010 8:48:00. How can I easily convert to the needed format?
I am using the abobe code to encrypt and decrypt a query string ID number.Any ideas to how to make this work with id numbers?Bellow is the code and store procdure.
<Conversion failed when converting the varchar value 'ID' to data type int.>am trying to insert datavalueField for a drop down list into a table column, its type is int. am using this syntax
Everything looks fine but I keep getting the error: 'Conversion failed when converting the nvarchar value 'xxxx' to data type int... where xxxx is the the value of @Query.
I get this error when I am trying to concatenate the two fields in bold into one field for a datagrid.
select count(cp.iOwnerID) as enrolled,(duration + '' + duration_interval) AS Duration ,session_id,school_id as SchoolId,program_id as ProgramId,convert(nvarchar(10),session_start_date,101) as SessionStartDate, session_class_size as SessionClassSize from dbo.cnSchoolProgramSessions cn with (nolock) left join customerproduct cp on convert(datetime,convert(nvarchar(10),session_start_date,101)) = convert(datetime,convert(nvarchar(10),cp.dtPurchasedate,101)) where record_status = 1 and school_id = '" & iSchool & "' and program_id = '" & iProgram & "' and (CP.chProductNumber = '" & Me.Label5.Text & "' or CP.chProductNumber IS NULL) group by session_id, school_id, program_id,session_start_date,session_class_size,duration,duration_interval
i want to bind my table to the datagridview in VB.net windows form (not in asp.net). but when i try to bind and run the applicatiion i got the following error "syntax error converting datetime from character string". my table contains two date filed"pdate","date" which contains the date in the followingg format"2010-02-01 00:00:00.000 i want to bind this date fileds to datagrid view.
once executing, I am getting following error: Conversion failed when converting the varchar value 'insert into Employee(EMPID,EMPNAME,EMPLname) select ' to data type int.
