How Do You Place A Variable In Your Gridview Sql Statement?
I have the below select command in my gridview that uses a library name.table approach in the sql. I created a label on the page and want to pass the label in where i have the libarary1 text in the select statement. This will allow me to swap out the production library with my development library when im working on the site (thru the use of a site variable)
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:as400con %>"ProviderName="<%$ ConnectionStrings:as400con.ProviderName %>
" SelectCommand="SELECT * from library1.table WHERE COMPANY = ? and ack = ? and HORZN_DATE <= ?">
<selectparameters>
<asp:controlparameter name="company" controlid="accts" propertyname="SelectedValue"/>
<asp:controlparameter name="ack" controlid="podropdown" propertyname="SelectedValue"/>
<asp:controlparameter name="dt" controlid="htoday" propertyname="Text"/>
</selectparameters>
How would I insert a label.text value into the select statement replacing the library1 ? I tried to use the asp:controlparameter adding the text and placing a ? mark in place of the library1 but it failed.
Thanks for any help you can lend on this,
Todd
View Complete Forum Thread with Replies
Related Forum Messages:
Can You Use A VARIABLE In Place Of A Database Name?
Does anyone know if it is possible to use a variable in place of a database..table combination in a select statement For Example: Instead of using the following with each database hardcoded in the SP: select @dataused = sum(b.reserved) from DBSglep..sysindexes b where b.indid in (0, 1, 255) and segment != 2 I would like to loop for every database listed in sysdatabases and do this: select @dbname = @dataname+'..sysindexes' select @dataused = sum(b.reserved from @dbname b where b.indid in (0, 1, 255) and segment != 2 I have got the loop working, but just can't get the name substitution working as MSSQL dosn't seem to allow a variable after the FROM statement (it only seems to work with a hardcoded specific database..table name). Any assistance in resolving this problem would be greatly appreciated! :-) Many thanks in advance... Regards, Wayne
View Replies !
Using A Temp Table And Variable With Gridview
Hi all, Im trying to use a temporary table along with a gridview. But i keep getting an issue that says that I need to declare the variable name. Right now, I have it set up that I have a permanent table in the database that is serving as my temporary table, however Im running into issues if multiple users access at once... So I would like to dynamically create a table and use that for my temporary table and then drop the table when the user is done with it... heres my code so far..Private Sub AddRecords(ByVal prodins As String, ByVal releaseins As String, ByVal opsysins As String, ByVal currelins As String, ByVal schemains As String, ByVal commentins As String, ByVal freeformins As String) Dim tablevar As String = lblRequestor.TextDim tablename As String = "##" + tablevar Dim myConnection As New SqlClient.SqlConnection Dim query As String = "CREATE TABLE " + tablename + "(UniqueID int Identity(1,1), Product varchar(50), OperatingSystem varchar(50), CurrentRelease varchar(50), Release varchar(50), SchemaSize varchar(50), Comments varchar(50), TempRelease varchar(50), FreeFormFlag varchar(50), sourcereleasedir varchar(125))" Dim query2 As String = "INSERT INTO " + tablename + "(Product, Release, OperatingSystem, CurrentRelease, SchemaSize, Comments, TempRelease, FreeFormFlag) VALUES ('" & prodins & "','" & releaseins & "', '" & opsysins & "', '" & currelins & "', '" & schemains & "', '" & commentins & "', '" & currelins & "', '" & freeformins & "')"Dim myCommand As SqlClient.SqlCommandmyConnection = New SqlClient.SqlConnection("Data Source=mydatasource") myConnection.Open()myCommand = New SqlClient.SqlCommand(query, myConnection) myCommand.ExecuteNonQuery()myCommand = New SqlClient.SqlCommand(query2, myConnection) myCommand.ExecuteNonQuery() myConnection.Close() And here is my query for the gridview.... SelectCommand="SELECT * FROM @tablename where FreeFormFlag <> 'Y'" and <SelectParameters> <asp:ControlParameter ControlID="tablename" Name="tablename" PropertyName="Text" DefaultValue="##default" /> </SelectParameters> so @tablename is set to the value of a textbox i have hidden which the textbox gets the username of the user and adds ## to it..so if john doe comes on... the texbox reads ##jdoe... and thats what im trying to name the temp table. SO the syntax is all there.... But i keep getting an error saying i need to declare @tablename... which is puzzling to me.. I tried doing declare @tablename varchar (50) select.....etc. but then it brings up an error saying... @tablename is already declared, each variable must be unique..., You must declare @tablename... It tells me that its already declared then tells me i must declare it??? it makes no sense.. Anyone have any ideas? Thanks,
View Replies !
Need Gridview To Display Different Columns Based On New SELECT Statement
I have built an Advanced Search page which allows users to select which columns to return (via checkbox) and to enter search criteria for any of the selected columns (into textboxes). I build the SQL statement from the properties of the controls. Works great. My problem is getting my gridview control to play nicely. At first I used a SqlDataReader and bound the gridview to it, thus giving me the ability to run new SQL statements through it (with different columns each time). Worked nicely. But, per Microsoft, sorting can only be done if the gridview is bound to a datasource control like the SqlDataSource. So I wrote the code to handle sorting. No big deal; worked nicely. But I could not adjust the column widths programmatically unless bound to a datasource control like the SqlDataSource. And could not figure out a work around. So, I decided to use the SqlDataSource. Works great. Except, I cannot figure out how to run a new SELECT statement through the SQLDataSource and have the gridview respond accordingly. If I try to return anything other than the exact same columns defined declaratively in the html, it pukes. But I need to be able to return a new selection of columns each time. For example, first time through the user selects columns 1,2,3,4 – the gridview should show those 4 columns. The second time the user selects columns 2,5,7 – the gridview should those 3 columns (and ONLY those 3 columns). Plus support selection and sorting. I am desperate on this. I've burned 2.5 days researching and testing. Does anyone have any suggestions? Thanks, Brad
View Replies !
[:#] Problems Viewing The Custom Sql Statement In The Gridview Control
Hi, I really need some help trying to figure out why my gridview is not working when I create a custom sql statement. It "executes" the query, but gives me an error message when I "test the query". Here is the error message: "There was an error executing the query. Please check the syntax of the command and if present, the types and values of the parameters and ensure they are correct. Syntax error: Expecting '.', identifier or quoted identifier." Here is my sql statement: SELECT TBLPROJECTS.NAME, TBLPROJECTTYPES.NAME AS PROJECTTYPE, TBLPROJECTS.DESCRIPTION, TBLUSERS_1.LOGIN AS OWNERNAME, TBLUSERS.LOGIN AS MANAGERNAME, TBLPROJECTS.START_DATE, TBLPROJECTS.END_DATE, TBLAOI.NAME AS AREAOFINTEREST, TBLPROJECTS.MANPOWER, TBLUNITS.NAME AS MANPOWERUNITFROM TBLPROJECTS INNER JOIN TBLAOI ON TBLPROJECTS.AOI_ID = TBLAOI.ID INNER JOIN TBLPROJECTTYPES ON TBLPROJECTS.PROJECTTYPE_ID = TBLPROJECTTYPES.ID INNER JOIN TBLUNITS ON TBLPROJECTS.MANPOWERUNITS_ID = TBLUNITS.ID INNER JOIN TBLUSERS ON TBLPROJECTS.MANAGER_ID = TBLUSERS.ID INNER JOIN TBLUSERS TBLUSERS_1 ON TBLPROJECTS.OWNER_ID = TBLUSERS_1.ID I have tested it on a new project and still it does not work, I cannot find any problem, please help!!!!!!!!!!!!!!!!!!!!!!!!
View Replies !
GridView Based On SQLServerDataSource Using A Select Union Statement, Impacts On Update And Insert?
I have a GridView dispalying from a SQLServerDataSource that is using a SQL Select Union statement (like the following): SELECT FirstName, LastNameFROM MasterUNION ALLSELECT FirstName, LastNameFROM CustomORDER BY LastName, FirstName I am wondering how to create Update and Insert statements for this SQLServerDataSource since the select is actually driving from two different tables (Master and Custom). Any ideas if or how this can be done? Specifically, I want the Custom table to be editable, but not the Master table. Any examples or ideas would be very much appreciated! Thanks, Randy
View Replies !
Want Error Message To Appear When No Database Results Were Returned In GridView, Also Other GridView Issues.
Hi, I am seeking a hopefully easy solution to spit back an error message when a user receives no results from a SQL server db with no results. My code looks like this What is in bold is the relevant subroutine for this problem I'm having. Partial Class collegedb_Default Inherits System.Web.UI.Page Protected Sub submit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles submit.Click SqlDataSource1.SelectCommand = "SELECT * FROM [college_db] WHERE [name] like '%" & textbox1.Text & "%'" SqlDataSource1.DataBind() If (SqlDataSource1 = System.DBNull) Then no_match.Text = "Your search returned no results, try looking manually." End If End Sub Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load SqlDataSource1.SelectCommand = "SELECT * FROM [college_db] ORDER BY [name]" SqlDataSource1.DataBind() End Sub Protected Sub reset_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles reset.Click SqlDataSource1.SelectCommand = "SELECT * FROM [college_db] ORDER BY [name]" SqlDataSource1.DataBind() End SubEnd Class I'm probably doing this completely wrong, I'm a .net newb. Any help would be appreciated. Basically I have GridView spitting out info from a db upon Page Load, but i also have a search bar above that. The search function works, but when it returns nothing, I want an error message to be displayed. I have a label setup called "no_match" but I'm getting compiler errors. Also, next to the submit button, I also have another button (Protected sub reset) that I was hoping to be able to return all results back on the page, similar to if a user is just loading the page fresh. I'd think that my logic would be OK, by just repeating the source code from page_load, but that doens't work.The button just does nothing. One final question, unrelated. After I set this default.aspx page up, sorting by number on the bottom of gridview, ie. 1,2,3,4, etc, worked fine. But now that paging feature, as long with the sorting headers, don't work! I do notice on the status bar in the browser, that I receive an error that says, "error on page...and it referers to javascript:_doPostBack('GridView1, etc etc)...I have no clue why this happened. Any help would be appreciated, thanks!
View Replies !
Cannot Set A Variable From A Select Statement That Contains A Variable??? Help Please
I am trying to set a vaiable from a select statement DECLARE @VALUE_KEEP NVARCHAR(120), @COLUMN_NAME NVARCHAR(120) SET @COLUMN_NAME = (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'CONTACTS' AND COLUMN_NAME = 'FIRSTNAME') SET @VALUE_KEEP = (SELECT @COLUMN_NAME FROM CONTACTS WHERE CONTACT_ID = 3) PRINT @VALUE_KEEP PRINT @COLUMN_NAME RESULTS ------------------------------------------------------------------------------------------- FirstName <-----------@VALUE_KEEP FirstName <-----------@COLUMN_NAME SELECT @COLUMN_NAME FROM CONTACTS returns: FirstName SELECT FirstName from Contacts returns: Brent How do I make this select statement work using the @COLUMN_NAME variable? Any help greatly appreciated!
View Replies !
Unable To Update Or Delete GridView Entries... &"Must Declare The Scalar Variable &"@ID1&".&"
Hello, I am having issues and can't see any errors in my code! When attempting to delete a table entry from my SQL database, I get the error "Must declare the scalar variable "@ID1"." even though I declare it as a parameter in my code! Can anyone see an issue with my code below?1 <asp:GridView ID="GridView1" runat="server" AllowSorting="True" 2 DataSourceID="SqlDataSource1" AutoGenerateColumns="False" OnSorted="GridView1_Sorted"> 3 <Columns> 4 <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" /> 5 <asp:BoundField DataField="column1" HeaderText="CO" SortExpression="column1" /> 6 <asp:BoundField DataField="column2" HeaderText="Network" SortExpression="column2" /> 7 <asp:BoundField DataField="column3" HeaderText="C/S/T" SortExpression="column3" /> 8 <asp:BoundField DataField="column4" HeaderText="Date Received" SortExpression="column4" DataFormatString="{0:d}" HtmlEncode="False" /> 9 <asp:BoundField DataField="DESCRIPTION" HeaderText="Description" SortExpression="DESCRIPTION" /> 10 <asp:BoundField DataField="column5" HeaderText="In Service Date" SortExpression="column5" DataFormatString="{0:d}" HtmlEncode="False" /> 11 <asp:BoundField DataField="REMARKS" HeaderText="Remarks" SortExpression="REMARKS" /> 12 <asp:BoundField DataField="ID" HeaderText="ID" InsertVisible="False" ReadOnly="True" 13 SortExpression="ID" /> 14 </Columns> 15 </asp:GridView> 16 <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConduitConnectionString %>" 17 SelectCommand="SELECT [ID], [CO/Area] AS column1, [NW #] AS column2, [C/S/T] AS column3, [DATE REC'D] AS column4, [DESCRIPTION], [I/S DATE] AS column5, [REMARKS] FROM [Inspector Workload] WHERE ([Inspector Name] = @Inspector_Name) ORDER BY [CO/Area], [DATE REC'D]" DeleteCommand="DELETE FROM [Inspector Workload] WHERE (ID = @ID1)" UpdateCommand="UPDATE [Inspector Workload] SET [CO/Area] = @column1, [NW #] = @column2, [C/S/T] = @column3, [DATE REC'D] = @column4, [DESCRIPTION] = @DESCRIPTION, [I/S DATE] = @column5, [REMARKS] = @REMARKS WHERE ([ID] = @ID1)"> 18 <SelectParameters> 19 <asp:QueryStringParameter Name="Inspector_Name" QueryStringField="Name" Type="String" /> 20 </SelectParameters> 21 <DeleteParameters> 22 <asp:Parameter Name="ID" Type="Int32" /> 23 </DeleteParameters> 24 <UpdateParameters> 25 <asp:Parameter Name="column1" Type="String" /> 26 <asp:Parameter Name="column2" Type="Double" /> 27 <asp:Parameter Name="column3" Type="String" /> 28 <asp:Parameter Name="column4" Type="DateTime" /> 29 <asp:Parameter Name="DESCRIPTION" Type="String" /> 30 <asp:Parameter Name="column5" Type="DateTime" /> 31 <asp:Parameter Name="REMARKS" Type="String" /> 32 <asp:Parameter Name="ID" Type="Int32" /> 33 </UpdateParameters> 34 </asp:SqlDataSource> Thanks!Rob.
View Replies !
Using C# Variable With SQL Statement
Greetings everyone, I am trying to use a c# string with an SQL statement in a data adapter (.NET 03) The code works fine and I have a variable called : string test = ..... that takes the needed values. I just need to implement this string in the sql statement. I tried adding this to my query but I only got an empty row: WHERE (login = '" & test & "') WHERE (login = '" + test + "') any ideas? PS: If I change to something like WHERE (login = 'abcdef') I get a result meaning there's something wrong with the way I am putting the variable in the sql query. Again, I am not putting the string in a normal query in my .cs code. this is happening by right clicking the data adapter and configuring the sql statement in the designer window THANKS!
View Replies !
Variable In An Sql Statement
Hi, I've created an sql statement: select * from fin_installment where key_construction = (select ser_construction from fin_construction where key_contract = ' " & variable & " ') order by int_serial which is in an Dataset's TableAdapter. This variable receives its value during the form init and it is an integer. When I start the page the folowing error message is displayed: " An error has occurred during report processing. Exception has been thrown by the target of an invocation. Conversion failed when converting the varchar value ' " & azonosito & " ' to data type int. " So my question is that how can I use variables in sql statement in dataset?
View Replies !
USE Statement With Variable
Hi,I am doing a really simple test with SQL Server 7.0:Using the Query AnalyzerLogged as saLocated in master database#1 USE Test#2 EXEC('USE Test')#1 => the database context is switched to Test#2 => the database is NOT switched???
View Replies !
USE Statement With A Variable?
I'm having some trouble modifing a script to save me tons of work. The script if from Microsoft, and it is used as step 3 in a 6 step process to move MS Great Plains users from one server to another. Anyway, the script runs on only 1 company database at a time, and for most Great Plains environments there would only be 1 or 2 company DBs. But I am administering in an ASP environment and we have over 30 company DBs to move. So, I though I would adapt thier script to iterate over each company DB to do the work (rather than creating 30 separate scripts). So I wrapped their loop with my loop to do the iteration. The problem is that T-SQL will not let me use a variable in a USE statement. I've tried to remove the USE statements, but that added a lot of complexity in the internal loop. What is the best way to do this? Here is the modified code: /* ** Drop_Users_Company.sql ** ** This script will remove all users from the DYNGRP in the company database ** specified. It will then drop the DYNGRP and readd the DYNGRP to the company. ** It will then add all users back to the DYNGRP based on the SY60100 table. ** NOTE: You will need to replace %Companydb% with the company database ** name. */ /* Instead of replacing %Companydb% (in each USE statement) with the name of the single company database that this script is supposed to work on, I've added @cCompany to hold the company DB name through each iteration of the outside cursor/while loop. */ declare @cCompany sysname/* ADDED BY ME FOR THE OUTSIDE LOOP */ declare @cStatement varchar(255)/* Misc exec string */ declare @DynDB varchar(15)/* DB Name exec string */ declare @DYNGRPgid int/* Id of DYNGRP group */ /* ** Loop through all company databases, emptying the DYNGRP group. */ SET QUOTED_IDENTIFIER OFF use DYNAMICS /* Select all of the Great Plains database names from the DB_Upgrade table, where the DB names are conviently stored */ declare C_cursor CURSOR for select db_name from DYNAMICS..DB_Upgrade where db_name not in ('DYNAMICS') OPEN C_cursor FETCH NEXT FROM C_cursor INTO @cCompany WHILE (@@FETCH_STATUS <> -1) begin use @cCompany select @DYNGRPgid = (select gid from sysusers where name = 'DYNGRP') declare G_cursor CURSOR for select "sp_dropuser [" + name+"]" from sysusers where gid = @DYNGRPgid and name <> 'DYNGRP' set nocount on OPEN G_cursor FETCH NEXT FROM G_cursor INTO @cStatement WHILE (@@FETCH_STATUS <> -1) begin EXEC (@cStatement) FETCH NEXT FROM G_cursor INTO @cStatement end DEALLOCATE G_cursor /* ** Do not delete the group to attempt to preserve the permissions already ** granted to it. */ use @cCompany if exists (select gid from sysusers where name = 'DYNGRP') begin exec sp_dropgroup DYNGRP end /* ** Recreate the DYNGRP group in all company databases. */ use @cCompany if not exists (select name from sysusers where name = 'DYNGRP') begin exec ("sp_addgroup DYNGRP") end end DEALLOCATE C_cursor ______________________________________ Thanks for any help you have.
View Replies !
Using Variable In LIKE Statement
Hi, I am trying to use a variable inside a LIKE statement, but it is not working as expected. It will not give a error, but it shows no results while it does show results if I replace the variable with the normal string within the LIKE statement. Here is my code: Code: -- this example returns results SELECT whatever FROM mytable WHERE whatever LIKE 'blah%'; Code: -- this example returns no results DECLARE @test VARCHAR; SET @test='blah%'; SELECT whatever FROM mytable WHERE whatever LIKE @test; Any ideas why the version using the variable would not work? Patrick
View Replies !
SQL Statement With Variable.
Hi , I am testing a very simple query that use variable for sort direction and sort expression DECLARE @SortExp nvarchar(256), @SortDir nvarchar(10) Set @SortExp = 'curTime' Set @SortDir = 'DESC' Select * from table where recID < 20 order by @SortExp @SortDir and i got this error... The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name. Is there anyway to do this task. Thanks Ddee
View Replies !
SQL Statement In Variable
Hello Everyone, I wanted to pass a SQL statement thru a variable, and use that variable in my source component. SELECT CLINIC_SUK, CLINIC_CODE, CLINIC_DESC, CLINIC_ARABIC, Load_DT FROM DIM_CLINIC where load_dt > ? I had created a variable with my SQL statement and mapped that variable in my source component. Its giving me some error. Parameter Information cannot be derived from SQL statement. Set parameter information before preparing command. Please do inform me about the solution for having a parameter in my source SQL Statement.
View Replies !
My Variable In Sql Statement
Declare @MyCode nvarchar(20); Set @MyCode='ABC' set @int_rowcount=(SELECT count(hoten) FROM @MyCode) I run it but still errors ! How can i implement above statement ? Thank you very much !
View Replies !
Passing Variable To Sql Statement
could anyone please help me to resolve this issue? here's my sql query which retrieve last 3 month data t.execute(SELECT * tbl1 where nmonth >= datepart(mm,DATEADD(month, -3, getdate())) or nmonth <=datepart(mm,getdate()) and empno='"+emppip+"'") now instead of passing 3 in this query(datepart(mm,DATEADD(month, -3, getdate())) ) i need to pass a variable to retrieve data based on user requirements. i tried this way, dim mno as n mno=4 t.execute(SELECT * tbl1 where nmonth >= datepart(mm,DATEADD(month, -'"+mno+"', getdate())) or nmonth <=datepart(mm,getdate()) and empno='"+emppip+"'") its not working. can i achieve this using stored procedure? or can i directly pass a variable to sql synatax? thanks for any help
View Replies !
Can Variable Be Used In SQL UPDATE Statement In VB.NET
Hy, i have this problem in vb.net: I must use a variable in SQL UPDATE statement, after SET statement, and i'm getting error. This is that line of code: Dim variable_name As StringDim variable As Integer Dim sqlString As String = ("UPDATE table_name SET " variable_name " = " & variable & " WHERE UserID = '" & UserID & "'")Dim cmdSqlCommand As New SqlCommand(sqlString, conConnetion) cmdSqlCommand.ExecuteNonQuery() When I don't use a variable after SET statement, everything work fine. This code works fine: Dim variable As Integer Dim sqlString As String = ("UPDATE table_name SET column_name = " & variable & " WHERE UserID = '" & UserID & "'")Dim cmdSqlCommand As New SqlCommand(sqlString, conConnetion) cmdSqlCommand.ExecuteNonQuery() Please, if someone can help me in this...thanks..
View Replies !
Use A Table Name As A Variable In The From Statement
I'm curious if anyone knows the correct way to do this pseudo-statement correctly? I want to create a stored procedure in which I send it the table name of the table I want to query.declare @tableName varchar(500)set @tableName = 'PortfolioPreferenceOwnership' select * from @tableName
View Replies !
Variable In A Select Statement
Is there anyway to use a variable to define a column in a select statement. I can put the variable in but I'm sure it will be read as a literal instead of the column. select @column_name from table
View Replies !
Variable Db Name In SP Select Statement
I need a general stored procedure so the database name will be an input parameter. I want to do a Select statemant such as: Select name from @DBName . . sysobjects where xtype - 'u' rather than a hard coded -- Select name from pubs..sysobjects where xtype = 'u' but I can't find the right way of using @DBName
View Replies !
Variable Db Name In SP Select Statement
I need a general stored procedure so the database name will be an input parameter, for example-- @DBName varchar(30) I want to do a Select statemant such as: Select name from @DBName . . sysobjects where xtype - 'u' rather than a hard coded -- Select name from pubs..sysobjects where xtype = 'u' but I can't find the right way of using @DBName Thanks, Judith
View Replies !
Want To Use A Variable In A 'use DB' Statement In SQL Script
Hi, I want to use a variable in a 'use' statement... but, I cannot figure out the syntax, nor do I know if it is possible... Here is an example SQL script: /*-----------------------------------------------------*/ DECLARE @DataBase varchar(60) --Declare cursor for all DBs except master, MSDB, Model, tempdb DECLARE curdb CURSOR for select name from master..sysdatabases where name not in ('master', 'MSDB', 'Model','tempdb')for read only --Open and perform initial fetch open curdb fetch curdb into @DataBase --While there are databases to process, process each DB While @@fetch_status = 0 PRINT @DataBase use + ' ' + @database --or, use @database fetch curdb into @DataBase end /*------------------------------*/ Thanks, Michael
View Replies !
USE @dbname (Use Statement With Variable)
Hi I need to run a stored procedure on each database in my SQL server. I want to have a loop to go through each db. Is there a way I can run 'Use @dbname', I tried Execute and sp_executesql but it didn't work. I want to execute the SP withing each db. Thanks
View Replies !
Variable In 'kill' Statement
I'm trying to kill all processes for a specific login name. My code is as follows: declare @spid int select @spid=(select spid from master.dbo.sysprocesses where loginame = 'name of user') kill @spid What I get is: Server: Msg 170, level 15, State 1, Line 3 Line 3: Incorrect syntax near '@spid'. Where have I gone wrong? Thanks.
View Replies !
Using A Variable In An Update Statement
I am having difficulties with some sql syntax with sql server 2000. I am trying to write code to update a column in which the name of it is unknown. At run time, I am able to set a variable equal to the correct column name but in doing so, treats the value as a String. Ex. Declare @varA varchar(12) select @varA = (select top 1 Value from #temp) Update TableX set @varA = y.ColTest from TableX x, TableY y where x.Colid = y.Colid It sets the variable = to the last value from TableX.ColTest I want the Update statement to update the value for the Variable which represents the correct column to update. Any ideas? Thanks, Daniel
View Replies !
Passing A Variable To A SQL Statement
I've been coding a few years and SSIS makes me feel more stupid than any program I've ever used. I've read BOL and bought a book. Can't say either one has really helped. I'm still a complete idiot after one week of working with it. I apologize for asking so many stupid questions. What I'm trying to do now is parameterize a SQL statement. I have a variable that's a string. I have a DataFlowComponent as a data source. I find references all over the Internet and this forum to something called an "ExecuteSQLTask" but I sure can't figure out what that is. In my toolbox I have data flow sources for DataReader, Excel, Flat Files, Old DB, Raw file, and XML but no Execute Sql Task. Anyway SELECT * FROM TABLE WHERE COLUMN='Value' in the SQL Command property is simple enough. Now I want 'Value' to be a variable. You know, like in T/SQL DECLARE @Foo VarChar(25). Creating the variable is easy as pie. I have found at least 10 different examples of specifying variables on the web, all of which claim to be SSIS examples, Is it User::Variable? @[User::Variable]? @Variable? I want to read rows from a table, with a a variable value in the WHERE clause, and pass them to the fuzzy lookup task. Is my approach fundamentally flawed in some way?
View Replies !
How To Set A Variable In An If Exists Statement
Hello, I would like to set a variable within my if exists statement, however SQL is throwing and error stating: Incorrect syntax near '='. If I remove the if exists, the query runs fine. Is there a reason why this is not working the way I have it and what suggestions can I use to accomplish what I am trying to do, which is store the ID into the permissionID variable Here is my code block: Code Snippet declare @permissionID int; if exists(select @permissionID = Id from Permission where [Description] = 'SettlementReport') Thanks, Flea#
View Replies !
Using A Variable In A GROUP BY Statement
Hi Experts, I would like to make a stored procedure in my db: I have sql express 2005. I get Error 164 when creating this procedure: CREATE PROCEDURE CrossTable @Variable1 smallint, @Variable2 smallint, @Value smallint AS BEGIN SELECT @Variable1, COUNT(@Variable1) AS 'Haufigkeiten' FROM SurveyData WHERE @Variable2 = @Value GROUP BY @Variable1 END GO I would like to generate a frequency chart per userdefined-variable (@variable) with a where restriction. The GROUP BY @Variable1 seems to be problem: is there some workaround in order to use variables in a GROUP BY clause? or how can I write an sql statement which do the same as this procedure (CrossTable) without using the GROUP BY clause? Thanks a lot for your replies Greets from Switzerland Chris
View Replies !
Variable In DDL Statement In Procedure
Create table tbl(title nvarchar(40)) Create procedure df_bppr @de nvarchar(30) As Begin Declare @sstr nvarchar(500) Set @sstr = N'Alter Table tbl Add Constraint df_title Default '+ @de + ' For title' Exec sp_executesql @sstr, @de End Execute df_bppr @de = 'NoTitle' ****************** Msg 102, Level 15, State 1, Line 1 Incorrect syntax near 'NoTitle'. Msg 128, Level 15, State 1, Line 1 The name "NoTitle" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted. -------------------- I created the procedure without any error. But when i execute the procedure it shows the above error I want to create a proc which dynamically change the default value for more than one column with same default value. Vijai
View Replies !
SQL Select Statement With A 'string' Variable?
I'm trying to add a 'change password' control to my site and seem to be having some issues. I have code that works if I statically define what user is displayed on the form, but I cant get it to detect the 'authenticated' user and show them the reset for for that ID.If I take the "+ myid" out of the select statement and just define the username statically the form works properly. Error:System.Data.SqlClient.SqlException: The column prefix 'System.Security.Principal' does not match with a table name or alias name used in the query. Here's a piece of the code that is supposed to detect the current logged in user. However, it gives the error. (some of the code may be redundant but its not causing issues that I can tell) public void InitPage() { IPrincipal p = HttpContext.Current.User; String myid = HttpContext.Current.User.ToString(); SqlServer sqlServer = new SqlServer(Util.SqlConnectionString()); DataTable dt; SqlConnection cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["myconnection"].ConnectionString); SqlDataAdapter cmd1 = new SqlDataAdapter("select * from USER WHERE USER_NAME = "+ myid, cnn); DataTable UIDtable = new DataTable(); cmd1.Fill(UIDtable); User_Id.Value = UIDtable.Rows[0]["ID"].ToString(); dt = sqlServer.USER_SELECT(Util.SiteURL(Request.QueryString["Pg"].ToString()), User_Id.Value);
View Replies !
Using A Variable To Specify Table Column In Sql Statement??
Hello everyone, I'm still quite new ASP.net, Visual Web Developer 2008, and SQL. It has been a fun learning experience so far. Anyways, the site I am designing needs to allow its users to extensively search several different databases (MS SQL databases). I have followed many of the tutorials and have found it rather easy to add table adapters, gridviews and other data features that use basic SQL Select statements. One of the major database tables contains several columns which I would like to include as a search parameters from a drop down list. I was wondering if there is any way I can write a select which will pass a variable to be used as a column name to the statement? For example: SELECT DATE, GAME, EXACT, @COLNAMEFROM HistoryWHERE @COLNAME = @SOMEVARIABLE This obviously doesnt work, but thats the gist of what I want to do. Any suggestions? I need this to be simple as possible, Most everything I'm doing is done through the visual design mode. Im still slow to learn C# and apply it in the codebehind files unless I have very detailed step by step instuctions. Thanks Scott
View Replies !
Calling Variable Inside T-SQL Statement
Can someone please take a quick look at this and tell me what I'm doing wrong I'm sure it's something simple. I'm a little new to stored procedures but I've been using SQL and T-SQL for quite some time, I've just always used inline queries with my ASP. This procedure needs to be run monthly by me or another person I grant access to and will update sales information that our sales staff will be paid commission on. I need to supply the start date and and end date for the query and it will pull this information from our business system which is hosted remotely by a third party and pull it into our local SQL server where we can run commission reports against it. (I hope this is enough information you can understand where I'm trying to go with this). I know my problem right now lies in how I'm trying to call the variable inside of my T-SQL. Any help is appreciated. This is an old Unix system and it stores the date as YYYYMMDD as numeric values incase someone wonders why I have dimed my dates as numeric instead of as datetime =) I'm using a relativity client to create an ODBC connection to the UNIX server and then using a linked server to map a connection in SQL this is the reason for the OpenQuery(<CompanyName> SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: XXXXXXXXXXXXX -- Create date: 10/4/2007 -- Description: This proc is designed to pull all CSA -- part sales from XXXXXX business system and upload them -- into the local XXXXXXXX Database for commission reporting -- =============================================CREATE proc usp_CSAPartsSalesUpdate@date1 int, @date2 int As INSERT INTO CSAPartsSales ( CSA, CustomerNumber, CustomerName, Location, InvoiceNumber, InvoiceDate, InvoiceAmount ) SELECT SalesRoute, HInvCust, CustOrCompName, HInvLoc, HInvNo, HInvDate, HInvAmt From OpenQuery(<CompanyName>, 'Select CPBASC_All.SalesRoute, PMINVHST.HInvCust, CPBASC_All.CustOrCompName, PMINVHST.HInvLoc, PMINVHST.HInvNo, PMINVHST.HInvDate, PMINVHST.HInvAmtFROM PMINVHST INNER JOIN CPBASC_All ON PMINVHST.HInvCust = CPBASC_All.CustomerNo WHERE (((PMINVHST.HInvAmt)<>0) AND ((PMINVHST.HInvDate)>=''' + @date1 + ''' And (PMINVHST.HInvDate)<=''' + @date2 + ''') AND ((Trim([CPBASC_All].[SalesRoute]))<>'''' And (Trim([CPBASC_All].[SalesRoute]))<>''000''))') In this example date1 will be equal to 20070901 and date2 will be equal to 20070930 so I can pull all CSA sales for the month of September. This is the error message I get when I try to create the proc: Msg 102, Level 15, State 1, Procedure usp_CSAPartsSalesUpdate, Line 17 Incorrect syntax near '+'. ~~~ Thanks All~~~
View Replies !
Using A Variable For Tablename In Select Statement?
I have a stored procedure that accepts the table name as a parameter. Is there anyway I can use this variable in my select statement after the 'from' clause. ie "select count(*) from @Table_Name"? When I try that is says "Must declare the table variable @Table_Name". Thanks!
View Replies !
Filtering SQL Statement By Server Variable
Hi Hope somebody can help me with a problem that i am having. I have the following piece of code <% Dim sqlConn As SqlConnection Dim sqlCmd As SqlCommand Dim sqlReader As SqlDataReader sqlConn = New SqlConnection("server=myserver;database=aspnet;uid=username;pwd=password") sqlCmd = New SqlCommand("SELECT SamAccount, FirstName, Surname, FirstName + N' ' + Surname+' Logged In ' AS UserLoggedIn FROM tblUsers WHERE (SamAccount = " + Request.ServerVariables("logon_user") + ")", sqlConn) sqlConn.Open() sqlReader = sqlCmd.ExecuteReader() %> <% Dim strPCNAme As String sqlReader.Read() strPCNAme = sqlReader("UserLoggedIn") Response.Write(strPCNAme) %> The bit i have i have underlined and made bold throws up the error. Basically what i am trying to do is to filter the the SQL statement by the server variable logon user. Everthing works fine until the point that i put the where clase in. Can anybody help please? Thanks Marcus
View Replies !
Variable For The Table Name In A SELECT Statement.
Hi,I'm trying to dynamically assign the table name for a SELECT statement but can't get it to work. Given below is my code: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE GetLastProjectNumber (@DeptCode varchar(20)) AS BEGIN TRANSACTION SET NOCOUNT ON DECLARE @ProjectNumber int SET @ProjectNumber = 'ProjectNumber' + REPLACE(CONVERT(char,@DeptCode),'.','') SELECT MAX(@ProjectNumber) FROM 'tbl_ProjectNumber' + REPLACE(CONVERT(char,@DeptCode),'.',''); END TRANSACTION Basically, I have a bunch of tables which were created dynamically using the code from this post and now I need to access the last row in a table that matches the supplied DeptCode. This is the error I get:Msg 102, Level 15, State 1, Procedure GetLastProjectNumber, Line 29Incorrect syntax near 'tbl_ProjectNumber'. Any help would be appreciated.Thanks.
View Replies !
Correct SQL Statement For URL Variable Display?
I have a page that is displaying different movie covers. They each have their respective genres stored in several keys (genre0, genre1, genre2). Now I can search within the movies by changing the url variable movies.aspx?genre=action but if I simply go to movies.aspx, I'm wanting all genres to appear. But for me, the page is blank if I don't specify the genre. So I've had to start filling in the genre All for all movies so I can go to movies.aspx?genre=all but I know this can't be necessary. Is it my SQL statement for the datasource causing the problem? Code is in VB. <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="Data Source=xxx;Initial Catalog=xxx;User ID=xxx;Password=xxx" ProviderName="System.Data.SqlClient" SelectCommand="SELECT * FROM [Movies] WHERE [genre0] = @strGenre OR [genre1] = @strGenre OR [genre2] = @strGenre"> <SelectParameters> <asp:QueryStringParameter Name="strGenre" QueryStringField="genre" /> </SelectParameters> </asp:SqlDataSource> <asp:DataList ID="DataList1" runat="server" DataKeyField="movieID" DataSourceID="SqlDataSource1" RepeatColumns="5" RepeatDirection="Horizontal" CellPadding="5" BorderStyle="None" BorderWidth="0px"> <ItemTemplate> <asp:HyperLink ID="HyperLink1" runat="server" NavigateUrl='<%# "movie.aspx?id=" & Eval("movieID") %>'> <asp:Image ID="Image1" runat="server" ImageUrl='<%# Eval("imageURL", "{0}") %>' BorderStyle="None" /><br /> </asp:HyperLink> </ItemTemplate> </asp:DataList>
View Replies !
Incorrect Sql Statement - Not Reading Variable
In the funtcion below I am selecting a value from the page - Request.QueryString("ProjectID") and this is being pulled through correctly - if I debug then ProjectID = Request.QueryString ("ProjectID") does equal the correct value. However this value is not then working in the following line: strSQL = "SELECT [ProjectID] FROM [Projects] WHERE [ProjectID] = [ProjectID]" This statement is not reading any value for ProjectID and so is selecting all from the table..... How do I write this statement to pick up the variable above????? Thanks in advance for your help! Clare Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load Dim ds As New Data.DataSet Dim da As Data.SqlClient.SqlDataAdapter Dim strSQL As String Dim ProjectID As Int32 ProjectID = Request.QueryString("ProjectID") strSQL = "SELECT [ProjectID] FROM [Projects] WHERE [ProjectID] = [ProjectID]" Dim connString As String = "Data Source=xxx;Initial Catalog=xxx;User ID=xxx;Password=xxx" da = New Data.SqlClient.SqlDataAdapter(strSQL, connString) da.Fill(ds) ds.Tables(0).Columns.Add("imgFile") For Each tempRow As Data.DataRow In ds.Tables(0).Rows tempRow.Item("imgFile") = ("imgGrab2.aspx?id=" & tempRow.Item("ProjectID")) Next ImgGrid3.DataSource = ds ImgGrid3.DataBind() End Sub
View Replies !
Variable Not Holding Value For Select Statement
this querry below works perfect when i assign the us.UserID = 29 but i need to be able to use the @UsersMaxID variable..... when i debug all of my values are right where they need to be... even this on ((( @UsersMaxID ))) but for some reason it will not work with the next select statement... can someone make the pain go away and help me here..?? erik.. GOSET ANSI_NULLS ON GO ALTER PROCEDURE AA ASDECLARE @GenericColumn Varchar (200) DECLARE @GenericValue Varchar (200) SET @GenericColumn = 'FirstName'SET @GenericValue = 'Erik' DECLARE @SQL NVARCHAR(4000) DECLARE @UserID INT DECLARE @UsersMaxID INT DECLARE @MaxID INT declare @tempResult varchar (1000) -------------------------------------------Define the #Temporary Table----------------------------------------------CREATE TABLE #UsersTempTable ( ID int IDENTITY PRIMARY KEY, UserID [int], FirstName [varchar](30), LastName [varchar](30), CompanyName [varchar](200), Address1 [varchar](75), Address2 [varchar](75), City [varchar](75),ActiveInd [int], Zip [varchar](10), WkPhone [varchar](12),HmPhone [varchar](12), Fax [varchar](12), Email [varchar](200), Website [varchar](200), UserType [varchar](20),Title [varchar](100),Note [text], StateCD [char](2), CountryCD [char](2), CompanyPhoto [varchar](50), CompanyDescr [varchar](2000)) ---------------------------------------Fill the temp table with the Customers data-----------------------------------SET @SQL = 'INSERT INTO #UsersTempTable (UserID, FirstName, LastName, CompanyName, Address1, Address2, City, ActiveInd, Zip, WkPhone, HmPhone,Fax, Email, Website, UserType, Title, Note, StateCD, CountryCD, CompanyPhoto, CompanyDescr) Select Users.UserID, Users.FirstName,Users.LastName, Users.CompanyName, Users.Address1, Users.Address2, Users.City, Users.ActiveInd, Users.Zip, Users.WkPhone, Users.HmPhone,Users.Fax,Users.Email,Users.Website, Users.UserType,Users.Title, Users.Note,Users.StateCD, Users.CountryCD,Users.CompanyPhoto,Users.CompanyDescr FROM USERS WHERE ' + @GenericColumn +' = ''' + @GenericValue + '''' EXEC sp_executesql @SQL SET @MaxID = (SELECT MAX(ID) FROM #UsersTempTable)SET @UsersMaxID = (SELECT UserID From #UsersTempTable WHERE ID = @MaxID) SELECT SpecialtyName FROM Specialty s INNER JOIN UserSpecialty us ON s.SpecialtyCD = us.SpecialtyCD WHERE us.UserID = 29 SELECT * FROM #UsersTempTable ==========================================================================================SET @UsersMaxID = (SELECT UserID From #UsersTempTable WHERE ID = @MaxID) SELECT SpecialtyName FROM Specialty s INNER JOIN UserSpecialty us ON s.SpecialtyCD = us.SpecialtyCD WHERE us.UserID = 29 <<<<<<<<<<<<<<<<< i need @UserMaxID ........RIGHT HERE
View Replies !
Table Name In Variable - Create Statement
Is it possible to have part of a table name used in a CREATE statementcontained in a variable? Here's what I'd like to do, althoughobviously the syntax of this isn't quite right or I wouldn't be hereasking:DECLARE @TblPrefix char (3)SET @TblPrefix = 'tst'CREATE TABLE @TblPrefix + TestTable (col1 int)The point there is to have a table named tstTestTableThe reason I need to do this is that my ISP will only give me onedatabase to work with and I'd like to have two copies of theapplication I'm developing running at the same time. So I'd like torun the sql script that creates the tables with TblPrefix set to "dev"and then run it again with TblPrefix set to "liv"thankseric
View Replies !
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 !
Help On Global Variable For Insert Statement
I am new to DTS, but really enjoy it and was wondering if someone could help me with the following small vb app. I am using the following DTS insert statement to insert records into my table. I have multiple textboxes that needs to be filled and then inserted, none of them exept Nulls. How can I modify my code to insert those textboxes as well as run through the boxes and then check if they have nulls and NOT insert the ones that has nulls? My form has 9 textboxes. Textbox1, 2, 3 Needs to insert values into the first column. Textbox4, 5, 6 into the second and then Texrbox7, 8, 9 into the last column. My question is how do I format the following line of code to do what I need? oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "Values ('1rowst', '2rowst', '3rowst')" I think it is something like this, but I am Really not sure and some help would be greatly appretiated: oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "Values ('Textbox1', 'Textbox4', 'Textbox7')" I am really not sure. Here is all the code: Public Sub Task_Sub1(ByVal goPackage As Object) Dim oTask As DTS.Task 'Dim oLookup As DTS.Lookup Dim oCustomTask1 As DTS.ExecuteSQLTask2 oTask = CType(goPackage, DTS.Package).Tasks.New("DTSExecuteSQLTask") oTask.Name = "DTSTask_DTSExecuteSQLTask_1" oCustomTask1 = oTask.CustomTask oCustomTask1.Name = "DTSTask_DTSExecuteSQLTask_1" oCustomTask1.Description = "Execute SQL Task: undefined" oCustomTask1.SQLStatement = "Insert into TestTable (Test1, Test2, Test3) " & vbCrLf oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "Values ('1rowst', '2rowst', '3rowst')" oCustomTask1.ConnectionID = 1 oCustomTask1.CommandTimeout = 0 oCustomTask1.OutputAsRecordset = False goPackage.Tasks.Add(oTask) oCustomTask1 = Nothing oTask = Nothing End Sub
View Replies !
Put Select Statement In SSIS Variable
Is it possible to add a variable in SSIS like name of variable: myVar Scope: Data Flow Task Data Type: String Value:SELECT hello FROM blah WHERE (azerty = @[User::pda]) AND (qwerty = @[User::phone]) @[User::pda] and @[User::phone] are also variables in SSIS just like the myVar I made I know I'm doing something wrong with the data type because it's stores the whole select statement as a string Help Worf
View Replies !
|
TRACKER
