Is it possible to create my own query analyzer?The idea is the user just have to type their query in a textbox (SELECT only) then after pressing the execute button, records will be displayed in the page.If the user uses Insert, Delete or anything transactional, it will display msg that will prohibit the user executing the script
View 5 RepliesI'm build a web page in ASP.net is supposed to work just like Query Analyzer. It has basically a textarea to input the sql command, and an Execute button.I'm using SMO to execute the command in the database, as follows:
//Create object SMO
Microsoft.SqlServer.Management.Smo.Server server = new Microsoft.SqlServer.Management.Smo.Server(new Microsoft.SqlServer.Management.Common.ServerConnection(oConn));
//To execute the command
server.ConnectionContext.ExecuteNonQuery(tbx_cmd.Text);
//OR
myDataset = server.ConnectionContext.ExecuteWithResults(tbx_cmd.Text);
The problem is that the textarea command can contain anything, from a stored procedure (with GO's statements) and any select command that return resultsets, just like Query Analyzer.But, if I have GO statements, I only can perform the query using ExecuteNonQuery method. If I use the ExecuteWithResults method, it raises errors because of the GO statements.And if I want the resultsets to be available, of course I can only use the ExecuteWithResults method. how can I do to execute the same command texts at the same time?
im trying to build two charts from a sql query. this is the table i have.
table name services in sql server 2005, with some fields like.
service name categorie creation date status, (status here is boolean, true when the service is finished and false when the service is pending)
this is the interfase i got so far:
and ill place two chart pies below this image, to display charts dependin on the selections.
one chart should display the count of services from the selected categorie in the dropdownlist in relation to the dates selected in those textboxes and if no categorie is selected, it would display the total of services in each categorie in the first pie webchart,
and the other pie webchart should display the number of services that are finished or pending according to the selected categorie in the dropdownlist and the date range especified.
but the problem is that i dont know how to count in the sql query and assign the results to the charts.
[Code]....
[Code]....
creating Linq query in return statement
I'm new to Jquery, JSON and MVC, and am trying to create a json object that contains AD info from an AD query. The ad query is working fine and returning data and I have 2 overloaded implementations of it.
#1 is a method from an older .net app that builds and returns a datatable that we bound to a grid view control
#2 was a modification of #1 that returns a System.Collections.Generic.List<> type. #2 returns data ok and i can display a table in the MVC view
however, I'd like to get the data results into a json object so i can bind it to a jqGrid object that has similar functionality to the old .Net server controls (Delete, Update, Sort) Here's the new method that's part of a C# static class:
[Code]....
Like I said...I'm new to this way of doing web development, so I'm not sure how to even approach getting this data into a json object.
[Code]....
Well I'm not familiar with SP's yet, and I can't find a single example of what I want to do. basically I want to retrieve the town & county where the customers id are equal (note there will only ever be 1 result)
I can't figure out how to address the results to be stored within the output variables.
I'm encountering a strange problem. Here is the scenario. I have built a query that accepts a parameter (WHERE LIKE clause).
I've tested this within the query builder and it returns exactly the number I would expect.
I then go back to my webpage and add the following controls:
Dropdown
Gridview
the user to make a selection from the dropdown. Postback on the dropdown is enabled so the page reloads and the Gridview displays the filtered results (via an objectdatasource). I've set the parameter to the dropdown control.
The trouble is when I run the page I make the selection in the dropdown no results are displayed. Even though the text displayed in the dropdown control is exactly the same as I inputted into the query builder when testing it.
I have a SQL Data Source that displays records after going through the Query Builder and select "Test Query" yet when I save it, go back to the design mode and select View in Browser I get nothing but a blank screen.
View 3 RepliesI'm looking to translate an SQL query into linq
INNER JOIN Usrs ON
SAQ.UserId =
Usrs.UserId AND Scan.UserId =
Users.UserId
I'm not sure how to include the "AND" in the LINQ statement.
1. I have a GridView on my page and it uses sqldatasource with parameterized query. What I want to do is, on page load (where nothing has been selected so no parameter supplied), I want it to query everything (something like SELECT * FROM [this_table]) but since my SelectCommand is something like
SELECT * FROM [this_table] WHERE [this_column] = @someParameters AND [that_column] = @someParameters.
Can I play around with default value to achieve something like that but how ? Now, when the page loads, it doesn't show anything (No Gridview).
2. On my page, I made something like (username, gender, address, and more) and one single search button. That means, no single control enable auto postback. What I am trying to accomplish is building dynamic query
(if username specifed -> SELECT * FROM [this_table] WHERE [username] LIKE @username).
If both username and gender are specified (SELECT * FROM [this_table] WHERE [username] LIKE @username AND [gender] = @gender) and you know the rest. How can I do this using GridView and SqlDataSource ? To my knowledge, I can only specify one SELECT statement in a sqldatasource.
[code]...
This query works perfectly on the query analyser.
But when configuring the Table adapter ,I try executing the query and i get 0 rows affected.
What could I be getting wrong in this case.
NB:Existing GalleryID has been supplied.
By right clicking on my database i created a query in server explorer. But where are this query stored can't find them back. I should aspect that their is a folder query's like there is a folder tables but this isn't the case.
View 10 RepliesI am trying to create statitics for game downloads and am having dificulty with a sub part of my query. the goup by command is not having the disired result and i think i have been looking at it too long to see what i am doing wrong. [Code]....
At the moment it is producing each result in many rows when only 4 rows should appear. its the download column that is doing it. needs to be sub queried or something to display total in relation to month,gameid,userid.
Month-download-GameID-UserID
1 1
1 1
1 1
1 1
I am trying to display this query result in an aspx page.
It is very slow in loading. Here is the query. The inner query inside the outer quesry is the problem.(Please see the underlined part in the query) - (If I remove that part it is very fast.)
select
top 500
--This column is the issue
,Governing_Class=( case when exists (select top 1 tqc.class_code from
t_quote_class tqc
inner join t_quote_class_premium tqcm on tqc.class_code =tqcm.
class_code
where tqc.appid=pi.appid and tqc.class_code not in('8742' ,'8810','7380')
order by tqcm.premium_amt desc
)
then ( select top 1 tqc.class_code from
t_quote_class tqc
inner join t_quote_class_premium tqcm on tqc.class_code =tqcm.
class_code
where tqc.appid=pi.
appid
order by tqcm.premium_amt desc
)
......... From tables
select Groupid,GroupName,onorusername from palgroup where groupid in (select distinct Groupid
View 5 RepliesA follow up question concerning the DAL layer part .. The first question about dealing with large objectsI have one table (PlacesTable) that is connected by like 6 other tables (1:Many and Many:Many relations)
In my DAL should I put a big query that join the 6 tables + the m:m reference tables (btw it produces multiple data that I don't need Please refer to my old question) and place all the data in it's specific object property or, should I use a single query for each table and create an object for each fetched table .. and finally send those to the UI ?If I wasn't clear enough please let me know what I further information do you need to know!
I am having a problem with my sql query in c#, basically it's inline query with parameters, but when I run it it tells me that parameter 1 or parameter 2 is not there here is my query declared on top of the page as public:
public const string InsertStmtUsersTable = "insert into Users (username, password, email, userTypeID, memberID, CM7Register) " +
"Values(@username, @password, @email, @userTypeID, @memberID,@CM7Register ); select @@identity";
this is my code for assigning the parameters, I know I am having problem so I am assigning the params twice:
Username =(cmd.Parameters["@username"].Value = row["username"].ToString()) as string;
cmd.Parameters["@username"].Value = row["username"].ToString();
In 1 methopd it calls this query and tries to insert to table, here is the code:
Result = Convert.ToInt32(SqlHelper.ExecuteScalar(con, CommandType.Text,InsertStmtUsersTable));
Exact error message is: Must declare the variable '@username'. Could this code be a problem, because all the previous coding is declared with in this using statement, except declaration of query, here the using statement: using (SqlCommand cmd = new SqlCommand(InsertStmtUsersTable, con))
I am a biggner in SQL DB . but i started a complicated and painfull work in SQL SERVER 2008. the problem convert Oracle hierarchical query to SQL query. the query
SELECT DISTINCT
LEVEL LVL,
SCH.NSCHEDULE_SL,
SCH.NSCHEDULE_SL_FM,
SCH.CSHED_CNAME
FROM FA_SCHEDULES SCH
WHERE LEVEL = 1
AND NSCHEDULE_SL_FM IS NULL
AND NBRANCH_SL = 2
CONNECT BY PRIOR SCH.NSCHEDULE_SL_FM = SCH.NSCHEDULE_SL
AND NBRANCH_SL = 2
I've got a query such as
Dim MediaQuery =
From m
In dB.DOWNLOADS _Where m.ID = id _Select
which returns a record from the database. One of the fields in the record is a FK to another table. I need to read this value to be able to set a dropdown based on the ID but I can't work out how to access it. For a standard record I can just do the following txtTitle.Text = MediaQuery.FirstOrDefault().TITLE
However with the foreign key it doesn't work like that. I've tried drpGroup.SelectedIndex = MediaQuery.FirstOrDefault().DOWNLOAD_GROUPS.ID where DOWNLOAD_GROUPS is the FK field but it returns Object reference not set to an instance of an object. If you're simply wanting to read some values from a single db record in the entitiy framework and one is a foreign key how should I go about getting the value?
I am trying to use a variable from the code-behind page in a sqlDataSource WHERE statement. I am using Membership.GetUser().ProviderUserKey to obtain the UserId of the logged in user and I would like to only show record that have this UserId as a foreign key. How can I write the SQL for this?
Here is what I have so far:
SQL Query:
SelectCommand="SELECT [UserID], [CarID], [Make], [Model], [Year] FROM [Vehicle]"
Code-Behind
String user2 = ((Guid)Membership.GetUser().ProviderUserKey).ToString();
And I want to add WHERE UserID = user2 to the SQL Query.
i have tow query in one stored procedure
first query and the second one every of them return only value
i want to copy the result of tow values in a new row in new table
as i want to check on this tow values in ado.net
i want to get only this tow values as a row not first and secon query
only a row has result of first and second query results
[Code]....
I am facing a big problem with simple linq query.. I am using EF 4.0..
I am trying to take all the records from a table using a linq query:
var result = context.tablename.select(x=>x);
This results in less rows than the normal sql query which is select * from tablename;
This table has more than 5 tables as child objects (foreign key relations: one to one and one to many etc)..
This result variable after executing that linq statement returns records with all child object values without doing a include statement.. I don't know is it a default behavior of EF 4.0 . I tried this statement in linqpad also..but there is no use... But interesting thing is if I do a join on the same table with another one table is working same is sql inner join and count is same..but I don't know why is it acting differently with that table only.. Is it doing inner joins with all child tables before returning the all records of that parent table?
I am writing a linq query to select a blogpost,
[code]....
The blogpost i am testing got 3 tags attached to it. The table structure is:
(table)BlogPost -> (table)BlogPostTags <- (table)Tags
So the BlogPostTags table only contains 2 fields, BlogPostID and TagID.
When i run the query above i get 3 results back. Same blogpost 3 times but with 1 tag in each. It should return 1 post with 3 tags. The problem lies in the Tags query above.
How can I SELECT second if first SELECT returns nothing?
View 8 Replieswhy I get the following error: Query input must contain at least one table or query
for this code...
Insert into BlogPost (BlogID,BlogTitle,BlogContent,Attatchments,IsEnabled,IsVisible) values
((SELECT BLOGID FROM Blog WHERE BlogOwnerID =7),'test','aaaaa','aaaa',true,true);
all i know that it is a problem with how i have nested the select statement...