Using Passed Parameters In A Select Statement
hello all, im trying to run a select statement using a parameter, but am having extreme difficulties. I have tried this about 50 different ways but i will only post the most recent cause i think that im the closest now than ever before ! i would love any help i can get !!!
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
Dim pageID As StringpageID = Request.QueryString("ID")
TextBox13.Text = pageID 'Test to make sure the value was stored
SqlDataSource1.SelectParameters.Add("@pageID", pageID)
End Sub
....
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ProviderName=System.Data.SqlClient ConnectionString="Data Source=.SQLEXPRESS;Initial Catalog=software;Integrated Security=True;User=something;Password=something" 'SelectCommand="SELECT * FROM Table1 WHERE [ClientID]='@pageID' ></asp:SqlDataSource>
The error that i am getting, regardless of what i put inside the ' ' is as follows:
"Conversion failed when converting the varchar value '@pageID' to data type int."
anyone have any suggestions ?
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
Insert From Parameters And Select Statement
Trying to insert into a history table. Some columns will come fromparameters sent to the store procedure. Other columns will be filledwith a separate select statement. I've tried storing the select returnin a cursor, tried setting the values for each field with a separateselect. Think I've just got the syntax wrong. Here's one of myattempts:use ESBAOffsetsgoif exists(select * from sysobjects where name='InsertOffsetHistory' andtype='P')drop procedure InsertOffsetHistorygocreate procedure dbo.InsertOffsetHistory@RECIDint,@LOB int,@PRODUCT int,@ITEM_DESC varchar(100),@AWARD_DATE datetime,@CONTRACT_VALUE float,@PROG_CONT_STATUS int,@CONTRACT_NUMBER varchar(25),@WA_OD varchar(9),@CURR_OFFSET_OBL float,@DIRECT_OBL float,@INDIRECT_OBL float,@APPROVED_DIRECT float,@APPROVED_INDIRECT float,@CREDITS_INPROC_DIRECT float,@CURR_INPROC_INDIRECT float,@OBLIGATION_REMARKS varchar(5000),@TRANSACTION_DATE datetime,@AUTH_USERvarchar(150),@AUTHUSER_LNAMEvarchar(150)asdeclare@idintinsert into ESBAOffsets..HISTORY(RECID,COID,SITEID,LOB,COUNTRY,PRODUCT,ITEM_DESC,AWARD_DATE,CONTRACT_VALUE,PROG_CONT_STATUS,CONTRACT_TYPE,FUNDING_TYPE,CONTRACT_NUMBER,WA_OD,PM,AGREEMENT_NUMBER,CURR_OFFSET_OBL,DIRECT_OBL,INDIRECT_OBL,APPROVED_DIRECT,APPROVED_INDIRECT,CREDITS_INPROC_DIRECT,CURR_INPROC_INDIRECT,PERF_PERIOD,REQ_COMP_DATE,PERF_MILESTONE,TYPE_PENALTY,PERF_GUARANTEE,PENALTY_RATE,STARTING_PENALTY,PENALTY_EXCEPTION,CORP_GUARANTEE,BANK,RISK,REMARKS,OBLIGATION_REMARKS,MILESTONE_REMARKS,NONSTANDARD_REMARKS,TRANSACTION_DATE,STATUS,AUTH_USER,PMLNAME,EXLD_PROJ,COMPLDATE,AUTHUSER_LNAME)values(@RECID,(Select COID from ESBAOffsets..Offsets_Master where RECID = @RECID),(Select SITEID from ESBAOffsets..Offsets_Master where RECID = @RECID),@LOB,(Select COUNTRY from ESBAOffsets..Offsets_Master where RECID =@RECID),@PRODUCT,@ITEM_DESC,@AWARD_DATE,@CONTRACT_VALUE,@PROG_CONT_STATUS,(Select CONTRACT_TYPE from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select FUNDING_TYPE from ESBAOffsets..Offsets_Master where RECID =@RECID),@CONTRACT_NUMBER,@WA_OD,(Select PM from ESBAOffsets..Offsets_Master where RECID = @RECID),(Select AGREEMENT_NUMBER from ESBAOffsets..Offsets_Master where RECID= @RECID),@CURR_OFFSET_OBL,@DIRECT_OBL,@INDIRECT_OBL,@APPROVED_DIRECT,@APPROVED_INDIRECT,@CREDITS_INPROC_DIRECT,@CURR_INPROC_INDIRECT,(Select PERF_PERIOD from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select REQ_COMP_DATE from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select PERF_MILESTONE from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select TYPE_PENALTY from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select PERF_GUARANTEE from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select PENALTY_RATE from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select STARTING_PENALTY from ESBAOffsets..Offsets_Master where RECID= @RECID),(Select PENALTY_EXCEPTION from ESBAOffsets..Offsets_Master where RECID= @RECID),(Select CORP_GUARANTEE from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select BANK from ESBAOffsets..Offsets_Master where RECID = @RECID),(Select RISK from ESBAOffsets..Offsets_Master where RECID = @RECID),(Select REMARKS from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select OBLIGATION_REMARKS from ESBAOffsets..Offsets_Master whereRECID = @RECID),@MILESTONE_REMARKS,@NONSTANDARD_REMARKS,@TRANSACTION_DATE,(Select STATUS from ESBAOffsets..Offsets_Master where RECID = @RECID),@AUTH_USER,(Select PMLNAME from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select EXLD_PROJ from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select COMPLDATE from ESBAOffsets..Offsets_Master where RECID =@RECID),@AUTHUSER_LNAME)select@@identity idgogrant execute on InsertOffsetHistory to publicgo
View Replies !
View Related
Controlling Fields In A Select Statement By Use Of Parameters
Hi to allI wish to be able to have a standard select statement which hasadditional fields added to it at run-time based on suppliedparameter(s).iedeclare @theTest1 nvarchar(10)set @theTest1='TRUE'declare @theTest2 nvarchar(10)set @theTest2='TRUE'selectp_full_nameif @theTest1='TRUE'BEGINother field1,ENDif @theTest2='TRUE'BEGINother field2ENDfrom dbo.tbl_GIS_personwhere record_id < 20I do not wish to use an IF statement to test the parameter for acondition and then repeat the entire select statement particularly asit is a UNIONed query for three different statementiedeclare @theTest1 nvarchar(10)set @theTest1='TRUE'declare @theTest2 nvarchar(10)set @theTest2='TRUE'if @theTest1='TRUE' AND @theTest2='TRUE'BEGINselectp_full_name,other field1,other field2from dbo.tbl_GIS_personwhere record_id < 20ENDif @theTest1='TRUE' AND @theTest2='FALSE'BEGINselectp_full_name,other field1from dbo.tbl_GIS_personwhere record_id < 20END......if @theTest<>'TRUE'BEGINselectp_full_namefrom dbo.tbl_GIS_personwhere record_id < 20ENDMake sense? So the select is standard in the most part but with smallvariations depending on the user's choice. I want to avoid risk ofbreakage by having only one spot that the FROM, JOIN and WHEREstatements need to be defined.The query will end up being used in an XML template query.Any help would be much appreciatedRegardsGIS Analyst
View Replies !
View Related
BETWEEN Predicate With Passed Parameters
I have a report in SQL that passes parameters at runtime entered by the user for two date ranges (beginning and ending). I'm trying to write a formula that will print a specific field *only if* the specified date range entered by the user is BETWEEN a specific value (like 200401). This is kind of reverse of a normal WHERE, BETWEEN clause. I tried a standard BETWEEN predicate in my WHERE clause like: IF '200401' BETWEEN ?BegPer and ?EndPer then salesanal.ptdbud01 else 0 But, it's returning an error that my Then statement is missing. I can't use a normal statement like 'IF ?BegPer >= '200401' and ?EndPer <= '200401', then.....' because users could enter a RANGE of periods, so it would be difficult to code all of the possible combinations this way. I'm actually doing this in Crystal, but if someone can give me a standard MSSQL example, I can translate that over to Crystal. Thanks in advance, Michelle
View Replies !
View Related
Can A Column Be Derived Using Substring But The Parameters Are A Result Of A Select Statement?
I have a table which has a field called Org. This field can be segmented from one to five segments based on a user defined delimiter and user defined segment length. Another table contains one row of data with the user defined delimiter and the start and length of each segment. e.g. Table 1 Org aaa:aaa:aa aaa:aaa:ab aaa:aab:aa Table 2 delim Seg1Start Seg1Len Seg2Start Seg2Len Seg3Start Seg3Len : 1 3 5 3 9 2 My objective is to use SSIS and derive three columns from the one column in Table 1 based on the positions defined in Table 2. Table 2 is a single row table. I thought perhaps I could use the substring function and nest the select statement in place of the parameters in the derived column data flow. I don't seem to be able to get this to work. Any ideas? Can this be done in SSIS? I'd really appreciate any insight that anyone might have. Regards, Bill
View Replies !
View Related
SQL Queries That Have Parameters Passed By User
I have an sql query that has specific criteria (like state='PA' orstate = 'NJ'...) and would like to be able to have the user specifythe criteria dynamically either through the web or from MSAccess oranother tool.The query also does a GROUP BY the state and other variables that arepart of the criteria.I know how to get MSAccess and asp pages to do the sorting andselecting against an SQL tbl or view, but when access queries the sameinfo as the original sql view, the process takes much longer than whenthe sql view does all of the sorting, selecting and grouping..The table we are currently using is 5 million records and will begrowing to 250 million records shortly, so speed is of the essence.The sql views and MSAccess are both running from the same server sothere is no issue at this point of a network impacting the MSAccessquery.Any suggestions...
View Replies !
View Related
Problem With Dates Format Passed As Parameters...
Hi, I am having the "classical problem" of the forums; A local date changed to the american format (normally in the development environment) which I call "Switching months with days"... well it switches both again if you press "view report" again :)The problem I'm having is that when I Navigate from one report to other, the dates get switched... Everything's been set in the locale uk date format (dd/mm/aaaa), in the operating system, in the database and in the reports... and the reports are working properly through the web interface.. the only thing that is not working properly is the Navigation which switches the date month with the day...Changing all the Reporting Services server, database server to the american format date is not an acceptable solution. Thanks for any guidance on this, as I'm pretty lost...Jose
View Replies !
View Related
Report Parameters: Child Param(s) Not Passed To Delivery Extension?!
I have written a custom delivery extension for our Reporting Server that is working quite well with no errors. It delivers the report to a public FTP server and writes a corresponding record to the database. It is happy. However, we are now trying to give some organizational structure to the reports on the FTP server. We want to organize the reports according to an identifer for the Entity which owns it: the report is generated by the EntityID parameter, but we obviously do not want to expose this internal ID to the outside world. Our hope is to use another identifier which we have specified as a "hidden" report parameter -- one that is populated when the report is generated, but hidden (not internal, although this is an option) from the user. After analyzing the objects available to the extension (Notification, Report and IDeliveryReportServerInformation), the only hint of the parameters used for the report are present in the Notification.Report.URL property -- but only the first EntityID parameter which drives the population of the child, "desired identifier" parameter. No other "child" parameters added to the report are referenced in the URL. So my question is this: how can I obtain access to the DesiredIdentifier parameter from my custom delivery extension? Is there another object I can gain access to? Can I get this parameter to be included in the URL which is available to the extension? And a final curiosity: why doesn't the Report object contain a collection of all Parameters used by the Report?
View Replies !
View Related
Do Stored Procedures Have A Limit On Number Of Parameters Or Byte Size Passed In?
Hi,I'm using c# with a tableadapter to call stored procedures. I'm running into a problem where if I have over a certain byte size or number of parameters being passed into my stored proc I get an exception that reads: "Cannot evaluate expression because a thread is stopped at a point where garbage collection is impossible, possibly because the code is optimized." If I remove one parameter, the problem goes away. Has anyone run into this before? Thanks,Mark
View Replies !
View Related
SQL Reporting Services Issue Using Multiple Parameters Where Data Is Passed In From A Stored Procedure
I have an issue with using multiple parameters in SQL Reporting services where data is passed in from a stored procedure When running the report in design mode - I can type in a parameter sting and it runs fine In the report preview screen I can select single parameters by ticking the drop down list and again it runs fine as soon as I tick more than one I get an error An error occurred during local report processing Query execution failed for data set €˜data' Must declare the scalar variable '@parameter' Some info... The dataset 'workshop' is using a sproc to return the data string? I get multiple values back fine in the sproc using this piece of code (select [str] from iter_charlist_to_table( @Parameter, DEFAULT) )) I have report parameters set to Multi-Value Looking through the online books it says... You can define a multivalued parameter for any report parameter that you create. However, if you want to pass multiple parameter values back to a query, the following requirements must be satisfied: The data source must be SQL Server, Oracle, or Analysis Services. The data source cannot be a stored procedure. Reporting Services does not support passing a multivalued parameter array to a stored procedure. The query must use an IN clause to specify the parameter. Am I trying to do the impossible ?
View Replies !
View Related
Multiple Select Values - How Are They Passed?
Greetings, all! I had a question about how SRS passes multiple values to a query. For example: Say I had a "select box" (Sorry, can't think of the proper term right now) with the following values: F1 F2 F3 F4 The user then selects F1 and F4. When SRS passes the selected values to the query or procedure, how are they passed? As a delimited value? Examples of what I mean: @Parameter = 'F1', 'F4' @Parameter = F1 [Some unknown delimiting symbol] F4 @Parameter = 'F1,F4' Does anyone know how this is done? Or is it done in some other odd way? Thanks!
View Replies !
View Related
Select X Record Based On Row Passed In URL
Hi all - this one has me stumped... PLEASE HELP!!! I have a back/forward navigation link that passes a URL.startrow number (lets call it n) - based on n - I only want to select the record that is the n'th record based on a sort order (gall_order) - (SQL SERVER). <cfquery name="gallHomePic1st" datasource="id" maxrows="1"> SELECT id FROM gall_home ORDER BY gall_order asc </cfquery> For e.g. - I want the 7th (14th - 21st etc) record based on gall_order asc. Thanks guys - this has me stumped!
View Replies !
View Related
Size Of Data-set Passed Back By A Select
When a SQL statement is executed against a SQL Server database is therea server-side setting which dictates the size of the fetch buffer? Weare having some blocking issues on a new server install which do notoccur on the old server until a much larger volume of data is selected.Any help appreciated.
View Replies !
View Related
Multiple Tables Used In Select Statement Makes My Update Statement Not Work?
I am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly. My problem is that the table I am pulling data from is mainly foreign keys. So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys. I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit. I run the "test query" and everything I need shows up as I want it. I then go back to the gridview and change the fields which are foreign keys to templates. When I edit the templates I bind the field that contains the string value of the given foreign key to the template. This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value. So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors. I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode. I make my changes and then select "update." When the page refreshes, and the gridview returns, the data is not updated and the original data is shown. I am sorry for so much typing, but I want to be as clear as possible with what I am doing. The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work. When I remove all of my JOIN's and go back to foreign keys and one table the update works again. Below is what I have for my SQL statements:------------------------------------------------------------------------------------------------------------------------------------- SELECT:SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) WHERE (People.PersonID = ?)UPDATE:UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ? ---------------------------------------------------------------------------------------------------------------------------------------The only fields I want to update are in [People]. My WHERE is based on a control that I use to select a person from a drop down list. If I run the test query for the update while setting up my data source the query will update the record in the database. It is when I try to make the update from the gridview that the data is not changed. If anything is not clear please let me know and I will clarify as much as I can. This is my first project using ASP and working with databases so I am completely learning as I go. I took some database courses in college but I have never interacted with them with a web based front end. Any help will be greatly appreciated.Thank you in advance for any time, help, and/or advice you can give.Brian
View Replies !
View Related
Using Conditional Statement In Stored Prcodure To Build Select Statement
hiI need to write a stored procedure that takes input parameters,andaccording to these parameters the retrieved fields in a selectstatement are chosen.what i need to know is how to make the fields of the select statementconditional,taking in consideration that it is more than one fieldaddedfor exampleSQLStmt="select"if param1 thenSQLStmt=SQLStmt+ field1end ifif param2 thenSQLStmt=SQLStmt+ field2end if
View Replies !
View Related
TSQL - Use ORDER BY Statement Without Insertin The Field Name Into The SELECT Statement
Hi guys, I have the query below (running okay): Code Block SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02' FROM myTables WHERE Conditions are true ORDER BY Field01 The results are just as I need: Field01 Field02 ------------- ---------------------- 192473 8461760 192474 22810 Because other reasons. I need to modify that query to: Code Block SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02' INTO AuxiliaryTable FROM myTables WHERE Conditions are true ORDER BY Field01 SELECT DISTINCT [Field02] FROM AuxTable The the results are: Field02 ---------------------- 22810 8461760 And what I need is (without showing any other field): Field02 ---------------------- 8461760 22810 Is there any good suggestion? Thanks in advance for any help, Aldo.
View Replies !
View Related
How To Write Select Statement Inside CASE Statement ?
Hello friends, I want to use select statement in a CASE inside procedure. can I do it? of yes then how can i do it ? following part of the procedure clears my requirement. SELECT E.EmployeeID, CASE E.EmployeeType WHEN 1 THEN select * from Tbl1 WHEN 2 THEN select * from Tbl2 WHEN 3 THEN select * from Tbl3 END FROM EMPLOYEE E can any one help me in this? please give me a sample query. Thanks and Regards, Kiran Suthar
View Replies !
View Related
Help With Delete Statement/converting This Select Statement.
I have 3 tables, with this relation: tblChats.WebsiteID = tblWebsite.ID tblWebsite.AccountID = tblAccount.ID I need to delete rows within tblChats where tblChats.StartTime - GETDATE() < 180 and where they are apart of @AccountID. I have this select statement that works fine, but I am having trouble converting it to a delete statement: SELECT * FROM tblChats c LEFT JOIN tblWebsites sites ON sites.ID = c.WebsiteID LEFT JOIN tblAccounts accounts on accounts.ID = sites.AccountID WHERE accounts.ID = 16 AND GETDATE() - c.StartTime > 180
View Replies !
View Related
Select Statement Problem - Group By Maybe Nested Select?
Hey guys i have a stock table and a stock type table and what i would like to do is say for every different piece of stock find out how many are available The two tables are like thisstockIDconsumableIDstockAvailableconsumableIDconsumableName So i want to,Select every consumableName in my table and then group all the stock by the consumable ID with some form of total where stockavailable = 1I should then end up with a table like thisEpson T001 - Available 6Epson T002 - Available 0Epson T003 - Available 4If anyone can help me i would be very appreciative. If you want excact table names etc then i can put that here but for now i thought i would ask how you would do it and then give it a go myself.ThanksMatt
View Replies !
View Related
Parameters With LIKE Statement
I have a sql that I want to execute with LIKE and parameters:I tried several options outlined that I found at http://aspnet101.com/aspnet101/tutorials.aspx?id=10%20but they all seem to return 0 records. When I try and execute my statement in Enterprise manager, it works fine.Code Snippet:...Dim sql as stringsql = "SELECT * FROM tblName WHERE First LIKE '%' + @fname + '%' AND Last LIKE '%' + @lname + '%'"Dim param(1) as sqlParametersqlParams(0) = New SqlParameter("@fname", SqlDbType.VarChar, 50)sqlParams(0).Value = Trim(fname.text)sqlParams(1) = New SqlParameter("@lname", SqlDbType.VarChar, 50)sqlParams(1).Value = Trim(lname.text)...Can anyone tell me if there is anything wrong with my code above?
View Replies !
View Related
WITH Statement And OLE DB Parameters
Hi, I have an issue with an OLE DB command and parameters. The statement I want to run starts with a WITH statement - when I take out the parameter it runs fine. When I take out the WITH statement and leave the parameter in it runs fine. When I run the statement in SSMS it runs fine. With both the WITH statement and the parameter in it gives the error 'Syntax error, permission violation, or other nonspecific error'. Has anyone seen this before?
View Replies !
View Related
Passing Parameters Using IN Statement
The SQL for a dataset in ASP.NET 2.0 is as follows.... SELECT DISTINCT StudentData.StudentDataKeyFROM Student2Roster INNER JOIN StudentData ON Student2Roster.StudentDataRecID = StudentData.StudentDataRecIDWHERE (Student2Roster.ClassRosterRecID IN (@ClassRosterRecIDs) ClassRosterRecIDs are giuds At design time i use 'b2cf594d-908b-4c0c-a67f-6364899a4d42', '2e0b3472-d3f0-4a54-94af-bfc0a99525d9' as the parameter and it works in the designer but not at runtime. At runtime I get the error Conversion failed when converting from a character string to uniqueidentifier. If I leave the quotes off and only use 1 value like b2cf594d-908b-4c0c-a67f-6364899a4d42 it works.How can I use multiple guids as an IN parameter like 'b2cf594d-908b-4c0c-a67f-6364899a4d42', '2e0b3472-d3f0-4a54-94af-bfc0a99525d9'? Thanks
View Replies !
View Related
Extract Parameters From SQL Statement
Hei,I'm currently trying to write a program in C# that will allow users toparametrize their queries.For instance, I have a query like this:SELECT * FROM Customers Where Region = @Region AND Gender > @GenderHow can I extract the Parameters names without using Stringmanipulation (which is not perfect since sql statements can alsocontain '@' in LIKE clauses for example)I tried this wayDim comm As New OleDbCommand(SqlStatement, myCon)comm.Prepare()MsgBox(comm.Parameters.Count.ToString())comm.Dispose()myCon.Close()but this always return me 0 (zero)Do you have any idea on how to solve this generic problem?RegarsPhilippe Graca
View Replies !
View Related
Parameters With A Union Statement
I have a report that is using a union statement to pull in data from two identical tables except that one is for current month, the other for archived data. What I want to do is prompt the user once for a date and use the value to select from the right table. Since a sales date can only exist in one of the tables, one union will work, the other not. But the report in prompting me for a parameter for each query....which is in Informix and the prompt is this: "?" Is there anyway to force both halves of the query to see this as one parameter so the user is only prompted once? Thanks
View Replies !
View Related
&&"(Select All)&&" In Multi-select Enabled Drop Down Parameters
There are several parameters on a report. One of the parameter is a multi-select enabled parameter and I suppressed the value "All" showing as one of the item in the drop down list, simply by filter out the [bha].[bha].CURRENTMEMBER.LEVEL.ORDINAL to 1, as "(Select All)" is pre-assigned to the drop list when multi-select is enabled and it is confusing to show "(Select All)" and "All" in the drop list. However I have another report which is linked to this report and the value which is required to pass to this report for this parameter is "All". Can I pass the "Select All" as a parameter from the other report? If so, how? Thanks.
View Replies !
View Related
Delete Statement With Parameters Not Working
When I debug my code I see the string going into the parameter correclty, but the the delete statement doesnt work and I'm not sure why. Does this look ok? // Set up SqlCommand, connection to db, sql statement, etc. SqlCommand DeleteCommand = new SqlCommand(); DeleteCommand.Connection = DBConnectionClass.myConnection; DeleteCommand.CommandType = CommandType.Text; // Store Primary Key photoID passed here from DeleteRows_Click // in a parameter for DeleteCommand SqlParameter DeletePrimaryKeyParam = new SqlParameter(); DeletePrimaryKeyParam.ParameterName = "@PhotoID"; DeletePrimaryKeyParam.Value = photoID.ToString(); // Insert new parameter into command object DeleteCommand.Parameters.Add(DeletePrimaryKeyParam); // Delete row, open connection, execute, close connection DeleteCommand.CommandText = "Delete From Photo_TBL where PhotoID IN (@PhotoID)"; Response.Write(DeleteCommand.CommandText); // DeleteCommand.Connection.Close(); DeleteCommand.Connection.Open(); DeleteCommand.ExecuteNonQuery(); DeleteCommand.Connection.Close();
View Replies !
View Related
Errors Using Multiple Parameters In A SQL Statement
In an OLE DB Source in an SSIS package, we are having difficulties using multiple parameters in a SQL statement. Using a single '?' works fine, but I've read that when you want to map more than 1 parameter you should use 'Parameter0, Parameter1, etc'. The problem is that when we use Parameter0 and Parameter1 and then try to map it, it says that the query contains no parameters. Can anyone help with the correct way to use multiple parameters in a SQL query that's part of an OLE DB Source task? Thanks, Mike
View Replies !
View Related
ADODB 2.8 - SQL Insert Statement - Using Parameters
Hi there, I am trying to use the ADO technology within MS Access 2000. Basically I'd like to use parameters in a command object to insert a new record and get its newly inserted ID. But instead of it it returns error: Run-time error '-2147217900 (80040e14) Must declare the scalar variable "@ii_file" Isn't this varaible (and all the rest of variables) declared by setting a parameter ".Parameters.Append .CreateParameter("@ii_file", adVarChar, adParamInput, 255, Me.cbo_ii) "? I'd like to avoid using stored procedures in order to create the whole SQL statement from the client side. Thanks! Darek Public Sub save_import() Dim rs As ADODB.Recordset, cmd As ADODB.Command, rec_affected As Long Set cmd = New ADODB.Command With cmd .ActiveConnection = ado_conn 'an existing connection .CommandType = adCmdText .CommandText = "insert into import_main (ii_file, id_file, oi_file, od_file, folder, import_desc, id_client,basis_of_study, id_is, project_leader, urisk_model_basis, as_at_date, extent_benchamark) " & _ "values (@ii_file, @id_file, @od_file, @od_file, @folder, @import_desc, @id_client, @basis_of_study, @id_is, @project_leader, @urisk_model_basis, @as_at_date, @extent_benchmark) " & _ "select @id_im = @@identity" .Parameters.Append .CreateParameter("@id_im", adInteger, adParamOutput) .Parameters.Append .CreateParameter("@ii_file", adVarChar, adParamInput, 255, Me.cbo_ii) .Parameters.Append .CreateParameter("@id_file", adVarChar, adParamInput, 255, Me.cbo_id) .Parameters.Append .CreateParameter("@oi_file", adVarChar, adParamInput, 255, Me.cbo_oi) .Parameters.Append .CreateParameter("@od_file", adVarChar, adParamInput, 255, Me.cbo_od) .Parameters.Append .CreateParameter("@folder", adVarChar, adParamInput, 1000, Me.txt_folder) .Parameters.Append .CreateParameter("@import_desc", adVarChar, adParamInput, 255, Me.txt_import_desc) .Parameters.Append .CreateParameter("@id_client", adBigInt, adParamInput, Me.cbo_client) .Parameters.Append .CreateParameter("@basis_of_study", adVarChar, adParamInput, 255, Me.txt_basis_of_study) .Parameters.Append .CreateParameter("@id_is", adSmallInt, adParamInput, Me.cbo_status) .Parameters.Append .CreateParameter("@project_leader", adVarChar, adParamInput, 255, Me.txt_project_leader) .Parameters.Append .CreateParameter("@urisk_model_basis", adVarChar, adParamInput, 1000, Me.txt_urisk_model) .Parameters.Append .CreateParameter("@as_at_date", adDate, adParamInput, CDate(Me.txt_as_at_date)) .Parameters.Append .CreateParameter("@extent_benchmark", adVarChar, adParamInput, 1000, Me.txt_extent_benchmark) .Parameters.Append .CreateParameter("@id_im", adInteger, adParamOutput) .Execute rec_affected, , adExecuteNoRecords If rec_affected > 0 Then Me.cbo_import = .Parameters.Item("@id_im") End If End With End Sub
View Replies !
View Related
Using Select Statement Result In If Statement Please Help
Hello How can i say this I would like my if statement to say: if what the client types in Form1.Cust is = to the Select Statement which should be running off form1.Cust then show the Cust otherwise INVALID CUSTOMER NUMBER .here is my if statement. <% If Request.Form("Form1.Cust") = Request.QueryString("RsCustNo") Then%> <%=Request.Params("Cust") %> <% Else %> <p>INVALID CUSTOMER NUMBER</p> <% End If%> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:RsCustNo %>" ProviderName="<%$ ConnectionStrings:RsCustNo.ProviderName %>" SelectCommand="SELECT [CU_CUST_NUM] FROM [CUSTOMER] WHERE ([CU_CUST_NUM] = ?)"> <SelectParameters> <asp:FormParameter FormField="Cust" Name="CU_CUST_NUM" Type="String" /> </SelectParameters> </asp:SqlDataSource>any help would be appreciated
View Replies !
View Related
If STATEMENT Within Select Statement Syntax
Hi, I am a newbie to this site and hope someone can help.... I have a select statement which I would like to create an extra column and put an if statement in it.... Current syntax is: if(TL_flag= '1', "yes") as [Trial Leave] it is coming up with an error.... I can use Select case but I should not need to as this should work? Any ideas?
View Replies !
View Related
Trying To Return A String Into Parameters Of An INSERT Statement
Hey guys, What I am trying to do is call a function that returns a string into the parameters of an INSERT command. I have a simple input form that asks the user for their first name, last name, address, city, state, zip code, e-mail address, home and work phone numbers. I then call a script that creates a 16 digit, alpha-numeric confirmation number that I want to also submit into the database. I know the script works, any ideas?? <% response.write(makePassword()) %> // This will write the confirmation number to the web page <asp:SQLDataSource ID="sqlDS1" Runat="Server" InsertCommand="Insert into customer (f_name,l_name,address,city,state,zip,email,h_phone,w_phone,conf) values (@fname,@lname,@addr,@city,@state,@zip,@email,@hp,@wp,@conf)" ConnectionString="<%$ ConnectionStrings:horesyconn %>"> </asp:SQLDataSource> Any advice will be appreciated
View Replies !
View Related
OLEDB Command Gives An Error For The Parameters Used In The Second Insert Statement
Hi, First script mentioned here does not work while the second script works even though they are doing the same thing and first one is good way. OLEDB Command gives an error for the parameters used in the second insert statement of the first script. Why is it so? --First Script INSERT INTO PrimaryKeyTable(ID, FirstName) VALUES (?,?) If @@rowcount=1 BEGIN DECLARE @MaxID as int SELECT @MaxID =max(AutoID) FROM dbo.PrimaryKeyTable --AutoIncremented column INSERT INTO [ForeignKeyTable] ( [MaxID] ,[Cost] ,[MarkDownDollars] ,[VersionCode] ) VALUES(@MaxID,?,?,'act') END -- Second Script INSERT INTO PrimaryKeyTable(ID, FirstName) VALUES (?,?) If @@rowcount=1 BEGIN DECLARE @Cost AS money SET @Cost=? DECLARE @MarkDownDollars AS money SET @MarkDownDollars=? DECLARE @MaxID as int SELECT @MaxID =max(AutoID) FROM dbo.PrimaryKeyTable INSERT INTO [ForeignKeyTable] ( [MaxID] ,[Cost] ,[MarkDownDollars] ,[VersionCode] ) VALUES(@MaxID,@Cost,@MarkDownDollars,'act') END
View Replies !
View Related
Select Parameters With Gridview
I have a form that has 4 fields to fill in. I have a button that can add these fields to a sql database. I also want to put a button next to the "add" button so you can fill out the same fields and search for those values. Here's what i have so far. I want to select based on the fields, but i'm having trouble with the syntax of the parameters. I also want to add something, so if nothing is filled out in one of those boxes, it retuns back all records for the default value. string strConnection = ConfigurationManager.ConnectionStrings["TimeAccountingConnectionString"].ConnectionString; SqlConnection myConnection = new SqlConnection(strConnection); String selectCmd = "SELECT * FROM users WHERE firstname = @firstame or lastname = @lastname or office = @office or team = @team"; SqlDataAdapter myCommand = new SqlDataAdapter(selectCmd, myConnection); myCommand.SelectCommand.Parameters.Add(new SqlParameter("@firstname", SqlDbType.VarChar, 50)); myCommand.SelectCommand.Parameters.Add("@firstname", txtFirstName.Text); myCommand.Parameters.AddWithValue("@lastname", txtLastName.Text); myCommand.Parameters.AddWithValue("@team", dwnTeam.Text); myCommand.Parameters.AddWithValue("@office", dwnOffice.Text); DataSet ds = new DataSet(); myCommand.Fill(ds, "users"); MyDataGrid.DataSource = ds.Tables["users"].DefaultView; MyDataGrid.DataBind();
View Replies !
View Related
Define Select Parameters
Hi I have a DropDownlist (Drop1) and a GridView,the GridView is bount to an SqlDataSource1 that has 2 Select parameters CatId and SourceId The dropdownlist has a selectedvalue of the following format 15-10(2 numbers seperated by -).I want to set CatId to 15 and SourceId to 10 <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:Art %>" SelectCommand="Select * from Option Where SourceId=@SourceId And CatId=@CatId"> <SelectParameters> <asp:ControlParameter ControlID="Drop1" Name="SourceId" /> <asp:ControlParameter ControlID="Drop1" Name="CatId" /> </SelectParameters> </asp:SqlDataSource> Can anyone help me to define the parameters? thanks
View Replies !
View Related
Select Parameters With Dates
I have a sqlDataSource control that has its select command and parameters set dynamically. One of the parameters is a date, which should be in smalldatetime format (ie, 12/22/2006). Obviously, using SQL Server, the data is stored with both date and time. The problem is, I cannot get any data selected. I initially thought that I needed to tell it to only look at the date and not the time, but from what I have seen this should work. Here is the pertinent code. dim seldate as datetime selDate = Session("ChosenDate") Dim SelCmd As String = "SELECT [PatientName], [AssignedTo], [CPT], [CPT2], [HospitalID], [RoomNbr], [ACType], [TxDx1], [TxDx2], [MRNbr], [Notes], [PatientID], [PCPID], [ResidentID], [Status], [CaseID], [AdmitDate], [crtd_datetime] FROM [DailyBilling] WHERE ([crtd_datetime] = @crtd_datetime) and ([AssignedTo] = @AssignedTo) and ([HospitalID] = @HospitalID) ORDER BY [PatientName]" SqlDataSource1.SelectCommand = SelCmd SqlDataSource1.SelectParameters.Clear() Dim parFilterProvider As New ControlParameter() parFilterProvider.ControlID = "ddlProvider" parFilterProvider.Name = "AssignedTo" parFilterProvider.Type = TypeCode.String SqlDataSource1.SelectParameters.Add(parFilterProvider) Dim parFilterHospital As New ControlParameter() parFilterHospital.ControlID = "ddlHospitals" parFilterHospital.Name = "HospitalID" parFilterHospital.Type = TypeCode.String SqlDataSource1.SelectParameters.Add(parFilterHospital) Dim parFilterStatus As New Parameter() parFilterStatus.Name = "crtd_datetime" parFilterStatus.DefaultValue = selDate.ToShortDateString parFilterStatus.Type = TypeCode.DateTime SqlDataSource1.SelectParameters.Add(parFilterStatus) Gridview1.databind() Any ideas?
View Replies !
View Related
Odbc Select Where Parameters
Im enabling an apllication to use ODBC to connect to sqlserver which currently uses Oracle OCI, i have no prior knowledge about odbc use. Im unsure how to approach where clause parameters (bind parameters) ie Oracle OCI select name into :name from emp where name = :a_name via ODBC, connecting to sql server i'm attempting select name from emp where name = ? with, sqlprepare sqlbindparameter sqlexecute sqlbindcol sqlfetch all seems ok with sqlbindparameter, but sqlexecute fails with sqlstate 22001, String data, right truncation. The question: can i use ? parameter in where conditions, if not whats the best approach. Many Thanks.
View Replies !
View Related
How To Select (see) Only Parameters Available In The Cube
Hello, I have a report based on a cube with some of the dimensions as parameters filling the drop-down list boxes. What I want to see, or choose from, are only the selections that are available in the particular cube. For example, although I have Managers, Directors and Partners in my dimension, this cube only contains Directors and Managers. How can I make the parameter only contains those two choices? Thank you for the help. -Gumbatman
View Replies !
View Related
|
TRACKER
