Stored Procdure To Update Two Tables While In Detailsview

Feb 21, 2008

I am using a stored procedure to update data while in detailsview.
I am having a rough time. Dont know why this wont work
thanks a lot
heres my code
ALTER PROCEDURE UpdateCustomerInfo
    ( @CustomerID as INT,
 @LName as varchar(50),
 @FName as varchar(50),
 @Dept as varchar(50)=NULL,
 @PhoneType as varchar(50)=NULL,
 @OfficeType as varchar(50) = NULL,
 @Ext as Int=NULL,
 @BridgeExt as int=NULL,
 @Workstation as varchar(50)=NULL,
 @OldPrinters as varchar(50)=NULL,
 @NewPrinters as varchar(50)=NULL,
 @PhoneImg as varchar(50)=NULL,
 @OfficeImg as varchar(50)=NULL,
 @Complete as Bit=NULL,
 @FaxModel as varchar(50)=NULL,
 @FaxNumber as varchar(50)=NULL,
 @ConfPhone as varchar(50)=NULL,
 @ConfExt as varchar(50)=NULL,
 @ReutersDacsId as Varchar(50)=NULL,
 @Bloomberg as Varchar(50)=NULL,
 @NewFloor as Int=NULL,
 @OldFloor as Int=NULL
 )

AS



    UPDATE CustomerInfo SET LName = @LName, FName = @FName, Dept = @Dept, PhoneType = @PhoneType, Ext = @Ext, BridgeExt = @BridgeExt, Workstation = @Workstation, OldPrinters = @OldPrinters, NewPrinters = @NewPrinters, OfficeImg = @OfficeImg, PhoneImg = @PhoneImg, Complete = @Complete Where CustomerId = @CustomerId
   

    UPDATE Extras SET CustomerID = @CustomerID, FaxModel=@FaxModel, FaxNumber=@FaxNumber, ConfPhone=@ConfPhone, ConfExt=@ConfExt, ReutersDacsId=@ReutersDacsId, Bloomberg=@Bloomberg where CustomerID = @CustomerID
   
    RETURN
 

View 2 Replies


ADVERTISEMENT

Please Help!!! Stored Procdure.

Mar 19, 2002

Whenever I run this DTS package that has a SQLTask, I get the following error message: "Maximum stored procedure nesting level exceeded(Limit 32)"

What does this mean? My query was working fine, but now I'm recieving this error message. I won't complet my required task. Is there any way to change the nesting level to be unlimited. I am totally confused. If you can please help. Thanks again.

Dre

View 1 Replies View Related

Update Detailsview

Dec 18, 2007

Hi,I am using detailsview to update my database and got a question regarding datetime format. Here is part of my code:
<asp:TemplateField HeaderText="Project Deadline">
<EditItemTemplate>
<asp:TextBox ID="txtDeadline_edit" runat="server" Text='<%# Bind("pro_app_deadline") %>'></asp:TextBox>
<asp:CompareValidator ID="txtDeadlineedit_validator" runat="server" ErrorMessage="Not a valid date format" ControlToValidate="txtDeadline_edit" Operator="DataTypeCheck" Type="Date"></asp:CompareValidator>
</EditItemTemplate>
<ItemTemplate><asp:Label ID="txtDeadline" runat="server" Text='<%# Eval("pro_app_deadline") %>'></asp:Label>
</ItemTemplate></asp:TemplateField>
 Since the format of my parameter 'pro_app_deadline' is datetime, it get stored as YYYY-MM-DD hh:mm:ss in datebase. If I want to validate user's input like above way, it always fails since hh:mm:ss append to YYYY-MM-DD. My question is, is there some way I can avoid hh:mm:ss?
Thanks a lot!

View 2 Replies View Related

DetailsView Update With Subquery

Jun 9, 2007

Hi everybody,I have a little Problem with an update and Detailsview.I have 2 table (tblUser - uid,uName,rId) and (tblRole - rId, rDescription) In my update I don't want to use the roles id, I want to use the roles Description.Via sql I do a update the role to Admin like that UPDATE tblUser
SET rId =
(SELECT rId
FROM dbo.tblRoles
WHERE (rDescription = 'Admin')) But I don't get it to work for the detailsview update   

View 4 Replies View Related

Problem With UPDATE With Detailsview

Jul 23, 2007

i get  Incorrect syntax near 'nvarchar'.Must declare the scalar variable "@BookingID".



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: Incorrect syntax near 'nvarchar'.Must declare the scalar variable "@BookingID".

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): Incorrect syntax near 'nvarchar'.Must declare the scalar variable "@BookingID".] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +857354 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734966 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188 ...i am using the Edit feature of the detailsview, seems like the datatype is wrong? ID are int (in my DB, BookingID is int), and also its readonly it should not be updating in the first place right? --------------------------------Any way to debug whats in "new" values? ItemUpdating? how to do it? 

View 3 Replies View Related

Update In DetailsView Not Working

Jun 9, 2008

Hi,
Can anyone tell me why my Update attempts are not working? Here is my code:
<body>
<form id="form1" runat="server">
<div>
 <asp:DetailsView ID="DetailsView1" runat="server" Height="50px" Width="125px"
