Next I am inserting into budcustomers tableÂ
 Â
--Customer
INSERT INTO [dbo].[BudCustomers]
([LegalName]
,[EffectiveDate]
,[LawsonCustomerNumber]
,[ChangeReason]
,[ImportedRecord]
[Code] .....
Next I am inserting into budcontracts table but here in this table to insert i want the inserted terminalid and customerid column which are unqiue identity based.
So how to do that here in this query
--Contracts
INSERT INTO [dbo].[BUDCONTRACTS]
([CONTRACTNAME]
,[CONTRACTNUMBER]
,[VERSIONSTARTDATE]
,[VERSIONTERMINATIONDATE]
I have two SQL queries that I would like to combine. Each query is dependent on the same table, and the same rows, but they each have their own WHERE statements. I've thought about using some JOIN statements (left outer join in particular) but then I run into the problem of not having two separate tables, and don't see where I can put in two separate WHERE statements into the final query. I've read into aliasing tables, but I'm not quite sure how that works (how to put it into code or a JOIN statement) , or if it would solve my question. Do you have any ideas or examples of how to solve this scenario?
I have made a couple of queries I want to use into a Visual studio project, Now is my problem:
All my queries have different statements. I believe the best is to show the results in a Datagridview
How to view them there? I know how to do it... but I have got about 30 queries
Here is my code so far:
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load ComboBox1.DataSource = MyDB_DevDataSet.DataSetName. Dim connectionString As String = "Data Source=myDBmySRV;Initial Catalog=Teknotrans_dev;Integrated Security=True" Dim sql As String = "SELECT * FROM CompanyMain"
Parameter Information cannot be derived from SQL statements with sub-select queries. Set Parameter information before preparing command.
Here's the query:
update GCDE_SEQ set LAST_NO = (select max(FLD_NO) from PONL_FLD) ,UPDT_USER = ? ,UPDT_DT = getdate() where SEQ_NM = 'FLD_NO'
Why can't Execute SQL Task handle this simple query? I figure i can use 2 SQL Execute SQL Task, one to get the max into a var, and the other to do the updating. However, this is alot of trouble since i'm having this almost exact query in alot of places. Any way around this?
I have a SP having three differnet select statements like,
IF @reporttype ='A'
SELECT Column1, column2 FROM table1
IF @reporttype = 'B'
SELECT Column3, column4 FROM table2
IF @reporttype = 'C'
SELECT column5, Column6 FROM table3
Now I need three different reports for all three reporttypes. If i creating first report for @reporttype A, it is executing fine and giving me the Columns which i need. But while creating reprots for reporttype B and C, I m getting columns column1 and column2 . Its not displaying Column3, Column4, Column5, Column6.
I am almost sure I can update variables columns in one select/case type statement, but having problems working out the syntax.
I have a table with transactions - with tran types as the key.
in this example, types = A,B,C ,D.
in this first example I am updating the sum of QTY to value t_A based on tran types =A.
can I perform sub query/case to update with the same where clause but for types B,C and D?? I also have to insert for specific lot numbers each sum values.
Create table #t_reconcile( t_lot_number int not null, t_A float, t_B float, t_C float, t_D float)
insert #t_reconcile
select t.lot_number, sum(t.qty) from i , t where i._id = t.event_id i.transaction_type = 'A' group by t.lot_number order by t.lot_number
I have a database that tracks billing and payment history records against a "relationship" record (the "relationship" maps a many-to-many relationship between employees and cell phone numbers).
I have two statements that look like this:
SELECT CellPhone.PhoneNumber, SUM(BillingHistory.AmountOwed) AS TotalOwed FROM Relationship INNER JOIN CellPhone ON CellPhone.PKCellPhone = Relationship.FKCellPhone INNER JOIN BillingHistory ON Relationship.PKRelationship = BillingHistory.FKRelationship GROUP BY Relationship.PKRelationship, CellPhone.PhoneNumber
SELECT CellPhone.PhoneNumber, SUM(PaymentHistory.AmountPaid) AS TotalPaid FROM Relationship INNER JOIN CellPhone ON CellPhone.PKCellPhone = Relationship.FKCellPhone INNER JOIN PaymentHistoryON Relationship.PKRelationship = PaymentHistory.FKRelationship GROUP BY Relationship.PKRelationship, CellPhone.PhoneNumber
Each statement correctly aggregates the sums, but I need a record that shows me:
CellPhone.PhoneNumber, SUM(BillingHistory.AmountOwed) AS TotalOwed, SUM(PaymentHistory.AmountPaid) AS TotalPaid
I can't figure out how to join or merge the statements together to get all of this information into one record without ruining the sums (I can't seem to correctly join the PaymentHistory table to the BillingHistory table without the sums going haywire).
I am using Visual Web Developer Express 2005 as a test environment. I have it connected to a SQL 2000 server. I would like to use a Select Case Statement with the name of a column from a SQL Query as the Case Trigger. Assuming the SQLDataSource is named tCOTSSoftware and the column I want to use is Type, it would look like the following in classic ASP: Select Case tCOTSSoftware("Type") Case 1 execute an SQL Update Command Case 2 execute a different SQL Update Command End Select What would a comparable ASP.Net (Visual Basic) statement look like? How would I access the column name used in the SQLDataSource?
I need to pass 3 column values and one Formula string into 4 replace statements and output the result in one column.
Nesting them in the usual way doesn't seem to work as that only allows for one column.
My table consits of four columns...PF (numeric), Hours (numeric), TotalNumber INT, and Formula (nvatchar)
My function needs to search and replace the Formula column for instances of all the three number columns and output the formula as a mathmatical formula rather than a string.
Here is what I have so far which works fine if all three columns have a value, but if only one is null then it will retrun NULL and not the other two values.
FUNCTION GetFormula (@numPF NUMERIC(10,2), @numHours NUMERIC(10,2), @intTotalNumber INT, @strFormula nvarChar(200)) RETURNS nvarchar(200) AS BEGIN DECLARE @strExpression nvarchar(200)
Hi all!In a insert-trigger I have two joins on the table named inserted.Obviously this construction gives a name collition beetween the twojoins (since both joins starts from the same table)Ofcourse I thougt the usingbla JOIN bla ON bla bla bla AS a_different_name would work, but itdoes not. Is there a nice solution to this problem?Any help appriciated
SELECT EventID, Role, EventDuty, Qty, StartTime, EndTime, Hours FROM dbo.tblEventStaffRequired;
and SELECT EventID, Role, StartTime, EndTime, Hours, COUNT(ID) AS Booked FROM tblStaffBookings GROUP BY EventID, Role, StartTime, EndTime, Hours;
How can I join the results of the two by matching the columns EventID, Role, StartTime and EndTime in the two and have the following columns in output EventID, Role, EventDuty, Qty, StartTime, EndTime, Hours and Booked?
is there any way of getting the identity without using the "@@idemtity" in sql??? I'm trying to use the inserted event of ObjectDataSource, with Outputparameters, can anybody help me???
Case: Exporting Report to PDF/Printing/TIFF Report: Contains 1 table with 19 Columns. 1 column is static, the other 18 are visible at the users descretion. Report when printed/exported to pdf spans 2 pages naturally, 16 on the first page, 3 on the second, and the column widths have been adjusted to provide a perfect page span .
User A elects to hide two of the columns, and show the rest. The report complies and the viewable version is perfect, the excel export is perfect.. the PDF export on the first page causes every fith column, starting with the last column that was hidden to be expanded to take up additional width. On the spanned page, it renders the first column on that page correctly, then there is a white space gap equal to the width of the hidden columns and then the rest of the cells show with the last column expanded to take up the same width that the original 2 columns were going to take up, plus its width.
We have tried several different settings to see if it helps this issue or makes it worse. So far cangrow/canshrink/keep together have made no impact. It is not possible to increase the page size due to limited page size selection availablility for the client. There are far too many combinations of what the user can elect to show or hide to put together different tables to show and hide on the same report to remove this effect.
Any help or suggestion on this issue would be appreciated
I'm doing a data migration job from our old to our new database. The idea is that we don't want to clutter our new tables with the id's of the old tables, so we are not going to add a column "old_system_id" in each and every table that needs to be migrated.
I have created a number of tables in a separate schema "dm" (for Data Migration) that will store the link between the old database table and the new database table. Suppose that the id of a migrated record in the old database is 'XRP002-89' and during the insert into the new table the IDENTITY column id gets the value 1, the link table will hold : old_d = 'XRP002-89', new_id = 1, and so on.
I knew I can get the value of IDENTITY columns with the OUTPUT INTO clause, although I have never actually used it. And now I can't get it to do what I need.
Below is some code to set up three tables: the old table, the new one, and the table that will hold the link between the id's of records in the old database table and the new database table.
Below I tried to use the OUTPUT INSERTED INTO clause. Beside getting the generated IDENTITY value, I also need to capture the value of the old id that will not be migrated to the new table. When I use "OUTPUT DaOldTable.pk" the system gives me the error: "The multi-part identifier "DaOldTable.pk" could not be bound." Using INSERTED .id gives no problem.
Code: INSERT INTO DaNewTable(a_column) --OUTPUT DaOldTable.pk, INSERTED.id link_old2new_DaTable--(DaOld_id, DaNew_id) SELECT a_column FROM DaOldTable
[Code] ...
--but getting "The multi-part identifier "DaOldTable.pk" could not be bound."
DROP TABLE DaOldTable DROP TABLE DaNewTable DROP TABLE link_old2new_DaTable
How can I populate a table that must hold the link between the id's of records in the old database table and the new database table? The records are migrated with set-based inserts.
HelloWhen I use a PreparedStatement (in jdbc) with the following query:SELECT store_groups_idFROM store_groupsWHERE store_groups_id IS NOT NULLAND type = ?ORDER BY group_nameIt takes a significantly longer time to run (the time it takes forexecuteQuery() to return ) than if I useSELECT store_groups_idFROM store_groupsWHERE store_groups_id IS NOT NULLAND type = 'M'ORDER BY group_nameAfter tracing the problem down, it appears that this is not preciselya java issue, but rather has to do with the underlying cost of runningparameterized queries.When I open up MS Enterprise Manager and type the same query in - italso takes far longer for the parameterized query to run when I usethe version of the query with bind (?) parameters.This only happens when the table in question is large - I am seeingthis behaviour for a table with > 1,000,000 records. It doesn't makesense to me why a parameterized query would run SLOWER than acompletely ad-hoc query when it is supposed to be more efficient.Furthermore, if one were to say that the reason for this behaviour isthat the query is first getting compliled and then the parameters aregetting sent over - thus resulting in a longer percieved executiontime - I would respond that if this were the case then A) it shouldn'tbe any different if it were run against a large or small table B) thisperformance hit should only be experienced the first time that thequery is run C) the performance hit should only be 2x the time for thenon-parameterized query takes to run - the difference in response timeis more like 4-10 times the time it takes for the non parameterizedversion to run!!!Is this a sql-server specific problem or something that would pertainto other databases as well? I there something about the coorect use ofbind parameters that I overall don't understand?If I can provide some hints in Java then this would be great..otherwise, do I need to turn/off certain settings on the databaseitself?If nothing else works, I will have to either find or write a wrapperaround the Statement object that acts like a prepared statement but inreality sends regular Statement objects to the JDBC driver. I wouldthen put some inteligence in the database layer for deciding whetherto use this special -hack- object or a regular prepared statementdepending on the expected overhead. (Obviously this logic would onlybe written in once place.. etc.. IoC.. ) HOWEVER, I would desperatelywant to avoid doing this.Please help :)
I will paste my code below. Â I inserted a breakpoint but nothing is being sent to the database and nothing came up when I ran it with the breakpoint. Â Can anyone tell me how to fix this?Protected Sub btn_addfriend_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btn_addfriend.Click Dim add_friend_source As New SqlDataSource add_friend_source.InsertCommand = "INSERT INTO [Friends] ([UserName], [UserID], [IP], [AddedOn], [FriendName]) VALUES (@UserName, @UserID, @IP, @AddedOn, @FriendName)" detailsview_addfriend.DataSource = add_friend_source add_friend_source.DataBind() add_friend_complete.Text = "Success!" End SubThe "Success!" text is the only thing that seems to work properly...
Hello, I am stumped on how to get the last id that was inserted. I have searched all over the internet to find the answer but all of them turned up to be classic asp.
while executing this command locally, ita working fine Insert into tblorderDetails (OrderID,ProductID,SofaPackageID,Quantity,UnitPrice,TotalOrd,ItemStatus ) values(1, 3915, 0, 1, 2049.00, 2049.00, 'PO') but when executing online, giving me following msg: (1 row(s) affected) Msg 207, Level 16, State 1, Line 1 Invalid column name 'Location16FreeStock'. and also data is not inserted in table.
I have a program to insert rows into a SQL Server table from an ASCII file. There appears to be some data mapping going on. For example:
"J & J Smith" is imported as "J _J Smith".
This doesn't happen on all servers. I can run it against my database and get the desired results.
I looked into the Server Configuation. Under the Security tab there are Mapping options. It appears that you can map _ to something else, but I don't see where you can map & to anything.
this is my spc: select top 1 u.userid, u.user_name, u.password, c.code_description as role_code, u.expiry_date, u.created_date, u.active, convert(varchar,v. user_date, 103) + ' ' + right('0' + stuff(right(convert(varchar,v.user_date, 109), 14), 8, 4, ''), 10) as user_date, v.operation,h.user_name as updatedby from [usermaster] u inner join [codeMaster] c on 'sp'=c.code inner join [HRUser_developerlog] v on u.userid=v.inserted_id and v.operation='update' inner join [usermaster] h on v.userid=h.userid where u.userid = '3' order by v. user_date
if i use it gives the v.user_date as fist modified date it is not giving last modified date.
I have a dataset that uses generated stored procedures to do its select, insert, update, delete. I am inserting a row to that dataset, and after the update, using the ID of newly created row. This worked just fine until I added triggers to some of the tables on my DB, and now, when I insert a row, the row's ID is not available after the update (it's 0) Any idea what happened / what I have to do to fix this? Thnx!
Hi,I use a Stored Procedure who works very well....INSERT INTO Computers (CategoryID, SubCategoryID, ......VALUES (@CategoryID, @SubCategoryID, .........But as soon as it creates the new row, i want to be able to get the Id (ComputerId) of this row. I use ComputerId as the primary key.How can i do that? I code in VB.Thanks
I'm creating a web application that has user input on 3 seperate pages. Each page prompts the user for specific information and at the bottom of the page the user will click on the "Submit" button to post the info from the form to the database table. Once the user has submitted the first page of information how do I retrieve the ID from the CustID column of that row so I can use the Update function to add additional info to that row when the user clicks on the submit button on page 2 & 3. I don't want to hold all the information in variables until the end in case they bail out of the form. TIA Steve
I've seen a lot of info on how to do this with @@IDENTITY and SCOPE_IDENTITY(), but can't get this to work in my situation. I am inserting a record into a table. The first field is a GUID (UNIQUEIDENTIFIER) that uses newid() to generate a unique GUID. Since I am not using an int, I can't set the IsIdentity property of the field. Without IsIdentity set, @@IDENTITY and SCOPE_IDENTITY() do not work. How can I get the ID (or whole record) of the last record that I inserted into a SQL database? Note that I am doing this in C#. As a last resort, I could generatate the GUID myself before the insert, but I can't find C# code on how to to this.
Hi! I would like to get the last inserted ID in a sql 2005 table to a variable.My code is: SqlDataSource ds = new SqlDataSource();ds.ConnectionString = ConfigurationManager.ConnectionStrings["ConnString"].ToString();ds.InsertCommandType = SqlDataSourceCommandType.Text;ds.InsertCommand = "INSERT INTO Products(SellerID, Price) OUTPUT INSERTED.ProductID VALUES(@SellerID,@Price)"; ds.InsertParameters.Add("SellerID", "Sony"); ds.InsertParameters.Add("Price", "123");string output = ds.Insert().ToString(); // it's return rowsAffected but I need productID (SCOPE_IDENTITY or @@IDENTITY )Thank you for your time and help :)
INSERT INTO tblArticleCategory(uidArticleId, uidCategoryId) VALUES(@uidArticleId, @uidCategoryId)
But i get an error when I EXEC the SP like this:
EXEC spInsertNews @strHeader = 'Detta är den andra nyheten', @strAbstract = 'dn första insatt med sp:n', @strText = 'här kommer hela nyhetstexten att stå. Här får det plats 2000 tecken, dvs fler än vad jag orkar skriva nu...', @dtDate = '2003-01-01', @dtDateStart = '2003-01-01', @dtDateStop = '2004-01-01', @strAuthor = 'David N', @strAuthorEmail = 'david@davi.com', @strKeywords = 'nyhet, blajblaj, blaj'
the errormessage is: Syntax error converting from a character string to uniqueidentifier.
does anyone have a sulution to this problem? Can I use something similar to the @@IDENTITY? I will be greatful for any ideas...
Hello, I want to return the ID of the last inserted row. I am using an ObjectDataSource and a FormView to perform the insert operation. Here is my presentation code: <asp:ObjectDataSource ID="odsOrders" runat="server" DataObjectTypeName="Auction.Info"InsertMethod="InsertOrder" SelectMethod="GetOrders" OnInserted="ReturnNewOrderID" OldValuesParameterFormatString="original_{0}" TypeName="Auction.Controller"> <SelectParameters> <asp:Parameter DefaultValue="00" Name="ModuleId" Type="Int32" /> </SelectParameters></asp:ObjectDataSource> <asp:FormView ID="fvAddOrder" runat="server" DataSourceID="odsOrders" DefaultMode="Insert" HorizontalAlign="Center" Width="100%"> <InsertItemTemplate>.... and my VB code: Protected Sub ReturnNewOrderId(ByVal sender As Object, ByVal e As ObjectDataSourceStatusEventArgs) Response.Write(e.ReturnValue) Response.Write("Test") End Sub and my Stored Procedure: ALTER PROCEDURE [dbo].[Auction_InsertOrder] (@ModuleID int,@FirstName nvarchar(50),@LastName nvarchar(50),@Email nvarchar(250),@Phone nvarchar(20),@Fax nvarchar(20),@DelAddress1 nvarchar(250),@DelAddress2 nvarchar(250),@DelTown nvarchar(50),@DelPostCode nvarchar(20),@DelCountry nvarchar(50),@InvAddress1 nvarchar(250),@InvAddress2 nvarchar(250),@InvTown nvarchar(50),@InvPostCode nvarchar(20),@InvCountry nvarchar(50),@AuctionName nvarchar(50),@Quantity int,@Price decimal(18,2),@PostCosts decimal(18,2))ASINSERT INTO Auction_Orders(ModuleID, FirstName, LastName, Email, Phone, Fax, DelAddress1, DelAddress2, DelTown, DelPostCode, DelCountry, InvAddress1, InvAddress2, InvTown, InvPostCode, InvCountry, AuctionName, Quantity, Price, PostCosts, DateEntered)VALUES (@ModuleID, @FirstName, @LastName, @Email, @Phone, @Fax, @DelAddress1, @DelAddress2, @DelTown, @DelPostCode, @DelCountry, @InvAddress1, @InvAddress2, @InvTown, @InvPostCode, @InvCountry, @AuctionName, @Quantity, @Price, @PostCosts, getdate())RETURN SELECT @@IDENTITY As NewOrderID I can insert the record into the database but the e.ReturnValue does not return anything. Thank you for your help/ Many thanks, Vincent
