Tuesday, April 20, 2010

Gridview Sorting Problem

When you bind a List to a Gridview, you can not sort it, because your data source is not a Dataset, Dataview or DataTable. So you should convert your data source (List) to a Dataset for example.


You can do it such as following code:


    1 public static DataSet dsSearch()
    2 {
    3 
    4     DataTable dtTelcoCenter = new DataTable();
    5     dtTelcoCenter.Columns.Add("Id", typeof(int));
    6     dtTelcoCenter.Columns.Add("City", typeof(string));
    7     dtTelcoCenter.Columns.Add("Capacity", typeof(int));
    8 
    9     IList<Telco> telcoCenter = Search(centerName, Prefix);// Search method return a list
   10 
   11 
   12     foreach (Telco telco in telcoCenter)
   13     {
   14         DataRow rowTelcoCenter = dtTelcoCenter.NewRow();
   15         rowTelcoCenter["Id"] = telco.Id;
   16         rowTelcoCenter["City"] = telco.City;
   17         rowTelcoCenter["Capacity"] = telco.Capacity;
   18         dtTelcoCenter.Rows.Add(rowTelcoCenter);
   19     }
   20     DataSet dsTelcoCenter = new DataSet();
   21     dsTelcoCenter.Tables.Add(dtTelcoCenter);
   22     return dsTelcoCenter;
   23 }

You can bind this Dataset to your Gridview data source

Rows to single column in Sql

Sample table as Cities in rows is convert to single column

declare @retstr varchar(8000)  
 select Top 5 @retstr =  COALESCE(@retstr + ';','') + City   
from State  
print @retstr  

Monday, January 04, 2010

How can you save a MS Office file into a database?

If you want to save a MS Office document such as 
Word doc in a database, you can do it with the 
following VB.net code:

    Dim vStream As New FileStream("C:\Alex.doc", FileMode.Open) 
    Dim vLen As Integer = New FileInfo("C:\Alex.doc").Length
    Dim vBlob(vLen) As Byte
    Dim n As Integer = vStream.Read(vBlob, 0, vLen)
    Dim vSql As String = 
"INSERT INTO tBlobTable (vId,vBlob) VALUES (1,@vBlob)" 
    Dim sql As New SqlCommand(vSql, vConn)
    Dim sqlPar As New SqlParameter("@vBlob", SqlDbType.Image)
    sqlPar.Value = vBlob
    sql.Parameters.Add(sqlPar) 

I hope you would find it useful.