hi all, i'm retreiving user input using textboxes and saving to a gridview. i'm getting this error and i dont know whats causing it. <asp:SqlDataSource ID="SqlDataSource1" runat="server"
InsertCommand="INSERT INTO test101(Surname,Names,Regno)VALUES (@Surname, @Names, @Regno)"ConnectionString="<%$ ConnectionStrings:engineeringConnectionString %>" ProviderName=System.Data.SqlClient
i have migrated a DTS package wherein it consists of SQL task.
this has been migrated succesfully. but when i execute the package, i am getting the error with Excute SQL task which consists of Store Procedure excution.
But the SP can executed in the client server. can any body help in this regard.
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.
Hi All, can someone help me, i've created a stored procedure to make a report by calling it from a website. I get the message error "241: Syntax error converting datetime from character string" all the time, i tryed some converting things but nothig works, probably it is me that isn't working but i hope someone can help me. The code i use is:
CREATE proc CP_Cashbox @mID varchar,@startdate datetime,@enddate datetime as set dateformat dmy go declare @startdate as varchar declare @enddate as varchar
--print "query aan het uitvoeren"
select sum(moneyout) / sum(moneyin)*100 as cashbox from dbo.total where machineID = '@mID' and njdate between '@startdate' and '@enddate' GO
Here's the deal. I'm a newbie trying to update a date (@Effective) in sql server. I've been trying all sorts of things, but nothing seems to work. Here's my current code:
Sub btnUpdate_Click(sender As Object, e As EventArgs) Dim sqlStmt As String Dim conString As String Dim cn As SqlConnection Dim cmd As SqlCommand Dim sqldatenull As SqlDateTime Try sqlStmt = "UPDATE [Submission] SET [Effective]=Convert(DateTime, '@Effective', 101), "& _ "[Status]=@Status WHERE ([Submission].[Submission]=@Submission)" conString = "server='(local)'; user id='sa'; password='password'; database='database'" cn = New SqlConnection(conString) cmd = New SqlCommand(sqlStmt, cn)
If (txtEffective.Text = "") Then cmd.Parameters("@Effective").Value = sqldatenull Else cmd.Parameters("@Effective").Value = txtEffective.Text End If cmd.Parameters("@Status").Value = dropStatus.SelectedItem.Text cmd.Parameters("@Submission").Value = LabelSubmission.Text
Somewhere, either in the aspx.vb code, or the stored procedure that's being called, it doesn't like my code since I am getting the dreaded "syntax error converting datetime from character string" error. I've been battling this for two days - can someone spot what is wrong here?
The dtWdate variable is coming from a calendar control on my aspx page. Please help - I've tried every conceivable way I can think to do this without success. Lynnette
Here's the code: Private Sub GetFLSAEmpInfoRecs() 'uses the AppSettings table to generate WHERE clause from the filter values
Dim dtWdate As DateTime = Calendar1.SelectedDate Dim wValue As String = Session("svFilterValue")
Dim cnn As New SqlConnection(constants.SQLConStrFLSA) Dim cmd As New SqlCommand("usp_AddFLSA", cnn) cmd.CommandType = CommandType.StoredProcedure cnn.Open()
Hi all, The last week this was working, and today i was testing and all start with errors. This is my SQL sentence:command = New SqlCommand("update ctlg_users set u_last_access = '" & FormatDateTime(Now(), 1) & "' where u_id = " & cstr(l_u_id),oConn)Is there an error? why was before working and now don`t?, including an old ASP page that i´m still using fall back to the same error when is using FormatDateTime, í´ve tried in the ASPX page using datetime.now.tostring("dd/mm/yyyy") but i got an error too!! HEEEELP!Thanks :D
I'm having a strange problem with the following stored proc:
Code:
sql
declare @orderBy tinyint
set @orderBy = 2
SELECT tblUsers.userID, firstName, lastName, dateRegistered, count(movieSessionID) As sessionsCount FROM tblUsers LEFT JOIN tblMovieSessions on tblUsers.userID = tblmovieSessions.userID Group By tblUsers.userID, firstName, lastName, dateRegistered Order By Case WHEN @orderBy = 2 THEN firstName WHEN @orderBy = 3 THEN dateRegistered WHEN @orderBy = 4 THEN count(movieSessionID) else lastName End desc
When I execute it with @orderBy = 1 or 2 I get the following error message: Quote: Server: Msg 241, Level 16, State 1, Line 5 Syntax error converting datetime from character string.
However, with @orderBy = 3 or 4 it executes successfully.
I don't understand why this change should have this impact because the error seems to be occuring on the first line (i.e. SELECT...).
If I take the dateRegistered column out of the query I get a new error message:
Quote: Server: Msg 245, Level 16, State 1, Line 5 Syntax error converting the nvarchar value 'Adam' to a column of data type int.
I don't understand why it is trying to convert these columns during the query anyway, let alone how to solve the problem.
I am using INSERT to replace about 160 fields for one company in the company table, all goes well until I come to the Rn_edit_date field which is a datetime datatype. When I insert the value of:
cast('2005-03-15 09:25:44.463' as datetime)
Error message as a result is: Syntax error converting datetime from character string.
NOTE: 2005-03-15 09:25:44.463 is the correct format which all other companies abide by, so I'm totally lost at where I'm going wrong?
I have a field that has dates that are currently set as a string. Dates are as follows:
Apr 01 2006 Aug 20 2006 Aug 20 2006
I did an isdate() function on the field to see if it can be converted and it came back as true; however, I have attempted several different ways to convert the field to a date field and it continues to give me a syntax error. Any assistance would be great!!!
I tried: convert(datetime,cast(New_Date as varchar),100) convert(varchar(25),New_Date,107) Both gave me syntax errors.
When I convert this data into a datetime, i get the foll error. Syntax error converting datetime from character string.
SELECT convert(datetime,'09/2007')
Basically I have a field in a tbl that has varchar datatype, and is in this format of mm-yyyy. I need to compare this value to current date and so I convert it to datetime. but it errs Pl help
CREATE PROCEDURE dbo.Synchronization_GetNewRecords(@item varchar(50),@last datetime)ASSET NOCOUNT ONDECLARE @sql nvarchar(4000)SET @sql = 'SELECT * FROM ' + @item + ' WHERE LastUpdated >' + @lastEXEC sp_executesql @sql, N'@Type varchar(50), @Last datetime', @item, @lastThis is my SP. Very simple. But it is throwing the error in the subject line.Any help would be greatly appreciated.
@startRowIndex int, @maximumRows int, @SortOrder int AS
DECLARE @TempItems TABLE ( ID int IDENTITY, OrderID int ) DECLARE @maxRow int SET @maxRow = (@startRowIndex + @maximumRows) - 1 SET ROWCOUNT @maxRow INSERT INTO @TempItems (OrderID) SELECT OrderID FROM Orders o INNER JOIN Customers c ON c.CustomerID = o.CustomerID INNER JOIN Employees e ON e.EmployeeID = o.EmployeeID ORDER BY CASE @SortOrder WHEN 0 THEN OrderID WHEN 1 THEN c.CompanyName WHEN 2 THEN e.FirstName WHEN 3 THEN o.OrderDate WHEN 4 THEN o.RequiredDate WHEN 5 THEN o.ShippedDate END
SET ROWCOUNT @maximumRows SELECT o.OrderID, CompanyName, e.FirstName + ' ' + e.LastName EmployeeName, o.OrderDate, o.RequiredDate, o.ShippedDate FROM @TempItems t INNER JOIN Orders o ON o.OrderID = t.OrderID INNER JOIN Customers c ON c.CustomerID = o.CustomerID INNER JOIN Employees e ON e.EmployeeID = o.EmployeeID WHERE t.[ID] >= @startRowIndex AND (o.OrderID LIKE @intOrderID OR @intOrderID = '') AND (o.CustomerID = @strCustomerID OR @strCustomerID='') AND (o.EmployeeID = @intEmployeeID OR @intEmployeeID = 0) AND (o.OrderDate = @dteOrderDate OR @dteOrderDate IS NULL) SET ROWCOUNT 0 GO
when i execute the sp with @sortOrder = 0, it works fine exec CustomPaging '','',0,null, 1, 10, 0
but if i try anything else (@SortOrder=1) i get the error message exec CustomPaging '','',0,null, 1, 10, 1 Syntax error converting datetime from character string.
but im unsure what is causing this problem? and how to sort it?
Hi guys and gals,I have the following code:CREATE PROCEDURE searchRecords( @searchFor NVarchar (25) = NULL, @fromDate NVarchar (25) = NULL, @toDate NVarchar (25) = NULL) AS IF @fromDate IS NOT NULL DECLARE @fromDateString VarChar(200) SET @fromDateString = ' CONVERT(smalldatetime, ''' + @fromDate + ''') ' IF @toDate IS NOT NULL DECLARE @toDateString VarChar(200) SET @toDateString = ' CONVERT(smalldatetime, ''' + @toDate + ''') ' SELECT * FROM MIPR WHERE ID = @searchFor OR DESC_QTY = @searchFor OR REQ_ACTIVITY = @searchFor OR ACC_ACTIVITY = @searchFor OR MANYEARS = @searchFor OR TECH_POC = @searchFor OR RESOURCE_POC = @searchFor OR SI_DATE BETWEEN @fromDateString AND @toDateString;GOit creates an error that I can't seem to fix. the error I get is:Syntax error converting character string to smalldatetime data type.How can I fix this? Thanks in advance!
I am using ASPOSE License(3rd party licensing tool).
Getting Error when using encrypted License file (*.lic) in my Application .
Error is .
"Invalid character in a Base-64 string "
What does it means ?????
But Demo License file *.lic is working fine.
Please provide solutions.
Reply is highly appreciated.
Rishi
Error is Invalid character in a Base-64 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.FormatException: Invalid character in a Base-64 string.
Source Error:
Line 59: Line 60: //license = new Licensing("Ghezt4vbwif7r9cf7ra2ttbKzv76+v7GAA==", licenceFile); Line 61: license = new Licensing("Ghezt4vbwif7r9cf7ra2ttbKzv76+v==", licenceFile); Line 62: Line 63: Source File: c:Documents and SettingsAdministratorMy DocumentsVisual Studio 2005WebSitesTestLicenseApp_CodeDemo.cs Line: 61
Stack Trace:
[FormatException: Invalid character in a Base-64 string.] System.Convert.FromBase64String(String s) +0 Aspose.License.Licensing.M0(String key, String encryptedString) +627 Aspose.License.Licensing.A0() +753 Aspose.License.Licensing..ctor(String key, String licenseFile) +72 Demo.Initialize() in c:Documents and SettingsAdministratorMy DocumentsVisual Studio 2005WebSitesTestLicenseApp_CodeDemo.cs:61 Demo..ctor(String licenseFolder, Page page) in c:Documents and SettingsAdministratorMy DocumentsVisual Studio 2005WebSitesTestLicenseApp_CodeDemo.cs:37 _Default.Page_Load(Object sender, EventArgs e) in c:Documents and SettingsAdministratorMy DocumentsVisual Studio 2005WebSitesTestLicenseDefault.aspx.cs:18 System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +31 System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +68 System.Web.UI.Control.OnLoad(EventArgs e) +88 System.Web.UI.Control.LoadRecursive() +74 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3035
Hi,I'm new at asp .net and am having a problem. On a linkbutton click event, I want to insert into my db a row of data which includes two parameters. Param1 is the id of the logged in user, and Param2 is <%#DataBinder.Eval(Container.DataItem, 'UserId')%> which is the username of a user given through a datalist.When I execute the query: "insert into aspnet_friendship (userId, buddyId) values (@Param1, @Param2)"I get the error: Conversion failed when converting from a character string to uniqueidentifier.I am trying to insert these into my table aspnet_friendship into the columns userId and buddyId which are both uniqueidentifier fields. I tested the query using the same values (@Param1, @Param1) and (@Param2, @Param2) and found out that the problem is with Param2 because when I use Param1 the insert works perfectly. The value passed by Param2 is supposed to be the id of a user which I get from the datalist which selects * from aspnet_Users, so that <%#DataBinder.Eval(Container.DataItem, 'UserId')%> should also be an Id like Param1 is. Since both params are in the form of .toString(), I don't understand why one works but the other doesn't.As you can see in the code below, both of these parameters are dimmed as strings, so I don't understand why Param2 doesn't work. Can anyone please help? Protected Sub LinkButton1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Dim MyConnection As SqlConnection MyConnection = New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Integrated Security=True;User Instance=True") Dim MyCommand2 As SqlCommand Dim CurrentUser As String CurrentUser = Membership.GetUser.ProviderUserKey.ToString() Dim add As String add = "<%#DataBinder.Eval(Container.DataItem, 'UserId')%>".ToString() Session("username") = User.Identity.Name Dim InsertCmd As String = "insert into aspnet_friendship (userId, buddyId) values (@Param1, @Param2)" MyCommand2 = New SqlCommand(InsertCmd, MyConnection) MyCommand2.Parameters.AddWithValue("@Param1", CurrentUser) MyCommand2.Parameters.AddWithValue("@Param2", add) MyCommand2.Connection.Open() MyCommand2.ExecuteNonQuery() MyCommand2.Connection.Close() End Sub Thank you.
Hello, Im Getting an error (below) at the login page. The only control on that page is a Login control, adn the error below comes when I click login. Does anyone know what Im doing wrong? This is my login page:<%@ Page Language="C#" MasterPageFile="~/MasterPage.master" AutoEventWireup="true" CodeFile="Login.aspx.cs" Inherits="Login" Title="myapp- Login" %><asp:Content ID="Content1" ContentPlaceHolderID="ContentHeader" Runat="Server"> LOGIN</asp:Content><asp:Content ID="Content2" ContentPlaceHolderID="ContentBody" Runat="Server"> <table border="1" cellpadding="0" cellspacing="0" width="305"> <tr> <td class="bodyText" colspan="2" style="height: 20px; text-align: center;"> <p> <!-- TemplateBeginEditable name="mainText" --> <asp:Login ID="Login1" runat="server" TitleText="Existing Users Log In"> </asp:Login> </p> </td> </tr> <tr> <td style="text-align: right"> <asp:LinkButton ID="lbNewmembers" runat="server" OnClick="lbNewmembers_Click">New Member?</asp:LinkButton></td> <td> <asp:LinkButton ID="lbForgotPassword" runat="server" OnClick="lbForgotPassword_Click">Forgot Password?</asp:LinkButton></td> </tr> </table></asp:Content>Error: Server Error in '/myApp' Application.
Conversion failed when converting from a character string to uniqueidentifier. 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.Data.SqlClient.SqlException: Conversion failed when converting from a character string to uniqueidentifier.Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace:
I've imported a CSV file into a table in SQL Server 2012. It's a large file, 140,000+ rows, so I couldn't covert it to Excel first to preserve the date format due to Excel's row limit. In the CSV file, there were 3 column with date data in "31-Aug-09" format, and the import automatically transformed these in "31AUG09" format (varchar(50)) in SQL Server. Now I need to convert these 3 columns from varchar to datetime so I could work with them in date format.
I have a problem that I'm not sure how to handle. In the application I am working on I will be importing fixed length strings from a CD into the database. Each specific character in the string represents some value.
I can't decide if I should just create a single field of 895 characters or create 895 single character fields. When I need data from the database I won't need all 895 characters of information, I'll only need one or a small subset of values.
Does it really matter which approach I take?
Thanks in advance for any advice/tips you can provide.
I am trying to insert a row into a table of Microsoft SQL Server 2000.
There are various columns.
[SNO] [numeric](3, 0) NOT NULL , [DATT] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [DATTA] [char] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [CODECS] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
The [DATTA] column is causing a problem. Even if I am trying to put only 1700 character string into [DATTA], the java code throws the following exception:-
StaleConnecti A CONM7007I: Mapping the following SQLException, with ErrorCode 0 and SQLState 08S01, to a StaleConnectionException: java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Connection reset
at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source)
Why is it throwing an exception even though the sum-total of this row doesn't exceed 8000 characters?
My SQL statement: SQL Server seems to think my SQL Statement is too long........ I'm not sure why. "INSERT INTO tblChangeControls (Initiator,BPM,AddRemMod,ChangeType,SpecificChange,ChangeDescription,TechnicalDescriptionOfChange,DateInitiated) select 'Benjamin Short' ,'6' ,'2' ,'Printer' ,'40' ,'' ,'';" Error message: The identifier that starts with 'INSERT INTO tblChangeControls (Initiator,BPM,AddRemMod,ChangeType,SpecificChange,ChangeDescription,TechnicalDescriptionOfChange,' is too long. Maximum length is 128.
USE [Analytical] GO /****** Object: Table [dbo].[DailyTickMinMaxAnalysis] Script Date: 08/07/2007 15:44:29 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[DailyTickMinMaxAnalysis]( [symbol] [varchar](50) NOT NULL, [cDate] [varchar](50) NOT NULL, [part] [varchar](50) NOT NULL, [collection] [varchar](max) NULL, CONSTRAINT [remPat_Trades] PRIMARY KEY CLUSTERED ( [symbol] ASC, [cDate] ASC, [part] 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 SET ANSI_PADDING OFF
Stored procedure is:
USE [Analytical] GO /****** Object: StoredProcedure [dbo].[InsertDailyTickMinMaxAnalysis] Script Date: 08/07/2007 15:45:49 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO
ALTER PROCEDURE [dbo].[InsertDailyTickMinMaxAnalysis] @symbol varchar(50), @cDate varchar(50), @part varchar(50), @collection varchar(50) = NULL AS BEGIN SET NOCOUNT ON; INSERT INTO dbo.DailyTickMinMaxAnalysis ( symbol, cDate, part, [collection]) Values ( @symbol, @cDate, @part, @collection) END
C# procedure: public void saveHistTickMinMaxAnalysis ( int tableIndex ) { if ( tableIndex != -1 ) { using ( SqlConnection conn_Analytical = new SqlConnection ( "server=ddmnvt61\sqlexpress;" + "database=Analytical;Trusted_Connection=Yes" ) ) { SqlCommand cmdm = new SqlCommand ( ); conn_Analytical.Open ( ); cmdm.Connection = conn_Analytical; cmdm.CommandType = CommandType.StoredProcedure; cmdm.CommandText = "InsertDailyTickMinMaxAnalysis"; cmdm.Parameters.Clear ( ); cmdm.Parameters.Add ( "@symbol", SqlDbType.VarChar, 50, "symbol" ); cmdm.Parameters.Add ( "@cDate", SqlDbType.VarChar, 50, "cDate" ); cmdm.Parameters.Add ( "@part", SqlDbType.VarChar, 50, "part" ); // 2 cmdm.Parameters.Add ( "@collection", SqlDbType.VarChar, 500, "collection" ); // 3 foreach ( DataRow row in Globals.dtSets.Tables[tableIndex].Rows ) { cmdm.Parameters[0].Value = ( string )row[0]; cmdm.Parameters[1].Value = ( string )row[1]; cmdm.Parameters[2].Value = ( string )row[2]; cmdm.Parameters[3].Value = ( string )row[3]; Console.WriteLine ( "Saving {0}", ( string )row[3] ); // <= where the print out occurs to confirm that the right string is being passed try { cmdm.ExecuteNonQuery ( ); } catch { } } } } } // saveHistTickMinMaxAnalysis
Table is defined with the last columns[3].Length = 500;
I am currently doing a mapping from text file to sql table. there is one column in the text file that contain more than 15,000 characters in it. it causes me an error because i was using varchar data type, in which i believe can only store 8000 char. can anybody tell me which data type to use? and also, will i be able to change the data type in the input column also? if yes, which data type should i change it to?
Hello Everybody,I have a problem that I have an exception from a SQLDataSource when a user types in more characters to a TextBox then is allowed.First of all - is there ant built in ASP.NET way to handle that problem?Second - if not then I have to handle the Exception from the SQLDataSource myself. I can write a handler for that which will look like:1 protected void DetailsViewDS_OnUpdated(object sender, SqlDataSourceStatusEventArgs e) 2 { 3 if (e.Exception != null) 4 { 5 // do some stuff 6 } 7 } The problem is that I would like to show the user an error message which says how many characters he can insert. So the other question is: is there any way to get automatically from the SQL DB what is a maximum length of a varchar? If I use LEN/LENGTH it gives me the current length of given entry (which actually is the current length of the varchar). But I want to get the maximum length of this concrete varchar (NOT the varchar(max) - this shows maximum length of a varchar in general - like ~20000 ).I hope I did not make it too complex ;)All the best,Joseph A. Habdank
I want to read a String - character by character.I mean If the string is 'SAMPLE' then I want to go to each and every character in it 'S','A','M','P','L','E' to compare with another string for equality. I hope I am clear.Is there a way that I can read it?