Hi I'm a n00b at ASP.Net and C# but the company I work for requires me to know this and develop their website using these languages.
Anyway I've been doing pretty good so far I got the page looking as it should etc and databases all set up, now I've hit my first real problem... I am using Visual Studio 2005 and in the Web Developer section. I want to make a SELECT query using the <asp:LoginName> control as the where clause.
e.g. if admin logs in there is a welcome message : Welcome Admin now I want to use the 'Admin' <asp:LoginName> as the SELECT in my query i.e. SELECT * FROM tbla WHERE ([fldCustomerCode] = 'LoginName' )
I was just really wondering if that is in the .aspx file or the aspx.cs code behind file?
I think I'm just braindead or simply thick...since this shouldn't be that hard, but I'm stumped right now.
So, I'm trying to retrieve from a table, with a sql stored procedure with the sql like "select height, width, depth from products where id=@idinput"
OK, so this part is easy, but if I wanted to say, return this to my code and assign height to a variable Ht, width to Wd and depth to Dp, how could I do that?
Pretty simple question and I see it's been asked a few times with no answers so I thought I'd try again. :) Is there a way to put a variable into an SQL connection string? Like: <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:BGHelpdeskConnectionString %>" OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT * FROM MyTable WHERE name=" & response.write(MyName) .... etc
Stored procedure retrieves a single row from a single table... Based on the specific values in 4 different columns, different branch actions are taken using 4 nested IF statements.
The question is, what is more efficient: storing column values in 4 variables and then evaluting each of them, or executing the same query 4 times?
Scenario A:
DECLARE @var1 char(20), @var2 char(20), @var3 char(20), @var4 char(20) SELECT @var1 = col1, @var2 = col2, @var3 = col3, @var4 = col4 FROM theTable WHERE rid = 12345 IF @var1 = 1 ... ELSE IF @var2 = 2 ... ELSE IF @var3 = 3 ... etc. ---------------
Scenario B:
IF (SELECT col1 FROM theTable WHERE rid = 12345) = 1 ... ELSE IF (SELECT col2 FROM theTable WHERE rid = 12345) = 2 ... ELSE IF (SELECT col3 FROM theTable WHERE rid = 12345) = 3 ... etc. --------
Is there a way to write an insert where the values you're inserting depend on another value in the row. Here is an example of pseudo-code that hopefully demonstrates what I'm tryihg to do:
field1, field2, field3, @k1=(select sm from tbl3 where mm=field1), @k2=(select sm from tbl3 where mm=@k1), @k3=(select sm from tbl3 where mm=@k2) from
tbl1 where
blah= blahblah and bldfddf = dfaljk
My main focus is using the variables @k1 - @k2 in the where part of subsequent subqueries in the select statement. I haven't been able to get this to work, but I thought maybe something like it would work. I'd like to do this in one statement rather than using a cursor or loop. I'm also trying to avoid functions. Any help would be appreciated.
I have problems to place my variable into the select statement.
DECLARE @DB_NAME varchar(64) DECLARE MR_ReqPro_DB_cursor CURSOR FOR select name from dbo.sysdatabases where name like '%MR_req%' OPEN MR_ReqPro_DB_cursor FETCH NEXT FROM MR_ReqPro_DB_cursor INTO @DB_NAME
WHILE @@FETCH_STATUS = 0 BEGIN print @DB_NAME; --works fine
Select NAME, FILEDIRECTORY FROM @DB_NAME.MR_ReqPro.RQDOCUMENTS WHERE (FILEDIRECTORY LIKE '%\%');
FETCH NEXT FROM MR_ReqPro_DB_cursor INTO @DB_NAME END CLOSE MR_ReqPro_DB_cursor DEALLOCATE MR_ReqPro_DB_cursor
GO
How could i use a variable like @DB_Name in my select ?
Hi I am trying to include a string variable in a Select Statement. My problem is that when I code with the user name hard coated in the SQL Statement it works fine (see below:) cmd.CommandText = "SELECT UserPswd, StudioID, StudioCode FROM Users WHERE UserName = 'jdoe' " But when I try to use the String variable I get an error (See below): cmd.CommandText = "SELECT UserPswd, StudioID, StudioCode FROM Users WHERE UserName = " & StrUserName I know there must be something wrong with my syntax ?? Thanks Jackson
Hello, Is there a way to assign multiple variables to one select statement as in the following example? DECLARE @FirstName VARCHAR(100) DECLARE @MiddleName VARCHAR(100) DECLARE @LastName VARCHAR(100) @FirstName, @MiddleName, @LastName = SELECT FirstName, MiddleName, LastName FROM USERS WHERE username='UniqueUserName'
I don't like having to use one select statement for each variable I need to pull from a query. This is in reference to a stored procedure.
I've tried declaring and setting variables in my sql statement and then trying to use them instead of defining a column directly - sorry quite hard to explain, i'll do a simple example
eg
DECLARE @column DECLARE @value SET @column = 'col1' SET @value = 'bloggs'
Select * FROM table1 WHERE @column = @value
It keeps returning no results even though i've tried
Select * FROM table1 WHERE col1 = 'bloggs' -- which returns results
I realise its the column which is not being selected, but there must be a way by using a variable?
I'm getting the error "No recepient is specified".
I have set up a dataflow from a select statement into a record set, then have that dataflow point to a ForEach group with a mail task in it. I have set up variables for the username and subjectline. So in the mail task I have no value in the To: line because I specify an "http://www.sqlis.com/59.aspx">http://www.sqlis.com/59.aspx exactly.
I couldn't figure out how to included screen shots.
1) is there any way to run a query over a query without having to create a table with the results of the first query? (would drop table work? If so, how?
2) how can i define input variables the same way i do in excel? I am trying to run a couple of simulations based on 2 core inputs (in excel i would just do a data table)
I can't understand why I get 2 different results on running with a Bracket I get 'NULL' and without a bracket I get the declared variable value which is 'Noname'
Below is Query 1:
Declare @testvar char(20) Set @testvar = 'noname' Select @testvar= pub_name FROM publishers WHERE pub_id= '999' Select @testvar
Out put of this query is 'Noname'
BUT when I type the same query in the following manner I get Null-------Please note that the only difference between this query below is I used brackets and Select in the Select@testvar statement
Declare @testvar char(20) Set @testvar = 'noname' Select @testvar=(Select pub_name FROM publishers WHERE pub_id= '999') Select @testvar
Not sure if this is possible, but maybe. I have a table that contains a bunch of logs. I'm doing something like SELECT * FROM LOGS. The primary key in this table is LogID. I have another table that contains error messages. Each LogID could have multiple error messages associated with it. To get the error messages. When I perform my first select query listed above, I would like one of the columns to be populated with ALL the error messages for that particular LogID (SELECT * FROM ERRORS WHERE LogID = MyLogID). Any thoughts as to how I could accomplish such a daring feat?
Hi,I'm new to SQL Server, but an experienced .Net developer. I'm tryingto accomplish a query the most efficient way possible. My question isif you can define a temporary variable within a query to store tablesor fields. (Like the LET clause of LINQ) My query makes use ofsubqueries which filter my table (WHEREs, not SELECTs) in the sameexact way. I'd like to have a subquery at the beginning of my queryto filter the table(s) once, and then SELECT off it of later in thequery.Here is an (utterly poor) example. No, this is not from my project.My filter is a little more complex than 'c=@p'.('c' is a column/field, 't' is a table', '@p' is a parameter)SELECT *FROM (SELECT COUNT(c) FROM t WHERE c=@p GROUP BY c)CROSS JOIN (SELECT c FROM t WHERE c=@p)Bottom line, would something like the following be possible?@v = (SELECT c FROM t WHERE a=@p)SELECT *FROM (SELECT COUNT(c) FROM @v GROUP BY c)CROSS JOIN (SELECT c FROM @v)I'd like to know if this is possible within a query, but I can move toa Stored Procedure if I must. (I'll still need help then.)Thank you all
I’ve reconfigured Microsoft’s IBS Store shopping cart to function within a small e-commerce website. What I am trying to do is to modify the code slightly in order to use a third party credit card processing center. The situation is this: once the customer clicks the final "check out" button, a stored procedure writes all of the product ordering information into the database. I, then, capture what they're wanting to purchase with the following SQL statement:
I'm trying to create a stored procedure with a dynamic update statement depending on the variables received. I receive a sql error "Incorrect syntax near the keyword 'WHERE'." although the variable, @tempvar, appears correct. Any ideas? ---- DECLARE @tempvar varchar(1025), @SolutionDetail varchar(1000), @hours varchar(12), @id int
if @SolutionDetail <> " " set @tempvar = "SolutionDetail = " + "'" +@SolutionDetail + "'"
IF @hours <> " " set @tempVar = @tempvar + ", hours = " + "'" + @hours +"'"
UPDATE WorkOrderTbl SET @tempvar WHERE workorderid = @id
Hi all, I was wondering if anyone can help me figure out how to insert a variable into a query using Visual Studio 2005 with the SqlDataSource control. I cant seem to be able to enter a var into the query parameters.
this is my SqlDataSource: <asp: SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:imLookinLikeConnectionString %>" DeleteCommand="DELETE FROM [tblDiaryEntries] WHERE [DiaryEntryID] = @DiaryEntryID" SelectCommand="SELECT tblDiaryEntries.DiaryEntryID, tblDiaryEntries.EntryDate, tblDiaryEntries.Subject, tblDiaryEntries.DiaryEntry, aspnet_Users.UserName FROM tblDiaryEntries INNER JOIN aspnet_Users ON tblDiaryEntries.UserID = aspnet_Users.UserId WHERE UserName=@UserName ORDER BY tblDiaryEntries.EntryDate DESC" UpdateCommand="UPDATE [tblDiaryEntries] SET [DiaryEntry] = @DiaryEntry, [EntryDate] = @EntryDate, [Subject] = @Subject WHERE [DiaryEntryID] = @DiaryEntryID" InsertCommand="INSERT INTO tblDiaryEntries(UserID, EntryDate, Subject, DiaryEntry) VALUES (@UserId, GETDATE(), @Subject, @DiaryEntry)"> <DeleteParameters> <asp: Parameter Name="DiaryEntryID" Type="Int32" /> </DeleteParameters> <UpdateParameters> <asp: Parameter Name="DiaryEntry" Type="String" /> <asp: Parameter Name="EntryDate" Type="String" /> <asp: Parameter Name="Subject" Type="String" /> <asp: Parameter Name="DiaryEntryID" Type="Int32" /> </UpdateParameters> <InsertParameters> <asp: Parameter Name="DiaryEntry" Type="String" /> <asp: Parameter Name="EntryDate" Type="String" /> <asp: Parameter Name="Subject" Type="String" /> <asp: ProfileParameter DefaultValue="Anonymous" Name="UserName" PropertyName="UserName" /> <asp: Parameter Name="UserId" /> </InsertParameters> <SelectParameters> <asp: ProfileParameter DefaultValue="Anonymous" Name="UserName" PropertyName="UserName" /> </SelectParameters> </asp: SqlDataSource> What I want to do is to tell the SqlDataSource that @UserName = this.User.Identity.Name, but I only know how to do that in code-behind, not sure how to insert it into the code above.Any ideas?
If I have a query string that is to be stored in a database, for example
Code:
SELECT prod_id, prod_name, prod_desc FROM products WHERE prod_id = 'variable'
how can I put a variable identifier into this string so that when I need to run the query I call it from the database and simply insert the relevant variable in the correct place.
Is there an appropriate way of doing this in MS SQL Server?
Hi, not exactly too sure if this can be done but I have a need to run a query which will return a list of values from 1 column. Then I need to iterate this list to produce the resultset for return. This is implemented as a stored procedure
declare @OwnerIdent varchar(7) set @OwnerIdent='A12345B'
SELECT table1.val1 FROM table1 INNER JOIN table2 ON table1. Ident = table2.Ident WHERE table2.Ident = @OwnerIdent
'Now for each result of the above I need to run the below query
SELECT Clients.Name , Clients.Address1 , Clients.BPhone, Clients.email FROM Clients INNER JOIN Growers ON Clients.ClientKey = Growers.ClientKey WHERE Growers.PIN = @newpin)
I got syntex error for the @max_id. The script is writen based on the sample given in MSSQL6.5 Book Online, chapter "Transaction-SQL Reference 6.0" section "C"->"Control-Flow Lang."->"Control-Flow Examples"
Can someone help me on how to assign a value to a local variable from a dynamic query.
I am brand spankin new to stored procedures and don't even know if what I want to do is possible. From everything I've read it seems like it will be. I have a table, punchcards. In this table are all the punch in/out times for a week. I want to create a stored proc to calculate how many hours a punchcard entry is.
Thats the dream.
The reality is that I can't even get a tinyint from a table to load to a variable and be printed out. I am using sql server 8.
Here is what I have as of this moment for my sp.
ALTER PROCEDURE usp_CalculatePunchcard AS DECLARE @dtPP DateTime SET @dtPP = (SELECT thursday_in1 FROM punchcards WHERE (punchcard_id = 1)) /* Also tried.... SELECT @dtPP=thursday_in1 FROM punchcards WHERE (punchcard_id = 1) */
PRINT @dtPP
RETURN /* for some reason i can't use GO ... even though every document i've read on stored procedures has used GO and none use RETURN */
The only output this is producing is ' Running dbo."usp_CalculatePunchcard". '
Any help would be greatly appreciated as I am about to kick someone/something.
I am writing a custom query to determine if a legacy table exists or not. From My CMS Server I already have all the instances I have to query and I store the name of the instance in the @Instance variable. I cannot get those stubborn ticks to work right in my query. Below I am using the IF EXISTS statement to search the metadata for the legacy table.
DECLARE @Found tinyint DECLARE @Instance varchar(100) set @Instance = 'The Instance' IF (EXISTS (SELECT a.* FROM OPENROWSET('SQLNCLI', 'Server=' + @Instance + ';UID=DBAReader;PWD=DBAReader;','SELECT * FROM [DBA].INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''TheTable''') AS a)) SET @Found = 1 ELSE SET @Found = 0
when I run this query against an Oracle 9 datasource I get a message saying 'not all variables bound'
select <some fields> from star where nr_jahr_star = :Year
my datasource is correct as the same report runs fine if I just put in a fixed year and I have the Oracle client software on both my PC and the RS server
I've tried various combinations with the parameter settings also with no luck.
I am not comfortable with DTS 2000 but I need to execute a encapsulated DTS 2000 package from a SSIS package. The real problem is when I need to pass SSIS variables to DTS 2000 package. The DTS 2000 package have 3 global variables that I can identify on " Execute DTS 2000 Package Task Editor - Inner Variables ". I believe the SSIS variables must be mapped on " Execute DTS 2000 Package Task Editor - OuterVariables ". How can I associate the SSIS variables(OuterVariables ) to "Inner Variables"? How can I do it? Much Thanks.
I have a data flow that uses an OLEDB Source Component to read data from a table. The data access mode is SQL Command. The SQL Command is:
select lpartid, iCallNum, sql_uid_stamp from call where sql_uid_stamp not in (select sql_uid_stamp from import_callcompare)
I wanted to add additional clauses to the where clause.
The problem is that I want to add to this SQL Command the ability to have it use a package variable that at the time of the package execution uses the variable value.
The package variable is called [User::Date_BeginningYesterday]
select lpartid, iCallNum, sql_uid_stamp from call where sql_uid_stamp not in (select sql_uid_stamp from import_callcompare) and record_modified < [User::Date_BeginningYesterday]
I have looked at various forum message and been through the BOL but seem to missing something to make this work properly.
The article, is the closest I have (what I belive) come to finding a solution. I am sure the solution is so easy that it is staring me in the face and I just don't see it. Thank you for your assistance.
