Using Variables In A SELECT Query
Oct 3, 2007
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?
Thanks for any help given
View 4 Replies
ADVERTISEMENT
Jul 13, 2004
Hi,
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?
This is what I've got so far...
[code]
cmdSelect = New SqlCommand( "GetProd", connstr )
cmdSelect.CommandType = CommandType.StoredProcedure
dbcon.Open()
dbcon.Close()
[/code]
The main prob is just what to connect this record to in order to access the individual fields.
Thx :)
View 2 Replies
View Related
May 14, 2007
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
View 7 Replies
View Related
Aug 15, 2007
Hi.
I was wondering if I could use SELECT INTO to put my data into variables.
I'm coding in VB using Visual Web Developer.
Basically, I want to do this:
SELECT data3, data4 INTO variable1, variable 2 FROM table WHERE data1= textbox1.text, data2 = textbox2.text
I am pulling data based on a selection of name (data1) and date (data2).
Thanks!
View 4 Replies
View Related
Jun 6, 2000
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.
--------
Scenario A or B? Please advise...
TIA,
Alex
View 1 Replies
View Related
Nov 14, 2007
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:
declare @k1 varchar(100)
declare @k2 varchar(100)
declare @k3 varchar(100)
insert into tbl2
select
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.
View 9 Replies
View Related
Oct 23, 2006
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 ?
View 1 Replies
View Related
Apr 13, 2008
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
View 5 Replies
View Related
Apr 6, 2006
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.
Thank you!
Cody
View 1 Replies
View Related
Oct 22, 2001
I am trying to do a proof of concept on a simple sql statement, but it doesn't work. Am I doing it wrong, or is this not possible....
Table: tblItems
field: account varchar(20)
In Query Analyzer:
DECLARE @acct varchar(20)
SET @acct = '457760,123456'
SELECT account from tblaccount where account in (@acct)
Result:
0 Rows
Any thoughts?
Thanks,
Brian
Expected
457760
View 2 Replies
View Related
Oct 20, 2006
Hi
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?
thanks
View 2 Replies
View Related
Oct 31, 2007
is it possible to assign the results of the qry to the 2 variables?
Declare @a Integer
Declare @b Integer
SELECT A, B from myTable
View 1 Replies
View Related
Jan 5, 2007
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.
Any ideas?
View 4 Replies
View Related
Jul 8, 2013
I have 2 requests for desperate Hélio..
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)
View 7 Replies
View Related
Oct 11, 2014
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
View 4 Replies
View Related
Feb 8, 2008
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?
View 9 Replies
View Related
Oct 15, 2007
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
View 3 Replies
View Related
Dec 24, 2003
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:
Dim strSQL as String = "Select orderID, modelNumber from orderDetails" & _
"where CustomerID = " & User.Identity.Name & _
"And orderid = (SELECT MAX(orderid)FROM orderDetails" & _
"where CustomerID = " & User.Identity.Name & ")"
What I would like to do is assign specific values to variables based off of the above query. For example:
Dim orderItem as String = (all of the modelNumbers from the query)
Dim orderIdItem as String = (all of the orderIDs from the query)
How do I do this?? Any help is much appreciated! Thanks in advance.
Ron
View 6 Replies
View Related
Dec 15, 2004
Hey guys,
I was wondering if there was a way to set a local variable from the results of a query or stored procedure.
For example, I have a stored procedure or a select statement that returns 1 row, with columns "a" "b" "c" "d"
I want to set my variable to be the value in column "b"
Is there a way to do that? I'm new to tsql, so any code would help.
I tried this, but I know my syntax is messed up
declare @myvar as integer
set @myvar = (execute mystoredProcedure).b
thanks
View 4 Replies
View Related
Mar 28, 2001
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
View 3 Replies
View Related
Jan 4, 2008
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?
View 3 Replies
View Related
Mar 19, 2006
Is it possible to use variable name to dynamically define a query in a stored procedure? EX:
@Column = 'COUNT(*)'
@Category = 'Products'
@Table = 'Items. + @Category
SELECT @Column
FROM @Table
View 1 Replies
View Related
Oct 28, 2004
Hi all,
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?
Thanks
Tryst
View 1 Replies
View Related
Oct 10, 2006
When I want to user variable in the name of the database, I have an error. What's wrong with my code ?
DECLARE @BASE_SOURCE varchar (30),@BASE_DESTI varchar(30),@TEST varchar(30)
set @BASE_SOURCE='BASE1'
set @BASE_DESTI='BASE2'
select * from @BASE_SOURCE.dbo.FOURNISS
Msg*170, Niveau*15, État*1, Ligne*4
Ligne 4 : syntaxe incorrecte vers '.'.
View 2 Replies
View Related
Sep 6, 2006
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)
'@newpin being the result from first query
Any help appreciated
View 4 Replies
View Related
May 18, 1999
Hi!
Here is a snap form my code
declare @max_id int
declare @the_db varchar (30)
select @the_db = 'mydb'
exec ("select """ @max_id """ = max(id) from " + @the_db + "..mytable")
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.
Thank for any help in advance
Hank Lee
View 1 Replies
View Related
Aug 18, 2004
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.
Thanks
View 2 Replies
View Related
Oct 22, 2015
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
PRINT @Found
View 2 Replies
View Related
Feb 19, 2007
Hi
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.
Thanks
Steven
View 1 Replies
View Related
Sep 4, 2006
Hi,
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.
João
View 8 Replies
View Related
Nov 2, 2006
Learning how to use SSIS...
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.
http://msdn2.microsoft.com/en-us/library/ms139904.aspx
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.
...cordell...
View 4 Replies
View Related
Jan 24, 2006
Hi,
I would like to design a SSIS package, which have couple of variables. It loads a xls file specified in a variable [varExcelFileFullPath] .
I will run it by commands: exec xp_cmdshell 'dtexec /SQL ....' (pls see an example below).
It seems it does not get the values passed in for those variables. I deployed the package to a sql server.
are there any grammar errors here? I copied it from dtexecui. It worked inside Dtexecui not in dos command.
exec xp_cmdshell 'dtexec /SQL "LoadExcelDB" /SERVER test /USER *** /PASSWORD ****
/MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW
/LOGGER "{6AA833A1-E4B2-4431-831B-DE695049DC61}";"Test.SuperBowl"
/Set Package.Variables[User::varExcelFileName].Properties[Value];"TestAdHocLayer"
/Set Package.Variables[User::varExcelWorkbookName].Value;"Sheet1$"
/Set Package.Variables[User::varExcelFileFullPath].Value;"D: estshareTestAdHocLayer.xls"
/Set Package.Variables[User::varDestinationTableName].Value;"FeaturesTmp"
/Set Package.Variables[User::varPreSQLAction].Value;"delete from FeaturesTmp"
'
thanks,
Guangming
View 2 Replies
View Related
Sep 1, 2015
I wonder if it is possible to run a stored procedure and save the results directly to a file in a predetermined directory
As an example I have created a (simple) stored procedure:
USE CovasCopy
GO
CREATE PROCEDURE spTryOut
(
@LastName as NVARCHAR(50)
, @FirstName AS NVARCHAR(25)
[Code] ....
What I would like to add is a (or more?) lines that save the results in a file (csv/txt/tab?)
The name I would like the file to have is "LastName, FirstName, Date query ran, time (HHMMSS?) query ran"
The directory: D:SQLServerResults
View 9 Replies
View Related
