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

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Two TSQL command lines into one

Posted By:      Posted Date: April 14, 2011    Points: 0   Category :

Hello, I create a set of new items for each new user by copying from a table which holds the templates. Then I rename the "dummy" user ID with the real user ID. I pass the real user ID from the application calling the the stored procedure.

THE problem is: what happens if two users create new records at the same time!!!! oh oh oh ...

Here goes the code:

INSERT INTO userTable (userID, TemplateFK, Rating) SELECT UserID, PK as TemlateFK, Rating  FROM  templateTable

UPDATE userTable SET UserID=@userid WHERE UserID=-1

I have set the UserID in the templateTable="-1"; So, all new set of fields per user are inserted with the value="-1"

How could i combine the two lines of code into one? This code will obviously cause problems if two users at the same time create a new set of records...

Many thanks for your help.







View Complete Post

More Related Resource Links

sql agent job command step multiple lines?


This is an oldie but a goodie, maybe there's a trick I haven't heard about?

When I create an OperatingSystem (CmdExec) job step, it seems to only execute the first line command. 

So if the first line is:

dtexec foo

It executes dtexec.  Fine.

But if I have two command lines:

set myvar=123
dtexec foo

It only executes the set command and never calls dtexec.  Bummer.

Now, we can work around this by executing a .cmd file, but then I have a whole issue of deploying .cmd files in different environments yada yada.

So the question is, is there any (new-ish) magic trick that will tell the SQL Agent job step to execute two or more command lines right from the job?




Best way to execute three TSQL lines?



We are in the process of consolidating all of our different dbases to one. Our has been chosen to be the main dbase and everyone will move their data to ours....however we want to maintain logical seperation. So for example Denver data might be Id's with Den01, Atlanta Atl01, etc. Now I was hoping to design a single set of objects to access this data for everyone so I came up with a single dbase that our web app calls. The web will pass the id such as Atl01 and then I will be able to determine which data to return. I have a working model but I'm not sure this is best approach / best way to get there. So web calls sp_customerlist and sends me Atl01. Inside sp_customerlist we see:

--First we select specific location data source
SELECT @PhysDb = MainDb FROM MyDb.dob.LocDataSourceLookUp WHERE ID = @WebLocVar --in our example we passed in Atl01
--MainDb = ATLDB so now I can get the list

Draw lines excactly on physical device pixels

When you draw a line in WPF you will experience that they often appear blurry. The reason for this is the antialiasing system that spreads the line over multiple pixels if it doesn't align with physical device pixels.

Row command working on all rows not selected one?



I have this code that hides a column in a gridview.  (The column houses another nested gridview).

    Protected Sub GridView1_RowCommand(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewCommandEventArgs) Handles GridView1.RowCommand
        If e.CommandName = "Select" Then
            If GridView1.Columns(0).Visible = False Then
                GridView1.Columns(0).Visible = True
                GridView1.Columns(0).Visible = False
            End If
        End If
    End Sub

The trouble is that if I click the select link on one row that action to hide is carried out on all rows.  How can I set this up so that it only hides the row that is selected?


Print command



Is there a way of setting up a print area for a part of my webpage, I only want to print a certain part of the page, when I select print at the moment it will print the whole page and the layout is rubbish!!

command object error


Could someone please tell me what is wrong with my syntax here?  I'd be much appreciative.


objCmd = New SQLCommand("SELECT CASE WHEN objDR('Item') = 'Enter Bill of Lading' then enterbol " _  

& "WHEN objDR('Item') = 'Bill of Lading History' THEN bolhistory " _  

& "WHEN objDR('Item') = 'Request Transfer' or objDR('Item') = 'View Transfer in Progress' THEN transfers " _  

& "END  from plantGroups where plantgroupid=@plantgroupid", objConn)

Design Patterns: Simplify Distributed System Design Using the Command Pattern, MSMQ, and .NET


Service-oriented architecture is a great framework when you need to perform distributed computing tasks over the Internet. But when you want to perform processing inside your local network, a different solution may provide a better fit. That solution, based on the Command pattern, uses Windows services and Microsoft Message Queuing to implement a queued system that meets your needs better than a service-oriented solution. This article explains how to build it.

Brad King

MSDN Magazine September 2004

Command Management: Use Design Patterns to Simplify the Relationship Between Menus and Form Elements


In Windows Forms applications, similar commands, such as those in a menu and their counterparts on a toolbar, are not automatically related. They don't fire the same event or run the same handler routine. Yet code that allows the same or similar user commands to fire the same code simplifies development.This article describes the principles of command management and why it's important to have functional commands that are not exclusive to any one UI element. In order to provide an MFC-like command management infrastructure, a design pattern is developed and applied as a series of C# classes.

Michael Foster and Gilberto Araya

MSDN Magazine October 2002

Use DB query result to generate Jquery required lines of code



I 'm gonna query database for photo records. each record include "photo path" where imagesURL  stored in string format

 I Choses my favorite Juqery plugin to show images. Plugin needs some lines of code like below.

How could I  generate these lines

