Dynamic Sql-srever - table column header name

Dynamic Sql-srever - table column header name
 
declare @colnm varchar(20)
declare @strsql varchar(2000)
set @colnm='1625195'
--set @strsql='select ['+  @colnm + '] from ia_book1 where ['+  @colnm + '] =''1625195'+''+char(39)
set @strsql='select * from ia_book1 where ['+  @colnm + '] ='+char(39)+@colnm +char(39)
Print @strsql
exec(@strsql)

JD Edwards IB Batch Error: The AA ledger type for 1/22/2011 , document xxxxxx is out of balance by xxxx.xx .

In JD Edwards batch fixing is a challance for each batch and each time is different. For same error you can fix different wy I fixed is IB batch error as following:-

1. Took backup for batch .

2. using sql updated F0911 all 'P' to blank in GLPOST field.

3. Ran batch post program R09801 and after that batch posted correctly.

4. Ran re-post program(R099102) for all related account (GLAID) in this batch.

 

 

 

docs

Database:
1. vdcsvfl structure should be as same as table name: Reporting (Done).
2. All final data must be inserted into table name: Reporting.
(Done with new date field)
3. All SQL Statement must be converted into Stored Procedure.
(Done)
Web:
1. Add CheckAll Checkbox so once it is checked, all checkboxes will be
checked.(Done)
2. Add AllowSorted in Gridview equal to true.(Done)
3. The Submit button <<next the browse>> should not be shown until
the browse textbox has value. (Not yet)
5. Delete proddta.aspx. We don't need it. Reporting.aspx will show all
information from Reporting table.
(done) in case you want to see Data for reporting table I am not
deleting this program. If you want to use you can use.
6. Validate the value when it is updated. At least check if it is empty.(Done)
Checking if file is .csv or not. If no file selected won't do anything.
7. <<A favor>> Please add textbox and it is a date field. I need this
date field saved into Reporting Table.
Done.
A date field called ReportingDate will be created in the Reporting table.

DateTime formate C#,vb asp.net 4.0

DateTime.Now;	1/27/2011 11:22:45 AM
DateTime.Now.ToString();	1/27/2011 11:22:45 AM
DateTime.Now.ToShortTimeString()	11:22 AM
DateTime.Now.ToShortDateString()	1/27/2011
DateTime.Now.ToLongTimeString()	11:22:45 AM
DateTime.Now.ToLongDateString()	Thursday, January 27, 2011

---

DateTime.Now.ToString("d")		1/27/2011
DateTime.Now.ToString("D")		Thursday, January 27, 2011
DateTime.Now.ToString("f")		Thursday, January 27, 2011 11:22 AM
DateTime.Now.ToString("F")		Thursday, January 27, 2011 11:22:45 AM
DateTime.Now.ToString("g")		1/27/2011 11:22 AM
DateTime.Now.ToString("G")		1/27/2011 11:22:45 AM
DateTime.Now.ToString("m")		January 27
DateTime.Now.ToString("r")		Thu, 27 Jan 2011 11:22:45 GMT
DateTime.Now.ToString("s")		2011-01-27T11:22:45
DateTime.Now.ToString("t")		11:22 AM
DateTime.Now.ToString("T")		11:22:45 AM
DateTime.Now.ToString("u")		2011-01-27 11:22:45Z
DateTime.Now.ToString("U")		Thursday, January 27, 2011 5:22:45 PM
DateTime.Now.ToString("y")		January, 2011
DateTime.Now.ToString("dddd, MMMM dd yyyy")		Thursday, January 27 2011
DateTime.Now.ToString("ddd, MMM d "'"yy")		Thu, Jan 27 '11
DateTime.Now.ToString("dddd, MMMM dd")		Thursday, January 27
DateTime.Now.ToString("M/yy")		1/11
DateTime.Now.ToString("dd-MM-yy")		27-01-11

 

 

 

http://www.csharpfriends.com/demos/system.datetime.aspx

checkbox c# check all

http://programming.top54u.com/post/ASP-Net-GridView-Checkbox-Select-All-using-C-sharp.aspx

csv file upload csv ,net c#

Csv file upload csv ,net c#, validate file before upload

 

 

if (FileUpLoad1.HasFile)

{

string filePath = FileUpLoad1.PostedFile.FileName;

string filenameStor = FileUpLoad1.FileName;

string filenameType = Path.GetExtension(FileUpLoad1.PostedFile.FileName);

if (filenameType.ToString().Trim().ToLower() == ".csv")

{

FileUpLoad1.SaveAs(@"c:\\sites\\" + "Book1.csv");

lblStatus.Text = "File Uploaded : " + FileUpLoad1.PostedFile.FileName;

insFileName();

SqlDataSource1.SelectCommand = "SELECT * FROM vdcsvfl";

gvid.Visible = true;

Button1.Visible = true;

}

else

{

lblStatus.Text =

"Invalid File Type...";

}

}

else

{

lblStatus.Text =

"No File Uploaded.";

gvid.Visible =

true;

Button1.Visible =

true;

}

}