AutoGenerateEditButton="True" AutoGenerateRows="False" DataSourceID="SqlDataSource1"
DefaultMode="Edit">
<Fields>
<asp:BoundField DataField="City" HeaderText="City" SortExpression="City" />
</Fields>
</asp:DetailsView><asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionStringListings %>"
SelectCommand="SELECT [City] FROM [Listings]"
UpdateCommand="UPDATE [Listings] Set [City]=@City WHERE [ListingID]=@ListingGuid ">
<UpdateParameters>
<asp:Parameter Name="City" />
<asp:Parameter Name ="ListingGuid" />
</UpdateParameters>
</asp:SqlDataSource>
 
<asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>
<asp:Label ID="Label2" runat="server" Text="Label"></asp:Label>
</div></form>
</body>
And here is my code behindProtected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load ListingGuid = Request.QueryString("GUID")
End SubProtected Sub DetailsView1_ItemUpdated(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewUpdatedEventArgs) Handles DetailsView1.ItemUpdated
Label1.Text = "updated"
Label2.Text = ListingGuidEnd Sub
Please let me know what I am doing wrong? 
 

View 3 Replies View Related

Pass Variable To Stored Procdure

Apr 4, 2007

I have the following stroed procedure and need someone to tell me what i am doing wrong. First off i am passing the value of sName from ASP to my sql stored procedure.

the following will work if i do (select distinct * )


Code:

SQL:
CREATE PROCEDURE Get_codes
@sName varchar(255)

AS

Select DISTINCT @sName
From CheckDetail
Where @sName is not Null and @sName <> ''
/*Order by @sName*/

GO

View 1 Replies View Related

Can't Update A Record Using A DetailsView And SqlDataSource

Feb 13, 2007

Hi,I'm trying to create a registration page that I've divided into multiple pages (first page for basic details, next page for address, etc.). I insert the record in the first page, and update it in the other pages. I pass the newly created ID to the other pages using the Page.PreviousPage property.  In the second page, I have the SqlDataSource configured as "SELECT * FROM [Table] WHERE ID = ?", and the UpdateCommand is "UPDATE .... WHERE ID = ?".  In Page_Load, I am updating the SelectCommand to "SELECT ... WHERE ID = " & intID, and the UpdateCommand similarly. The I do a dtlsvw.Databind()But when I go to the next page (the newly created ID is being passed properly), the update doesn't do anything. The new record doesn't contain the values in the detailsview. Can somebody help me out? Thanks,Wild Thing 

View 2 Replies View Related

Multiple Return Values From Stored Procdure.

Feb 1, 2007

I have a table that contains 5 columns (VarChar); where column(0) is a unique ID. Using the unique ID I would like to get the other 4 columns return to me via a stored procedure. Is it possible to have a sproc that has one input var and 4 output?

View 4 Replies View Related

Calling Linked Server Stored Procdure From Another

Oct 27, 2007

Hi Sir,

I want to call one linked server stored procedure from another stored procedure.

--BATCH FOR LINKED SERVER SETUP

SET @IBatchNum = 2


EXEC PR_Linked_Server

@VServer = 'Linked_Amisys'
,@VSrvProduct = ''
,@VProvider = 'SQLOLEDB'
,@VDataSrc = 'DTCORPMXDV9'
,@VCatalog = 'pubs'
,@VCurrentUsr = 'CORPajohri'
,@VServerUsr = 'dba'
,@VServerPwd = 'dba'

END TRY

The linked server was not created When I executed the query. However, I was able to create the linked server when I executed this query(batch 2) alone.
Is there any limitation of SP which I am not aware of?

View 1 Replies View Related

Insert Works, But Delete And Update Don't In A DetailsView Control

Mar 28, 2008

I have 2 Gridviews and a DetailsView for each GridView. The first Gridview and DetailsView work fine and I can Insert, Delete and Update the DetailsView just fine.  However the second Gridview/DetailsView will only let me Insert but not Delete or Update.  When I click on the "Delete" button it just ignores me.  If I do an "Edit", when I try to click on the "Update" button it is ignored again and I have to click on "Cancel".  I don't get any error messages...
 
Anyone have an idea what might be wrong?

View 4 Replies View Related

Must Declare The Scalar Variable @CategoryID. Error In Stored Procdure.

Dec 12, 2007

Hi People, i'm having some trouble with a stored proceddure used for an updat from a Formview.
Error message i'm getting is Must declare the scalar variable "@CategoryID".
I can't seem to work out why this is occuring and not allowing my proc to run properly. If anyone could help me that would be great :-)
Here is the whole store procedure.
 ALTER PROCEDURE dbo.DeluxeGuideAdmin_Update
@ID int,@CategoryID varchar(10),
@RegionID int,@CompanyName nvarchar(25),
@Email nvarchar(25),@PDFFront nvarchar(50),
@PDFBack nvarchar(50),@ThumbFront nvarchar(50),
@ThumbBack nvarchar(50),@Notes nvarchar(max)
 
AS
DECLARE @SQL varchar(1000)
SET @SQL = 'UPDATE DeluxeGuide SET CategoryID = @CategoryID, RegionID = @RegionID, CompanyName = @CompanyName, Email = @Email, Notes = @Notes'IF (@PDFFront IS NOT NULL) OR (@PDFFront != '')
SET @SQL = @SQL + ', PDFFront = @PDFFront'IF (@PDFBack IS NOT NULL) OR (@PDFBack != '')
SET @SQL = @SQL + ', PDFBack = @PDFBack'IF (@ThumbFront IS NOT NULL) OR (@ThumbFront != '')
SET @SQL = @SQL + ', ThumbFront = @ThumbFront'IF (@ThumbBack IS NOT NULL) OR (@ThumbBack != '')
SET @SQL = @SQL + ', ThumbBack = @ThumbBack'
SET @SQL = @SQL + ' WHERE (ID = @ID)'
Print '@SQL = ' + @SQLEXEC(@SQL)
 
RETURN

View 14 Replies View Related

Linked Server Exec Stored Procdure Results In Transaction Context In Use By Another Session.

Nov 3, 2005

I am using sql 2005 beta

View 23 Replies View Related

SQLDataSource, Stored Procedure, DetailsView

Jul 30, 2007

For some reason I can't make the stars align.  Could someone spot the problem here?  I'm looking to select the Identity of my last inserted record in the code behind my DetailsView.  Here are the relavent bits:
Stored Procedure:ALTER PROCEDURE usp_EW_INSERTMajor
@StartDate datetime, @Finishdate datetime, @ProjectName nvarchar(1000), @WorkCell nvarchar(1000),
@JobName nvarchar(1000), @PartName nvarchar(1000), @StatusID int, @ResponsibleID int, @FacilityID int

AS
Set nocount on

DECLARE @ProjectID int
/*This saves the bits to the Project table*/
INSERT INTO EW_Project (ProjectTypeID,FacilityID,StartDate,FinishDate,Status,EmployeeID)
VALUES(1,@FacilityID,@StartDate,@FinishDate,@StatusID,@ResponsibleID)


/*This saves the bits to the Major table*/



SET @ProjectID = SCOPE_IDENTITY()
INSERT INTO EW_MajorBasic(ProjectID,ProjectName,WorkCell,JobName,PartName)
VALUES (@ProjectID,@Projectname,@WorkCell,@JobName,@PartName)

Set nocount off

SELECT @ProjectID as ProjectID
 
SQLDataSource<asp:SqlDataSource ID="ProjectData" runat="server" ConnectionString="<%$ ConnectionStrings:myConnectionString %>"
SelectCommand="usp_EW_GETMajorBasic"
SelectCommandType="StoredProcedure"
UpdateCommand="usp_EW_UPDATEMajorBasic"
InsertCommand="usp_EW_INSERTMajor"
InsertCommandType="StoredProcedure"
UpdateCommandType="StoredProcedure"
DeleteCommand="DELETE FROM EW_Project WHERE ProjectID = @ProjectID"
>
<UpdateParameters>
<asp:Parameter Name="ProjectID" Type="Int32" />
<asp:Parameter Name="StartDate" Type="DateTime" />
<asp:Parameter Name="Finishdate" Type="DateTime" />
<asp:Parameter Name="ProjectName" Type="String" />
<asp:Parameter Name="WorkCell" Type="String" />
<asp:Parameter Name="JobName" Type="String" />
<asp:Parameter Name="PartName" Type="String" />
<asp:Parameter Name="StatusID" Type="Int32" />
<asp:Parameter Name="ResponsibleID" Type="Int32" />
<asp:CookieParameter CookieName="EW_FacilityID" Name="FacilityID" />
</UpdateParameters>
<InsertParameters>
<asp:Parameter Name="StartDate" Type="DateTime" />
<asp:Parameter Name="Finishdate" Type="DateTime" />
<asp:Parameter Name="ProjectName" Type="String" />
<asp:Parameter Name="WorkCell" Type="String" />
<asp:Parameter Name="JobName" Type="String" />
<asp:Parameter Name="PartName" Type="String" />
<asp:Parameter Name="StatusID" Type="Int32" />
<asp:Parameter Name="ResponsibleID" Type="Int32" />
<asp:CookieParameter CookieName="EW_FacilityID" Name="FacilityID" />
</InsertParameters>
<SelectParameters>
<asp:Parameter Name="ProjectID" Type="Int32" />
</SelectParameters>
<DeleteParameters>
<asp:Parameter Name="ProjectID" />
</DeleteParameters>
</asp:SqlDataSource>
 
DetailsView<asp:DetailsView
ID="DetailsView1"
runat="server"
AutoGenerateRows="False"
DataKeyNames="ProjectID"
DataSourceID="ProjectData"
SkinID="SimpleDetailsView"
OnItemDeleted="ProjectData_Deleted"
OnItemInserted="DetailsView1_ItemInserted">  
 
Code-Behind: Sub DetailsView1_ItemInserted(ByVal sender As Object, ByVal e As DetailsViewInsertedEventArgs) Handles DetailsView1.ItemInserted
Dim newID As Integer = e.Values("ProjectID")
testlabel.Text = newID
End Sub
 
 
To be clear, everything is working.  When I come to this page with an ID, the records display fine (Select works).  The Update and Delete work just fine.  The Insert works fine too.  It's just that the ItemInserted part does not want to grab the ProjectID.  Specifically, testlabel displays a Zero. 

View 2 Replies View Related

Master-Detail W/Gridview-DetailsView Stored Procedure Problem

Oct 26, 2007

 I am attempting to setup a Master-Details with GridView/DetailsView but I can't seem to find any information on using a stored procedure that requires parameters with the SqlDataSource control.  SelectCommandType specifies that you are using a stored proc.  SelectCommand specifies the name of the proc, but I haven't found any information on how to pass a parameter to the stored procedure.Is it even possible or do I have to forget about using the DetailsView control altogether?

View 4 Replies View Related

Update Values In Two Tables Via Stored Procedure

Apr 19, 2014

