CODEWITHSUNDEEP
c#asp.netsqldatasource
Stefi Pallo
Stefi Pallo

Reputation: 111

ASP. NET - SqlDataSource update data base

I'm learning ASP and I get stuck to update the database using asp SqlDataSource and GridView. I have the following controls:

<asp:SqlDataSource ID="SqlDataSource1" 
        runat="server" 
        ConnectionString="<%$ ConnectionStrings:BPersonalConnectionString %>" 
        OldValuesParameterFormatString="original_{0}"
        ConflictDetection="CompareAllValues"
        SelectCommand="SELECT [Marca], [Nume], [Prenume], [Salariu], [Profesia] FROM [Salariati]"
        UpdateCommand="update [Salariati] set [Marca] = Marca, [Nume] = Nume, [Prenume] = Prenume, [Salariu] = Salariu, [Profesia] = Profesia
                       where [Marca] = @original_Marca">
        <UpdateParameters>
            <asp:Parameter Name="Marca" Type="Int16"/>
            <asp:Parameter Name="Nume" Type="String" Size="20"/>
            <asp:Parameter Name="Prenume" Type="String" Size="20" />
            <asp:Parameter Name="Salariu" Type="Int32" />
            <asp:Parameter Name="Profesia" Type="String" Size="10" />
            <asp:Parameter Name="original_Marca" Type="Int16" />
        </UpdateParameters>
    </asp:SqlDataSource>

    <asp:GridView ID="GridView" 
        runat="server" 
        DataSourceID="SqlDataSource1" 
        AutoGenerateColumns="true" 
        AutoGenerateEditButton="true"
        DataKeyNames="Marca"
        AllowPaging="true"
        PageSize="3"></asp:GridView>

Whenever I try to edit and update a item I dont get any result. The grid shows me exact same values as before the update. I did not wrote any code in code behind file. I looked to some other examples already done on this subject but I was unable to identify the cause of the update problem. Any hints? Thanks!!!

Upvotes: 0

Views: 1513

Answers (3)

MethodMan
MethodMan

Reputation: 18843

If you want to do this by creating a method that returns a DataSet as well as passing parameters to execute the query then do something like this I will post the same thing returning a DataTable as well but it's pretty straight forward it works with any query that you pass dynamically

public static DataSet ExecuteDataSet(string sql, CommandType cmdType, params SqlParameter[] parameters)
{
    using (DataSet ds = new DataSet())
    using (SqlConnection connStr = new SqlConnection(ConfigurationManager.ConnectionStrings["DbConn"].ConnectionString))
    using (SqlCommand cmd = new SqlCommand(sql, connStr))
    {
        cmd.CommandType = cmdType;
        foreach (var item in parameters)
        {
            cmd.Parameters.Add(item);
        }

        try
        {
            cmd.Connection.Open();
            new SqlDataAdapter(cmd).Fill(ds);
        }
        catch (Exception ex)
        {
            throw ex;
        }
        return ds;
    }
}

if you want to return a DataTable

public static DataTable ExecuteDataSet(string sql, CommandType cmdType, params SqlParameter[] parameters)
{
    using (DataSet ds = new DataSet())
    using (SqlConnection connStr = new SqlConnection(ConfigurationManager.ConnectionStrings["DbConn"].ConnectionString))
    using (SqlCommand cmd = new SqlCommand(sql, connStr))
    {
        cmd.CommandType = cmdType;
        foreach (var item in parameters)
        {
            cmd.Parameters.Add(item);
        }

        try
        {
            cmd.Connection.Open();
            new SqlDataAdapter(cmd).Fill(ds);
        }
        catch (Exception ex)
        {
            throw ex;
        }
        return ds.Tables[0];
    }
}

Upvotes: 0

Ahmed Abd Elmoniem
Ahmed Abd Elmoniem

Reputation: 179

Use the code Behind to fill gridView, it's much easier and offer you more handling and customization to gridView binding

here you will find some guide for the simplest way to connect gridView with sql data source.

Upvotes: 1

Khazratbek
Khazratbek

Reputation: 1656

You added parameters, but where is the value?

And look to your query: to some parameters you didn't used @, but for last one used. Change your query to the following:

UpdateCommand="update [Salariati] set [Marca] = @Marca, [Nume] = @Nume, [Prenume] = @Prenume, [Salariu] = @Salariu, [Profesia] = @Profesia
                   where [Marca] = @original_Marca">

And then your parameters:

<asp:ControlParameter ControlID="YourControlID" Name="Marca" PropertyName="Text" />
<asp:ControlParameter ControlID="YourControlID" Name="Nume" PropertyName="Text" />
<asp:ControlParameter ControlID="YourControlID" Name="Prenume" PropertyName="Text" />
<asp:ControlParameter ControlID="YourControlID" Name="Salariu" PropertyName="Text" />
<asp:ControlParameter ControlID="YourControlID" Name="Profesia" PropertyName="Text" />
<asp:ControlParameter ControlID="YourControlID" Name="original_Marca" PropertyName="Text" />

Upvotes: 0

Related Questions