void insFileName()

{

SqlConnection myConnection = new SqlConnection(ConnectionString);

string strSQL;

strSQL =

"insbulkcsvP";

SqlCommand cmd = new SqlCommand(strSQL, myConnection);

cmd.CommandType =

CommandType.StoredProcedure;

　myConnection.Open();

cmd.ExecuteNonQuery();

myConnection.Close();

lblStatus.Text =

"Added..";

}

 

 

 

ALTER

proc [dbo].[insbulkcsvP] as

delete

from dbo.vdcsvfl

Alter

Table vdcsvfl Drop Column id

--ALTER TABLE a DROP COLUMN y

BULK

INSERT dbo.vdcsvfl

FROM

'c:\sites\Book1.csv'

WITH

(

FIELDTERMINATOR

= ',',

ROWTERMINATOR

= '\n'

)

delete

from dbo.vdcsvfl where video_id='video_id'

Alter

Table vdcsvfl Add id int identity(1,1) --primary key

Find & Replace Sql-server update

UPDATE dbo.states
SET    adds = replace([city], 'Bihar', 'Jharkhand')
WHERE  adds LIKE 'Bihar%';

Godaddy Hosting Account's Absolute Path

To Find Your Absolute Hosting Path

1. Log in to your Account Manager.
2. From the Products section, click Web Hosting.
3. Next to the hosting account you want to use, click Launch.

In the Server section, your hosting account's Absolute Hosting Path displays.

 

http://help.godaddy.com/article/58

Alter table add identity column

If there is exising filed you want to change

Alter

Table temp_inscsv Drop Column id

 

if not want to add a brand new use pnly following:-

Alter Table temp_inscsv Add id int identity(1,1) primary key

'1,2,3,4' sql-server - Split Function in Sql Server to break Comma-Separated Strings into Table

CREATE FUNCTION dbo.Split(@String varchar(8000), @Delimiter char(1))
returns @temptable TABLE (items varchar(8000))
as
begin
declare @idx int
declare @slice varchar(8000)

select @idx = 1
if len(@String)<1 or @String is null return

while @idx!= 0
begin
set @idx = charindex(@Delimiter,@String)
if @idx!=0
set @slice = left(@String,@idx - 1)
else
set @slice = @String

if(len(@slice)>0)
insert into @temptable(Items) values(@slice)

set @String = right(@String,len(@String) - @idx)
if len(@String) = 0 break
end
return
end


select top 10 * from dbo.split('Chennai,Bangalore,Mumbai',',')
http://www.logiclabz.com/sql-server/split-function-in-sql-server-to-break-comma-separated-strings-into-table.aspx

upload excel file and upload to sql-server c# asp.net 4.0

 
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default2.aspx.cs" Inherits="Default2" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div> <asp:FileUpLoad id="FileUpLoad1" runat="server" /><asp:Button id="UploadBtn" Text="Upload File" OnClick="UploadBtn_Click" runat="server" Width="105px" CssClass="but" />

<br />
<br />
<br />
<br />
<asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>

</div>
</form>
</body>
</html>
 
code behind
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.OleDb;
using System.Data.Common;
using System.Data.SqlClient;
using System.Configuration;
public partial class Default2 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}

