Gridview and update

[shanthidiana]

hi All,

I am new to dot net...

i am learning it and I am doing my masters project in c# dot net with sql server 2000 backend...

The issue i am having now is...
i am having a gridview, upon selection of one of the rows, the row data is loaded into the textbox and dropdowns, so that when changes are made and update button is hit, the data is updated in the backend...

all works fine except for the update :( issue is with postback but i dont know how to do that... pls help... giving all the codes below...

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;

public partial class scenarios : System.Web.UI.Page
{
    SqlConnection con = new SqlConnection("...;");
    SqlCommand cmd = new SqlCommand();
    SqlDataReader dr;
    SqlDataAdapter da;
    common obj = new common();
    string sql;
    string s;

    protected void Page_Load(object sender, EventArgs e)
    {
        Session["projid"] = "12345";
        Session["projname"] = "ProjectA";
        if (!IsPostBack)
        {
            pid.Text = Session["projid"].ToString();
            pname.Text = Session["projname"].ToString();
            sql = "select * from scenarios where pid = '" + Session["projid"].ToString() + "'";
            da = new SqlDataAdapter(sql, con);
            DataTable dt = new DataTable();
            da.Fill(dt);
            int prowct = dt.Rows.Count;
            if (prowct == 0)
            {
                btnadd.Visible = true;
                btnupdate.Visible = false;
                GridView1.DataBind();
            }  
            else
            {
                btnadd.Visible = true;
                btnupdate.Visible = false;
                GridView1.DataBind();
            }  


        }

    }
    protected void btnadd_Click(object sender, EventArgs e)
    {
        con.Open();
        sql = "Insert into scenarios values ('" + pid.Text + "','" + pname.Text + "','" + tbcat.Text + "','" + tbscenario.Text + "','" + ddlprob.SelectedValue.ToString() + "','" + ddlprob.SelectedItem.Text + "','" + ddlimpact.SelectedValue.ToString() + "','" + ddlimpact.SelectedItem.Text + "','" + tbmitig.Text + "')";
        cmd = new SqlCommand(sql, con);
        cmd.ExecuteNonQuery();
        obj.msgbox("Values are inserted");
        con.Close();
        clearall();
        GridView1.DataBind();

    }

    public void clearall()
    {
        tbcat.Text = "";
        tbscenario.Text = "";
        ddlprob.ClearSelection();
        ddlimpact.ClearSelection();
        tbmitig.Text = "";
    }
    protected void btnreset_Click(object sender, EventArgs e)
    {
        clearall();
    }
    protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
    {
        btnadd.Visible = false;
        btnupdate.Visible = true;
        s = GridView1.SelectedValue.ToString();
        sql = "select * from scenarios where ID = '" + s + "'";
        con.Open();
        cmd = new SqlCommand(sql, con);
        dr = cmd.ExecuteReader();
        if (dr.Read())
        {
            tbcat.Text = dr[3].ToString();
            tbscenario.Text = dr[4].ToString();
            ddlprob.SelectedIndex = int.Parse(dr[5].ToString());
            ddlimpact.SelectedIndex = int.Parse(dr[7].ToString());
            tbmitig.Text = dr[9].ToString();
        }
        con.Close();

    }
    protected void btnupdate_Click(object sender, EventArgs e)
    {
        //cmd.Connection = con;
        con.Open();
        sql = "update scenarios set discateg = '" + tbcat.Text + "', disscenario= '" + tbscenario.Text + "', probval = '" + ddlprob.SelectedValue.ToString() + "', prob = '" + ddlprob.SelectedItem.Text + "', impactval = '" +ddlimpact.SelectedValue.ToString() + "', impact = '" + ddlimpact.SelectedItem.Text + "', mitig = '" + tbmitig.Text + "' where ID= '" +s + "'";
        cmd = new SqlCommand(sql, con);
        cmd.ExecuteNonQuery();
        obj.msgbox("Values are updated");
        con.Close();
        clearall();
        GridView1.DataBind();

    }
}

-----------

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="scenarios.aspx.cs" Inherits="scenarios" %>

<!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>Untitled Page</title>
    <style type="text/css">
        .style1
        {
            width: 100%;
        }
        .style2
        {
            text-align: right;
        }
    </style>
</head>
<body style="font-family: Verdana; font-size: x-small">
    <form id="form1" runat="server">
    <div>
    
        <table class="style1">
            <tr>
                <td>
                    &nbsp;</td>
                <td class="style2">
                    &nbsp;</td>
                <td>
                    &nbsp;</td>
                <td>
                    &nbsp;</td>
            </tr>
            <tr>
                <td>
                    &nbsp;</td>
                <td class="style2">
                    Project Name:</td>
                <td>
                    <asp:Label ID="pname" runat="server"></asp:Label>
                </td>
                <td>
                    &nbsp;</td>
            </tr>
            <tr>
                <td>
                    &nbsp;</td>
                <td class="style2">
                    &nbsp;</td>
                <td>
                    <br />
                </td>
                <td>
                    &nbsp;</td>
            </tr>
            <tr>
                <td>
                    &nbsp;</td>
                <td class="style2">
                    Project ID:</td>
                <td>
                    <asp:Label ID="pid" runat="server"></asp:Label>
                </td>
                <td>
                    &nbsp;</td>
            </tr>
            <tr>
                <td>
                    &nbsp;</td>
                <td class="style2">
                    &nbsp;</td>
                <td>
                    &nbsp;</td>
                <td>
                    &nbsp;</td>
            </tr>
            <tr>
                <td>
                    &nbsp;</td>
                <td class="style2">
                    Category:</td>
                <td>
                    <asp:TextBox ID="tbcat" runat="server" Width="183px" Font-Names="Verdana" 
                        Font-Size="X-Small"></asp:TextBox>
                </td>
                <td>
                    &nbsp;</td>
            </tr>
            <tr>
                <td>
                    &nbsp;</td>
                <td class="style2">
                    &nbsp;</td>
                <td>
                    &nbsp;</td>
                <td>
                    &nbsp;</td>
            </tr>
            <tr>
                <td>
                    &nbsp;</td>
                <td class="style2">
                    Disaster Scenario:</td>
                <td>
                    <asp:TextBox ID="tbscenario" runat="server" Height="62px" Width="183px" 
                        Font-Names="Verdana" Font-Size="X-Small"></asp:TextBox>
                </td>
                <td>
                    &nbsp;</td>
            </tr>
            <tr>
                <td>
                    &nbsp;</td>
                <td class="style2">
                    &nbsp;</td>
                <td>
                    &nbsp;</td>
                <td>
                    &nbsp;</td>
            </tr>
            <tr>
                <td>
                    &nbsp;</td>
                <td class="style2">
                    Probability:</td>
                <td>
                    <asp:DropDownList ID="ddlprob" runat="server" Font-Names="Verdana" 
                        Font-Size="X-Small">
                        <asp:ListItem Value="0">Select</asp:ListItem>
                        <asp:ListItem Value="1">Low</asp:ListItem>
                        <asp:ListItem Value="2">Medium</asp:ListItem>
                        <asp:ListItem Value="3">High</asp:ListItem>
                    </asp:DropDownList>
                </td>
                <td>
                    &nbsp;</td>
            </tr>
            <tr>
                <td>
                    &nbsp;</td>
                <td class="style2">
                    &nbsp;</td>
                <td>
                    &nbsp;</td>
                <td>
                    &nbsp;</td>
            </tr>
            <tr>
                <td>
                    &nbsp;</td>
                <td class="style2">
                    Impact:</td>
                <td>
                    <asp:DropDownList ID="ddlimpact" runat="server" Font-Names="Verdana" 
                        Font-Size="X-Small">
                        <asp:ListItem Value="0">Select</asp:ListItem>
                        <asp:ListItem Value="1">High</asp:ListItem>
                        <asp:ListItem Value="2">Medium</asp:ListItem>
                        <asp:ListItem Value="3">Low</asp:ListItem>
                    </asp:DropDownList>
                </td>
                <td>
                    &nbsp;</td>
            </tr>
            <tr>
                <td>
                    &nbsp;</td>
                <td class="style2">
                    &nbsp;</td>
                <td>
                    &nbsp;</td>
                <td>
                    &nbsp;</td>
            </tr>
            <tr>
                <td>
                    &nbsp;</td>
                <td class="style2">
                    Mitigation:</td>
                <td>
                    <asp:TextBox ID="tbmitig" runat="server" Height="62px" Width="183px" 
                        Font-Names="Verdana" Font-Size="X-Small"></asp:TextBox>
                </td>
                <td>
                    &nbsp;</td>
            </tr>
            <tr>
                <td>
                    &nbsp;</td>
                <td class="style2">
                    &nbsp;</td>
                <td>
                    &nbsp;</td>
                <td>
                    &nbsp;</td>
            </tr>
            <tr>
                <td>
                    &nbsp;</td>
                <td class="style2">
                    <asp:Button ID="btnupdate" runat="server" Height="20px" 
                        onclick="btnupdate_Click" Text="Update" Width="50px" />
                    <asp:Button ID="btnadd" runat="server" Height="20px" Text="Add" Width="50px" 
                        onclick="btnadd_Click" />
                </td>
                <td>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                    <asp:Button ID="btnreset" runat="server" Height="20px" Text="Reset" 
                        Width="50px" onclick="btnreset_Click" />
                </td>
                <td>
                    &nbsp;</td>
            </tr>
            <tr>
                <td>
                    &nbsp;</td>
                <td class="style2" colspan="2">
                    <br />
                    <asp:GridView ID="GridView1" runat="server" AllowPaging="True" 
                        AllowSorting="True" DataKeyNames="ID" DataSourceID="SqlDataSource1" 
                        onselectedindexchanged="GridView1_SelectedIndexChanged" 
                        style="text-align: left">
                        <Columns>
                            <asp:CommandField ShowSelectButton="True" />
                        </Columns>
                    </asp:GridView>
                    <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
                        ConnectionString="<%$ ConnectionStrings:bcpertConnectionString %>" 
                        SelectCommand="SELECT * FROM [scenarios] WHERE ([pid] = @pid)">
                        <SelectParameters>
                            <asp:SessionParameter Name="pid" SessionField="projid" Type="String" />
                        </SelectParameters>
                    </asp:SqlDataSource>
                </td>
                <td>
                    &nbsp;</td>
            </tr>
        </table>
    
    </div>
    </form>
</body>
</html>

------------------------

table creation code:

CREATE TABLE [dbo].[scenarios] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[pid] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[pname] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[discateg] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[disscenario] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[prob] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[impact] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[mitig] [varchar] (300) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

[sreemathy2000]

Did u check the AutoPostback property of the update button? it should be set to true,

[shanthidiana]

still now luck Sreemathy... here is wat i did... anything else i should do???

<asp:Button ID="btnupdate" AutoPostBack = "true" runat="server" Height="20px"
onclick="btnupd ate_Click" Text="Update" />

[artov]

You might like to study SQL injection, for example your statement

sql = "select * from scenarios where ID = '" + s + "'";

is begging for trouble. Even if you control where the s comes, using parameters shows to your teacher you think about security.

[shanthidiana]

yep i understand... but being a newbie i am just learning the stuff...

Any help on the issue i am actually facing??? (mentioned in the first post)

[artov]

I still recommend you to use parameters. For example the update statement; you use

sql = "update scenarios set discateg = '" + tbcat.Text + "', disscenario= '" + tbscenario.Text + "', probval = '" + ddlprob.SelectedValue.ToString() + "', prob = '" + ddlprob.SelectedItem.Text + "', impactval = '" +ddlimpact.SelectedValue.ToString() + "', impact = '" + ddlimpact.SelectedItem.Text + "', mitig = '" + tbmitig.Text + "' where ID= '" +s + "'";

What if the tbscenario.Text contains value It wasn't my cat ? You get run time error or all the data in screnarios table is updated. Use parameters to prevent this (and SQL injection...)

[Frinavale]

hi All,
all works fine except for the update :( issue is with postback but i dont know how to do that... pls help... giving all the codes below...

What is this "issue with postback"?
Could you please explain this issue in more details?


Check out this article for more information about SQL Injection attacks and please take a look at the article about how to use a database in your program for more information on how to use parametrized SQL queries like Artov was suggesting.

If you change your SQL query to use Parameters you may see an error with your SQL statement more clearly. Have you tried executing the update sql statement in Query Analyzer or some other tool to make sure it works?

[shanthidiana]

hi all i fixed the issue at last... the issue was with GridView1_Selec tedIndexChanged event... the string s was assigned a value of null everytime... so for this, i used a hidden label instead of getting the value assigned to s... it worked... tat is y i gave the code... if someone wanted to run that from their end... but no one did... anyway thanks for ur help.

Also i have started using parameters now... i should be excused for the above code as i am new to dot net :)