I have a formview with name, email, and password. I bind all fields to sql except the password which is blank. In my sqldatasource, I define parameters for name, email and id: UpdateCommand="UPDATE UserProfile SET Name = @Name,Email = @Email WHERE (ID = @ID)"><UpdateParameters><asp:Parameter Name="Name" /><asp:Parameter Name="Email" /><asp:Parameter Name="ID" /></UpdateParameters> In code I want to add a password parameter if there is value in the password field otherwise I don't want the password field updated. If I add define a password parameter like above then if a user left the password field blank then their new is blank. That's way I think adding it dynamically is the way. But I am having problems with the code to add the parameter in sqldatasource_updating event. Protected Sub SqlProfile_Updating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles SqlProfile.UpdatingDim password As TextBox = FormView1.FindControl Protected Sub SqlProfile_Updating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles SqlProfile.Updating Dim password As TextBox = FormView1.FindControl("tb_password1") If Not password.Text.ToString & "" = "" Then SqlProfile.UpdateParameters.Add(New Parameter("@Password", TypeCode.String, password.Text.ToString)) End If
I am trying to use the code below to set a formview datasource parameter in the page_load section of a user control (ascx file): public void Page_Load(object sender, EventArgs e){ formview_datasource.SelectParameters.Add("@department_id", "e62bbc7d623f44a68e101cba90e839s3");} However I am getting the following error: Exception Details: System.Data.SqlClient.SqlException: Must declare the variable '@department_id'. So it would seem that page_load in my user control isn't being called? I'm not sure why or how to work around it. Has anyone else experienced anything like this or can give me some pointers on where I am going wrong? ThanksBrad
Hello frdz, I have two tables in sqlserver 2005. I have created the stored procedure for insert,update data.I m creating my application in asp.net with C# Table-1 CUSTOMERFields:customerid int identity,cardid int,customername varchar(20) not null,address varchar(20) not null,city varchar(20) not null,emailid varchar(20) Table-2 CARDFields:cardid int identity,cardtype varchar(20) not null,carddetails varchar(20) not null INSERT INTO CUSTOMER (customername ,address,city,emailid) VALUES (@customername,@address,@city,@emailid) SELECT @customerid = SCOPE_IDENTITY()/* HELP HERE NOT ABLE TO GET DATA OF CARD */ SELECT @cardid = cardid from CARD where customerid =@cardid Pls tell me how to insert the data ...There is only one cardid for only one customerid both should be unique no duplication....One customer can have only one and one card...
I would like to pass into a stored procedure the name of a table. Following code does not work. USE pubs GO CREATE PROC test1 @tableName varchar(40) AS SELECT * FROM @tableName EXEC test1 @tableName='authors'
I would appreciate it very much if someone could help me with this.
I have upsized a complex Access 97 application to SQL Server 7, and want to substantially preserve the user interface design by using an Access 2000 project (*.adp) as the front end.
Most of the original Access queries accepted their parameters from screen form objects, and this works well using SQL 7 stored procedures as form recordsources.
However, Access 2000 does not seem to provide a way to tell a listbox or combo box how to pass a parameter value from a screen form object to its SQL 7 stored procedure rowsource.
These parametized listboxes are critical to the interface design. Please help!!!
Sorry if I haven't choose appropriate forum for this question.
I have MSSQL05 beta. I know how to list all stored procedures in selected database (everything is in localhost). I need to list parameter names and types for selected stored procedure(s). How can I do that or anything that can return parameter names and types?
Variable Name : User::IntValue Direction: ReturnValue Data Type: Long ParameterName: 0
I am getting the following error, when I run this package.
[Execute SQL Task] Error: Executing the query "EXEC ? = [dbo].[SetSLATimePriority]" failed with the following error: "Invalid parameter number". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
I am not able to figure out, where exactly the problem is.. Can some one please help me out?
I have formview and want to get uniq id from SQL Server 2005 after it inserted. I think i should put some code in the event of oniteminserted, anybody have sample code Thanks Hu
Hello,I am trying to determine the best way to do the following. For simplicity we have two tables Master and Awards. These share a common pk UFID. Master contains columns (UFID, AccountName...) Awards contains columns (ID, UFID, AwardingAgency, Amount) When a user submits a new award in Formview I would like to populate the UFID automatically so that the user does not have to enter this each time. I am currently using the logged in username to select records for that user only. The value of this username matches Master.AccountName. Since I am hoping to use this same logic across many tables, I would appreciate any suggestions as to how best handle this.Thanks,Ken
i had writen a stored procedure for inserting vlaues frma form into sql but iam getting this error can any pls help me Failed to convert parameter value from a String to a Int32 my stored procedureALTER PROCEDURE sp_addcustomer
(@customerid as int, @fname as char(10),@lname as char(10) , @companyname as char(10),@email as nvarchar(50), @address as char(10),@city as char(10), @zip as char(10),@country as char(10), @phone as nvarchar(50),@state as char(10) )
this error comes up when I run a form to supmit information to the DB. .
"Procedure or function 'aspnet_Users_CreateUser' expects parameter '@Lname', which was not supplied" I have it declared in the procedure. so why is it telling me it was not supplied. Here is what I have:
Hi.here is my code with my problem described in the syntax.I am using asp.net 1.1 and VB.NETThanks in advance for your help.I am still a beginner and I know that your time is precious. I would really appreciate it if you could "fill" my example function with the right code that returns the new ID of the newly inserted row. Public Function howToReturnID(ByVal aCompany As String, ByVal aName As String) As Integer 'that is the variable for the new id.Dim intNewID As Integer Dim strSQL As String = "INSERT INTO tblAnfragen(aCompany, aName)" & _ "VALUES (@aCompany, @aName); SELECT @NewID = @@identity" Dim dbConnection As SqlConnection = New SqlConnection(connectionString)Dim dbCommand As SqlCommand = New SqlCommand()dbCommand.CommandText = strSQL 'Here is my problem.'What do I have to do in order to add the parameter @NewID and'how do I read and return the value of @NewID within that function howToReturnID'any help is greatly appreciated!'I cannot use SPs in this application - have to do it this way! :-( dbCommand.Parameters.Add("@aFirma", aCompany.Trim)dbCommand.Parameters.Add("@aAnsprAnrede", aName.Trim) dbCommand.Connection = dbConnection TrydbConnection.Open()dbCommand.ExecuteNonQuery() 'here i want to return the new ID!Return intNewID Catch ex As Exception Throw New System.Exception("Error: " & ex.Message.ToString()) Finally dbCommand.Dispose()dbConnection.Close()dbConnection.Dispose() End Try End Function
Hi,I'm trying to insert some values into 2 tables using stored procedures (which should be pretty straight forward) but I'm running into problems.Given below are my 2 sp that I'm using: USE [DBName] GO
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO
CREATE PROCEDURE [dbo].[AddToProject] (@DeptCode varchar(20), @ProjectTitle varchar(300), @ProjectDetails varchar(3000), @ProjectManagerID int, @RequestedBy varchar(100), @DateRequested datetime, @DueDate datetime, @ProjectStatusID int) AS BEGIN TRANSACTION SET NOCOUNT ON
DECLARE @Dept varchar(50), @ProjID varchar(50)
SET @Dept = REPLACE(CONVERT(char,@DeptCode),'.','')
GO I get the following errors: Msg 128, Level 15, State 1, Line 1The name "BAT" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.Msg 137, Level 15, State 1, Line 1Must declare the scalar variable "@ProjID".Msg 515, Level 16, State 2, Procedure AddToProject, Line 17Cannot insert the value NULL into column 'ProjID', table 'NYCEDC.dbo.tbl_Project'; column does not allow nulls. INSERT fails. If I run just the GetProjID sp, I get the following error: Must declare the scalar variable "@ProjID". -------------------------------------What I'm trying to do is, get a bunch of values from the aspx page, insert the DeptCode into the field DeptCode of the table 'tbl_ProjectNumber'+DeptCode so that the IDENTITY value field 'ProjectNumber'+DeptCode can increment by 1. After 'ProjectNumber'+DeptCode has incremented, I want to run the GetProjID sp so that I can insert ProjID into the 'tbl_Project' table along with the other values taken from the aspx page. ProjID is the primary key for the 'tbl_Project' table so it needs to be added along with the other values (other they obviously can't be added). I'm running the AddToProject as a Transaction to make sure that if more than 1 person is adding a project at the same time, the project numbers and other information don't get messed up. Any help would be appreciated.Thanks.
I m trying to INSERT a record to my Consultants table using sqldatasource and formview: here is my sqldatasource code: <asp:SqlDataSource ID="sqlDS1" runat="server" ConnectionString="<%$ ConnectionStrings:myDB %>"InsertCommand="INSERT INTO [Consultants] ([firstName],[lastName],[skillCategoryID],[resourceManagerID],[AMgroupID],[skillSet],[statusID],[location],[comments],[profile],[isAvailable],[dateModified],[focusID]) VALUES (@FNAME, @LASTNAME, 1, @RESOURCEMANAGERID, @AMGROUPID, @SKILLSET, @STATUSID, @LOCATION,@COMMENTS,@PROFILE,@ISAVAILABLE,getdate(),1)">
<InsertParameters> <asp:FormParameter FormField="txtFName" Name="FNAME" /> <%--other parameters--%></InsertParameters></asp:SqlDataSource> now in my formview i have this:<asp:FormView DefaultMode="Insert" ID="FormView1" runat="server" DataSourceID="sqlDS1" DataKeyNames="id"> <table border="0" cellspacing="5" cellpadding="0"><tr> <td class="blacktextbold">First Name:</td> <td class="blacktext"><asp:TextBox ID="txtFName" CssClass="txtfield" Width="200" runat="server" Text="<% #Bind('firstName')%>" /> <asp:RequiredFieldValidator ControlToValidate="txtFName" runat="server" ValidationGroup="gpInsert" ErrorMessage="Required Field" Display="Dynamic" CssClass="redtextsmallbold" /> </td></tr> </table> THE PROBLEM:no matter what i put the txtFName field its ALWAYS null and i get INSERT error as column firstName cant accept NULLany ideas? ur help would be appreciated
I am using Excel VBA to run a stored procedure which executes a package using the built-in SQL Server stored procedures. The VBA passes two values from excel to the stored proc., which is then supposed to pass these "parameters" to the package to use as a variable within the package.
@Cycle sql_variant = 2 WITH EXECUTE AS 'USER_ACCOUNT' - account that signs on using windows authentication AS BEGIN SET NOCOUNT ON; declare @execution_id bigint
[code]....
When I try to execute the package, from SQL Server or Excel using the Macro I built, I get the following error:"The parameter '[User::Cycle]' does not exist or you do not have sufficient permissions." I have given the USER_ACCOUNT that runs executes the stored procedure permission to read/write to the database and the SSIS project folder.
Hi,I'm trying to insert some values into 2 tables using stored procedures (which should be pretty straight forward) but I'm running into problems.Given below are my 2 sp that I'm using:
I am trying to create stored procedure i Query analyzer in visual studio 2005. I am havingdifficulty though. Whenever I press the execute button, here is the error message I get: Msg 102, Level 15, State 1, Procedure MarketCreate, Line 21Incorrect syntax near 'MarketName'. Here is the stored procedure. Note that the very first column in named "MarketId" but I did notinclude it in the stored procedure since it should be auto generated. USE [StockWatch]GO/****** Object: StoredProcedure [dbo].[MarketCreate] Script Date: 08/28/2007 15:49:26 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO CREATE PROCEDURE [dbo].[MarketCreate] ( @MarketCode nvarchar(20), @MarketName nvarchar(100), @LastUpdateDate nvarchar(2), @MarketDescription nvarchar(100)) ASINSERT INTO Market( MarketCode MarketName LastUpdateDate MarketDescription)VALUES( @MarketCode @MarketName @LastUpdateUser @MarketDescription)
I have a formview that uses a predefined dataset based on a cross table query. When the formview is in insert mode I need to insert the data into two seperate tables. Essentially I have tblPerson and tblAddress and my formview is capturing username, password, name, address line1, address line 2, etc. I presume I need to use a stored procedure to insert a row into tblPerson and then insert a row intp tblAddress. This is easy enough to do but the tables use RI and tblPerson has an imcremental primary key which needs to be innserted into a foreign key field in my address row. How do I do this? I'm using SQL Server.
hi i need to insert the list of ipaddress using stored procedures. the user will give the from and to range of IP ADDRESS.i've to insert all the possible ip address between those values. how to do this..
I am trying to simulate the <sequence name>.nextval of oracle in SQL Server 2000.
The situation is that i need to be able to run programmatically INSERT statements. In Oracle I am able to do INSERT INTO TABLE_A (ID, NAME) VALUES (TABLE_A_SEQUENCE.NEXTVAL, 'MIKKO') in a single prepared statement in my code.
I know that to recreate this in SQL Server 2000 I need to create a stored procedure and table to set up a way to generate "the next value" to use in my INSERT. but the schema below forces me to do my insert in 2 steps (first, to get the next value. second, to use that value in the INSERT statement), since I cannot execute the stored procedure inside my INSERT statement.
Is there any way for me to generate values within my INSERT statement that would simulate Oracle's <sequence name>.nextval and allow me to execute my INSERT in 1 line of code?
TABLE ----- CREATE TABLE sequences ( -- sequence is a reserved word seq varchar(100) primary key, sequence_id int );
MS SQL SERVER STORED PROCEDURE: ------------------------------- CREATE PROCEDURE nextval @sequence varchar(100)AS BEGIN -- return an error if sequence does not exist -- so we will know if someone truncates the table DECLARE @sequence_id int set @sequence_id = -1
UPDATE sequences SET @sequence_id = sequence_id = sequence_id + 1 WHERE seq = @sequence
Dear All, i have a SqlDataSource with a simple select command(e.g. "select a,b,c from foo"). The insert command is a stored procedure and takes less parameters than there are columns in select statement (e.g. "insertFoo(a char(10))"). When used in combination with form view, i get "Procedure or function insertFoo has too many arguments specified" error. It seems that form view always posts all columns as parameter collection (breakpoint in formview_inserting event shows this) to insert command. Am I doing something wrong or is this by design? Is the only solution to manualy tweak parameters in formview_inserting event? TIA Jernej
I have my database: "RequestTrack" My table (with its columns): "Request"RequestKey (automatically generated)..and the Primary KeyEntryDate (datetime)Summary (nvarchar)RequestStatusCodeKey (bigint)EntryUserID (nvarchar)EntryUserEmail (nvarchar)I am wanting to create a basic web form where my user interface has 3 text boxes and a Submit button: txtUserID.TexttxtEmailAddress.TexttxtRequestSummary.Text **After I hit the submit button the information will then be inserted into the database. Also the RequestStatusCodeKey will be MANUALLY typed in so that will not require the user to add that. Please please please help ! I've been searching online for days and looking at various websites and still havent found anything. I've found somethings but they went into too much depth with too much information. I am just wanting to stay basic but w/o using SQLDataSource Controls. I would like to be able to store a lot of data. Thanks for your help!!!
I am trying to write a stored procedure, which does a couple of things.
First thing is it looks up a persons Location based on an ID number, which is passed from an external Script. This will return four Values from a table.
I want to insert those four values into another table, along with another ID passed to the procedure from the same script.
My question is, what do I do to the script below to get the four values out of the first look up, into the insert?
The Field names returned from the SELECT are, AREA1, AREA2, AREA3, AREA4
I have a form view that I am using to insert new data into a sql express database and would like to find a way to avoid attempting to insert a record if the key already exists. is there a way to do this with the formview insert command. Everything works great until I try to add a record with an already existing value in the unique key field, then it breaks.
I'm trying to get to grips with the formview control and have a problem regarding where an exception is raised if SQL server raises an error when an insert is taking place. I am new to both asp.net & c# I have put the following code in the SqlDataSource2_Inserted & Formview1_Inserted events as a way of determining where the exception is raised: if (e.ExceptionHandled){ string exceptionmessage = e.Exception.Message; lblStatus.Text = exceptionmessage;} These two events are the only one's that have the ExceptionHandled and Exception properties. I run the code with data that will raise a duplicate key error from SQL Server but the error is not being caught by either of these two handlers. All I get is a page that starts with Server Error in '/SourceCode' Application.
Violation of UNIQUE KEY constraint 'IX_reftblAntigens'. Cannot insert duplicate key in object 'reftblAntigens'.The statement has been terminated. 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: Violation of UNIQUE KEY constraint 'IX_reftblAntigens'. Cannot insert duplicate key in object 'reftblAntigens'.The statement has been terminated.... Is there somewhere else I should be looking to handle this sort of thing? I thought using the databound controls would make life easier but I'm actually finding it more of a pain than coding everything myself.
I have a database schema that has an Address table used to store addresses for different entities such as Customers and Employees. I want to reuse the same Address record between different Customers and Employees without duplicating any address information. I'm not sure what the best approach might be.
Should have I have seperate stored procedures on the Address table that update and insert new addresses, where each Address record remains immutable once created? (So the update stored procedure actually creates a new Address record if the data changes). These stored procedures would then be invoked by business logic and used in tandem with stored procedures that act on Customers and Employees to ensure that no address records are duplicated.
Or should I create a view on a Customer joined with Address, and similarily with Employee and Address, and have stored procedures that act on these views and ensure that no Address records are duplicated. Should I use instead of triggers to override the behavior of insert and update on the view to achieve these?
I'm rather lost as to what direction I should take. Any help would be much appreciated, thanks!
I m trying to make insert stored procedure with parameters, i want to send table name and insert values with parameters, i m trying that below stored procedure but it gives syntax eror to me what shoul i do to correct it ?
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[insertalepform]
-- Add the parameters for the stored procedure here
Please excuse me if this question has been asked before; I couldn’t find it. Perhaps someone could point me to the solution. A few years ago I wrote an order-entry application in classic ASP that I am now re-writing in ASP.NET 2.0. I am having very good success however I can’t figure out how to retrieve data from a stored procedure. I am using the FormView & SqlDataSource controls with a stored procedure to insert items into an order. Every time an item is inserted into the order the stored procedure does all kinds of business logic to figure out if the order qualifies for pricing promotions, free shipping, etc. If something happens that the user needs to know about the stored procedure returns a message with this line (last line in the SP) SELECT @MessageCode AS MessageCode, @MessageText AS MessageText I need to retrieve both the @MessageCode and the @MessageText values in my application. In classic ASP I retrieved these values by executing the SP with a MyRecordset.Open() and accessing them as normal fields in the recordset. In ASP.NET I have specified the SP as the InsertCommand for the SqlDataSource control. I have supplied all the parameters required by my SP and told the SqlDataSource that the insert command is a “StoredProcedure�. Everything actuly works just fine. The items are added to the order and the business logic in the SP works fine. I just have no way of telling the user that something important has happened. Does anyone know how I can pickup these values so that I can show them to my users? Bassicly if @MessageCode <> 0 I want to show @MessageText on the screen.