protected void UploadBtn_Click(object sender, EventArgs e)
{
if (FileUpLoad1.HasFile)
{
string filePath = FileUpLoad1.PostedFile.FileName;
string filenameStor = FileUpLoad1.FileName;
FileUpLoad1.SaveAs(@"c:\\" + "Book1.xls");
Label1.Text = "File Uploaded : " + FileUpLoad1.PostedFile.FileName;
datatosql();
}
else
{
Label1.Text = "No File Uploaded.";
}


}
void datatosql()
{
string strConn = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\Book1.xls;Extended Properties=""Excel 8.0;HDR=NO;IMEX=1""";
using (OleDbConnection connection = new OleDbConnection(strConn))
{
OleDbCommand command = new OleDbCommand("Select * FROM [Sheet1$]", connection);
connection.Open();
 
using (DbDataReader dr = command.ExecuteReader())
{
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(ConnectionString))
{
bulkCopy.DestinationTableName = "testexceldata";
bulkCopy.WriteToServer(dr);
}
}
}
}
 
private string ConnectionString
{
get
{
string connectionString = ConfigurationManager.ConnectionStrings["smdbConnectionString"].ConnectionString;
return connectionString;
}
}
}

Display Excel data in Gridview asp.net c#

string strConn = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\Book1.xls;Extended Properties=""Excel 8.0;HDR=NO;IMEX=1""";

OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn);

DataSet myDataSet = new DataSet();

myCommand.Fill(myDataSet,

"ExcelInfo");

GridView1.DataSource = myDataSet.Tables[

"ExcelInfo"].DefaultView;

GridView1.DataBind();

The 'Microsoft.Jet. OLEDB.4.0' provider is not registered on the local machine.- Error 64 bit 4.0

 You will have to run asp.net in 32 bit environment
Run following command
cscript c:\inetpub\adminscripts\adsutil.vbs SET W3SVC/AppPools/Enable32bitAppOnWin64 0
c:\windows\Microsoft.NET\Framework64\v4.0.30319\aspnet_regiis.exe -i

Word or PDF into Database sql-server c#

Word or PDF into Database sql-server c#

 

 private int SaveToDB(string imgName, byte[] imgbin, string imgcontenttype)
{

 

SqlConnection connection = new SqlConnection(ConfigurationSettings.AppSettings["DSN111"]);
SqlCommand command = new SqlCommand( "INSERT INTO Image (img_name,img_data,img_contenttype) VALUES ( @img_name, @img_data,@img_contenttype )", connection );

SqlParameter param0 = new SqlParameter( "@img_name", SqlDbType.VarChar,50 );
param0.Value = imgName;
command.Parameters.Add( param0 );

SqlParameter param1 = new SqlParameter( "@img_data", SqlDbType.Image );
param1.Value = imgbin;
command.Parameters.Add( param1 );

SqlParameter param2 = new SqlParameter( "@img_contenttype", SqlDbType.VarChar,50 );
param2.Value = imgcontenttype;
command.Parameters.Add( param2 );

connection.Open();
int noRowsAffected = command.ExecuteNonQuery();
connection.Close();

return noRowsAffected;

}

 

connect to ftp from browser with port

ftp://user:password@host:port/path

Read & create tabs excel file C#

The technique that is most frequently used to transfer data to an Excel workbook is Automation. With Automation, you can call methods and properties that are specific to Excel tasks. Automation gives you the greatest flexibility for specifying the location of your data in the workbook, formatting the workbook, and making various settings at run-time.
http://support.microsoft.com/kb/306023

HyperLinkField which are not part of your Gridview DataSource

How to pass external values with GridView HyperLinkField which are not part of your Gridview DataSource member ?

 

DataNavigationUrlField use only those fields as parameter which are the part of GridView DataSource. Now the problem comes when you want to pass some other variables as parameter which are not part of the DataSource. As shown in below image we are passing EmpID and ParentId as argument and this two field are the data member of GridView DataSource.

http://dailydotnettips.com/2011/01/15/how-to-pass-external-values-with-gridview-hyperlinkfield-which-are-not-part-of-your-gridview-datasource-member/

 

To change the path for linked tables in access

 To change the path for a set of linked tables in access database

