Passing A Parameter From A Form To An Sql Sequel View
Oct 23, 2006
Hi,
We have recently upsized an access db to a sequel server db. The queries in access have been made into views in sql. ASP will be the front end. We are having problems passing an input from a form into a view. In access the parameter had to match what was selected on the form but sequel doesn't like it. At the moment, for testing purposes, the value has to be hard coded i.e. case_id = 64. Is there anything is ASP that can help?
Thank you
View 1 Replies
ADVERTISEMENT
Apr 18, 2008
I've written a Union query that joins 16 different tables. I need to somehow make this into a permanent view so that i can query directly from excel to extract specific data - i guess i can set up excel to pull straight from this view then ?
I'm used to running excel VB code to SQL via Access (linking access up) but want to cut out the middleman now.
Any ideas ?
An example of the Union Query i have built is below :
SELECT[Vendor No_],[Posting Date],[Purchase (LCY)],[Buy-from Vendor No_],[External Document No_],[IC Partner Code]
FROM [NAV402].[dbo].[TESTCO Sweden$Vendor Ledger Entry]
WHERE[Posting Date]>='20070701' AND [Vendor No_] LIKE 'ZX%'
UNION
SELECT[Vendor No_],[Posting Date],[Purchase (LCY)],[Buy-from Vendor No_],[External Document No_],[IC Partner Code]
FROM [NAV402].[dbo].[TESTCO Taiwan$Vendor Ledger Entry]
WHERE[Posting Date]>='20070701' AND [Vendor No_] LIKE 'ZX%'
UNION
SELECT[Vendor No_],[Posting Date],[Purchase (LCY)],[Buy-from Vendor No_],[External Document No_],[IC Partner Code]
FROM [NAV402].[dbo].[TESTCO US$Vendor Ledger Entry]
WHERE[Posting Date]>='20070701' AND [Vendor No_] LIKE 'ZX%';
Thanks for any help you can give me.
PID
View 3 Replies
View Related
Jan 9, 2007
Request is to have a Requirement number from the requirement form generate a report in Reporting Services with the requirement number as a filter.
I can set up the parameter - how does the value get there? Should I be asking this question in the Visual C# group?
Thanks!
Terry B
View 1 Replies
View Related
Jun 23, 2006
Frustration has gotten the best of me on this one. Can anyone help?
I need to pass the current value in an Access Data Project (back-end is MS SQL) text field to the where condition in an SQL using VBA. This is what I have, but does not work.
Private Sub Command44_Click()
Me.SS.SetFocus
Dim strSQL As String
Dim strSSecurity As String
strSSecurity = Me.SS
strSQL = "Update Employees Set employees.PositionID = '',employees.jobcode = '' Where employees.ss = strSSecurity"
DoCmd.RunSQL strSQL
End Sub
It is supposed to take the current Social Security number from the form and match it against the employees table. Once it finds the matching record it should update the PositionID and JobCode fields to '' (empty string)
But it doesn't
Anyone with any ideas?
Thanks
Dan
View 2 Replies
View Related
Apr 12, 2007
Not sure what i'm doing wrong here. it seems to be sending nulls or no value to database in places where i want it to send the @headline and @entryhere's the .aspx code: <%@ Page Language="c#" MasterPageFile="~/MasterPage.master" AutoEventWireup="true" CodeFile="NewPost.aspx.cs" Inherits="NewPost" Title="Scribbler Insert" %> <asp:Content ID="PageBody" runat="server" ContentPlaceHolderID="PageBody" ><table border="0" width="700" > <tr> <td width="80" valign="top"> Subject: </td> <td width="620" valign="top"> <asp:TextBox ID="txtHeadline" runat="server" Width="400px"/> <asp:RequiredFieldValidator runat="server" ID="RequiredFieldValidator1" ControlToValidate="txtHeadline" ErrorMessage="Headline is required" Display="Dynamic" /> </td> </tr> <tr> <td width="80" valign="top"> Text: </td> <td width="620" valign="top"> <asp:TextBox ID="txtEntryText" runat="server" TextMode="MultiLine" Height="250px" Width="400px" /> <asp:RequiredFieldValidator runat="server" ID="RequiredFieldValidator2" ControlToValidate="txtEntryText" ErrorMessage="Text is required" Display="Dynamic" /> </td> </tr></table><asp:Button ID="btnPost" runat="server" Text="Post" OnClick="btnPost_Click" /><asp:Button ID="btnCancel" runat="server" PostBackUrl="~/Admin/Admin.aspx" Text="Cancel" /> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:scribblerdbConnectionString %>" ProviderName="<%$ ConnectionStrings:scribblerdbConnectionString.ProviderName %>" InsertCommand="INSERT INTO entries_tbl (Headline, entry, PostedBy, PostedDate, PostedTime) VALUES (@Headline, @Entry, 'SteveP', '2007-12-12', '12:44');" > <InsertParameters> <asp:ControlParameter Name="Headline" ControlID="txtHeadline" PropertyName="Text" Type="String" /> <asp:ControlParameter Name="entry" ControlID="txtEntryText" PropertyName="Text" Type="String" /> </InsertParameters></asp:SqlDataSource></asp:Content>
View 2 Replies
View Related
Jul 20, 2005
I have a whole bunch of forms that have an unbound StartDate and anEndDate field that I have used in MSAccess MDB databases as parametersin queries (ie tblEvent.StartDate > Forms!myFormName.StartDate.)So, now I'm migrating this beast over to and ADP/SQL Server projectusing Views and Procedures.How do I pass the value in Forms!myFormName.StartDate to a Procedureand get something that looks like:If tblEvent.StartDate > Forms!myFormName.StartDate then ...Any help is GREATLY appreciated. This is a major problem before I canmove ahead with this beast!lq
View 1 Replies
View Related
Jul 23, 2005
I just changed my Access 2002 database to a SQL Server ADP project. Ihad a form where the user entered a value into a text box and when acommand button on the form was clicked a Report was opened. The reportsrecord source is a query. The query uses the value from the form textbox to restrict the query.Table name = EggsTableone of the columns in the table is named: EggColorForm name = EggColorFormForm text box name = ColorTextBoxThis sql worked for the query before I converted to SQL:SELECT EggsTable.EggColorFROM EggsTableWHERE (((EggsTable.EggColor)=[Forms]![EggColorForm]![ColorTextBox]));This no longer works. Can I change the syntax somehow to get this towork? I tried dropping the brackets around the word "Forms", I trieddropping all the square brackets, etc., nothing worked.I also tried just opening the report with the report's Server Filterproperty set to:EggColor=N'Forms.EggColorForm.ColorTextBox'I tried using the Report's open event to pass the form value directlyto the report. I tried setting a variable from the text box value onthe form. So far, nothing works. Any ideas?
View 7 Replies
View Related
Jul 29, 2015
I have two report , first is main report which is matrix and have one parameter User_ids which is multi value selection and my second report is basic chart of user_wise performance.
Now, my main report (matrix ) works fine for Multiple selection of users and i have putted one textbox on main report chart which has action properties set for chart report, when user click on chart button it must goes to chart with user selected in main report. Now , i have used expression for parameter to send it like ..
=join(parameter!user_id!value,",") which pass selected value to chartÂ
And when I am selecting single user it passing that value to chart parameter list but , when it is more than one user it errors with conversion failed when converting the nvarchar value '121,128' to data type int. But my chart also works when passing 121,128 in user parameter in preview of report .
View 2 Replies
View Related
Dec 3, 2015
Using SQL Server 2008R2 and Report Builder 3.0..I have an action set in a text box of a table. My intent is to pass the value of that text box (which is variable) to a sub-report in a popup window. Here's my code: URL....The parameter of the report I'm trying to open is @SONum.I'm guessing my error is involved in the formatting of how the value of the parameter is being passed. I've also seen examples where the report server and report values were parameterized, but I don't know where to define
Parameters!ServerAddress.Value anywhere.Do I need to have something set up a certain way within the report I'm opening? Here's the report Parameter settings on the report I'm trying to open.
View 3 Replies
View Related
Jan 10, 2007
i getting the following error message in runtime, i have no idea what my username and password is for my computer, does anyone know what this error message means, and how i can get my username and password.
Server Error in '/' Application.
Cannot open user default database. Login failed.Login failed for user 'ACER-0BCB4DEC3AASPNET'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Cannot open user default database. Login failed.Login failed for user 'ACER-0BCB4DEC3AASPNET'.Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace:
[SqlException (0x80131904): Cannot open user default database. Login failed.
Login failed for user 'ACER-0BCB4DEC3AASPNET'.]
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +735107
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838
System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +33
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +628
System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +359
System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28
System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424
System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66
System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105
System.Data.SqlClient.SqlConnection.Open() +111
System.Web.DataAccess.SqlConnectionHolder.Open(HttpContext context, Boolean revertImpersonate) +84
System.Web.DataAccess.SqlConnectionHelper.GetConnection(String connectionString, Boolean revertImpersonation) +197
System.Web.Security.SqlMembershipProvider.GetPasswordWithFormat(String username, Boolean updateLastLoginActivityDate, Int32& status, String& password, Int32& passwordFormat, String& passwordSalt, Int32& failedPasswordAttemptCount, Int32& failedPasswordAnswerAttemptCount, Boolean& isApproved, DateTime& lastLoginDate, DateTime& lastActivityDate) +1121
System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved, String& salt, Int32& passwordFormat) +105
System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved) +42
System.Web.Security.SqlMembershipProvider.ValidateUser(String username, String password) +83
System.Web.UI.WebControls.Login.OnAuthenticate(AuthenticateEventArgs e) +160
System.Web.UI.WebControls.Login.AttemptLogin() +105
System.Web.UI.WebControls.Login.OnBubbleEvent(Object source, EventArgs e) +99
System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35
System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +115
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +163
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42
P.s Also does any one know how to enable cookies for the login control on website.
i have noticed within the objectdatasource wizard, that something called profile is there, and this is where you specify the cookies, sessions etc. But i dont know where to start with this. Can anyone help?
thanks
andy
View 16 Replies
View Related
Mar 22, 2008
I am using Stored Procedures and C# to create my very first web app (VS2005 Professional, C#, SQLServer 2005). I am wanting to display the users just inserted details via the form view in read only mode. I have read through several partial examples, but I am still unsure as to how to call the id of the just inserted record and use that ID to display the details in the Form View as many examples do not use stored procedures and make use of VB code (I am using C#) and the SqlCommand. I am pretty sure that I do not have to use SqlCommand as I am using an object data souce with a DAL – but I could be wrong, as I am a noob. My stored procedure reads as follows: ALTER PROCEDURE dbo.usp_ResumeNameDetailsInsertFV @FirstName varchar(50), @MiddleName varchar(50), @LastName varchar(50), @UserID uniqueidentifier AS INSERT INTO [ResumeNameDetail] ([FirstName], [MiddleName], [LastName], [UserID]) VALUES (@FirstName, @MiddleName, @LastName, @UserID) RETURN SCOPE_IDENTITY() The stored procedure is called through the object data source and inserts the record as it should. I have manually tested this through the execute stored procedure in VS Server tab. Below is my Object Data Source: <asp:ObjectDataSource ID="ObjectDataSourceResumeNameDetailsFV" runat="server" DeleteMethod="Delete" InsertMethod="Insert" OldValuesParameterFormatString="{0}" SelectMethod="GetResumeNameDetailsFV" TypeName="ResumeTableAdapters.ResumeNameDetailsTableAdapter" UpdateMethod="Update"> <DeleteParameters> <asp:Parameter Name="NameDetailID" Type="Int32" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="FirstName" Type="String" /> <asp:Parameter Name="MiddleName" Type="String" /> <asp:Parameter Name="LastName" Type="String" /> <asp:Parameter Name="NameDetailID" Type="Int32" /> </UpdateParameters> <SelectParameters> <asp:ControlParameter ControlID="GridViewResumeNameDetails" PropertyName="SelectedValue" Name="NameDetailID" Type="Int32" /> </SelectParameters> <InsertParameters> <asp:Parameter Name="FirstName" Type="String" /> <asp:Parameter Name="MiddleName" Type="String" /> <asp:Parameter Name="LastName" Type="String" /> <asp:ControlParameter ControlID="HiddenFieldGUID" Name="UserID" PropertyName="Value" Type="String" /> </InsertParameters></asp:ObjectDataSource> Below is the partial of my Form View: <asp:FormView ID="FormViewResumeNameDetails" runat="server" Caption="<br />" CaptionAlign="Top" CssClass="formView" DataKeyNames="NameDetailID" DataSourceID="ObjectDataSourceResumeNameDetailsFV" EnableViewState="False" HeaderText="Name Details" HorizontalAlign="Center" OnItemDeleted="FormViewResumeNameDetails_ItemDeleted" OnItemInserted="FormViewResumeNameDetails_ItemInserted" OnItemUpdated="FormViewResumeNameDetails_ItemUpdated" RowStyle-HorizontalAlign="Center" SkinID="FormViewStandard" Width="100%"> Below is my insertion C# code: protected void FormViewResumeNameDetails_ItemInserted(object sender, FormViewInsertedEventArgs e){ GridViewResumeNameDetails.DataBind(); FormViewResumeNameDetails.DataBind(); GridViewResumeNameDetails.Visible = false; FormViewResumeNameDetails.Visible = true; } Any help in writing th C# code to call the stored procedure to call the scope_identity would be appreciated, as this has me stumped, even though I am sure it is quite a simple fix.
View 4 Replies
View Related
Jun 1, 2008
need help with my problem
i have this view
this code change the value field "new_unit" evry month from 1 > 2 > 3 > 4
like this evry 4 month it return to 1 >2..........
------------------------------------------ for example
if i put
unit_date = 01/05/2008
and unit=1
than new_unit=1
Code Snippet
SELECT empID, ShiftType, unit_date, unit, ISNULL(NULLIF ((unit - DATEDIFF(mm, unit_date, '01/01/' + CONVERT(varchar, YEAR(GETDATE())))) % 4, 0), 4)
AS new_unit
FROM dbo.empList
my question is how to create a stored procedure that move forward (all the employee) the "new_unit" field
in +1 OR "unit_date" value MONTH +1
like create stored procedure name "plus" + so if i run this name stored procedure name "plus"
the stored procedure go to the viewor table and change the code view or table value
so i forward all the the "new_unit" or "unit_date" value IN one (change the cycle)+1
it doesn't matter if it change the "unit" value in the table "dbo.empList" or "unit_date" value
the important thing is that i can forward +1 or backward -1
evry time i run the stored procedure i get +1 (in the "new_unit")
and olso
create stored procedure name "minus" + so if i run this name stored procedure name "minus"
this stored procedure that move backward the the "unit" value in the table "dbo.empList" or "unit_date" value in -1
TABLE dbo.empList
empid ShiftType unit_date unit
----------------------------------------------------------------------------
11111 6 01/01/2008 1
2222 8 01/03/2008 4
3333 9 01/04/2008 3
TNX for the help
View 7 Replies
View Related
May 13, 2015
I want to change the color of the ssas cube calculated member in formview from red background to white or back to default color settings. This also includes changing the text color back to default. What properties do I have to change?
View 4 Replies
View Related
Feb 19, 2007
Hi,
I'm pretty new to ASP.NET and VB, but I'm working on a project which is essentially a staff directory. Using VS 2005, I've setup a basic grid view which connects to an object which connects via a data layer to a SQL database with Name, Surname, Email, Extension No and Department fields.
I have it working so that if a user enters a name and surname for example, it will return all records with either the name matching or surname matching input parameters. What I want to do is to set up a SQL query which is:
SELECT * FROM records WHERE (Name LIKE @Name) AND (Surname LIKE @Surname) AND (Email LIKE @Email) AND (Dept LIKE @Dept)
so that if the user only enters the first name and surname for example as above, it ONLY returns the record which matches the first name and surname and not all records with either/or. One way I've thought of to do this is to convert the empty fields to "Nothing" so that it fullfils the search parameter for the empty fields. If someone can explain how to do this, or can suggest a better way, I'd be grateful.
Cheers,
Tom
View 3 Replies
View Related
Aug 27, 2004
Procedure 'sp_displaylabels' expects parameter '@mod_id', which was not supplied.
I get the error at the SQLAdptr1.Fill(DS): Procedure 'sp_displaylabels' expects parameter '@mod_id', which was not supplied.
But i am passing the @mod_id value in the string Valmodid.
Private Sub BindData()
Dim DS As New DataSet
Dim SQLCmd1 As New SqlCommand
Dim moduleId As New SqlParameter
Dim Valmodid As String
Valmodid = Request.QueryString("modid")
moduleId = SQLCmd1.Parameters.Add("@mod_id", Valmodid)
SQLCmd1 = New SqlCommand("sp_displaylabels", MyConnection)
Dim SQLAdptr1 As SqlDataAdapter = New SqlDataAdapter(SQLCmd1)
SQLAdptr1.Fill(DS)
MyDataGrid.DataSource = DS
MyDataGrid.DataBind()
End Sub
'''My Stored Procedure code:
CREATE PROCEDURE [dbo].[sp_displaylabels]
(
@mod_id nvarchar(10)
)
AS
SELECT *
FROM tbl_labels
where module_id=@mod_id ORDER BY id ASC
GO
View 1 Replies
View Related
Sep 18, 2002
Can I pass a parameter to a view? Can something like this even be done?
CREATE view co_interlinks1
AS
SELECT [TDirectorships].[IDDir], [TDirectors].[DirLName] + ', ' + [TDirectors].[DirFName] AS DirectorName, [TDirectorships].[Ticker], [TCompanies].[CompanyName]
FROM TDirectorships
INNER JOIN TCompanies
ON [TCompanies].[Ticker]=[TDirectorships].[Ticker]
INNER JOIN TDirectors
ON [TDirectorships].[IDDir]=[TDirectors].[IDDir]
WHERE [TDirectorships].[IDDir] in (SELECT [TDirectorships].[IDDir]
FROM TDirectorships
WHERE Ticker=@Ticker)--This line requires a variable value corresponding to Ticker for the chosen company
and [TDirectorships].[Ticker] <> =@Ticker --This line requires a variable value corresponding to Ticker for the chosen company
View 2 Replies
View Related
Jan 18, 2002
Hi,
I have a stored proc as follows:
Create stored procedure sp_test
@Cno nvarchar (1000) AS
SELECT * FROM Contracts WHERE Cno IN (@Cno)
How do I actually pass the parameters to the stored proc?
sp_test "'cn01','cn02','cn03','cn04'" doesn't work.
In VB.NET this doesn't work either:
dim s as string = "'cn01','cn02','cn03','cn04'"
cmd.parameters.add(new sqlparameter("@Cno",nvarchar,1000))
cmd.parameters("@Cno").value = s
I don't want to parse the string inside the proc and then Exec the parsed string.
TIA
Jeremy Holt
View 5 Replies
View Related
Apr 9, 2008
Hi ,
I have this situation,I need to store results of a Select Count(*) from TableA to a parameter and only if the value of the count(*) is greater than 50,000,a notification mail would be sent out to the table owner.Currently this is what I have done:
1) Use SQL task to include "Select Count(*) from TableA"
2) In parameter mapping tab of SQL Task, define the following
Parameter name : Table::Count
Direction: Input
Data Type: Numeric
Parameter Name : @Count
3) I have connected the SQL Task to a Send Mail Task [ Only If @count>50 000, then a mail should be sent out]
4) The constraints has been set as
Evaluation Operation : Expression and Constraints
Value: Success
Expression : @Count >"50000"
In SSIS tab > Variable
I have set the following
Variable Name : Count
DataType : Int32
Can someone advise me where I am going wrong, as when I execute the SQL Task turns greens and ends.Even when the count of tableA is greater than 50 000, no mails I sent out[send mail task does not get executed]
Thank You in Advance
View 12 Replies
View Related
Mar 18, 2008
how can i pass report parameter via URL?
my jump to URL link is:
http://itreportsme096/Reports/Pages/Report.aspx?ItemPath=%2fDevelopment%2fhoneylette%2fdetailed+phone+calls
and i want to pass two parameters: billingmonth and phoneno
how can i do that?
View 7 Replies
View Related
Jul 23, 2005
I have a stored procedure named "processInventory" like the following.Depending on the passed in parameters, I would like to add a WHEREclause for "select" action. For example, if any varchar type ofparameter is passed in, the where clause would use "LIKE" operator. Forexample, "Select * from Main where [s/n] like @Serial. All other typeswill use "=" operator. For example, "Select * from Main where MAKE =@Make and Type = @type".How could this be achieved? Thanks.CREATE PROCEDURE processInventory@Action varchar(7),@ControlNumber int = null,@AssetTag int = null,@Serial varchar(50) = null,@Description varchar(50) = null,@Make int = null,@Type int = null,@Model int = null,@Status int = null,@Networked bit = null,@LoginName varchar(50) = null,@Shared bit = null,@Org varchar(15) = null,@RecordDate datetime = null,@LastUpdate datetime = null,@ManufactureDate datetime = null,@Comment ntext = nullASdeclare @processError intset @processError = 0if @Action = 'Select' goto selectInventoryelseIf @Action = 'Update'beginif @ControlNumber = null return(1) --Required parameter value notspecifiedelsegoto updateInventoryendelseif @Action = 'Insert'beginif @Serial = null return(1) --Required parameter value notspecifiedelsegoto InsertInventoryendelseif @Action = 'Delete'beginif @ControlNumber = null return(1) --Required parameter valuenot specifiedelse goto deleteInventoryendselectInventory:if @Serial <> nullbeginselect * from Main where [S/N] like @Serialif @@Error<>0beginset @processError = @@Errorreturn @processErrorendendelseif @ControlNumber <> nullbeginselect * from Main where ControlNumber = @ControlNumberif @@Error <>0beginset @processError = @@Errorreturn @processErrorendendelseselect top 100* from MainupdateInventory:update MAINset [Org Asset Tag] = @AssetTag, [S/N] = @Serial, [Description]= @Description, Make = @Make, Type = @Type,Model = @Model, Status = @Status, Networked = @Networked,LoginName = @LoginName, Shared = @Shared,Org = @Org, [Date Of Record] = @RecordDate, [Date LastUpdated] = @LastUpdate, [Manuf Date] = @ManufactureDate,Comments = @Commentwhere ControlNumber = @ControlNumberif @@ERROR <> 0beginset @processError = @@ERRORreturn @processErrorendelsereturn(0) -- successful updateinsertInventory:insert MAIN([Org Asset Tag], [S/N], [Description], Make, Type,Model, Status, Networked, LoginName, Shared,Org, [Date Of Record], [Date Last Updated], [ManufDate],Comments)values(@AssetTag, @Serial, @Description, @Make, @Type, @Model,@Status, @Networked, @LoginName, @Shared,@Org, @RecordDate, @LastUpdate, @ManufactureDate,@Comment)if @@ERROR <> 0beginset @processError = @@ERRORreturn @processErrorendelse return(0) -- successful insertdeleteInventory:delete MAIN where ControlNumber = @ControlNumberif @@ERROR <> 0beginset @processError = @@ERRORreturn @processErrorendelse return(0) -- successful deleteGO
View 4 Replies
View Related
Jun 17, 2005
I don't understand how to pass a parameter to a dts package from another. In SQL 2000 you can map a variable with a variable of the package you will call.
View 10 Replies
View Related
May 11, 2006
Hi all
I need to pass parameters when I call a .SQL file.
Is there a way to do this in SQL Server?
thanks!!!!
View 5 Replies
View Related
Dec 21, 2006
Hi guys...
As of now I'm working on a certian report which needs to pass a certain parameter to another dataset... I'm refering to the dataset of the same report. Can anyone tell me how this is done? I really need to know it to finish my report...
Thanks a bunch in advance!
View 4 Replies
View Related
Jan 28, 2008
Hi...I just installed Sequel Server Express v5 and have a database and tables created. Now trying to create a DSN to connect to the database for my mail server. The mail server and sequel server reside on the same system (windows 2003 server).
I'm choosing a system dsn and have tried local with named pipes, the sequel server name with named pipes and the sequel server name using TCP/IP to connect - they all fail. Not sure what else to do...
Any help appreciated. THANKS!
View 1 Replies
View Related
Sep 14, 2006
My data table contains a column (a foreign key) with numbers from 0 and up. (it could be null or a number. I used ISNULL to convert it.) I want to be able to select in three ways. 1) equals zero (or is null)2) greater than zero (or is not null)3) equal to a specific number.How do I do this?My application setup is Presentation Layer, BLL, DAL and Sql Server.I don't want to create another Query because this is only part of a larger query with other parameters.If I could just do something like @IDOperator @IDValue in the Filter of the Sql it would be great.
View 2 Replies
View Related
Aug 9, 2004
Hello,
Can someone kindly point out what is wrong with the following code file. I'm trying to:
- fill a dropdown from a db on page load (this works!)
- when user selects from the list and hits a button, pass the dropdown value to a second query
- use the second query to make another call to the db and fill a data grid
In the code below, if I swap an actual value (eg '1005') into the command and comment out the .Parameter.Add statements, the dategrid is filled sucessfully. Otherwise, when the button is pressed, nothing is displayed.
Thanks
PS comments about my coding approach are welcome - I'm new to aspx...
<%@ Language="VBScript" Debug="true"%>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.data.SqlClient" %>
<html>
<head>
<script language="vb" runat="server">
Dim dbConnection As SqlConnection
Dim ds_Teams,ds_Agents As DataSet
Dim sqlCmd_Teams,sqlCmd_Agents As SqlDataAdapter
Dim dbConn = "server=csacd01;uid=mpierce;pwd=cabledog;database=clearviewacd"
Dim sql_select_teams = "" & _
"SELECT team_no, team_name " & _
"FROM dbo.team " & _
"WHERE (team_status = 'Curr')"
Dim sql_select_agents = "" & _
"SELECT last_name + ', ' + first_name AS name, agent_no " & _
"FROM dbo.users " & _
"WHERE (team_no = @Team) AND (NOT (agent_no = '1029')) " & _
" AND (NOT (last_name IS NULL)) " & _
" AND (NOT (first_name IS NULL)) " & _
" AND (NOT (first_name = 'FirstName')) " & _
"ORDER BY last_name"
Dim teamList = "teamList"
Dim agentList = "agentList"
Sub Page_Load(Sender As Object, E As EventArgs)
if not (IsPostBack)
ds_Teams = new DataSet()
dbConnection = New SqlConnection(dbConn)
sqlCmd_Teams = New SqlDataAdapter(sql_select_teams, dbConnection)
sqlCmd_Teams.Fill(ds_Teams, teamList)
dbConnection.close()
dropdownlist_Teams.DataSource=ds_Teams.Tables(teamList).DefaultView
dropdownlist_Teams.DataBind()
end if
End Sub
sub Get_Agents(Sender As Object, E As EventArgs)
ds_Agents = new DataSet()
dbConnection = New SqlConnection(dbConn)
sqlCmd_Agents = new SqlDataAdapter(sql_select_agents, dbConnection)
sqlCmd_Agents.SelectCommand.Parameters.Add(new SqlParameter("@Team", SqlDbType.NVarChar,4))
sqlCmd_Agents.SelectCommand.Parameters("@Team").Value = dropdownlist_Teams.DataValueField
sqlCmd_Agents.Fill(ds_Agents,agentList)
dbConnection.close()
datagrid_Agents.DataSource=ds_Agents.Tables(agentList).DefaultView
datagrid_Agents.DataBind()
end sub
</script>
</head>
<body>
<form runat="server">
<asp:DropDownList id="dropdownlist_Teams" runat="server"
DataTextField="team_name"
DataValueField="team_no">
</asp:DropDownList>
<input type="submit" onserverclick="Get_Agents" value="Get Agents" runat="server"><br />
<ASP:DataGrid id="datagrid_Agents" runat="server"
Width="500"
BackColor="#ccccff"
BorderColor="black"
ShowFooter="false"
CellPadding=3
CellSpacing="0"
Font-Name="Verdana"
Font-Size="8pt"
HeaderStyle-BackColor="#aaaadd"
EnableViewState="false"
/>
</form>
</body>
</html>
View 1 Replies
View Related
Dec 17, 2005
Interesting problem, selecting and updating (with reference to a Form View) works great. Whenever I try to delete I get this error:Must declare the variable '@template_id'.
Description: An
unhandled exception occurred during the execution of the current web
request. Please review the stack trace for more information about the
error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Must declare the variable '@template_id'.
Here is my code:<asp:SqlDataSource ID="SqlTemplateDS" runat="server"SelectCommand="SELECT [template_id], [client_id], [language], [label], [email_default], [email_template], [event_template] FROM [CMT] ORDER BY [template_id]"DeleteCommand="DELETE FROM CMT WHERE (template_id = @template_id)" ConnectionString="<%$ ConnectionStrings:BrandMSConnectionString %>"> </asp:SqlDataSource><asp:GridView ID="grdTemplates" runat="server" AutoGenerateColumns="False" DataKeyNames="template_id" DataSourceID="SqlTemplateDS" Font-Names="Verdana" Font-Size="Small" ForeColor="Black" Width="100%" OnSelectedIndexChanged="grdTemplates_SelectedIndexChanged" CellPadding="2"> <Columns> <asp:BoundField DataField="template_id" HeaderText="ID" ReadOnly="True" SortExpression="template_id" /><asp:BoundField DataField="client_id" HeaderText="Client" SortExpression="client_id" /><asp:BoundField DataField="language" HeaderText="Language" SortExpression="language" /><asp:BoundField DataField="label" HeaderText="Label" SortExpression="label" /><asp:CheckBoxField DataField="email_default" HeaderText="Default" SortExpression="email_default" /><asp:CheckBoxField DataField="email_template" HeaderText="Email" SortExpression="email_template" /><asp:CheckBoxField DataField="event_template" HeaderText="Event" SortExpression="event_template" /> <asp:TemplateField HeaderText="Options"><ItemTemplate> <asp:LinkButton ID="btnModify" CommandName="Select" runat="server" ForeColor="Blue">Edit</asp:LinkButton> | <asp:LinkButton ID="btnDelete" CommandName="Delete" runat="server" ForeColor="Blue">Delete</asp:LinkButton></ItemTemplate></asp:TemplateField> </Columns><HeaderStyle BackColor="Khaki" /><AlternatingRowStyle BackColor="Beige" /></asp:GridView>Any ideas?Chris
View 1 Replies
View Related
Apr 28, 2006
why this wont work?error: Exception Details: System.Data.SqlClient.SqlException: Procedure or Function 'test1' expects parameter '@cid', which was not supplied..................................................................................ASPX CODE: string cid12; cid12 = "user5"; // Connection ConnectionStringSettings mysettings; mysettings = System.Configuration.ConfigurationManager.ConnectionStrings["dbase1_connection"]; string myConnectionString = mysettings.ConnectionString; SqlConnection conn1 = new SqlConnection(myConnectionString); SqlCommand cmd = new SqlCommand(); cmd.Connection = conn1; cmd.CommandText = "test1"; cmd.CommandType = CommandType.StoredProcedure; SqlParameter param = cmd.Parameters.Add("@cid", SqlDbType.NChar, 20); param.Direction = ParameterDirection.Input; param.Value = cid12; conn1.Open(); cmd.ExecuteNonQuery(); conn1.Close();.................................................................................STORED PROCEDURE:ALTER PROCEDURE dbo.test1 ( @cid nvarchar(20) )AS /* SET NOCOUNT ON */ select tech_id, customer_id, issue_main from [case] where customer_id = @cid --GO RETURN.................................................................................im using asp.net, C#, and sql expressforgive me. im new to this .netPLS HELP
View 1 Replies
View Related
Jul 19, 2004
possible to pass in a query as a parameter to a stored proc?
Number of constraints right now would make it a lot easier if I could pass in a query that selects all the ID's, tried but couldn't come up w/anything, just have a simple proc that does the deletes on 1 ID @ a time...since there are up to 100 that will need to be deleted, the qry as a param would be much more convenient. below is the proc...Thx for any help.
Code:
CREATE PROCEDURE [dbo].[s_DeletePeople]
@PeopleID int
/* single id to be deleted, tried just passing
* in a query that resembled a string and using it
* but it didn't work either.
*/
AS
Deletefrom tProjectManager
whereManager_ID in (@PeopleID)
Deletefrom tMerchandiser
whereID in (@PeopleID)
Deletefrom tProjectCall
wheremerchandiser_id in (@PeopleID)
Delete from tManager
whereID in (@PeopleID)
Deletefrom tDistrictManager
whereID in (@PeopleID)
Deletefrom tPeople
whereID in (@PeopleID)
....and on and on
GO
View 2 Replies
View Related
Jul 2, 2004
Hi there,
I have this problem..
I created a function in MS SQL and through this fuinction I am also passing the table name... now when I call the table name through the function in a SELECT statement, MS SQL is giving me an error that I have to declare the variabel....
Can someone help me??
Thanks!!
View 1 Replies
View Related
Jul 3, 2004
Hi there..
I created the following FUNCTION and I am trying to pass as a parameter a variable name withe the TABLE name.
MS SQL is not accepting it because it is asking me to declare it... can someone help me??
Thankyou
"CREATE FUNCTION getNSR2 (@tablename varchar(30)) RETURNS decimal(9,0)
AS
BEGIN
DECLARE @TB varchar(30)
SET @TB = @tablename
DECLARE @SR decimal(9,0)
SET @SR= (
SELECT MAX(SysRef)+1
FROM @tablename )
RETURN(@SR)
END
"
View 1 Replies
View Related
Feb 17, 2005
Hi...
I'm creating a procedure to fetch rows from table. One field will come come as IN(). Its the condition. That field is numeric field (note down), i would like to pass the In values as parameter.
eg: procedurename @fieldvalue varchar(100)
as
begin
...
WHERE fieldname IN(@fieldvalue)
while executing the procedure how to pass the value... or procedure itself has problem...?
Help me...
Tx in Advance...
View 2 Replies
View Related
Aug 1, 2014
I have a table with month names as columns, below is the structure
CREATE TABLE [MonthlyTarget]
(
[Jan] [int] NULL,
[Feb] [int] NULL,
[March] [int] NULL,
[April] [int] NULL,
[Code] ....
I will store some integer values in these columns,
I want to pass these column names as a parameter,and pull the data from the column.
How to do this..?
View 1 Replies
View Related
