This only happens when FieldTypeID = 5.
So, I need an insert query for when FieldTypeID = 5, to insert 5 rows into the Table FieldListValues(ItemDetailID, [value])
I have created a function to split the [Value] into a table of INTs
Hi, I am a rookie at sql and asp.net. I have another post is the asp.net section http://forums.asp.net/thread/1589094.aspx. Maybe I posted it in the wrong section. I am collecting multiple rows from a gridview. I validated/confirmed I am capturing the correct values. How ever, I am having major problems passing these to an sql database. The problems that I know of is declaring my parameters correctly and then adding the values through a for each statement. I added the post over 30 hours ago with only me as the replier trying to refining or clarifying the problem. Part 1 is the code that works, part 2 is my problem, I have tried may different ways to resolve this but no luck. Regards! Protected Sub Botton1_click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click**************** PART1 **************************Dim gvIDs As String = ""Dim ddIDs As String = ""Dim chkBox As Boolean = FalseDim chkBox1 As Boolean = False'Navigate through each row in the GridView for checkbox items ddIDs = DropDownList1.SelectedValue.ToStringFor Each gv As GridViewRow In GridView1.RowsDim addChkBxItem As CheckBox = CType(gv.FindControl("delCheckBox"), CheckBox)Dim addChkBx1Item As CheckBox = CType(gv.FindControl("CheckBox1"), CheckBox) If addChkBxItem.Checked Then chkBox = True gvIDs = CType(gv.FindControl("TestID"), Label).Text.ToString Response.Write(ddIDs + " " + gvIDs + " ") If addChkBx1Item.Checked Then chkBox1 = True Response.Write("Defaut Item") End If Response.Write("<br />")End IfNext******************** Part 2 *****************************************************Dim cn As SqlConnection = New SqlConnection(SqlDataSource1.ConnectionString)If chkBox ThenTry Dim insertSQL As String = "INSERT INTO testwrite (TestLast, test1ID, TestDefault) VALUES (@TestLast, @test1ID, @TestDefault)" Dim cmd As SqlCommand = New SqlCommand(insertSQL, cn) cmd.Parameters.AddWithValue("@TestLast", DropDownList1.SelectedValue.ToString) cmd.Parameters.AddWithValue("@test1ID", CType(gv.FindControl("TestID"), Label).Text.ToString) cmd.Parameters.AddWithValue("@TestDefault, CType(gv.FindControl("CheckBox1"), CheckBox)) cn.Open() For Each ..... Problems here also Next cmd.ExecuteNonQuery()Catch err As SqlException Response.Write(err.Message.ToString)Finally cn.Close()End TryEnd If
I need to insert records in two tables, one is main table and another is child table. From my aspx page I need to pass info. for one records in main table, insert that record into main table, get the is of the inserted table.
Then insert 15 records in the child table.
Everything must be in a transaction, either everything works or everything fails. Should I do it with aspx or should I pass arrays to a stored procedure?
Hi.. I really need a help. Is there any way to insert multiple records into a table in one go?
I have a table named Fruit. It contains FruitId,OwnerId,Colour
The list of colour is got from another table name FruitColour. FruitColour Consists of 2 column, FruitName and Colour.
Is it possible to insert multiple records into Fruit table with one query if only the colour is changed.
Sample case I have an Apple. Fruit id=2 OwnerId=2 Colour -- > Select Colour From FruitColour where FruitName='Apple' (Will return multiple records)
I tried to insert using this query: Insert into Fruit(FruitId,OwnerId,Colour) Values (2,2,Select Colour from FruitColour where FruitName='Apple').
Gives me this error Subqueries are not allowed in this context. Only scalar expressions are allowed.
I need to do this because actually I am inserting multiple fruit at one time, and each have multiple colour. If I need to insert the colour one by one for each fruit it will takes a very long time.
Any suggestion are welcomed. Thank you in advanced.
Have a table that is used during a conversion. It is hit pretty heavily with inserts by mulitple session ids. For performance reasons I cannot add a unique constraint on the column that is getting duplicates (it is an encrypted cell in varbinary).
I do not want duplicates in this Encrypted Column. So before each insert the insert programs reads the table and verifies that the Encrypted Column value does not already exist. But unfortunately several duplicates are falling through the cracks. What are my options for not allowing duplicates?
Is there a way to insert multiple records into a database table when you're just given "count" of the number of rows you want? I want to do this in ONE insert statment, so I don't want a solution that loops round doing 100 inserts - that would be too inefficient.
For example, suppose I want to create 100 card records starting it card number '1234000012340000'. Something like this ...
declare @card_start dec(16) set @card_start = '1234000012340000' declare @card_count int set @card_count = 100
In the ECASE table there is trigger to get the max value of case_id column in ecase based on project and increment one to that case_id value and insert into ecase table .
When we insert a new record to the ECASE table this trigger calls and insert the case_id column value.
When i run with multiple threads , the transaction is rolled back because of trigger . The reason is , on the project table the lock is happening while getting the max value of case_id column based on project.
writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.
Ok, I'm really new at this, but I am looking for a way to automatically insert new records into tables. I have one primary table with a primary key id that is automatically generated on insert and 3 other tables that have foreign keys pointing to the primary key. Is there a way to automatically create new records in the foreign tables that will have the new id? Would this be a job for a trigger, stored procedure? I admit I haven't studied up on those yet--I am learning things as I need them. Thanks.
Hi, I'm using VWD 2005 Express with SQL Server 2005 Express. I've created a page "create.aspx" which will be used to insert records into a table within the database. I've used the DetailsView control together with a SqlDataSource control on the create.aspx page. When I run the create.aspx page it retrieves the first record in the table of the database becuase the SqlDataSource is obviously performing a"Select * from Table" command. I don't want this to occur. I want an empty page with no records. I know I can set a WHERE clause to a value that will never return any results but I don't beleieve this is an elegant way of doing things becuase I am 'hitting the database' for no reason other than to get the table structure poplutaed into the DetailsView control. Questions: 1. Am I using the right controls? (in particular the SqlDataSource control) 2. Is there a better way of doing this? Your advice is most appreciated. Thank you
pls i have this issue on inserting records into SQL SERVER Express. this is the code so far :- imports system.data imports system.data.sqlclient Protected Sub btn1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btn1.Click Dim strConn As String = ConfigurationManager.ConnectionStrings("Connect1").ConnectionString Dim ObjConn As New SqlConnection(strConn) Dim strComm As String = "insert Products(ProductName,UnitPrice) values(@ProductName,@UnitPrice)" Dim ObjComm As New SqlCommand(strComm, ObjConn) ObjComm.Parameters.AddWithValue("@ProductName", txtprodname.Text) ObjComm.Parameters.AddWithValue("@UnitPrice", SqlDbType.Money).Value = txtprice.Text ObjConn.Open() ObjComm.ExecuteNonQuery() ObjConn.Close() End Sub End Class The issue i have is that anytime i click the button to insert a new record, i get two records added to the database automatically
hi im a beginner trying to build an application that needs the user to enter some data into a table named Delievary. This table is related to another table named Products. The Products table has the following columns :- ProductsId - PK ProductName ProductPrice Delievary table has the following columns :- DelivaryId - PK QuantityRecieved ProductId - FK i know to select i have to use inner joins but to insert is what i dont really know about. Please help me out
Ok i hav three tables namely customers, orders and products. This are there structures Customers-: CustomerId-PK FirstName LastName Address Orders -: OrderId -PK QuantityOrderd OrderDate CustomerId -FK ProductId -FK Products -: ProductId -PK ProductName UnitPrice my question is that i need to relate them so that i can get info on a customer order Thanks
Before I start driving myself nuts, I'd like to make sure my approach is correct.I want to create a simple job posting board.I have a text boxes for company name, email, job title, and job description, and a submit button.I created a table in my database called "JobPostings", with columns called "CoName", "CoEmail", "JobTitle", and "JobDesc"When someone fills out the fields and clicks submit, it will insert the new records.So,1. Is this the correct approach so far?2. What is the best way to display the job listings? A grid view?3. At submit time, how can I include that day's date?4. How can I get the records in the database to delete after 90 days?Thanks.
I have a table with sixty columns in it, five of which define uniqueness for the records. Currently there are 190,775 records in the table. One of the records is a duplicate. I need to insert only the unique records from this table (all columns) into another table. I cannot use a unique nonclistered index with IGNORE_DUP_KEY in the destination table because of a problem I am having with the 'duplicate key was ignored' message. The destination table has a primary key with a clustered index on the same five columns.
How can I put together a SELECT statement that will give me all of the columns in the source table based on uniqueness of the five key columns?
Does my request make sense? Please let me know if you have questions.
I'm faced with the task of creating a script that will do a series of inserts. The insert statements will look the same except for one value, which will differ for each insert.
For simplicity's sake, here is a table with some inserts:
CREATE TABLE #MyTable ( MyPk INT IDENTITY, MyValue VARCHAR(24) )
[Code] ....
Rather than having to modify the string for each individual update statement, I'm hoping there is a simple way to create a variable (perhaps an array) which holds an indefinite number of values, then have a loop which automatically does an insert for each specified value in the array. Is something like this possible? Or is there a preferred industry standard for this sort of thing that I should use instead?
I'm having a problem finding step by step instructions in the few books I have on how to upload to your new table and database once those are set up.
I've got some data in an excel file, and I try to run the DTS wizard to create a package to upload the data from excel, but I can't seem to straiten out my data type problems.
How do I get my excel data types to match my table data types? It shouldn't be this hard to upload something so rudimentary. =(
I have a question about new record insertion. I have a SQL Database that I have cofigured as a publisher for a mobile database. I want to be able using the PDA to insert new Customers and synchronize the CE database with the SQL DB. But I encounter a problem with the column field rowguid that SQL inserts as a unique indentifier during the publication. It cannot accept null values and I can't insert a value using the keyboard since the value is generated by SQL.
So my impression is that you cannot use PDA to insert new records, only to update old ones. Is that true or I miss something??
I am trying to insert a record in a SQL2005 Express database. I can use the sp fine and it works inside of the database, but when I try to launch it via ASP.NET it fails... here is the code. I realize it is not complete, but the only required field is defined via hard code. The error I am getting states it cannot find "sp_InserOrder"
=== Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click Dim conn As SqlConnection = Nothing Dim trans As SqlTransaction = Nothing Dim cmd As SqlCommand conn = New SqlConnection(ConfigurationManager.ConnectionStrings("PartsConnectionString").ConnectionString) conn.Open() trans = conn.BeginTransaction cmd = New SqlCommand() cmd.Connection = conn cmd.Transaction = trans cmd.CommandText = "usp_InserOrder" cmd.CommandType = Data.CommandType.StoredProcedure cmd.Parameters.Add("@MaterialID", Data.SqlDbType.Int) cmd.Parameters.Add("@OpenItem", Data.SqlDbType.Bit) cmd.Parameters("@MaterialID").Value = 3 cmd.ExecuteNonQuery() trans.Commit() =====
I get an error stating cannot find stored procedure. I added the Network Service account full access to the Web Site Directory, which is currently running locally on Windows XP Pro SP2.
Please help, I am a newb and lost...as you can tell from my code...
I really need your help now, and I know I can always count on this group for tough answers to tough questions. OK, here's my dilemma. I have my trigger, which upon a record being inserted into db1.table1, inserts the same record into db2.table2 (SQL 7 db on the same server). What's happening is only a few of the fields are getting over there, but most are ending up NULL or 0. Everything besides the following records are inserting into the other database table properly:
Note: The following fields have their Default Value set to (0) SystemID EventStatusID Coop NewProductFlag TVSupportFlag RadioSupportFlag FSISupportFlag RollbackPricing PleaseContactFlag
Default Value set to (1): KitInformationID
Here is the trigger:
CREATE TRIGGER EmoesImport ON mc_events FOR INSERT AS IF @@ROWCOUNT<>0
BEGIN
SET IDENTITY_INSERT mcweb2.dbo.mc_events ON
DECLARE @SystemID int DECLARE @EventID int DECLARE @AccountID int DECLARE @BillingContactID int DECLARE @EventName varchar(100) DECLARE @EventStatusID tinyint DECLARE @Coop bit DECLARE @CoopSupplier varchar DECLARE @SamplesPerDay int DECLARE @BrochuresPerDay int DECLARE @AverageDailyMovement int DECLARE @SalesGoal int DECLARE @NumberofDays int DECLARE @NumberofHours int DECLARE @NumberofStores int DECLARE @WeekNumber tinyint DECLARE @PreferredHourID tinyint DECLARE @PreferredHourother char(20) DECLARE @PreferredDate1 varchar(20) DECLARE @PreferredDate2 varchar(20) DECLARE @NewProductFlag bit DECLARE @TVSupportFlag bit DECLARE @RadioSupportFlag bit DECLARE @FSISupportFlag bit DECLARE @RollbackPricing bit DECLARE @PleaseContactFlag bit DECLARE @EventStoreSelection tinyint DECLARE @EventClusterID int DECLARE @KitInformationID tinyint DECLARE @KitDescription varchar(1000) DECLARE @KitOther varchar(200) DECLARE @MCProgNum varchar(7) DECLARE @rowguid uniqueidentifier
SELECT @SystemID = SystemID FROM INSERTED SELECT @EventID = EventID FROM INSERTED SELECT @AccountID = AccountID FROM INSERTED SELECT @BillingContactID = BillingContactID FROM INSERTED SELECT @EventName = EventName FROM INSERTED SELECT @EventStatusID = EventStatusID FROM INSERTED SELECT @Coop = Coop FROM INSERTED SELECT @CoopSupplier = CoopSupplier FROM INSERTED SELECT @SamplesPerDay = SamplesPerDay FROM INSERTED SELECT @BrochuresPerDay = BrochuresPerDay FROM INSERTED SELECT @AverageDailyMovement = AverageDailyMovement FROM INSERTED SELECT @SalesGoal = SalesGoal FROM INSERTED SELECT @NumberofDays = NumberofDays FROM INSERTED SELECT @NumberofHours = NumberofHours FROM INSERTED SELECT @NumberofStores = NumberofStores FROM INSERTED SELECT @WeekNumber = WeekNumber FROM INSERTED SELECT @PreferredHourID = PreferredHourID FROM INSERTED SELECT @PreferredHourother = PreferredHourother FROM INSERTED SELECT @PreferredDate1 = PreferredDate1 FROM INSERTED SELECT @PreferredDate2 = PreferredDate2 FROM INSERTED SELECT @NewProductFlag = NewProductFlag FROM INSERTED SELECT @TVSupportFlag = TVSupportFlag FROM INSERTED SELECT @RadioSupportFlag = RadioSupportFlag FROM INSERTED SELECT @FSISupportFlag = FSISupportFlag FROM INSERTED SELECT @RollbackPricing = RollbackPricing FROM INSERTED SELECT @PleaseContactFlag = PleaseContactFlag FROM INSERTED SELECT @EventStoreSelection = EventStoreSelection FROM INSERTED SELECT @EventClusterID = EventClusterID FROM INSERTED SELECT @KitInformationID = KitInformationID FROM INSERTED SELECT @KitDescription = KitDescription FROM INSERTED SELECT @KitOther = KitOther FROM INSERTED SELECT @MCProgNum = MCProgNum FROM INSERTED SELECT @rowguid = rowguid FROM INSERTED
I need to insert a record in a master table and 20 records in a child table. I want to do this using stored procedure. Is it better to do it in stored procedure? Have somebody already tried this? Or is there any sample that I can use?
1 john john smith smith 1 john j smith smith 1 john jon smith smit
I want to be able to insert all the records from the result set into another table, like in the eg, but only excluding those ones that match all the above fields. How would I do this...
I need help with this. When I run the below script (only select) it retrives around 130K records and gives me the output within 2 mins. Whenever I try to put the same output in a temp or permanent table it takes hours. Any Idea why?
SET NOCOUNT ON
DECLARE @ImportId INT SET @ImportId = 5151
DECLARE @ResultXML XML SET @ResultXML = (SELECT ResultXML FROM tbRequests WITH(NOLOCK) WHERE ImportId = @ImportId)
SELECT resultNode.value('(./DealName)[1]','VARCHAR(200)') AS DealName, resultNode.value('(./CUSIP)[1]','VARCHAR(100)') AS CUSIP, CASE WHEN resultNode.value('(./Vintage)[1]','VARCHAR(100)') = '' THEN NULL ELSE resultNode.value('(./Vintage)[1]','INT') END AS Vintage, resultNode.value('(./PoolPoolType)[1]','VARCHAR(100)') AS PoolType, CASE WHEN resultNode.value('(./PaidOff)[1]','VARCHAR(100)') = '' THEN NULL ELSE resultNode.value('(./PaidOff)[1]','BIT') END AS PaidOff FROM @ResultXml.nodes('./WebService1010DataOutput') resultXml(resultXmlNode) CROSS APPLY resultXmlNode.nodes('./Results/Result') resultNodes(resultNode)
INSERT into #TResults (DealName,CUSIP,Vintage,PoolType,PaidOff) SELECT resultNode.value('(./DealName)[1]','VARCHAR(200)') AS DealName, resultNode.value('(./CUSIP)[1]','VARCHAR(100)') AS CUSIP, CASE WHEN resultNode.value('(./Vintage)[1]','VARCHAR(100)') = '' THEN NULL ELSE resultNode.value('(./Vintage)[1]','INT') END AS Vintage, resultNode.value('(./PoolPoolType)[1]','VARCHAR(100)') AS PoolType, CASE WHEN resultNode.value('(./PaidOff)[1]','VARCHAR(100)') = '' THEN NULL ELSE resultNode.value('(./PaidOff)[1]','BIT') END AS PaidOff FROM @ResultXml.nodes('./WebService1010DataOutput') resultXml(resultXmlNode) CROSS APPLY resultXmlNode.nodes('./Results/Result') resultNodes(resultNode)
I have a very big table with 20 million records DistinctProjectionKeywhich i join several times to different tables in this query.select distinct distinctprojectionkeyid,dpk.MarketID,dpk.Classificationid,dpk.DistributorID,dpk.ManufacturerID,dpk.LocationID,dpk.TimeID,P4.FACTOR as factor1,P3.FACTOR as factor2 ,P2.FACTOR asfactor3,P1.FACTOR as factor4into Projectionfactors1FROM DistinctProjectionKey dpk INNER JOIN D_Time tON t.TimeID = dpk.TimeIDINNER JOIN (select * from (select distinctClassificationID_Major,'fam' as lab fromStagingOLTP..ClassificationFlat) cf1)cfON cf.ClassificationID_Major = dpk.ClassificationIDLEFT OUTER JOIN StagingOLTP..ProjectionDefaultFlat p4ON t.TheDate = p4.TheDateAND p4.Name = 'FAM'AND cast(cf.Lab as varchar(20)) = cast(p4.Lab as varchar(20))AND dpk.MarketID = p4.MarketIDAND p4.ManufacturerID IS NULLAND p4.ClassificationID IS NULLLEFT OUTER JOIN StagingOLTP..ProjectionDefaultFlat p3ON t.TheDate = p3.TheDateAND p3.Name = 'fam'AND cast(cf.Lab as varchar(20)) = cast(p3.Lab as varchar(20))AND dpk.MarketID = p3.MarketIDAND p3.ClassificationID = dpk.ClassificationIDAND p3.ManufacturerID IS NULLLEFT OUTER JOIN StagingOLTP..ProjectionDefaultFlat p2ON t.TheDate = p2.TheDateAND p2.Name = 'fam'AND cast(cf.Lab as varchar(20)) = cast(p2.Lab as varchar(20))AND dpk.MarketID = p2.MarketIDAND p2.ManufacturerID = dpk.ManufacturerIDAND p2.ClassificationID IS NULLLEFT OUTER JOIN StagingOLTP..ProjectionDefaultFlat p1ON t.TheDate = p1.TheDateAND p1.Name = 'fam'AND cast(cf.Lab as varchar(20))= cast(p1.Lab as varchar(20))AND dpk.MarketID = p1.MarketIDAND p1.ManufacturerID = dpk.ManufacturerIDAND p1.ClassificationID = dpk.ClassificationIDthe other table have fewer number of records .I find that when I try to do the insert tempdb goes out of control ,itgrows above 100 GB?Would anyone know the reason why and the solution to apply to avoidthis problem?The other tables have fewer recodsClassification flat has 5000 records and projection default flat has32652 records.AjayAjay
I am trying to insert records via ASP, with a user that has only writeaccess to the table (db_datawriter, db_denydatareader).That way, if the server is ever compromised, the access informationstored in the source code's connection string will not allow anybody toactually read the database.The problem is that I would like to use ADO methods to insert the data(to prevent SQL injections), but I can't seem to get the rightconnection. It works in plain SQL, but I'd rather not use it.My current code looks like this:connection="Provider=SQLOLEDB.1;User ID=DBwriter;Password=XXX;DataSource=MYSERVER;Initial Catalog=MYDB;"set conn=server.createobject("ADODB.Connection")conn.mode=2 ' adModeWriteconn.open connectionSet rs = Server.CreateObject ("ADODB.Recordset")rs.Open "MYTABLE", conn, adOpenKeySet, adLockPessimistic, adCmdTablers.AddNewrs.Fields("testfield") = "TESTDATA"rs.UpdateAnd the error I get is:Microsoft OLE DB Provider for SQL Server (0x80040E09)SELECT permission denied on object 'MYTABLE', database 'MYDB', owner'dbo'.(If I use a User with read privileges in the connection stringeverything works fine.)
I have a basic data flow which tries to insert data from an excel spreadsheet to a loading table (sql server 2005). I have created this table in a non dbo schema. I have used the schema owner as the sql server login for this loading step.
The problem is SSIS seems to throw a strange error when I do this:
OnError,VH0635,VHOLSlakema,Populate Load Table,{F1C28F63-39D2-4FBB-9803-E24385014E9F},{514E8012-6998-409C-BED1-E04CE3200295},06/09/2006 11:17:37,06/09/2006 11:17:37,-1071636471,0x,An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
OnError,VH0635,VHOLSlakema,RunControllerFares,{0F7B32E6-58D9-4DD4-A0AC-311E2C194028},{514E8012-6998-409C-BED1-E04CE3200295},06/09/2006 11:17:37,06/09/2006 11:17:37,-1071636471,0x,An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
OnError,VH0635,VHOLSlakema,Populate Load Table,{F1C28F63-39D2-4FBB-9803-E24385014E9F},{514E8012-6998-409C-BED1-E04CE3200295},06/09/2006 11:17:37,06/09/2006 11:17:37,-1071636443,0x,Cannot create an OLE DB accessor. Verify that the column metadata is valid.
OnError,VH0635,VHOLSlakema,RunControllerFares,{0F7B32E6-58D9-4DD4-A0AC-311E2C194028},{514E8012-6998-409C-BED1-E04CE3200295},06/09/2006 11:17:37,06/09/2006 11:17:37,-1071636443,0x,Cannot create an OLE DB accessor. Verify that the column metadata is valid.
OnError,VH0635,VHOLSlakema,Populate Load Table,{F1C28F63-39D2-4FBB-9803-E24385014E9F},{514E8012-6998-409C-BED1-E04CE3200295},06/09/2006 11:17:37,06/09/2006 11:17:37,-1073450982,0x,component "OLE DB Destination" (5195) failed the pre-execute phase and returned error code 0xC0202025.
OnError,VH0635,VHOLSlakema,RunControllerFares,{0F7B32E6-58D9-4DD4-A0AC-311E2C194028},{514E8012-6998-409C-BED1-E04CE3200295},06/09/2006 11:17:37,06/09/2006 11:17:37,-1073450982,0x,component "OLE DB Destination" (5195) failed the pre-execute phase and returned error code 0xC0202025.
When I create this table in the dbo it seems to work ok. I have tried giving the schema owner sa rights on the sql server and it still doesnt work. Im wondering if this is a known bug in ssis.
