|
You can't pass anonymous types to views as models (or anywhere else for that matter, they can only be used in the functions they are defined). You need to convert the data you get back via Linq into a list of concrete classes that you can pass to your view. You have a CarCost class but you're not using it, this code
var carCostLinq = from e in db.Cars
join q in db.Costs on e.CarId equals q.CarId
select new {Car = e, Cost = q};
returns an anonymous type (new { .. }). You need to return CarCost instead, as the other member advised
var carCostLinq = from e in db.Cars
join q in db.Costs on e.CarId equals q.CarId
select new CarCost {car = e, cost = q};
I think the reason that member's code didn't work for you was a case issue, your properties are "car" and "cost" rather than "Car" and "Cost". I'd advise you take some time to get to know .net fundamentals such as types, linq etc, as you really should be able to sort simple case issues yourself if you actually understand what it is you're doing.
|
|
|
|
|
I have a datagridview that I changed the name Jones to Brown in a row. Now I need to update my SqlServer table with that change.
I am using C# for the code and SqlServer 2010 for the database and SharePoint to display the data as a webpart.
I have read almost everything on the internet and can't seem to find an answer to my issue.
Datagridview
KeyPer Test
Name
Doe
Smith
Jones
I change a name in the Name column
KeyPer Test
Name
Doe
Smith
brown
I'm having trouble getting past this setting of the parameter for ID.
It is giving me am error: The name ID does not exist in the current content.
Code Behind
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
namespace StaffingWebParts.KeyPerTest
{
public partial class KeyPerTestUserControl : UserControl
{
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLStaffingConn"].ConnectionString);
SqlCommand cmd = new SqlCommand();
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
DataTable dt = new DataTable();
dt.Columns.Add("Name");
dt.Columns.Add("VDCIDIQ");
dt.Columns.Add("VDCFFS");
dt.Columns.Add("VDCHIM");
dt.Columns.Add("VDCWEBHOSTING");
dt.Columns.Add("VDCCWF");
SqlDataReader myReader = null;
SqlCommand cmd = new SqlCommand("SELECT distinct Name, VDCIDIQ , VDCFFS, VDCHIM, VDCWEBHOSTING, VDCCWF from CMS_Key_Personnel where Name <> ' ' order by Name");
cmd.Connection = conn;
conn.Open();
myReader = cmd.ExecuteReader();
while (myReader.Read())
{
DataRow dr = dt.NewRow();
dr[0] = myReader["Name"].ToString();
dr[1] = myReader["VDCIDIQ"].ToString();
dr[2] = myReader["VDCFFS"].ToString();
dr[3] = myReader["VDCHIM"].ToString();
dr[4] = myReader["VDCWEBHOSTING"].ToString();
dr[5] = myReader["VDCCWF"].ToString();
dt.Rows.Add(dr);
}
gvKeyPersonnel.DataSource = dt;
gvKeyPersonnel.DataBind();
conn.Close();
}
}
protected void Button_Update(object sender, EventArgs e)
{
using(SqlCommand cmd = new SqlCommand())
{
cmd.Connection = conn;
conn.Open();
cmd.CommandText = "UPDATE SP2010_EDCStaffing_AppDB.dbo.CMS_Key_Personnel SET Name = @Name WHERE ID = @id";
cmd.Parameters.Add("@Name", SqlDbType.NVarChar, 75);
cmd.Parameters.Add("@id", SqlDbType.Int).Value = id;
cmd.ExecuteNonQuery();
conn.Close();
}
}
}
}
|
|
|
|
|
Look at this line:
cmd.Parameters.Add("@id", SqlDbType.Int).Value = id;
What is "id" (on the RHS of that assignment statement) here? I think that should be it.
-- EDIT
I don't see any "ID" column you are fetching while binding the data to the grid. So, I am not sure from where you will be using that in your SQL statement. Perhaps a label or something else somewhere on the page? 
You have just been Sharapova'd.
modified 11-May-15 2:40am.
|
|
|
|
|
The id is the [ID] [int] IDENTITY(1,1) NOT NULL, field. Do I need to show it on my datgridview?
|
|
|
|
|
When I use this code
cmd.Parameters.Add("@id", SqlDbType.Int).Value = id;
I am getting this error: The name 'id' does not exist in the current context
|
|
|
|
|
Norris Chappell wrote: I am getting this error: The name 'id' does not exist in the current context
Yes, that's what I was referring to. You don't have a variable "id" from which you are assigning your "@ID" parameter a value.
Even though your "ID" is an identity column, you will still need its value in order to execute your UPDATE query. How else will you be uniquely identifying the row which you need to be updated? 
Here's what you can do (top off my head, as I worked on ASP.NET WebForms a long time ago ):
- Select and bind your ID column to your grid but make it invisible.
- Use DataKeys[^] property of grid for setting the "ID" field.
- While updating a row, retrieve the "ID" value for it using its DataKeys property (and possibly its index).
- Use this value to pass in to your update query, i.e. the value for your "@ID" parameter.
You have just been Sharapova'd.
|
|
|
|
|
I was able to do #1.
#2 - Now to get the DataKeys what is the Code behind to do that?
|
|
|
|
|
Hi,
I'm getting Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index
Here is my code?
protected void Button_Update(object sender, EventArgs e)
{
using(SqlCommand cmd = new SqlCommand())
{
foreach (GridViewRow row in gvKeyPersonnel.Rows)
{
cmd.Connection = conn;
conn.Open();
cmd.CommandText = "UPDATE SP2010_EDCStaffing_AppDB.dbo.CMS_Key_Personnel SET Name = @Name WHERE ID = @id";
cmd.Parameters.AddWithValue("@id", Convert.ToInt32(gvKeyPersonnel.DataKeys[row.RowIndex].Values[0]));
cmd.Parameters.AddWithValue("@Name", Convert.ToString(gvKeyPersonnel.DataKeys[row.RowIndex].Values[1]));
int numRegs = cmd.ExecuteNonQuery();
}
conn.Close();
}
}
Update the two Parameters but still gets the OOR error.
modified 11-May-15 14:22pm.
|
|
|
|
|
Your issue is probably on cmd.ExecuteNonQuery not returning anything from the query remove the int numregs = and see what you get
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft,
I am now getting Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index. I think something is wrong with this parameter? cmd.Parameters.AddWithValue("@Name", Convert.ToChar(gvKeyPersonnel.DataKeys[row.RowIndex].Values[0])); I don't know if I should have two Datakeys?
|
|
|
|
|
What I would do is create 2 variables to hold the cell values, put a break point in the loop and check the values before they are passed to the parameters.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Norris Chappell wrote: cmd.Parameters.AddWithValue("@Name", Convert.ToString(gvKeyPersonnel.DataKeys[row.RowIndex].Values[1]));
This statement shouldn't be there, you should only have your "ID" field in the DataKeys. Instead, you should be having something like this:
cmd.Parameters.AddWithValue("@Name", row.Cells[IndexOfCellContainingNameField].Text);
Regarding the IndexOutOfRangeException you are getting, I think if you only have the "ID" in the DataKeys property (i.e. remove the "Name" from DataKeys and pass its value like the above line), that won't come up - assuming your RowIndex is not the culprit.
Not related to your current question, but I think if you are having to iterate all rows in the grid for updating a row, you might want to have a look at examples here[^], here[^] or here[^].
You have just been Sharapova'd.
|
|
|
|
|
Hi, I don't have any of my fields with a label. I'm still getting an Out of Range error.
protected void Button_Update(object sender, EventArgs e)
{
using (SqlCommand cmd = new SqlCommand())
{
foreach (GridViewRow row in gvKeyPersonnel.Rows)
{
cmd.Connection = conn;
conn.Open();
cmd.CommandText = "UPDATE SP2010_EDCStaffing_AppDB.dbo.CMS_Key_Personnel SET Name = @Name, VDCIDIQ = @VDCIDIQ, VDCFFS = @VDCFFS, VDCHIM = @VDCHIM, VDCWEBHOSTING = @VDCWEBHOSTING, VDCCWF = @VDCCWF WHERE ID = @id";
cmd.Parameters.AddWithValue("@id", Convert.ToInt32(gvKeyPersonnel.DataKeys[row.RowIndex].Values[0]));
cmd.Parameters.AddWithValue("@Name", row.Cells[1].Text);
cmd.Parameters.AddWithValue("@VDCIDIQ", row.Cells[2].Text);
cmd.Parameters.AddWithValue("@VDCFFS", row.Cells[3].Text);
cmd.Parameters.AddWithValue("@VDCHIM", row.Cells[4].Text);
cmd.Parameters.AddWithValue("@VDCWEBHOSTING", row.Cells[5].Text);
cmd.Parameters.AddWithValue("@VDCCWF", row.Cells[6].Text);
cmd.ExecuteNonQuery();
}
conn.Close();
}
}
|
|
|
|
|
Just a quick suggestion - try your row.Cells[1].Text, row.Cells[2].Text, ... statements with [0], [1], and so on - i.e. with a Zero-based index. I think that should be it.
If not, without a debugger, I can't help you any further, I am afraid.
You have just been Sharapova'd.
|
|
|
|
|
Caveat, can the user reorder your DGV columns, this would change the index of the cells and screw up your code.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
If you put a breakpoint on cmd.CommandText, press F5 and step through the code when the debugger hits this line. Look to see which one of those lines the application crashes on. As a hint, you can use Visual Studio to inspect the values of each cell to see what's in it.
|
|
|
|
|
Hi Pete, It crashes on this line:
cmd.Parameters.AddWithValue("@id", Convert.ToInt32(gvKeyPersonnel.DataKeys[row.RowIndex].Values[0])); The value is null.
|
|
|
|
|
Now that you know the line, you need to identify which part is the problem. Is it the DataKeys[row.RowIndex] or is it Values[0]? My suspicion is that it's the latter - in other words, you don't actually have a value in there.
|
|
|
|
|
I don't see the out of range error anymore. It doesn't update but deletes the first record in the database. I getting this error: The variable name '@id' has already been declared. Variable names must be unique within a query batch or stored procedure.
|
|
|
|
|
using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Configuration;
using System.Web.UI.WebControls.WebParts;
namespace StaffingWebParts.KeyPerTest
{
public partial class KeyPerTestUserControl : UserControl
{
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLStaffingConn"].ConnectionString);
SqlCommand cmd = new SqlCommand();
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
DataTable dt = new DataTable();
dt.Columns.Add("ID");
dt.Columns.Add("Name");
dt.Columns.Add("VDCIDIQ");
dt.Columns.Add("VDCFFS");
dt.Columns.Add("VDCHIM");
dt.Columns.Add("VDCWEBHOSTING");
dt.Columns.Add("VDCCWF");
SqlDataReader myReader = null;
SqlCommand cmd = new SqlCommand("SELECT ID, Name, VDCIDIQ , VDCFFS, VDCHIM, VDCWEBHOSTING, VDCCWF from CMS_Key_Personnel where Name <> ' ' order by Name");
cmd.Connection = conn;
conn.Open();
myReader = cmd.ExecuteReader();
while (myReader.Read())
{
DataRow dr = dt.NewRow();
dr[0] = myReader["ID"].ToString();
dr[1] = myReader["Name"].ToString();
dr[2] = myReader["VDCIDIQ"].ToString();
dr[3] = myReader["VDCFFS"].ToString();
dr[4] = myReader["VDCHIM"].ToString();
dr[5] = myReader["VDCWEBHOSTING"].ToString();
dr[6] = myReader["VDCCWF"].ToString();
dt.Rows.Add(dr);
}
gvKeyPersonnel.DataSource = dt;
gvKeyPersonnel.DataBind();
if (gvKeyPersonnel.Columns.Count > 0)
gvKeyPersonnel.Columns[0].Visible = false;
else
{
gvKeyPersonnel.HeaderRow.Cells[0].Visible = false;
foreach (GridViewRow gvr in gvKeyPersonnel.Rows)
{
gvr.Cells[0].Visible = false;
}
}
conn.Close();
}
}
protected void gvKeyPersonnel_RowUpdating(object sender, EventArgs e)
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = conn;
conn.Open();
foreach (GridViewRow row in gvKeyPersonnel.Rows)
{
SqlDataAdapter da = new SqlDataAdapter("", conn);
cmd.CommandText = "UPDATE SP2010_EDCStaffing_AppDB.dbo.CMS_Key_Personnel SET Name = @Name, VDCIDIQ = @VDCIDIQ, VDCFFS = @VDCFFS, VDCHIM = @VDCHIM, VDCWEBHOSTING = @VDCWEBHOSTING, VDCCWF = @VDCCWF WHERE ID = @id";
cmd.Parameters.AddWithValue("@id", Convert.ToInt32(gvKeyPersonnel.DataKeys[row.RowIndex].Values[0]));
cmd.Parameters.AddWithValue("@Name", row.Cells[1].Text);
cmd.Parameters.AddWithValue("@VDCIDIQ", row.Cells[2].Text);
cmd.Parameters.AddWithValue("@VDCFFS", row.Cells[3].Text);
cmd.Parameters.AddWithValue("@VDCHIM", row.Cells[4].Text);
cmd.Parameters.AddWithValue("@VDCWEBHOSTING", row.Cells[5].Text);
cmd.Parameters.AddWithValue("@VDCCWF", row.Cells[6].Text);
cmd.ExecuteNonQuery();
}
conn.Close();
}
}
}
}
|
|
|
|
|
The reason you're getting this exception is because you're attempting to add the parameters to the same command object inside a loop. This will work on the first trip through the loop, but will fail on the next trip through. A way around this is to do cmd.Parameters.Clear(); before you start adding the parameters in. This will ensure the parameters collection has no entries on each journey through the loop.
|
|
|
|
|
Okay that got rid of that error. Thanks but my update delete all of the fields. I now have in my database the id's number of all of the previous rows. How can I just update the database with what I have in my datgridview?
|
|
|
|
|
As I don't know what keys are on either side of the equation here, I can't help you. It's up to you now. Step through the code and note the values that you're seeing - in particular, what's getting assigned to @ID. That's the one that's causing you issues, so that's the line that you are going to have to inspect. It's pretty apparent that the wrong value is being written to that field - you are in the best position to see what that value is.
|
|
|
|
|
Thanks for your help. I am now writing out a record to the database. The problem is writing the same first record over 42 times. You are correct the @ID is the culprit. I got to figure how to increment it.
|
|
|
|
|
Looking at your code, you have put the ID in cell 0 and made it invisible. Why not just use the contents of that cell for your ID instead?
cmd.Parameters.AddWithValue("@id", row.Cells[0].Text));
cmd.Parameters.AddWithValue("@Name", row.Cells[1].Text);
cmd.Parameters.AddWithValue("@VDCIDIQ", row.Cells[2].Text);
cmd.Parameters.AddWithValue("@VDCFFS", row.Cells[3].Text);
cmd.Parameters.AddWithValue("@VDCHIM", row.Cells[4].Text);
cmd.Parameters.AddWithValue("@VDCWEBHOSTING", row.Cells[5].Text);
cmd.Parameters.AddWithValue("@VDCCWF", row.Cells[6].Text);
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
