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 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 here goes the code1 DataSet ds = new DataSet();
2
3 SqlDataAdapter myCommand1 = new SqlDataAdapter("select * from SurveyTemplate", myConnection);
4 SqlCommandBuilder cb = new SqlCommandBuilder(myCommand1);
5 myCommand1.FillSchema(ds, SchemaType.Source);
6 DataTable pTable = ds.Tables["Table"];
7 pTable.TableName = "SurveyTemplate";
8 myCommand1.InsertCommand = cb.GetInsertCommand();
9 myCommand1.InsertCommand.Connection = myConnection;
10
11 SqlDataAdapter myCommand2 = new SqlDataAdapter("select * from Question", myConnection);
12 cb = new SqlCommandBuilder(myCommand2);
13 myCommand2.FillSchema(ds, SchemaType.Source);
14 pTable = ds.Tables["Table"];
15 pTable.TableName = "Question";
16 myCommand2.InsertCommand = cb.GetInsertCommand();
17 myCommand2.InsertCommand.Connection = myConnection;
18
19 SqlDataAdapter myCommand3 = new SqlDataAdapter("select * from Possible_Answer", myConnection);
20 cb = new SqlCommandBuilder(myCommand3);
21 myCommand3.FillSchema(ds, SchemaType.Source);
22 pTable = ds.Tables["Table"];
23 pTable.TableName = "Possible_Answer";
24 myCommand3.InsertCommand = cb.GetInsertCommand();
25 myCommand3.InsertCommand.Connection = myConnection;
26
27 ds.Relations.Add(new DataRelation("FK_Question_SurveyTemplate", ds.Tables["SurveyTemplate"].Columns["id"], ds.Tables["Question"].Columns["surveyTemplateID"]));
28 ds.Relations.Add(new DataRelation("FK_Possible_Answer_Question", ds.Tables["Question"].Columns["id"], ds.Tables["Possible_Answer"].Columns["questionID"]));
29
30 DataRow dr = ds.Tables["SurveyTemplate"].NewRow();
31 dr["name"] = o[0];
32 dr["description"] = o[1];
33 dr["active"] = 1;
34 ds.Tables["SurveyTemplate"].Rows.Add(dr);
35
36 DataRow dr1 = ds.Tables["Question"].NewRow();
37 dr1["questionIndex"] = 1;
38 dr1["questionContent"] = "q1";
39 dr1.SetParentRow(dr);
40 ds.Tables["Question"].Rows.Add(dr1);
41
42 DataRow dr2 = ds.Tables["Possible_Answer"].NewRow();
43 dr2["answerIndex"] = 1;
44 dr2["answerContent"] = "a11";
45 dr2.SetParentRow(dr1);
46 ds.Tables["Possible_Answer"].Rows.Add(dr2);
47
48 dr1 = ds.Tables["Question"].NewRow();
49 dr1["questionIndex"] = 2;
50 dr1["questionContent"] = "q2";
51 dr1.SetParentRow(dr);
52 ds.Tables["Question"].Rows.Add(dr1);
53
54 dr2 = ds.Tables["Possible_Answer"].NewRow();
55 dr2["answerIndex"] = 1;
56 dr2["answerContent"] = "a21";
57 dr2.SetParentRow(dr1);
58 ds.Tables["Possible_Answer"].Rows.Add(dr2);
59
60 dr2 = ds.Tables["Possible_Answer"].NewRow();
61 dr2["answerIndex"] = 2;
62 dr2["answerContent"] = "a22";
63 dr2.SetParentRow(dr1);
64 ds.Tables["Possible_Answer"].Rows.Add(dr2);
65
66 myCommand1.Update(ds,"SurveyTemplate");
67 myCommand2.Update(ds, "Question");
68 myCommand3.Update(ds, "Possible_Answer");
69 ds.AcceptChanges();
70
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
View 3 Replies View RelatedI 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')
[code]...
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:
ALTER PROCEDURE dbo.InsertReportLineItem
(
@LineUserName varchar(50),
@LineWeek int,
@LineDate datetime,
@LineStartTime datetime,
@LineEndTime datetime,
@LineHeatTicket int,
@LineTicketDescription varchar(MAX),
@LineTakenAs varchar(50),
@LineDinnerPremium varchar(50),
@LineHours float,
@LineRate float,
@LineReportDueDate datetime
)
AS
SET NOCOUNT OFF;
INSERT INTO [dbo].[ReportLineItems] ([LineUserName], [LineWeek], [LineDate], [LineStartTime], [LineEndTime], [LineHeatTicket], [LineTicketDescription], [LineTakenAs], [LineDinnerPremium], [LineHours], [LineRate], [LineReportDueDate]) VALUES (@LineUserName, @LineWeek, @LineDate, @LineStartTime, @LineEndTime, @LineHeatTicket, @LineTicketDescription, @LineTakenAs, @LineDinnerPremium, @LineHours, @LineRate, @LineReportDueDate);
SELECT LineId, LineUserName, LineWeek, LineDate, LineStartTime, LineEndTime, LineHeatTicket, LineTicketDescription, LineTakenAs, LineDinnerPremium, LineHours, LineRate, LineReportDueDate FROM ReportLineItems WHERE (LineId = SCOPE_IDENTITY())
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;
int rowsAffected = comm.ExecuteNonQuery();
bttnAddItem.Text = rowsAffected.ToString() + " row affected: Success";
comm.ExecuteNonQuery();
conn.Close();
}
Hi,
I'm getting an Error on this code - "no overlod for method 'select' takes '0' arguments
what should I do? protected void Button2_Click(object sender, EventArgs e)
{SqlDataSource SqlDataSource6 = new SqlDataSource();
SqlDataSource6.ConnectionString = ConfigurationManager.ConnectionStrings["DolevDBConnectionString"].ToString();SqlDataSource6.SelectCommandType = SqlDataSourceCommandType.Text;
SqlDataSource6.SelectCommand = "SELECT * FROM Employees WHERE (EE_ID = @EE_ID)";
SqlDataSource6.SelectParameters.Add("EE_ID", TextID.Text);int Rowseffected = 0;
try
{
Rowseffected = SqlDataSource6.Select();
}
finally
{SqlDataSource6 = null;
}if (Rowseffected == 1)
{Server.Transfer("Default6.aspx");
}
else
{Label20.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)
ORder By c.Pl_ID
any help will be appreciated.
Regards,
Karen
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??
I am inserting information into my SQL table and receiving the following error:
error:
--------------------------------------------------------------------------------
INSERT permission denied on object 'tblMaster', database 'master', owner 'dbo'.
Exception Details: System.Data.SqlClient.SqlException: INSERT permission denied on object 'tblMaster', database 'master', owner 'dbo'
---------------------------------------------------------------------------------
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()
Any help would be appreciated.
Thanks
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"
'
'
'
What would produce this error message? my data types are correct as well as
the number of columns & values.
insert into table1(field1,field2,field3,field4,field5)
values(10,11,'abc',2000-06-14,1,1)
field1 numeric
field2 numeric
field3 varchar
field4 datetime
field5 numeric
Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.
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?
Thanks!
Toni
Hi
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?
help
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'.
The statement has been terminated.
Here is my query:
USE FCobContactssqltest
GO
SET IDENTITY_INSERT AMGR_Client_Tbl ON
INSERT INTO AMGR_Client_Tbl
(Name,Last_Modify_Date,Phone_1,Phone_2,Phone_3,Phone_4,First_Name,Initial,MrMs,Title,Firm,Address_Line_1,Address_Line_2,City,State_Province,Zip_Code,Phone_1_Desc,Phone_2_Desc,Phone_3_Desc,Phone_4_Desc,Record_Id)
SELECT Name,Last_Modify_Date,Phone1_Work,Phone2_Work,Phone3_Home,Phone4,First_Name,Initial,MrMs,Title,Firm,Addr_Line1,Addr_Line2,Addr_City,Addr_State,Addr_Zip,Phone1_Desc,Phone2_Desc,Phone3_Desc,Phone4_Desc,0
FROM ADIS_Import.dbo.vw_ADIS_Import_MR
thanks,
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
Any ideas how to fix this?
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
View 2 Replies View Relatedhii,,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
Hey Guys:
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
<%@ Page Language="vb" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<html>
<script language="VB" runat="server">
Dim MyConnection As SqlConnection
Sub Page_Load(Sender As Object, E As EventArgs)
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)
MyCommand.Parameters.Add(New SqlParameter("@lastname", SqlDbType.NVarChar, 50))
MyCommand.Parameters("@lastname").Value = txtLastName.Value
MyCommand.Connection.Open()
Try
MyCommand.ExecuteNonQuery()
Message.InnerHtml = "Record Added<br>" & InsertCmd.ToString()
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>
<table width="95%">
<tr>
<td valign="top">
<ASP:DataGrid id="MyDataGrid" runat="server"
Width="700"
BackColor="#ccccff"
BorderColor="black"
ShowFooter="false"
CellPadding=3
CellSpacing="0"
Font-Name="Verdana"
Font-Size="8pt"
HeaderStyle-BackColor="#aaaadd"
EnableViewState="false"
/>
</td>
<td valign="top">
<table style="font: 8pt verdana">
<tr>
<td colspan="2" bgcolor="#aaaadd" style="font:10pt verdana">Add a New Author:</td>
</tr>
<tr>
<td nowrap>Last Name: </td>
<td>
<input type="text" id="txtLastName" runat="server" NAME="txtLastName"><br>
</td>
</tr>
<tr>
<td></td>
<td style="padding-top:15">
<input type="submit" OnServerClick="AddAuthor_Click" value="Add Author" runat="server" ID="Submit1" NAME="Submit1">
</td>
</tr>
<tr>
<td colspan="2" style="padding-top:15" align="center">
<span id="Message" EnableViewState="false" style="font: arial 11pt;" runat="server"/>
</td>
</tr>
</table>
</td>
</tr>
</table>
</form>
</body>
</html>
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... :(
Thanks is advance,
Nancy
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 am not sure what I am doing wrong
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.
Any suggestion? I'm new to SQL7
Thanks
Jason
Hi All,
I am trying to run an insert statement and getting an error.
Insert statement:
insert into conv_owner (name,street_num,
street_direction,street_name,street_type,street_un it,
address2,city,state,city_state,zip,wphone,inservic e,
db_name,table_name)
select "Facility's Owner",null,null,null,null,null,null,null,
null,null,null,"Owner's Telephone Number",inservice,
'BKFoodProtection.mdb','FP_Master'
from fp_master
Error message:
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
Any idea why? Thanks.
In SQL 7.0 SP3 , I am receiving this message....
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.
Any help would be appreciated.
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.
Thanks!