$(function() {
sleep: 2,
fade: 1
}, [
{ src: 'picture1.jpg' },
{ src: 'picture2.jpg' },
{ src: 'picture4.jpg' }

stsadm Import command + include user security



When I move sites from test to staging to production, I am using the stsadm –o export command with the –includeusersecurity option in the staging environment.  In production environment, I then use the stsadm –o import command with the –includeusersecurity option.  I was assuming that this option was bringing over the security on the exported site only.  This does not seem to be the case.

Last night I exported one sub site to produtction.  Today, I was notified that the security groups on other sub sites have been altered.  


So why is it when using the –includeusersecurity option effect all sub sites and not just the one sub site being moved from staging to production

Executing Command Logic in a View Model

Through the use of the Windows Presentation Foundation's (WPF) advanced data-binding capabilities, the logical structure of a user interface can relatively easily be disconnected from its presentation. Bindable components can now contain nearly all of an application's user interface logic and in most circumstances, replace the need for controller objects from the classic Model-View-Controller (MVC) pattern. So it's no wonder that a new pattern for UI design has appeared

There is already an open DataReader associated with this Command which must be closed first.


when i insert record

There is already an open DataReader associated with this Command which must be closed first.

help me.

SqlConnection conn = new SqlConnection(@"Data Source=Localhost\SQLEXPRESS;Initial Catalog=StudentDetails;Integrated Security=True");

SqlCommand cmd2 = new SqlCommand();
SqlCommand cmd1 = new SqlCommand();

if (conn.State == ConnectionState.Closed)

cmd2 = new SqlCommand("Select * from UserInfo where Standard", conn);
            SqlDataReader dr1 = cmd2.ExecuteReader();
            string stu, par,std,sec,roll;
            par = "";
            stu = "";
            std = "";

Help: Too many lines of code to achieve a simple goal: keep my NULL SQL values...


Hello all

What is the best way (best practice) to preserve my NULL SQL values in the database
during an insert/update operation (without receiving cast invalid errors)?
Also, how to display a string 'n/a' when a sql value is NULL?

My project is using FormView with Edit/Insert templates and classes to represent my tables

Goal1....: display 'n/a' when there are NULL SQL values in the database
Solution1: I am using ISNULL(field,'') in my store procedure for SELECT statements

Goal2....: if the fields are 'n/a', then save them back in the database as NULL
Solution2: on my insert/edit methods I am having to check the values being passed, i.e:

cmd.Parameters.Add(new SqlParameter("@scope", SqlDbType.NVarChar, 50));

if (item.Scope == "n/a")
 cmd.Parameters["@scope"].Value = DBNull.Value;
 cmd.Parameters["@scope"].Value = item.Scope;

Not to mention that for SQL DateType fields when you use ISNULL(field,'') the return string is '1/1/1900 12:00:00 AM'
and I have to change the field value in every field that represents a date, i.e:

protected void FormView1_DataBound(object sender, Sy

Query Command to Report


I would like to generate a crystal report showing the report data based on my custom SQL query.

Where I can provide my SQL query or stored procedure.

Please give me good tutorial links on this too.

Thanks in advance.

Localization for command text Microsoft Ribbon for WPF

Hello, Is there any way I can localize the command text that comes with the ribbon, like the commands in the Ribbon context menu?   Add to Quick Access Toolbar Add gallery to Quick Access Toolbar Remove from Quick Access Toolbar Show Quick Access Toolbar above the Ribbon Show Quick Access Toolbar below the Ribbon Minimize the Ribbon Maximize the Ribbon   I got this list from the Source -- \Resources\ExceptionStringTable.txt

Command - Hyperlink

Why does the command work when I use it in a button, but not in the hyperlink? They are both bound the same way and named the same...             <Border Grid.Column="0" Width="170"                     Background="WhiteSmoke" BorderBrush="Lightgray" BorderThickness="1" CornerRadius="5" SnapsToDevicePixels="True">             <TreeView>                <TreeViewItem Header="Provinces">                   <Button Content="Show All Provinces" Command="{Binding Path=Command_ShowAllProvinces}" />                                     <Hyperlink Command="{Binding Path=Command_ShowAllProvinces}">                      <TextBlock T

TSQL: Passing array/list/set to stored procedure (MS SQL Server)

Passing array/list/set to stored procedure is fairly common task when you are working with Databases. You can meet this when you want to filter some collection. Other case - it can be an import into database from extern sources. I will consider few solutions: creation of sql-query at server code, put set of parameters to sql stored procedure's parameter with next variants: parameters separated by comma, bulk insert, and at last table-valued parameters (it is most interesting approach, which we can use from MS SQL Server 2008). Ok, let's suppose that we have list of items and we need to filter this items by categories ("TV", "TV game device", "DVD-player") and by firms ("Firm 1", "Firm2", "Firm 3). It will look at database like this So we need a query which will return us list of items from database. Also we need opportunity to filter these items by categories or by firms. We will filter them by identifiers. Ok, we know the mission. How we will solve it? Most easy way, used by junior developers - it is creating SQL-instruction with C# code, it can be like this List<int> categories = new List<int>() { 1, 2, 3 };   StringBuilder sbSql = new StringBuilder(); sbSql.Append( @" select i.Name as ItemName, f.Name as FirmName, c.Name as CategoryName from Item i inner join Firm f on i.FirmId =
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