Having Difficulty With FormView, Stored Procedures And Insert Parameter
Feb 17, 2005
I setup the databse and Visual Web Developer to use
stored procedures when the insert command is used. The
database also uses the field UserName that I pass using a
SessionParameter within the InserParameter block from the
Membership.GetUser.Username from the aspnet_ tables.
My difficulty is when using the "Formview" as the body to
with which to insert (I wanted the design versatility of
FormView) I get an error: "system.formatExpression: Input
string was not in a correct format" when I leave the
textboxes empty and invoke the insert command. I first
assumed that the bound textboxes are not being converted
correctly when left blank, so I used the
ConvertEmptyStringToNull=True, with no success. I then
coupled that with the Type=[correct format] still with
the same error. Alas, my final attempt was to set
defaults in the "ALTER PROCEDURE" within the stored
procedure itself.
Any help would be most appreciated. Thanks.
.
View 2 Replies
ADVERTISEMENT
May 3, 2007
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
End Sub Thanks
View 9 Replies
View Related
May 23, 2007
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
View 3 Replies
View Related
Jun 3, 2007
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...
View 5 Replies
View Related
Jun 20, 2006
Hello,
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.
Thanks
View 5 Replies
View Related
Apr 22, 2001
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!!!
View 1 Replies
View Related
Jun 1, 2006
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?
It's windows application.
View 3 Replies
View Related
Mar 9, 2001
How can a Stored Procedure use a variable table name and column name ?
The statement :-
SELECT @columnname FROM @tablename
gives error "Line 5: Incorrect syntax near '@tablename'."
I am passing the parameters 'tablename' and 'columnname' into the stored procedure, (in order to select a variable column from a variable table).
If I hard-code the tablename, then I get a column of output with just the name of the column I was trying to list, e.g. surname,
i.e. SELECT @columnname FROM employeetable gives :-
surname
surname
surname
surname
surname
surname
How can I get the procedure to accept a variable table name and column name ?
I thought one way to do it would be to use the 'EXEC' statement :-
SELECT @sql = 'SELECT '+ @columnname +' FROM '+@tablename
EXEC(@sql)
but this gives error : "Incorrect syntax near the keyword 'FROM' "
although the following table works :-
SELECT @sql = 'SELECT surname FROM '+@tablename
EXEC(@sql)
The problem seems to be mainly with variable column names.
Also, we don't really want to use the EXEC statement because it will not be compiled and will be slower. (Does anyone know by how much slower ?)
Any advice would be appreciated.
Kind regards,
Ian.
(ian.mitchell@sds.no)
View 1 Replies
View Related
Dec 17, 2007
Hi ALL,
I have a Execute SQL Task to execute a stored procedure. It has no input and output parameters.
The stored procedure is defined as follows:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE PROCEDURE [dbo].[SetSLATimePriority]
AS
BEGIN
DECLARE @PriorityID tinyint,@MAXPriorityID tinyint
DECLARE @Priority NVARCHAR(MAX), @SLATime int
SET @PriorityID=1
SET @MAXPriorityID=0
SELECT @MAXPriorityID=MAX([Priority ID]) FROM [MTD Dashboard].[dbo].[Priority]
SET NOCOUNT ON;
WHILE @PriorityID<=@MAXPriorityID
BEGIN
SELECT @Priority= [Priority] FROM [MTD Dashboard].[dbo].[Priority]
WHERE [Priority ID]=@PriorityID
SELECT @SLATime= [SLA Time in hours] FROM [MTD Dashboard].[dbo].[Priority]
WHERE [Priority ID]=@PriorityID
UPDATE [MTD Dashboard].[dbo].[Remedy Dump-Filtered]
SET [SLA Time] = @SLATime WHERE [Priority] like @Priority
SET @PriorityID=@PriorityID+1
END
END
The Properties of Execute SQL Task are set as follows:
Result Set: None
Connection Type: OLEDB
SQL Source Type: Direct Input
SQL Statement: EXEC ? = [dbo].[SetSLATimePriority]
IsQueryStoredProcedure: True
ByPassPrepare: False
Parameter Mapping:
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?
View 3 Replies
View Related
Mar 19, 2007
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
View 1 Replies
View Related
Apr 22, 2008
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
View 1 Replies
View Related
Jun 20, 2007
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)
)
ASinsert into customer(customerid ,fname ,lname ,companyname, email , address ,city,zip,country,phone,state)
values(@customerid ,@fname ,@lname ,@companyname,@email,@address,@city,@zip,@country,@phone,@state)
RETURN
CODE IN C# IS
SqlConnection cn = new SqlConnection(Session["conn"].ToString());
cn.Open();SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;cmd.CommandType = CommandType.StoredProcedure;cmd.CommandText = "sp_addcustomer";
//SqlParameter p1, p2, p3, p4, p5, p6, p7, p8, p9, p10,p11;cmd.Parameters.Add(new SqlParameter("@customerid",SqlDbType.Int));
cmd.Parameters["@customerid"].Value =CustomerID.Text;cmd.Parameters.Add(new SqlParameter("@fname", SqlDbType.Char));
cmd.Parameters["@fname"].Value = fname.Text;cmd.Parameters.Add(new SqlParameter("@lname", SqlDbType.Char));
cmd.Parameters["@lname"].Value = lname.Text;cmd.Parameters.Add(new SqlParameter("@companyname", SqlDbType.Char));
cmd.Parameters["@companyname"].Value = companyname.Text;cmd.Parameters.Add(new SqlParameter("@email", SqlDbType.NVarChar));
cmd.Parameters["@email"].Value = email.Text;cmd.Parameters.Add(new SqlParameter("@address", SqlDbType.Char));
cmd.Parameters["@address"].Value = address.Text;cmd.Parameters.Add(new SqlParameter("@city", SqlDbType.Char));
cmd.Parameters["@city"].Value = city.Text;cmd.Parameters.Add(new SqlParameter("@zip", SqlDbType.NVarChar));
cmd.Parameters["@zip"].Value = zip.Text;cmd.Parameters.Add(new SqlParameter("@country", SqlDbType.Char));
cmd.Parameters["@country"].Value = Country.Text;cmd.Parameters.Add(new SqlParameter("@phone", SqlDbType.NVarChar));
cmd.Parameters["@phone"].Value = Phone.Text;cmd.Parameters.Add(new SqlParameter("@state", SqlDbType.Char));cmd.Parameters["@state"].Value = state.Text;
cmd.ExecuteNonQuery();
cn.Close();
Response.Redirect("main.aspx?st=Employee added successfully");
View 4 Replies
View Related
Jun 21, 2007
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:
ALTER PROCEDURE [dbo].aspnet_Users_CreateUser
@ApplicationId uniqueidentifier,
@UserName nvarchar(256),
@IsUserAnonymous bit,
@LastActivityDate DATETIME,
@Fname varchar(50) OUTPUT,
@Lname nvarchar(50) OUTPUT,
@Address nvarchar(50) OUTPUT,
@City varchar(50) OUTPUT,
@State nchar(2) OUTPUT,
@Zip nchar(5) OUTPUT,
@RefNum nchar(10) OUTPUT,@UserId uniqueidentifier OUTPUTASBEGIN IF( @UserId IS NULL ) SELECT @UserId = NEWID() ELSE BEGIN IF( EXISTS( SELECT UserId FROM dbo.aspnet_Users WHERE @UserId = UserId ) ) RETURN -1 ENDINSERT dbo.aspnet_Users (ApplicationId, UserId, UserName, LoweredUserName, IsAnonymous, LastActivityDate, Fname, Lname, Address, City, State, Zip, RefNum) VALUES (@ApplicationId, @UserId, @UserName, LOWER(@UserName), @IsUserAnonymous, @LastActivityDate, @Fname, @Lname, @Address, @City, @State, @Zip, @RefNum) RETURN 0END
Thanks in advance
View 3 Replies
View Related
Oct 30, 2005
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
View 7 Replies
View Related
Apr 28, 2007
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),'.','')
EXEC ('INSERT INTO dbo.tbl_ProjectNumber' + @Dept + '(DeptCode) VALUES(' + @Dept + ')')
EXEC GetProjID @Dept, @ProjID OUTPUT
INSERT INTO dbo.tbl_Project (ProjID, ProjectTitle, ProjectDetails, ProjectManagerID, RequestedBy, DateRequested, DueDate, ProjectStatusID)
VALUES (@ProjID, @ProjectTitle, @ProjectDetails, @ProjectManagerID, @RequestedBy, @DateRequested, @DueDate, @ProjectStatusID);
COMMIT TRANSACTION
---------------------------------------------------------------------
USE [DBName]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetProjID] (@DeptCode varchar(20), @ProjID varchar(50) OUTPUT)
AS
BEGIN
SET NOCOUNT ON
DECLARE @ProjectNumber varchar(100), @Table varchar(100)
SET @ProjectNumber = 'ProjectNumber' + REPLACE(CONVERT(char,@DeptCode),'.','')
SET @Table = 'dbo.tbl_ProjectNumber' + REPLACE(CONVERT(char,@DeptCode),'.','')
EXEC ( 'SELECT @ProjID = MAX('+@ProjectNumber + ') FROM ' + @Table )
SET @ProjID = @ProjID + '-' + @DeptCode
END When I run the AddToProject sp using the following values: USE [DBName]
GO
DECLARE@return_value int
EXEC@return_value = [dbo].[AddToProject]
@DeptCode = N'BAT',
@ProjectTitle = N'Some Title',
@ProjectDetails = N'Some Details',
@ProjectManagerID = 3,
@RequestedBy = N'Me',
@DateRequested = N'04/27/2007',
@DueDate = N'05/04/2007',
@ProjectStatusID = 4
SELECT'Return Value' = @return_value
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.
View 13 Replies
View Related
Aug 10, 2006
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
View 4 Replies
View Related
Jul 23, 2014
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.
View 4 Replies
View Related
Apr 28, 2007
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:
View 5 Replies
View Related
Aug 28, 2007
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)
View 2 Replies
View Related
Jul 31, 2007
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.
View 3 Replies
View Related
Sep 14, 2007
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..
View 3 Replies
View Related
Aug 28, 2002
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
RETURN @sequence_id
END
View 1 Replies
View Related
Dec 7, 2005
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
View 2 Replies
View Related
Mar 21, 2008
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!!!
View 4 Replies
View Related
Mar 28, 2008
Hi,
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
Thanks
David
CREATE PROCEDURE UserAssign_Location
@UserID Int, @AreaID Int
AS
BEGIN
SET NOCOUNT ON;
SELECT * From Locations Where EntryID = @AreaID
INSERT INTO Members_Locations (UserID, Location1, Location2, Location3, Location4, Active)
VALUES (@UserID, 'AREA1', 'AREA2', 'AREA3', 'AREA4', 1)
END
GO
View 1 Replies
View Related
Apr 6, 2007
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.
View 1 Replies
View Related
Dec 2, 2005
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.
View 4 Replies
View Related
May 25, 2006
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!
View 1 Replies
View Related
Jul 31, 2006
First of all hi for all,
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
@type nvarchar(15), @talepbrmno int, @birimisteksayi bigint, @birimistektarih datetime,
@aciklama nvarchar(50), @onay int, @seviye int, @talepformno bigint, @taleptarih datetime
AS
BEGIN
SET NOCOUNT ON;
exec ('INSERT INTO [BelediyeWork].[dbo].['+@type+'TalepFormu]
([TalepBirimNo], [BirimistekSayısı], [BirimistekTarihi], [Acıklama], [Onay]
,[Seviye], [TalepFormNo], [TalepTarih])
VALUES ('+@talepbrmno+','+@birimisteksayi+','+@birimistektarih+','+@aciklama+'
,'+@onay+','+@seviye+','+@talepformno+','+@taleptarih+') ')
END
View 12 Replies
View Related
Mar 2, 2007
Hello everyone.
I need a stored procedure that excecutes a INSERT sentence.
That's easy. Now, what I need is to return a the key value of the just inserted record.
Someone does know how to do this?
View 5 Replies
View Related
Apr 3, 2015
Is there a way in SQL server that can generate stored procedures for select, insert, update, delete on certain tables?
View 4 Replies
View Related
Mar 3, 2008
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.
View 10 Replies
View Related
