UpdateCommand Parameter value always a string

Question

I am trying to update a shopping cart in my application using a gridview and a SQLDataSource control, I'm trying to set the update command parameters so that I can send values to my SQL Stored proc, but it keeps throwing an exception saying that it can't convert from nvarchar to string.

Given the below code;

 ProductsDataSource.UpdateParameters.Add("Description", row.Cells[2].Text);

it seems that this method will not accept anything other than a String as it's second argument, so how can I convert that to other values to pass into my parameters?

I have already tried something like this;

 int productID = int.Parse(row.Cells[1].Text);

but since that second parameter HAS to have a string argument, I can't insert it into my DB (Complaining it can't implicitly convert to string!)

Answer 1

Try string productID = (string)(row.Cells[1].Text)

Answer 2

Rather than directly answer why your code isn't working, I thought I'd present an alternative. Even if you fix your error with SqlDataSource, I believe it's bad to continue using in the long run. Using SqlDataSource as a control on your webpage sprinkles database code in your UI layer. This is very messy and we try to avoid that with modern applications. SqlDataSource also encourages magic strings instead of using strongly typed model objects.

A better alternative is to completely ditch SqlDataSource and use ADO.NET directly (perhaps through some micro-ORM such as Dapper). We can also move this logic into its own class, and follow the repository pattern. That class is best placed in a separate class library that your application then references, then you can reuse the class from other applications. I personally often have a console application so that I can test bits of my repository without having to go through the website.

Rather than having your website rely directly on this repository class though, we often work through an interface. This keeps our website from needing to directly depend on how the database logic is implemented. Often this is coupled with Dependency Injection, but that's a bit too big a subject for this post. I highly recommend you check out this excellent video about Dependency Injection.

So, here's our interface:

public interface IProductRepository
{
    Product GetProductById(int id);

    void UpdateProduct(Product product);

    List<Product> GetAllProducts();
}

Now for the actual implementation:

public class SqlServerProductRepository: IProductRepository
{
    private readonly string _connectionString;

    public ProductRepository(string connectionString)
    {
        _connectionString = connectionString;
    }

    public Product GetProductById(int id)
    {
        using(var connection = new SqlConnection(_connectionString))
        {
            //QuerySingle is an extension method from Dapper
            return connection.QuerySingle<Product>("select Name, Description, Id from Products where Id = @Id", new {Id = id});
        }
    }

    public void UpdateProduct(Product product)
    {
        using(var connection = new SqlConnection(_connectionString))
        {
            //Execute is an extension method from Dapper
            connection.Execute("update Products set Name = @Name, Description = @Description where Id = @Id", product);
        }
    }

    public List<Product> GetAllProducts()
    {
        using(var connection = new SqlConnection(_connectionString))
        {
            //Query is an extension method from Dapper
            //You'd likely want to implement filters/paging etc in a real world app
            return connection.Query<Product>("select Name, Description, Id from Products").AsList();
        }
    }
}

You'll need a model class if you don't have an existing one:

public class Product
{
    public int Id { get; set; }

    public string Name { get; set; }

    public string Description { get; set; }
}

Now the code in your website becomes much simpler:

//you can remove the direct reference to SqlServerProductRepository
//via Dependency Injection, not shown here
IProductRepository productRepository = new SqlServerProductRepository(connectionString);

var product = productRepository.GetProductById(1);
product.Description = "Updated Description";
productRepository.UpdateProduct(product);

ProductsGridView.DataSource = productRepository.GetAllProducts();
ProductsGridView.DataBind();

There's other ways you could go about implementing your repository, such as having it batch changes until you call SaveChanges etc, but this is a basic implementation.

Another advantage of abstracting your database interaction behind an interface is that you can try out different implementations without needing to change your entire website. Want to try Entity Framework? Create a new EntityFrameworkProductRepository that implements IProductRepository. What if you want to switch databases entirely? SqlLite is free and lightweight and suitable for small apps. Create a new SqlLiteProductRepository.

Answer 3

you can specify parameter type on markup..

<UpdateParameters >
  <asp:Parameter Name="paramName" DbType="String" Type="String" />

and you can set value on code behind.

ProductsDataSource.UpdateParameters["paramName"].DefaultValue = "parameter value";

OR you can use overloads of Add method without markup definition.

SqlDataSource1.UpdateParameters.Add("paramName", DbType.String, "parameter value");