.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
Kaviya Balasubramanian
Imran Ghani
Post New Web Links

configure both select and Update statements in ADO.NET

Posted By:      Posted Date: September 06, 2010    Points: 0   Category :ASP.Net
 
 i would like to retrieve a value with select and perform a calculation and then save using update in the same batch code?  


View Complete Post


More Related Resource Links

Multiple update statements per row in Gridview Edit

  

I want to set up a gridview that will display data like this

WeekEndingDate Project Category Sun Mon Tues Wed Thu Fri Sat
8/14/2010      proj1   test     2   1   2    3   2   1   0
8/21/2010      proj1   test     0   2   2    2   2   1   0
8/28/2010      proj1   test     0   1  

Copy a column with update and select

  
Hi, I am using SQL Compact 3.5 I am trying to copy the values of a column called "CODIGO" from one table to another table. I mean, I want to copy the column "CODIGO"  from table  "DATOS_ACADEMICOS" to table "ALUMNOS". I use this code: UPDATE ALUMNOS  SET CODIGO=(SELECT DATOS_ACADEMICOS.CODIGO FROM DATOS_ACADEMICOS WHERE DATOS_ACADEMICOS.ALUMNO_ID=ALUMNOS.ID)   But I get an error in the SELECT statment: Major error 0x80040E14, Minor error 25501 [ Token line number = 2,Token line offset = 13,Token in error = SELECT ]   I also tried this code with the same error: UPDATE ALUMNOS  SET CODIGO = (SELECT DATOS_ACADEMICOS.CODIGO             FROM DATOS_ACADEMICOS             WHERE DATOS_ACADEMICOS.ALUMNO_ID=ALUMNOS.ID) WHERE EXISTS (SELECT 1 FROM DATOS_ACADEMICOS WHERE DATOS_ACADEMICOS.ALUMNO_ID=ALUMNOS.ID)   How can I copy from one column to another in a SQL sentence? Thanks    

SSIS SQL SP and Update statements

  
I have a SP that does a bunch of selects and some updates, it ends with doing a select from several temp tables its built, and then doing the update.  My questions is, when I run it manually for the first time i get all the records i would expect.  If i run it from SSIS for the first time i dont get the results i expect, its like the updates that are in the SP have already occurred and so it reduces the number of records I would expect to get back.  My question related to the validation peice, does it actually run the SP when it validates, if it does it would answer my question i guess since its doing the updates.  If it doesnt, not sure hot to explain the diff

Select and update in same query

  
Hi all, I need to update the selected rows from a select statement and update it using an update query. For that the query i am using is UPDATE SERVERACCOUNTSTRAN..TRAN_JOURNALDET SET CONTRATRANSACTIONHEADCODE=T.CODE FROM ##TRAN TR INNER JOIN SERVERSSRETAIL..TRANSACTIONHEAD T ON T.SHORTNAME=TR.CREDT_ACCT WHERE T.COMPANYCODE=1 AND CONTRATRANSACTIONHEADCODE=0 Now the problem is all the values of contratransactionheadcode field is updated to 18. But the real values are different for each row. only the first row value is 18 which is updated to every other row. Thanks in advance.Sujith Kumar www.sujithkumar.tk

Error: "Select statements included within a function cannot return data to a client"

  
hi all, Not sure if there is an easier way to do this but this is my first ever Function I created so far. I am getting the error: "Select statements included within a function cannot return data to a client" All I am trying to do is ...if the date is NOT passed then apply NULL as the default value and run the SELECT statement. If the date IS NOT null then run a different SELECT statement. CREATE FUNCTION [SchemaTest].[fn_Test] ( @instance as uniqueidentifier ,@date as datetime = null ) RETURNS varchar(8000) AS BEGIN If (@date is null) --@date = null ?? Begin Select * From Uds.RealtimeLogging Where instanceID = @instance End Else Begin Select * From Uds.RealtimeLogging Where instanceID = @instance and [timeStamp] > @date End Return @stepIds END Thank you!JCD

is it possible Update in Select?

  
Hi i have z query that select 5 rows from 100,000 rows, there is a field named "VIEW" that must increment if the row have been selected. so when i select this 5 rows , i should increment VIEW field of them. do can i update VIEW in the select line or not? and is it possible execute SP on Select? ALTER PROCEDURE [dbo].[sp_MyProc] AS BEGIN with cte as ( select top 5 id from myTable where (active=1) order by newid() ) select top 5 * from cte c inner join myTablet on t.id = c.id END  Best Regards. Morteza