1. Open the database that contains links to tables.
2. On the Tools menu, point to Database Utilities, and then click Linked Table Manager.
3. Select the Always prompt for new location check box.
4. Select the check box for the tables whose links you want to change, and then click OK.
5. In the Select New Location of <table name> dialog box, specify the new location, click Open, and then click OK.

WPF Two-way Databinding in ASP.NET

Bringing WPF like declarative data binding to ASP.NET Web Forms to enable declarative two-way data binding to any object whilst opening up MVVM UI development.

http://www.codeproject.com/KB/aspnet/WPFDataBindingInASPX.aspx

Using Razor Pages with WebForms Master Pages

There's been some recent chatter about mixing ASP.NET MVC with WebForms, which is something I'm all too familiar with. This setup works just fine, but what if you wanted to use the new Razor view engine within ASP.NET MVC? A bit of backstory.
 On a secondary project that I work on within Microsoft, we were given the ability to rewrite the entire UI of the application in ASP.NET MVC. At that time, the first beta of MVC3 was rolling out and we decided to take our first plunge and use the Razor view engine. Unfortunately, the common UI layout that is shared across multiple properties that we had to use was WebForms based. Our attempts to convince the developers of the common UI components to support more of a modularized infrastructure failed for any foreseeable future, so we were left with the conundrum of figuring out an intermediary solution.

http://www.eworldui.net/blog/post/2011/01/07/Using-Razor-Pages-with-WebForms-Master-Pages.aspx

Building an HTML5 App with ASP.NET

The application takes advantage of HTML5 local storage to store all of the reference entries on the local browser. IE 8, Chrome 8, Firefox 3.6, and Safari 5 all support local storage.

When you open the application for the first time, all of the reference entries are transferred to the browser. The data is stored persistently. Even if you shutdown your computer and return to the application many days later, the data does not need to be transferred again.

http://stephenwalther.com/blog/archive/2011/01/11/building-an-html5-app-with-asp-net.aspx

sql-server 2008 express how to save Import and Export Wizard

In SQL Server Express, Web, or Workgroup, you can run the package that the Import and Export Wizard creates, but cannot save it. To save packages that the wizard creates, you must upgrade to SQL Server Standard, Enterprise, Developer or Evaluation.

The settings saved on this computer do not match the requirements of the wireless network.

 

in vista you go to network and sharing center then click manage networks. delete the one your using coz the settings are different. you  maybe changed your router settings . when your done deleteing it, find it in the wireless list, type in the password and you wil be able to connect now.

Cross tab query in sql-server sample

 

declare

@drdate as varchar(15)

declare

@drweek as varchar(15)

declare

@fdate1 as varchar(15)

declare

@fdate2 as varchar(15)

declare

@fdate3 as varchar(15)

declare

@fdate4 as varchar(15)

declare

@fdate5 as varchar(15)

declare

@fdate6 as varchar(15)

declare

@fdate7 as varchar(15)

declare

@fdate8 as varchar(15)

declare

@fdate9 as varchar(15)

declare

@SQL varchar (max)

set

@drdate='1/3/2010'

select

@drweek=drweek from dbo.DateRef where drdate=@drdate

set

@fdate1=convert(varchar(10), cast(@drweek as datetime),101)

set

@fdate2=convert(varchar(10),dateadd(day,1,@fdate1),101)

set

@fdate3=convert(varchar(10),dateadd(day,1,@fdate2),101)

set

@fdate4=convert(varchar(10),dateadd(day,1,@fdate3),101)

set

@fdate5=convert(varchar(10),dateadd(day,1,@fdate4),101)

set

@fdate6=convert(varchar(10),dateadd(day,1,@fdate5),101)

set

@fdate7=convert(varchar(10),dateadd(day,1,@fdate6),101)

set

@fdate8=convert(varchar(10),dateadd(day,1,@fdate7),101)

set

@fdate9=convert(varchar(10),dateadd(day,1,@fdate8),101)

print

@fdate9

print

@fdate1

select

distinct m.FICLBNO,m.FIUPC,m.prscitemno,m.prsamsitemno,m.OwnerName,m.HubName,

isnull

(m1.FIQTY,0) Q1 ,isnull(m1.FICOST,0) C1,isnull(m1.FISALES,0) T1,

isnull

