Getting The ORDER BY clause is invalid in views, unless TOP or FOR XML is also specified

I have this SQL Server stored procedure where I am returning an XML type output, however before that I will be updating the corresponding records as well.

ALTER PROCEDURE [dbo].[GetPublishedData](@PublicationURL varchar(100),@Number int) AS IF (@Number = 0) BEGIN BEGIN TRANSACTION TRAN2 SELECT 1 AS Tag, NULL AS Parent, NULL AS [root!1!], NULL AS [Item!2!Id], NULL AS [Item!2!Action], NULL AS [Item!2!Pub_Id], NULL AS [Item!2!Item_Id], NULL AS [Item!2!Item_type], NULL AS [Item!2!Last_Published_Date], NULL AS [Item!2!Url], NULL AS [Item!2!Schema_Id] UNION SELECT 2, 1, '1', T.ID, T.ACTION, T.Pub_Id, T.Item_Id, T.ITEM_TYPE, T.LAST_PUBLISHED_DATE, T.URL, T.SCHEMA_ID FROM DBO.AUTN_ITEMS T WHERE FLAG = 0 AND ACTION IN ('ADD','UPD') AND URL LIKE @PublicationURL + '%' ORDER BY [Item!2!Last_Published_Date] ASC FOR XML EXPLICIT /*Update the FLAG with 1*/ UPDATE DBO.AUTN_ITEMS SET FLAG = 1 WHERE ID IN (SELECT ID FROM DBO.AUTN_ITEMS AT WHERE AT.FLAG = 0 AND ACTION IN ('ADD','UPD') AND AT.URL LIKE @PublicationURL + '%' ORDER BY AT.LAST_PUBLISHED_DATE ASC) COMMIT TRANSACTION TRAN2 ENDRETURN

I have two questions:

How to use Order by in my Update SQLIs there better way to write above query where I can simply pass the transaction id for updating the records

Please suggest!!

View the original article here

ListView (activity feed) with 20 different types of rows, each having 20 different views

A week ago I asked a question on stackoverflow about having a listview with different kinds of items in it.

I know how to populate a list with custom items via custom adapter. But how do I populate it with a few different types of items?

At the time I didnt know that it would have made a difference so I mentioned I only have 4 types of list items not to complicate things. I got some responses with extremely simple cases explained (2 types of items with a single view inside each one). I dont think I can implement those approaches explained in the examples I got, I need a more object oriented approach.

I would like to have a separate class that takes an XML layout and deals with its views and its buttons.

Something like this:

public class MyProfileCommentAdapter extends BaseAdapter { private ArrayList itemNames = new ArrayList(); private ArrayList itemRatings = new ArrayList(); private ArrayList itemComments = new ArrayList(); private ArrayList itemCommentDates = new ArrayList(); private ArrayList itemIds = new ArrayList(); private Context context; private LayoutInflater layoutInflater; private ImageButton iMyCommentsCommentedProductImage; private TextView tvMyCommentsCommentedItemName; private TextView tvMyCommentsCommentedItemDate; private TextView tvMyCommentsCommentedItemContent; private TextView tvMyCommentsCommentedItemRating; private Button bMyCommentsEditComment; private Button bMyCommentsDeleteComment; public MyProfileCommentAdapter(Context context, ArrayList itemNames, ArrayList itemRatings, ArrayList itemComments, ArrayList itemCommentDates, ArrayList itemIds){ this.context = context; this.itemNames = itemNames; this.itemComments = itemComments; this.itemCommentDates = itemCommentDates; this.itemRatings = itemRatings; this.itemIds = itemIds; layoutInflater = (LayoutInflater) context.getSystemService(Context.LAYOUT_INFLATER_SERVICE); } @Override public int getCount() { return itemIds.size(); } @Override public Object getItem(int arg0) { return itemIds.get(arg0) ; } @Override public long getItemId(int position) { return itemIds.get(position); } @Override public View getView(int position, View convertView, ViewGroup parent) { if(convertView==null) { convertView = layoutInflater.inflate(R.layout.item_adapterable_my_profile_comment, parent, false); } iMyCommentsCommentedProductImage = (ImageButton) convertView.findViewById(R.id.iMyCommentsCommentedProductImage); tvMyCommentsCommentedItemName = (TextView) convertView.findViewById(R.id.tvMyCommentsCommentedItemName); tvMyCommentsCommentedItemDate = (TextView) convertView.findViewById(R.id.tvMyCommentsCommentedItemDate); tvMyCommentsCommentedItemContent = (TextView) convertView.findViewById(R.id.tvMyCommentsCommentedItemContent); tvMyCommentsCommentedItemRating = (TextView) convertView.findViewById(R.id.tvMyCommentsCommentedItemRating); bMyCommentsEditComment = (Button) convertView.findViewById(R.id.bMyCommentsEditComment); bMyCommentsDeleteComment = (Button) convertView.findViewById(R.id.bMyCommentsDeleteComment); tvMyCommentsCommentedItemName.setText(itemNames.get(position)); tvMyCommentsCommentedItemDate.setText(itemCommentDates.get(position)); tvMyCommentsCommentedItemRating.setText(String.valueOf(itemRatings.get(position))); tvMyCommentsCommentedItemContent.setText(itemComments.get(position)); return convertView; }}

I need to have about 20 of these and put them all in a list view (the activity feed of my app), depending on what activities have recently occurred in my app. Each different activity has a separate XML defined with as much view elements in it, as this one, all doing and displaying different stuff.

Now, If I was to use the suggested approach,

@Override public View getView(int position, View convertView, ViewGroup parent) { ViewHolder holder = null; int type = getItemViewType(position); Log.v(LOG_TAG, "getView " + position + " " + convertView + " type = " + type); if (convertView == null) { holder = new ViewHolder(); switch (type) { case TYPE_ITEM: convertView = mInflater.inflate(R.layout.item, null); holder.textView = (TextView)convertView.findViewById(R.id.text); break; case TYPE_SEPARATOR: convertView = mInflater.inflate(R.layout.separator, null); holder.textView = (TextView)convertView.findViewById(R.id.textSeparator); break; default: break; } convertView.setTag(holder); } else { holder = (ViewHolder)convertView.getTag(); } holder.textView.setText(mData.get(position)); return convertView; }

Looks simple, doesnt it? But in my case, I should have 20 different cases in the SWITCH and each one should have 20-30 rows of code in it. I dont want to implement it this way. Sadly, I have no clue of what to do...

View the original article here