Simple Insert Statement
Apr 27, 2006I have a textbox with the id of txtName it is a name text box.
and I have a submit button.
How do i get the value of the text in the text box to popluate a new row of data in my Sql Server database.
ADVERTISEMENT
Simple Insert Statement
Apr 19, 2008Here are my tables:
-------------------------------
Events
-------------------------------
ID | E_EventName
-------------------------------
-------------------------------
Photos
-------------------------------
ID | P_EventID | P_Filename
-------------------------------
Given an event like "2005 cookout", and a file like "bob.jpg", I'd like to insert the event id based on the event name and the filename into Photos. How can I do that?
Problem Of Duplicate Records With A Simple Insert Statement !
Oct 11, 2007
I am using a simple stored proc shown below which inserts a record and updates the same record field called SortID with the Primary Key Column data of the same record.Everything was working fine until few days back. The site has been deployed 2 years back and was LIVE ever since.
We have got thousands of records in this Credits table. The table field called SortID is used for moving the credits up and down.
My problem is now after 2 years of deploying the table has got duplicate records, suprisingly a same credit is appearing under different MemberID and with the same SortID.
How are these duplicate records inserted with a simple insert statement & update ??
Is this a Locking problem or Transaction problem I have no idea.
Any ideas will be of great help
Thanks in Advance
Stored Procedure Used
CREATE PROC SP_SC_INSERT (@memberID int,@title varchar(30),@dir varchar(30),@desc varchar(20))ASINSERT INTO [dbo].[Credits]([MemberID],[Title],[Director], [Description], )VALUES(@memberID,@title,@dir, @desc, )UPDATE Credits Set SortID = @@IDENTITY WHERE CreditID = @@IDENTITYGO
Simple SQL Statement
Apr 19, 2004I am trying to concatenate two columns together firstname, lastname
i have done some sql in oracle, but i'm noticing that mssql is different
what i have seen is
select firstname||lastname from table
that errors, what is the right syntax?
Thanks
Simple SQL Statement,
Jan 16, 2005I am attempting to do an insert on the DataGrids Delete command. The problem I am running into is that im not very good at SQL inserts.
Can anyone tell me how to insert a record into a table based on the ID of a record in another table? I want to do this.
As a record (datagrid Row) is deleted, I want to run code (that I dont know how to write) that does this...
Insert into Table-Interface-LOG (fields), from Table-Interface (fields)
Where Interface.Interface_ID = @interface_id
I just need the SQL to do the insert, no ADO code or anything. I dont understand how to use the SET in a SQL command.
What I have so far that Doesnt work.. not even sure why it doesnt work..
INSERT INTO InterfaceLog
SELECT Interface_ID, App_ID, SendsToApp_ID, InterfaceName, Description, ITContact_ID, Type, Frequency, DataFormat, Status, CommMethod, Complexity,
ProdEntryDate, ProdExitDate
FROM Interface
WHERE (Interface_ID = @interface_id)
any Ideas? Do I use the set comand somehow here?
Thanks for any code...
Simple Sql Statement
Apr 23, 2006 Hi, I feel a difficulty to understand the following procedure, it is simple but i am not sure the logic, could anyone give me some explain / hints , then I know how to write the similiar procedure next time. thanks for your time. I extract the code from the Classifieds Starter Kit
For the procedure of GetCategoryByParentIdWHERE (ParentCategoryId = @ParentCategoryId)OR (@ParentCategoryId = 0 AND ParentCategoryId IS NULL)
For the procedure of GetParentCategoryById WHERE (SELECT Path FROM Category WHERE Id = @Id) LIKE Path + '%'
ALTER PROCEDURE GetCategoryByParentId(@ParentCategoryId int = 0)ASSET NOCOUNT ON;SELECT [Id], [ParentCategoryId], [Name], NumProductsFROM CategoryWHERE (ParentCategoryId = @ParentCategoryId)OR (@ParentCategoryId = 0 AND ParentCategoryId IS NULL)ORDER BY NameALTER PROCEDURE GetParentCategoryById @Id intASSELECT Id, ParentCategoryId, Name, NumProductsFROM CategoryWHERE (SELECT Path FROM Category WHERE Id = @Id) LIKE Path + '%'ORDER BY Path
Thanks a lot, Trevor
It Seemed Like A Simple SQL Statement
Jun 6, 2008I need to see whether there's any unapproved timecards, and if so compile a list of supervisors that need to make approvals.
my original statement was:
SELECT DISTINCT
EmployeeMaster.SupervisorNumber
, SupervisorMaster.EmailAddress
, SupervisorMaster.EmployeeMasterNumber
, RTRIM(SupervisorMaster.FirstName) + ' ' + RTRIM(SupervisorMaster.LastName) AS SupervisorName
FROM
Cards
LEFT OUTER JOIN EmployeeMaster ON Cards.EmployeeNumber = EmployeeMaster.EmployeeNumber
LEFT OUTER JOIN SupervisorMaster ON EmployeeMaster.SupervisorNumber = SupervisorMaster.EmployeeNumber
WHERE
(Cards.SupApp = 0)
And this worked great until they asked for a date range.
If I am correct the distinct will look for the first occurrence that meets the select criteria and ignore the rest of the rows that meet the criteria. Leading to the possiblity that the first row is not a timecard that meets the date requirement, and even though other timecards exist that will satisfy the condition, they will not be tested because of the Distinct clause.
What's the best way to handle a situation like this?
Help With Simple Sql Statement
Aug 16, 2006Hi.
Please help me.
Table 'Counter' has a date field and an integer field. I want to auto increase the integer field by 1 using the update statement. I DO NOT want to first run a select statement to find out the initial value of the integer field prior to incrementing it. how would i go about doing this?
Thanks a lot!
Need Help For A Simple Formula For An Sql Statement
Apr 6, 2007I have a products database on my online store that records 2 columns for each product:DateAdded: the datetime the item was addedTotalSales: the total sales generated since the item was added to the siteI have already figured out how to calculate best sellers with this formula:TotalSales / TotalDays I am adding a computed column to my database, I need to calculate the followingTotalSales / ("this is where i need something to use today's date and calculate how many days it has been since it was added")Then if I can do that, I will automatically have a column that will give me a number ranking each of my items.
View 2 Replies View RelatedSimple SELECT Statement
Dec 6, 2004Ok, I do know SQL and have been using it for quite soem time. For some reason, it is giving me an error and I was wondering if someone could help.
Here is the few lines of interest
System.Data.SqlClient.SqlCommand command;
command = new System.Data.SqlClient.SqlCommand(@"SELECT Password FROM User WHERE Username='" + user + "'", this.sqlConn);
dataReader = command.ExecuteReader();
This is the error I am coming up with.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'User'.
I have tried enclosing the Table name in quotes and removing the @. What am I doing wrong? Thanks!
What's Wrong With My Statement? (simple)
Aug 3, 2007Hello well-intentionned reader,
I'm having trouble figuring out what's wrong with my statement. This is what the table looks like. I want to display employees under the manager with the last name King. my statements are below the tableName Null? Type
----------------------------------------- -------- ----------------------------
EMPLOYEE_ID NOT NULL NUMBER(6)
LAST_NAME NOT NULL VARCHAR2(25)
SALARY NUMBER(8,2)
MANAGER_ID NUMBER(6)
My statement
SQL> SELECT last_name, salary
FROM employees
WHERE manager_id =
(SELECT employee_id
FROM employees
WHERE EMPLOYEE.LAST_NAME = .King.); 2 3 4 5 6
WHERE EMPLOYEE.LAST_NAME = .King.)
*
ERROR at line 6:
ORA-00936: missing expression
Thank you for your time.
/gozu
Simple If Statement, What Am I Missing
Oct 13, 2007Hi everyone and thank for taking the time to read, I am new to this forum and to SQL to.
I am taking a programmers course, i learned mainly C# for now, but i also know a bit of C and C++ and linux programming,
i am going through my SQL school book, (SQL Server 2000 design and implementation) and i am trying a simple IF statement. heres how it goes
DECLARE @P CHAR
SET @P=NULL
SELECT @P=Powerlck FROM vehicle
IF (@P=NULL)
PRINT 'got you'
powerlck is a collum taking char(1) which is either Null or 1.
there is five entry all but one is set to 1, null is the default
when i run this script, it does not print 'got you'
but if i run select * from vehicle
it will show one with <NULL> value.
what did i miss??
Should Be A Simple SELECT Statement
Nov 15, 2007I've been struggling with this problem, hope someone can help. I'm using SQL Server 2005 Express. I have a simple database with 7 tables (m64,m67,m69,m71,m87) all have an identical design(column name, type).
All I want to do is pull out one recordset (if it's in that table) from each of the tables.
This is what I have:
"SELECT * FROM m64, m67, m69, m71, M87 WHERE P_N Like '" & Request.Querystring("P_N") & "'"
If I only enter one table, I get a result.
DB Engine :: Can't Use The MERGE Statement / How To Design WHERE Condition For Insert Statement
Nov 5, 2015I'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.
View 2 Replies View RelatedA Simple Question About SQL Update Statement
Jul 10, 2006hi, everyone,
When I update a row that does not exist in a table using VBscript and SQL 2003 server, the row is automatically added to the table. Why does this happen?
Can somebody help me? Thanks in advance!
Help Needed With Simple Case Statement In SQL
Mar 1, 2007Hello,
I am looking to modify this Case Statement. Where it says ELSE '' I need it to display the actual contents of the cell. 1 = Yes , 0 = No, (any other integer) = actual value.
Right now if the value is anything other than 1 or 0, it will leave the cell blank.
CASE dbo.Training.TrainingStatus WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' ELSE '' END AS TrainingStatus
Thank You.
Pleas Help With Simple Sql Statement Question
Apr 24, 2006Hello and thanks for looking! I have this sql stament:
Dim strInsertCommand As String = "INSERT INTO CurrentDrawVar (Username, GamesBought, DateLastBuy) VALUES ('" & UserID & "', '" & gamestobuy & "', '" & Now & "')"
It works great and inserts into a database like it should. My question is where can I insert a WHERE stament into it so that I can insert this data over information that already exists in the database. FOr instance. I have a user grogo21 and in this row I want to insert gamestobuy and now. Where can i put this and is the stament below right:
WHERE Username = 'grogo21'
I tried putting it after the parenthases after the values but it doesnt work. Thanks alot!!
Simple? Coding Problem With A Where Statement
Apr 17, 2008This should be terribly simply, but I just can't seem to figure out where I'm going wrong. I have a table that logs usage of a few software licenses in my division, so it has columns for the username, the license, and the date/time checked out and date/time checked in. I have written a quick SQL query that totals how much time all the users have spent in three of the licenses during this work week. It works fine. But now I just want to limit that query to those users who have used those licenses for more than two hours.
Sounds simple enough, but I must be overlooking something obvious. I have tried [Minutes]>120 in both a WHERE and a HAVING statement, but neither worked. I have also tried changing the name of the column to something other than Minutes in the off case that there might be some kind of keyword confusion, but it didn't work. I have also tried various other unlikely fixes but continually get the error "Invalid Column Name 'Minutes'".
Any help would be very appreciated since this is driving me nuts. The code that works is below.
Declare @datMin datetime
Declare @datMax datetime
set @datMin=DateAdd(mi,
-(DatePart(hh,Getdate())*60 + DatePart(mi,GetDate())),--Gets the current minute of the day
DateAdd(dd,-datepart(dw,GetDate())+1,GetDate()))--Gets First Day of the Week at this time
set @datMax=DateAdd(mi,
1439-(DatePart(hh,Getdate())*60 + DatePart(mi,GetDate())),
GetDate())
Select UserName, Sum(Case
When DateOut<@datMin and DateIn<@datMax and DateIn>@datMax
Then datediff(n, @datMin, DateIn)
When DateOut<@datMin and DateIn>@datMax
Then datediff(n, @datMin, @datMax)
When DateOut>@datMin and DateOut<@datMax and DateIn>@datMax
Then datediff(n, DateOut, @datMax)
Else datediff(n, DateOut, DateIn)
End) as [Minutes]
From [Log]
Where ((DateOut>=@datMin and DateOut<=@datMax)
or (DateIn>=@datMin and DateIn<=@datMax)
or (DateOut<@datMin and DateIn>@datMax))
And (License='Viewer' or License='ARC/INFO' or License = 'Editor')
--And ([Minutes]>120)
Group by UserName
--Having ([Minutes]>120)
Order by [Minutes] desc
Simple Select Statement Problem
Aug 13, 2007Hi,
I have several records in a table of "links", which has the fields ID, UserID, Description and URL. I'm trying to select all the records in this table for a specific user. The stored prodecure I'm using is:
ALTER PROCEDURE dbo.sprocLinkSelect (@UserID uniqueidentifier)
AS
SELECT ID, UserID, description, URL
FROM links
WHERE UserID = @UserID
When I execute the procedure without "UserID" in the SELECT part of the statement it works and returns the correct rows for the UserID entered as a parameter. However, when UserID is included in the SELECT part of the statement (as above), it says:
"No rows affected.
(1 row(s) returned)"
and just shows the column headings, but with no record details beneath. (When the example works as intended, it returns two rows.)
I've tried changing the procedure to take @description as its parameter and return all records with one specific description, and it also only works when UserID isn't included in the SELECT line. I've also tried using SELECT *, but that doesn't seem to work either.
Any help would be greatly appreciated, I'm using SQL Express.
Simple Select Statement OrderBy
Feb 4, 2008Well my problem lies in that I am generating reports with the data I retrieve from my sql database. However my problem resides in the fact that I am generating one report at a time and if I want to grab each entry in the order in which they were produced its no problem.
IE - Using PowerBuilder 10.0 as an IDE for my application to generate reports.
select i_id into :insp_id from inspection where i_id = :index order by i_id asc using sqlca;
But now if I want to grab them in alphabetical order from another table I have problems.
this is the code I am trying to use maybe I am just thinking it through wrong.
select s_id into :insp_id from section where s_id = :index order by s_name asc using sqlca;
any help is appreciated. Is there a way to grab each row in alphabetical order?
Simple Question On Update Statement
Mar 4, 2008When I execute following SQL Query Analyzer (SQL 2005) i am getting message 3 times "95 Records effected" in query analyzer message window.
UPDATE dbo.Load_AC_Install
SET Load_AC_Install.Rejected_Reason = Load_AC_Install_Rejected.Validation_Reason
FROM (Load_AC_Install JOIN Load_AC_Install_Rejected
ON Load_AC_Install.Hardware_Portfolio_Barcode = Load_AC_Install_Rejected.Hardware_Portfolio_Barcode
AND Load_AC_Install.Install_Model_Barcode = Load_AC_Install_Rejected.Model_BarCode )
After I load "Load_AC_Install" table into Temporary table #Load_Install and When I execute following SQL Query from SQL Query Analyzer using temporary table I am getting message Only 1 time "95 Records effected"
Update #Load_Install
SET #Load_Install.Rejected_Reason = Load_AC_Install_Rejected.Validation_Reason
FROM (#Load_Install JOIN Load_AC_Install_Rejected
ON #Load_Install.Hardware_Portfolio_Barcode = Load_AC_Install_Rejected.Hardware_Portfolio_Barcode
AND #Load_Install.Install_Model_Barcode = Load_AC_Install_Rejected.Model_BarCode )
So My Question is if i use phicical table (dbo.Load_AC_Install) then i get message 3 times, but If i use same data with tempoary table(#load_Install) I get message only one time "95 Records Effected" in query analyzer message window!
Anyone know why?
Thanks for response and please ask me if you still have any questions!
Simple IIF Statement Driving Me Nuts
Oct 17, 2007
I have an IIF statement in a text box. Its very basic
What I want is to check if the value of something is greater than 1. If it is, then make it 1. If it isn't then just use what the value is.
(loose example)
=IIF((Field!Var1.Value * Field!Var2.Value)>1, 1,Nothing)
Basically I can't have a pecentage greater than 100. If it is then make it 100. If its not then just use the result of the calculation.
Solution For Simple Select Statement.
Jul 30, 2007I have a table with the name of customers in it. The problem is that I have one column with the full name in it like
|Last First Middle|.
As you can see this is a problem for me.
I need to find a way to put these values into 3 seperate columns.
|Last| First| Middle |
Is there a function to seperate them via a space?
I have been looking on the web and have not found any functions or solutions.
Any help would be most appreciated.
Thanks in advance,
Gene
Simple Select Statement Gone Wild!!
Sep 14, 2007I am very new to sql and would like some help with a simple select statement. I am trying to pull birthdates using the following code. I can get the query to run but some of the dates are not between the parameters. What am i doing wrong?
SELECT Employees.EmployeeStatusID, People.FirstName, People.LastName, People.BirthDate
FROM Employees FULL OUTER JOIN
People ON Employees.SystemAssignedPersonID = People.SystemAssignedPersonID
WHERE (People.BirthDate BETWEEN '10 - 01 - 1900' AND '12 - 15 - 2007')
ORDER BY People.BirthDate
<<<<<<<<<<<<<<<<<<<<<<<<<< Results
A
KWANG
TAK
10/25/1929 12:00:00 AM
T
ETHEL
MOE
5/24/1933 12:00:00 AM
T
GILBERT
BARAJAS
1/9/1937 12:00:00 AM
A
EDILBERTO
PENA
2/10/1937 12:00:00 AM
Strange Problem: SQL Insert Statement Does Not Insert All The Fields Into Table From Asp.net C# Webpage
Apr 21, 2008An 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
thanks
A Simple Insert
Mar 7, 2006Hi,
how do I perform a simple insert command?
I dont need the SQL syntax, just how do i do it using VS2005.
using SqlDataSource, for some reason, after writing the insert sentence, the "next" button remains gray.
i am using VS2005 and sql server.
Thank you.
Simple Insert Err
Mar 30, 2006I am trying to do a simple insert 3 items into 3 colms the table has 4 the 4th is an identity with a seed of 1
I am getting this error that i need an explicit val for the key
TABLE SCRIPT :
GO
/****** Object: Table [dbo].[Company] Script Date: 03/30/2006 17:48:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Company](
[idCompany] [int] IDENTITY(1,1) NOT NULL,
[CompanyName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Abreviation] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Sol] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_Company] PRIMARY KEY CLUSTERED
(
[idCompany] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
--SHOULDNT NEED THIS :
--SET IDENTITY_INSERT dbo.Company ON
INSERT INTO COMPANY
VALUES('AmericanExpressCheck','Amex','ax')
Msg 545, Level 16, State 1, Line 2
Explicit value must be specified for identity column in table 'Company' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column.
Help With Simple Insert
Feb 13, 2007Hello everyone:
From the help files in sql 2000:
INSERT INTO archivetitles
(title_id, title, type, pub_id)
SELECT title_id, title, type, pub_id
FROM titles
WHERE (pub_id = '0766')
How would I modify this if the table archivetitles had columns:
titleid, title, newcolumn, type, pub_id
and I wanted the newcolumn value to be null, but I still wanted to select the values from titles?
Cannot INSERT Data To 3 Tables Linked With Relationship (INSERT Statement Conflicted With The FOREIGN KEY Constraint Error)
Apr 9, 2007Hello
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.
Interaction Between Instead Of Insert Trigger And Output Clause Of Insert Statement
Jan 14, 2008
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
Any thoughts would be much appreciated
aero1
/************************************************
1) - Create the table
************************************************/
CREATE TABLE [dbo].[my_table](
[my_table_id] [bigint] IDENTITY(1,1) NOT NULL,
[forename] [varchar](100) NULL,
[surname] [varchar](50) NULL,
CONSTRAINT [pk_my_table] PRIMARY KEY NONCLUSTERED
(
[my_table_id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 70) ON [PRIMARY]
)
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
************************************************/
DECLARE @my_insert TABLE( my_table_id bigint )
declare @forename VARCHAR(100)
declare @surname VARCHAR(50)
set @forename = N'john'
set @surname = N'smith'
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
Network Performance For Simple Select Statement
Jul 23, 2005I've just inherited a system and have some concerns about the speed ofconnections to a remote server (SQL2000). If I do a simple selectstatement on the table below, it takes 14 minutes to retrive 6 millionrows across a 2Mb line. Obviously it's a reasonable amount of data toretrieve, but I would have thought this would be quicker if I'm honest.Run locally, this is 50 seconds.My thoughts are that there may be some issues with our connection (weget general network errors sporadically, which are being looked at),but wanted some thoughts if the performance is acceptable for what itis doing with what is available. I don't think there is a SQL issue,but want to check if this sounds about right.It's early days, so I'm after a general impression of the speed ofretrieval for the amount of data on the available bandwidth. Assuming abest performance scenario, what is the minimum time it should take as abest guess ?ThanksRyanCREATE TABLE [FIELD_VALUES] ([DEALER_DATA_ID] [int] NOT NULL ,[FIELD_CODE] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL ,[FIELD_VALUE] [numeric](15, 5) NULL ,[CHANGED_TYPE] [int] NULL ,CONSTRAINT [PK_FIELD_VALUES] PRIMARY KEY CLUSTERED([DEALER_DATA_ID],[FIELD_CODE]) WITH FILLFACTOR = 90 ON [PRIMARY]) ON [PRIMARY]GO
View 1 Replies View RelatedMissing Row In Simple Conditioned SELECT Statement
Nov 7, 2007Hey there everyone,
I'm sure there's a good reason for this, I just have no idea what it is.
If I run a SELECT * statement on one of my tables, the result set is missing one of the records.
If I SELECT that specific row (by identifier or anything else really), it returns just fine.
Any kind of select (e.g. WHERE ID > X) fails to return that specific row.
Any idea why this might be happening?
Thanks in advance for any guidance!
Simple Insert Into... MS SQL 2000
Mar 21, 2006Hello,I just started with ms sql. I need to insert data from some textboxes into ms sql 2000 database.I can't get it work.I'm trying this:"INSERT INTO tblUplate (ID_Uplata, JMB, IDOsnovaUplate, Iznos, Datum) Values('', ' + txtJMBUplate.Text + ', ' + ddVrstaPrihoda.SelectedItem + ', ' + txtIznos.Text + ', ' + DateTimePicker1.Value + ')"Is this wrong? Should I use + or & (like I used to use with MS Access)?Thanks
View 1 Replies View Related