(m2.FIQTY,0) Q2,isnull(m2.FICOST,0) C2,isnull(m2.FISALES,0)T2,

isnull

(m3.FIQTY,0) Q3,isnull(m3.FICOST,0) C3,isnull(m3.FISALES,0)T3,

isnull

(m4.FIQTY,0) Q4,isnull(m4.FICOST,0) C4,isnull(m4.FISALES,0)T4,

isnull

(m5.FIQTY,0) Q5,isnull(m5.FICOST,0) C5,isnull(m5.FISALES,0)T5,

isnull

(m6.FIQTY,0) Q6,isnull(m6.FICOST,0) C6,isnull(m6.FISALES,0)T6,

isnull

(m7.FIQTY,0) Q7,isnull(m7.FICOST,0) C7,isnull(m7.FISALES,0)T7,

isnull

(m8.FIQTY,0) Q8,isnull(m8.FICOST,0) C8,isnull(m8.FISALES,0)T8,

isnull

(m9.FIQTY,0) Q9,isnull(m9.FICOST,0) C9,isnull(m9.FISALES,0)T9

from

smrsalesrep_m m

left

outer join smrsalesrep m1 on m.FICLBNO=m1.FICLBNO and m.FIUPC=m1.FIUPC and m.prscitemno=m1.prscitemno

and

m.prsamsitemno=m1.prsamsitemno and m.OwnerName=m1.OwnerName and m.HubName=m1.HubName and m1.FISALDAT=@fdate1

left

outer join smrsalesrep m2 on m.FICLBNO=m2.FICLBNO and m.FIUPC=m2.FIUPC and m.prscitemno=m2.prscitemno

and

m.prsamsitemno=m2.prsamsitemno and m.OwnerName=m2.OwnerName and m.HubName=m2.HubName and m2.FISALDAT=@fdate2

left

outer join smrsalesrep m3 on m.FICLBNO=m3.FICLBNO and m.FIUPC=m3.FIUPC and m.prscitemno=m3.prscitemno

and

m.prsamsitemno=m3.prsamsitemno and m.OwnerName=m3.OwnerName and m.HubName=m3.HubName and m3.FISALDAT=@fdate3

left

outer join smrsalesrep m4 on m.FICLBNO=m4.FICLBNO and m.FIUPC=m4.FIUPC and m.prscitemno=m4.prscitemno

and

m.prsamsitemno=m4.prsamsitemno and m.OwnerName=m4.OwnerName and m.HubName=m4.HubName and m4.FISALDAT=@fdate4

left

outer join smrsalesrep m5 on m.FICLBNO=m5.FICLBNO and m.FIUPC=m5.FIUPC and m.prscitemno=m5.prscitemno

and

m.prsamsitemno=m5.prsamsitemno and m.OwnerName=m5.OwnerName and m.HubName=m5.HubName and m5.FISALDAT=@fdate5

left

outer join smrsalesrep m6 on m.FICLBNO=m6.FICLBNO and m.FIUPC=m6.FIUPC and m.prscitemno=m6.prscitemno

and

m.prsamsitemno=m6.prsamsitemno and m.OwnerName=m6.OwnerName and m.HubName=m6.HubName and m6.FISALDAT=@fdate6

left

outer join smrsalesrep m7 on m.FICLBNO=m7.FICLBNO and m.FIUPC=m7.FIUPC and m.prscitemno=m7.prscitemno

and

m.prsamsitemno=m7.prsamsitemno and m.OwnerName=m7.OwnerName and m.HubName=m7.HubName and m7.FISALDAT=@fdate7

left

outer join smrsalesrep m8 on m.FICLBNO=m8.FICLBNO and m.FIUPC=m8.FIUPC and m.prscitemno=m8.prscitemno

and

m.prsamsitemno=m8.prsamsitemno and m.OwnerName=m8.OwnerName and m.HubName=m8.HubName and m8.FISALDAT=@fdate8

left

outer join smrsalesrep m9 on m.FICLBNO=m9.FICLBNO and m.FIUPC=m9.FIUPC and m.prscitemno=m9.prscitemno

and

m.prsamsitemno=m9.prsamsitemno and m.OwnerName=m9.OwnerName and m.HubName=m9.HubName and m9.FISALDAT=@fdate9