SQL Insert Into With A Subselect --- HELP!
Nov 16, 2006
I'm working on a purchasing website for a store. A request has many line items, and a line item can have many products. One of the characteristics of the line item data table is a total price, calculated from multiplying lineitems.quantity and product.price.
INSERT INTO lineitems
(request_id, quantity, product_id, total_price)
VALUES (@rid,@quant,@pid,@totalprice)
WHERE @totalprice =
(SELECT products.price * @quant
FROM lineitems, products
WHERE lineitems.product_id = products.id)
Visual Studio isnt accepting this. Is there a way to do this better?
View 10 Replies
ADVERTISEMENT
Feb 27, 2006
Im not sure the best way to go about doing this
i want to pass 2 values to the procedure
one passed value will be a direct insert value
the other passed value will be used within a subselect
and the result from the subselct will be the second inserted value.
Code:
Create procedure InsertSubSelect
(
@Value1 nvarchar(50),
@Value2 nvarchar(50)
)
AS
INSERT INTO Mytable (field1, field2)
VALUES (@Value1,
(select Value2
from Mytable2
where Value2 = @Value2 ) )
Go
View 5 Replies
View Related
Mar 16, 2006
Is this syntax not supported by SQL Server 2000...?
select count(*) from (select useridentifier from registered_users
where useridentifier < -120)
View 5 Replies
View Related
Jan 24, 2007
I have two tables with the following relevant fields:
Apps
appID appName
PBC
pbcID appID appCT
These are joined on appID. appCT can be 1 of 2 values, either "PC" or "LA". So an example of a few records in PBC would be:
1 1 PC
2 1 LA
3 2 PC
4 2 LA
5 3 PC
6 4 LA
...
...
You can see that for each App, in PBC there can be two related records - PC and LA. But for example, record number 5 is App 3 PC, but there is no App 3 LA. I am trying to build a select to tell me which Apps are not in PBC at all, AND which Apps only have either LA or PC, not both.
Help?
Thanks!
View 5 Replies
View Related
Aug 1, 2004
How do you combine the following 2 updates into one Update statement (1 SUBSELECT statement)
Update SPLL_Policy
SET SPLL_Policy.Prog_Year =
(Select TOP 1 Prog_Year From SPLL_WinsPolicy_Input
Where SPLL_WinsPolicy_Input.Policy_Number = SPLL_Policy.Policy_Number
ORDER BY SPLL_WinsPolicy_Input.DATE_TIME_RECEIVED DESC)
Update SPLL_Policy
SET SPLL_Policy.Prog_NAME =
(Select TOP 1 Prog_Name From SPLL_WinsPolicy_Input
Where SPLL_WinsPolicy_Input.Policy_Number = SPLL_Policy.Policy_Number
ORDER BY SPLL_WinsPolicy_Input.DATE_TIME_RECEIVED DESC)
Thanks
Harold Hoffman
View 7 Replies
View Related
Feb 18, 2002
I have the following table:
CREATE TABLE ITEMS ([ITEMID] int, [itRULE] varchar(1))
INSERT INTO ITEMS (ITEMID, itRULE) VALUES (11, 3)
INSERT INTO ITEMS (ITEMID, itRULE) VALUES (12, 3)
INSERT INTO ITEMS (ITEMID, itRULE) VALUES (21, 2)
INSERT INTO ITEMS (ITEMID, itRULE) VALUES (22, 2)
INSERT INTO ITEMS (ITEMID, itRULE) VALUES (31, 1)
INSERT INTO ITEMS (ITEMID, itRULE) VALUES (32, 1)
INSERT INTO ITEMS (ITEMID, itRULE) VALUES (41, 0)
INSERT INTO ITEMS (ITEMID, itRULE) VALUES (42, 0)
-- Those works and gives me 11,12,21,22
SELECT ITEMID FROM ITEMS WHERE itRULE IN (2,3)
SELECT ITEMID FROM ITEMS WHERE itRULE IN ('2','3')
-- This doesn't works
declare @Rule varchar(10)
set @Rule='2,3'
SELECT ITEMID FROM ITEMS WHERE itRULE IN (@Rule)
Any idea?
I don't mind to change the data type if it works.
View 1 Replies
View Related
Aug 28, 2007
I have two tables in which I need to select data from and I don't know what construct to use. The two tables are SY and MV. SY contains stocks and MV contains a log of all price changes of these stocks. I need to produce a report of price changes between the latest price and the previous price and take the difference between the two. I'm using MSSQL. Here are the important fields in my tables:
SY - SY.SYID
MV - MV.SYID
MV - MV.PRICE
MV - MV.DATE
Here is what I have so far:
Code:
SELECT
sy.syid,
sy.sycode,
mv.price,
(SELECT TOP 1(mv.price) FROM MV, SY WHERE mv.syid = sy.syid AND mv.date < '8/27/2007' AND sy.ACTIVE = '1' ORDER BY mv.date DESC) AS lastprice,
(mv.price - (SELECT TOP 1(mv.price) FROM MV, SY WHERE mv.syid = sy.syid AND mv.date < '8/27/2007' AND sy.ACTIVE = '1' ORDER BY mv.date DESC)) AS diff,
(mv.price - (SELECT TOP 1(mv.price) FROM MV, SY WHERE mv.syid = sy.syid AND mv.date < '8/27/2007' AND sy.ACTIVE = '1' ORDER BY mv.date DESC)) /
(SELECT TOP 1(mv.price) FROM MV, SY WHERE mv.syid = sy.syid AND mv.date < '8/27/2007' AND sy.ACTIVE = '1' ORDER BY mv.date DESC) * 100 AS percentdiff,
mv.date
FROM
mv,
sy
WHERE
mv.syid = sy.syid
AND mv.date = '8/27/2007'
AND sy.ACTIVE = '1'
ORDER BY
sy.syid,
mv.date
DESC
View 1 Replies
View Related
May 20, 2008
Hi,
I have a query which contains 2 subselects joined with a union all. The select for each is just a count, so I'm only returning 2 rows. I then want to be able to perform a calculation between these 2 results... ie divide one by the other to get the percentage.
The only way I could think of doing that was make the whole query a subselect of another query where I could then perform the calculation in the new select statement, however it doesn't like this. I just get incorrect syntax near the closing bracket of the from section.
Any ideas? Thanks!
SELECT *
FROM
(SELECT count(t0.product)
FROM
(SELECT t0.packslip ,
t1.date_upld ,
t0.product AS product ,
t0.qty_topick as topick ,
t0.qty_picked as picked ,
t0.qty_topick - t0.qty_picked as shorted,
(t0.qty_picked / t0.qty_topick) * 100 as linefill
FROM rbeacon.dbo.shipline2 t0
INNER JOIN rbeacon.dbo.shiphist t1
ON t0.packslip = t1.packslip
WHERE t1.date_upld = CONVERT(VARCHAR(10), GETDATE()-3, 101)) t0
UNION ALL
SELECT count(t1.product)
FROM
(SELECT t0.packslip ,
t1.date_upld ,
t0.product AS product ,
t0.qty_topick as topick ,
t0.qty_picked as picked ,
t0.qty_topick - t0.qty_picked as shorted,
(t0.qty_picked / t0.qty_topick) * 100 as linefill
FROM rbeacon.dbo.shipline2 t0
INNER JOIN rbeacon.dbo.shiphist t1
ON t0.packslip = t1.packslip
WHERE t1.date_upld = CONVERT(VARCHAR(10), GETDATE()-3, 101)
AND t0.qty_picked <> t0.qty_topick) t1) t2
View 11 Replies
View Related
Aug 31, 2007
Hello,I have a problem with a subselect I use in a stored procedure:UPDATE #TEMP_TABLESET P_ID_1=(SELECT top 1 b.P_ID_1 from #TEMP_TABLE b whereb.ID=PARENT_ID),P_ID_2=PARENT_ID,P_ID_3=IDWHERE PARENT_ID IN (SELECT P_ID_2FROM #TEMP_TABLE b)So the subselect is (SELECT top 1 b.P_ID_1 from #TEMP_TABLE b whereb.ID=PARENT_ID), and it returns NULL. The cause of that is mostprobably the fact that I try to link ID from inner table b withPARENT_ID from the outer table. I thought it had to be done this way,but obviously not. Can somebody help me with this syntax problem?Thx,Bart
View 5 Replies
View Related
Jul 20, 2005
Not sure if this is the right group to post this to but.This is the current query that I have.select tableA.id,tableB.artist,tableB.image,from tableA,tableB wheretableA.image = tableB.image AND tableB.price >0 AND tableB.price < 20order by tableB.price DESC'What I need is, for each row returned I need information from a thirdand fourth table. tableC, and tableD.tableC has information ( the tableA.id = tableC.eventId) that I need toobtain tableC.accountId = tableD.accountId in order do select thethe binding information in tableD between a Vendor(name,address..etc..)and tableB.imageAny help would be greatly appreciated.
View 2 Replies
View Related
Sep 26, 2013
Is possible to use like hint with subselect? , i mean i want to find all rows in table A that contains a word in a field(CALLED CONTENT) in table B, concretely in a field called content too, i show you the idea although the syntax is incorrect.
select
' + char (39) + @country + char (39) + ' as PAIS,
A.ID,
A.IDUSUARIO MSISDN,
NULL AS MSISDN_COD,
convert(char(19),A.FECHA_ALVENTO, 121) AS FECHA_MO_LOCAL,
NULL AS FECHA_MO_LOCAL_D,
[code]...
View 2 Replies
View Related
Mar 2, 2007
Hi,
Hope someone could help me in revising a long running query. Here is the query
select *
from table1
where classid is null
and productid not in (
select productid
from table1
where classid = 67)
In here table1 could have several occurance of productid in which productid could have different classid. The possible values of classid are: NULL,1,2,3,67. Basically I am looking for all records whose classid is null but should never had an instance in table1 where its classid is 67.
Do you have something like a "join" statment that will only include all records in the left table that is not in the right table?
Hope someone could help me with this. Thanks in advance.
-Ruel
View 9 Replies
View Related
Mar 2, 2007
Hi,
Hope someone could help me in revising a long running query. Here is the query
select *
from table1
where classid is null
and productid not in (
select productid
from table1
where classid = 67)
In here table1 could have several occurance of productid in which productid could have different classid. The possible values of classid are: NULL,1,2,3,67. Basically I am looking for all records whose classid is null but should never had an instance in table1 where its classid is 67.
Do you have something like a "join" statment that will only include all records in the left table that is not in the right table?
Hope someone could help me with this. Thanks in advance.
-Ruel
View 4 Replies
View Related
Jul 20, 2005
I have a stored procedure what produces N number of rows.The rows are ordered by a cataegoryType as followscatAcatBcatCWhat is needed to do on the C++ code side is break these out intotheir respective categories by iterating through the rows and checkingthe category type. Is there a way to let the DB do this via some sort ofsubselect on the rows returned via the stored procedure.Thanks in advance.
View 3 Replies
View Related
May 31, 2007
I have a sqldatasource, and on the selectcommand I'm trying to use a case statement with a subselect. The case statement works fine without the subselect, but I'm trouble getting it to work with the case statement. Could you help me with the syntax? ThanksSelectCommand="
SELECT DISTINCT RecipeID, Title
FROM [Recipes]
WHERE
(CASE WHEN @Type='Appetizer' THEN Appetizer
WHEN @Type='Pies' THEN
(Select Distinct RecipeID, Title From Recipes
WHERE Title like '%Pie%')
WHEN @Type='Beverages' THEN Beverage
WHEN @Type='Dessert' THEN Dessert
WHEN @Type='Kids' THEN Kids
WHEN @Type='Side' THEN Side
WHEN @Type='Soup' THEN Salad
WHEN @Type='Main' THEN Main
WHEN @Type='Breakfast' THEN Breakfast
END) = 1"
View 1 Replies
View Related
Jul 20, 2005
Help!I'm trying to understand the new ANSI join syntax (after many years ofcoding using the old style). I am now working with an application that onlyunderstands ANSI syntax so I am struggling.My first (old style syntax) SQL statement below produces 60 rows:SELECT A1.CONTACTID, A1.LASTNAME, A1.FIRSTNAME, A1.ACCOUNT,A6.CITY, A6.STATE, A1.WORKPHONE, A1.FAX, A1.EMAILFROM CONTACT A1,ADDRESS A6WHERE A1.ADDRESSID=A6.ADDRESSIDAND A1.CONTACTID IN(SELECT A4.CONTACTIDFROM CONTACT_LEADSOURCE A4,LEADSOURCE A5WHERE A4.LEADSOURCEID = A5.LEADSOURCEIDAND A5.DESCRIPTION = 'some_description' )AND A1.CONTACTID IN(SELECT A2.CONTACTIDFROM TICKET A2,ENROLLHX A3,EVENT A7WHERE A3.STATUS IN ('R', 'Confirmed')AND A2.TICKETID = A3.EVXEVTICKETIDAND A3.EVENTID = A7.EVENTIDAND A7.CODE IN('AHS00','AHS01','AHS02','AHS03','AHS04','AHS98',' AHS99'))ORDER BY A1.LASTNAME ASCI am trying to convert this to the newer ANSI sytax. My second SQL statementbelow produces 67 rows (duplicates):SELECT A1.CONTACTID, A1.LASTNAME, A1.FIRSTNAME, A1.ACCOUNT,A6.CITY, A6.STATE, A1.WORKPHONE, A1.FAX, A1.EMAILFROM CONTACT A1JOIN ADDRESS A6 ON (A1.ADDRESSID=A6.ADDRESSID)JOIN( SELECT C.CONTACTIDFROM CONTACT CJOIN CONTACT_LEADSOURCE A4 ON (C.CONTACTID= A4.CONTACTID)JOIN LEADSOURCE A5 ON (A4.LEADSOURCEID =A5.LEADSOURCEIDAND A5.DESCRIPTION ='some_description' )) AS C1 ON C1.CONTACTID = A1.CONTACTIDJOIN(SELECT C2.CONTACTIDFROM CONTACT C2JOIN TICKET A2 ON (C2.CONTACTID =A2.CONTACTID)JOIN ENROLLHX A3 ON (A2.TICKETID =A3.TICKETID AND A3.STATUS in ('R', 'Confirmed'))JOIN EVENT A7 ON (A3.EVENTID = A7.EVENTIDAND A7.CODE IN ('AHS00','AHS01','AHS02','AHS03','AHS04','AHS98',' AHS99')))AS C3 ON C3.CONTACTID = A1.CONTACTIDCan anyone shed some light on what I am missing?cheers,Norm
View 3 Replies
View Related
Jun 15, 2015
I'm running the following test query on a single table:
SELECT sph.datestamp, sph.stocksymbol, sph.closing, DATENAME(dw, sph.datestamp),
CASE DATENAME(dw, sph.datestamp)Â Â Â
WHEN 'Monday' then 'Monday'Â Â
ELSE (SELECT CAST(sph2.datestamp AS nvarchar) FROM BI_Test.dbo.StockDB AS sph2 WHERE sph2.DateStamp = DATEADD(d, -1, sph.datestamp) AND sph2.StockSymbol = 'NYA')Â
END AS TestCase,
[Code] ....
And here's an example of the output I'm getting:
Why the exact same subquery in the THEN of the second CASE statement is returning NULL when the first one completes as expected?
View 7 Replies
View Related
Apr 21, 2008
Hello,
I've got the following query:
SELECT zA."ID" AS fA_A
, zA."TEXT" AS fA_B
, (
SELECT COUNT(zC."ID")
FROM Test."Booking" AS zC
) AS fA_E
FROM Test."Stack" AS zA
WHERE zA."ID" = ?
With this query I call:
- SQLPrepare -> SQL_SUCCESS=0
- SQLNumParams -> SQL_SUCCESS=0, pcpar = 1
- SQLDescribeParam( 1 ) -> SQL_ERROR=-1, [Microsoft][ODBC SQL Server Driver]Invalid parameter number", "[Microsoft][ODBC SQL Server Driver]Invalid Descriptor Index"
Is there a problem with this calling sequence or this query? Or is this a problem of SQL Server?
Regards
Markus
View 7 Replies
View Related
Jun 4, 2008
hello friends
my one insert code lines is below :) what does int32 mean ? AND WHAT IS DIFFERENT BETWEEN ONE CODE LINES AND SECOND CODE LINES :)Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
Dim cmd As New SqlCommand("Insert into table1 (UserId) VALUES (@UserId)", conn)
'you should use sproc instead
cmd.Parameters.AddWithValue("@UserId", textbox1.text)
'your value
Try
conn.Open()Dim rows As Int32 = cmd.ExecuteNonQuery()
conn.Close()Trace.Write(String.Format("You have {0} rows inserted successfully!", rows.ToString()))
Catch sex As SqlExceptionThrow sex
Finally
If conn.State <> Data.ConnectionState.Closed Then
conn.Close()
End If
End Try
MY SECOND INSERT CODE LINES IS BELOWDim SglDataSource2, yeni As New SqlDataSource()
SglDataSource2.ConnectionString = ConfigurationManager.ConnectionStrings("ConnectionString").ToString
SglDataSource2.InsertCommandType = SqlDataSourceCommandType.Text
SglDataSource2.InsertCommand = "INSERT INTO urunlistesi2 (kategori1) VALUES (@kategori1)"
SglDataSource2.InsertParameters.Add("kategori1", kategoril1.Text)Dim rowsaffected As Integer = 0
Try
rowsaffected = SglDataSource2.Insert()Catch ex As Exception
Server.Transfer("yardim.aspx")
Finally
SglDataSource2 = Nothing
End Try
If rowsaffected <> 1 ThenServer.Transfer("yardim.aspx")
ElseServer.Transfer("urunsat.aspx")
End If
cheers
View 2 Replies
View Related
Nov 14, 2007
I have a web form with a text field that needs to take in as much as the user decides to type and insert it into an nvarchar(max) field in the database behind. I've tried using the new .write() method in my update statement, but it cuts off the text after a while. Is there a way to insert/update in SQL 2005 this without resorting to Bulk Insert? It bloats the transaction log and turning the logging off requires a call to sp_dboptions (or a straight-up ALTER DATABASE), which I'd like to avoid if I can.
View 6 Replies
View Related
Apr 21, 2008
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
thanks
View 7 Replies
View Related
Apr 9, 2007
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.
View 5 Replies
View Related
Mar 24, 2008
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 + ' ')
View 4 Replies
View Related
Sep 11, 2006
Hello, I'm new to the forum and new to SQL, ASP.NET, etc. I am creating an intranet site for my company in VS 2005 and have run into a very annoying problem that I can't seem to solve. I have tried Googling it and came up empty. I have a database in SQL Express 2005 and my website will be accessing several tables within the database. I can retrieve info just fine and I can update, delete, etc just fine using gridview or other prebuilt tools, but when I add a few text boxes and wire a button to the SqlDataSource.Insert() command, I get a new record that is full of null values except for the identity key I have set. The kicker is that I am also using a master page and when I duplicate the web page without the master page link, everything works just fine. The following snippets show what I'm doing:<InsertParameters><asp:FormParameter Name="Name" Type="String" FormField="txtName" /><asp:FormParameter Name="Location" Type="String" FormField="ddlLocation" /><asp:FormParameter Name="Issue" Type="String" FormField="txtProblem" /></InsertParameters>Of course I match the formfields to the text boxes, create an onclick event for my button, the sqldatasource is configured correctly, it just doesn't work with the master page no matter what I do. Any help would be appreciated. Thanks
View 3 Replies
View Related
Apr 20, 2008
On my site users can register using ASP Membership Create user Wizard control.
I am also using the wizard control to design a simple question and answer form that logged in users have access to.
it has 2 questions including a text box for Q1 and dropdown list for Q2.
I have a table in my database called "Players" which has 3 Columns
UserId Primary Key of type Unique Identifyer
PlayerName Type String
PlayerGenre Type Sting
On completing the wizard and clicking the finish button, I want the data to be inserted into the SQl express Players table.
I am having problems getting this to work and keep getting exceptions.
Be very helpful if somebody could check the code and advise where the problem is??
<asp:Wizard ID="Wizard1" runat="server" BackColor="#F7F6F3"
BorderColor="#CCCCCC" BorderStyle="Solid" BorderWidth="1px"
DisplaySideBar="False" Font-Names="Verdana" Font-Size="0.8em" Height="354px"
onfinishbuttonclick="Wizard1_FinishButtonClick" Width="631px">
<SideBarTemplate>
<asp:DataList ID="SideBarList" runat="server">
<ItemTemplate>
<asp:LinkButton ID="SideBarButton" runat="server" BorderWidth="0px"
Font-Names="Verdana" ForeColor="White"></asp:LinkButton>
</ItemTemplate>
<SelectedItemStyle Font-Bold="True" />
</asp:DataList>
</SideBarTemplate>
<StepStyle BackColor="#669999" BorderWidth="0px" ForeColor="#5D7B9D" />
<NavigationStyle VerticalAlign="Top" />
<WizardSteps>
<asp:WizardStep runat="server">
<table class="style1">
<tr>
<td class="style4">
A<span class="style6">Player Name</span></td>
<td class="style3">
<asp:TextBox ID="PlayerName" runat="server"></asp:TextBox>
</td>
<td>
<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server"
ControlToValidate="PlayerName" ErrorMessage="RequiredFieldValidator"></asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td class="style5">
<td class="style3">
<asp:DropDownList ID="PlayerGenre" runat="server" Width="128px">
<asp:ListItem Value="-1">Select Genre</asp:ListItem>
<asp:ListItem>Male</asp:ListItem>
<asp:ListItem>Female</asp:ListItem>
</asp:DropDownList>
</td>
<asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server"
ControlToValidate="PlayerGenre" ErrorMessage="RequiredFieldValidator"></asp:RequiredFieldValidator>
</td>
</tr>
</table>
Sql Data Source
<asp:SqlDataSource ID="InsertArtist1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" InsertCommand="INSERT INTO [Playerst] ([UserId], [PlayerName], [PlayerGenre]) VALUES (@UserId, @PlayerName, @PlayerGenre)"
ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>">
<InsertParameters>
<asp:Parameter Name="UserId" Type="Object" />
<asp:Parameter Name="PlayerName" Type="String" />
<asp:Parameter Name="PlayerGenre" Type="String" />
</InsertParameters>
</asp:SqlDataSource>
</asp:WizardStep>
Event Handler
To match the answers to the user I get the UserId and insert this into the database to.protected void Wizard1_FinishButtonClick(object sender, WizardNavigationEventArgs e)
{
SqlDataSource DataSource = (SqlDataSource)Wizard1.FindControl("InsertArtist1");
MembershipUser myUser = Membership.GetUser(this.User.Identity.Name);
Guid UserId = (Guid)myUser.ProviderUserKey;String Gender = ((DropDownList)Wizard1.FindControl("PlayerGenre")).SelectedValue;
DataSource.InsertParameters.Add("UserId", UserId.ToString());DataSource.InsertParameters.Add("PlayerGenre", Gender.ToString());
DataSource.Insert();
}
View 1 Replies
View Related
Oct 12, 2007
Hi,
i have a file which consists data as below,
3
123||
456||
789||
Iam reading file using bulk insert and iam inserting these phone numbers into table having one column as below.
BULK INSERT TABLE_NAME FROM 'FILE_PATH'
WITH (KEEPNULLS,FIRSTROW=2,ROWTERMINATOR = '||')
but i want to insert the data into table having two columns. if iam trying to insert the data into table having two columns its not inserting.
can anyone help me how to do this?
Thanks,
-Badri
View 5 Replies
View Related
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
View 5 Replies
View Related
Feb 22, 2008
Hi All
I decided to change over from Microsoft Access Database file to the New SQLServerCe Compact edition. Although the reading of data from the database is greatly improved, the inserting of the new rows is extremely slow.
I was getting between 60 to 70 rows per sec using OLEDB and an Access Database but now only getting 14 to 27 rows per sec using SQLServerCe.
I have tried the below code changes and nothing seams to increase the speed, any help as I would prefer to use SQLServerCe as the database is much smaller and I€™m use to SQL Commands.
Details:
VB2008 Pro
.NET Frameworks 2.0
SQL Compact Edition V3.5
Encryption = Engine Default
Database Size = 128Mb (But needs to be changes to 999Mbs)
Where Backup_On_Next_Run, OverWriteQuick, CompressAns are Booleans, all other column sizes are nvarchar and size 10 to 30 expect for Full Folder Address size 260
TRY1
Direct Insert Using Data Adapter.
Me.BackupDatabaseTableAdapter1.Insert(Group_Name1, Full_Folder_Address1, File1, File_Size_KB1, Schedule_To_Run1, Backup_Time1, Last_Run1, Result1, Last_Modfied1, Last_Modfied1, Backup_On_Next_Run1, Total_Backup_Times1, Server_File_Number1, Server_Number1, File_Break_Down1, No_Of_Servers1, Full_File_Address1, OverWriteQuick, CompressAns)
14 to 20 rows per second (Was 60 to 70 when using OLEDB Access)
TRY 2
Using Record Sets
Private Sub InsertRecordsIntoSQLServerce(ByVal Group_Name1 As String, ByVal Full_Folder_Address1 As String, ByVal File1 As String, ByVal File_Size_KB1 As String, ByVal Schedule_To_Run1 As String, ByVal Backup_Time1 As String, ByVal Last_Run1 As String, ByVal Result1 As String, ByVal Last_Modfied1 As String, ByVal Latest_Modfied1 As String, ByVal Backup_On_Next_Run1 As Boolean, ByVal Total_Backup_Times1 As String, ByVal Server_File_Number1 As String, ByVal Server_Number1 As String, ByVal File_Break_Down1 As String, ByVal No_Of_Servers1 As String, ByVal Full_File_Address1 As String, ByVal OverWriteQuick As Boolean, ByVal CompressAns As Boolean)
Dim conn As SqlCeConnection = Nothing
Dim CommandText1 As String = "INSERT INTO BackupDatabase (Group_Name, Full_Full_Folder_Adress, File1,File_Size_KB, Schedule_To_Run, Backup_Time, Last_Run, Result, Last_Modfied, Latest_Modfied, Backup_On_Next_Run, Total_Backup_Times, Server_File_Number, Server_Number, File_Break_Down, No_Of_Servers, Full_File_Address, OverWrite, Compressed) VALUES ('" & Group_Name1 & "', '" & Full_Folder_Address1 & "', '" & File1 & "', '" & File_Size_KB1 & "', '" & Schedule_To_Run1 & "', '" & Backup_Time1 & "', '" & Last_Run1 & "', '" & Result1 & "', '" & Last_Modfied1 & "', '" & Latest_Modfied1 & "', '" & CStr(Backup_On_Next_Run1) & "', '" & Total_Backup_Times1 & "', '" & Server_File_Number1 & "', '" & Server_Number1 & "', '" & File_Break_Down1 & "', '" & No_Of_Servers1 & "', '" & Full_File_Address1 & "', '" & CStr(OverWriteQuick) & "', '" & CStr(CompressAns) & "')"
Try
conn = New SqlCeConnection(strConn)
conn.Open()
Dim cmd As SqlCeCommand = conn.CreateCommand()
cmd.CommandText = "SELECT * FROM BackupDatabase"
cmd.ExecuteNonQuery()
Dim rs As SqlCeResultSet = cmd.ExecuteResultSet(ResultSetOptions.Updatable Or ResultSetOptions.Scrollable)
Dim rec As SqlCeUpdatableRecord = rs.CreateRecord()
rec.SetString(1, Group_Name1)
rec.SetString(2, Full_Folder_Address1)
rec.SetString(3, File1)
rec.SetSqlString(4, File_Size_KB1)
rec.SetSqlString(5, Schedule_To_Run1)
rec.SetSqlString(6, Backup_Time1)
rec.SetSqlString(7, Last_Run1)
rec.SetSqlString(8, Result1)
rec.SetSqlString(9, Last_Modfied1)
rec.SetSqlString(10, Latest_Modfied1)
rec.SetSqlBoolean(11, Backup_On_Next_Run1)
rec.SetSqlString(12, Total_Backup_Times1)
rec.SetSqlString(13, Server_File_Number1)
rec.SetSqlString(14, Server_Number1)
rec.SetSqlString(15, File_Break_Down1)
rec.SetSqlString(16, No_Of_Servers1)
rec.SetSqlString(17, Full_File_Address1)
rec.SetSqlBoolean(18, OverWriteQuick)
rec.SetSqlBoolean(19, CompressAns)
rs.Insert(rec)
Catch e As Exception
MessageBox.Show(e.Message)
Finally
conn.Close()
End Try
End Sub
€™20 to 24 rows per sec
TRY 3
Using SQL Commands Direct
Private Sub InsertRecordsIntoSQLServerce(ByVal Group_Name1 As String, ByVal Full_Folder_Address1 As String, ByVal File1 As String, ByVal File_Size_KB1 As String, ByVal Schedule_To_Run1 As String, ByVal Backup_Time1 As String, ByVal Last_Run1 As String, ByVal Result1 As String, ByVal Last_Modfied1 As String, ByVal Latest_Modfied1 As String, ByVal Backup_On_Next_Run1 As Boolean, ByVal Total_Backup_Times1 As String, ByVal Server_File_Number1 As String, ByVal Server_Number1 As String, ByVal File_Break_Down1 As String, ByVal No_Of_Servers1 As String, ByVal Full_File_Address1 As String, ByVal OverWriteQuick As Boolean, ByVal CompressAns As Boolean)
Dim conn As SqlCeConnection = Nothing
Dim CommandText1 As String = "INSERT INTO BackupDatabase (Group_Name, Full_Full_Folder_Adress, File1,File_Size_KB, Schedule_To_Run, Backup_Time, Last_Run, Result, Last_Modfied, Latest_Modfied, Backup_On_Next_Run, Total_Backup_Times, Server_File_Number, Server_Number, File_Break_Down, No_Of_Servers, Full_File_Address, OverWrite, Compressed) VALUES ('" & Group_Name1 & "', '" & Full_Folder_Address1 & "', '" & File1 & "', '" & File_Size_KB1 & "', '" & Schedule_To_Run1 & "', '" & Backup_Time1 & "', '" & Last_Run1 & "', '" & Result1 & "', '" & Last_Modfied1 & "', '" & Latest_Modfied1 & "', '" & CStr(Backup_On_Next_Run1) & "', '" & Total_Backup_Times1 & "', '" & Server_File_Number1 & "', '" & Server_Number1 & "', '" & File_Break_Down1 & "', '" & No_Of_Servers1 & "', '" & Full_File_Address1 & "', '" & CStr(OverWriteQuick) & "', '" & CStr(CompressAns) & "')"
Try
conn = New SqlCeConnection(strConn)
conn.Open()
Dim cmd As SqlCeCommand = conn.CreateCommand()
cmd.CommandText = CommandText1
'cmd.CommandText = "INSERT INTO BackupDatabase (€¦"
cmd.ExecuteNonQuery()
Catch e As Exception
MessageBox.Show(e.Message)
Finally
conn.Close()
End Try
End Sub
€˜ 25 to 30 but mostly holds around 27 rows per sec I
Is this the best you can get or is there a better way. Any help would be greatly appericated
Kind Regards
John Galvin
View 3 Replies
View Related
Apr 11, 2008
Hi,ALL
I wants to insert data into multiple table within a single insert query
Thanks
View 3 Replies
View Related
Mar 1, 2004
Hi
I am trying to use multiple insert for a table T1 to add multiple rows.
Ti has trigger for insert to add or update multiple rows in Table T2.
When I provide multiple insert SQL then only first insert works while rest insert statements does not work
Anybody have any idea about why only one insert works for T1
Thanks
View 10 Replies
View Related
Oct 23, 2014
I'm working on inserting data into a table in a database. The table has two separate triggers, one for insert and one for update (I don't like it this way, but that's how it's been for years). When there is a normal insert, done via a program, it looks like the triggers work fine. When I run an insert manually via a script, the first insert trigger will run, but the update trigger will fail. I narrowed down the issue to a root cause.
This root issue is due to both triggers using the same temporary table name. When the second trigger runs, there's an error stating that a few columns don't exist. I went to my test server and test db and changed the update trigger so that the temporary table is different than the insert trigger temporary table, the triggers work fine. The weird thing is that if the temporary table already exists, when the second trigger tries to create the temporary table, I would expect it to fail and say that it already exists.I'm probably just going to update the trigger tonight and change the temporary table name.
View 1 Replies
View Related
Apr 24, 2008
I am currently using openquery to insert data into a SQL 2000 database from a Lotus Notes database. The Lotus database is a linked server with a datasource named CLE_CARS_SF. My SQL table is called Webcases.
The query below works well because the table's columns are even in both databases:
Insert into Webcases select * from openquery(CLE_CARS_SF,
'Select * from Web_Cases')
I am moving this over to SQL 2005. The query works well, but I want to add a column to the Webcases SQL database and manually insert a value along with the openquery values.
My insert statement above no longer works because the column numbers don't match.
In a nutshell I would like a way to combine the following queries:
Insert into Webcases select * from openquery(CLE_CARS_SF,
'Select * from Web_Cases')
Insert into Webcases (insurancetype) Values ('SF')
--insurancetype is the new column.
View 9 Replies
View Related
Jun 28, 2007
Help please!
I have an asp page with some simple vbscript to add a record to a table, the record has a datefield (dob).
the insert results in a US formated date if I add a record to a dynamic recordset but a UK formated date if I insert direct to the table ?????
i.e.
if request("dob") is "01/11/2007" (1st november 2007)
set conn = server.createobject("adodb.connection")
set rs = server.createobject("adodb.recordset")
rs.open "tez", mc, 2, 2 rs.addnew
rs("dob") = request("dob")
rs.update
11 jan 2007 stored in table
while
set trs = Server.CreateObject("ADODB.RecordSet")
qfn= "insert tez values('"+request("dob")+"')"
trs.Open qfn,mc
results in
1 november 2007 is written to the table.
Both of these methods are used in the same asp page.
This is on a windows2003 server, sql2005,iisv6, asp.netv2
I have tried every setting I can find in iis,asp,sql server to no avail.
I need the recordset method to work correctly.
Terry
View 8 Replies
View Related
