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]....
I was wondering in which order MSSQL executes a command. Say if I have this statement:
select top 1 * from table1 t1
inner join table2 t2 on t2.productid = t1.id
order by t1.buydate desc
what is done first:
- find if there's a matching record in t2 through the join
OR
- sort the results in t1 based on the buydate?
And when I have a large database with many records in both t1 and t2, what are possible performance issues I might hit?
I have set up Linq to sql. I now need to fill a formview with a select that does a join across a few tables. Have not been able to find any example or tuts that go beyond the simple select. Any suggestions? Can this even be done?
View 1 RepliesTo write a join query with or condition. It means a query has two inner join, here it is possible to fetch the records, if one inner join is true and other is false. I got a record when only two join conditions are true.
View 1 RepliesI 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 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.
myCommand.CommandText = "update Stutable set password as '" & TextBox2.Text & "' where rollno = '" & TextBox3.Text & "'"
View 21 RepliesI 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 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 new to LINQ. I have a GridView which I am populating using LINQ. My LINQ statement is taking query string from previous page. The query string is in string format. Here is the code:
protected void Page_Load(object sender, EventArgs e)
{
string getEntity = Request.QueryString["EntityID"];
[code]...
let me know the types of joins available in SQL, so far i've used only Inner joins and left joins.
But have been clear and comfortable using inner joins only.
give me a more clear picture on all the types of joins available and their importance.
I have three tables nameTbl contains the following columns: BarID, Name, Description locTbl contains locID and Location namelocationTbl contains BarID(from the nameTbl) and locID(from the locTbl)
View 2 RepliesI have a gridview with a sqldatasource with SelectCommand as below:
[Code]....
but it's not workling.....
I need to join some tables to get the matching records. I often have to ask around to see how certain tables could be joined. Is there any way to query the information schema or some thing else to see what columns/values match in certain tables in order to figure out how tables should be joined.
View 4 RepliesAnyone know if it's possible to inner join tables using two different connection strings? I'm using vb.
View 3 Repliesi have two queries they are:
query1:
select CustID,NoOfChits from tblCust1 where [Group]='A' and CustID='a001'
query2:
select count(TKCustID) as Taken from tblAuctionGroup where [Group]='A' and TKCustID='a001'
Results of 1st query was:
----------
CustID NoOfChits
a001 2
Results of 2nd query was:
----------
Taken
4
But i want to like below:
--------------------------
CustID NoOfChits Taken
a001 2 4
in the above queries, 1st query displayed two columns and 2nd query displayed one column. but i would like to append the both results in single table or single record, i mean all results should be in same row in same result.
I can join with a regular SQL table fine but this won't work:
SELECT normalTbl.*, tmp.fld1 FROM normalTbl
LEFT JOIN #Temp tmp ON tmp.joinID = normalTbl.joinID
All tmp.fld1 values are shown as NULL even though fld1 contains valid values and the joinID matches the normalTbl (when I call SELECT * from #Temp). If I do the same exact thing with a regular table, fld1 shows the correct value. What am I doing wrong?
pull data from 3 databases, same query with the same result set (data can vary) and i want to combine those results in to a single datareader so i can easily dump it to a gridview.
View 2 RepliesI would like to create a gridview where all of the items in one table are listed and a checkbox is checked if that item also shows up in another table. The difficulty comes in the fact that table are not only in different databases, but possibly on different servers.
Is there any way to do this via ASP.NET connection strings, so that it doesn't matter where the databases live? I'd rather use connection strings, because I don't want to hard-code credentials into the queries (OPENROWSET or whatnot).
How to use Left and Right Outer Join in C# using Linq with Eg.
View 2 Replieshow to do this query in EF1:
[Code]....
i am trying to use predicatebuilder for a linq to sql query but i cant seem to get a join to another table. in my model, i have a table for applicants and another for job applications.. each applicant can have several job applications.
i expect in linq to sql to be able to do something like;
p.jobapptable.jobappdate where p is the applicant.
this isnt the case..i am trying to use predicatebuilder to search through applicants based on the job applications they have made? how`can i get linq to sql to show the foreign key relationships?
I have 2 different datatables. 2 datatables from 2 different servers.
My first datatable is this:
SELECT DISTINCT a.name, a.create_date, a.Modify_Date, a.Object_Id
FROM dbname.sys.objects a
and my 2nd datatable is the same, but from a different server connection string
SELECT DISTINCT b.name, b.create_date, b.Modify_Date, b.Object_Id
FROM dbName.sys.objects b
Basically, I want to compare which procedures are on one server and not the other (dev and prod)
So after I have my 2 datatables i want to create a data relationship on the name and then do a left join to see whats in a and not b.
my stored procedure is always extract some data into temp table such as"select name,address,content into #TEMP1 from table1,table2,table3"
finally, select another table together with #TEMP1 to extract all desire data from one queryselect T.name,T.address,T.content,C.other from #TEMP1 T, otherTable C
where T.ID=C.IDI would like to ask if i want to join one more table T2 into final query with "otherTable left C outer join on C.ID=T2.ID"