I have two tables and I need to update values in them via a stored procedure. Tried too much to update but some times it update the first table only, others the second or even fail due to cannot allow duplicates. Also when it updates the WHOLE data in the table becomes the same as the new updated ones. I've now reached to this error after all these lines of codes

Cannot insert the value NULL into column 'Emp_ID',table 'DatePics'; column does not allow nulls. UPDATE fails.The statement has been terminated

Here is the SQL code :

ALTER procedure [dbo].[UpdateEmp]
@EmpName nvarchar(100),
@Nationality nvarchar(30),
@Passport nvarchar(20),
@ContractDate date,
@HealthDate date

[Code] ......

View 4 Replies View Related

Stored Procedure - INSERT INTO Or UPDATE - INNER JOIN TWO TABLES

Jun 13, 2008

Hi all,can somebody help to write this stored procedure  Table1                   Table2LogID                    MigIDUserMove              LogIDUserNew               Domain                            User The two tables are inner join with LogID.If in Table2 LogID=NULL then create new dataset in Table1 (INSERT)and then Update LogID in Table2IF in Table2 LogID= 2 (or something else) then update the dataset in Table1 with the same LogID Thanks

View 1 Replies View Related

How To Manage Stored Procedure Transaction Involving Update In Several Tables

Jul 22, 2004

I am running a vba procedure ( adp file ) that executes successively 5 stored procedures . however it happens that the execution breaks at the middle of the code thus giving a situation where only 2 tables among 5 are updated.

Is it any solution to rollback transactions update already done before
the code breaks due to error ?

I was thinking about combining all stored proc on a big one and use
Begin transaction - commit transaction and rollback transaction ... however i am not sure wheter updates involving several tables can be handled on one transaction.

Any advise highly appreciated !

View 2 Replies View Related

SQL 2012 :: Generate Stored Procedures For Select / Insert / Update / Delete On Certain Tables?

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

Listing Procdure/trigger SQL Code

Aug 2, 2006

I would like to create a script that lists out all of the SQL code for the procedures and triggers I have created in a database.

What I want to do is save the code for each procedure/trigger as a *.SQL file without having to open up each one in Enterprise Manager and save them individually.

Listing the names is easy by looking in sysobjects, but is the SQL code stored in a system table anywhere?

Does anybody have any alternative approach to this problem?

Thanks in advance,

Mark

View 1 Replies View Related

Can I Call Tigger In Store Procdure And Vice Versa??(urgent)

Jun 19, 2006

Can i call tigger in store procdure and vice versa??
 
if yes how it ?????????

View 2 Replies View Related

Trigger To Update One Record On Update Of All The Tables Of Database

Jan 3, 2005

hi!

I have a big problem. If anyone can help.

I want to retrieve the last update time of database. Whenever any update or delete or insert happend to my database i want to store and retrieve that time.

I know one way is that i have to make a table that will store the datetime field and system trigger / trigger that can update this field record whenever any update insert or deletion occur in database.

But i don't know exactly how to do the coding for this?

Is there any other way to do this?

can DBCC help to retrieve this info?

Please advise me how to do this.

Thanks in advance.

Vaibhav

View 10 Replies View Related

DetailsView Control (Please Help!!!!)

May 14, 2006

Can someone please tell me what I am doing wrong...
I have a simple webform that has contains a GridView and a DetailsView. Once the GridView is populated the user simply selects a record from the list.  The DetailsView is them populated with all of the data from the selected record.
The DetailsView is bound to a SQL DataSource/CustomerData. I have made sure that the DV control has the Edit/Update Command listed.  The user can click the Edit button and successfully edit the fields but when the user clicks the "Update" button I am getting the following message.
Updating is not supported by data source 'CustomerData' unless UpdateCommand is specified
Why isnt the control handling the Update.
Many Thanks!!!!
T
 
 

View 4 Replies View Related

SqlDataSource && DetailsView UpdateCommand

Mar 6, 2006

Hi
I have a Page with a DetailsView and SqlDataSource. When Editing the DetailsView the Sql;DataSource UpdateCommand does not seem to pick up the @parameters of the form fields. I have used OnItemUpdating to view all Keys/OldValues/NewValues passed into the UpdateCommand but nothing seems to be picked up.
When I place a value into UpdateCommand it updates correctly but does not when I use the form. At this stage I have simplified the code down to typical Master-Details Pages (Separate) using a QueryString to filter the SelectCommand. But nothing seems to work.
Please help! I have wasted so much time trying to resolve this and I am on deadline and need this to work.
Thanks
Dave

View 3 Replies View Related

Edit Function In DetailsView

May 1, 2006

Hello
Am developing a web site using ASP.net 2.0. I have page to manage the employees information in term of viewing, inserting and updating. The problem is that the updating part doesn't work and it didn't give me any error messages.
My code is as following:
<%@ Page Language="VB" AutoEventWireup="false"%>
 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
 
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Untitled Page</title>
</head>
<script runat="server" >
   
    Protected Sub DetailsView1_ItemInserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewInsertedEventArgs)
   
        If (Not e.Exception Is Nothing) Then
 
            ErrorMessageLabel.Text = "An error occured while entering this record.  Please verify you have entered data in the correct format."
            e.ExceptionHandled = True
        End If
        GridView1.DataBind()
    End Sub
 
    Protected Sub DetailsView1_ItemUpdated(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewUpdatedEventArgs)
   
        GridView1.DataBind()
    End Sub
 
    Protected Sub DropDownList1_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs)
    
        DetailsView1.ChangeMode(DetailsViewMode.ReadOnly)
    End Sub
 
    Protected Sub GridView1_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs)
   
        DetailsView1.ChangeMode(DetailsViewMode.ReadOnly)
    End Sub
 
    Protected Sub GridView1_PageIndexChanged(ByVal sender As Object, ByVal e As EventArgs)
   
        DetailsView1.ChangeMode(DetailsViewMode.ReadOnly)
    End Sub
 
    Protected Sub GridView1_Sorted(ByVal sender As Object, ByVal e As System.EventArgs)
        DetailsView1.ChangeMode(DetailsViewMode.ReadOnly)
    End Sub
   
