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
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")
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.
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????????
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");
}
}
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.
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???
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.
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.
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.
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?
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.
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:
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?
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
Last edited by gtyagi; Oct 14th, 2009 at 03:09 PM.
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?
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.
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:
And click on Edit Columns.
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.
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();
}
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).
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();
}
}
Last edited by gtyagi; Oct 15th, 2009 at 02:30 PM.
[RESOLVED] SqlObject data Source + Update
Reply With Quote
