I am using SQL Server 2005 Express and one of the fields in my table has the UNIQUE constraint. When I try inserting a duplicate nothing gets inserted in the table, but no error is thrown. Where can I do a check for this? I would like to popup a message to the user indicating that the insert failed to the the unique constraint. I have configured the SQLDataSource with Insert/Update/Delete so the actual insert is done behind the scenes.
I'm trying to run a DTS package, and cant find an easy direct way to run it. It seems like the easiest solution would be to throw a custom error- then the server notices the error and runs a custom job, and the custom job runs the DTS. This is a roundabout way, but seems like it would be the simplest solution.Anyways, how do I throw the error in my code? Do I just write a throw 3829 statement? Also maybe this is a very bad way to do this- any suggestions?
I am using Data flow object in that I take OleDb as Source and Sql Server As destination. Without enabling Package configuration everything is working fine data is moving from Source to destination.
But When i apply Package Confiiguration It throw Error And My OleDb Source object Convert in to RED Color. I cant able to track This errror
My team is starting to implement error handling in our sprocs. One question we have is whether or not to use unique error numbers for custom errors (ie Errors we throw after doing some sort of validity check, not SQL Server errors). For example, we might check the value of a parameter and then throw an error that says "Parameter State_Date must be less than today, please retry".
We are using SQL Server 2012 and will be using the THROW statement, not RAISERROR, so we don't HAVE to put the numbers in sys.messages. Also, we are going to log the errors in a table, along with the error message, sproc name, line number, etc.
Is it useful to maintain a custom list of error numbers and messages? Or is it just as useful to use one standard error number and add a custom error message (which we can then search for in our code, or use the sproc name & line number we logged)? And if it is worth maintaining a list of numbers plus messages, should we go ahead and put them in sys.messages?
I'm looking for help on how to throw a page so that when printing duplex (both sides) a group will start on an odd page number eg 1, 3, 7, 9 etc. My large report needs to be split up into departments. I don't want a department starting on the reverse side of a sheet of paper.
When I execute the SQL Script in SQL Server Management Studio, everything is just fine. Â I have included this SP in a VS 2012 Database Project, and when I build, I get the error 'Incorrect Syntax near Throw'. Â The Database project is set to SQL 2012. Â I did try putting a ';' in front of the THROW as has been suggested on other threads. is this a case where we will have to abandon use of VS 2012 Database integration?
I'm using Decimal.MinValue in SqlParameter( SqlDbType.Decimal ) to execute a stored procedure. But, I receive this stack trace exception: System.OverflowException: Conversion overflows. at System.Data.SqlTypes.SqlDecimal.ToDecimal() at System.Data.SqlTypes.SqlDecimal.get_Value() at System.Data.SqlClient.TdsParser.AdjustDecimalScale(Decimal value, Int32 newScale) at System.Data.SqlClient.TdsParser.TdsExecuteRPC(_SqlRPC[] rpcArray, Int32 timeout, Boolean inSchema, SqlNotificationRequest notificationRequest, TdsParserStateObject stateObj) 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() I have changed Decimal.MinValue to Decimal.Zero to resolve my trouble. But, Why Decimal.MinValue throw OverflowException? Isn't Decimal.MinValue valid SqlDbType.Decimal type?
Hi everybody, I have a good question. If anybody know help me.
Every body know that SQL Server has SQL SERVER LOG 1- Current and 6 - Archives I'm shure every DBA look inside every day. And we can note inside a lot of many adds looks like this one:
2000-09-28 10:23:42.85 spid10 Starting up database 'pubs'.
So, we can see that 'spid10' had some activity at that day, but HOW TO GET INFORMATION about WHO IS IT - Login name or Username at least. Any idea will be appreciated
This is my code connect to SQL Server SqlConnection con = new SqlConnection("Data Source=OIT;Initial Catalog=big_db;User ID=sa; Password="); SqlDataAdapter cmd = new SqlDataAdapter("select * from myDB", con); SqlCommand sqlCmd = new SqlCommand(); DataTable dt = new DataTable(); cmd.Fill(dt); // It throw exception When myDB table have a lot of data, it throw exception like this : "It reached the time-out. Did the time-out period pass before completing the operation or the server doesn't respond. ". I config TCP/IP for SQL Server is Enable, but it throws SqlException too. How can I do? Help me please!!!
I have a report runs without any problem (Url access) and export to other format also without any problem. Only when I export the same report to Excel, the following error occurs in the reportserver log and its throws an error on screen.
I have found out it is down to a table grouping on a data field.
The report conatins a list and the list has a details group, which grouping the data by week. A table is sit inside the list to report the data. Within the table, there is a group list which is grouped by a field from the dataset.
I have the actual RDL and RDL.data file if anyone like the challenge.
Here is the error from the report server log:
--------------------------------------------------------------------------------------------------------------------------------------------- aspnet_wp!library!1!01/11/2008-14:54:49:: i INFO: Call to RenderNext('/ExportExcelProblemReport' ) aspnet_wp!reportrendering!1!01/11/2008-14:54:50:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.WrapperReportRenderingException: An error occurred during rendering of the report., ; Info: Microsoft.ReportingServices.ReportProcessing.WrapperReportRenderingException: An error occurred during rendering of the report. ---> Microsoft.ReportingServices.ReportRendering.ReportRenderingException: An error occurred during rendering of the report. ---> System.InvalidOperationException: Operation is not valid due to the current state of the object. at Microsoft.ReportingServices.ReportRendering.TableGroupCollection.get_Item(Int32 index) at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.GetReferenceInTable(TableHeaderFooterRows header, TableHeaderFooterRows footer, TableGroupCollection tableGroups, TableRowsCollection detailRows, IntList indexPath, Int32 startIndex) at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.GetReference(ReportItem dataRegion, IntList indexPath, Int32 startIndex) at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.GetReference(ReportItem dataRegion, IntList indexPath, Int32 startIndex) at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.GetReference(ReportItem dataRegion, IntList indexPath, Int32 startIndex) at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.CollectHiddenReportItems(IntList indexPath, PageReportItems& pageRI) at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.PreScanPage(ReportItem parentDataRegion, Boolean isPageNeedsEvaluation, PageLayout& pageLayout, PageReportItems& pageRIItems, Stack& dynamicLayoutStack, Hashtable& rowHeightStateList, OutlineRenderStates[]& verticalOutlineStates, OutlineRenderStates[]& horizontalOutlineStates) at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderStaticPage(ReportItem parentDataRegion, PageLayout pageLayout, Hashtable formulaRIMap, Int32 currentPageNumber, PageReportItems& pageRIItems, Stack& dynamicLayoutStack) at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderPageLayout(PageLayout pageLayout, Int32& currentPageNumber, Stack& stack) at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderPageCollection(PageCollection pageCollection, Int32& currentPageNumber, Stack& stack, PageLayout& lastPageLayout) at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderOrCountDynamicPage(Stack& renderDynamicStack, Int32& currentPageNumber, Int32 stackTop, PageLayout& lastPageLayout, Action action) at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderPageCollection(PageCollection pageCollection, Int32& currentPageNumber, Stack& stack, PageLayout& lastPageLayout) at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.GenerateWorkSheets() at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.GenerateMainSheet() at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderExcelWorkBook(CreateAndRegisterStream createAndRegisterStream) at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.ProcessReport(CreateAndRegisterStream createAndRegisterStream) at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.Render(Report report, NameValueCollection reportServerParameters, NameValueCollection deviceInfo, NameValueCollection clientCapabilities, EvaluateHeaderFooterExpressions evaluateHeaderFooterExpressions, CreateAndRegisterStream createAndRegisterStream) --- End of inner exception stack trace --- at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.Render(Report report, NameValueCollection reportServerParameters, NameValueCollection deviceInfo, NameValueCollection clientCapabilities, EvaluateHeaderFooterExpressions evaluateHeaderFooterExpressions, CreateAndRegisterStream createAndRegisterStream) at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RenderSnapshot(IRenderingExtension renderer, CreateReportChunk createChunkCallback, RenderingContext rc, GetResource getResourceCallback) --- End of inner exception stack trace --- aspnet_wp!webserver!1!01/11/2008-14:54:50:: e ERROR: Reporting Services error Microsoft.ReportingServices.Diagnostics.Utilities.RSException: An error occurred during rendering of the report. ---> Microsoft.ReportingServices.ReportProcessing.WrapperReportRenderingException: An error occurred during rendering of the report. ---> Microsoft.ReportingServices.ReportRendering.ReportRenderingException: An error occurred during rendering of the report. ---> System.InvalidOperationException: Operation is not valid due to the current state of the object. at Microsoft.ReportingServices.ReportRendering.TableGroupCollection.get_Item(Int32 index) at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.GetReferenceInTable(TableHeaderFooterRows header, TableHeaderFooterRows footer, TableGroupCollection tableGroups, TableRowsCollection detailRows, IntList indexPath, Int32 startIndex) at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.GetReference(ReportItem dataRegion, IntList indexPath, Int32 startIndex) at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.GetReference(ReportItem dataRegion, IntList indexPath, Int32 startIndex) at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.GetReference(ReportItem dataRegion, IntList indexPath, Int32 startIndex) at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.CollectHiddenReportItems(IntList indexPath, PageReportItems& pageRI) at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.PreScanPage(ReportItem parentDataRegion, Boolean isPageNeedsEvaluation, PageLayout& pageLayout, PageReportItems& pageRIItems, Stack& dynamicLayoutStack, Hashtable& rowHeightStateList, OutlineRenderStates[]& verticalOutlineStates, OutlineRenderStates[]& horizontalOutlineStates) at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderStaticPage(ReportItem parentDataRegion, PageLayout pageLayout, Hashtable formulaRIMap, Int32 currentPageNumber, PageReportItems& pageRIItems, Stack& dynamicLayoutStack) at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderPageLayout(PageLayout pageLayout, Int32& currentPageNumber, Stack& stack) at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderPageCollection(PageCollection pageCollection, Int32& currentPageNumber, Stack& stack, PageLayout& lastPageLayout) at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderOrCountDynamicPage(Stack& renderDynamicStack, Int32& currentPageNumber, Int32 stackTop, PageLayout& lastPageLayout, Action action) at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderPageCollection(PageCollection pageCollection, Int32& currentPageNumber, Stack& stack, PageLayout& lastPageLayout) at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.GenerateWorkSheets() at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.GenerateMainSheet() at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderExcelWorkBook(CreateAndRegisterStream createAndRegisterStream) at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.ProcessReport(CreateAndRegisterStream createAndRegisterStream) at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.Render(Report report, NameValueCollection reportServerParameters, NameValueCollection deviceInfo, NameValueCollection clientCapabilities, EvaluateHeaderFooterExpressions evaluateHeaderFooterExpressions, CreateAndRegisterStream createAndRegisterStream) --- End of inner exception stack trace --- at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.Render(Report report, NameValueCollection reportServerParameters, NameValueCollection deviceInfo, NameValueCollection clientCapabilities, EvaluateHeaderFooterExpressions evaluateHeaderFooterExpressions, CreateAndRegisterStream createAndRegisterStream) at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RenderSnapshot(IRenderingExtension renderer, CreateReportChunk createChunkCallback, RenderingContext rc, GetResource getResourceCallback) --- End of inner exception stack trace --- at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RenderSnapshot(IRenderingExtension renderer, CreateReportChunk createChunkCallback, RenderingContext rc, GetResource getResourceCallback) at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RenderSnapshot(CreateReportChunk createChunkCallback, RenderingContext rc, GetResource getResourceCallback) at Microsoft.ReportingServices.Library.RenderSnapshotAction.Render() at Microsoft.ReportingServices.Library.RSService.RenderFromSessionNoCache(CatalogItemContext reportContext, ClientRequest session, RenderingResult& result) at Microsoft.ReportingServices.Library.RSService.RenderFromSession(CatalogItemContext reportContext, ClientRequest session, Warning[]& warnings, ParameterInfoCollection& effectiveParameters) at Microsoft.ReportingServices.Library.RSService.RenderNext(CatalogItemContext reportContext, ClientRequest session, Warning[]& warnings, ParameterInfoCollection& effecectiveParameters, String[]& secondaryStreamNames) at Microsoft.ReportingServices.Library.RenderNextCancelableStep.Execute() at Microsoft.ReportingServices.Diagnostics.CancelablePhaseBase.ExecuteWrapper() --- End of inner exception stack trace --- at Microsoft.ReportingServices.Diagnostics.CancelablePhaseBase.ExecuteWrapper() at Microsoft.ReportingServices.Library.RenderNextCancelableStep.RenderNext(RSService rs, CatalogItemContext reportContext, ClientRequest session, JobType type, Warning[]& warnings, ParameterInfoCollection& effectiveParameters, String[]& secondaryStreamNames) at Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderReport(HttpResponseStreamFactory streamFactory) at Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.DoStreamedOperation(StreamedOperation operation) at Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderItem(ItemType itemType) at Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderPageContent() at Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderPage()
Maybe I am mistaken ( most likely ). But I am missing a dataflow task, which would do something similar like Throw Execption.
The project I am currently working on needs to validate a lot of different data. And sometimes incorrect data ( corrupted, incomplete or unexepected ) is coming from the source system. In case of this I need to trigger an error and discard the complete row or batch depending on the situation.
For example: In our source system we have some flag fields. And certain combinations of flags are not logical (business rules) but the system allows data to be inputted in this unlogical ways. And because we are creating the system I am expecting some combinations flags which are allowed but not properly defined in the specification.
So when I use a conditional split, I want the default output to trigger an error instead of an output.
I have a dataset that is between 40-50K records that has to go through a process that is pre-defined. SSIS works just fine with the smaller sets even up to 20K but this job keeps blowing up saying something along the lines of cannot write to recordset destination. Does this make sense to anyone? The sever is a 2 processor with 2GB of ram. Physical memory usage spikes to about 1.6GB during the run but the processor never really gets above 30% usage. Does this product just not scale yet?
I receive the following error message when I try to use the Bulk Insert Task to load BCP data into a table:
Error: 0xC002F304 at Bulk Insert Task, Bulk Insert Task: An error occurred with the following error message: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.The bulk load failed. The column is too long in the data file for row 1, column 4. Verify that the field terminator and row terminator are specified correctly.Bulk load data conversion error (overflow) for row 1, column 1 (rowno).".
Task failed: Bulk Insert Task
In SSMS I am able to issue the following command and the data loads into a TableName table with no error messages: BULK INSERT TableName FROM 'C:DataDbTableName.bcp' WITH (DATAFILETYPE='widenative');
What configuration is required for the Bulk Insert Task in SSIS to make the data load? BTW - the TableName.bcp file is bulk copy file as bcp widenative data type. The properties of the Bulk Insert Task are the following: DataFileType: DTSBulkInsert_DataFileType_WideNative RowTerminator: {CR}{LF}
Any help getting the bcp file to load would be appreciated. Let me know if you require any other information, thanks for all your help. Paul
Hello I have a problem with setting relations properly when inserting data using adonet. Already have searched for a solutions, still not finding a mistake... Here's the sql management studio diagram :
and that causes (at line 67):"The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Question_SurveyTemplate". The conflict occurred in database "ankietyzacja", table "dbo.SurveyTemplate", column 'id'. The statement has been terminated. at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount) at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount) at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping) at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping) at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable) at AnkietyzacjaWebService.Service1.createSurveyTemplate(Object[] o) in J:\PL\PAI\AnkietyzacjaWebService\AnkietyzacjaWebServicece\Service1.asmx.cs:line 397"
Could You please tell me what am I missing here ? Thanks a lot.
Is there a way to avoid entering column names in the excel template for me to create an excel file froma dynamic excel using openrowset. I have teh following code but it works fien when column names are given ahead of time. If I remove the column names from the template and just to Select * from the table and Select * from sheet1 then it tells me that column names donot match. Server: Msg 213, Level 16, State 5, Line 1Insert Error: Column name or number of supplied values does not match table definition. here is my code... SET @sql1='select * from table1'SET @sql2='select * from table2' IF @File_Name = '' Select @fn = 'C:Test1.xls' ELSE Select @fn = 'C:' + @File_Name + '.xls' -- FileCopy command string formation SELECT @Cmd = 'Copy C:TestTemplate1.xls ' + @fn -- FielCopy command execution through Shell Command EXEC MASTER..XP_CMDSHELL @cmd, NO_OUTPUT -- Mentioning the OLEDB Rpovider and excel destination filename set @provider = 'Microsoft.Jet.OLEDB.4.0' set @ExcelString = 'Excel 8.0;HDR=yes;Database=' + @fn exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT * FROM [Sheet1$]'') '+ @sql1 + '') exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT * FROM [Sheet2$]'') '+ @sql2 + ' ')
I am working with parent child tables and want to populate the primary key on insert so that the user does not have to enter this for each record. Here is my codeInsertCommand="INSERT INTO [Awards] ([UFID], [DateAwarded], [Amount], [AwardingAgency]) Select UFID, @DateAwarded, @Amount, @AwardingAgency from master where GatorlinkName = @LoginName" <InsertParameters><asp:Parameter Name="LoginName" Type="String" /> <asp:Parameter Name="strusername" Type="String" /> <asp:Parameter Name="UFID" Type="String" /> <asp:Parameter Name="DateAwarded" Type="DateTime" /> <asp:Parameter Name="Amount" Type="Decimal" /> <asp:Parameter Name="AwardingAgency" Type="String" /> </InsertParameters> The UFID field is the only field that should be populated from SQL data the others are coming from a form view insert form. When I run an insert I get no error but the insert does not happen. I know that the @LoginName works since I am using this same logic in my select statement. Thanks in advance for your help,Ken
I have a sp that when executed inserts data into two tables(shown below). The sp works fine when the correct information is inserted into the tables but when you try and insert data that breaks the constraints in the table it obviously errors, but it seems to inert a new row in to the tmptimesheet table(error message shown below). When you open the tmptimesheet table there isn’t the row of a data there but if you run this (SELECT IDENT_CURRENT('tmptimmesheets') after the error it will come back with a id one higher than what’s in the table. Then the next time you run the sp a record will be inserted into the tmptimesheet table and not in to the tmptimsheethours table!? I’m at a total lost at what to do can someone please help!?
CREATE TABLE [dbo].[tmptimesheets]( [TimesheetID] [int] IDENTITY(1,1) NOT NULL, [Placementid] [int] NOT NULL, [Periodstarting] [datetime] NOT NULL, [createdon] [datetime] NOT NULL, [createduserid] [int] NOT NULL, [Issued] [nchar](1) COLLATE Latin1_General_CI_AS NOT NULL, [ReadyForBilling] [nchar](1) COLLATE Latin1_General_CI_AS NOT NULL, [Reject] [nchar](1) COLLATE Latin1_General_CI_AS NULL, [Comments] [text] COLLATE Latin1_General_CI_AS NULL, [Rate] [nchar](1) COLLATE Latin1_General_CI_AS NOT NULL, CONSTRAINT [PK_tmptimesheets] PRIMARY KEY CLUSTERED ( [TimesheetID] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY], CONSTRAINT [IX_tmptimesheets_1] UNIQUE NONCLUSTERED ( [Placementid] ASC, [Periodstarting] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
CREATE TABLE [dbo].[tmptimesheethours]( [TmpTimesheethourid] [int] IDENTITY(1,1) NOT NULL, [Timesheetid] [int] NOT NULL, [applicantid] [int] NOT NULL, [Workedon] [datetime] NOT NULL, [Hoursworked] [numeric](10, 2) NOT NULL, [performancevalueid] [int] NOT NULL, [Reject] [ntext] COLLATE Latin1_General_CI_AS NULL, [Breaks] [numeric](10, 2) NOT NULL, CONSTRAINT [PK_tmptimesheethours] PRIMARY KEY CLUSTERED ( [TmpTimesheethourid] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO USE [Pronet_TS] GO ALTER TABLE [dbo].[tmptimesheethours] WITH NOCHECK ADD CONSTRAINT [FK_TimesheetHours_Timesheets] FOREIGN KEY([Timesheetid]) REFERENCES [dbo].[tmptimesheets] ([TimesheetID]) GO ALTER TABLE [dbo].[tmptimesheethours] CHECK CONSTRAINT [FK_TimesheetHours_Timesheets]
Error Message Msg 2627, Level 14, State 1, Procedure sp_tmptimesheet_insert_day, Line 40 Violation of UNIQUE KEY constraint 'IX_tmptimesheets_1'. Cannot insert duplicate key in object 'dbo.tmptimesheets'. The statement has been terminated. Msg 547, Level 16, State 0, Procedure sp_tmptimesheet_insert_day, Line 65 The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TimesheetHours_Timesheets". The conflict occurred in database "Pronet_TS", table "dbo.tmptimesheets", column 'TimesheetID'. The statement has been terminated. Msg 547, Level 16, State 0, Procedure sp_tmptimesheet_insert_day, Line 86 The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TimesheetHours_Timesheets". The conflict occurred in database "Pronet_TS", table "dbo.tmptimesheets", column 'TimesheetID'. The statement has been terminated. Msg 547, Level 16, State 0, Procedure sp_tmptimesheet_insert_day, Line 108 The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TimesheetHours_Timesheets". The conflict occurred in database "Pronet_TS", table "dbo.tmptimesheets", column 'TimesheetID'. The statement has been terminated. Msg 547, Level 16, State 0, Procedure sp_tmptimesheet_insert_day, Line 130 The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TimesheetHours_Timesheets". The conflict occurred in database "Pronet_TS", table "dbo.tmptimesheets", column 'TimesheetID'. The statement has been terminated. Msg 547, Level 16, State 0, Procedure sp_tmptimesheet_insert_day, Line 153 The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TimesheetHours_Timesheets". The conflict occurred in database "Pronet_TS", table "dbo.tmptimesheets", column 'TimesheetID'. The statement has been terminated. Msg 547, Level 16, State 0, Procedure sp_tmptimesheet_insert_day, Line 178 The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TimesheetHours_Timesheets". The conflict occurred in database "Pronet_TS", table "dbo.tmptimesheets", column 'TimesheetID'. The statement has been terminated. Msg 547, Level 16, State 0, Procedure sp_tmptimesheet_insert_day, Line 200 The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TimesheetHours_Timesheets". The conflict occurred in database "Pronet_TS", table "dbo.tmptimesheets", column 'TimesheetID'. The statement has been terminated. Msg 3902, Level 16, State 1, Procedure sp_tmptimesheet_insert_day, Line 223 The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
I'm trying to use Bulk insert for the first time and getting the following error. I think it might have something to do with my Format File and from the error msg there's a conversion error for the first column. In my database the Field is nvarchar(6) so my best guess is to use SQLNChar for the first column. I've checked the end of each line is CR LF therefore the is correct for line 7 right?
Msg 4863, Level 16, State 1, Line 1 Bulk load data conversion error (truncation) for row 1, column 1 (ASXCode). Msg 7399, Level 16, State 1, Line 1 The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error. Msg 7330, Level 16, State 2, Line 1 Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
BULK INSERTtbl_ASX_Data_temp FROM 'M:DataASXImportTest.txt' WITH (FORMATFILE='M:DataASXSQLFormatImport.Fmt')
Hi All Here is the error I am getting: "No mapping exists from object type System.Web.UI.WebControls.DropDownList to a known managed provider native type." Here is my code:Protected Sub btnSave_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSave.Click'set variables for Items to save to Time SheetDim strUserName As String '@usernameDim strWeek As String '@weekDim strDate As String '@dateDim strStartTime As String '@starttimeDim strEndTime As String '@endtimeDim intHeatTicket As Integer '@heatticketDim strDesc As String '@descriptionDim strTakenAs As String '@takenasDim strDinner As String '@dinnerDim dblHours As Double '@hoursDim dblRate As Double '@rateDim strDueDate As String '@duedate'set variables for SPstrUserName = User.Identity.NamestrWeek = CStr(ddlWeek.SelectedItem.Text)strDate = CStr(lblSelectDate.Text)strStartTime = txtStartTime.TextstrEndTime = txtEndTime.TextintHeatTicket = txtHeatTicket.TextstrDesc = txtReason.TextstrTakenAs = CStr(ddlTakenAs.SelectedItem.Text)strDinner = CStr(ddlDinner.SelectedItem.Text)dblHours = txtHours.TextdblRate = CDbl(ddlRate.SelectedItem.Text)strDueDate = CStr(ddlDueDate.SelectedItem.Text)'set connection stringDim errstr As String = ""Dim conn = New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Integrated Security=True;User Instance=True")'set parameters for SP to create new blank time sheetDim cmdcommand = New SqlCommand("InsertReportLineItem", conn)cmdcommand.commandtype = CommandType.StoredProcedurecmdcommand.parameters.add("@LineUserName", strUserName)cmdcommand.parameters.add("@LineWeek", strWeek)cmdcommand.parameters.add("@LineDate", strDate)cmdcommand.parameters.add("@LineStartTime", strStartTime)cmdcommand.parameters.add("@LineEndTime", strEndTime)cmdcommand.parameters.add("@LineHeatTicket", intHeatTicket)cmdcommand.parameters.add("@LineTicketDescription", strDesc)cmdcommand.parameters.add("@LineTakenAs", strTakenAs)cmdcommand.parameters.add("@LineDinnerPremium", ddlDinner)cmdcommand.parameters.add("@LineRate", dblHours)cmdcommand.parameters.add("@Rate", dblRate)cmdcommand.parameters.add("@LineReportDueDate", ddlDueDate)Try'open connection hereconn.Open()'Execute stored proccmdcommand.ExecuteNonQuery()Catch ex As Exceptionerrstr = ""'An exception occured during processing.errstr = "Exception: " & ex.Message.ToString()'MsgBox("This is your Error: " & errstr, MsgBoxStyle.Exclamation)Finally'close the connection immediatelyconn.Close()End TryEnd Sub Here is my Stored Procedure:
I am trying to insert information into a database calls FSI and a table called Racks. Below is the error I recieve.
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.OleDb.OleDbException: Syntax error in INSERT INTO statement.
The sub with the error is inserted at the bottom of this post. I have used the exact same code to inset into other tables and it worked but for some reason this one is not working. If you would like to see the entire file please let me know and I will email it to you. or post it on here. The field names for the table are (beside the field name is datatype. Im using an Access table for now). RackID - autonumber Container - text Date - Date/Time CustomerID - number (all numbers are set to interger) Comments - text Size - number Bars - number If you need any further database information it can be seen at http://paws.wcu.edu/tf32761/database/tool_database.aspx The link for the actuall page is http://paws.wcu.edu/tf32761/fsi/racks.aspx feel free to insert dumby data to see the stack trace and the entire error message.
What is wrong with the syntax of my sql insert statement? 1 '--------------------------------------------- 2 ' name: BuildCommandObject() As OleDbCommand 3 '--------------------------------------------- 4 Function BuildCommandObject(ByVal strDate As String, ByVal strContainer As String, ByVal decCustomerID As Decimal, ByVal strComments As String, ByVal decSize As Decimal, ByVal decBars As Decimal) As OleDbCommand 5 6 Dim strSQL As String 7 Dim objCommand As New OleDbCommand() 8 9 'Build sql string 10 strSQL = "INSERT INTO Racks " & _ 11 "(Date, Container, CustomerID, Comments, Size, Bars)" & _ 12 " VALUES(?, ?, ?, ?, ?, ?)" 13 14 Trace.Warn("strSQL=" & strSQL) 15 16 objCommand.CommandText = strSQL 17 18 'Build parameters and add to parameters collection 19 objCommand.Parameters.Add("@Date", OleDbType.VarChar, 50).Value = strDate 20 objCommand.Parameters.Add("@Container", OleDbType.VarChar, 50).Value = strContainer 21 objCommand.Parameters.Add("@CustomerID", OleDbType.Decimal, 50).Value = decCustomerID 22 objCommand.Parameters.Add("@Comments", OleDbType.VarChar, 255).Value = strComments 23 objCommand.Parameters.Add("@fldItemNumber", OleDbType.Decimal, 50).Value = decSize 24 objCommand.Parameters.Add("@fldItemNumber", OleDbType.Decimal, 50).Value = decBars 25 26 Return objCommand 27 28 End Function
Thanks for the assistance. Hope everyone has a great Thanksgiving holiday. TJ
I've been staring at this code and I am at a lost as to what is the problem, Could someone look at it and let me know? The error I get is "Error in INSERT INTO syntax" thanks1 Dim sConnStr As String = ConfigurationManager.ConnectionStrings("accmon").ConnectionString 2 Dim MyConn As New OleDbConnection(sConnStr) 3 4 Dim MySQL As String = "INSERT INTO icrRenewal (month, year, oa, omb_number, current_Inventory, 60fr) " & _ 5 "Values (@month, @year, @oa, @omb_number, @currentInv, @60fr)" 6 Dim Cmd As New OleDbCommand(MySQL, MyConn) 7 8 With Cmd.Parameters 9 .Add(New OleDbParameter("@month", ddlMonth.SelectedItem.Value)) 10 .Add(New OleDbParameter("@year", ddlYear.SelectedItem.Value)) 11 .Add(New OleDbParameter("@oa", ddlOA.SelectedItem.Value)) 12 .Add(New OleDbParameter("@omb_number", txtOMBNumber.Text)) 13 .Add(New OleDbParameter("@currentInv", txtcurrentInv.Text)) 14 .Add(New OleDbParameter("@60fr", txt60fr.Text)) 15 End With 16 MyConn.Open() 17 Cmd.ExecuteNonQuery() 18 MyConn.Close() 19
I have been learning how to retrieve data from my SQL database using C#/Asp.Net. I am able to read the data and I am able to update my data. I am now wanting to insert into my database. I think I've gotten everything under control except for one error. It's something I don't understand and would like some assistance with.I assumed that as soon as I inserted something into a table that the auto-increment field would auto increment. From the error I take it that I am wrong. The INSERT statement conflicted with the FOREIGN KEY constraint "FK_inventoryTbl_categoryTbl". The conflict occurred in database "Chaos", table "dbo.categoryTbl", column 'categoryID'.The statement has been terminated.That foreign key is categoryID but it's a number I am trying to insert that does exist in the other table, categoryID 11. The auto increment field is ID, in the inventoryTbl. The id of a new item that gets entered is auto incremented for the new item. I am not sure whether it's an error related to that I am not inserting a number into the id field of a newly added item into the table or whether it's come kind of constraint rule that I do not understand concerning Insertion and relationships between two tables. Here is the code: protected void bttnApplyChanges_Click(object sender, EventArgs e) { string connectionString = ConfigurationManager.ConnectionStrings["ChaosConnectionString1"].ConnectionString; SqlConnection conn = new SqlConnection(connectionString); conn.Open(); SqlCommand comm = new SqlCommand("INSERT INTO inventoryTbl (categoryID, item, description, price, image_url, qty, page_url) VALUES (@category, @item, @desc, @price, @imageurl, @quantity, @page)", conn); // Add command parameters comm.Parameters.Add("@category", System.Data.SqlDbType.SmallInt); comm.Parameters["@category"].Value = txtbx_itemCategory.Text; comm.Parameters.Add("@item", System.Data.SqlDbType.NVarChar); comm.Parameters["@item"].Value = txtbx_itemName.Text; comm.Parameters.Add("@desc", System.Data.SqlDbType.NVarChar); comm.Parameters["@desc"].Value = txtbx_ItemDesc.Text; comm.Parameters.Add("@price", System.Data.SqlDbType.Money); comm.Parameters["@price"].Value = txtbx_ItemPrice.Text; comm.Parameters.Add("@imageurl", System.Data.SqlDbType.NVarChar); comm.Parameters["@imageurl"].Value = txtbx_itemImgUrl.Text; comm.Parameters.Add("@quantity", System.Data.SqlDbType.SmallInt); comm.Parameters["@quantity"].Value = txtbx_qty.Text; comm.Parameters.Add("@page", System.Data.SqlDbType.NVarChar); comm.Parameters["@page"].Value = txtbxPageUrl.Text;
Msg 8101, Level 16, State 1, Line 10 An explicit value for the identity column in table '@tbl' can only be specified when a column list is used and IDENTITY_INSERT is ON. and this is my query..Declare @tbl table ( Id int identity(1,1), PlanName varchar(200), ClientPlan Varchar(10), AddDate datetime, LastchangeDate datetime )
Insert into @tbl Select c.Pl_Id, c.Pl_Name, c.Pl_Number, c.pl_Create_Date, c.pl_Modify_Date
--pf.PF_LI_ID, -- f.FundId From Plan c Where
(c.PL_CL_ID = 396) OR (c.PL_CL_ID = 410) OR (c.PL_CL_ID = 411) OR (c.PL_CL_ID = 412) OR (c.PL_CL_ID = 413) OR (c.PL_CL_ID = 414)
Hello there,I have now looked at his code for an hour I think, and I cant simpy not see what's wrong: 1 Try 2 'aspnet_Membership 3 nonqueryCommand.CommandText = "CREATE TABLE aspnet_Membership (ApplicationId uniqueidentifier NOT NULL, UserId uniqueidentifier NOT NULL PRIMARY KEY, Password nvarchar(128) NOT NULL, PasswordFormat integer NOT NULL, PasswordSalt nvarchar(128) NOT NULL, MobilePIN nvarchar(16), Email nvarchar(256), LoweredEmail nvarchar(256), PasswordQuestion nvarchar(256), PasswordAnswer nvarchar(128), IsApproved bit NOT NULL, IsLockedOut bit NOT NULL, CreateDate datetime NOT NULL, LastLoginDate datetime NOT NULL, LastPasswordChangedDate datetime NOT NULL, LastLockoutDate datetime NOT NULL, FailedPasswordAttemptCount integer NOT NULL, FailedPasswordAttemptWindowStart datetime NOT NULL, FailedPasswordAnswerAttemptCount integer NOT NULL, FailedPasswordAnswerAttemptWindowStart datetime NOT NULL, Comment ntext)" 4 Console.WriteLine(nonqueryCommand.CommandText) 5 Session("Tables") = Session("Tables") + "Number of Rows Affected with table aspnet_Membership is: " + nonqueryCommand.ExecuteNonQuery().ToString + "<br />" 6 7 nonqueryCommand.CommandText = "INSERT INTO aspnet_Membership(ApplicationId, UserId, Password, PasswordFormat, PasswordSalt, Email, LoweredEmail, IsApproved, IsLockedOut, CreateDate, LastLoginDate, LastPasswordChangedDate, LastLockoutDate, FailedPasswordAttemptCount, FailedPasswordAttemptWindowStart, FailedPasswordAnswerAttemptCount, FailedPasswordAnswerAttemptWindowStart) VALUES ('69272d43-c1d4-46d5-af8b-5f638882fb55','b0e198c6-1fbb-4aa6-82a6-32c2bc60d097','5gCmCptv9egj+IkDHk1yeozjp6I=','1','cKURew9OVHBmK46GTl8ykg==','din@email.dk','din@email.dk','True','False','03-03-2008 16:05:51','05-06-2008 12:28:37','16-05-2008 22:58:28','01-01-1754 00:00:00','0','01-01-1754 00:00:00','0','01-01-1754 00:00:00')" 8 Console.WriteLine(nonqueryCommand.CommandText) 9 10 Session("Tables") = Session("Tables") + "Number of Rows Affected with table aspnet_Membership is: " + nonqueryCommand.ExecuteNonQuery().ToString + "<br />" 11 12 Catch ex As SqlException 13 Session("FEJL") = Session("FEJL") + "<br />" + ex.ToString() + "<br />" 14 End Try
I this error show up:System.Data.SqlClient.SqlException: 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. 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.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at updates_100_2.Page_Init(Object sender, EventArgs e) in http://server//admin/NewSystem/1.0.0/2.aspx.vb:line 53 Anyone who can see what I'm doing wrong in this insert statement??
Here is my code: (I am only trying to insert one field. ):
Dim myConnectionString As String
If myConnectionString = "" Then myConnectionString = "server=Discovery3;database=master;trusted_Connection=true" End If
Dim myConnection As New Data.SqlClient.SqlConnection(myConnectionString) Dim myInsertQuery As String = "INSERT INTO tblMaster(TITLE) values('" & TextBox1.Text & "')" Dim myCommand As New Data.SqlClient.SqlCommand(myInsertQuery) myCommand.Connection = myConnection myConnection.Open() myCommand.ExecuteNonQuery() myCommand.Connection.Close()
I'm getting an error on the insert... I recently when to switch over from the @param.Oledb type and am now using the ? for the values.... Well I switched my updates and now am working on the inserts, but I am getting the error from this statement. I tried taking off the VALUES portion, but that didn't work either... queryString = "INSERT INTO [griff30_24_stats] ([W_Rec], [Opponent], [L_Rec], [points], [done]) VALUES(?, ?, ?, ?,)" Dim cmd as new OleDbCommand(queryString,dbConnection) cmd.Parameters.Add("W_Rec",OleDbType.VarChar).Value = "Y" ' ' '
When in Query Analyzer performing the following insert command on the PUBS database (SQL 7.0 sp1) "insert into address_list select au_lname + ',' + au_fname, address, city, state from authors" I receive the following error message: "Server: Msg 8152, Level 16, State 9, Line 1 String or binary data would be truncated. The statement has been terminated."
If I just run the select portion of the query, it works fine. Can anyone tell me what the problem is?
I have imported to sql my db and it worked fine. After i developed the diagram (relations in SQL S).
Now, with my .mde front-end i connected via-DSN and, again, it worked fine.
I started the tests and problems: 1. I have a relation 1 to 1 - in the .mde i have a ctlSeparator with both tables but i'm getting always the same error (3621)- and says that i can't insert a null value in my field (primary key in both table). Why?
2. Should i perform something to correct this problem. Sould i change (besides DAO to ADO) in my .mde?
I have a database using a sql backend and an access front end. I am trying to add employees to the employee table and I am getting the following error: Violation of Primary Key Contraint 'aaaatblEmployees_PK. Cannot insert duplicate key in object. I am going right to the access front end to add these employees to the table. I have this field autonumbered and it is not duplicating. What can be wrong. Thanks