Having subqueries within select statements

  
I want to know whether there are any consequences of having sub queries within a select statement so for example writing queries using this style.     SELECT NAME ,PROFILE CONVERT(VARCHAR(10), (SELECT PROFILEDATE FROM tblTable WHERE ProfileID= tst.[ID] AND [ProfileDate]=(SELECT MAX([ProfileDate]) FROM [EtpForecastDividendsStaging] WHERE [ProfileID]=qry.[ProfileID] AND [ProfileDate]<qry.[ProfileDate])), 120) , CONVERT(VARCHAR(10), (SELECT MAX([ProfileDate]) FROM tbltable WHERE [ProfileID]=qry.[ProfileID] AND [ProfileDate]<qry.[ProfileDate]), 120) FROM tbltable join tblothertable The above example is just a sample and not a query i had expect to compile, i am just using it as an illustration. I believe its bad programming, but i may be wrong. I tend to like normal traditional joins where you select the column and join based on the corresponding keys, but I am trying to grasp some justification for my approach.   Any ideas    

stored procedure: select and update

  
Is it possible to use a stored procedure to get a value of the database, change it and save it back at ones? Let me show a simple example: In the databse I have an integer value (20). Then I get another number (5), and I would like to subtract it of the  value from database (20 - 5). That means I have to get the value 20 out of the database, of it subtract value 5, and insert (update) new value (15) back to the database. Or is it not even necessary to get the value 20 out of the databse, but only directly subtract value 5 of the value in the database? Is this possible to do with stored procedure?

I have the following 4 select statements.. but the 4th one doesnt return what i think i should be ge

  

I have a report that is presented to the client. It consists of "Scheduled", "Uncompleted", "Completed" and "All". Now what I PERSONALLY think these reports should return for the first 3 should total what is presented in the "All" report.

if i run the report "Scheduled" it returns 0
if i run the report "Uncompleted" it returns 157
if i run the report "Completed" it returns 59
But when i run the "All" report it returns 210

Which leaves a difference of 6. Can anyone tell me what im doing wrong with the following 4 select statements?

--------------------------------
Here is the scheduled report:
--------------------------------
select p.permit_num,K.task_id,K.task_due_date,K.date_completed,K.complete_ind,K.inspRpt_ind,C.*, R.service_rep_name,L.*,S.sys_name,M.manu_name, SI.sentAgc_date 
from tasks K, service_contracts C,service_reps R,locations L, manufacturers M, sys_inspection SI, systems S left outer join sys_permits sp on sp.sys_id = s.sys_id left outer join permits p on p.permit_id = sp.permit_id and p.active = 1 
where K.service_contract_id=C.service_contract_id and C.sys_id=S.sys_id and C.service_rep_id=R.service_rep_id and M.manu_id=S.manu_id and S.location_id=L.location_id and

Update query from select statement

  

Cana you I need to do an update from a select statement...i'm really close here but have not quite figured out the nuance...

the full select statement provides correct results and i've joined the outer table with the inside table....can you make recommendations?

BEGIN TRAN --rollback

UPDATE geprs_price..T_ITEM_PRC_CHG 

SET UPDT_OPR_ID = (select dm.CONFIRM_BY

		FROM geprs_price..T_ITEM_PRC_CHG gep
		JOIN SMS_DATAMART.dbo.T_PRC dm 
		ON dm.PRC_EFF_DT = gep.PRC_EFF_DT
		AND gep.SG_CNTRC_BID_PRC = dm.PRC
		and gep.prc_eff_dt = dm.prc_eff_dt
		LEFT JOIN construct..T_AD_USERS con
		ON dm.CONFIRM_BY  = con.UID
		LEFT JOIN construct..T_AD_USERS crt
		ON dm.CREATE_BY  = crt.UID

		WHERE gep.sg_CNTRC_BID_PRC >0
		AND gep.UPDT_OPR_ID <>dm.CONFIRM_BY
		AND dm.PRC_TYP_ID in (39)
		AND con.DISPLAYNAME IS NOT NULL)

		



--COMMIT

Linq to entities: Always select before delete and update?

  

Hi,

With linq to entities, how can I delete and update data inside a database without first selecting the complete entity at first?
The idea behind this is to improve performance.

Thanks!


Use XML DML with a select or update statement.

  
Given an SQL XML table column with contents like this:

<fld>
  <state>WA</state>
  <taxcert>A18 3865 10</taxcert>
  <effective>1/1/2010</effective>
  <certexp_yn>Y</certexp_yn>
  <expires>12/31/2010</expires>
  <singleuse_yn>N</singleuse_yn>
  <exempt_yn>N</exempt_yn>
  <exempt_reason />
</fld>

I'm trying to modify it with an SQL query like this:

SELECT TOP 100
        [XMLData].modify('insert <entityusecode_id />
              after (/fld)[1]
             ')
FROM    [SCANREF] AS S

This SQL causes this error:

Msg 8137, Level 16, State 1, Line 39
Incorrect use of the XML
data type method 'modify'. A non-mutator method is expected in this context.

Deadlock: Store Procedure that only contains select statement owns an update key lock

  

I have a seemingly simple deadlock graph that contains 2 SP's. One SP is updating a table, while another SP (the victim) is selecting from it. The interesting thing about the graph is that the SP that contains the select (and only a select) is shown to own an UPDATE lock on the table that SP2 wants to update. How is this possible?

TIA,

Kevin


Gridview - Update with Dynamic SELECT Command?

  

Hi All...

We created a GridView-based webform with Update using the design wizards - very nice and it works great.  But then we wanted to the ability to create the SELECT command dynamically based on other controls on the page.  We added something like the following to Page_Load()...

            SqlDataSource1.SelectCommand = "SELECT [StationId], [IP] FROM [Stations]";
            GridView1.DataBind();

The GridView populates normally, but the Update no longer works.  It changes the "Edit" link to "Update", gives the user the edit boxes, but when "Update" is clicked it never seems to write the new data to the database.  No error - it just doesnt work...

Any thoughts?  How can we change the SELECT command (and Connection String) dymamically and still retain the Update functionality?

Many thanks - Curt.  Code follows...

    protected void Page_Load(object sender, EventArgs e)
    {
        //  Doesn't work with or without this conditional
        //
        //if (!IsCallback)
        //{
            SqlDataSource1.SelectCommand = "SELECT [StationId], [IP] FROM [Stations]";
            GridView1.DataBind();
        //}
    }
<

SELECT and UPDATE query problems

  

Hello!

I have a web form that should display advertisements depending of the number of impressions (number stored in a database) and I also need to update the number of impressions when page is loaded. The advertisements are displayed on a web page, but the problem is I don't know where to insert the SqlCommand for updating number of impressions.

Labels are used for displaying the number of impressions.  


        String connectionString = WebConfigurationManager.ConnectionStrings["CMS"].ToString();
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();

            using (SqlCommand commandReklama = new SqlCommand("SELECT * FROM Reklama", connection))
            {
                SqlDataReader readerReklama = commandReklama.ExecuteReader();
                while (readerReklama.Read())
                {
                    string redirectUrl = readerReklama.GetString(1);
                    string url = readerReklama.GetString(2);
                    int brojPrikazivanja = readerReklama.GetInt32(3);
                    bool show = readerReklama.GetBoolean(4);                  
                    string naziv = readerReklama.GetString(5);
                    int location = readerReklama.GetInt32(6);

                    if (

UPDATE query based on the SELECT query

  

Hi,

Can you please advise how can i achive following UPDATE query based on the SELECT query. You can see my comments in Green

UPDATE [Table_POforecast]
   SET [ForecastAmount] = [ForecastAmount]* Coeff --- Coeff value to be from SELECT Statement
 WHERE [PO_No] = [PO_No] --- PO_No to match from SELECT statement

--- SELECT Statement
SELECT     PO_No, (NewBalance / TotalForecastAmount) AS Coeff
FROM       View_Forecast
WHERE     (OutstandingForecast < 0)


 

 


INSERT the Results of a Stored Procedure with Multiple SELECT Statements into Multiple Tables?

  
I have a stored procedure (which is overly complex and written by someone else) that I need to take the results of and put them into tables.  The stored procedure uses Dynamic SQL to build and then run 5 SELECT statements based on literally hundreds of variables.  I need to take the results of these SELECT statements and put them into tables in a different database.  Rewriting or re-creating the logic of the stored procedure is NOT AN OPTION as the logic may change over time and maintaining it in multiple places would be nightmarish.  Can anybody help me with this?  I've googled extensively, but have only been able to find examples of using one result set not multiple ones.
Stephen
Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend