SQL Select Command With Where Clause Variable Based On ASP.NET TextBox
All,
Please can someone advise how to use SQL select statement with where clasue which is based on a textBox.text value.
ex. below example I set the textbox.text value to a C# variable called TextBoxValue1 but I receive error this is not a valid
This is all done in Page_Load event backend code.
string strCommandtext = "Select Type.TypeName, Type.TypeDesc FROM Type Where Type.TypeName = TextBoxValue1";
View Complete Forum Thread with Replies
Related Forum Messages:
Use A Variable Along With The FROM Clause In SELECT Statement
I have a table 'table_list' which contains two columns, table_name and a record_count. This table stores a list of tables and their corresponding record counts. What I am trying to do is, to be able to write a select statement, that can read each table name in the 'table_name' column, execute a select count(*) for the same, and update its record_count with the result of select count(*). This is the code in my procedure.. DECLARE @tab_list CURSOR set @tab_list = CURSOR FOR select * from table_list OPEN @tab_list DECLARE @tab_name varchar(256) DECLARE @rec_cnt int FETCH NEXT FROM @tab_list INTO @tab_name, @rec_cnt select count(*) from @tab_name This select is looping around along with FETCH till all the table names are exhausted and their counts are updated from the cursor back into the table. Problem is that, I am not able to use select count(*) from @tab_name, and its not accepting a variable there. Please help me to construct the select statement that is similiar to x=<table name> select * from x where x is a variable and the table name gets substituted. what is the syntax for it ?
View Replies !
Combing In A Cursor, A Select Statement With The WHERE Clause Stored In A Variable
Hi I am ramesh here from go-events.com I am using sql mail to send out emails to my mailing list I have difficulty combining a select statement with a where clause stored in a variable inside a cursor The users select the mail content and frequency of delivery and i deliver the mail I use lots of queries and a stored procedure to retrieve thier preferences. In the end i use a cursor to send out mails to each of them. Because my query is dynamic, the where clause of my select statement is stored in a variable. I have the following code that does not work For example DECLARE overdue3 CURSOR LOCAL FORWARD_ONLY FOR SELECT DISTINCT Events.E_Name, Events.E_SDate, Events.E_City, Events.E_ID FROM Events, IndustryEvents + @sqlquery2 OPEN overdue3 I get an error message at the '+' sign which says, cannot use empty object or column names, use a single space if necessary How do I combine the select statement with the where clause? Help me...I need help urgently
View Replies !
Use TextBox Values In WHERE Clause To Perform Date Search?
I want to have a FromDateTextBox and a ToDateTextBox where the user can enter in dates (most likely in mm/dd/yy format, although intelligently handing other formats might be a plus). Then I want to use these dates as the basis for a WHERE clause like:<some sql...> WHERE start_date BETWEEN 'FromDateTextBox.Text' AND 'ToDateTextBox.Text' (Note this WHERE clause will be used as the basis for an SqlDataSource FilterExpression). 1. I believe the date strings need to be in the format 'yyyy-mm-dd' to search SQL server is this correct?2. What's a decent way to convert the strings from the textboxes to the required format?3. How can I avoid an SQL injection attack?
View Replies !
Error Comparing Textbox Variable To Database Value
hi im trying to code a login page using asp.net, vb.net. i have 2 web-controlled textboxes whose values i want to compare to userID and password stored on a SQL server database called users. When button clicked, call function checklogin. heres the code: Private Sub btn_login_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_login.Click If Page.IsValid Then Dim usersDS As New System.Data.DataSet usersDS = checklogin(userID.Text, password.Text) If usersDS.Tables(0).Rows.Count = 1 Then FormsAuthentication.RedirectFromLoginPage(userID.Text, False) Else error_log.Text = "Invalid Credentials: Please try again" End If End If End Sub Function checklogin(ByVal userID As Char, ByVal password As Char) As System.Data.DataSet Dim connectionString As String Dim dbConnection As New SqlConnection Dim dbCommand As New SqlCommand Dim dataAdapter As New SqlDataAdapter Dim ds As New DataSet connectionString = "server=localhost;user id=sa; password=chaos; Integrated Security=SSPI; database=users" 'server=localhost; database=users; integrated Security=SSPI;Â?@user id=sa; password=chaos dbConnection.ConnectionString = connectionString With dbCommand .Connection = dbConnection .CommandText = "SELECT COUNT (*)Â?@AS pass FROM tbl_users WHERE ((tbl_users.userId = @userId) AND (tbl_users.password = @password))" End With dataAdapter.SelectCommand = dbCommand dataAdapter.Fill(ds) Return ds End Function Heres the error i get: Details of exception: System.Data.SqlClient.SqlException: There is a syntax invalid near line {''1 Source error: Line 75:. CommandText = "declare@userID As varChar; declare @password As varChar; End With .."Line 76:.. line 77:of SELECT COUNT(*) AS pass FROM tbl_users WHERE((tbl_users.userId=@userId)AND(tbl_users.password=@password)) Line 78: DataAdapter.SelectCommand=dbCommand Line 79: DataAdapter.Fill(ds) ive just started using .net, im a lil lost...thanks for any help. cheers
View Replies !
Defining Command,commandtype And Connectionstring For SELECT Command Is Not Similar To INSERT And UPDATE
i am using visual web developer 2005 and SQL 2005 with VB as the code behindi am using INSERT command like this Dim test As New SqlDataSource() test.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString1").ToString() test.InsertCommandType = SqlDataSourceCommandType.Text test.InsertCommand = "INSERT INTO try (roll,name, age, email) VALUES (@roll,@name, @age, @email) " test.InsertParameters.Add("roll", TextBox1.Text) test.InsertParameters.Add("name", TextBox2.Text) test.InsertParameters.Add("age", TextBox3.Text) test.InsertParameters.Add("email", TextBox4.Text) test.Insert() i am using UPDATE command like this Dim test As New SqlDataSource() test.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString").ToString() test.UpdateCommandType = SqlDataSourceCommandType.Text test.UpdateCommand = "UPDATE try SET name = '" + myname + "' , age = '" + myage + "' , email = '" + myemail + "' WHERE roll 123 " test.Update()but i have to use the SELECT command like this which is completely different from INSERT and UPDATE commands Dim tblData As New Data.DataTable() Dim conn As New Data.SqlClient.SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Database.mdf;Integrated Security=True;User Instance=True") Dim Command As New Data.SqlClient.SqlCommand("SELECT * FROM try WHERE age = '100' ", conn) Dim da As New Data.SqlClient.SqlDataAdapter(Command) da.Fill(tblData) conn.Close() TextBox4.Text = tblData.Rows(1).Item("name").ToString() TextBox5.Text = tblData.Rows(1).Item("age").ToString() TextBox6.Text = tblData.Rows(1).Item("email").ToString() for INSERT and UPDATE commands defining the command,commandtype and connectionstring is samebut for the SELECT command it is completely different. why ?can i define the command,commandtype and connectionstring for SELECT command similar to INSERT and UPDATE ?if its possible how to do ?please help me
View Replies !
Is It Possible To Re-reference A Column Alias From A Select Clause In Another Column Of The Same Select Clause?
Example, suppose you have these 2 tables(NOTE: My example is totally different, but I'm simply trying to setupthe a simpler version, so excuse the bad design; not the point here)CarsSold {CarsSoldID int (primary key)MonthID intDealershipID intNumberCarsSold int}Dealership {DealershipID int, (primary key)SalesTax decimal}so you may have many delearships selling cars the same month, and youwanted a report to sum up totals of all dealerships per month.select cs.MonthID,sum(cs.NumberCarsSold) as 'TotalCarsSoldInMonth',sum(cs.NumberCarsSold) * d.SalesTax as 'TotalRevenue'from CarsSold csjoin Dealership d on d.DealershipID = cs.DealershipIDgroup by cs.MonthIDMy question is, is there a way to achieve something like this:select cs.MonthID,sum(cs.NumberCarsSold) as 'TotalCarsSoldInMonth',TotalCarsSoldInMonth * d.SalesTax as 'TotalRevenue'from CarsSold csjoin Dealership d on d.DealershipID = cs.DealershipIDgroup by cs.MonthIDNotice the only difference is the 3rd column in the select. Myparticular query is performing some crazy math and the only way I knowof how to get it to work is to copy and past the logic which isgetting out way out of hand...Thanks,Dave
View Replies !
Using A Variable In SSIS - Error - &&"Command Text Was Not Set For The Command Object.&&".
Hi All, i am using a OLE DB Source in my dataflow component and want to select rows from the source based on the Name I enter during execution time. I have created two variables, enterName - String packageLevel (will store the name I enter) myVar - String packageLevel. (to store the query) I am assigning this query to the myVar variable, "Select * from db.Users where (UsrName = " + @[User::enterName] + " )" Now in the OLE Db source, I have selected as Sql Command from Variable, and I am getting the variable, enterName,. I select that and when I click on OK am getting this error. Error at Data Flow Task [OLE DB Source [1]]: An OLE DB error has occurred. Error code: 0x80040E0C. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E0C Description: "Command text was not set for the command object.". Can Someone guide me whr am going wrong? myVar variable, i have set the ExecuteAsExpression Property to true too. Please let me know where am going wrong? Thanks in advance.
View Replies !
Why Can't I Use Columns Based On Case Down In My Where Clause
I am writing a fairly simple sql, and I would like to write something like Code Snippet select firstname as firstname, case when firstname = 'Peter' then 'yes' else 'no' end as whatever from MyTable where whatever = 'yes' And this should then select out the rows where column number 2 is 'yes'. It doesn't work, and I have to copy the firstname = 'Peter' into the where clause. But why?
View Replies !
Query Based On Group By Clause
Hello! suppose i have two tables, table1 columns(empcode (pk), empDept) and table2 columns(empcode (FK),Date,Attendance) i wanted to write a query to get output like DEPT ABSENT ----------------------------- Accounts 10 EDP Section 0 ** Admin 2 Stationary 0** if no employee is absent in the department it has to display Zero
View Replies !
SELECT TOP (textbox Value)
Is it possible to do this as i want to select the top based on the value i enter in the textbox. usually we using this SELECT TOP 10 * FROM Customers But i want the value is get from my textbox.
View Replies !
Need Help Selecting Data With Date -based Where Clause
OK. I have this query, works on another box fine. SELECT * FROM bookkeep RIGHT OUTER JOIN acraccts ON LEFT(bookkeep.accnum, 9) = acraccts.p_accnum WHERE (bookkeep.busdate = '03/09/10') AND (bookkeep.tradetype = 'S') on my sql box, if i run it, i get no data. i figured out that if i change the where clause to (bookkeep.busdate='2003/09/10') it works OR if i simply put SET DATEFORMAT YMD on the first line before the SELECT * that it also works. my problem is the basic query is hard coded and i really can't change it. is there a global sql server setting that will make my sql 2000 sp3 box recognize '30/09/10' as 2003/09/10?
View Replies !
Where Clause Changing Based On NULL Column(s)
Hello DBAs: I am having difficulty constructing a where clause. I have 4 columns in a table. Col1 is never NULL. Col2,3,4 may or may not contain NULL values. Here is the situation. 1. Col2,3,4 are NULL WHERE Col1 = condition 2. Col2 is NULL, 3 may or may not be NULL. When Col3 is NOT NULL, Col4 may or may not be NULL where col1 and (col3(Not Null values) OR Col4(Not Null values)) = condition. How do I construct this logic. Please help
View Replies !
STOPAT Clause In Restore Database Command
Hi, I have my backup scheduled as shown below at 9.00am -- full backup at 9.15am - Transaction log backup at 9.30am - Transaction log backup Suppose at 9.20am(say) the user deletes a table and i want to restore that particular table,i will use STOPAT clause when im restoring the transaction backup at 9.30am (WITH RECOVERY).But my doubt is how to find out the exact time the user has deleted the table.I have even checked the SQLServer Error log file but it doesnt give the exact date and time stamp of the deletion activity tht has taken place so that i can use that in STOPAT clause of RESTORE LOG command. Please tell me where should i look for date and timestamp for all the Deletions/Updations done on a particular database. Thanks in advance. Regards Arvind L
View Replies !
Change Sproc Where Clause Based On Parameter Null
I have an optional param passed to my sproc @Pid if that is populated i want to have it part of where clause, if it is null then not in where I know this is wrong, and very new to sqlsrver: WHERE (Pos = 'T') AND (ofDate= @OfDate) IF not @PID is null AND (PID = @PID ) GROUP BY bla bla bla Thanks
View Replies !
Populate A Textbox Using A Select Statement
I want to create a details view page without using the DetailsView control. I would prefer to have separate textboxes or labels for each column. I just don't have a clue how to go about after the Select is done putting the data into the boxes. Below is as far as I've gotten. The purpose of the label is just my way of wanting to display the QueryString value, it serves no other purpose. protected void Page_Load(object sender, EventArgs e) { { string tktid = Request.QueryString["id"].ToString(); Label1.Text = tktid.ToString(); } { SqlCommand cmd = new SqlCommand("Select tkt_creator from Ticket where tkt_id = @tktid", HelpdeskConnectionString); HelpdeskConnectionString.Open(); cmd.ExecuteReader(); Label2.Text = ?; } Any help would be appreciated. So far, I've learned a lot from this forum. Y'all have been great.
View Replies !
SELECT Then DELETE Versus Extra Clause In SELECT
Far below (in section "original 3 steps"), you see the following:1. a temp table is created2. some data is inserted into this table3. some of the inserted data is removed based on a join with the sametable that the original select was made fromIn my opinion, there is no way that the join could produce more rowsthan were originally retrieved from viewD. Hence, we could get rid ofthe DELETE step by simply changing the query to be:INSERT INTO #details ( rec_id, orig_corr, bene_corr )SELECT rec_id, 0, 0FROM viewDWHERE SOURCE_SYS NOT IN ( 'G', 'K' )AND MONTH( VALUE_DATE_A8 ) = MONTH( @date )AND YEAR( VALUE_DATE_A8 ) = YEAR( @date )AND INMESS NOT LIKE '2__' ---- the added line===== original 3 steps (mentioned above) =====CREATE TABLE #details (rec_id UNIQUEIDENTIFIER PRIMARY KEY NOT NULL,orig VARCHAR(35) NULL,bene VARCHAR(35) NULL,orig_corr TINYINT NULL,bene_corr TINYINT NULL)INSERT INTO #details ( rec_id, orig_corr, bene_corr )SELECT rec_id, 0, 0FROM viewDWHERE SOURCE_SYS NOT IN ( 'G', 'K' )AND MONTH( VALUE_DATE_A8 ) = MONTH( @date )AND YEAR( VALUE_DATE_A8 ) = YEAR( @date )DELETE dFROM #details dJOIN viewD v ON ( d.rec_id = v.rec_id )WHERE INMESS LIKE '2__'
View Replies !
Expression Defined In SELECT Clause Overwrites Column Defined In FROM Clause
2 examples: 1) Rows ordered using textual id rather than numeric id Code Snippet select cast(v.id as nvarchar(2)) id from ( select 1 id union select 2 id union select 11 id ) v order by v.id Result set is ordered as: 1, 11, 2 I expect: 1,2,11 if renamed or removed alias for "cast(v.id as nvarchar(2))" expression then all works fine. 2) SQL server reject query below with next message Server: Msg 169, Level 15, State 3, Line 16 A column has been specified more than once in the order by list. Columns in the order by list must be unique. Code Snippet select cast(v.id as nvarchar(2)) id from ( select 1 id union select 2 id union select 11 id ) v cross join ( select 1 id union select 2 id union select 11 id ) u order by v.id ,u.id Again, if renamed or removed alias for "cast(v.id as nvarchar(2))" expression then all works fine. It reproducible on Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2) and Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2) In both cases database collation is SQL_Latin1_General_CP1251_CS_AS If I check quieries above on database with SQL_Latin1_General_CP1_CI_AS collation then it works fine again. Could someone clarify - is it bug or expected behaviour?
View Replies !
Using A Variable In The FROM Clause
Is it possible to have a variable stand for the table names in a FROM clause for a select statement? If not, does anyone have any suggestions on how do do this instead? Thanks for any and all help, Michelle
View Replies !
Using Variable In Where Clause
I am trying to use a local variable and case statment in a where clause but I keep getting an error. I'm passing an input parameter into a stored procedure and based on the input parameter I want the WHERE clause to reflect. spOpenClosedList @openclose varchar(5) DECLARE @result varchar(12) if @openclose = 'open' Begin set @result = 'is null' end else set @result = 'is not null' SELECT * FROM WorkOrderTbl WHERE WorkOrderTbl.CompletedDate @result AND username = "Thomas"
View Replies !
Variable In Where Clause
Hi Gurus, Can any one help me making thhe following query work without using dynamic sql? use northwind declare @fc varchar set @fc = ' CustomerID ' Select * from dbo.Customers where @fc like 'a%' Thanks a lot in adv Korr
View Replies !
Using Variable In SQL IN Clause
I have a table name emp(empid) having one record as emp1. Now i am writing DECLARE @EmpID VARCHAR(8000) SET @EmpID = '''Emp1'',''Emp2''' Select * from emp where empid in(@empid) But there are no rows returned can't i use variable in IN Clause. I have to achiev this backend only.
View Replies !
SQL Select Statement (Textbox Used To Grab Data From Database)
I have a problem....SOMEONE PLEASE HELP! Here is the setup. Text Box: User enters in customer transaction number Button: User clicks button to display information about the customer Now the database has a lot of unique customer numbers. What I am trying to do is take what the user enters so it can search the database and pull out that customers information. I am having a hard time getting that information from the textbox. Any suggestions! Here is what I have so far. Private Sub btnViewFlow_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnViewFlow.Click Try Me.SqlConnection1 = New System.Data.SqlClient.SqlConnection Me.SqlDataAdapter1 = New System.Data.SqlClient.SqlDataAdapter Me.SqlSelectCommand1 = New System.Data.SqlClient.SqlCommand Me.DataSet11 = New links.DataSet1 CType(Me.DataSet11, System.ComponentModel.ISupportInitialize).BeginInit() ' 'SqlConnection1 ' Me.SqlConnection1.ConnectionString = "workstation id=<14852>;packet size=4096;user id=<userID>;password=<Strong Password>;data source=ZRTPD0WB;p" & _ "ersist security info=False;initial catalog=DTR" ' 'SqlDataAdapter1 ' Me.SqlDataAdapter1.SelectCommand = Me.SqlSelectCommand1 Me.SqlDataAdapter1.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "DTR_Document_Summary", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("DocumentId", "DocumentId"), New System.Data.Common.DataColumnMapping("PartnerId", "PartnerId"), New System.Data.Common.DataColumnMapping("PartnerName", "PartnerName"), New System.Data.Common.DataColumnMapping("Direction", "Direction"), New System.Data.Common.DataColumnMapping("TranSet", "TranSet")})}) ' 'SqlSelectCommand1 ' Me.SqlSelectCommand1.CommandText = "SELECT DocumentId, PartnerId, PartnerName, Direction, TranSet FROM DTR_Document_S" & _ "ummary " 'WHERE (DTR_Document_Summary.PartnerId = 'txtPartnerId.text.toString')" 'THE STATEMENT ABOVE DOESNT WORK Me.SqlSelectCommand1.Connection = Me.SqlConnection1 ' 'DataSet11 ' Me.DataSet11.DataSetName = "DataSet1" Me.DataSet11.Locale = New System.Globalization.CultureInfo("en-US") CType(Me.DataSet11, System.ComponentModel.ISupportInitialize).EndInit() 'Open the connection SqlConnection1.Open() TextBox1.Text = "Connection Open" 'Populate DataSet11 SqlDataAdapter1.Fill(DataSet11) TextBox1.Text = "DataSet11 has been filled!" 'Display Data DataGrid1.DataBind() TextBox1.Text = "Here is your requested information" Catch ex As Exception TextBox1.Text = ex.Message End Try 'Close the connection SqlConnection1.Close() End Sub
View Replies !
SQL WHERE Clause Passing In Variable
Hi All, Would somebody be able to help me from pulling my hair out!??I have a form with a radiobuttonlist. I would like to change my select statement depending on what radiobutton value is selected.E.g.SELECT * FROM table WHERE <<variable from radiobuttonlist>> LIKE 'Y'So,if radiobutton value selected = 1, it will select * from column A in the dbif radiobutton value select = 2, it will select from column B in the dband so on... Am i attempting to do the impossible?Thanks All,
View Replies !
Order By Clause Using A Variable
I am trying to pass as an input parameter a user selected order by clause, and instead of repeating the SQL statement with a new Order By based on the parameter, I want to set the Order by using this parameter. I can't get it to work. Here is the statement: Create Procedure sp_InfoDump ( @StartDate varchar(12), @EndDate varchar(12), @OrderBy varchar(50) ) As /* Local variables */ DECLARE @MinDate datetime, @MaxDate datetime IF @StartDate = 'ALL DATES' BEGIN SELECT @MinDate = Min(AccessTime) FROM tblAudit END ELSE BEGIN SELECT @MinDate = @StartDate END IF @EndDate = 'ALL DATES' BEGIN SELECT @MaxDate = Max(AccessTime) FROM tblAudit END ELSE BEGIN SELECT @MaxDate = @StartDate END BEGIN SELECT tblReports.ReportName, tblReports.ReportCode, tblAudit.BadAttempts, tblAudit.LogonUser, tblAudit.AccessTime, tblAudit.RemoteHost, tblAudit.RemoteIdent, tblAudit.ExitTime, tblAudit.BrowserType, tblAudit.Access_ID, TotalTime=DateDiff(Minute,tblAudit.AccessTime,tblA udit.ExitTime) FROM tblReports INNER JOIN tblReportsAccess ON tblReports.Report_ID = tblReportsAccess.Report_ID INNER JOIN tblAudit ON tblReportsAccess.Audit_ID = tblAudit.Audit_ID WHERE tblAudit.AccessTime >= @MinDate AND tblAudit.AccessTime <= @MaxDate ORDER BY (SELECT 'ColumnName'=ColumnName FROM tblOrderBy WHERE ColumnName = @OrderBy) END RETURN
View Replies !
Using A @Variable As An IN Clause Expression
Is there a good way to use a @variable in a Stored procedure to represent the expressions for an IN clause I.e. I need to accomplish the following: Create Procedure sp_ABC @Variable VarChar (1024) AS Select * From tblAnyTable Where intTableID IN (@Variable) Is there a valid way to accomplish this? The above code presented here does not work, because the VarChar cannot be translated into int.
View Replies !
CONTAIN Clause Not Taking A Variable?
Hi, I created a full-text catalog on a table and ran a contain clause against it with a declare variable on the search. Example.... DECLARE @chrClause VARCHAR(204) SET @chrClause = ' "XXXX" ' SELECT ID FROM Emp WHERE CONTAINS(Tax_Nam, @chrClause) I'm getting an error that says... Line 8: Incorrect syntax near '@chrContainClause'. Is this a problem i can fix by applying SP3?
View Replies !
Error: A Rowset Based On The SQL Command Was Not Returned By The OLE DB Provider.
I have a dataflow task. On which I have OLEDB as my source. I connect to my database and execute a stored proc. the stored proc results in a result set with only one row and two columns. First Column is an integer and the second row is a varchar(max) with xml script in it. Not that it should matter because it is in varchar(max). Anyway, it give me an error [OLE DB Source [321]] Error: A rowset based on the SQL command was not returned by the OLE DB provider. What am I doing wrong? Can I not have a stroed proc that returns a result set as my data source?
View Replies !
DMX Export Command On OLAP Based Mining Structure
Hi, I've tried to use the export command to export a mining model from management studio, but it returns that export statement is not supported for OLAP mining structures, Ive checked the EXPORT(DMX) reference I can't see any note that it is not applicable on OLAP structures. Can anyone confirm this? -Rgds, Sheryaar
View Replies !
Variable To Store String For Where Clause
I want to declare a variable that will serve as my where clause. The variable will be passed in from our website. Does anyone have any information or can guide me in the right direction to do this? example: @variable varchar(1000) SELECT * FROM table WHERE @variable (The @variable would reflect the where clause string) Thanks!
View Replies !
Cursor Declared With Variable In Where Clause
When I execute next query on sqlserver 6.5 nested in stored procedure I can see that 'open testCursor' selected rows using new value of @var. When I execute query on sqlserver 7.0 I can see that 'open testCursor' selected rows using value of @var before 'declare ... cursor'. Is there any way to force sqlserver 7.0 to proccess cursor like it did it before. select @var = oldValue declare testCursor cursor for select someColumns from someTable where someColumn = @var select @var = newValue open testCursor fetch next from testCursor into @someColumns Thank's in advance. Mirko.
View Replies !
SQL Cache Dependency Invalidation - Can It Work On The Procedure Based Command?
I was wondering if SQL Cache Dependency would be in fact invalidated if: 1. it was created based on a procedure type command. 2. if the select statement retrieves the data from multiple database tables Any help would be more appreciated. I am stuck with the fact that none of the data bases on sql dependency is invalidated. I spent literally hours to understand what i am doing incorrectly. Thanks
View Replies !
SQL COMMAND FROM VARIABLE
KEEPS SAYING MISSING EXPRESSION: IN THE VARIABLE EXPRESSION I'VE WRITTEN THE FOLLOWING COMMAND: "SELECT * FROM MY_TABLE WHERE T1.DATE >= @[USER:ATE]" I'VE CLICKED EVALUATE EXPRESSION AND IT ACCEPTS THE EXPRESSION HOWEVER WHEN I GO TO DATA FLOW TO SELECT SQL COMMAND FROM VARIABLE IT BRINGS BACK MISSING EXPRESSION????
View Replies !
Command USE With Variable
Why I can´t use the command USE with variable? declare @banco varchar(20) select @banco='xpto' USE @banco I have try in other way without success DECLARE @banco varchar(50) declare @SQL varchar(50) select @banco='xpto' print @banco select @SQL='USE '+@banco print @SQL exec(@SQL) Help me, please. Rodrigo
View Replies !
SQL Command From Variable
I'm having difficulty passing a date parameter in a SSIS OLEDB connection to a stored procedure using SQL command with the error message "Attempted to read or write protected memory ..." and if I create a variable, I get the error "must declare the scalar variable..". Here is the OLEDB command: "EXEC dbo.mic_TGDrop ?" where ? requires a date parameter @ReportDate. Why does the call to the stored procedure fail? What would be the correct syntax of a variable to use SQL Command from variable? The SQL BOL does not cover this; is there another resource that has a simple example? Thanks.
View Replies !
How Do I Set A Variable Value Based On A SQL Query
I have a SQL query that finds a value which I need to pass into an SSIS variable so I can use it later to set the filename for my data flow task. So far, I have been unsuccessful in determining how this is done. google searches, etc seem to be fruitless for me, there are so many hits but none really seem to address what I want to do because there are so many things you can do with variables. Likewise the books online and the boks I have on SSIS also only give a very high level of info. I need some examples, if anyone could help I would appreciate it. Here is the basic gist of my query: exec SP_GET_FILE_NAME @file_name OUTPUT I need to take the resulting @file_name and store it in an ssis variable. How?
View Replies !
Passing Variable To Where Clause In Sql Query From A Grid
Hi everyone I am new to this site I have a major issue I cant figure out seeing how im fairly new to asp, and vb, but i have 5 years php mysql experience. Im pulling the correct data into a grid. Then i need to make a button or some sort of link that will take the value of one field in the record set and replace it with @transid in the where statement I can enter in the value of transid into form field with that name and it will run the rest of the script correctly, I just cant get past this hurdle. If anyone can help that would be great. I tried to get this to work with java script but then realized thats not possible to transfer varaibles to asp from it. ///javascript function DisplayReciept(transactionnum) { recieptdis = transactionnum; } ////field in grid <asp:BoundField htmlEncode=false DataFormatString="<a href=javascript:DisplayReciept{0}>Display</a>" DataField="transid" HeaderText="Show Reciept" SortExpression="transid" /> //////////////query////////////// <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:dbsgcConnectionString %>" SelectCommand="SELECT [fulldata] FROM [data] WHERE ([transid] = @transid)"> <SelectParameters> <asp:FormParameter FormField="transid" Name="transid" Type="Int32" /> </SelectParameters> </asp:SqlDataSource>
View Replies !
How To Include Variable In CURSOR SQL Filter Clause?
After trying every way I could come up with I can't get a filter clauseto work with a passed variable ...I have a cursor that pulls a filter string from a table (works OK),then I want to use that filter in a second cursor, but can't get thesyntax ...@bakfilter is equal to "MISV2_db_%.BAK" before I try to open and fetchfrom the second cursor. Here is the cursor declaration:DECLARE curFiles CURSOR FORSELECT FileName, FileDateFROM DataFileWHERE (((Active)=1) AND ((FileName) LIKE '@bak_filter'))ORDER BY FileDate DESCWhat do I need to do to get it to use the string contained in@bak_filter?Thanks in advance, Jim
View Replies !
Using A Stored Procedure Variable To Define A Where Clause
Hi all, I'm trying to build a Where clause in a stored procedure based on the information that is passed into the stored procedure. Because I don't know how many items will be passed into the stored procedure, I'm having to split the string on a specific character and build the Where clause based on how many strings are found. However, when I try to execute the Where clause it throws an error. Code Snippet ALTER PROCEDURE getUsersAddress -- Add the parameters for the stored procedure here @LName varchar(1000) @City varchar(1000), @State varchar(1000), @License varchar(1000) AS declare @Count as int declare @x as int declare @wLName as varchar(2000) declare @wCityas varchar(2000) declare @wStateas varchar(2000) declare @wLicense as varchar(2000) BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; if right(rtrim(@LName),1) <> ';' begin set @LName = @LName + ';' end set @Count = PATINDEX('%;%',@LName) set @wLName = '(tblUsers.LName = ''' while @Count <> 0 begin set @wLName = @wLName + left(@LName, @Count - 1) set @LName = stuff(@LName, 1, @Count, '') set @Count = PATINDEX('%;%',@LName); if @Count <> 0 begin set @wLName = @wLName + ''') OR (tblUsers.LName = ''' end else begin set @wLName = @wLName + ''')' end end print cast(@wLName as varchar(5000)) -- Insert statements for procedure here SELECT tblUsers.FullName, tblUsers.Addy1, tblUsers.Addy2, tblUsers.City, tblState.StateAbbr, tblUsers.Zip, tblUsers.Zip4 FROM tblUsers INNER JOIN tblState ON tblUsers.FK_StateID = tblState.StateID INNER JOIN tblUserDetails ON tblUsers.UserID = tblUserDetails.FK_UserID WHERE (@wLName) OR (tblUsers.City = @City) OR (tblState.StateAbbr = @State) OR (tblUserDetails.CDLType = @License) END So when I print do an exec getUsersAddress 'Johnson;Smith', 'City;Test City', 'TX;OK', 'Class A;Class B' @wLName comes out as (tblUsers.LName = 'Johnson') OR (tblUsers.LName = 'Smith') However, I can't save the procedure as it gives me an error of: An expression of non-boolean type specified in a context where a condition is expected, near 'OR'. When I copy and paste the @wLName value in place of @wLName in the Where clause it works, so how can I get the @wLName variable to work in that Where clause?
View Replies !
Using Variable String In Sql Command
Using SQL Server 7.0, I am trying to use a variable string called from a form to perform a simple query. Here's what it looks like: [after submitting a form on the previous page with a drop down list box titled "fiscal"] dim strYear strYear = Request.Form("fiscal") sql = "SELECT * FROM VENDOR WHERE STATUS = 'P' AND '"& strYear &"';" [where strYear is the following: PYMNT_DT > ''12/31/98''] Records exist that meet this criteria. If the PYMNT_DT > '12/31/98' is hardcoded into the sql statement, 12 records are returned. However, when the variable string is used instead, no records are found. Any ideas on how this problem might be fixed? Thanks in advance. Sincerely, Chad Massie
View Replies !
Can't Set Variable Through Command Line
Hello everyone! Bit of a problem executing a DTS command from a command line. I have the following variables defined in my package: UserVarchar1 UserVarchar2 UserVarchar3 All have a scope of Package, all of the mstrings The command I'm attempting to run in 1 line is: DTEXEC /FILE "C:SSIS PackagesED-CustomersPackage.dtsx" /SET Package.Variables[User::UserVarchar1].value;"10" /SET Package.Variables[User::UserVarchar2].value;"30719" /SET Package.Variables[User::UserVarchar3].value;"BILLTO" Description: The package path referenced an object that cannot be found: "Package.Variables[User::UserVarchar1].value". This occurs when an attempt is made to resolve a package path to an object that cannot be found. DTExec: Could not set Package.Variables[User::UserVarchar1].value value to 10. Any idea why? Thanks for the help!
View Replies !
Creating SQL Command From Variable
This is a really simple question. When I set my variable (the one will hold my SQL Command) property "EvaluateAsExpression=True", I don't get the option to edit the expression, I was expecting to get a little button that would open the "Property Expressions Editor" if pressed. Am I doing anything wrong? Should I edit my SQL Command somewhere else and then Copy+Paste on my variable expression? I need to pass a ServerName as a field to my query and I'm doing that by looping through a list of servers...
View Replies !
OLE DB Source, SQL Command From Variable
I read several posts on this topic and I would like to confirm my understanding. This question has to do with parameterizing the select statement in the OLE DB Source editor. Initially, I thought I could use Data access mode: SQL command, and somehow use a user variable in there to build my Select statement. But in researching further, it looks like I need to store the entire SQL command in a one long string variable (let's call it A), where A is built off of another variable that has my parameter. I then use A in Data access mode: SQL command from variable. Am I correct? And is there not a way to accomplish the samething with only one variable that is your parameter value? It seems a bit clumsy to store the entire SQL expression in a string variable.
View Replies !
|
TRACKER