</script>
<body dir="rtl">
    <form id="form1" runat="server">
        <br />
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:warehouse DBConnectionString2 %>"
            ProviderName="<%$ ConnectionStrings:warehouse DBConnectionString2.ProviderName %>"
            SelectCommand="SELECT DISTINCT [dep_Code], [dep_Name], [BRN_Code] FROM [Departments]"></asp:SqlDataSource>
       
        <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:warehouse DBConnectionString2 %>"
            ProviderName="<%$ ConnectionStrings:warehouse DBConnectionString2.ProviderName %>"
            SelectCommand="SELECT DISTINCT [ID], [Name],[Position], [DEPT_Code] FROM [Employees]
            where [DEPT_Code]=@dep_Name">
        <SelectParameters>
        <asp:ControlParameter ControlID="DropDownList1" Name="dep_Name" Type="Int32" />
        </SelectParameters>
        </asp:SqlDataSource>
       
        <asp:SqlDataSource ID="SqlDataSource3" runat="server" ConnectionString="<%$ ConnectionStrings:warehouse DBConnectionString2 %>"
            ProviderName="<%$ ConnectionStrings:warehouse DBConnectionString2.ProviderName %>"
            SelectCommand="SELECT DISTINCT [ID], [Authentication], [Name], [Password], [Position], [DEPT_Code], [Contact] FROM [Employees]
            where [ID]=@ID "
           
            UpdateCommand="UPDATE [Employees]
                           SET
                           [ID] = @ID,
                             [Authentication]=@Authentication,
                             [Name]=@Name,
                             [Password]=@Password,
                           [Position]=@Position,
                             [DEPT_Code]=@DEPT_Code,
                             [Contact]=@Contact
                           WHERE [ID] = @ID  "
                           
            InsertCommand="INSERT INTO [Employees]
                   ([ID], [Authentication], [Name], [Password], [Position], [DEPT_Code], [Contact])
            Values (@ID, @Authentication, @Name, @Password, @Position, @DEPT_Code, @Contact) "
            >
        <SelectParameters>
        <asp:ControlParameter ControlID="GridView1" Name="ID" Type="Int32"  PropertyName="SelectedValue" />
        </SelectParameters>
       
        <UpdateParameters>
        <asp:Parameter Name="ID" Type="Int32" />
        <asp:Parameter Name="Authentication" Type="Int16" />
        <asp:Parameter Name="Name" Type="String" />
        <asp:Parameter Name="Password" Type="String" />
        <asp:Parameter Name="Position" Type="String" />
        <asp:Parameter Name="DEPT_Code" Type="Int32" />
        <asp:Parameter Name="Contact" Type="String" />
        </UpdateParameters>
       
        <InsertParameters>
        <asp:Parameter Name="ID" Type="Int32" />
        <asp:Parameter Name="Authentication" Type="Int16" />
        <asp:Parameter Name="Name" Type="String" />
        <asp:Parameter Name="Password" Type="String" />
        <asp:Parameter Name="Position" Type="String" />
        <asp:Parameter Name="DEPT_Code" Type="Int32" />
        <asp:Parameter Name="Contact" Type="String" />
        </InsertParameters>
        </asp:SqlDataSource>
        &nbsp;
        <br />
        <br />
        أختر القسم :&nbsp;
        <asp:DropDownList ID="DropDownList1" DataSourceID="SqlDataSource1" AutoPostBack="True"
      DataTextField="dep_Name" runat="server" DataValueField="dep_Code" OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged" >
        <asp:ListItem></asp:ListItem>
    </asp:DropDownList>
        <table >
            <tr>
                <td style="width: 100px">
                </td>
                <td style="width: 100px">
                </td>
            </tr>
            <tr>
                <td style="width: 100px; height: 154px" valign="top">
                    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="ID"
                        DataSourceID="SqlDataSource2" AllowPaging="True" AllowSorting="True" OnSelectedIndexChanged="GridView1_SelectedIndexChanged" OnPageIndexChanged="GridView1_PageIndexChanged">
                        <Columns>
                            <asp:BoundField DataField="ID" HeaderText="ID" ReadOnly="True" SortExpression="ID" />
                           
                            <asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />
                            <asp:BoundField DataField="Position" HeaderText="Position" SortExpression="Position" />
                            <asp:BoundField DataField="DEPT_Code" HeaderText="DEPT_Code" SortExpression="DEPT_Code" Visible="False" />
                           
                            <asp:CommandField ShowSelectButton="True" />
                        </Columns>
                    </asp:GridView>
                </td>
                <td style="width: 100px; height: 154px" valign="top">
                    <asp:DetailsView ID="DetailsView1" runat="server" AutoGenerateRows="False" DataKeyNames="ID" OnItemUpdated="DetailsView1_ItemUpdated"
                        DataSourceID="SqlDataSource3" Height="50px" Width="125px" AllowPaging="True" OnItemInserted="DetailsView1_ItemInserted">
                        <Fields>
                            <asp:BoundField DataField="ID" HeaderText="ID" ReadOnly="True" SortExpression="ID" />
                            <asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />
                            <asp:BoundField DataField="Password" HeaderText="Password" SortExpression="Password" />
                            <asp:BoundField DataField="Position" HeaderText="Position" SortExpression="Position" />
                            <asp:BoundField DataField="Contact" HeaderText="Contact" SortExpression="Contact" />
                            <asp:TemplateField HeaderText="Department" SortExpression="DEPT_Code" >
                                <ItemTemplate>
                                    <asp:Label ID="Label1" runat="server" Text='<%#Eval("DEPT_Code") %>'></asp:Label>
                                </ItemTemplate>
                                <EditItemTemplate>
                                    <asp:DropDownList ID="DropDownList2" runat="server" DataSourceID="SqlDataSource1"
                                    DataTextField="dep_Name" DataValueField="dep_Code" SelectedValue='<%# Bind("DEPT_Code") %>'>
                                    </asp:DropDownList>
                                </EditItemTemplate>
                                <InsertItemTemplate>
                                <asp:DropDownList ID="DropDownList3" runat="server" DataSourceID="SqlDataSource1"
                                DataTextField="dep_Name" DataValueField="dep_Code" SelectedValue='<%# Bind("DEPT_Code") %>'>
                                </asp:DropDownList>
                                </InsertItemTemplate>
                            </asp:TemplateField>
                            <asp:TemplateField HeaderText="Authentication" SortExpression="Authentication">
                            <ItemTemplate>
                                <asp:Label ID="Label2" runat="server" Text='<%#Eval("Authentication") %>'></asp:Label>
                            </ItemTemplate>
                            <EditItemTemplate>
                                <asp:DropDownList ID="DropDownList4" runat="server" SelectedValue='<%# Bind("Authentication") %>'>
                                <asp:ListItem Text="Admin_User" Value="1" />
                                <asp:ListItem Text="Super_User" Value="2" />
                                <asp:ListItem Text="Normal_User" Value="3" />
                                </asp:DropDownList>
                            </EditItemTemplate>
                            <InsertItemTemplate>
                            <asp:DropDownList ID="DropDownList5" runat="server" SelectedValue='<%# Bind("Authentication") %>'>
                                <asp:ListItem Text="Admin_User" Value="1" />
                                <asp:ListItem Text="Super_User" Value="2" />
                                <asp:ListItem Text="Normal_User" Value="3" />
                                </asp:DropDownList>
                            </InsertItemTemplate>
                            </asp:TemplateField>
                            <asp:CommandField ShowInsertButton="True" />
                            <asp:CommandField ShowEditButton="True" />
                        </Fields>
                    </asp:DetailsView>
                </td>
            </tr>
        </table>
      
       <asp:Label ID="ErrorMessageLabel" EnableViewState="false" runat="server" />
    </form>
</body>
</html>
  

View 4 Replies View Related

Track The Changes To Normalised Tables And Update The Denormalised Tables Depending On The Changes To Normalised Tables

Dec 7, 2006

We have 20 -30 normalized tables in our dartabase . Also we have 4tables where we store the calculated data fron those normalised tables.The Reason we have these 4 denormalised tables is when we try to dothe calcultion on the fly, our site becomes very slow. So We haveprecalculated and stored it in 4 tables.The Process we use to do the precalcultion, will get do thecalculation and and store it in a temp table. It will compare the thetemp with denormalised tables and insert new rows , delte the old oneans update if any changes.This process take about 20 mins - 60mins. Ittakes long time because in this process we first do the calculationregardless of changes and then do a compare to see what are changed andremove if any rows are deleted, and insert new rowsand update thechanges.Now we like to capture the rows/columns changed in the normalisedtables and do only those chages to the denormalised table , which weare hoping will reduce the processing time by atleast 50%WE have upgraded to SQL SERVER 2005.So We like to use the newtechnology for this process.I have to design the a model to capture the changes and updated onlythose changes.I have the list of normalised tables and te columns which will affectthe end results.I thought of using Triggers or OUTPUT clause to capture the changes.Please help me with the any ideas how to design the new process

View 3 Replies View Related

GridViews/DetailsView (only Appear After Control Selection)

May 5, 2008

Hi all,I want to be able to implement what the following site shows in regards to dynamically displaying Data views of various types: http://quickstarts.asp.net/QuickStartv20/aspnet/samples/data/GridViewMasterDetails_vb.aspx I follow everything to the 'T' but for some reason all my data views are showing even before any initial selection is made in the drop down.  How can I have certain grids appear only after a selection from another control has been made?Thanks in advance,Nick  

View 1 Replies View Related

Images Inside Gridview And Detailsview

Mar 20, 2008

I have been stuck on this problem for over a week, and have ripped out about all of my hair, so here it goes.

I am working on a portfolio on a website that administrators are allowed to update, but the public may only view....easy enough? The grid has three parameters; CatID, Descrip, and Pic - as seen below.


<asp:SqlDataSource ID="SqlDataSource2" runat="server"


ConnectionString="<%$ ConnectionStrings:NORTHWNDConnectionString %>"




SelectCommand="SELECT [CatID], [Descrip], [Pic] FROM [Categories]"




UpdateCommand="UPDATE Categories SET Descrip = @Descrip, Pic = @Pic WHERE CatID = @CatID"

DeleteCommand="DELETE FROM Categories WHERE CatID = @CatID"




InsertCommand="INSERT INTO [Categories] ([CatID], [Descrip], [Pic]) VALUES (@CatID, @Descrip, @Pic)" >



<DeleteParameters>


<asp:Parameter Name="CatID" Type="String" />

<asp:parameter Name="Descrip" />

</DeleteParameters>



<UpdateParameters>


<asp:parameter Name="Descrip" />

<asp:parameter Name="Pic" />

<asp:parameter Name="CatID" Type="String" />

</UpdateParameters>



<insertparameters>


<asp:parameter Name="CatID" Type="String" />

<asp:parameter Name="Descrip" Type="String" />

<asp:parameter Name="Pic" Type="String"/>

</insertparameters>

</asp:SqlDataSource>

The first this I am not sure of, is whether Pic should be a String or not.

Basically I am using Detailsview for the administrator to update, and the gridview for the public view.


<asp:DetailsView ID="DetailsView1" runat="server" AllowPaging="True"



AutoGenerateRows="False" DataKeyNames="CatID" HeaderText = "Portfolio"

DataSourceID="SqlDataSource2" Height="50px"

Width="300px" Font-Size = "14px" HeaderStyle-Font-Size="Larger" HeaderStyle-BackColor ="#27437D" HeaderStyle-ForeColor = "White"

BackColor="White" BorderColor="#999999" BorderStyle="None" BorderWidth="1px"

CellPadding="3" GridLines="Vertical">

<footerstyle backcolor="#CCCCCC" forecolor="Black" />

<rowstyle backcolor="#EEEEEE" forecolor="Black" />

<pagerstyle backcolor="#999999" forecolor="Black" horizontalalign="Center" />

<Fields>


<asp:boundfield DataField="CatID" HeaderText="Building/Job Name" ReadOnly="True" SortExpression="CatID">

</asp:boundfield>

<asp:boundfield DataField="Descrip" HeaderText="Description" SortExpression="CompanyName">

</asp:boundfield>

<asp:TemplateField HeaderText="Picture">


<EditItemTemplate>


<asp:TextBox ID="TextBox1" runat="server" Text='<%#Eval("Pic")%>' ></asp:TextBox>

</EditItemTemplate>

<InsertItemTemplate>


<asp:TextBox ID="TextBox1" runat="server" Text='<%#Eval("Pic")%>' ></asp:TextBox>

</InsertItemTemplate>

<ItemTemplate>


<asp:Image ID="Image1" runat="server" ImageUrl='<%# Eval("Pic") %>' />

</ItemTemplate>

</asp:TemplateField>

<asp:commandfield ShowDeleteButton="True" ShowEditButton="True" ShowInsertButton="True">

</asp:commandfield>

</Fields>

<HeaderStyle BackColor="#27437D" Font-Size="Larger" ForeColor="White"></HeaderStyle>

<alternatingrowstyle backcolor="#DCDCDC" />

</asp:DetailsView>

And my Gridview...

<asp:GridView ID= "GridView1" runat="server" DataSourceID="SqlDataSource2" AllowSorting="True" BackColor="White" CellPadding="3" Caption = "Portfolio"


Font-Size ="14px" BorderColor="#999999"BorderStyle="None"BorderWidth="1px"GridLines="Vertical"AutoGenerateColumns="False">

<FooterStyle BackColor="#CCCCCC" ForeColor="Black" />

<RowStyle BackColor="#EEEEEE" Font-Size = "Small" ForeColor="Black" />

<columns>


<asp:boundfield DataField="CatID" HeaderText="Building/Job Name"></asp:boundfield>

<asp:boundfield DataField="Descrip" HeaderText="Description"></asp:boundfield>







<asp:TemplateField HeaderText="Picture">


<EditItemTemplate>


<asp:TextBox ID="TextBox1" runat="server" text='<%# Eval("Pic") %>'

></asp:TextBox>

</EditItemTemplate>

<ItemTemplate>


<asp:Image ID="Image1" runat="server" ImageUrl= '<%# Eval("Pic") %>' />



</ItemTemplate>

</asp:TemplateField>



</columns>

<PagerStyle BackColor="#999999" ForeColor="Black" HorizontalAlign="Center" />

<SelectedRowStyle BackColor="#008A8C" Font-Bold="True" ForeColor="White" />

<HeaderStyle BackColor="#27437D" Font-Bold="True" ForeColor="White" />

<AlternatingRowStyle BackColor="#DCDCDC" />

</asp:GridView>


Now as I was looking around on the web I saw many people converting the path of the image to binary and the casting it to the DB, and likewise retrieving it from the DB as binary and converting it to an image, the problem is, I don't know how to do this.

Any help to ease my troubles is apprechiated greatly.

Edit: Also I am not totally sure what the '<%Eval("Pic") %>' is doing, it was autofilled for me.
Edit again: Another thing, if I replace any of the ImageUrl to an image path on the computer it does fill the space in the gridview/detailsview. However, with both cases I get a Operand type clash: nvarchar is incompatible with image error.

View 1 Replies View Related

Label Showing User.identity.name In Detailsview

Aug 30, 2007

