AJAY HELP: 11/01/2011

Nov 28, 2011

Linking Sql Server 2005 to Oracle

Here is a quick guide to setting up linked servers so you can query Oracle through Sql Server. For my testing of this I used Sql Server Express 2005, and Oracle 9.2. This all uses simple commands that can be run via Query Analyzer or Query Tool.

First, if you've been doing this and having issues, you need to clear out any of your linked server's logins:

sp_droplinkedsrvlogin @rmtsrvname = 'LINKED_ORA', @locallogin = null

Next, drop the server

EXEC sp_dropserver LINKED_ORA

Ok, now let's add the linked server to Sql Server. The linked server will be called LINKED_ORA. Once this is done you should see this listed in SS Management Studio under Server Objects->Linked Servers

-- add server
EXEC sp_addlinkedserver
@server= 'LINKED_ORA'
, @provider='MSDASQL'
, @srvproduct='Oracle'
, @datasrc= 'myOracle'

@server - this is the name to use. It can be anything
@provider - can be either MSDASQL and MSDAORA. Both seem to both work fine. There is talk of another provider from Oracle, but I have not gotten this one to work.
@srvproduct - must be "Oracle". I do not know why this matters
@datasrc - this is a DSN that you have set up using Oracle's .NET manager

Now, the Oracle system does not implicitly let Sql Server connect. You need to add a login for this linked server to use.

-- add login - the DSN is protected by a password
EXEC sp_addlinkedsrvlogin
@rmtsrvname='LINKED_ORA'
, @useself='false'
, @rmtuser='oracleUserId'
, @rmtpassword='oraclePassword'

Once this is set, then you can test using this command:

--verify tables OK
exec sp_tables_ex @table_server = 'LINKED_ORA', @table_schema='MySchema'

@table_schema is optional. If not provided, you will get a list of all tables in all schemas.

This will return information about the tables in the linked server. I use it as a basic diagnostic to verify that the linked server is working. if it's not, then I use the scripts above to delete the login and linked server, then re-create using other inputs.

Now, the big pain with Oracle is that you cannot just query into the database. I know, it is ridiculous, but I found that only OPENQUERY works. Here is how it looks:

SELECT * FROM OPENQUERY( LINKED_ORA, 'SELECT * FROM Exp.Proj')

But from there you can now do handy things in Sql Server. One of my favorites is to re-create the Oracle schema in a Sql Server database, then use INSERT to pull in all of the Oracle data. Then you can just work with Sql Server!

Nov 24, 2011

Upload file on fileUpload onchange event in asp.net

==================== ASPX code======================
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
        <div>
        <asp:FileUpload ID="FileUpload1" onchange="if (confirm('Upload ' + this.value + '?')) this.form.submit();" runat="server" />
            <br />

<asp:RegularExpressionValidator ID="rexp" runat="server" ControlToValidate="FileUpload1"
     ErrorMessage="Only .DAT,.PNG,.BMP,.JPEG,.XLSX,.DOCX,.PPTX,
.DXF,.DRW,.SLD,.TXT,.PPT,.XLS,.PPS,.DOC,
.PDF,.GIF,.JPG,.RTF"
     ValidationExpression="(.*\.([Gg][Ii][Ff])|.*\.([Jj][Pp][Gg])|.*\.([Bb][Mm][Pp])|.*\.([pP][nN][gG])|.*\.([xX][lL][sS][xX])|.*\.([dD][oO][cC][xX])|.*\.([pP][pP][tT][xX])|.*\.([dD][xX][fF])|.*\.([dD][rR][wW])|.*\.([sS][lL][dD])|.*\.([tT][xX][tT])|.*\.([pP][pP][tT])|.*\.([xX][lL][sS])|.*\.([pP][pP][sS])|.*\.([dD][oO][cC])|.*\.([pP][dD][fF])|.*\.([dD][aA][tT])|.*\.([rR][tT][fF])$)"></asp:RegularExpressionValidator>

            <asp:Label ID="lblFileSize" runat="server" Text="Label"></asp:Label></div>
    </form>
</body>
</html>
==================================================

====================On page Load======================
protected void Page_Load(object sender, EventArgs e)
    {
        if (Page.IsPostBack)
        {
            if (FileUpload1.HasFile)
            {
                string strFileName, strFileExtension;
                strFileName = FileUpload1.FileName;
                strFileExtension = strFileName.Substring(strFileName.LastIndexOf('.') + 1);
                lblFileSize.Text = FileUpload1.PostedFile.ContentLength.ToString();
                string strSavedFilePath = Server.MapPath("~//CTD_Files");
                strFileName = strFileName.Substring(0, strFileName.LastIndexOf('.')) + DateTime.Now.ToString("ddMMyyyyhhmmss") + "." + strFileExtension.Trim();
                strSavedFilePath += "\\" + strFileName;
                FileUpload1.PostedFile.SaveAs(strSavedFilePath);
            }
        }
    }

====================================================

Nov 22, 2011

How to give Link for Download or code for uploding and downloading file.

=========================== Upload Code ============================

String strFileName = "", strFileExtension, strSavedFilePath;

            if (FileUploadCtd.HasFile)
            {
                strFileName = FileUploadCtd.FileName;
                strFileExtension = strFileName.Substring(strFileName.LastIndexOf('.') + 1);
                //if (strFileExtension.ToUpper() != "XLS")
                //{
                //    ClientScript.RegisterStartupScript(Page.GetType(), "", "<script>alert('Select Excel File Only.')</script>");
                //    return;
                //}
                strSavedFilePath = Server.MapPath("../CTD_Files");
                if (!Directory.Exists(strSavedFilePath))
                {
                    Directory.CreateDirectory(strSavedFilePath);
                }
                strFileName = strFileName.Substring(0, strFileName.LastIndexOf('.')) + DateTime.Now.ToString("ddMMyyyyhhmmss") + "." + strFileExtension.Trim();
                //strSavedFilePath += "\\" + strFileName.Substring(0, strFileName.LastIndexOf('.')) + DateTime.Now.ToString("ddMMyyyyhhmmss") + "." + strFileExtension.Trim();
                strSavedFilePath += "\\" + strFileName;
                FileUploadCtd.PostedFile.SaveAs(strSavedFilePath);

                objDesign._strvCTDPath = strFileName; // save in DB table.
                lblCTDLink.Text = strFileName;
            }
            else
            {
                objDesign._strvCTDPath = "";
                lblCTDLink.Text = "";
            }
            // CTD file uploading end Here

===============================================================

=========================On Page Load (Data Load) ====================

// get file name from Db
lblCTDLink.Text = dtItemData.Rows[0]["vCTDPath"].ToString();

================================================================

======================= On download link button click ====================

protected void linkCTD_Click(object sender, EventArgs e)
    {
        // code for show donload link
        if (lblCTDLink.Text.Trim() != "")
        {
            string strSavedFilePath;
            strSavedFilePath = Server.MapPath("../CTD_Files" + "\\" + lblCTDLink.Text);
            FileInfo fileInfo = new FileInfo(strSavedFilePath);

            if (fileInfo.Exists)
            {
                Response.Clear();
                Response.AddHeader("Content-Disposition", "attachment; filename=" + fileInfo.Name);
                Response.AddHeader("Content-Length", fileInfo.Length.ToString());
                Response.ContentType = "application/octet-stream";
                Response.Flush();
                Response.WriteFile(fileInfo.FullName);
            }
        }
        else
        {
            objFnLib.WebMessageBox("No CTD Path Present", this);
        }
    }

================================================================

Nov 16, 2011

How to Send value return from child form/page to parent form/page via window.open() in java script

------------------------------------------In Default4.aspx------------------------------------------------

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:TextBox ID="YourTextBox" runat="server"></asp:TextBox>
        <asp:Button ID="Button1" runat="server" Text="open" OnClientClick="openPopup();" /></div>
    </form>
</body>

<script type="text/javascript">

</script>
</html>
------------------------------------------------------------------------------------------------

------------------------------------------PopupPage.aspx----------------------------------
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:Button ID="Button1" runat="server" OnClientClick="closeWindow();" Text="Close" /></div>
    </form>
</body>

<script type="text/javascript">

</script>
</html>
-------------------------------------------------------------------------------------------------