ASP. NET - SqlDataSource update data base

Question

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!!!

you are missing the `@` parameter symbol in your Update statement here is a msdn reference to look at [SqlDataSource.UpdateParameters Property](https://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.sqldatasource.updateparameters(v=vs.110).aspx) use google if you need more examples — MethodMan, Jan 13 '16 at 21:56
if I put @ then I get the error: "Must declare scalar variable: @Marca"... — Stefi Pallo, Jan 13 '16 at 21:59
Either switch to ControlParameters or set them in the code behind. — Steve Greene, Jan 13 '16 at 22:03
why don't you create a method that updates the database using a different approach in code behind personally using UpdateParameters is rather outdated in my opinion — MethodMan, Jan 13 '16 at 22:03
After some tries I found what was wrong in the code above. First, as Marca is PK should not be used it the update command before the WHERE clause. Also I deleted UpdateParameters section too. And I added @ for each value to set. Which means that a pair Field=Value is now Field=@Value — Stefi Pallo, Jan 16 '16 at 11:37

score 1 · Answer 1 · edited May 23 '17 at 11:59

1

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.

edited May 23 '17 at 11:59

Community

1
1

answered Jan 13 '16 at 22:21

Ahmed Abd Elmoniem

157
1
4
12

score 0 · Answer 2 · answered Jan 14 '16 at 00:21

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" />

score 0 · Accepted Answer · answered Jan 14 '16 at 15:02

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];
    }
}

ASP. NET - SqlDataSource update data base

3 Answers3