Hi, any suggestions on why my label doesn't show the current user.identity.name when used in a detailsview (header-field or insert-field), when it works just fine placed directly on my page ?
Please help ! (I want to bind myLabelX.Text to a db-table, but it contains no value..)
codebehind  : 1 using System;
2 using System.Data;
3 using System.Configuration;
4 using System.Collections;
5 using System.Web;
6 using System.Web.Security;
7 using System.Web.UI;
8 using System.Web.UI.WebControls;
9 using System.Web.UI.WebControls.WebParts;
10 using System.Web.UI.HtmlControls;
11
12
13 public partial class Test_2 : System.Web.UI.Page
14 {
15
16 protected void Page_Load(object sender, EventArgs e)
17 {
18 if ((!IsPostBack)) myLabelX.Text = User.Identity.Name;
19 }
20
21 }
 

View 3 Replies View Related

Event To Catch A Primary Key Violation In A DetailsView?

Apr 23, 2008

I have a SQLDataSource bound to a DetailsView.  In which event would I catch a Primary Key violation?  Would be an event of the SQLDataSource or the DetailsView?  And which particular event?  A small example would be great.  Thx.

View 1 Replies View Related

Help Send An Personal Email From Database Mail On Row Update-stored PROCEDURE Multi Update

May 27, 2008

hi need help how to send an email from database mail on row update
from stored PROCEDURE multi update
but i need to send a personal email evry employee get an email on row update
like send one after one email


i use FUNCTION i get on this forum to use split from multi update

how to loop for evry update send an single eamil to evry employee ID send one email

i update like this


Code Snippet
:

DECLARE @id nvarchar(1000)
set @id= '16703, 16704, 16757, 16924, 17041, 17077, 17084, 17103, 17129, 17134, 17186, 17190, 17203, 17205, 17289, 17294, 17295, 17296, 17309, 17316, 17317, 17322, 17325, 17337, 17338, 17339, 17348, 17349, 17350, 17357, 17360, 17361, 17362, 17366, 17367, 17370, 17372, 17373, 17374, 17377, 17380, 17382, 17383, 17385, 17386, 17391, 17392, 17393, 17394, 17395, 17396, 17397, 17398, 17400, 17401, 17402, 17407, 17408, 17409, 17410, 17411, 17412, 17413, 17414, 17415, 17417, 17418, 17419, 17420, 17422, 17423, 17424, 17425, 17426, 17427, 17428, 17430, 17431, 17432, 17442, 17443, 17444, 17447, 17448, 17449, 17450, 17451'
UPDATE s SET fld5 = 2
FROM Snha s
JOIN dbo.udf_SplitList(@id, ',') split
ON split.value = s.na
WHERE fld5 = 3

now
how to send an EMAIL for evry ROW update but "personal email" to the employee



Code Snippet
DECLARE @xml NVARCHAR(MAX)DECLARE @body NVARCHAR(MAX)
SET @xml =CAST(( SELECT
FirstName AS 'td','',
LastName AS 'td','' ,
SET @body = @body + @xml +'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@recipients =''
@copy_recipients='www@iec.com',
@body = @body,
@body_format ='HTML',
@subject ='test',
@profile_name ='bob'
END
ELSE
print 'no email today'


TNX

View 2 Replies View Related

Detailsview Control - How To Edit Record In Sql Server Express

Aug 25, 2006

I have null values in some of the fields & when I use EDIT to make changes these changes are not recorded + I receive no error.  

View 1 Replies View Related

Stored Proc Won't Update From C# .NET Code, But Will Update When Testing On Its Own.

Jul 23, 2006

I'm having a strange problem that I can't figure out. I have an SQL stored procedure that updates a small database table. When testing the Stored Procedure from the Server Explorer, it works fine. However, when I run the C# code that's supposed to use it, the data doesn't get saved. The C# code seems to run correctly and the parameters that are passed to the SP seem to be okay. No exceptions are thrown.
The C# code:
   SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["touristsConnectionString"].ConnectionString);
SqlCommand cmd = new SqlCommand("fort_SaveRedirectURL", conn);
cmd.CommandType = CommandType.StoredProcedure;
Label accomIdLabel = (Label)DetailsView1.FindControl("lblID");
int accomId = Convert.ToInt32(accomIdLabel.Text);
cmd.Parameters.Add("@accomId", SqlDbType.Int).Value = accomId;
cmd.Parameters.Add("@path", SqlDbType.VarChar, 250).Value = GeneratePath();
try
{
conn.Open();
cmd.ExecuteNonQuery();
}
catch(Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
 The Stored Procedure:
  ALTER PROCEDURE developers.fort_SaveRedirectURL
(
@accomId int,
@path varchar(250)
)
AS
DECLARE
@enabled bit,
@oldpath varchar(250)

/* Ensure that the accommodation has been enabled */
SELECT @enabled = enabled FROM Experimental_Accommodation
WHERE Experimental_Accommodation.id = @accomId

IF (@enabled = 1)
BEGIN
/* Now check if a path already exists */
SELECT @oldpath = oldpath FROM Experimental_Adpages_Redirect
WHERE Experimental_Adpages_Redirect.accom_id = @accomId

IF @oldpath IS NULL
BEGIN
/* If Path already exists then we should keep the existing URL */
/* Otherwise, we need to insert a new one */
INSERT INTO Experimental_Adpages_Redirect
(oldpath, accom_id)
VALUES (@path,@accomId)
END
END
RETURN 

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved