locked
How to add Parameters for DB2 Database behind code? RRS feed

  • Question

  • User-2055741253 posted

    Hello,

    To begin with, I wanted to mention that I am very new to C# and working with ASP.NET. I am interning this summer for an electric utility company with their .NET group and I've been asked to create a website that pulls the data from their IBM DB2 Database.

    Functionality of the website:

    I have added two Text Boxes, Drop Down List, and a submit button. I have also added the post back function behind the code, which seems to work fine. The problem that I seem to be having is when I add the parameters to the query.  Could someone please guide me in the right direction. I have provided the WebForm and the code behind below.

    WebForm:

    <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" Inherits="Momentary_App.UsingPostback" %>

    <!DOCTYPE html>

    <html xmlns="http://www.w3.org/1999/xhtml">

    <head runat="server">

    <title>DMS Analyst Tool</title>

    <link rel="stylesheet" type="text/css" href="stylesheet1.css" />

    </head>

    <body>

    <form id="form1" runat="server" class="form-wrapper" method ="get" style ="width: auto;">

    <div id = "wrapper2">

    <div class= "nav">

    <ul>

    <li><a href="WebForm1.aspx">Home</a></li>

    </ul>

    </div>

    </div>

    <div id="wrapper">

    <h1 id="header">DMS Outage Analyst Tool</h1>

    <div id="content">

    <br/><br />

    <fieldset style ="padding-left:15px;">

    <legend style ="color: #CC0000;"><b>Please fill in the required information:</b></legend> <br />

    <asp:Label ID="Label1" runat="server" Text="*Device Circuit" style="color: darkslategray; font-size:.90em;"></asp:Label>

    <asp:TextBox ID="TextBox1" required="" runat="server" style ="margin-top:5px; margin-left:6px; width:95px;"></asp:TextBox>

    <asp:Label ID="Label2" runat="server" Text="*Device District" style="color: darkslategray; font-size:.90em;"></asp:Label>

    <asp:TextBox ID="TextBox2" required="" runat="server" style="margin-top:5px; margin-left:6px; width:95px;"></asp:TextBox>

    <asp:Label ID="Label3" runat="server" Text="*Outage Type" style="color: darkslategray; font-size:.90em;"></asp:Label><br />

    <asp:DropDownList ID="DropDownList1" runat="server" style ="margin-top:5px; margin-left:6px; width:100px;">

    <asp:ListItem Text="Momentary" Value="M"></asp:ListItem>

    <asp:ListItem Text="NLOAD" Value="O"></asp:ListItem>

    <asp:ListItem Text="TRIP" Value="O"></asp:ListItem>

    </asp:DropDownList>

    <asp:Button Cssclass="button" style="margin-top: 25px;" ID="btnSubmit" Text="Submit" runat="server" OnClick="btnSubmit_Click"/>

    </fieldset>

    <br />

    <asp:GridView ID="GridView1" runat="server" CellPadding="1" ForeColor="#333333" GridLines="None">

    <AlternatingRowStyle BackColor="White" ForeColor="#284775" />

    <EditRowStyle BackColor="#999999" />

    <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />

    <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />

    <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />

    <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />

    <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />

    <SortedAscendingCellStyle BackColor="#E9E7E2" />

    <SortedAscendingHeaderStyle BackColor="#506C8C" />

    <SortedDescendingCellStyle BackColor="#FFFDF8" />

    <SortedDescendingHeaderStyle BackColor="#6F8DAE" />

    </asp:GridView>

    <br />

    <p style="color: darkslategray; font-size:1.0em; margin-left:15px;">Click the button to show the AA Log</p>

    <asp:Button CssClass="button" style="margin-left:15px;" ID="AALog" text =" AA Log " runat="server" OnClick="aa_log_btn"/>

    </div>

    </div>

    <br /><br />

    </form>

    </body>

    </html>

    The Code Behind:

    using IBM.Data.DB2;

    using System;

    using System.Collections.Generic;

    using System.Data;

    using System.Linq;

    using System.Text;

    using System.Web;

    using System.Web.UI;

    using System.Web.UI.WebControls;

    using System.Configuration;

     

    namespace Momentary_App

    {

    public partial class UsingPostback : System.Web.UI.Page

    {

    protected void Page_Postback(object sender, EventArgs e)

    {

     

    }

    protected void btnSubmit_Click(object sender, EventArgs e)

    {

    if (IsPostBack)

    {

    TextBox1.Text.ToString();

    TextBox2.Text.ToString();

    DropDownList1.Text.ToString();

    RetrieveCircuitDataSet();

    }

    }

    public DataSet RetrieveCircuitDataSet()

    {

     

    #region Declarations

    System.Data.DataSet dataSet = new System.Data.DataSet();

    System.Data.DataSet ds = new System.Data.DataSet();

    StringBuilder _qryDevice = new StringBuilder();

    #endregion

     

    try

    {

    using (DB2Connection db2Cn = new DB2Connection(Db2IBMConnectionString))

    {

    db2Cn.Open();

    using (DB2DataAdapter db2Ad = new DB2DataAdapter(_qryDevice.ToString(), db2Cn))

    {

    db2Ad.SelectCommand.CommandTimeout = Convert.ToInt32(System.Configuration.ConfigurationManager.AppSettings["DBCommandTimeout"]);

    db2Ad.SelectCommand.Parameters.Add("@Circuit", DB2Type.VarChar).Value = TextBox1.Text;

    db2Ad.SelectCommand.Parameters.Add("@District", DB2Type.VarChar).Value = TextBox2.Text;

    db2Ad.SelectCommand.Parameters.Add("@Outage", DB2Type.VarChar).Value = DropDownList1.Text;

    db2Ad.SelectCommand.CommandText = (@"SELECT VARCHAR_FORMAT(DL_TIMESTAMP, 'yyyy-mm-dd, HH24:MI:SS NNNNNN') AS TIMESTAMP, SUBSTR(DL_DATA,1,5) AS ""BREAKER STATUS"", SUBSTR(DL_DATA,6,4) || '-' || SUBSTR(DL_DATA,10,2) || '-' || SUBSTR(DL_DATA,12,2) || ', ' || SUBSTR(DL_DATA,14,2) || ':' || SUBSTR(DL_DATA,16,2) || ':' || SUBSTR(DL_DATA,18,2) || ' ' || SUBSTR(DL_DATA, 20,4) AS ""BREAKER STATUS DATE"", DL_OUTAGE_TYPE AS ""OUTAGE TYPE"" FROM TRSDB001.DMSTRS_LOG WHERE DL_OUTAGE_TYPE = '@Outage' and DL_DEVICE_CIRCUIT = '@Circuit' and DL_DEVICE_DISTRICT = '@District' ORDER BY DL_TIMESTAMP DESC");

    try

    {

    db2Ad.Fill(dataSet);

    if (dataSet.Tables[0].Rows.Count > 0)

    {

    ds = dataSet;

    GridView1.DataSource = dataSet;

    GridView1.DataBind();

    GridView1.Visible = true;

    }

    }

    catch (Exception ex)

    {

    throw ex;

    }

    }

    }

    }

    catch (Exception ex)

    {

    throw new Exception(ex.Message, ex);

    }

    return ds;

    }

    public static string Db2IBMConnectionString

    {

    get

    {

    System.Text.StringBuilder strTemp = new System.Text.StringBuilder();

    strTemp.Append("database=");

    strTemp.Append("TRSIDRQA");

    strTemp.Append(";user Id=");

    strTemp.Append("TRSDBCQ");

    strTemp.Append(";Password=");

    strTemp.Append("TRSDB2Q");

    return strTemp.ToString();

    }

    }

    protected void aa_log_btn(object sender, EventArgs e)

    {

    }

    }

    }

    Wednesday, July 8, 2015 3:24 PM

