I am using Linq to SQL. Tables have timestamp columns and I am using regular Linq methodology in ASP.NET MVC.I do use detached entities and always try to do updates without first querying the database for the original entity. To update an entity I use context.Attach(entity, true) followed by context.SubmitChanges() and all is fine. The sql statement that gets executed looks like:
[Code]...
I have a method to execute two "Update" sql statements.
The first one works fine and the second one I can't get to work for nothing.
Here is the entire method:
[Code]....
The error I am getting is "Incorrect syntax near '05'."
It is occuring on the next to the last line which is:
dc.ExecuteCommand(sql);
I update the variable "sql" first so I can debug. At the time it crashes the value of sql is:
"Update Blogs set Status = 'Archived', ArchiveDate = 06/22/2010 05:14:46 PM Where (BlogID != 1) and (Status = 'Published') and (AuthorID = 9e37e028-c205-4903-920e-e8643b30a724)"
It appears to be the ArchiveDate that is causing the problem here. The "05" is the hour of the time portion. Both ArcgiveDate and PublishDate are defined exactly the same in the database. If you look at the first dc.ExecuteCommand it does exactly the same
thing with PublishDate (DateTime column) in the same table and it works just fine.
Then I modifed the code and remove the ArchiveDate from the sql statement then I get the error:
"Incorrect syntax near 'e028'."
which as you can see is part of the AuthorIDs value in the where clause. So I am assuming both the date and the Guid / Author ID is causing problems. I have tried enclosing the values in quotes but I just cant make this work even though it seems to be working fine in other places.
I even remove the code for the author ID and left in the code for the ArchiveDate and it still does not work so I have proved that both are causing a problem.
I am trying to do an insert statement to a table from classic ASP.I tried the below:
[Code]....
But, I am getting the error as: Incorrect syntax near the keyword 'where'.How to insert data, using a where clause in the above scenario?
I have the following statement:
List<string> tracks = new List<string> { "ABC", "DEF" };
var items = (from i in Agenda.AgendaSessions
select i).Where(p => p.Tracks.Any(s => tracks.Contains(s.Code)));
this returns all sessions which track contains either ABC or DEF, now when I rewrite the statement like the following, it returns All sessions regardless, as if the clause always yeilds into true,
var items = from i in Agenda.AgendaSessions
where i.Tracks.Any(s=> tracks.Contains(s.Code))
select i;
Update
if there are other clauses within the where, does that affect the results?
Ive created a new LINQ To SQL class and dragged several tables across from my server, in my code I access them like this
[Code]....this is all ok, however, I have several stored procedures that I want to use as well, but when I drag the stored procedures across I get a compilatiopn error
Error 60 'DB_BL.DB_BLDataContext': type used in a using statement must be implicitly convertible to 'System.IDisposable'
how do I use stored procedures from LINQ with a using clause ?
I've already spent hours in searching through the forum to be able to do the following:I have a SQL datasource defined with a checkbox in it. Purpose is to update one column (userid) behind the table for only that row when the checkbox is selected.This is the code I'm currently having:
[Code]....
[Code]....
I must have a LINQ Statement does i can show if my Time in the DB <= as the Time where i become from my Session +60 Minutes ??
I'm having trouble writing what should be a simple sub-query using LINQ to Entities. I have two tables: Customers and Orders that have a relation on the CustID field. Not all Customers have a record in the Orders table, while some have mutiple records. In traditional SQL, you could write the query like this: SELECT * FROM Orders where CustID IN (SELECT CustID FROM Customers) I know this could be done as a JOIN in both SQL and L2E, but my actual query is more complex (about 8-9 joins), so I am hoping to find a L2E sub-query equivalent. Something like this:
[Code]....
I know LINQ to Entities does not support the " IN " clause, so I am looking for something that will work in its place.
We have an application developed on VS2008 [Asp.Net & C#], in this application we provide the facility to the user to write his/her business logic code. For that purposes we maintained a Businesslogic.cs file where user can write his/her code and our application build it as a BusinessLogic.dll file at runtime.
But the problem is when we introducing Linq statement/code into the BusinessLogic.cs file then it generated compilation error [CS1026 ].
Note that, when we build the dll from VS2008 environment then it working fine with the same code which we write into the BusinessLogic.cs & don't generated any compilation error.
I am writing a simple form that will take in data and insert it into a db if it's a new record and update the existing record if it already exists. The insert works fine, but the update isn't working at all, yet the code looks solid to me and everything I've compared it to in my google searches.
[Code]....
I am trying to update a Single Record in a table. I also trying to join columns of that single record with another table to update prices the prices of that record.
Here is my current query.
[Code]....
myCommand.CommandText = "update Stutable set password as '" & TextBox2.Text & "' where rollno = '" & TextBox3.Text & "'"
View 21 RepliesI am trying to create a multi column listbox by combining fields in Linq select statement, as Let Name = Last + First. The problem is how to pad the Last column with spaces as you would in SQL using REPLICATE so when I bind the listbox to the data, it looks like two columns.
View 5 Repliesi built an oledb update stmt using the syntax from a 4 guys tutorial, http://www.4guysfromrolla.com/articles/071002-1.3.aspx , but am getting the above error
this is the update stmt
Dim strSQL As String = "UPDATE [orientationData] SET " & _
"[orientationTime] = @orientationTime, [targetAttendeeCount] = @targetAttendeeCount, " & _
"[locationRoom] = @locationRoom, [locationStreet] = @locationStreet, " & _
"WHERE [orientationDate] = @orientationDate"
sample how i build an update field into the update string
Dim targetAttendeeCount As Integer = _
CType(e.Item.Cells(0).FindControl("targetAttendeeCountTextBox"), TextBox).Text
Dim parmTarAttCnt As OleDbParameter = _
New OleDbParameter("@targetAttendeeCount", OleDbType.Integer)
parmTarAttCnt.Value = targetAttendeeCount
myCommand.Parameters.Add(parmTarAttCnt)
printing out the strSQL gives
sql string = UPDATE [orientationData] SET [orientationTime] = @orientationTime, [targetAttendeeCount] = @targetAttendeeCount, [locationRoom] = @locationRoom, [locationStreet] = @locationStreet, WHERE [orientationDate] = @orientationDate
so none of the variables are being resolved, i know they are being returned from, in this case a datagrid, because i have printed them separately, so am not sure at this point how to get the vars to resolve, i was hoping to not have to use all the quotes + apostrohes and thought the @syntax was simplifying that but not so. is there some way to code this with the @'s?
in generate the Stored Procedures when using Create TableAdapter configuration Wizard in VS.it can not generate the Stored Procedures for Delete and Update Commands, but it generated for Select and Insert. i tried many times, but still get the same problem.
View 2 RepliesI am having trouble with this I have 3 Data tables i use over and over again which are cached I would like to write a LINQ statement which would do the following is this possible?
T-SQL VERSION:
SELECT P.[CID],P.[AID]
,B.[AID], B.[Data], B.[Status], B.[Language]
FROM MY_TABLE_1 P
JOIN
(
SELECT A.[AID], A.[Data], A.[Status], A.[Language] FROM MY_TABLE_2 A
UNION ALL
SELECT B.[AID], B.[Data], B.[Status], B.[Language] FROM MY_TABLE_3 B
) B on P.[AID] = B.[AID]
WHERE B.[Language] = 'EN' OR B.[Language] = 'ANY' AND B.STATUS = 1 AND B.[Language] = 'EN' OR B.[Language] = 'ANY' AND B.STATUS = 1
Then i would like it to create a result set of the following
Results:
|CID|AID|DATA|STATUS|LANGUAGE
OK So I know WHY I am having the error I am getting. I don't know HOW to fix it. Basically, if the user doesn't have a certain permission, I need to join another table. But .NET is so picky I can't just make two different queries in an IF statement and then use it outside of the if statement. I can think of some ugly work arounds for this, but I would rather not. I am fairly new to .NET I know just enough to be dangerous.
[code]....
I get the error: Unable to cast object of type
'System.Data.Linq.DataQuery1[VB$AnonymousType_111[System.Guid,System.String,System.String,System.String,System.String,System.Nullable1[System.DateTime],System.Nullable1[System.DateTime],System.Nullable1[System.Guid],System.Nullable1[System.DateTime],System.Nullable1[System.Guid],System.Nullable1[System.DateTime]]]' to type 'System.Collections.Generic.IEnumerable`1[Ten11CRMLib.Company]'.
because of this: Dim l As IEnumerable(Of Company) its not just IEnumerable of a Company, its got the source in it. I have to explicitly select source to use it in my datagrid. Can I make Dim l something that will make it stop complaining?
I am running the folowing simple test code:
[Code]....
But the UpdateUser field does not update - it remains at NULL. No errors occur. I can use similar code to update table fields in other dbmls successfully.
I have a table with Id, Name,...IsActive, etc. If user wants to delete a record from the user, he/she should update the field IsActive to false. I am able to acheive this functionality using the below steps,
1. Query the database for the row which is to be updated.
2. Update the property IsActive to false.
3. Submit the changes to the database.
This way i am successful. But i have a query here is that, when we execute steps 1 and 3, there will be two trips to the database for getting the required object and again updating the same object to the database.
Is this not a performance hit having multiple calls to DB for a simple operation.?
Do we have any other go, where in within a single call to database, can i update a field of row?
I'm studing Linq on TheBeerHouse Project,I would create a my Linq Version.I have a big problem with Updating, this i a part of source :
protected static ArticlesDataContext GetContext()
{
return new ArticlesDataContext(Settings.ConnectionString);
[code]...
I have a select statement:
cmd = new SqlCommand("select lab_key from tblorderwhere", connection);
I would like to add a where statement that pass variable to the select statement. How I can do this?
Where lab_key =ChargeFine( that is my variable)
i trying following query Select
View 4 RepliesI like to use my own stored procs as much as possible.
That way I know exactly what's happening and I can explain and control things.
I'm using stored procs with LINQ to SQL for CRUD operations.
For select, insert and delete - using stored procs with LINQ to SQL is pretty straightforward.
However, if I want to use my own custom stored procs with L2S for updates what's the best way to accomplish this?
Can you provide some code that shows the best approach that you would personally choose for this?
I have a form that gets populated by LINQ and I want to be able to update the database using that same form. The form is populated by grabbing a querystring (for example, "edit=10") and searching the primary key for that record. So far so good. I have a button attached to the function below which is supposed to use LINQ to update the database with data from the form. I get no errors, but for some reason, it is only updating one field (Last_Updated) instead of all of them.
[Code]....
i want to update many rows with difference values inside table. i'm coding a timetable.
View 3 Replies
