-
[RESOLVED] SqlObject data Source + Update
I m facing problem in Update operation,Select operation is completed.
To Perform Update operation-
1)Add Class Products
2) Add a new method called UpdateProducts
below is the Vb code,but i want to work with C# Code
Code:
Public Sub updateProducts(ByVal ProductID As Integer, _
ByVal ProductName As String, _
ByVal SupplierID As Integer, _
ByVal CategoryID As Integer, _
ByVal QuantityPerUnit As String, _
ByVal UnitPrice As Double)
Dim conn As New SqlConnection("Server=(local);Integrated
Security=True;Database=Northwind;Persist Security
Info=True")
Dim adapter As New SqlDataAdapter("SELECT * FROM Products WHERE ProductID=" & ProductID, conn)
Dim ds As New DataSet
adapter.Fill(ds, "Products")
With ds.Tables(0).Rows(0)
.Item("ProductName") = ProductName
.Item("SupplierID") = SupplierID
.Item("CategoryID") = CategoryID
.Item("QuantityPerUnit") = QuantityPerUnit
.Item("UnitPrice") = UnitPrice
End With
Dim cb As New SqlCommandBuilder(adapter)
adapter.Update(ds, "Products")
End Sub
C# Code-
Code:
public class Products
{
void UpdateProducts(int UID, string firstname, string LastName, string EMail, string Address, string phoneNumb)
{
SqlConnection conn = new SqlConnection("Data Source=(local);Initial Catalog=ProductsDB;User ID=gaurav;Password=gaurav");
SqlDataAdapter da = new SqlDataAdapter("Select * from ProductsTable", conn);
DataSet ds = new DataSet();
da.Fill(ds, "ProductsTable");
I NEED CODE HERE
Help me in converting VB Code to C# Code.
-
Re: SqlObject data Source + Update
Hey,
The With construct doesn't exist in C#, and to be honest, I have never really liked it :)
For situations like this, I find that this website is quite good:
http://www.developerfusion.com/tools.../vb-to-csharp/
Putting this into it:
Code:
Dim conn As New SqlConnection("Server=(local);Integrated Security=True;Database=Northwind;Persist Security Info=True")
Dim adapter As New SqlDataAdapter("SELECT * FROM Products WHERE ProductID=" & ProductID, conn)
Dim ds As New DataSet
adapter.Fill(ds, "Products")
With ds.Tables(0).Rows(0)
.Item("ProductName") = ProductName
.Item("SupplierID") = SupplierID
.Item("CategoryID") = CategoryID
.Item("QuantityPerUnit") = QuantityPerUnit
.Item("UnitPrice") = UnitPrice
End With
Dim cb As New SqlCommandBuilder(adapter)
adapter.Update(ds, "Products")
Results in this:
Code:
{
    SqlConnection conn = new SqlConnection("Server=(local);Integrated Security=True;Database=Northwind;Persist Security Info=True");
    SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM Products WHERE ProductID=" + ProductID, conn);
    DataSet ds = new DataSet();
    adapter.Fill(ds, "Products");
    {
        ds.Tables(0).Rows(0).Item("ProductName") = ProductName;
        ds.Tables(0).Rows(0).Item("SupplierID") = SupplierID;
        ds.Tables(0).Rows(0).Item("CategoryID") = CategoryID;
        ds.Tables(0).Rows(0).Item("QuantityPerUnit") = QuantityPerUnit;
        ds.Tables(0).Rows(0).Item("UnitPrice") = UnitPrice;
    }
    SqlCommandBuilder cb = new SqlCommandBuilder(adapter);
    adapter.Update(ds, "Products");
}
Which is not a bad attempt, but does need some refinement, namely:
Code:
SqlConnection conn = new SqlConnection("Server=(local);Integrated Security=True;Database=Northwind;Persist Security Info=True");
SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM Products WHERE ProductID=" + ProductID, conn);
DataSet ds = new DataSet();
adapter.Fill(ds, "Products");
ds.Tables[0].Rows[0]["ProductName"] = ProductName;
ds.Tables[0].Rows[0]["SupplierID"] = SupplierID;
ds.Tables[0].Rows[0]["CategoryID"] = CategoryID;
ds.Tables[0].Rows[0]["QuantityPerUnit"] = QuantityPerUnit;
ds.Tables[0].Rows[0]["UnitPrice"] = UnitPrice;
SqlCommandBuilder cb = new SqlCommandBuilder(adapter);
adapter.Update(ds, "Products");
These changes are needed due to the fact that C# uses []'s for indexing, where as VB.Net uses ()'s, also that C# doesn't have an item property, but rather you index straight into the columns.
The only thing that I would say in addition is that you might want to put a using statement around the disposable classes, such as the connection, that way you don't have to explicitly call .Close() but rather they are disposed of just before they go out of scope.
Hope that helps!!
Gary
-
Re: SqlObject data Source + Update
hi Gep,first of all thx.
I want to ask that I Add a class products.cs in App_Code Folder. But sometimes when i select congigure Data source ,in Drop down choose your Business object,nothing item is coming. Y so????????
-
Re: SqlObject data Source + Update
hello gep, First problem is solved.
I add class Products in App_Code Folder.
Now When I click on Update Tab, & choose a method from dropdown, None is there.y SO??????
Code:
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Data;
using Microsoft.VisualBasic;
/// <summary>
/// Summary description for Products
/// </summary>
public class Products
{
public DataSet Getproducts()
{
SqlConnection conn = new SqlConnection("Data Source=(local);Initial Catalog=gaurav;User ID=gaurav;Password=gaurav");
SqlDataAdapter da = new SqlDataAdapter("Select * from Products", conn);
DataSet ds = new DataSet();
da.Fill(ds, "Products");
return ds;
}
void UpdateProduct(int ProdID, string ProdName, int PhoneNum)
{
SqlConnection conn = new SqlConnection("Data Source=(local);Initial Catalog=gaurav;User ID=gaurav;Password=gaurav");
SqlDataAdapter da = new SqlDataAdapter("Select * from Products", conn);
DataSet ds = new DataSet();
da.Fill(ds, "Info1");
ds.Tables[0].Rows[0]["ProdID"] = ProdID;
SqlCommandBuilder cb = new SqlCommandBuilder(da);
da.Update(ds, "Products");
}
}
-
Re: SqlObject data Source + Update
Hey,
Am I right in saying that you can see the Getproducts method?
If so, then I think you need to mark your UpdateProduct method as public.
I would question the logic that I see in your Update Method though!??! Why are you first doing a select? Why not just directly update the database using an update command?
Also, you might want to think about returning an integer from your Update Method, that way the caller can know how many rows were altered.
Here is an example from a one of my methods:
Code:
/// <summary>
/// Updates an article
/// </summary>
public override bool UpdateArticle(ArticleDetails article)
{
using (MySqlConnection cn = new MySqlConnection(this.ConnectionString))
{
MySqlCommand cmd = new MySqlCommand("ARTICLES_UpdateArticle", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@ArticleID", MySqlDbType.Int32).Value = article.ID;
cmd.Parameters.Add("@CategoryID", MySqlDbType.Int32).Value = article.CategoryID;
cmd.Parameters.Add("@Title", MySqlDbType.Text).Value = article.Title;
cmd.Parameters.Add("@Abstract", MySqlDbType.Text).Value = article.Abstract;
cmd.Parameters.Add("@Body", MySqlDbType.Text).Value = article.Body;
cmd.Parameters.Add("@Country", MySqlDbType.Text).Value = article.Country;
cmd.Parameters.Add("@State", MySqlDbType.Text).Value = article.State;
cmd.Parameters.Add("@City", MySqlDbType.Text).Value = article.City;
cmd.Parameters.Add("@ReleaseDate", MySqlDbType.DateTime).Value = article.ReleaseDate;
cmd.Parameters.Add("@ExpireDate", MySqlDbType.DateTime).Value = article.ExpireDate;
cmd.Parameters.Add("@Approved", MySqlDbType.Bit).Value = article.Approved;
cmd.Parameters.Add("@Listed", MySqlDbType.Bit).Value = article.Listed;
cmd.Parameters.Add("@CommentsEnabled", MySqlDbType.Bit).Value = article.CommentsEnabled;
cmd.Parameters.Add("@OnlyForMembers", MySqlDbType.Bit).Value = article.OnlyForMembers;
cn.Open();
int ret = ExecuteNonQuery(cmd);
return (ret == 1);
}
}
Where the stored procedure, ARTICLES_UpdateArticle, simply does an Update SQL Command.
Gary
-
Re: SqlObject data Source + Update
hey gep thx,I just mark the method as public & it worked.
I used Select Method,cz i m binding the Gridview with ObjectdataSource only. Now I have Edit hyperlink in my Gridview,tell me that we have to write the code To Edit the data in DB,or it do automatically by itself.
If we have to write any code,then tell me on which event to write code???
-
Re: SqlObject data Source + Update
Hey,
The fact that you are binding to the GridView isn't a justification to do the select in the update statement. I have code to do exactly the same as what you are saying, but I don't need to do the select. I just take the parameters from the GridView and pass them back to the Update Statement.
To an extent, the update code can happen automatically, but there will be some work on your part in order to ensure that the parameters that you create for the UpdateCommand for your ObjectDataSource is correct.
For a complete example, I would suggest that you take a look at the BeerHouse Sample kit, you can find a link to the starter kits page in my signature. It is one of the best examples that I have seen.
Gary
-
Re: SqlObject data Source + Update
Ya u r right,Y i select data in Update method,
I modify the code,but didnt getting what to do after it-
Code:
public void UpdateProduct(int UID, string firstname, string LastName, string EMail, string Address, int phoneNumb)
{
SqlConnection conn = new SqlConnection("Data Source=(local);Initial Catalog=Products;User ID=gaurav;Password=gaurav");
//SqlDataAdapter da = new SqlDataAdapter("Select * from Products", conn);
//DataSet ds = new DataSet();
//da.Fill(ds, "Info1");
//ds.Tables[0].Rows[0]["UID"] = UID;
//ds.Tables[0].Rows[0]["FirstName"] = firstname;
////ds.Tables[0].Rows[0]["CategoryID"] = CategoryID;
////ds.Tables[0].Rows[0]["QuantityPerUnit"] = QuantityPerUnit;
////ds.Tables[0].Rows[0]["UnitPrice"] = UnitPrice;
//SqlCommandBuilder cb = new SqlCommandBuilder(da);
//da.Update(ds, "Products");
I download beer house ,but it contains so many errors in all forms.Not even a single form is opening.
-
Re: SqlObject data Source + Update
Hey,
I am confused, do you have two usernames?
ritu verma and gtyagi?!?
What errors are you getting with the BeerHouse? The last time I downloaded it, it worked perfectly? Even if it isn't working, it is actually the structure of the code that you should be looking at. I shows exactly how you need to segregate your code across the different layers, UI, BLL and DAL.
Gary
-
Re: SqlObject data Source + Update
hi no two user names,ACtually we both are from same college, & doing Project together.We are just able to see Source View,When we switch to design View,error is dere Design view does not support creating or editing nested master pages.To create or edit master page,use source view.
-
Re: SqlObject data Source + Update
Hey,
Can you perhaps elaborate on your last post, I am not sure what you are referring to? Are you talking about the problems that you are having with the BeerHouse Sample kit?
Gary
-
Re: SqlObject data Source + Update
yes gep,dose problems are faced in Beer Hosue Kit.Leave about Beer house,just tell us how to do update operation in ObjectDataSource????Its urgent.No solution is there on the net.
-
Re: SqlObject data Source + Update
Hey,
I understand that it is urgent, but I would encourage you to understand what it is you are trying to do. As for there not being a solution on the Net, that is quite simply not the case.
In order to perform the operation via an ObjectDataSource you need to have the following:
1) A sql select statement to provide the data to the user interface
2) A sql update statement to update a record in the database
3) A sql delete statement to remove a record from the database
4) A sql insert statement to add a record to the database
Once you have all of the above, you can create a method in your class that executes these sql statements. For each parameter that you SQL Statement requires, you will need to pass as a parameter into the method.
Once you have that, you can create the ObjectDataSource on your page, and hook up the Commands of that ObjectDataSource to the methods that you have created.
You can then assign the ObjectDataSource to the Data-Binding Control that you are using.
Next, you need to add to the parameters collection for each of these commands, i.e SelectCommand, UpdateCommand, InsertCommand and DeleteCommand. If you can't tie these paratemeters directly, then you can assign them dynamically using the corresponding events of the ObjectDataSource:
http://msdn.microsoft.com/en-us/libr...e_members.aspx
All of this is discussed here:
http://msdn.microsoft.com/en-us/libr...y5(VS.80).aspx
Gary
-
Re: SqlObject data Source + Update
Thx very much gep,Without Your support its not possible to do.
Just Small Probs. I dont want the productid to be edit, I want that if i click on edit, only second & third columns comes to edit mode,but not the first column that contains ProductId. Is it possible to do?
Complete Working Solution.
Code:
<form id="form1" runat="server">
<div>
</div>
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" SelectMethod="Getproducts"
TypeName="Products" UpdateMethod="UpdateProduct">
<UpdateParameters>
<asp:Parameter Name="ProdID" Type="Int32" />
<asp:Parameter Name="ProdName" Type="String" />
<asp:Parameter Name="ProdDesc" Type="String" />
</UpdateParameters>
</asp:ObjectDataSource>
<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True"
DataSourceID="ObjectDataSource1">
<Columns>
<asp:CommandField ShowEditButton="True" />
</Columns>
</asp:GridView>
</form>
Code:
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using Microsoft.VisualBasic;
using System.Data.SqlClient;
/// <summary>
/// Summary description for Products
/// </summary>
public class Products
{
public DataSet Getproducts()
{
SqlConnection conn = new SqlConnection("Data Source=(local);Initial Catalog=gaurav;User ID=gaurav;Password=gaurav");
SqlDataAdapter da = new SqlDataAdapter("Select * from Products", conn);
DataSet ds = new DataSet();
da.Fill(ds, "Info1");
return ds;
}
public void UpdateProduct(int ProdID, string ProdName, string ProdDesc)
{
SqlConnection conn = new SqlConnection("Data Source=(local);Initial Catalog=gaurav;User ID=gaurav;Password=gaurav");
string queryString = "UPDATE Products SET ProdName=@ProductName,ProdDesc=@ProductDesc WHERE ProdId=@ProductID";
SqlCommand cmd;
cmd = new SqlCommand(queryString, conn);
cmd.Parameters.AddWithValue("@ProductName", ProdName);
cmd.Parameters.AddWithValue("@ProductDesc", ProdDesc);
cmd.Parameters.AddWithValue("@ProductID", ProdID);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
}
Code:
create table Products(ProdId int,ProdName varchar(100),ProdDesc varchar(100))
Insert into Products values(1,'A','A Desc')
Insert into Products values(2,'B','B Desc')
Insert into Products values(3,'C','C Desc')
select * from Products
-
Re: SqlObject data Source + Update
Noice!!!
Looks good to me!!
Glad you got it working!!
Remember to mark your thread as resolved.
Gary
-
Re: SqlObject data Source + Update
Gep i have small probs .
Just Small Probs. I dont want the productid to be edit, I want that if i click on edit, only second & third columns comes to edit mode,but not the first column that contains ProductId. Is it possible to do?
-
Re: SqlObject data Source + Update
What's the control type for productid? For the control if you have ReadOnly property set it to true.
-
Re: SqlObject data Source + Update
Control type????? I didnt get u. R u asking abot datatype. If yes,its integer. Set Readonly property how??
-
Re: SqlObject data Source + Update
My bad. Didn't go through your markup. I thought you had EditItemTemplate defined.
Another thing that I learn is, apparently you can not set it to ReadOnly (even if you have EditItemTemplate defined) as the ViewState doesn't carry that value. As a workaround, I would suggest is in your UpdateProduct() method, use the DataKey (you don't have it right now but all you need to do is set the property to the field ProdId) value to get the Product id rather than the product id from the editable field.
-
Re: SqlObject data Source + Update
I m not getting you yaar.Can u please provide me the code.
-
2 Attachment(s)
Re: SqlObject data Source + Update
Hey,
See if this works for you...
Go into Design View and select your GridView, click the little icon at the top right of the Gridview to bring up the GridView Tasks Window:
Attachment 73747
And click on Edit Columns.
Attachment 73748
Here, select your ProdID Column, and set the ReadOnly property of it to True, optionally, you can also set this to Visible = False. That way the value is still available to you at runtime, but it isn't shown to the client.
Hope that helps!!
Gary
-
Re: SqlObject data Source + Update
hello gep its not working.
Code:
create table Products(ProdId int,ProdName varchar(100),ProdDesc varchar(100))
Insert into Products values(1,'A','A Desc')
Insert into Products values(2,'B','B Desc')
Insert into Products values(3,'C','C Desc')
select * from Products
Everytime in ProdID,0 is coming.Not the product Id is coming,Suppose i click on first row that has prod id 1,in Procedure UpdateProduct 0 ProdID is going..& so on.
Code:
public void UpdateProduct(int ProdID, string ProdName, string ProdDesc)
{
SqlConnection conn = new SqlConnection("Data Source=(local);Initial Catalog=Products;User ID=gaurav;Password=gaurav");
string queryString = "UPDATE Products SET ProdName=@ProductName,ProdDesc=@ProductDesc WHERE ProdId=@ProductID";
SqlCommand cmd;
cmd = new SqlCommand(queryString, conn);
cmd.Parameters.AddWithValue("@ProductName", ProdName);
cmd.Parameters.AddWithValue("@ProductDesc", ProdDesc);
cmd.Parameters.AddWithValue("@ProductID", ProdID);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
Code:
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" SelectMethod="Getproducts"
TypeName="Products" UpdateMethod="UpdateProduct">
<UpdateParameters>
<asp:Parameter Name="ProdID" Type="Int32" />
<asp:Parameter Name="ProdName" Type="String" />
<asp:Parameter Name="ProdDesc" Type="String" />
</UpdateParameters>
</asp:ObjectDataSource>
<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True"
DataSourceID="ObjectDataSource1" AutoGenerateColumns="False">
<Columns>
<asp:CommandField ShowEditButton="True" />
<asp:BoundField HeaderText="ProdId" ReadOnly="True" Visible="False" />
<asp:BoundField DataField ="ProdName" HeaderText="ProdName"/>
<asp:BoundField DataField ="ProdDesc" HeaderText="Desc" />
</Columns>
</asp:GridView>
-
Re: SqlObject data Source + Update
Hey,
You will have to be very careful here, as I think Parameters are Case Sensitive:
Code:
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" SelectMethod="Getproducts"
TypeName="Products" UpdateMethod="UpdateProduct">
<UpdateParameters>
<asp:Parameter Name="ProdID" Type="Int32" />
<asp:Parameter Name="ProdName" Type="String" />
<asp:Parameter Name="ProdDesc" Type="String" />
</UpdateParameters>
</asp:ObjectDataSource>
<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True"
DataSourceID="ObjectDataSource1" AutoGenerateColumns="False">
<Columns>
<asp:CommandField ShowEditButton="True" />
<asp:BoundField HeaderText="ProdId" ReadOnly="True" Visible="False" />
<asp:BoundField DataField ="ProdName" HeaderText="ProdName"/>
<asp:BoundField DataField ="ProdDesc" HeaderText="Desc" />
</Columns>
</asp:GridView>
Try making the above the same case.
Gary
-
Re: SqlObject data Source + Update
Still no luck.
Code:
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" SelectMethod="Getproducts"
TypeName="Products" UpdateMethod="UpdateProduct">
<UpdateParameters>
<asp:Parameter Name="ProdID" Type="Int32" />
<asp:Parameter Name="ProdName" Type="String" />
<asp:Parameter Name="ProdDesc" Type="String" />
</UpdateParameters>
</asp:ObjectDataSource>
<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True"
DataSourceID="ObjectDataSource1" AutoGenerateColumns="False">
<Columns>
<asp:CommandField ShowEditButton="True" />
<asp:BoundField HeaderText="ProdID" ReadOnly="True" Visible="False" />
<asp:BoundField DataField ="ProdName" HeaderText="ProdName"/>
<asp:BoundField DataField ="ProdDesc" HeaderText="Desc" />
</Columns>
</asp:GridView>
-
Re: SqlObject data Source + Update
Hey,
But this has been changed the wrong way, your query is pulling back this:
Code:
create table Products(ProdId int,ProdName varchar(100),ProdDesc varchar(100))
Make both your parameters use this.
Gary
-
Re: SqlObject data Source + Update
Not working still.
Code:
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" SelectMethod="Getproducts"
TypeName="Products" UpdateMethod="UpdateProduct">
<UpdateParameters>
<asp:Parameter Name="ProdId" Type="Int32" />
<asp:Parameter Name="ProdName" Type="String" />
<asp:Parameter Name="ProdDesc" Type="String" />
</UpdateParameters>
</asp:ObjectDataSource>
<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True"
DataSourceID="ObjectDataSource1" AutoGenerateColumns="False">
<Columns>
<asp:CommandField ShowEditButton="True" />
<asp:BoundField DataField="ProdId" ReadOnly="True" Visible="False" />
<asp:BoundField DataField ="ProdName" HeaderText="ProdName"/>
<asp:BoundField DataField ="ProdDesc" HeaderText="Desc" />
</Columns>
</asp:GridView>
Is there any need to attach project ?
-
Re: SqlObject data Source + Update
Hey,
That might help yes.
But let's just back up a step...
can you confirm exactly what you have working, and what you don't?
Gary
-
Re: SqlObject data Source + Update
my friend did you ever think of using TRY & Catch..
try that option..
-
1 Attachment(s)
Re: SqlObject data Source + Update
My code is working if i show ProdId column in Gridview, If i made the column ReadOnly,them i facing the probs.Evertime in Product Id 0 is coming.
-
Re: SqlObject data Source + Update
hi Gep,me didnt able to find solution,u tell na!
-
Re: SqlObject data Source + Update
Hey,
Actually, I got distracted, I did download the file, but I don't have winrar installed.
Let me go and grab it, and see what I can make of it, not sure when I will be able to post back though.
Gary
-
Re: SqlObject data Source + Update
Hey,
Can you also provide information about the structure of your database?
Would need to have this in order to test your code as is.
Gary
-
Re: SqlObject data Source + Update
Structure
Prod Id -Integer, prod Name- string, prod Desc- string.
I want to bind Gridview with Object Data Source & do all the operations Insert,Update & Delete.But i got stuch in Update Operation.
-
Re: SqlObject data Source + Update
Let me give it another shot. If you make the item readonly, the viewstate doesn't carry the value and hence during a postback the ProdId will have 0 (the default value). This was a known bug and apparently has been fixed; I'm not sure which version. (https://connect.microsoft.com/Visual...dbackID=260674).
As a workaround, you can have DataKeys defined in your gridview and then retrieve the ProdId for the row being edited from there (won't be a straight forward approach). Also, your gridview seems to be a runtime generated one, I'm not even sure how this will work out (and hence no sample code).
There are a few articles about Object DataSources you may want to skim
http://www.manuelabadia.com/blog/Per...5ceaf21cc.aspx
http://geekswithblogs.net/mnf/archiv.../01/89957.aspx
-
Re: SqlObject data Source + Update
Thx rjv_rnjn.Link provided is too gud
http://www.manuelabadia.com/blog/Per...5ceaf21cc.aspx
gep Its not necessary that in DataField="Id" ,Column Name from DB is there. I just bound the dataGridview with ObjectDataSource .
Code:
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" DataObjectTypeName="Product"
SelectMethod="LoadAll" TypeName="ProductsDAL" UpdateMethod="Edit"></asp:ObjectDataSource>
</div>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="ObjectDataSource1" AllowPaging="True">
<Columns>
<asp:CommandField ShowEditButton="True" />
<asp:BoundField DataField="Id" HeaderText="Id" SortExpression="Id" ReadOnly="true" />
<asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />
<asp:BoundField DataField="Description" HeaderText="Description" SortExpression="Description" />
</Columns>
</asp:GridView>
Final Solution
Product.cs
Code:
using System;
public class Product
{
#region Fields
protected int _id;
protected string _name;
protected string _description;
#endregion
#region Properties
public int Id
{
get { return _id; }
set { _id = value; }
}
public string Name
{
get { return _name; }
set { _name = value; }
}
public string Description
{
get { return _description; }
set { _description = value; }
}
#endregion
#region Constructors
public Product()
{
_id = -1;
}
public Product(int id, string name, string description, decimal price)
{
_id = id;
_name = name;
_description = description;
}
#endregion
}
ProductsDAL.cs
Code:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Web;
public class ProductsDAL
{
public List<Product> LoadAll()
{
List<Product> products = new List<Product>();
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
string query = "Select * from Products";
conn.Open();
SqlCommand cmd;
cmd = new SqlCommand(query, conn);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
Product prod = new Product();
prod.Id = (int)dr["ProdID"];
prod.Name = (string)dr["ProdName"];
prod.Description = (string)dr["ProdDesc"];
products.Add(prod);
}
dr.Close();
conn.Close();
return products;
}
public void Edit(Product prod)
{
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
SqlCommand command = new SqlCommand("UPDATE Products SET ProdName=@ProductName,ProdDesc=@ProductDesc WHERE ProdId=@ProductID", conn);
command.Parameters.Add(new SqlParameter("@ProductName", prod.Name));
command.Parameters.Add(new SqlParameter("@ProductDesc", prod.Description));
command.Parameters.Add(new SqlParameter("@ProductID", prod.Id));
conn.Open();
command.ExecuteNonQuery();
conn.Close();
}
}
-
Re: [RESOLVED] SqlObject data Source + Update
Good that you got it working!
I like your gridview more now (you've the columns defined)! :thumb: