How to copy a datatable to another specify column mapping

Hello,

Can any one show me how to copy the data in one datatable to another datatable? For example:

say from tableA .ColumnX to tableB.ColumnY

Thanks in advance for any suggestions.

You may wish to look into the bulk copy class featured in .Net 2.0 this can only be used with SQL Server. You can read morehere

You could also read from your current table and insert ino the new table using another command object. If you are unsure about doing this, reply to this thread and let me know which language you prefer and I'll post an example.

Good Luck

First make sure that the two columns are of the same size.Then may you use the sql statements--"Insert" to add the data column from Table B into Table A.

Maybe what I said was not correct,Please tell me.

how to convert to sqltype?

For example there is a DataTable dt and the first column in the firstrow is type of string, I want to assign it to SqlString as following:

SqlString s = (SqlString)dt.Rows[0][0];

The above code throws "Invalid cast" exception. My question is how tocast it to SqlString in this case? In general, how about cast othertypes? such as if the second column in the first row is type ofdecimal, then how to cast it to SqlDecimal?

SqlDecimal d ? dt.Rows[0][1]

Thanks.

I think you could do:

SqlString s = new SqlString(dt.Rows[0][0].ToString());

I'm curious why you're wanting to work with it as a SqlString?

Marcie

How to convert a GridView Column into Hyperlink fields?

I am trying to display records from an SQL database using an ASP.Net GridView control but I would like for one of the columns to be a Hyperlink or Hyperlink fields. Im using a DataTable to get the info from the DB.
For example:

protected void Page_Load(object sender, EventArgs e)
{
string queryString = "SELECT * FROM Candidates";
connection.SelectQuery = queryString;
myTable = connection.get_Connection();//recive the data as DataTable
//
//May be something here to convert a specific column to an hyperlink.
//
GridView1.DataSource = myTable;
GridView1.DataBind();
myTable.Dispose();
}

You can convert the BoundField to a TemplateField:

For example:

<asp:TemplateFieldHeaderText="colName"><ItemTemplate><asp:HyperLinkrunat="server"ID="HyperLink2"NavigateUrl='<%# "mypage.aspx?Sample_ID=" & Eval("Sample_ID")& "&Count_ID=" & Eval("Count_ID")%>'Text='<%# Eval("colName")%>'></asp:HyperLink></ItemTemplate></asp:TemplateField>

You can also use a HyperLinkField. The DataTextField property is what is displayed. The DataNavigateUrlFields property is for anything you might need to add to the DataNavigateUrlFormatString property.

<asp:HyperLinkFieldDataTextField="ColumnName"DataNavigateUrlFields="ColumnName2"DataNavigateUrlFormatString="~/mypage.aspx?UserID={0}"HeaderText="Name"></asp:HyperLinkField>

Thank you maspr, I tryed it before and it creates a new column just like the one that I want to convert to hyperlinks, and actually it works but I dont want to show two columns (one regular and the same one like hyperlinks). Do you know a way to hide or make invisible the regular one?

thanks so much for your help.

Thanks Limno I just tryed that right now. The problem is that if i want to add the<asp:TemplateField> and then the hyperlink inside of it, first I need to create a <column> </column> tag on the GridView, but if I create a column, there will be two identical columns but one of them will be hyperlink and the other not. Maybe the conversion must be on the .cs file, to force the DataColum or the cell to be hyperlinks.
Actually, it is a very easy job to do. From the samrt tag, you use the Edit columns, Highlight the column you want to convert, and you can see there is a link on the right side says: "Convert this field into TemplateField". Click on it and put the code i showed you. You can play with it to make it fit your need. By the way, the TemplateField is very useful, you will need it from time to time for formating you databound controls (gridview, detailsview...). Let me know if I can help more. Thanks.

You can do it in the rowdatabound event of gridview if you like.

If e.Row.RowType = DataControlRowType.DataRowThen
Dim hlAsNew HyperLink
hl.NavigateUrl = "yoururl"
hl.Text = e.Row.Cells(indexOfYourColumn).Text
hl.Target ="daview" 'target frame for url
e.Row.Cells(indexOfYourColumn).Controls.Add(hl)

end if

But limno my GridView doesn't know which table it will recive because it is been fill dinamically by one Class that I create to make the connection and a method there return tha table as DataTable. So I cant use the Edit column to converti it. My DataBase isn't inside the project, I connect to it using a connection string which is hide on my connection class. This is why I think that the code must be on the C# files and not on the aspx ones. Any suggestions will be welcome.

P.S. I will post next the connection class and the code which recive the table.

Thanks a lot to all
Neozaid

This example do the connection to an access DB, I will change it later for the SQL one.

//This is my default.cs file
public partialclass _Default : System.Web.UI.Page
{
private Connection connection =new Connection();
private SortClass sorting =new SortClass();
private DataTable myTable;
private GridViewSortEventArgs column;

public _Default()
{
myTable =null;
}

protected void Page_Load(object sender, EventArgs e)
{
string queryString ="SELECT FirstName ... FROM Candidates";
connection.SelectQuery = queryString;
myTable = connection.get_Connection();//DataTable
GridViewCandidates.DataSource = myTable;
GridViewCandidates.DataBind();
myTable.Dispose();
}
}

////////////////////////////The next is the connection class/////////////////////////////////////

using System.Data.OleDb;public class Connection{private string selectQueryString;private OleDbDataReader readerDataReader;private OleDbCommand commandCommand =new OleDbCommand();private DataTable myDataTable;public Connection()//initialize the query string { selectQueryString =""; }//Here the class get or set the query to look on the DB. //If it don't have any query it will use the default one "".public string SelectQuery {get {return selectQueryString; }set { selectQueryString =value; } }//Method: make the connection to DB, & return the desired table.public DataTable get_Connection() {string connectionString ="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\\DB.mdb;"; OleDbConnection connConnection =new OleDbConnection(); connConnection.ConnectionString = connectionString; commandCommand.CommandText = SelectQuery; commandCommand.CommandType = CommandType.Text; commandCommand.Connection = connConnection; commandCommand.Connection.Open(); readerDataReader = commandCommand.ExecuteReader(CommandBehavior.CloseConnection); myDataTable =new DataTable(); myDataTable.Load(readerDataReader);//Dispose the objects. myDataTable.Dispose(); commandCommand.Dispose(); connConnection.Dispose();return myDataTable; }}

Sincerely,
Neozaid