Using Variable From Code-behind In SQL Query, C# / Add WHERE UserID = User2 To The SQL Query?
Apr 26, 2010
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.
View 2 Replies
Similar Messages:
May 21, 2010
I get this error while trying to run my project: "Column name 'UserID' appears more than once in the result column list." Indeed, the query generated in my SqlTableProfileProvider.vb is the following (text in brackets [] has been changed):
"IF EXISTS (SELECT 1 FROM [table] WHERE UserId = @UserId) BEGIN UPDATE [table] SET UserID=@Value0, LastUpdatedDate=@LastUpdatedDate WHERE UserId = '[Guid]'END ELSE BEGIN INSERT [table] (UserId, UserID, LastUpdatedDate ) VALUES ('[Guid]', @Value0,
@LastUpdatedDate) END"
I'll emphasize that the above statement is generated in SqlTableProfileProvider.vb, which was written by .Net people. While I have little control over its source, I've apparently given the fodder whereby the mistake can be generated. Any clues as to why UserID is being written twice in the statement?
View 2 Replies
Jan 28, 2011
I want to enter a url of the form [URL] and pass the "MC001" to a query string.
the query string I have now is:
qry = "SELECT QRID, QRContent FROM " & tablename & WHERE QRID= '" & request.querystring() & "'"
Why does this not work?
View 1 Replies
Nov 15, 2010
I'm having some trouble writing a query using variables. Here's my code
Dim bondnumber as String = "69836"
Dim PasswordCheck As String = "DECLARE @investor varchar(10),
@thepassword varchar(20), @linkedserver2 varchar(25), @sql varchar(1000) "
PasswordCheck += "SELECT @investor = '" & bondnumber & "',
@linkedserver2 = 'binfodev', "PasswordCheck += "@sql = 'SELECT * FROM ' +
@linkedserver2 + ' WHERE bondno = ''@investor'' ' EXEC(@sql)"
It doesn't seem to be passing the variables properly in the query and I'm not sure where I'm going wrong.
View 2 Replies
Oct 2, 2010
I've managed to use my MS Access database with repeaters and gridviews in my website. Basically, printing the result of a query directly to the website seems easy enough using something like this :
[Code]....
After quite a bit of googling this seems to be a common way to do this. My question then seems kinda silly.
I want to use some of the data from the query in for example a variable without sending the data to a repeater or gridview and so on. Say I just want the username and a password from a database and want to store the username in one variable and the password in another for further use in the code. What do I do?
View 1 Replies
Aug 11, 2010
[Code]....
say i have the code above which adds a single row to a list how would i add all the information in a linq query or a var to the list?
View 2 Replies
Jan 16, 2011
I have a Query which I run as an SQL. This is my SQL:
"SELECT COUNT(*) As NoDup FROM [XXX Dir] WHERE (FileName Like 'XXX_YYY_20100916.txt')"
The "XXX Dir" is a table and "FileName" is a text field within that table.
When I execute the SQL in the Query development windows, it returns a single value like 2.
I would like to assign that value to a variable so I can use it in my VB code.
View 1 Replies
Mar 11, 2011
I would like to run a SQL query on the codebehind VB page that will also set a value from that query to a Session variable. Also, this needs to be done on the Page_Load.
Example:
[Code]....
View 3 Replies
Jan 21, 2010
I want query like this
declare @stateQuery as nvarchar(200)
if @stateid is null
set @stateQuery=''
if @stateid is not null
set @stateQuery='and tbl_city.stateid ='+cast(@stateid as nvarchar(10))
set @length=(select top 1 len(dbo.Tbl_city.cityName) FROM dbo.tbl_city INNER JOIN tbl_cityinfonew ON dbo.tbl_city.cityId = tbl_cityinfonew.cityId where (tbl_cityinfonew.language =@language )+cast( @stateQuery as nvarchar(100)) order by tbl_cityinfonew.createdate desc)
I don't want to use the exec @string.
View 6 Replies
May 5, 2010
I am using a data list to display some value retrieved from a SQL query. My problem is that I would like to insert a variable for the field of ISDS_454.dbo.booksusers.userID. The variable has already been declared and stored upon page load. I just need some way to get it within the SQL query for the datalist.
<script runat="server"> Sub Page_Load()
Dim ID As String = "D07AD7E0-8E34-4C18-9B4D-10576B0CC6BF" End Sub
</script> <asp:DataList ID="DataList1" runat="server" DataSourceID="SqlDataSource1">
<ItemTemplate> fic: <asp:Label ID="ficLabel" runat="server" Text='<%# Eval("fic") %>' />
<br /> <br /> </ItemTemplate> </asp:DataList> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ISDS_454ConnectionString %>" SelectCommand="Select COUNT(*) as fic
From ISDS_454.dbo.Books, ISDS_454.dbo.booksusers, ISDS_454.dbo.Genre
where ISDS_454.dbo.Books.ISBN = ISDS_454.dbo.booksusers.ISBN
And ISDS_454.dbo.Genre.Genre_ID = '1'
And ISDS_454.dbo.booksusers.userID = 'D07AD7E0-8E34-4C18-9B4D-10576B0CC6BF'">
</asp:SqlDataSource>
View 6 Replies
Oct 26, 2010
sales.aspx.vb
Custom function which stores the current DATE into todayDate. I use this function to convert system date into SQL friendly date.
sales.aspx
I used Data Grid and SQL Data source.
SelectCommand="select * from sales where aab = 110026"
I want my select statement should take the date from todayDate.
i.e.: SelectCommand="select * from sales where aab = todayDate"
How to write this statement in correct syntax.
View 4 Replies
Oct 7, 2010
i am wishing to store the query result in any variable, have look at below code.
[Code]....
I have marked the code with bold, where I am getting error.
how do I store query result in any variable.
View 8 Replies
Jan 12, 2011
How would I take a text box value and use it in the query string on submit? I'd like it to start as this,
/News?favorites=True
and end up something like this after the user enters in a search and clicks search.
/News?query=test&favorites=True
The controller action looks like this
public ActionResult Index(string query,bool favorites)
{
//search code
}
View 1 Replies
Mar 22, 2011
I am having trouble getting a SQL query to post its results to a Session variable. I am close, but no success thus far. Here is what I have:
[Code]....
However, I keep getting this error when I click to a new page that is using this "PracticeID" Session variable: No mapping exists from object type System.Data.SqlClient.SqlCommand to a known managed provider native type.
View 5 Replies
Jul 30, 2010
I want to do execute a LINQ query, and I have the name of the table to query stored in a variable:
string tableName = "SomeTable";
DataContext db = new DataContext();
var vResult = from t in db.tableName where t.Id = ....
View 2 Replies
May 7, 2015
If that Possible then How?
Response.Redirect("Manage Admin.aspx?AdminId=Select AdminId from Admins where UserName=" + Session["AdminSession"]);
View 1 Replies
May 17, 2010
I have a web application which uses a session variable to store the logged in userid. If no user is logged in, of course this variable will be empty and the contents displayed on my website are meant for guests. If there is a user logged in, the user specific controls/access/links will then be a displayed.
I am now having issues with my hosting where on shared application pool, the worker recycle is triggered every 90 minutes, this will clear sessions causing all my users to be logged out. I opted for a dedicated application pool, which got worse because I am only allocated 50MB memory limit and if this is reached, the worker recycle is triggered and I lose my sessions again. I have tried as much as possible optimization techniques, e.g. dispose where possible, close connections, disable viewstate for static controls etc but my memory per instance keeps building up from page to page without any signs of improvement. I don't use loops nor store huge objects like bitmaps etc but my sessions are now gone even faster than 90 minutes in shared application pool before.
I have considered using SQL Session State but there isn't a simple guide on using this with MySQL. I am getting desperate and considering using a public variable, a string as a replacement to store logged in user id instead of in a session variable. I am pretty sure this will solve my issue with sessions being recycled but are there any negative consequences of doing this? One problem I can think of is if the user closes the browser, the system will never know that the user is now logged out and this public variable should be nothing. In this scenario, will the GC eventually clear this abandoned public variable.
View 10 Replies
Aug 30, 2010
Example scenario:
User login has a list of associated 'accounts' it has access to... this list of accounts is stored in a database table, say tblAccountAccessControl, something like:
login, accountID
userA, account123
userA, account456
userA, account798
userB, accountABC
userB, accountDEF
etc..
This info is used throughout the web application and determines the 'domain' of information the particular login has access to. For example there are many other tables that have the 'accountID' field and whenever a page pulls data from the table it only pulls data that the currently logged in user should be allowed to see, based on that tblAccountAccessControl data.
The security question:
Do I need to query the database every time I need to get this list of allowed accountID's? I was about to use a plain old query string to pass a particular accountID to another page but quickly caught myself as I realized that would be a major security flaw (the receiving page was going to use that accountID to grab info from the database, and there would be nothing preventing a user from simply typing in a url manually to get data from an accountID they are not supposed to be allowed to view). So I then thought I'll just store the list of accountID's in a session state variable so they will be carried with the user throughout the session.
But then I remembered there are tools out there that allow you to easily intercept and modify asp.net viewstate information and I'm not sure if that would included session state variables, but I'm guessing they would. Is there a way to create a secure query string? If I used session state would that session state information be exposed and modifyable by utilities out there? Am I just stuck having to query the database every time I need this info to assure security? What about encrypting it and then storing in session state variable (just thought of that one)?
View 5 Replies
Mar 28, 2011
I have to build a screen that tracks despatched goods on trucks on a certain date. I have created a form (using C# and ASP.NET) where I am displaying the calendar and am allowing the user to select the date he wants to check for the despatched goods.
My question is how to store the date that was clicked by a user in a variable which I want to use it later in an SQL query.
Should I store the date as a session variable? Suppose I want to display the date that the user clicked, how do I display it?
View 1 Replies
Nov 10, 2010
I am building a web application where i am using multilingual support.
I am using variable for label text display.so that administrator can change in one area and that text reflects throughout the application.
which is better less time consuming for display label text?
1)using relational db interaction.
2)constant variable.
3)xml interaction.
how could I found/calculate the processing time of the above three.
View 2 Replies
Aug 22, 2010
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.
View 11 Replies
Jul 13, 2010
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.
View 3 Replies
Jan 15, 2010
[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.
View 1 Replies
May 17, 2010
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 Replies
Mar 2, 2011
I 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
View 2 Replies