Monthly Archives: October 2011

Restoring SQL Database from Remote Computer

You have backed up your database on your remote server into .bak file. You moved the .bak file to your local system and want to restore the database and it does not work!

Well here are some of the things you need to be aware of.

  • You can back up a database and restored as a different database name on one computer
  • You can not back a database on one computer and restore it on another using a different name.

The second was the scenario that I was trying to do as I did not want to overrwite my existing database. This is probably a security issue when you import from one machine to the other. You can not change the database name.

Scenario

You are working on a development machine. You made some changes to the database. Meantime, the production machine got new data. You want to import the new data into the existing one.

If you simply import the database, you will loose all your changes (stored procedures, new column etc).  What you really want to do is import just one or two tables which has the new data.

To make things complex, you can not import individual table because the data in those table is somewhat corrupt (this was the case with me).

Solution

As earlier pointed out, it would be nice if we can import the .bak file into a new database on local system but we can’t. The only way to do it is update the current database by right clicking on the database->Task->Restore->Database. You can not change the name of the database, if you do you will get some error.

What you can do is backup you existing database and you can restored it with different database name. This is what you should do first. Back it up and restore it with a different name. That way the changes you made are their in a new database which you can always refer too.

Note that the database permission do not play a role when you import/export from remote computer. Also make sure you give everyone the permission to read/write and execute for this to work.

References

This tutorial is designed for Microsoft SQL Server 2005. It should be valid for SQL Server 2008.

Keywords

How to backup and restore database

How to restored database across servers

How to backup database across different machines

SQL DISTINCT usage

Note: SQL DISTINCT is available only in MS SQL not MySQL

Select Distinct * from table

removed duplicates rows not duplicate column. DISTINCT works on rows not column. If you join two tables lets say

Doctors                                                             Appointment (Patients)

doc_id | Doc_Name                             Patient Name, doc_id

If you left join on doc_id, it will produce a table with doctor name repeated more than once for each appointment he has. Using distinct will not solve the problem because the rows are already distinct different (Each doctor had a different appointment) so in each row the patient name is different in front of the same doctor.

Sort template field in gridView

If you add a template field to your gridview, you will find that the default sort function do not work to sort your gridview based on the template field. It is actually rather easier to implement by adding and event, writing some code and adding a little bit of markup. Here is how

1. First change the markup of your template field.

Inside your template field, add this markup. You can do that in Code Edit or Design Mode.

<HeaderTemplate>
<asp:LinkButton ID=”LinkButton2″ runat=”server”  CommandName=”Sort” CommandArgument=”myDBfield” >Image</asp:LinkButton>
</HeaderTemplate>

2. Add RowCommand event to the gridview

Select GridView, click properties (F4) and go to events tab and click on RowCommand event. This will add yourgridviewName_RowCommand event.

3. Put this code in the event handler.

protected void gvCustomerFind_RowCommand(object sender, GridViewCommandEventArgs e)
{
if (e.CommandName.Equals(“Sort”))
{
if (ViewState[“SortExpression”] != null)
{
if (this.ViewState[“SortExpression”].ToString() == e.CommandArgument.ToString())
{
if (ViewState[“SortOrder”].ToString() == “ASC”)
ViewState[“SortOrder”] = “DESC”;
else
ViewState[“SortOrder”] = “ASC”;
}
else
{
ViewState[“SortOrder”] = “ASC”;
ViewState[“SortExpression”] = e.CommandArgument.ToString();
}
}
else
{
ViewState[“SortExpression”] = e.CommandArgument.ToString();
ViewState[“SortOrder”] = “ASC”;
}
}
gvCustomerFind.DataBind(); // Bind your gridview *important*
}

FindControl in Master Page

Accessing a control  can be a problem in codebehind in ASP.NET. However you will often need to access the control  because you want to change the text property of a textbox or changing certain value. If the control is directly laying in the page itself, then you can directly access it via mycontrolID But if the control is sitting inside a gridview or formview or panel, you can not access that control directly. In fact that control is invisible to codebehind file. To access that you have to use GridView.FindControl(controlname) to find the control and only then you can change its value, provided it did finds the control. If you do not know the exact structure of where the control is, you are at a disadvantage. In that case, there is a user define function that you have to use to find your control. One such method is this

Copy this code the code behind at the bottom of the page and now this function will be available to all the functions in your codebehind.

To use the funcation, use this syntax

Note here that mygridview is the top level control in the page that is recognized on the page. It will be in most case the container object that contains your particular control.  If your control is other than TextBox, you will have to typecast it as that type of control.

Caution

Caution need to be made

  • ASP:Textbox: It must be ASP.NET control. FindControl does not work with regular HTML controls
  • SPAN:Some html tags for example SPAN might have some trouble with it too. If a control is not supposed to have childeren, FindControlRecursive will probably not find it. span.innerthtml did not work in my case.
  • Table: I am not sure if it is suppose to work with table. It did not work for me even with <asp:table …> Link. TableRow, TableCell might be needed, not sure.