Hi
All as i have a class where i wrote a procedure for insert statement and i passed the value from the form and previously it was fine and now its giving the following error
" 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."
And
code is as follows
Code Snippet
Public Sub Purchase_Header(ByVal BillNo As VariantType, ByVal BillDate As Date, ByVal GRNNO As VariantType, ByVal GRNdate As Date, ByVal TOP As Integer, ByVal Supplier As Integer, ByVal TotalPurchasevalue As Double, ByVal TotalProductValue As Double, ByVal BillPaid As Date, ByVal BillDue As Date, ByVal NoOfSBills As Integer, ByVal noofbreceived As Integer, ByVal billstatus As Integer, ByVal paymentstatus As Integer, ByVal appstatus As Integer, ByVal recitstatus As Boolean, ByVal sbillstatus As Boolean, ByVal ccindicator As String)
MsgBox("Data saved successfully")
myConnection.Close()
and table structure is as follows and for date types i am passing them through date variables from textbox
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.
Hi All, I've looked through the forum hoping I'm not the only one with this issue but alas, I have found nothing so I'm hoping someone out there will give me some assistance. My problem is the case statement in my Insert Statement. My overall goal is to insert records from one table to another. But I need to be able to assign a specific value to the incoming data and thought the case statement would be the best way of doing it. I must be doing something wrong but I can't seem to see it.
Here is my code: Insert into myTblA (TblA_ID, mycasefield = case when mycasefield = 1 then 99861 when mycasefield = 2 then 99862 when mycasefield = 3 then 99863 when mycasefield = 4 then 99864 when mycasefield = 5 then 99865 when mycasefield = 6 then 99866 when mycasefield = 7 then 99867 when mycasefield = 8 then 99868 when mycasefield = 9 then 99855 when mycasefield = 10 then 99839 end, alt_min, alt_max, longitude, latitude ( Select MTB.LocationID MTB.model_ID MTB.elevation, --alt min null, --alt max MTB.longitude, --longitude MTB.latitude --latitude from MyTblB MTB );
The error I'm getting is: Incorrect syntax near '='.
I have tried various versions of the case statement based on examples I have found but nothing works. I would greatly appreciate any assistance with this one. I've been smacking my head against the wall for awhile trying to find a solution.
i'm having a problem with my project we need to make for school with asp.net & c#. the problem is i'm a newbie with c#. the project we need to make is a survey. so the problem is my first question of the survey works fine when i push the go to next it enters the data nice into my sql db and my second question loads, then I enter the answer for my second question i press go to next question and i get this error: " The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Antwoorden_Gebruikers". The conflict occurred in database "GIP_enquete", table "dbo.Gebruikers", column 'Gebruiker_ID'. " I don't know how i can fix this cause im to newbie to understand whats wrong.
here is a view of my code:
Code Snippet
using System; using System.Data; using System.Configuration; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; using enqueteTableAdapters;
public partial class _Default : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { int Vraag_ID = Convert.ToInt16(Request.QueryString["vraagid"]); VragenTableAdapter VraagAdapter = new VragenTableAdapter(); lblVraag.Text = Convert.ToString(VraagAdapter.GeefVraag(Vraag_ID));
if (Vraag_ID == 1) { pnlVraag1.Visible = true; }
if (Vraag_ID == 2) { pnlVraag2.Visible = true; }
} protected void btnVraag1_Click(object sender, EventArgs e) { //make a new user GebruikersTableAdapter GebruikerAdapter = new GebruikersTableAdapter(); DateTime Moment = System.DateTime.Now; GebruikerAdapter.GebruikerToevoegen(Moment); int GebruikerID = Convert.ToInt16(GebruikerAdapter.GeefGebruikerID(Moment)); this.ViewState.Add("gebruiker_id", GebruikerID);
//go to 2nd question Response.Redirect("Default.aspx?vraagid=2", true); }
protected void btnVraag2_Click(object sender, EventArgs e) { //get back the user from question 1 int GebruikerID = Convert.ToInt16(this.ViewState["gebruiker_id"]);
Ok, the following four lines are four lines of code that I'm running, I'll post the code and then explain my issue: sqlCommand = New SQLCommand("INSERT INTO Bulk (Bulk_Run, Bulk_Totes, Bulk_Drums, Bulk_Boxes, Bulk_Bags, Bulk_Bins, Bulk_Crates) VALUES (" & RunList(x,0) & ", " & Totes & ", " & Drums & ", " & Boxes & ", " & Bags & ", " & Bins & ", " & Crates & ")", Connection) sqlCommand.ExecuteNonQuery() sqlCommand = New SQLCommand("INSERT INTO Presort (Presort_Run, Presort_Totes, Presort_Drums, Presort_Boxes, Presort_Bags, Presort_Bins, Presort_Crates) VALUES (" & RunList(x,0) & ", " & Totes & ", " & Drums & ", " & Boxes & ", " & Bags & ", " & Bins & ", " & Crates & ")", Connection) sqlCommand.ExecuteNonQuery() The two tables (Bulk & Presort) are <b>exactly</b> the same. This includes columns, primary keys, IDs, and even permissions. If I run the last two liens (the INSERT INTO Presort) then it works fine without error. But whenever I run the first two lines (the INSERT INTO Bulk) I get the following error: Incorrect syntax near the keyword 'Bulk'. Anyone have any ideas, thanks
I am receiving the following error when trying to insert values from textboxes on a registration form:
Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.
Compiler Error Message: BC30205: End of statement expected.
Source Error:
Line 19: Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString) Line 20: Line 21: Dim queryString As String = "INSERT INTO [UserID_db] ([Code], [UserID], [Password], [Email1], [Name_First], [Name_Last], [Admin_level]) VALUES ('" & txtUserID.Text"', '" & txtUserID.Text"', '" & txtPassword.Text"', '" & txtEmail1.Text"', '" & txtFirstName"', '" & txtLastName.Text"', '" & txtAdminLevel.Text"')" Line 22: Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand Line 23: dbCommand.CommandText = queryString
My code is below. Can anyone help me figure out what is wrong with my code?
Sub btnSubmit_Click(sender As Object, e As EventArgs)
Dim UCde as string = txtUserID.Text Dim UserID as string = txtUserID.Text Dim Password as string = txtPassword.Text Dim Email1 as string = txtEmail.Text Dim FirstName as string = txtFirstName.Text Dim LastName as string = txtLastName.Text Dim AdminLevel as string = dropAccountType.SelectedItem.Value
Dim connectionString As String = "server='(local)'; trusted_connection=true; database='master'" Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim queryString As String = "INSERT INTO [UserID_db] ([Code], [UserID], [Password], [Email1], [Name_First], [Name_Last], [Admin_level]) VALUES ('" & txtUserID.Text"', '" & txtUserID.Text"', '" & txtPassword.Text"', '" & txtEmail1.Text"', '" & txtFirstName"', '" & txtLastName.Text"', '" & txtAdminLevel.Text"')" Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand dbCommand.CommandText = queryString dbCommand.Connection = dbConnection
Dim rowsAffected As Integer = 0 dbConnection.Open() dbCommand.ExecuteNonQuery() dbConnection.Close() End Sub
Here is my insert statement: StringBuilder sb = new StringBuilder(); sb.Append("INSERT INTO patients_import_test "); sb.Append("(Referral_Number,Referral_Date,FullName,Patient_ien,DOB,FMP,SSN_LastFour,Race_Id,PCM,Age) "); sb.Append("VALUES(@rnum,@rdate,@fname,@patid,@birthDate,@fmp,@ssan,@race,@pcm,@age) "); sb.Append("WHERE Referral_Number NOT IN ( SELECT Referral_Number FROM patients_import_test )");I'm getting an "Incorrect syntax near the keyword 'WHERE'".If I remove the WHERE clause the INSERT statement work fine.
Using Access 2010 and SQL Server 2012..i added a database through SSMS and added a table named tblEmployee (dbo.tblEmployee)
The table has 3 fields LName (nvarchar(50), null) FName (nvarchar(50), null) Code (nvarchar(50), null)
The access table has 3 fields FName, text LName, text Code, text
I found a code snippet here to insert from the Access table to the SQL table.UtterAccess Discussion Forums > Appending Access table to SQL Server table (and vice-versa) usin...The code is generating this error
Run-time error '3134': Syntax error in INSERT INTO statement
Code: Option Compare Database Sub AppToSQL() Dim strODBCConnect As String Dim strSQL As String 'Code from:
Server Error in '/ys(Do Not Remove!!!)' Application.
Syntax error in INSERT INTO statement.
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.
Source Error:
Line 8: <Script runat="server"> Line 9: Private Sub InsertAuthorized(ByVal Source As Object, ByVal e As EventArgs) Line 10: SqlDataSource1.Insert() Line 11: End Sub ' InsertAuthorized Line 12: </Script> Source File: C:Documents and SettingsDream_AchieverDesktopys(Do Not Remove!!!)Authorizing.aspx Line: 10
Public DBString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Q:VoicenetRTS FolderRTS ChargesAccountscosting.mdb" Private Sub Button13_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button13.Click Dim connstring As New OleDbConnection(DBString) connstring.Open() Dim searchstring As String = "SELECT * FROM Costings1" Dim da As New OleDbDataAdapter(searchstring, connstring) Dim DS As New DataSet() Dim dt As DataTable = DS.Tables("Costings1") da.FillSchema(DS, SchemaType.Source, "Costings1") da.Fill(DS, "Costings1") Dim cb As New OleDbCommandBuilder(da) da.InsertCommand = cb.GetInsertCommand da.UpdateCommand = cb.GetUpdateCommand Dim dr As DataRow = DS.Tables("Costings1").NewRow dr("Key") = TextBox1.Text dr("CODE") = TextBox2.Text dr("Element") = TextBox3.Text etc... DS.Tables("Costings1").Rows.Add(dr) da.Update(DS, "Costings1") <<<<<<<<<<<Syntax error in INSERT INTO statement.
i'm having a problem with my project we need to make for school with asp.net & c#. the problem is i'm a newbie with c#. the project we need to make is a survey. so the problem is my first question of the survey works fine when i push the go to next it enters the data nice into my sql db and my second question loads, then I enter the answer for my second question i press go to next question and i get this error: " The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Antwoorden_Gebruikers". The conflict occurred in database "GIP_enquete", table "dbo.Gebruikers", column 'Gebruiker_ID'. " I don't know how i can fix this cause im to newbie to understand whats wrong.
here is a view of my code:
Code Snippet
using System; using System.Data; using System.Configuration; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; using enqueteTableAdapters;
public partial class _Default : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { int Vraag_ID = Convert.ToInt16(Request.QueryString["vraagid"]); VragenTableAdapter VraagAdapter = new VragenTableAdapter(); lblVraag.Text = Convert.ToString(VraagAdapter.GeefVraag(Vraag_ID));
if (Vraag_ID == 1) { pnlVraag1.Visible = true; }
if (Vraag_ID == 2) { pnlVraag2.Visible = true; }
} protected void btnVraag1_Click(object sender, EventArgs e) { //make a new user GebruikersTableAdapter GebruikerAdapter = new GebruikersTableAdapter(); DateTime Moment = System.DateTime.Now; GebruikerAdapter.GebruikerToevoegen(Moment); int GebruikerID = Convert.ToInt16(GebruikerAdapter.GeefGebruikerID(Moment)); this.ViewState.Add("gebruiker_id", GebruikerID);
//go to 2nd question Response.Redirect("Default.aspx?vraagid=2", true); }
protected void btnVraag2_Click(object sender, EventArgs e) { //get back the user from question 1 int GebruikerID = Convert.ToInt16(this.ViewState["gebruiker_id"]);
Hi,all,When I use following sql, an error occurs:insert into #tmprepEXECUTE proc_stat @start,@endThere is a "select * from #tmp " in stored procedure proc_stat, and theerror message is :Server: Msg 8164, Level 16, State 1, Procedure proc_stat, Line 42An INSERT EXEC statement cannot be nested.What's the metter? Any help is greatly appreciated. Thanks
When I run this query against PFGDSMRISSQLQ1 in Server manager 2005:use <database>INSERT INTO dbo.<table> (<column1>, <column2>, <column3>)VALUES (12598,2900,1.00)I get this error:Msg 8624, Level 16, State 1, Line 5Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services.Can someone please help. Thanks!
Have run to a select permission error when attempting insert data to a table. received the following error
Msg 229, Level 14, State 5, Line 11 The SELECT permission was denied on the object 'tableName', database 'DBname', schema 'Schema'.
Few things to note - There are no triggers depending on the table - Permissions are granted at a roll level which is rolled down to the login - The test environments have the same level of permission which works fine.
Hi All I'm having a bit of trouble with an sql statement being inserted into a database - here is the statement: string sql1; sql1 = "INSERT into Customer (Title, FirstName, FamilyName, Number, Road, Town,"; sql1 += " Postcode, Phone, DateOfBirth, email, PaymentAcctNo)"; sql1 += " VALUES ("; sql1 += "'" + TxtTitle.Text + "'," ; sql1 += "'" + TxtForename.Text + "'," ; sql1 += "'" + TxtSurname.Text + "'," ; sql1 += "'" + TxtHouseNo.Text + "',"; sql1 += "'" + TxtRoad.Text + "',"; sql1 += "'" + TxtTown.Text + "',"; sql1 += "'" + TxtPostcode.Text + "',"; sql1 += "'" + TxtPhone.Text + "',"; sql1 += "'" + TxtDob.Text + "',"; sql1 += "'" + TxtEmail.Text + "',"; sql1 += "'" + TxtPayAcc.Text + "')"; Which generates a statement like:INSERT into Customer (Title, FirstName, FamilyName, Number, Road, Town, Postcode, Phone, DateOfBirth, email, PaymentAcctNo) VALUES ('Mr','Test','Test','129','Test Road','Plymouth','PL5 1LL','07855786111','14/04/1930','mr@test.com','123456') I cannot for the life of me figure out what is wrong with this statement. I've ensured all the fields within the database have no validation (this is done within my ASP code) that would stop this statement being inserted. Line 158: dbCommand.Connection = conn;Line 159: conn.Open();Line 160: dbCommand.ExecuteNonQuery();Is the line that brings up the error - I presume this could be either an error in the statement or maybe some settings on the Database stopping the values being added. Any ideas which of this might be ? I'm not looking for someone to solve this for me, just a push in the right direction! Thanks!
I've have a need with SQL Server 2005 (so I've no MERGE statement), I have to merge 2 tables, the target table has 10 fields, the first 4 are the clustered index and primary key, the source table has the same fields and index.Since I can't use the MERGE statement (I'm in SQL 2005) I have to make a double step operation, and INSERT and an UPDATE, I can't figure how to design the WHERE condition for the insert statement.
An insert statement was not inserting all the data into a table. Found it very strange as the other fields in the row were inserted. I ran SQL profiler and found that sql statement had all the fields in the insert statement but some of the fields were not inserted. Below is the sql statement which is created dyanmically by a asp.net C# class. The columns which are not inserted are 'totaltax' and 'totalamount' ...while the 'shipto_name' etc...were inserted.there were not errors thrown. The sql from the code cannot be shown here as it is dynamically built referencing C# class files.It works fine on another test database which uses the same dlls. The only difference i found was the difference in date formats..@totalamount=1625.62,@totaltax=125.62are not inserted into the database.Below is the statement copied from SQL profiler.exec sp_executesql N'INSERT INTO salesorder(billto_city, billto_country, billto_line1, billto_line2, billto_name,billto_postalcode, billto_stateorprovince, billto_telephone, contactid, CreatedOn, customerid, customeridtype,DeletionStateCode, discountamount, discountpercentage, ModifiedOn, name, ordernumber,pricelevelid, salesorderId, shipto_city, shipto_country,shipto_line1, shipto_line2, shipto_name, shipto_postalcode, shipto_stateorprovince,shipto_telephone, StateCode, submitdate, totalamount,totallineitemamount, totaltax ) VALUES(@billto_city, @billto_country, @billto_line1, @billto_line2,@billto_name, @billto_postalcode, @billto_stateorprovince, @billto_telephone, @contactid, @CreatedOn, @customerid,@customeridtype, @DeletionStateCode, @discountamount,@discountpercentage, @ModifiedOn, @name, @ordernumber, @pricelevelid, @salesorderId,@shipto_city, @shipto_country, @shipto_line1, @shipto_line2,@shipto_name, @shipto_postalcode, @shipto_stateorprovince, @shipto_telephone,@StateCode, @submitdate, @totalamount, @totallineitemamount, @totaltax)',N'@billto_city nvarchar(8),@billto_country nvarchar(13),@billto_line1 nvarchar(3),@billto_line2 nvarchar(4),@billto_name nvarchar(15),@billto_postalcode nvarchar(5),@billto_stateorprovince nvarchar(8),@billto_telephone nvarchar(3),@contactid uniqueidentifier,@CreatedOn datetime,@customerid uniqueidentifier,@customeridtype int,@DeletionStateCode int,@discountamount decimal(1,0),@discountpercentage decimal(1,0),@ModifiedOn datetime,@name nvarchar(33),@ordernumber nvarchar(18),@pricelevelid uniqueidentifier,@salesorderId uniqueidentifier,@shipto_city nvarchar(8),@shipto_country nvarchar(13),@shipto_line1 nvarchar(3),@shipto_line2 nvarchar(4),@shipto_name nvarchar(15),@shipto_postalcode nvarchar(5),@shipto_stateorprovince nvarchar(8),@shipto_telephone nvarchar(3),@StateCode int,@submitdate datetime,@totalamount decimal(6,2),@totallineitemamount decimal(6,2),@totaltax decimal(5,2)',@billto_city=N'New York',@billto_country=N'United States',@billto_line1=N'454',@billto_line2=N'Road',@billto_name=N'Hillary Clinton',@billto_postalcode=N'10001',@billto_stateorprovince=N'New York',@billto_telephone=N'124',@contactid='8DAFE298-3A25-42EE-B208-0B79DE653B61',@CreatedOn=''2008-04-18 13:37:12:013'',@customerid='8DAFE298-3A25-42EE-B208-0B79DE653B61',@customeridtype=2,@DeletionStateCode=0,@discountamount=0,@discountpercentage=0,@ModifiedOn=''2008-04-18 13:37:12:013'',@name=N'E-Commerce Order (Before billing)',@ordernumber=N'BRKV-CC-OKRW5764YS',@pricelevelid='B74DB28B-AA8F-DC11-B289-000423B63B71',@salesorderId='9CD0E11A-5A6D-4584-BC3E-4292EBA6ED24',@shipto_city=N'New York',@shipto_country=N'United States',@shipto_line1=N'454',@shipto_line2=N'Road',@shipto_name=N'Hillary Clinton',@shipto_postalcode=N'10001',@shipto_stateorprovince=N'New York',@shipto_telephone=N'124',@StateCode=0,@submitdate=''2008-04-18 14:37:10:140'',@totalamount=1625.62,@totallineitemamount=1500.00,@totaltax=125.62
This problem is being seen on SQL 2005 SP2 + cumulative update 4
I am currently successfully using the output clause of an insert statement to return the identity values for inserted rows into a table variable
I now need to add an "instead of insert" trigger to the table that is the subject of the insert.
As soon as I add the "instead of insert" trigger, the output clause on the insert statement does not return any data - although the insert completes successfully. As a result I am not able to obtain the identities of the inserted rows
Note that @@identity would return the correct value in the test repro below - but this is not a viable option as the table in question will be merge replicated and @@identity will return the identity value of a replication metadata table rather than the identity of the row inserted into my_table
Note also that in the test repro, the "instead of insert" trigger actually does nothing apart from the default insert, but the real world trigger has additional code.
To run the repro below - select each of the sections below in turn and execute them 1) Create the table 2) Create the trigger 3) Do the insert - note that table variable contains a row with column value zero - it should contain the @@identity value 4) Drop the trigger 5) Re-run the insert from 3) - note that table variable is now correctly populated with the @@identity value in the row
I need the behaviour to be correct when the trigger is present
GO /************************************************ 2) - Create the trigger ************************************************/ CREATE TRIGGER [dbo].[trig_my_table__instead_insert] ON [dbo].[my_table] INSTEAD OF INSERT AS BEGIN
INSERT INTO my_table ( forename, surname) SELECT forename, surname FROM inserted
END
/************************************************ 3) - Do the insert ************************************************/
INSERT INTO my_table ( forename , surname ) OUTPUT inserted.my_table_id INTO @my_insert VALUES( @forename , @surname )
select @@identity -- expect this value in @my_insert table select * from @my_insert -- OK value without trigger - zero with trigger
/************************************************ 4) - Drop the trigger ************************************************/
drop trigger [dbo].[trig_my_table__instead_insert] go
/************************************************ 5) - Re-run insert from 3) ************************************************/ -- @my_insert now contains row expected with identity of inserted row -- i.e. OK
I'm quite stuck with this:I have an import table called ReferenceMatchingImport which containsdata that has been sucked from a data submission. The contents ofthis table have to be imported into another table ExternalReferencewhich has various foreign keys.This is simple but one of these keys says that the value inExternalReference.CompanyRef must be in the CompanyReference table.Of course if this is an initial import then it will not be so as partof my script I must insert a new row into CompanyReference andpopulate ExternalReference.CompanyRef with the identity column of thistable.I thought a good idea would be to use an SP which inserts a new rowand returns @@Identity as the value to insert. However this doesn'twork as far as I can tell. Is there a approved way to perform thissort of opperation? My code is below.Thanks.ALTER PROCEDURE SP00ReferenceMatchingImportAS/*Just some integrity checking going on here*/INSERT ExternalReference(ExternalSourceRef,AssetGroupRef,CompanyUnitRef,EntityTypeCode,CompanyRef, --this is the unknown ref which is returned by the spExternalReferenceTypeCode,ExternalReferenceCompanyReferenceMapTypeCode,StartDate,EndDate,LastUpdateBy,LastUpdateDate)SELECT rmi.ExternalDataSourcePropertyRef,rmi.AssetGroup,rmi.CompanyUnit,rmi.EntityType,SP01InsertIPDReference rmi.EntityType, --here I'm trying to run thesp so that I can use the return value as the insert value1,1,GETDATE(),GETDATE(),'RefMatch',GETDATE()FROM ReferenceMatchingImport rmiWHERE rmi.ExternalDataSourcePropertyRef NOT IN (SELECT ExternalSourceRefFROM ExternalReference)
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
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 was wondering if any SQL people out there know if i can INSERT INTO 2 tables at the same time? I havent found a yes to the question so i thought i would throw it out there and see what everyone has to say. word
I have put together the following code, but am getting a syntax error on the Insert Into statement. Could someone kindly tell me what I am doing wrong? Thanks 1 Protected Sub SubmitButton2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles SubmitButton2.Click2 authAndSaveToDB()3 End Sub4 Private Sub authAndSaveToDB()5 On Error GoTo errhand6 If connectToDB() Then 'connection to database was successful7 'insert candidate in the database 8 insertCandidate(ddlPositions.Text, txtName.Text, txtBio.Text, conn)9 Response.Redirect("FileUpload.aspx")10 11 Else 12 Response.Write("<font color = red><strong>Database connection failure. Please try again in a few minutes or contact system administrator.</strong></font>")13 End If 14 closeDB() 'end the connection to the database 15 Exit Sub 16 errhand:17 Response.Write("<br>ERROR: " & Err.Description & "<br>")18 End Sub19 Private Sub insertCandidate(ByVal strPositions As String, ByVal strCandidate As String, ByVal strBiography As String, ByVal myconn As OleDb.OleDbConnection)20 Dim cmd As OleDb.OleDbCommand21 Dim strSQL As String 22 23 strSQL = "INSERT INTO Position (Positions,Candidate,Biography) VALUES ('" & _ 24 strPositions & "'," & _ 25 "'" & strCandidate & "'," & _ 26 "'" & strBiography & "')" 27 cmd = New OleDb.OleDbCommand(strSQL, myconn)28 cmd.ExecuteNonQuery()29 End Sub30 End Class
Hi, I am retriving the value time from my database and i have the convert function on it CONVERT (varchar, Time, 8) When i edit this and try and insert it back to the database i am losing the information. I am not sure how the insert statement shoulld read in order to do this. Thanks Mike
Hi forum, currently I insert a mobile phone number into colomn OK but what I need to do is add +61 as a prefix.Users provide 055 555 555, I want to store number as +61 55 555 555 (note this also drops the 0) my SQL skills a little lacking so appreciate all good info, cheers P
I am having difficulties with my first hand coded insert statement. The record inserts BUT the first item VALUE is selected for all the drop down lists ( 3 of them are optional) I have Prerenders on the page to insert a null value at the top of the list. For the dropdown that is mandatory it only enter first item. Thats without specifying type. As soon as I specify type I get - Input string not in correct format - doesn't actually tell me which one!!!!!! All char types match the database.
Here is my insert:
Sub Add_Click(sender as object, e as EventArgs)
Dim conBooks As SqlConnection Dim strInsert As String Dim cmdInsert As SqlCommand
Hi All, I am trying to insert data into table1 by getting data from table2, table3 and looking whether the data is already not exist in table1. Here is my code... insert into table1 (first_name, last_name, user_login, email, organization_name) values (select ru.firstname, ru.lastname, ru.UserID, ru.EmailAddress, ru.BusinessName from table2 ru, table3 wepsUsers where ru.id = wepsUsers.user_id and not exists (select user_login from table1 where user_login = ru.UserID))
When I excute the code, I am getting the following error..... Server: Msg 156, Level 15, State 1, Line 3Incorrect syntax near the keyword 'select'.Server: Msg 170, Level 15, State 1, Line 7Line 7: Incorrect syntax near ')'. But, If execute the following code.... select ru.firstname, ru.lastname, ru.UserID, ru.EmailAddress, ru.BusinessName from table2 ru, table3 wepsUsers where ru.id = wepsUsers.user_id and not exists (select user_login from table1 where user_login = ru.UserID).... I am able to see the values.
Can any one shade on my code? and please let me know, where I am doing wrong. I am appreciate your help.
Is there anyway that we can use insert into statement to insert data into the first row of a table, or initialize the column with the new inserted data.
I am trying to do an insert in a particular View...one of the field has got an 'Apostrophe'.(Ex: SQl Server's). When i do this i get an error. the system does not allow me to insert.... is there any way i can do an Insert with that...???
