.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

LTRIM to a VIEW without updating the table

Posted By:      Posted Date: October 28, 2010    Points: 0   Category :Sql Server

Hi All,

I have a VIEW that compares two fields from two tables in the same database, the trouble is that one field has leading zero's and the other doesnt, I cannot update the table containing the field with leading zero's as this will impact on other process that have been wriiten to compensate for the leading zero's and would therefore like to write the update into the VIEW, so that the table data is not disturbed.

This is an excerpt from the code...


WHEN ledgertrans.paymreference!='-%' 

THEN bankchequetable.AccountID 


WHEN ledgertrans.paymreference='-%' AND ledgertrans.paymreference=vendtrans.voucher AND vendtrans.paymreference=bankchequetable.chequenum 

THEN bankchequetable.AccountID 



View Complete Post

More Related Resource Links

Change grid view from table to other HTML layout



You'll have to excuse my ignorance, I'm coming at this from a front-end perspective, and don't really know how to deal with data views in ASP.NET.

Basically, I'm trying to change the layout of some repeated data from a straight table, to something more design-heavy.  It's the same data, just re-organised slightly.  The current ASP.NET code looks like this:

                    <asp:GridView ID="GVMoments" runat="server" AllowPaging="True" AllowSorting="True"
                        AutoGenerateColumns="False" PageSize="20" RowHeaderColumn="FirstName"
                        SkinID="GroupSkin" DataKeyNames="moment_id" 
                        <PagerSettings Mode="NumericFirstLast" PageButtonCount="20" Position="TopAndBottom" />
                        <RowStyle />
                                <asp:BoundField DataField="moment_id" HeaderText="moment_id" ReadOnly="True" SortExpression="moment_id"
                                    Visible="False" In

Entity Data Model and database view returning the same columns as there are in a table


When adding a stored procedure into the Entity Data Model I can select whether the procedure returns a scalar, a (new) complex type or one of the entity types I already defined. 

How do I do something similar for a view?

I mean assuming I have a view like this

CREATE VIEW FilteredFoos as SELECT Foo.* FROM Foo join ... WHERE ...

(that is a view that implements some involved filtering, but returns all columns from one table) how do I add it to the project so that I can use the entity set, but get the Foo objects, not some new FilteredFoo objects.

var foos = myDB.FilteredFoos.Include("Bar").ToList();