Answers

  • User-2055741253 posted

    I figured it out. Problem was the single quote ' ' in the SQL statement when referring to the parameters and removing them solved the issue.

    _qryDevice.Append("WHERE DL_DEVICE_CIRCUIT = @Circuit AND DL_DEVICE_DISTRICT = @District AND DL_OUTAGE_TYPE = @Outage ");

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, July 9, 2015 5:32 PM

All replies

  • User269602965 posted

    The @ symbol is required in the SQL COMMAND string as the BIND VARIABLE symbol

    but not in the parameter NAME.  In ADD parameters you are simply NAMING the parameter

    db2Ad.SelectCommand.Parameters.Add("Circuit", DB2Type.VarChar).Value = TextBox1.Text;

    db2Ad.SelectCommand.Parameters.Add("District", DB2Type.VarChar).Value = TextBox2.Text;

    db2Ad.SelectCommand.Parameters.Add("Outage", DB2Type.VarChar).Value = DropDownList1.Text;

    Also good idea to issue the Parameters.Clear() command before populating parameters collection

    Also the parameters in the collection must appear in the same order they appear in the SQL statement.

    Wednesday, July 8, 2015 7:54 PM
  • User-2055741253 posted

    Hello Lannie, Thank you for replying to the post.

    I corrected my code using the steps above, but I am still not getting the data pulled back on the website.

    Wednesday, July 8, 2015 9:19 PM
  • User-2055741253 posted

    I figured it out. Problem was the single quote ' ' in the SQL statement when referring to the parameters and removing them solved the issue.

    _qryDevice.Append("WHERE DL_DEVICE_CIRCUIT = @Circuit AND DL_DEVICE_DISTRICT = @District AND DL_OUTAGE_TYPE = @Outage ");

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, July 9, 2015 5:32 PM