hi myself avi
i am developing one appliacaion in which i am using vb 6 as front end,
adodb as database library and sql sever 7 as backend.
i want to update one table for which i required data from other table. and
iretrive data from second table by giving some condition. when i get data,
then to update first table i need to use do while loop. instead of that i
want to use select statement directly in update query.
plz give me some help.
following is the my queries and its out put
StrSql = ""
StrSql = "Select * From SalesVchMaterialDesc where TransactionID=" &
txtTransactionID.text & ""
rsMName.Open StrSql, Conn, adOpenKeyset
Do While Not rsMName.EOF
StrSql = ""
StrSql = "Update StockTable Set Outward=Outward - " &
rsMName("Netweight") & ",OutwardQty=OutwardQty - " & rsMName("Qty") & "
Where MaterialId=" & rsMName("Material_Name") & " and VoucherDate='" &
Format(rsMName("VoucherDate"), "mm/dd/yyyy") & "'
RsAdd.Open StrSql, Conn, adOpenStatic
rsMName.MoveNext
Loop
rsMName.Close
out put
***main query
Select * From SalesVchMaterialDesc where TransactionID=848
do while not loop
Update StockTable Set Outward=Outward - 8.06,OutwardQty=OutwardQty - 1
Where MaterialId=221 and VoucherDate='04/01/2004' and SMID=0
loop
I am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly. My problem is that the table I am pulling data from is mainly foreign keys. So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys. I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit. I run the "test query" and everything I need shows up as I want it. I then go back to the gridview and change the fields which are foreign keys to templates. When I edit the templates I bind the field that contains the string value of the given foreign key to the template. This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value. So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors. I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode. I make my changes and then select "update." When the page refreshes, and the gridview returns, the data is not updated and the original data is shown. I am sorry for so much typing, but I want to be as clear as possible with what I am doing. The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work. When I remove all of my JOIN's and go back to foreign keys and one table the update works again. Below is what I have for my SQL statements:------------------------------------------------------------------------------------------------------------------------------------- SELECT:SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) WHERE (People.PersonID = ?)UPDATE:UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ? ---------------------------------------------------------------------------------------------------------------------------------------The only fields I want to update are in [People]. My WHERE is based on a control that I use to select a person from a drop down list. If I run the test query for the update while setting up my data source the query will update the record in the database. It is when I try to make the update from the gridview that the data is not changed. If anything is not clear please let me know and I will clarify as much as I can. This is my first project using ASP and working with databases so I am completely learning as I go. I took some database courses in college but I have never interacted with them with a web based front end. Any help will be greatly appreciated.Thank you in advance for any time, help, and/or advice you can give.Brian
Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".
Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.
I have a table which I need to obtain data from but am having a problem with select statement. Specifically, I have a table that has multiple records for a particular hostName where the name of the host is either a shortname (say "Larry") or a long name (say "Larry's"). I need to display only the long names (NOT THE SHORT NAME records with the similar hostName). Select winsHostName, len(winsHostName) from winsData where winsClientIPAddress IN (SELECT winsClientIPAddress from winsData Group By winsClientIPAddress Having count(winsClientIpAddress) > 1) Order by winsHostName Which returns data Name Length ATVDDR 6 ATVDDR1 7 This is a s far as I can get but, Now, I need to list all remaining table fields based on the Name with the longer length and this is where I run into trouble. The output should read per below with the remaining table fields which I cannot seem to extract with nested select statement Name IP Addr Location ATVDDR1 1.1.1.1 2ndFloor I tried adding another GROUP BY by this gets me no where because I do not need to execute another aggregate in select statement. Maybe I need to use INNER JOIN Please advise any assistance.
Would it be possible to retrieve a "dynamically" named field from a table by using an input parameter?
For example, if a table has fields named Semester1, Semester2, Semester3, Semester4, and I was lazy and only wanted to create one stored procedure for all semesters could I do the following...
ALTER PROCEDURE u_sp_x @semester int AS Select Semester@semester From ThisTable
Update ed_abcdeeh set category = case when name_of_school = '' then category = 'No Facility' else '' end,status = case when name_of_school = '' then status = 'Non-Compliant' else 'Compliant' end.
How to make this query right.. when name of school is blank i want to update my category to No facility, but if the name of school has data it will just make it blank. same to the status..
I have the following list of fields in a table named call.CallID, CallGroupID, PersonID, ParentID,I have two records.23, 45, John, Null26, <Incorrect Value>, Jane, 23Now, I'm wanting to set the CallGroupID of record two, to that of theCallGroupID of record one.This is what I came up with, but it doesn't work.update call set CallGroupID = (Select CallGroupID from call whereCallID = ParentID) where ParentID is not nullAs you know the Select statment I have embedded is trying to pull up alist CallGroupIDs from records where the CallID = ParentID. What Iwant it to do is pull up the CallGroupID of the record where the CallID= ParentID.The CallID is a unique field, so there is only one such record in thetable.Any help?Thanks
I am trying to write an update t-SQL statement from the following select statement:
SELECT EduNextContacts.ssn FROM EduNextContacts Left JOIN EduContactsAuditChanges ON EduNextContacts.ssn = EduContactsAuditChanges.ssn AND EduNextContacts.campaign_code = EduContactsAuditChanges.campaign_code GROUP BY EduNextContacts.ssn HAVING(SUM(CASE EduContactsAuditChanges.release_code WHEN '103' THEN 1 ELSE 0 END) + SUM(CASE EduContactsAuditChanges.release_code WHEN '102' THEN 1 ELSE 0 END) >= 2)
I tried many versions of writing my update statement with no luck. My most recent version is as follows:
UPDATE EduNextContacts
SET EduNextContacts.overLMLimit = 'Y'
FROM EduNextContacts Left JOIN
EduContactsAuditChanges ON EduNextContacts.ssn = EduContactsAuditChanges.ssn AND
HAVING(SUM(CASE EduContactsAuditChanges.release_code WHEN '103' THEN 1 ELSE 0 END) +
SUM(CASE EduContactsAuditChanges.release_code WHEN '102' THEN 1 ELSE 0 END) >= 2))
And gives the following error:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Can anyone help shed some light on how to make an update statement from my SELECT query above?
and i have also another table which is namelist that is linked to the masterlist table.. after i search for the record andrew in the table namelist..i updated andrew as 25 and sex is female..now i want reset andrew's record, same to the records that andrew has in the table masterlist..
SELECT - 1 AS OrganisationID, '--Please Select--' AS OrganisationName UNION ALL SELECT OrganisationID, OrganisationName FROM tblOrganisation ORDER BY OrganisationName
SELECT SUM(PTR_QUANTITY) OVER (PARTITION BY PTR_SYMBOL ORDER BY PTR_DATE, PTR_SEQUENCE) AS 'ACUMULADO' FROM MPR_portfolio_transactions ORDER BY PTR_SYMBOL, PTR_DATE, PTR_SEQUENCE
This select statement generates one line per existing record. And what I would like to do next is to UPDATE the field 'PTR_ACUM' with the result of the 'ACUMULADO'
Dim cn As New System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("LocalSqlServer").ToString())
cn.Open()
Dim adapter1 As New System.Data.SqlClient.SqlDataAdapter()
adapter1.SelectCommand = New Data.SqlClient.SqlCommand("update aspnet_Membership_BasicAccess.Products set id = '" & textid.Text & "', name = '" & textname.Text & "', price = '" & textprice.Text & "', description = '" & textdescription.Text & "', count = '" & textcount.Text & "', pictureadd = '" & textpictureadd.Text & "', artist = '" &textartist.Text & "', catergory = '" & textcategory.text & "' where id = " & Request.Item("id") & ";", cn)
cn.Close()
Response.Redirect("database.aspx")
it posts and the page loads but the data is still the same in my datagrid. what could be wrong with this simple statement... i've tried testing the statement above with constant values of the correct type but i don't think that matters because the SqlCommand() accepts a string only anyways.. doesn't it?
Hello All I have had asked the same question in another post, i didnt get answer to it i might have had asked it wrongfully
Soo the question is: When creating a SQLDataSource in the wizard you get to the pont where you select the option . It says that by using this datasource you can select to update delete and insert. So my question is if i am creating a select statement to reterieve the data from the Table, then what does it do it do if my intention is to only reterie the data. Or what is the other way that it could be helpful to me ??
thanks all I hope it make sence, if not I wrill write another post to bring step by step info into it.
I have a GridView dispalying from a SQLServerDataSource that is using a SQL Select Union statement (like the following):
SELECT FirstName, LastNameFROM MasterUNION ALLSELECT FirstName, LastNameFROM CustomORDER BY LastName, FirstName I am wondering how to create Update and Insert statements for this SQLServerDataSource since the select is actually driving from two different tables (Master and Custom). Any ideas if or how this can be done? Specifically, I want the Custom table to be editable, but not the Master table. Any examples or ideas would be very much appreciated! Thanks, Randy
Hi Group!I am having a problem of using SUM under UPDATE statement. I understandthat SQL does not allow me to use SUM in UPDATE, but unfortunately, Ican not find a way to get around it. Can someone please give me someidea? Thanks a million!--Here is the criteria:Under the same PORTFOLIO_ID, if the ACCOUNT_OPENDATE is in the samemonth as the PORTFOLIO_OPENDATE, then sum the account_openamt for thesame PRODUCT, and update the CHECKING_OPENAMT, SAVINGS_OPENAMT,CD_OPENAMT, etc in table PORTFOLIO. For other accounts opened NOT inthe same month as the PORTFOLIO_OPENDATE, just ignore them.--Here are the tablescreate table portfolio(portfolio_id int,portfolio_opendate smalldatetime,checking_openamt money,savings_openamt money,cd_openamt money)insert into portfolio values(1,'2/15/2005',0,0,0)create table account(portfolio_id int,product varchar(20),account_opendate smalldatetime,account_openamt money)insert into account values(1,'checking','2/15/2005',2000)insert into account values(1,'checking','2/20/2005',3000)insert into account values(1,'savings','2/20/2005',3000)insert into account values(1,'cd','5/15/2005',5000)--Ideal Output--id portfolio_opendatechecking_openamtsavings_openamtcd_openamt1 2/15/2005500030000--Here is my query:update portfolioset checking_openamt=sum(b.account_openamt) --problem appears!from portfolio as ajoin account as bon a.id=b.idand year(a.portfolio_opendate)=year(b.account_opendate )and month(a.portfolio_opendate)=month(b.account_openda te)and product ='checking'--and product='savings'--and product='cd'Thanks again!!
I have a table called notes, with thousands of rows of "notes" entered by customer services agents. Each row has an account number, date and username columns.
An account can have many notes on thetable.
How can I select the last two notes on the table left for each account? Trying to use select top 2, but of course it's only giving me the top 2 notes for ALL accounts.
In my select statement, I return a column for 'datediff' using a CASEquery. I call this column 'Elapsed_days'is there anyway I can use this result later on in the same select? IEI want to refer to 'elasped days' in another CASE query rather thanhave to re-write something which incorporates the original one.Simpler the better - I'm new!Make any sense?Hope so
I am trying to Write an update string for individual partID's. I wrote this query below but it isn't populating the time to test.
SELECT 'UPDATE Parts SET = [TimeToTest]' + ' ' + Convert(varchar, (select test From [dbo].[db_PartsCats] as c Join Parts As P on P.category = C.CatID Where PartID = 48727)) + ' ' + 'WHERE PartID = ' + CONVERT(varchar, P.PartID) From Parts As P Where FRID = 0 And TimeToTest = 0 and TimeToInstall = 0 and TimeToProgram = 0 And TimeToTrain = 0 And manufacturer = 187 Order By categoryMy results:
Should get UPDATE Parts SET = [TimeToTest] 0.5000 WHERE PartID = 48871 But getting Nulls instead
table A: | ID | FRUIT | VEGETABLE | GOOD | -------------------------------------------- | 1 | orange | cabbage | no | | 1 | apple | lettuce | yes | | 1 | kiwi | broccoli | no | | 1 | pear | kale | yes |
table B: | ID | FRUIT | VEGETABLE | ------------------------------- | 1 | apple | lettuce | | 2 | pear | kale |
If the fruit and vegetable in table A is found in table B, then set the GOOD column = yes, else no.
This is what I have so far.
update tableA set GOOD = (case when tableA.id = C.id then 'yes' else 'no' end ) from (select tableA.id as id from tableA A left join tableB B on B.fruit = A.fruit and B.vegetable = A.vegetable) C
I'd like to make a logic statement, that would take as arguments result of the sql select query. In more details: I would like to create a local Bool variable that would be false if some value is NULL in the table (or select query).Query example:select taskID from Users where Login=@usernameWhich classes/methods should i use to solve this problem? I use SqlDataSource to get access to database and i think i should use something like SqlDataSource.UpdateCommand and SqlDataSource.UpdateParameters but dont know how to build from this a logic statement.Thanks in advance
I need to pull one field from one table and one field from another table that is i need to pull 'eGroupName' field from 'Exception' Table and 'eGroup Description' field from 'eGroup' Table but there is no connection between these two tables means there is no forign key relationship between these two tables but i need to pull both fields . If i use INNER JOIN i need to mention relationship between both tables right? so how to write query for this , and one more thing is i need to add an extra column as "Location"which is not there in either of tables for that i need to use CASE Statement as if DataSource = 1 then "ABC" else "BCD" . pls help me out in writing SQL Statement??? is this correct ?? its showing me errors Select Exception.eGroupName, eGroup.eGroupDescription from Exception Inner Join eGroup ON ??? (case when 'DataSource =1' then 'ABC' then 'BCD' endcase) Where ..... Pls correct me Thanks
Hello friends , I have table (MoneyTrans) with following structure [Id] [bigint] NOT NULL, [TransDate] [smalldatetime] NOT NULL, [TransName] [varchar](30) NOT NULL, -- CAN have values 'Deposit' / 'WithDraw' [Amount] [money] NOT NULL I need to write a query to generate following output Trans Date, total deposits, total withdrawls, closing balance i.e. Trans Date, sum(amount) for TransName='Deposit' and Date=TransDate , sum(amount) for TransName=Withdraw and Date=TransDate , Closing balance (Sum of deposit - sum of withdraw for date < = TransDate ) I am working on this for past two days with out getting a right solution. Any help is appreciated Sara
How do I Query two tables and minus the result to be displayed in a gridview. I will appreciate all the help that I can get in this regard. Find below my two select statement 1st Select StatementDim SelectString As String = "SELECT DISTINCT [Course_Code], [Course_Description], [Credit_Hr], [Course_Type], [Course_Method] FROM [MSISCourses] WHERE (([Course_Type] = Core) OR ([Course_Type] = Information Integration Project) "If radBtnView.Checked = True ThenSelectString = SelectString & " OR ([Course_Type] = 'Knowledge')"End IfIf chkGView.Checked = True ThenSelectString = SelectString & " OR ([Specialization] = 'Data Management')"End IfIf chkGView2.Checked = True ThenSelectString = SelectString & " OR ([Specialization] = 'General')"End IfIf chkGView1.Checked = True ThenSelectString = SelectString & " OR ([Specialization] = 'Electronic Commerce')"End IfIf chkGView3.Checked = True ThenSelectString = SelectString & " OR ([Specialization] = 'Network Administration and Security')"End IfIf chkGView4.Checked = True ThenSelectString = SelectString & " OR ([Specialization] = 'Healthcare Information Systems')"End IfSqlDataSource3.SelectCommand = SelectString 2nd Select Statement"SELECT DISTINCT [Co_Code], [Co_Description], [Cr_Hr], [Co_Type], [Co_Method] FROM [StudentCourses] WHERE ([Co_Code] = StdIDLabel)" my gridview<asp:GridView ID="GridView2" runat="server" AutoGenerateColumns="False" DataKeyNames="Course_Code" DataSourceID="SqlDataSource3" GridLines="Horizontal"><Columns><asp:BoundField DataField="Course_Code" HeaderText="Course_Code" ReadOnly="True" SortExpression="Course_Code" /> <asp:BoundField DataField="Course_Description" HeaderText="Course_Description" SortExpression="Course_Description" /> <asp:BoundField DataField="Credit_Hr" HeaderText="Credit_Hr" SortExpression="Credit_Hr" /> <asp:BoundField DataField="Course_Type" HeaderText="Course_Type" SortExpression="Course_Type" /> <asp:BoundField DataField="Course_Method" HeaderText="Course_Method" SortExpression="Course_Method" /> </Columns></asp:GridView>
I am using a CASE statement within a SELECT query to sum up values for different customers.
SELECT CR_CUST.Customer_Code, 'General_01' = CASE WHEN CR_PROD.Part_Class_Code = '01' THEN SUM(CR_INVOICE.Line_Value) ELSE 0 END, 'General_07' = CASE WHEN CR_PROD.Part_Class_Code = '07' THEN SUM(CR_INVOICE.Line_Value) ELSE 0 END, 'General_08' = CASE WHEN CR_PROD.Part_Class_Code = '08' THEN SUM(CR_INVOICE.Line_Value) ELSE 0 END FROM CR_CUST INNER JOIN CR_INVOICE ON CR_CUST.Customer_Code = CR_INVOICE.Customer_Code INNER JOIN CR_PROD ON CR_INVOICE.Product_Code = CR_PROD.Product_Code WHERE (CR_PROD.Part_Class_Code = 1 OR CR_PROD.Part_Class_Code = 7 OR CR_PROD.Part_Class_Code = 8) GROUP BY CR_CUST.Customer_Code, CR_PROD.Part_Class_Code
My question is this - is it possible to expand my SQL Query into a Sub Query so that each customers data appears on the same line of the results?, like so...
I can achieve this by writing my results into a temporary table and extracting the data with the following SQL Query, but I just thought it would be really cool if I could do it in one SQL Statement without using a temporary table.
SELECT Customer_Code, SUM(General_01), SUM(General_07), SUM(General_08) FROM #MyTempTable GROUP BY Customer_Code
I am trying to apply the logic from the following resource: URL....but cannot get it to work with my logic for some reason.For example, the following query:
;WITH CTE1 AS (SELECT CONVERT(VARCHAR, GETDATE(), 120) AS Col1), CTE2 AS (SELECT CONVERT(VARCHAR, GETDATE(), 111) AS Col2) SELECT CTE1.Col1,CTE2.Col2 FROM CTE1 CROSS JOIN CTE2 GO
Produces the following output:
Col1 | Col2 2014-05-08 10:55:54 | 2014/05/08
But, as soon as I try to do something else like:
;WITH CTE1 AS (SELECT COUNT(login) FROM userinfo AS Col1), CTE2 AS (SELECT COUNT(login) FROM userinfo AS Col2) SELECT CTE1.Col1,CTE2.Col2 FROM CTE1 CROSS JOIN CTE2 GO
I receive the following errors:
Msg 8155, Level 16, State 2, Line 1 No column name was specified for column 1 of 'CTE1'. Msg 8155, Level 16, State 2, Line 2 No column name was specified for column 1 of 'CTE2'.
Are there limitations when trying to use multiple CTE in a single query?
hi my self avii want to copy data from one table to other table,by giving certaincondition and i want o use insert statement .in this i want to pass somevalue directly and some value from select statement , if i try i ll geterror i.e all column of destination table (i.e in which i want to insertdata) should match with all columns in values column some thing likethis.plz give me some helpful suggetion on this
