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 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 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
I have a database using a sql backend and an access front end, which was upsized. 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. The primary field is autonumbered in access and when it upsized to sql the identity increment was set to 1 automatically. Could this be conflicting with the field in access? I made a local table of the employee table and I was able to insert records fine. What can be wrong. Thanks
I cannot figure out why I am getting this error below. There are no keys defined in my table? I don't know what unique index 'Client_Key0'. means or is?
Msg 2601, Level 14, State 1, Line 4
Cannot insert duplicate key row in object 'dbo.AMGR_Client_Tbl' with unique index 'Client_Key0'.
Hello there, I made a page to add these fields (name, description, price, end_date) into a SQL database. I'm using ASP.NET 2.0 with C#, my database is SQL 2005. I did it without DetailsView or FormView, it consists of TextBoxes and a Calendar. Anyways, i wrote this code for Button1, which is the button that adds the data. protected void Button1_Click(object sender, EventArgs e) { SqlDataSource sql = LoginView1.FindControl("SqlDataSource1") as SqlDataSource; sql.Insert(); Response.Redirect("home.aspx"); } The parameters and insert statments and commands are written from the SqlDataSource Insert Query wizard. But I keep getting this error when trying to click Button1. Object must implement IConvertible. 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: Object must implement IConvertible.Source Error:
Line 31: { Line 32: SqlDataSource sql = LoginView1.FindControl("SqlDataSource1") as SqlDataSource; Line 33: sql.Insert(); Line 34: Response.Redirect("home.aspx"); Line 35: } Source File: c:Documents and SettingsDoublethink..!!My DocumentsVisual Studio 2005WebSitesSunnDarkProjectsell.aspx.cs Line: 33
Hi All, I have a form that inserts information into two tables in sql 2005 database. I have a unique key on the tables preventing duplicate information which works fine. The problem I have is that at the minute if a users tries to insert a duplicate row they just get the built in sql error message. Is there a way I can validate the information before if goes into the database and display perhaps a label telling the user of their error or is there a way I can customize the build in sql error message? I've had a search on various forums and sites and can't find any info that would help me. Thanks in advance Dave
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. Thanks in advanceEric
hii,,i am using asp.net 2005 and sql server 2005... i m using sql data source in my form,,i want to insert the details into a table using the insert command thr sqldatasource....one of the column name is service_w/lb, this is where i get an error.i am not able to insert the new data..here is my code:::: ______________________________________ <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:sm123_trackerConnectionString %>"SelectCommand="SELECT * FROM [Agency_Master]" InsertCommand="INSERT INTO [Agency_Master] ([Agency_Id], [Agency_Main_Contact], [Agency_Invoice_Contact], [Address], [Email_Address], [Phone], [Mobile], [Fax], [TimeZone_Id], [Agency_Name], [Service_w/LB], [Notes], [Technology_Used], [Status with LB]) VALUES (@Agency_Id, @Agency_Main_Contact, @Agency_Invoice_Contact, @Address, @Email_Address, @Phone, @Mobile, @Fax, @TimeZone_Id, @Agency_Name, @Service_w/LB, @Notes, @Technology_Used, @Status_with_LB)" OldValuesParameterFormatString="original_{0}" > _____________________________________ even if i put it as @[service_w/Lb] i get an error....here is the error i get __________________________________________ Incorrect syntax near 'nvarchar'.The name "Service_w" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.____________________ pls reply as soon as possible ,,,thnks in advance
hi all when i try to insert a record to sql table i have this error ncorrect syntax near 'japan'. the code part iscmdInsert = New SqlCommand("insert into empbill values(" & Val(eno.Text) & "," & contry1.Text , db) cmdInsert.ExecuteNonQuery() the value i entered is 123.japan i have 2 field in empbill (emp,cont) what happend
I am trying to create a form, and then insert the values entered by a user into a sql database, I have enclosed the page code below. Everything works except the data is not being inserted into the database, and i keep getting the default message in my error message section. I took this right from the quick start tutorial and started working with it, and keep getting an error.
I believe the error is located in the INSERT statement
MyConnection = New SqlConnection("server=localhost;database=planetauction;uid=planetauction;pwd=bean13")
If Not (IsPostBack) BindGrid() Page.DataBind() End If End Sub
Sub AddAuthor_Click(Sender As Object, E As EventArgs) Page.Validate() If Not Page.IsValid Return End If
Dim DS As DataSet Dim MyCommand As SqlCommand
If txtLastName.Value = "" Message.InnerHtml = "ERROR: Null values not allowed for Author ID, " & _ "Name or Phone" Message.Style("color") = "red" BindGrid() Return End If
Dim InsertCmd As String = "insert into users (txtLastName) values (@lastname)"
MyCommand = New SqlCommand(InsertCmd, MyConnection)
Catch Exp As SQLException If Exp.Number = 2627 Message.InnerHtml = "ERROR: A record already exists with the " & _ "same primary key" Else Message.InnerHtml = "ERROR: Could not add record, please ensure " & _ "the fields are correctly filled out" End If Message.Style("color") = "red"
End Try
MyCommand.Connection.Close()
BindGrid() End Sub
Sub BindGrid()
Dim MyCommand As SqlDataAdapter = new SqlDataAdapter( _ "select * from users", MyConnection)
Dim DS As DataSet = new DataSet() MyCommand.Fill(DS, "Users")
MyDataGrid.DataSource=DS.Tables("Users").DefaultView MyDataGrid.DataBind() End Sub
</script>
<body style="font: 10pt verdana">
<form runat="server" ID="Form1">
<h3><font face="Verdana">Inserting a Row of Data</font></h3>
Hi - I am trying to do the following simple insert that fails...
insert into tbloutgoingobms_hold select aOBMMessageID, nGlobalCaseID, nOBMailerID,'Food','05-JUN-2002' from tbloutgoingobms
The table that I am inserting into is a duplicate of the table I am selecting from plus I added two new columns - thus I am inserting constant values into those two new columns: 'Food','05-JUN-2002' . This insert works in Oracle but when I run it on SQL*Server I get the following error: An explicit value for the identity column in table 'tbloutgoingobms_hold' can only be specified when a column list is used and IDENTITY_INSERT is ON.
I used the command: SET IDENTITY_INSERT tbloutgoingobms_hold ON
The column that the 'Food' constant is going into is a varchar(10) and the date column is a datetime column.
Still no luck.... Help please - this cannot be such a difficult thing to do... :(
I apprecaite your hlep. I run this code and got an error message ----------------------------------------------------------- select customer_id ,company_name,CITY_AND_STATE, substring (city_and_state,1,CHARINDEX(',',city_and_state,0)-1)as city, substring (city_and_state,CHARINDEX(',',city_and_state,0)+2, 4) as state into validCustomers from customer
This gave me an error: ------------------------------------------------- Server: Msg 536, Level 16, State 3, Line 1 Invalid length parameter passed to the substring function. The statement has been terminated.
I'm doing a bulk insert from a text file to sql server 7 I'm getting an error:
Server: Msg 4867, Level 16, State 1, Line 1 Bulk insert data conversion error (overflow) for row 1, column 169 (LOT_WIDTH). Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'STREAM' reported an error. The provider did not give any information about the error. The statement has been terminated.
Now my lot-width field coming in is defined as a numeric 9(5). My table is defined as an INT.
Server: Msg 170, Level 15, State 1, Line 17 Line 17: Incorrect syntax near ')'.
When I try to execute this code from SQL Query Analyzer...
DECLARE @DPPNumberCursor INT DECLARE DPPNumberCursor Cursor for Select PPAP_ID from ppap where ppap_cancel <> "1" and ppap_close <> "1" and projectonhold <> "1"
OPEN DPPNumberCursor
Fetch Next From DPPNumberCursor
INTO @dppnumbercursor
While @@Fetch_Status = 0 Begin INSERT INTO APQPSubformTable (apqpsub_id) Values (@dppnumbercursor)
Bsically, I want to insert the number held in @dppnumbercursor in the APQPSub_id field.
I have an asp page that inserts several pieces of input into an SQL database. Page was working fine, and Im not sure what could have changed to cause the following error when data is entered into one of the fields:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'room'.
/Global.asp, line 20
where global.asp line 20 is oConn.execute cSql
In this case the text I input was: this room is big
I also tried input of fdsa, and recieved the following error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'fdsa'.
/Global.asp, line 20
More Details:
The field I am inserting this value into is a (nvarcar(1000),null).
None of the other fields on the page have a problem, and this is the last value insert into the database.
Any suggestions or ideas on where I should look to begin solving this issue.
