ADO.NET :: How To Write Queries To Select Data From Different Tables With Condition
Jan 23, 2011eg: thiss is the ordinary query
[Code]....
how in linq??
eg: thiss is the ordinary query
[Code]....
how in linq??
Suppose we want to select the data from the database then we write the query for that. Example:
SqlConnection con=new SqlConnection(Connetion name) string selectPkId = @"SELECT PK_ID FROM TABLE" SqlCommand cmd=new SqlCommand(selectPkId ,con);
So,my question is that why we basically use @ before the sql query.If I don't use @ before that then it again work fine (does not give any error), then what is need of using "@"?
I am moving to the world of ASP.NET from an Access VBA / VB6 background and having read a few books on the subject I am still confused about communicating with SQL server in VB with ASP.NET.
As a very quick explination I am used to communicating with the server by passing strings containing the data I want to write (or read) such as:
[Code]....
I do not seem to be able to work out from ALAX, Dynamic Data, Bound Controls, DataViews, connecting to services with SOAP, DataSources and all of the other methods of database communication in ASP.NET which is the right system to do the simple reads and writes in SQL that I want to do.
At present I have a page with all of the controls on it required to collect a block of data I want to insert, it is all now fully validated but although I can build the SQL INSERT command in a string I cannot figure out how to fire it over to the local SQL Express server. Am I even doing this correctly or do I need to look at other controls?
how to use jquery to write sql queries. consider a webservice call using jquery to asp.net webservice, sending an sql query and getting back the results in json format. but there's a problem with the connection string because it must not be available if a user views the source of the page.
View 1 RepliesSELECT 1000000*QuantityNC/Quantity AS PPM FROM [table1] However I need my WHERE clause to do thisI need the above calculation depending on BusUnit, which is linked to PartNumber, which is in the above table. also it has to be by Company (which is selected from a dropdown). And also based on the current month and year.
PartNumber and CompanyName are in [table1]
BusUnitID, PartNumber, SupplierID, are in tblParts
BusUnitID and BusUnit are in tblBusUnits
I have to write data to multiple (two) tables.
tbl1 has a column "ORDERID" with auto increment. I have to get this "ORDERID" whenever I write to tbl1 (Insert new record)
tbl2 has a coumn "ORDERID" as foreign key, I have to write "ORDERID" in tbl2 received from the tbl1.
How to do it using SQL ( I am not using StoredProcedure)
Is it possible to write multiple queries on a single button click? if yes then how?
View 5 RepliesI need to write condition on client side within GridView itemtemplate.
<% if(Eval("item").Contains("keyword"){%>
<img src='<# Eval("imagepath") %>' />
<%}
else if(Eval("item").Contains("keyword2")){
%>
I really have no idea what i am doing here, but all I want to do is display a one to many list from two tables in the database in a view.
I have a ChartController with the following code:
public ActionResult ChartCategoryOne()
{
var chart = from e
in db.PTP_Exercise
[Code]....
Now I need a view. The view that VS2010 creates via the PTP_AthleteExerciseResult model looks like just what I need. But I get the following error:
The model item passed into the dictionary is of type 'System.Data.Entity.Infrastructure.DbQuery`1[PersonalTrainerPro.Models.PTP_Exercise]',
but this dictionary requires a model item of type 'System.Collections.Generic.IEnumerable`1[PersonalTrainerPro.Models.PTP_AthleteExerciseResult]'.
I'm not sure why it is passing PersonalTrainerPro.Models.PTP_Exercise, which is one of my automatically generated models derived from the database in the edmx and I have no idea how to set it up to pass IEnumerable instead of DbQuery. I actually have no idea what it means or even if I'm heading in the right direction.
anyone know that can we used select statement to called queries table in database or not?usually people used select statement to called common table in database.
View 4 RepliesIs there a way to select data from multiple tables other than the UNION ALL statement. Addtionally I need to select data from a different database.
My current statement is as follows (there are alot more fields but I have shortened it ):
SELECT server.dbo.SRFILE.SR_GROUP, server.dbo.SRFILE.SROWNERSHIP, server.dbo.SRFILE.SRCONMAE, BSFLBWF_1.Incidentx,
BSFLBWF_1.Severityx FROM server.dbo.SRFILE INNER JOIN
BSFLBWF AS BSFLBWF_1 ON BSFLBWF_1.Incidentx = server.dbo.SRFILE.SRONUMBER
WHERE (BSFLBWF_1.Closex IS NULL).........
this is my select Statement.:-
Select (datediff(day,db_TargetDate,getdate())) from tbl_abc
in select statement only i wanted only those record in which datedifference is less than 5.
declaration of variable & then conditional checking is not wanted me because i wanted result is into select statement only & not using print statement.
I was wondering how Linq to Sql determines which tables to write to when given primary/foreign Key relationships. I can (hopefully) better explain using an example. I have the following tables:
Customer CustomerStatus CustomerType CustomerAddresses
Therefore I have a Customer Entity, a CustomerStatus entity, a CustomerType Entity and a List of CustomerAddresses. In my code, I load up the Customer Entity and the List of CustomerAddresses because that's all I need. For some reason Linq to Sql is trying to insert a new Customer Status record. I have no idea why its doing this.
Can anyone please explain to me how linq to sql determines which table(s) will be written to based on the entities above?
how can I create stored procedure and write my select statement in it, I know how to create dataset then put type stored procedure and assign it to the sp ... what I want is writing my sp... how can I make it ?
check the following code, this is what I want to write put I don't know where or how !
CREATE STORED PROCEDURE SP_CATEGORY
@CATEGORY VARCHAR(30)
AS
BEGIN
SELECT LATIN_NAME, ENGLISH_NAME, ARABIC_NAME, CATEGORY
FROM FLORA, CATEGORY_LIST
WHERE FLORA.CATEGORY=CATEGORY_LIST.CATEGORY_NAME AND CATEGORY_LIST.CATEGORY_NAME IN (SELECT * FROM SPLITLIST(@CATEGORY, ','))
END
where can I write this code ?!
How to change to use like instead of = ?
([Card_no] = @Search)
Moreover, if i want to show all in default, this method force to user to type something first. How to show all in default when using control parameter?
SelectCommand="SELECT [Serial_no], [Transaction_Date], [Card_no], [Company],[IsCredit], [Fee] FROM [CarPark] WHERE ([Card_no] = @Search) or ([Company] = @Search)"
UpdateCommand="UPDATE [CarPark] SET
Card_no=@Card_no,
Company=@Company,
IsCredit=@IsCredit,
Fee=@Fee........
I want to retrieve the name and phone columns from all the tables in my database not in systables.
Ok this works but i dont want to get it from just the test table I want to get it from all the tables that I create
"USE mrpoteat SELECT name, phone FROM mrpoteat.dbo.test where name = name and phone = phone"
SELECT u.Email FROM UserProfiles p, aspnet_Membership u WHERE p.RulesCheckBox = 'True' AND u.UserId = p.UserId This gives me a list of registered member email addresses that have selected the RulesCheckBox in their profile, so I can email them with an email script. There is a relationship between the Membership table and the UserProfile table, so it finds the email addresses from the Membership table where that user has the RulesCheckBox checked in their profile (in UserProfiles table). I'm wanting to add some additional names to this list, from a different table. The purpose is so I can manually add email addresses to the additional table, so the additional email addresses will also receive the same email, even though they aren't registered members of the site. So how can I adjust that SELECT statement so this one is combined with it somehow?:
SELECT Email FROM ExtraEmails WHERE Rules = 'true'
[Code]....
Select statement across three tables
I am trying to count records belonging to a certain person in three different tables in a single select statement. The database looks like this:
Player
-PlayerId
-Name
Game
-GameId
-TournamentId
...
Goal
-GameId (FK->Game.GameId)
-PlayerId (FK->Player.PlayerId)
-Assist (FK->Player.PlayerId)
Card
-CardId
-GameId (FK->Game.GameId)
-PlayerId(FK->Player.PlayerId)
I want to select the number of goals, assist and cards a player has in a specific tournament. I tried with this query but then the goal and card column shows the sum of the number of rows matching the playerid in the Goal table and the Card table...For example if there is two matching rows in the goal table and three matching rows in the card table, I get 5 in both these columns in the resultset.
[Code]....
I'm using ASP.NET MVC to create a small web apllication.
Here is my database And here is my class
[Code]....
If I have a huge Order-table and I want to display records from it using paging, what is the best way to select the records from the database?
If I first do like this:
[Code]....
..and then select the records I want based on RowNum it seems like I select ALL records in the first query anyway. I guess I want to do something like SELECT TOP @PageSize FROM Orders...
How to write select query to get counts in row values
View 6 RepliesI'm facing a problem in our current running application. There is a table in database which has 67 columns,among these 18 columns are nvarchar(max) datatype and contains 30K records. Problem is now that SELECT * FROM myTable it takes around 3 to 4 seconds. Which is degrading our application's performance.
View 12 RepliesI'm writing a SQL query where I need to detect if the value of an integer column in one table is contained within the value of a varchar column of another table. For example:
Table1 has columns A, B, C
A = 1
B = 2
C = 3
Table2 has columns D, E, F
D = "1,2,3,4,5"
E = "2, 5, 10, 15"
F = "1, 3, 49"
How would I write SQL SELECT statement to determine if the value in Table1.A is in Table2.D, Table1.B is in Table2.E, and Table1.C is in Table2.F?
I have two tables:
GalleryAlbums and GalleryPhotos.
I would like to Select all entries in the GalleryAlbums table, but Order them (DESC) by date values in a column (upload_date) on the GalleryPhotos table. Both tables have a column "album_id"
A little confused how I might write this statement.