SELECT DISTINCT TblSections.SectionID, TblSections.Description
FROM TblSections INNER JOIN
UMG.Users ON TblSections.SectionID = UMG.Users.SectionID
WHERE (TblSections.Description IS NOT NULL) AND (dbo.Users.LoginName = [@Session(UserName)])
ORDER BY TblSections.Description
I have problem in using Session name in the above BOLD WHERE clause.
How I can Pass session parameters to sqlDataSource. I do like this but it is not work:
[Code]....
The session name is :session["EmailAddress"]
I have an ASP.NET VB website in which I am using the Membership functionality to authenticate users. I am doing this through the Login control on the HTML page. I have working that it will authenticate the user and then opens a welcome screen in which I have a button they can push to open a Reports.aspx page. On that page I have a dropdown box that is being populated from a datasource connection query to my SQL Server database table. It is a hard coded query but want it to query the table off the username text value that the user provided on the login (i.e. select <something> from inventory where username = <username>). I have not behind code but only HTML at this point. Here it is ...
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Login.aspx.vb" Inherits="Login" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
[Code] .....
Here is the Reports form that has the dropdown on it:
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Report.aspx.vb" Inherits="Report" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
[Code] .....
I'm trying to make a dropdown list with data available based on the users login name. Heres what I have as my SQL Query in the SQLDataSource:
SELECT Client_Name, Client_ID FROM Client WHERE (Client_Name LIKE '%@Client_Name%')
So say if the login username is "User1" but the database client name is "User1 Steel" I want the dropdownlist to pull "User1 Steel" as the display and Client_ID as the value from the dropdown list.
I am having difficulties using a variable in the "where" clause:
declare @shift_started as datetime
set @shift_started = (SELECT *
FROM OPENQUERY(ADS_RGWP_SERVER, 'SELECT MAX(shift_started) as shift
FROM salestkt AS STK
WHERE NOT EXISTS(SELECT *
FROM SHFTDATE AS SD
WHERE SD.shift_started = STK.shift_started)'))
SELECT *
FROM OPENQUERY(ADS_RGWP_SERVER, ' sum(case when customer_type = ''Inter-Company'' then qty_shipped_today else 0 end) as intercompany_qty,
sum(case when customer_type = ''Charge'' then qty_shipped_today else 0 end) as outside_qty
FROM salestkt
WHERE shift_started ='" & @shift_started & "' ''')
Getting error:
Incorrect syntax near ' & @shift_started & '.
i am developing an application using asp.net and backend server is SQL SERVER 2005.
in my application i have 12 regions. from that 12 regions user will select any of them.
if user select 1,2,3 then i have to pass this string to IN clause
for that i have storedproc like
create procedure sp_updateDistributorContractforAllRegions (
@unitprice float,
@region varchar(50)
)
update employee set UnitPrice=@unitprice where regionid in (@region)
end
GO
here regionid datatype in employee table is INT.
from code behind i am adding the parameter as 1,2,3.when i run the query it gives the error as
Conversion failed when converting the varchar value '1,2,3' to data type int.
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 an SqlDataSource query that should use an input parameter for multiple integer values, like:
select * from table1 where TableID IN (1,2,3,4,5)
->
select * from table1 where TableID IN (@IDValues)
How do I do that ? Default parameter settings won't work, returning "invalid integer value" . I suppose that parameter should be passed as some kind of array that SQL recognizes as multiple integer values , but what's the correct way of setting this ?
I have a GridView that's currently bound to an ObjectDataSource. I would like to see if it's possible to instead bind it to an EntityDataSource. However, I need to be able to apply custom filters to it.
One of the filters corresponds to a bit / boolean property in the database / EntityDataModel. It has 3 possible selections:
[Code]....
[code]....
How can I accomplish the same thing in an eSQL Where clause? There is no COALESCE function, is there an equivalent?
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]....
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]...
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'm using asp.net login on a SQL Database.
I have 12 users in my User tabel - one of the user I don't want to have in my select (the user is an admin).
I tried this:
[Code]....
I also tried:
WHERE (dbo.aspnet_Roles.RoleName <> 'Administrators')
Both time it took the user into the table....
If I instead try this
WHERE (dbo.aspnet_Roles.RoleName = 'Administrators')
I get only one row (as expected).
What is wrong with the above sql statement - why can't I get 11 users and not them all (12 users)?
[Code]....
modifying WHERE Clause and change to RegEx?
I have some duplication that I need to clean up. I wrote a SQL query that is supposed to return duplicate customers who are located in the same city and zipcode.
I have 2 questions regarding it:
1. Is the query syntax correct to find duplicate records by same city and zipcode, data is being returned but it just returns the same customer a few times?
2. I only need to do the GROUP BY clause for Fullname and City however it gives an error when the other columns are left out. The error is Address, State and Zip not being in the aggregate function or in the group by clause.
Adding all the remaining columns to the GROUP BY clause works.
Query is as below:
[code].....
I'm using parameterized sql query to get data from database
string query = "Select * from employee where employee_city in (@value)";
strign city ="'NewDelhi','Bangalore','Mumbai'";
I'm using following code to achive this
[code]...
But this is not giving the result. If run the query in SQLServer query window as "Select * from employee where employee_city in ('NewDelhi','Bangalore','Mumbai')", records are there.
But the same query will not return any records from ADO.Net.
I know I'm missing something here but I can't figure out what it is. I've got a query joining three tables....accounts, payments, and a table linking the two (there is a M:M relationship).I'm trying to pull a list of all accounts in the account table that have a payment that needs to be resequenced, and also the maximum payment priority if there are any payments that haven't been fully paid. If all payments HAVE been fully paid, I want to return a 0.
It's that last bit of logic that I can't get right. If I include that in the where clause, I get only the accounts that have a payment that hasn't been fully paid. If I take it out, I get all the accounts I get, but I get the highest payment priority whether or not the payment has been fully met.
Here is the query....how do I include the where clause criteria but still include all accounts?
select distinct
bat.acct_id,
isnull(max(isnull(crt.pymt_priority, 0)), 0)[code].....
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.
Here is my LinqDataSource
[Code]....
Here is a button inside a listview that is using the LinqDataSource and also has a pager:
[Code]....
And here is the Code Behind that is triggered by the linkbutton:
[Code]....
It almost works. When i click it it loads and shows the current page of objects that have dates that are higher then todays date. Which is what i want. But as soon as you use the pager to go back or forward it reverts to the default, which is ALL articles, sorted by ID. How can i make the "where" selection stick through the paging?
I have a formview that is working fine for with the exception of the username is not inserting on a new record. The username is visible on login so I know that is working. I'm attaching my sql code as well as the code behind as I don't know what I'm missing.
[Code]....
I have the following code where i enable the user to insert their password to retrieve their username.
[Code]....
But nothing happens, it doesnt work. I want it to do the check and then do something. I am just testing with a label for the time being.
But why does this not work?
I would like my website to fetch the data for username and password from a database in SQL server and authenticate that. I have used a login control in the website.
Presently I can assign username and passwords using the 'login control' from the website administration tool.
How do I define @UserName in my function:
[Code]....
I believe "UserName" = Me.User.Identity.Name
but I don't know the proper way to add it to the function.
[Code]....
How to Pass UserName and Password to codebehind page
I am using SQL server triggers to do the database auditing for my website. My problem is that I cannot pass the username who manipulate data into the Audit table. The username saved into Audit table is always the SQL server user name which is part of my connection string. Is there any better way to handle this?
View 6 Replies