This statement is extracting a list to excel which works fine
string sql = "select wo.email, wo.productid, wo.variantid ";
sql += "from woeosemails wo ";
sql += "order by email, productid ";
string attachment = "attachment; filename=EmailList.csv";
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.ClearHeaders();
HttpContext.Current.Response.ClearContent();
HttpContext.Current.Response.AddHeader("content-disposition", attachment);
HttpContext.Current.Response.ContentType = "text/csv";
HttpContext.Current.Response.AddHeader("Pragma", "public");
HttpContext.Current.Response.Write("email,productid,variantid");
HttpContext.Current.Response.Write(Environment.NewLine);
using (SqlConnection conn = new SqlConnection(DB.GetDBConn()))
{
conn.Open();
using (IDataReader NotifyReader = DB.GetRS(sql, conn))
{
while (NotifyReader.Read())
{
string email = DB.RSField(NotifyReader, "email");
int productid = DB.RSFieldInt(NotifyReader, "productid");
int variantid = DB.RSFieldInt(NotifyReader, "variantid");
email = email.Replace("\"","\"\"");
HttpContext.Current.Response.Write("\"" + email + "\"," + productid.ToString() + "," + variantid.ToString());
HttpContext.Current.Response.Write(Environment.NewLine);
}
}
conn.Close();
}
HttpContext.Current.Response.End();
I've tried adding a variety and I thought that this would work
I want to add two columns from another sql table, these are Name and SKU. Any ideas on how I can modify the first part of this code, I have tried joining the tables but nothing seems to work. The closest I have is modifying the first part to this
string sql = "select wo.email, wo.productid, wo.variantid, p.Name pname, p.Name psku, ";
sql += "from woeosemails wo ";
sql += "join Product p with (nolock) on p.ProductID = wo.productid ";
sql += "order by email, productid ";
Any help would be great
First of all you need to put alias to your order by properties and remove the comma at first line at the end. Try following:
string sql = "select wo.email, wo.productid, wo.variantid, p.Name pname, p.Name psku ";
sql += "from woeosemails wo ";
sql += "join Product p with (nolock) on p.ProductID = wo.productid ";
sql += "order by wo.email, wo.productid ";