foreach (Foo foo in foos) { ...

Thanks, Jenda

Personal view reverts back to default after updating properties of item


I am working with a company that has 1 site collection with several sub sites.  I have built 3 document libraries with workflow in 3 different sub sites and all behave as expected.  I have one site where ALL libraries do something perplexing;

If you are in a custom personal view and you go into the properties of a document and then exit (update or not, does not matter) it reverts back to the default view of that library. 

Has anyone seen this?  is there an option when creating an album that could cause this?

thanks in advance for any input on this odd issue.

How to create scrolling table for columns in a SharePoint Designer Data View


I have a fairly complex SharePoint Designer 2007 Data View they have added two more requirements.

1. Have the columns on the right side be able to scroll left to right as there is a large number of columns, while the first 3 columns on the left side stay locked in place.  This is only one list not two, and because of some other requirements I have it must remain to be just one list.

2. Then they also need to have scrolling up and down of the entire list with the headers locked in place.

The first requirement is more important than the second, but preferrably I need to do both.

I am new to XSL so not familar with the syntax. Is this something I can do on a custom Data View? Does anyone have any examples of how?

I have a drawing that show how it needs to look but I don't see an option here to upload a graphic.

So here is a link to my graphic example uploaded to my blog area: http://lindachapman.blogspot.com/  or just the image here: http://4.bp.blogspot.com/_aB01ue__NvQ/TGL42MOvrGI/AAAAAAAAAA4/c3xP4uCy4BU/s1600/Scrolling.png

I failed to mention that 3/4 of the columns are HTML Calculated fields wh

How to create a view in my database from a table in another server/database

Is there a way to create a view in my database referencing a table in another server/database if I have the connection information.  I know how to do it in Oracle, simply create a database link, but have never done so in sqlserver.

Updating table from a table on another server

I am updating a column in table from a table on another server and am having some troubles. My code looks like this...   UPDATE TABLE1 SET COLUMN1A = SERVER2.DATABASE2.SCHEMA2.TABLE2.COLUMN2A FROM TABLE1, SERVER2.DATABASE2.SCHEMA2.TABLE2 WHERE TABLE1.COLUMN1B = SERVER2.DATABASE2.SCHEMA2.TABLE2.COLUMN2B   I keep getting "The multi-part identifier ____ could not be bound."   Security rights are in place and I have access to Server2    Thoughts?

customize list view table header css file

i need to make table header for my list to be red and on hover to get specific  image so all my site have the same appearence also i need this to reflect to the list view webpart thanks

How to Add "Modified On" to my Table and View Listings (SQL 2008)

There is a "Create Date" - but I want to know when the last "Modify" date is in both Tables and Views.  is this even doable?  I think it was in SQL 2005RICHARDG

application role how query a view whose reference table is in another db?

Hi everybody. I created  an application role in a database (DB1) and gave it all the rights on a view  in DB1 which refers to a table located in another db (DB2).  I also gave the rights to the app role on a table of  DB1 I tried to use this app. role through the sp_setapprole launched by a user  (server principal?) which is SQL Server administrator (and local administrator (Win 2003 Server)). With the following query SELECT USER_NAME() I see that the approle is being used. Than, if I query the table on DB1 everything works, but if I query the view, referring a table in db2 I get following error:   The server principal "NameOfServerPrincipal" is not able to access the database "DB2" under the current security context. What should I do to make it work? The table in DB2 has the same schema of the view in DB1 which refers to it. I put the DB1 TrustWorthy and both the database have the db_chaining option activated.   Any idea on how to solve the problem would be widely appreciated. Thank you very much. Vania

SQL Server Table OR Indexed View

Hi     I am having a problem between chosing VIEW and TABLE from a SQL server database   PROBLEM Suppose the data table is name ACTION (of size 30M) with  schema ACTIONID/ ACTIONNAME/USERID/ ACTIONDATE. The Table is updated once everyday with 20K new rows For my purpose I only want a query on ACTION rows which are as recent as 1 week I want queries to run faster selecting from a smaller dataset(as there are joins as well)        SOLUTION A(Indexed) Instead of querying the 30M database everytime; I create a view that will store data from current date to 7 days earlier I set the view to refresh ONLY once a day and NOT everytime there is an insert update delete on the ACTION table That way my queries are faster (as from a smaller dataset) BUT my view doesnt refresh 20k times a day making the performance a disastrous one       Question : Can a VIEW be actually configured to REFRESH only periodically and NOT when table updates?         SOLUTION B(Indexed View) I create a View which stores all the ACTION table rows between 9/16/2010 and 9/9/2010 (today and a week, hardcoded) When new 20K entries are inserted; the view isnt refreshed even once as all the dates ate outside range(despite the ACTION table is updated) A trigger is attached to view to run a script everyday The trig

Adding a table or view multiple times when building a query with BI Report Designer and Report Build

How can I add a table/view multiple times to a single query by using BI Report Designer or Report Builder 3.0? In Report Designer I managed (workaround) to add one table multiple times by creating multiple Named Queries and having the same SELECT. Still I don't know how to add one table multiple times in Report Builder 3.0.

Bridge Table dimension or fact? updating from snapshots



Scenario: Bank Accounts and Customers. One Account can have many customers and many customers can have one joint Account. so its Many to Many relationship.

Special Scenaio: Bank provide us daily snapshot of all thier dimensions and facts, every night thier ETL run, and newsnapshot is available, previous is gone.

I am using SCD Transformation to update the dimensions, Type 1 for all the columns.

Tables1: DimAccounts (AccountsID(PK))

Table2:DimCustomers (CustomerID(PK))

Table3:DimBridge (AccountID (FK), CustomerID(FK), RelationShip (varchar10))

Question1: Are we supposed to treat bridge tables as Dimensions or Facts?

Question2:If it is to be treated as Dimension, How would I apply SCD Wizard to it, Since there are two business keys involved?

Question3: Do i need surrogate key in Bridge Table, like i have in other dimensions?

Thank You


convert string to int before updating database table?


I have a form in which I display data from a database table, change the data and then update the database table. The form consists of textboxes, so all data displayed in the form is string. However - one data is int in the database table (the rest is varchar), so I need to convert it from string to int when I update the database table. How do I do it?

protected void SaveProd_Click(object sender, EventArgs e)
        string BildID = TextBoxProdPictID.Text;
        string namn = TextBoxProdName.Text;
        string kategori = TextBoxProdCategory.Text;
        string stl = TextBoxProdSize.Text;
        string pris = TextBoxProdPrice.Text;
        string iLager = TextBoxProdNumberOf.Text; //this is int in the database table

        SqlConnection conn = new SqlConnection(config);
      //  conn.Open();
        string sql = "UPDATE Produkter SET kategori ='" + kategori + "', namn ='" + namn + "', stl ='" + stl + "', pris ='" + pris + "', iLager ='" + iLager + "' WHERE ID ='" + ID + "'";
        SqlCommand comm = new SqlCommand(sql, conn);//Conversion failed when converting the varchar value '__Page' to data type int.
        SqlDataReader dr = comm.ExecuteReader(); 

        //clear t

EF - create Table View


I am writing a table view for my ef model - I am not using the designer - I keep having issues with it. So I'm doing it the old fashion way of writing the code - All my tables and stored procs are working but now I added these two views and all is broken!

In my SSDL I have -

<edmx:StorageModels><Schema Namespace

Having trouble updating 1,000,000 row table


Using this T-SQL statement this process should take about 2 minutes.  We know this by limiting the update to 100,000 rows. (the table has an identity field so we can control how many and where to start).  100,000 rows update in 11 sec.  That translates to about 2 minutes for the entire million row table.  There is some kind of problem that resolves itself after a while.  We timed the statement in a larger proc where it was contained and it eventually finished after 2 to 4 hours. 






[Cost] = dw_Prod.

updating view based Clr type


i created a clr type (point) and table.

i have view based properties of this type (select C1.X , C1.Y from tableName)

how can i update this view ( i don't have in view the source object(point))


Updating a table using a variable object


I have a variable of Data Type 'Object'. I populate this with 1 row of data via an Execute SQL Task control,  because I have a For Each Loop Container around this Execute SQL task control that gets 1 ID at a time. What I want to do is now that I have the row of data in my variable object, is update my table with the new data.

I am thinking I need to start with a Data Flow Task? But I'm not sure how/what to use to reference my variable so that I can map it to my destination.

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