Hello all, Iv been making a lot of progress on my first functional webapp, but I cannot get this bit of code to work correctly. I think my UPDATE SQL statement is where the problem is. It works fine the first time through when there is no Session("estimateid") set, but after that is set it gives me error this on line 40: Incorrect syntax near '('. 1 Dim CustID As Integer 2 3 Dim DbConnection As SqlConnection 4 DbConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("harringtonairdb").ConnectionString) 5 DbConnection.Open() 6 Dim DbCommand As SqlCommand 7 8 If Session("estimateid") = 0 Then 9 Dim DbSqlInsert As String 10 DbSqlInsert = "INSERT INTO tblcustomers (strname, strAddress1, strAddress2, strCity, strState, strZip, strPhone, strEmail, strContact) VALUES (@name, @address1, @address2, @city, @state, @zip, @phone, @email, @contact)" & "SELECT @@IDENTITY AS CustID" 11 DbCommand = New SqlCommand(DbSqlInsert, DbConnection) 12 Else 13 Dim DbSqlUpdate As String 14 DbSqlUpdate = "UPDATE tblcustomers SET (strcustname=@name, straddress1=@address1, straddress2=@address2, strcity=@city, strstate=@state, strzip=@zip, strphone=@phone, stremail=@email, strcontact=@contact) JOIN tblestimates ON pkcustomerid WHERE pkestimateid=@estimateid" 15 DbCommand = New SqlCommand(DbSqlUpdate, DbConnection) 16 DbCommand.Parameters.AddWithValue("@estimateid", Session("estimateid")) 17 18 End If 19 20 DbCommand.Parameters.AddWithValue("@name", txtCustName.Text) 21 DbCommand.Parameters.AddWithValue("@address1", txtCustAddress1.Text) 22 DbCommand.Parameters.AddWithValue("@address2", txtCustAddress2.Text) 23 DbCommand.Parameters.AddWithValue("@city", txtCustCity.Text) 24 DbCommand.Parameters.AddWithValue("@state", txtCustState.Text) 25 DbCommand.Parameters.AddWithValue("@zip", txtCustZip.Text) 26 DbCommand.Parameters.AddWithValue("@phone", txtCustPhone.Text) 27 DbCommand.Parameters.AddWithValue("@email", txtCustEmail.Text) 28 DbCommand.Parameters.AddWithValue("@contact", txtCustTimes.Text) 29 30 31 If Session("estimateid") = 0 Then 32 CustID = Convert.ToInt32(DbCommand.ExecuteScalar()) 33 DbCommand.Dispose() 34 Dim DbSqlInsert As String 35 DbSqlInsert = "INSERT INTO tblestimates (fkcustomerid) VALUES (@customerid)" & "SELECT @@IDENTITY AS EstimateID" 36 DbCommand = New SqlCommand(DbSqlInsert, DbConnection) 37 DbCommand.Parameters.AddWithValue("@customerid", CustID) 38 Session.Add("estimateid", Convert.ToInt32(DbCommand.ExecuteScalar())) 39 Else 40 DbCommand.ExecuteNonQuery() 41 End If 42 43 DbConnection.Close() 44 DbCommand.Dispose()
I want to update the startdate column (for all rows) so that when period is 0 then the new value is a hardcoded value (say '01-Dec-2000') but for all other rows it takes the value in the enddate column for the row of the previous column (with the same freq)
ie the startdate column for period 1 takes the enddate value for period 0 and so on for a particular freq
create table #periods (period int , startdate datetime , [enddate] datetime , freq int) insert #periods ( period , startdate , enddate , freq) select 0 , '01-Jan-1900' , '31-Jan-2001' , 1 union all select 1 , '01-Jan-1900' , '28-Feb-2001' , 1 union all select 2 , '01-Jan-1900' , '31-Mar-2001' , 1 union all select 3 , '01-Jan-1900' , '30-Apr-2001' , 1 union all select 4 , '01-Jan-1900' , '31-May-2001' , 1 union all select 0 , '01-Jan-1900' , '31-Jan-2002' , 3 union all select 1 , '01-Jan-1900' , '28-Feb-2002' , 3 union all select 2 , '01-Jan-1900' , '31-Mar-2002' , 3 union all select 3 , '01-Jan-1900' , '30-Apr-2002' , 3 union all select 4 , '01-Jan-1900' , '31-May-2002' , 3
/* I know I need a case statement to test for column 0 and to join the table on itself and have put something together but it fails for column 0 and updates to NULL - I think it must be to do with the join ??
This is what I've got so far :
UPDATE PA1 SET PA1.Startdate = CASE WHEN PA2.period = 0 THEN 2000-12-01 00:00:00.000 ELSE PA1.Enddate END FROM #periods AS PA1 JOIN #periods AS PA2 ON PA1.Freq = PA2.Freq AND PA1.Period = PA2.Period + 1
Hi i have a view that contain multiple tables from my database and i want to view it on datagridview and update it's data some people says you can update joined tables using instead of triggers how is that ?is there any example ?
I have a script that is supposed to run thru 2 joined tables and update a field in the 3rd table. The script works but takes approx. 4 hours to run against 250k records.
UPDATE a SET Con_Mailings = STUFF((SELECT '; ' + c.ListName FROM [server].[xxxxx_MSCRM].[dbo].ListBase c with (nowait) INNER JOIN [server].[xxxxxx_MSCRM].[dbo].[ListMemberBase] b with (nowait) ON b.ListID = c.ListID WHERE b.EntityID = a.TmpContactID FOR XML PATH('')),1,1,'') FROM [xx_Temp].[dbo].[Lyris_CombinedTest] a
I should end up with something like this in the con_mailings field:
I want to return all rows in table giftregistryitems with an additional column that holds the sum of column `amount` in table giftregistrypurchases for the respective item in table giftregistryitems.
What I tried, but what returns NULL for purchasedamount:
SELECT (SELECT SUM(amount) from giftregistrypurchases gps where registryid=gi.registryid AND gp.itemid=gps.itemid) as purchasedamount,* FROM giftregistryitems gi LEFT JOIN giftregistrypurchases gp on gp.registryid=gi.id WHERE gi.registryid=2
How can I achieve what I need?
Here's my table definition and data:
/****** Object: Table [dbo].[giftregistryitems] Script Date: 02-05-15 22:37:11 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[giftregistryitems]( [id] [int] IDENTITY(1,1) NOT NULL,
We currently have an application whereby the user will come along sign up add some information about them, choose what interests / hobbies they're in to and then click save.After completing the sigh up they're able to search for other individuals within locations (look at this functionality as a dating site) We currently have a stored procedure whereby when the user clicks Advance Search they fill in all the fields they filled in when signing up and when they click search we return all users that match the data the user has selected. This part is working correctly.
The issue im having is extending this stored procedure to take a user defined table type which has two columns UserId and ActivityId both columns are nullable.This user defined table type is referred to in two places, when the user signs up and when they do an advance search.When the user does an advance search he / she can choose a list of hobbies / interests this will then be passed in to the database and I will join on my User_Activities table to find the users that have the Activities passed in.When I join on this table I get duplicate records purely because one user may have 5 - 10 activities depending on how active the individual is. My current stored procedure looks like this
@Ambition int = null, @Body int = null, @Diet int = null, @Drinking int = null, @Ethnicity int = null, @Exercise int = null, @HeightFrom int = null,
[code]...
So my question is how can I select all users that match the criteria passed in, as well as finding the users that match the activities passed in (the user can either match all of them or a minimum of 1). Also my User_Activities has two columns UserId and ActivityId identical to the user defined table type
I want to return all rows in table giftregistryitems with an additional column that holds the sum of column `amount` in table giftregistrypurchases for the respective item in table giftregistryitems.
What I tried, but what returns NULL for purchasedamount:
SELECT (SELECT SUM(amount) from giftregistrypurchases gps where registryid=gi.registryid AND gp.itemid=gps.itemid) as purchasedamount,* FROM giftregistryitems gi LEFT JOIN giftregistrypurchases gp on gp.registryid=gi.id WHERE gi.registryid=2
How can I achieve what I need?
Here are my table definitions and data:
/****** Object: Table [dbo].[giftregistryitems] Script Date: 02-05-15 22:37:11 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[giftregistryitems]( [id] [int] IDENTITY(1,1) NOT NULL,
Here's my problem:I'm developing an ASP.NET 2.0 application that has a user select one or moreauto manufacturers from a listbox ("lstMakes"). Once they do this, anotherlistbox ("lstModels") should be filled with all matching models made by theselected manufacturers. If lstMakes was not multi-select, I'd have noproblem. But in this case it has to be multi-select. The database is SQLServer 2005 which does not accept arrays as parameters. I've been told thatI have to create an XML document that will act as a filtered Manufacturerstable that I can join to my Models table in my stored procedure. Problem isI don't have the foggiest idea how to do this. I've seen some examples thatjust leave me scratching my head so I was hoping someone could look at whatI'm trying to do and show me how to do this. Thanks!
with cte as ( select DataID, Name, SubType, FileType, MimeType, VersionID, Version
from dtree A1, dversdata A2 where A1.dataid=A2.Docid And A1.Subtype='144' AND A2.mimetype='application/news-message-id' update cte set MimeType = 'application/x-outlook-msg', Subtype=749
what I want to do is to update two columns. Both are from different tables and I get an error..
Each row of my datagrid comes from two tables, A and B, which are (left) joined: not every row from table A has a corresponding row in table B. I think this is quite a common scenario.If I want to edit a row in my datagrid which contains data from both Table A and Table B then presumably I can just use an UPDATE statement behind the scenes.But what happens if I want to edit a particular row in the datagrid which contains data from Table A but no corresponding data from table B? I can't use an UPDATE statement because the record in Table B doesn't yet exist. So what do I do?Does anyone know the answer to this, or could you point me to a good tutorial please?
I have a query written that filters on joined table data. The SELECT looks like this:
SELECT * FROM tbl_bol AS a LEFT OUTER JOIN bol_status AS b ON b.bol_status_id = a.bol_status_id LEFT OUTER JOIN tbl_carrier AS c ON c.carrier_id = a.carrier_id WHERE (a.carrier_name LIKE 'five%') AND (a.accrueamt = 0) AND (a.imported = 1) AND (b.description = 'tendered') AND (a.ship_date BETWEEN '9/1/13' AND '9/30/13') ORDER BY a.bol_number DESC
If I want to do an UPDATE query that uses those filters in the WHERE clause, how do I go about doing that? It doesn't look like you can used joined tables in the UPDATE line like this:
UPDATE tbl_bol AS a LEFT OUTER JOIN bol_status AS b ON b.bol_status_id = a.bol_status_id LEFT OUTER JOIN tbl_carrier AS c ON c.carrier_id = a.carrier_id SET accrueamt='1348' WHERE (a.carrier_name LIKE 'five%') AND (a.accrueamt = 0) AND (a.imported = 1) AND (b.description = 'tendered') AND (a.ship_date BETWEEN '9/1/13' AND '9/30/13')
This runs in Access, but SQL Server 7 complains that BETWEEN is unrecognized. Can anyone help me? thanks
SELECT yearId, IIf(Date() BETWEEN [qrtOneStart] AND [qrtOneEnd],1, IIf(Date() BETWEEN [qrtTwoStart] AND [qrtTwoEnd], 2, IIf(Date() BETWEEN [qrtThreeStart] AND [qrtThreeEnd], 3, 4))) AS CurrentQrt, yearName FROM tblYear
I am working on a model where I have a sales fact table. Each fact record has four different customer fields (ship- to, sold-to, payer, and bill-to customer). I have one customer dimension table that joins to the sales fact table four times (once for each of the customer fields above). When viewing the data in Excel, I would like to have four hierarchies (ship -to, sold-to, payer, and bill-to customer) within Customer.
Is there a way to build hierarchies within my Customer dimension based on the same Customer table? What I want is to view the data in Excel and see the Customer dimension. Within Customer, I want four hierarchies.
I know how to access different tables on the same server by prefixingthe table with the database name. Is there anyway to prefix theserver name to link two tables across servers? Thanks.
) ) ) ) ) AS timeType, Sum([2007_hours].Hours) AS SumOfHours from................
how can you convert it to sql syntax
I need to have a nested If statment which I can't do in sql (in sql I have to have select and from Together for example ( I can't do this in sql): select ID, FName, LName if(SUBSTRING(FirstName, 1, 4)= 'Mike') Begin Replace(FirstNam,'Mike','MikeTest') if(SUBSTRING(LastName, 1, 4)= 'Kong') Begin Replace(LastNam,'Kong,'KongTest') if(SUBSTRING(Address, 1, 4)= '1245') Begin ......... End End
end
Case Statement might be the solution but i could not do it.
I have a table with almost a million rows, although it's quite slim with just ID, date, userID, JobID etc.
Now I want to the ability to add comments to some (probably less than 1%) of those lines.
The question is whether to create a separate comments table to join to it, or to create a comments field within the existing table? The comments field would obviously default to NULL, so wouldn't bloat the table unnecessarily if I add that field (right?), and would always be selected with the row from that table, so I'm leaning towards the latter alternative.
Ok here goes. I have 3 tables, one holds case info, the 2nd holds possible outcome on the charges, and they're joined on a 3rd table (CaseOutComes). With me so far? Easy stuff, now for the hard part. Since there's a very common possiblitly that the Case has multiple charges, we need to track those, and therefore, display them on a datagrid or some other control. I want the user to be able to edit the info and have X number of dropdowns pertaining to how many ever charges are on the case. I can get the query to return the rows no sweat, but ...merging them into 1 record (1 row) with mutiple drops is seeming impossible -- I thought about using a placeholder and added the controls that way, but it was not in agreement with what I was trying to tell it . Any ideas on how to attack this?
I want to return all rows in table giftregistryitems with an additional column that holds the sum of column `amount` in table giftregistrypurchases for the respective item in table giftregistryitems.
What I tried, but what returns NULL for purchasedamount, where I want purchasedamount to be the sum of the `amount` for THAT item, based on giftregistrypurchases.itemid=giftregistryitems.id:
SELECT (SELECT SUM(amount) from giftregistrypurchases gps where registryid=gi.registryid AND gp.itemid=gps.itemid) as purchasedamount,* FROM giftregistryitems gi LEFT JOIN giftregistrypurchases gp on gp.registryid=gi.id WHERE gi.registryid=2
How can I achieve what I need?
Here's my table definition and data:
USE [tt] GO /****** Object: Table [dbo].[giftregistry] Script Date: 09-05-15 11:15:18 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[giftregistry]( [id] [int] IDENTITY(1,1) NOT NULL,
Hello everyone, I have a query problem.I'll put it like this. There is a 'publishers' table, and there is a'titles' table. Publishers publish titles (of course). Now I want to make aquery (in MS SQL Server) that would return the last title published by everyof the publishers. Quite clear situation. But I can't make it work.If I use inner join (which I should, because I need data from both tables)then I get a result showing all publishers and all titles. What I want toget is all publishers, and only their last title, so I don't have more thanone line for the same publisher, and this line should contain publisherdetails and last title details.I tried using DISTINCT, but it works on a whole resultant row rather then acolumn, and since rows are all distnict (because they also contain columnsfrom titles) this didn't help me.What I can do is (in my application) first get a list of publishers, andthen loop through them selecting only the last title belonging to eachpublisher. I want to see if there is a way to accomplish the same thing withan SQL query (or maybe a stored procedure, view, or whatever). Anything ispossible, as long as it stays within SQL server and doesn't rely on theclient application.Of course, both 'publishers' and 'titles' tables have a primary key('publisherID', and 'titleID'), and 'titles' has a 'publisherID' columnwhich relates titles with publishers.Help :)
Can someone please help me with a better way to format the following query. This works, but I know it is hidious.
Code Snippet
select
convert(varchar, processed, 101) as Date, count(o.id) as [# Orders], sum(distinct a.runnercount) as [# Runners], sum(o.total) as [$ Gross], sum(o.fee) as [$ Fees], (sum(o.total)-sum(o.fee)) as [$ Net]
from [order] o join (select convert(varchar,processed,101) as date, count(*) as runnercount from orderitem oi inner join [order] o on o.id = oi.orderid where typeofextraid = 4 group by convert(varchar,processed,101)) a on convert(varchar,processed,101) = a.date
where statemented = @statemented group by convert(varchar, processed, 101) 2 tables: Order and OrderItem. I need the sum of a specific record type from the OrderItem table along with all the other aggregate columns group by day.
Hi guys! I am using SQL 2005 and I wonder why I am encountering the error "[Microsoft][ODBC SQL Server Driver]Syntax error or access violation" everytime I am trying to create stored procedure with temp table and table variable. See my code below with temp table.
Any thoughts will be appreciated!
CREATE PROCEDURE DBO.SAMPLESP (@DETAILS AS VARCHAR(8000), @ID AS VARCHAR(15))
AS
BEGIN TRANSACTION
CREATE TABLE DBO.#TEMPTABLE { ASSET VARCHAR(50) }
DECLARE @INSTINSERT AS NVARCHAR(4000) SET @INSTINSERT= 'INSERT INTO #TEMPTABLE(ASSET)' SET @INSTINSERT= @INSTINSERT+ @DETAILS
EXEC sp_ExecuteSQL @INSTINSERT
INSERT INTO InstDetail (TrackNum, ASSETID) SELECT @ID, A.ASSE FROM #TEMPTABLE A
DROP TABLE #TEMPTABLE
IF @@ERROR != 0 BEGIN ROLLBACK TRANSACTION RAISERROR('There was an error in here', 11, 1) RETURN END ELSE COMMIT TRANSACTION
I am unable to update a table (either by opening it and entering datadirectly or through a form). I have set the recordsettype property toupdateable snapshot and have set the permissions to update inside ofthe SQL Server 2000 to checked. Unfortunately, these items have notworked. Any advice is appreciated. Thanks.Dave Christman
Hi, This is probably straightforward but I'm not sure how to approach this problem.
I have 2 tables, one parent and a child. The child table has a Quantity and a Price field and also a FK to the parent table.
I want the parent table to display its own row data along with the Total Cost of the child rows in the child table. So it needs to multiply the price by the quantity but also do a sum of this calculated column.
To start off with I tried SUM( TOTALCOST ) and it didn't like it. I've got an idea I might need to create a View but was wondering what is the standard way of doing something like this? E.g.
