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
ADVERTISEMENT
Aug 19, 2007
Almost new to ssrs, accessing Oracle's stored procedures to feed the reports (no other choice), working on a master/detail report and badly stuck. This report needs to access 3 stored procedures one for master and two for small tables under it. The master's order ID is fed to the two stored procedures that may or may not pull any thing for certain order ID's
I am approaching it by creating 2 datasets and a page-long list, added desired text boxes and a table in the list, grouped the list on order ID and passing it to tables using default values etc. but keep getting the following message.
"The Group expression for the table €˜table1€™ refers to the field €˜ProductName€™. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope".
The list seems to be associated to one dataset only and any item in it cannot refer to anyother dataset, even if I try to access the table's dataset in its properties (The 'current data set scope' implies that).
(Not sure what will happen when I will try to add the second table and dataset with the same parameter name).
How should I approach this issue. I will appreciate help, if explained in steps but won't mind a good tip.
Thanks.
View 8 Replies
View Related
Mar 25, 2008
Hello,
I'm new to SQL and need help with a query. Not sure if this is the right place.
I have 2 tables, one MASTER and one DETAIL.
The MASTER table has a masterID, name and the DETAIL table has a detailID, masterID, and value columns.
I want to return a populated MASTER table with entries based on the DETAIL.value.
SELECT MASTER.*
FROM MASTER
WHERE DETAIL.value > 3
This is a simplified version of my problem. I can't figure out how to set the relationship between MASTER.masterID and DETAIL.masterID. If I do an INNER JOIN, the number of results are based on the number of DETAIL entries. I only want one entry per MASTER entry.
Hope this makes sense.
How can I do this?
GrkEngineer
View 9 Replies
View Related
Oct 1, 2007
now i want to learn how to make a stored procedure to insert a record to `purchase` table, and many records to `purchase_detail` table with transaction where the some value are passed from vb6 through the parameters. i've made a SP to insert 1 record to `purchase` table n 1 record to `purchase_detail` just for testing, so i set the disc value to 10. it works fine... --------------------------------------------------------------------------------- CREATE PROCEDURE `usp_save_purchase`(xpurch_id VARCHAR(10), xpurch_date VARCHAR(10), xsupp_id VARCHAR(10), xitem_id VARCHAR(10), xqty TINYINT(3), xprice DOUBLE(15,2)) BEGIN START TRANSACTION; INSERT INTO purchase(purch_id,purch_date,supplier_id) VALUES(xpurch_id, xpurch_date, xsupplier_id); INSERT INTO purchase_detail(purch_id,item_id,qty,price,disc) VALUES(xpurch_id, xitem_id, xqty, xprice, 10); COMMIT; END --------------------------------------------------------------------------------- what i need is something like that but i only pass 3 variables (purch_id, purch_date, and supp_id) to SP, and then the SP will insert 1 record of purchase to `purchase` table, and add the purchase items to `purchase_detail` automatically from `purch_temp` table, and use the disc rate based on `supplier_id` and `item_id` from supplier_disc table, which will be looked something like this: --------------------------------------------------------------------------------- CREATE PROCEDURE `usp_save_purchase`(xpurch_id VARCHAR(10), xpurch_date VARCHAR(10), xsupp_id VARCHAR(10)) BEGIN START TRANSACTION; INSERT INTO purchase(purch_id,purch_date,supplier_id) VALUES(xpurch_id, xpurch_date, xsupplier_id); /*start looping here get the disc rate for each items where supp_id = xsupplier_id and item_id = the item_id from purch_temp table, and save it in a local variable (let's say local_disc) INSERT INTO purchase_detail(purch_id,item_id,qty,price,disc) VALUES(xpurch_id, xitem_id, xqty, xprice, local_disc); */ COMMIT; END --------------------------------------------------------------------------------- can anyone help me please? thank you in advance...
View 1 Replies
View Related
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
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
Jan 19, 2007
Can someone help me with this issue? I am trying to update a record using a sp. The db table has an identity column. I seem to have set up everything correctly for Gridview and SqlDataSource but have no clue where my additional, phanton arguments are being generated. If I specify a custom statement rather than the stored procedure in the Data Source configuration wizard I have no problem. But if I use a stored procedure I keep getting the error "Procedure or function <sp name> has too many arguments specified." But thing is, I didn't specify too many parameters, I specified exactly the number of parameters there are. I read through some posts and saw that the gridview datakey fields are automatically passed as parameters, but when I eliminate the ID parameter from the sp, from the SqlDataSource parameters list, or from both (ID is the datakey field for the gridview) and pray that .net somehow knows which record to update -- I still get the error. I'd like a simple solution, please, as I'm really new to this. What is wrong with this picture? Thank you very much for any light you can shed on this.
View 9 Replies
View Related
Oct 23, 2006
Info: (vs 2005, sql server 2005, asp 2.0, C# project)I need to pass a variable from my web form to a stored procedure which should return a dataset to a gridview.My stored proc works fine but it’s not returning properly (or at all). Here is my stored proc:set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[HrsUsed]
-- Add the parameters for the stored procedure here
@idUser INT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT DATENAME(mm, leaveDate) + ' ' + DATENAME(yyyy, leaveDate) AS 'Leave Date', totalV, totalS
FROM tblLeave
WHERE idUser = @idUser
ORDER BY leaveDate DESC
RETURN
END
Here is my code: try
{
SqlConnection cxnHrsUsed = new SqlConnection(ConfigurationManager.ConnectionStrings["cxnLeaveRecords"].ConnectionString);
SqlCommand cmdHrsUsed = new SqlCommand("HrsUsed", cxnHrsUsed);
cmdHrsUsed.CommandType = CommandType.StoredProcedure;
cmdHrsUsed.Parameters.Add("@idUser", SqlDbType.Int).Direction = ParameterDirection.Input;
cmdHoursUsed.Parameters["@idUser"].Value = Session["sessionUserID"];
cmdHrsUsed.Connection.Open();
gvHrsUsed.DataSource = cmdHrsUsed.ExecuteReader();
gvHrsUsed.DataBind();
cmdHrsUsed.Connection.Close();
}
catch (Exception ex)
{
lblStatus.Text = ex.Message;
} I can see the caption of the gridview, but no data below it. Any ideas?
View 3 Replies
View Related
Sep 14, 2007
I am trying to do something that I would think is simple. I have a stored procedure used for deleting a record, and I want to call it from the "Delete" command of a Delete button on a GridView. This incredible simple SP accepts one value, the unique record ID to delete the record like this:CREATE PROCEDURE usp_DeleteBox
/* *******************************************
Delete a record using the Passed ID.
********************************************** */
(
@pID as int = Null
)
AS
DELETE FROM [Boxes]
WHERE ID = @pID
When I configured the data source for the GridView, I selected the "Delete" tab and selected my Stored Procedure from the list. As mentioned on another post I saw here, I set the "DataKeyNames" property of the GridView to my id field (called "ID", naturally).
When I click the Delete button on a row, I get this error message: "Procedure or function usp_DeleteBox has too many arguments specified." If I leave the "DataKeyNames" property empty, it does nothing when I click delete.
Can someone tell me the correct way to configure this? I am sure I am missing something obvious, and I would appreciate any suggestions. Thank you!
View 3 Replies
View Related
Apr 17, 2006
Help!I am trying to fill my datagrid using the SQLDataSource, using a stored procedure.The stored procedure expects a parameter which I can collect via the querystring, or a string. How can I pass the parameter through the SQLDatasSource?My SQLDataSource is SQLData1. I already have:SQLData1.SelectCommandType = SqlDataSourceCommandType.StoredProcedureSQLData1.SelectCommand = "dbo.get_players"Thanks in advance,Karls
View 2 Replies
View Related
Jan 7, 2007
Hello community,I think my problem is easy to solve even though I did not find a solution through different tutorials and help pages. Here it is (select statements are hier simplified):In the gridview "GridView1" I have a master record with person_id, which is the data-key-value. There is also another id-field named task_id (This record comes from a database view which joins the persons- and the tasks- table) SelectCommand="SELECT [id], [person_id], [task_id] FROM [ViewPersonTasks] WHERE ([id] =
@id)"For both fields I want to display details in two different detail-views. One for the person (depending on person_id) and one for the tasks (depending on the task_id).The first one is easy. I declare a details-view for the person data based on a SqlDataSource with a control-parameter like this: SelectCommand="SELECT [person_id], [first_name], [last_name], [birth_date] FROM [TabPersons] WHERE ([person_id] = @person_id)" .... <SelectParameters> <asp:ControlParameter ControlID="GridView1" Name="person_id" PropertyName="SelectedValue" Type="Int32" /> </SelectParameters>But now the problem: how should I declare a parameter @task_id for the task_id, so that the second select statement for the tasks-details-view retrievs the data for the tasks: SelectCommand="SELECT [task_id], [task_name],
[task_date], [task_description] FROM [TabTasks] WHERE ([task_id] =
@task_id)"@task_id should have the value from the task_id-field of the master record, displayd in the master grid-view.Thank you in advance for your help
View 1 Replies
View Related
Aug 14, 2007
I'd like to put together a summary report for some of my management staff. The report should show the # of jobs that match about 20 different statuses. Each status has different criteria. For example, one might look for a date in a datetime field and the type of job. Another might look at whether a date has passed and the quantity we're shipping.
Sort of like this:
Status Items Jobs
Status #1: 50000 15
Status #2: 25251 3
I want the user to see this summary information, but also have drill down capability. I'd like the ability to print the summary or just a drill-down.
I'm also considering using Dundas Charts for RS on the report.
Can I accomplish this with Reporting Services? If so, any tips on how to do it? If this a bunch of sub-reports? Can I sum the # of jobs on a sub-report? Should I be looking at BI for this?
Or should I be working on a Forms-based?
Thanks!
Brian
View 7 Replies
View Related
Feb 19, 2007
I have a stored procedure in my SQL 2005 Server database named Foo that accepts two parameters, @paramA and @paramB.In my ASP.NET page, I have these:<asp:GridView id="gv" runat="server" AutoGenerateColumns="true" DataSourceID="DS" AllowSorting="true" DataKeyNames="ID"/><asp:SqlDataSource ID="DS" runat="server" ConnectionString="<%$ ConnectionStrings:CS1 %>" SelectCommand="Foo" SelectCommandType="StoredProcedure" OnSelecting="DS_Selecting"> <asp:Parameter Name="paramA" Type="String" /> <asp:Parameter Name="paramB" Type="String" /></asp:SqlDataSource>In my setup, paramA and paramB are set in DS_Selecting(), where I can access the Command.Parameters[] of DS.Now, here's the problem. As you can see, the GridView allows for sorting. When you click on a header title to sort, however, the GridView becomes empty. My question is, how can I get the GV sorted and in the correct direction (i.e. asc/desc)? My first step in my attempt was to add another parameter to the SqlDataSource and sotred procedure Foo (e.g. @SortByColumn), then changed Foo appropriately: ALTER PROCEDURE Foo @paramA nvarchar(64), @paramB nvarchar(64), @SortColumn nvarchar(16) = 'SearchCount' AS SELECT * FROM Searches ORDER BY CASE WHEN @SortColumn='SearchCount' THEN SearchCount WHEN @SortColumn='PartnerName' THEN PartnerName ELSE ID ENDThat works find and dandy. But wait--I want to get the correct ORDER BY direction too! So I add another parameter to the SqlDataSource and Foo (@SortDirection), then alter Foo: ... SELECT * From Searchces ORDER BY CASE /* Keep in mind that CASE short-circuits */ WHEN @SortColumn='SearchCount' AND @SortDirection='desc' SearchCount DESC WHEN @SortColumn='SearchCount' SearchCount WHEN @SortColumn='PartnerName' AND @SortDirection='desc' PartnerName DESC WHEN @SortColumn='PartnerName' PartnerName WHEN @SortColumn='ID' AND @SortDirection='desc' ID DESC ELSE ID END ...But including DESC or ASC after the column name to sort by causes SQL to error. What the heck can I do, besides convert all my stored procedures into in-line statements inside the ASP page, where I could then dynamically construct the appropriate SQL statement? I'm really at a loss on this one! Any help would be much appreciated!Am I missing a much simpler solution? I am making this too complicated?
View 2 Replies
View Related
May 7, 2008
Hello. im tryinng to build an application that has a girdview with values from 2 different tables. The select query i have used in a stored procedure works great but when i try to write something to update into 2 different tables I cant figure out how to do it.
This is the first time im writing stored procedures but from what i can tell i need to do 2 seperate updates to insert mu values.
im using the AdventureWorksLT database provided by microsoft to experiment with and my gridview consists of 2 tables populated by the following stored procedure:ALTER PROCEDURE dbo.GetSomething AS
SELECT Customer.CustomerID, Customer.LastName, Customer.FirstName, CustomerAddress.AddressID, CustomerAddress.AddressType FROM SalesLT.Customer, SalesLT.CustomerAddress
WHERE Customer.CustomerID = CustomerAddress.CustomerID
RETURN
What id like to do is to make the GridView editable and then send the all thee values back so the changes are saved in both tables. Could anyone help me write a stored procedure that gets the values from the fields in the gridview that is beeing changed and send them back to the tables?
View 4 Replies
View Related
Apr 7, 2008
Hi, can anyone help me? I want to know whether is it possible to insert records simultaneously in both master & detail tables?
For elaboration, say there is a master table contains (orderid,orderdate,amount) and details table contains (orderid, productid,qty,price). 1 record of master table associated with n records of details table. Can it be possible to insert both the 1 record at master table with n records in details table in a single sql statement?
View 3 Replies
View Related
Sep 20, 2007
Hi Experts,
I need to know the best approach to save data in master table and then in detail table.
I know this method but i know it's not a good approach why i will explain
Insertion in Master Table..................................... A
Insertion in Detail Table........................................B
Now if there is any exception occurred while step A then the step B will not take place which is ok but if there is exception while step B then the process A will have completed
i.e the data in master table will be Inserted/Deleted/Updated but there will not be a corresponding action in Detail table which is not good approach.
So please can any one tell me a good approach for this.
View 6 Replies
View Related
Nov 30, 2007
I need to export data to a text file in the following format:
Master Record
Detail Record Detail Record
Detail Record
Master Record
Detail Record
...
Example would be:
Master Record Format:
----------------------------------------------------------------------
RecordType|FirstName|LastName
Detail Record Format:
----------------------------------------------------------------------
RecordType|Order#|OrderedItem
Sample Data:
M|Micheal|Smith
D|123|1-123-1
D|123|1-123-2
M|John|Smith
D|142|1-444-1
D|142|1-444-3
Could someone direct me how I can acomlish this task using SSIS?
Thanks -
View 8 Replies
View Related
Mar 27, 2007
As part of a project, I'm not allowed to use foreign keys, well can't. But I have a problem with this master detail relationship (and a very simple one too).
Tables to begin with:
Invoice:
invoiceid : bigint indentity autoincrement primarykey
Detail:
invoiceid: bigint - detailnumber:bigint - desc : char20
Okay - I want to set these two tables up such than when I create a new invoice, and subsequently get the new invoiceid, and I start to add detail records to the detail table, I want the detailnumber to autoincrement when I do an SQL Insert. Right now I have detailnumber as the primary key, so when you view the detail records, it looks like:
(invoiceid, detailnumber, desc) 1,1,text - 1,2,text - 1,3,text - 2,4, text on another invoice - 3,5, text
I want it to work like:
(invoiceid, detailnumber, desc) 1,1,text - 1,2,text - 1,3,text - 2,1, text on another invoice - 3,1, text
where the invoiceid and the detail number make up the primary key.
I seem to recall you could do this is PHP before it had foreign keys & such, and I'm completely drawing a blank on this. BTW - I'm using enterprise manager to set this up, but I can use QA with a sample script to acheive the same.
I realize this may be (and probably is) off-topic here, but I can't figure out where to ask.
Thanks in advance.
View 3 Replies
View Related
Sep 30, 2007
Hi !
I want to insert master/detail data using transaction if while insert if error it will Rollback. Help me! Thanks
View 2 Replies
View Related
Feb 19, 2004
SQL Server 2000
Help! Surely this has happened to others before me. A new customer wants to send updates in a fixed-width txt file in which master and detail rows alternate.
How do you do this?
Do you:
1) Painfully muck around with 100,000 rows in the .txt file first--split it into 2 files, one for master records and one for detail records and then process? If so, what do you use? I'd probably hack at it with a VB Script module in a DTS package.
or
2) Is there a way to feed it into 2 tables where rows starting with x go to one table and rows starting with y go to another?
The plan was to use a fast and dirty DTS package to shove this stuff into a table (probably 2 but we might just toss the stuff we don't need and put in in one) but I'd like some advice on how to proceed.
Thanks for any suggestions!
View 14 Replies
View Related
Jun 24, 2008
Dear Sir
Thank for your reply but our requirements are not this
I am fully explain my requirements
In Master table have 5 columns; In Master Table stored all records.
NameAppidFunctionCodeFunNameSubFunCode
Ad630Manual0
Ad630Log10
Data810Summary0
Data820View0
Data830&View0
Data840Row10
Ad630Mbl20
In second Table store those records who are selected and stored in 2nd table.
NameAppidFunctionCodeFunNameSubFunCode
Data810Summary0
Data820View0
Data830&View0
Ad630Mbl20
Our requirements we use one query,
In query fetch total 5 rows. and output show like this
NameAppidFunctionCodeFunNameSubFunCode
Ad630Manual0N
Ad630Log10N
Data810Summary0Y
Data820View0Y
Data830&View0Y
Data840Row0N
Ad630Mbl20Y
Please immediate reply me,
I am waiting your reply.
Thanks Asit Sinha
View 3 Replies
View Related
Jul 27, 2007
I'd like to create a master-detail output from SQL2005 to display in a page using classic ASP. Ideally, I want the output to contain a <div> to show/hide the order details beneathe the order header, as shown below.
OrderNo OrderDate
1001 7/27/2007
+ <div to show/hide>
Item Description
AAA desc_for_itemAAA
BBB desc_for_itemBBB
+ </div>
1002 7/26/2007
+ <div to show/hide>
Item Description
CCC desc_for_itemCCC
+ </div>
I currently just have a stored procedure returning the results and it is very slow as I'm simply querying the details section for each order header. Does anyone have any ideas on how I can create an efficient and fast method for returning these results in the format above?
View 5 Replies
View Related
Feb 25, 2008
Like the subject says I have tested the SP in Gridview. Everything looks fine. But when run nothing shows up on the page.
I have tried using QueryStrings to pass the data and Controls.
Here is the Gridview Code:<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
BackColor="White" BorderColor="#3366CC" BorderStyle="None" BorderWidth="1px"
CellPadding="4" DataSourceID="SqlDataSource1">
<FooterStyle BackColor="#99CCCC" ForeColor="#003399" />
<RowStyle BackColor="White" ForeColor="#003399" />
<Columns><asp:BoundField DataField="FirstName" HeaderText="FirstName"
SortExpression="FirstName" /><asp:BoundField DataField="LastName" HeaderText="LastName"
SortExpression="LastName" /><asp:BoundField DataField="EthnicID" HeaderText="EthnicID"
SortExpression="EthnicID" /><asp:BoundField DataField="Gender" HeaderText="Gender"
SortExpression="Gender" /><asp:BoundField DataField="Height" HeaderText="Height"
SortExpression="Height" /><asp:BoundField DataField="Weight" HeaderText="Weight"
SortExpression="Weight" />
<asp:BoundField DataField="Hair" HeaderText="Hair" SortExpression="Hair" />
<asp:BoundField DataField="Eyes" HeaderText="Eyes" SortExpression="Eyes" />
<asp:BoundField DataField="City" HeaderText="City" SortExpression="City" />
<asp:BoundField DataField="State" HeaderText="State" SortExpression="State" />
</Columns>
<PagerStyle BackColor="#99CCCC" ForeColor="#003399" HorizontalAlign="Left" />
<SelectedRowStyle BackColor="#009999" Font-Bold="True" ForeColor="#CCFF99" />
<HeaderStyle BackColor="#003399" Font-Bold="True" ForeColor="#CCCCFF" />
</asp:GridView>
Here is the Datasource:<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:SQL2005_311004_modelsystemConnectionString %>"
SelectCommand="spSearch" SelectCommandType="StoredProcedure">
<SelectParameters><asp:QueryStringParameter DefaultValue="" Name="FirstName"
QueryStringField="fn" Type="String" />
<asp:QueryStringParameter Name="LastName" QueryStringField="ln" Type="String" />
<asp:Parameter Name="EthnicID" Type="String" />
<asp:Parameter Name="Gender" Type="String" />
<asp:Parameter Name="Height" Type="String" />
<asp:Parameter Name="weight" Type="String" />
<asp:Parameter Name="Hair" Type="String" />
<asp:Parameter Name="Eyes" Type="String" />
<asp:Parameter Name="City" Type="String" />
<asp:Parameter Name="State" Type="String" />
</SelectParameters>
</asp:SqlDataSource>
I hope you can help me with this one!!!
View 2 Replies
View Related
Mar 7, 2008
Here is the Stored procedure
ALTER procedure [dbo].[ActAuditInfo](@IndustryName nvarchar(50) output,@CompanyName nvarchar(50) output,@PlantName nvarchar(50) output,@GroupName nvarchar(50) output,@UserName nvarchar(50) output
)asbegindeclare @AuidtID as varchar(30)Select @IndustryName=Industry_Name from Industry whereIndustry.Ind_Id_PK =(Select Audit_Industry from Audits whereAd_ID_PK=@AuidtID)Select @CompanyName=Company_Name from Company whereCompany.Cmp_ID_PK =(Select Audit_Company from Audits whereAd_ID_PK=@AuidtID)Select @PlantName=Plant_Name from Plant where Plant.Pl_ID_PK=(Select Audit_Plant from Audits where Ad_ID_PK=@AuidtID)Select @GroupName=Groups_Name from Groups whereGroups.G_ID_PK =(Select Audit_Group from Audits whereAd_ID_PK=@AuidtID)Select @UserName=Login_Uname from RegistrationDetails whereRegistrationDetails.UID_PK =(Select Audit_Created_By fromAudits where Ad_ID_PK=@AuidtID)SELECT Ad_ID_PK, Audit_Name, @IndustryName, @CompanyName, @PlantName,@GroupName, Audit_Started_On, Audit_Scheduledto, @UserName FROMAudits where Audit_Status='Active'end
U can see here different parameters,my requirement is that iam havingID's of Industry,company,plant,group,username stored in a table calledPcra_Audits and i must display their related names in the front end.so this is the query iam using for that.
Data in the database:Commercial83312 2 2 2 1 1 InactiveHere u can see 2,2,2,1,1 these are the IDs ofindustry,company,plant,group and username and Commercial83312 is tehaudit ID.now i want to display this data in teh front end as i cannot displaythe IDs i am retrieving the names of the particular IDs from therelated tables.Like iam getting name of the IndustryID from Industry Table,in thesame way others too.when iam running this procedure iam getting the gridview blank.iam passing the output parameters:@IndustryName nvarchar(50) output,@CompanyName nvarchar(50) output,@PlantName nvarchar(50) output,@GroupName nvarchar(50) output,@UserName nvarchar(50) outputinto the function in the frontend and iam calling that into the pageload method.please help me with this.
View 1 Replies
View Related
Feb 22, 2008
Hi
i have a search page having four text boxes like name,accountnumber,ssn..etc we have to search the database by these values. but even if we give value in one text box keeping the others null the stored procedure have to serach and get the values. and we display it using gridview control.
here is the stored procedure i wrote.but its not working.its not giving any erros...but its not showing any values.
ALTER Procedure [dbo].[usp_CheckUser]
@name nvarchar(50),
@ssn nvarchar(50),
@accountnumber nvarchar(50)
AS
BEGIN
if(@name!=null AND @ssn!=null AND @accountnumber!=null)
select * from Userinfo where name=@name AND ssn=@ssn AND accountnumber=@accountnumber
else if(@name=null AND @ssn!=null AND @accountnumber!=null)
select * from Userinfo where ssn=@ssn AND accountnumber=@accountnumber
else if(@name=null AND @ssn=null AND @accountnumber!=null)
select * from Userinfo where accountnumber=@accountnumber
else if(@name!=null AND @ssn=null AND @accountnumber!=null)
select * from Userinfo where accountnumber=@accountnumber AND name=@name
else if(@name!=null AND @ssn=null AND @accountnumber=null)
select * from Userinfo where name=@name
else if(@name!=null AND @ssn!=null AND @accountnumber=null)
select * from Userinfo where name=@name AND ssn=@ssn
else if(@name=null AND @ssn!=null AND @accountnumber=null)
select * from Userinfo where ssn=@ssn
end
table name is userinfo
please help me with this. its very urgent.
thanx for your help in advance.
ramya
View 7 Replies
View Related
Jan 29, 2007
I have multiple drop down lists that I would like to have send parameters to a stored procedure that does a select on the data. (Kind of like a search feature) But if a ddl isnt populated, I want that to act like a * and get all. For instance, I have a persontype, state, and country drop down lists. If I drop the state one down to WA, I want the gridview to populate with all the people in WA regardless or their type. If I populate type and state, have it give me both regardless of country.
What does the stored procedure or select syntax look like for this? (What would the default values of my dropdown boxes?)
my current sql select statement (The bottom 3 are bit fields which are throwing me off a bit too). do i need to have some IsNull statments around each variable in the select?SELECT
PersonFirstName
, PersonLastName
, PersonStreetAddress
, PersonCity
, PersonZip
, PersonEmail
, PersonEmailDate
, PersonPhone1
, PersonNotes
, InterestChapterLeaders
, InterestMembers
, InterestUniLeader
, PersonGradYear
, PersonTypeID
, CountryID
, StateID
, SchoolID
, PersonID
FROM tblPerson
WHERE
tblPerson.PersonTypeID = @PersonTypeID
and tblPerson.StateID = @StateID
and tblPerson.CountryID = @CountryID
and tblPerson.SchoolID = @SchoolID
and tblPerson.InterestChapterLeaders = @InterestChapterLeaders
and tblPerson.InterestMembers = @InterestMembers
and tblPerson.InterestUniLeader = @InterestUniLeader
View 8 Replies
View Related
Feb 28, 2002
I need to write a sql query which is a master-detail query. Here's the example structure of tables:
Master table:
ColID as longint, ColA as int, ColB as int, ColPartID as longint, ColPartName as longint
Child table -- Wheel:
ColID as longint, ColA as int, ColB as int
Child table -- Door:
ColID as longint, ColA as int, ColB as int
Child table -- Window:
ColID as longint, ColA as int, ColB as int
..... etc
From the master table, it needs to join with its child in order to get the detailed information. However, there're more than one child table for it to join. In other words, the query has to choose the correct child table to join for each row selectively. The use of correct child depends on one of the columns in its master table (ColPartName).
My question is: Does it worth of me keep finding a solution for this query or should I abandon this? I really need some advice, please.
Many thanks,
Leonard
View 1 Replies
View Related
Aug 26, 2014
I have been asked to give the users the ability to copy a set of records within the database. The current setup is
Master Table: JSA1
JSAID: (PK, int, not null)
JOBTITLE(nvarchar(200), null)
PlantNumber(int, not null)
Detail Table: tblSteps
STEPID (PK, int, not null)
JSAID (FK, int, not null)
StepNo (int, null)
BasicSteps (nvarchar(200), null)
DBPhoto(varbinary(max), null)
The plant number field is a location based field that the application uses to filter/select data on for the end users. What they want to be able to do is to select a record, select another location from a dropdown list and then click a button that duplicates the master record and the detail records to the new location.
I am thinking that a stored procedure passing the JSAID and new Location number to do it, I am just not sure how to get the new ID when I go to copy the detail records.
View 4 Replies
View Related
Dec 15, 2006
Hallo,
I'm currently strugling with the setup of our packages for deployment to a new environment.
We are working with a master/detail package setup. One master package is created that will call all child packages. In the master package we don't have any connection towards our source and/or target databases/sourcesystems.
Everything works fine, however, starting to deploy the whole set of packages, it seems that we don't have the option to set specific properties of our detailed packages, e.g. connection properties. But this is just what we need.
When we are adding a job in the Job Agent for our master package to be scheduled, we want to be able to set all different connection manager properties, not only the one from the master package and definitely the ones from the detailed packages as there we switch the connections from the development environment towards the acceptance environment.
I tried to fix this with parent package variables, but I can't set the password property, only the ServerName and UserName can be set, not the Password.
Anyone an idea what the easiest and best approach is to solve this burden?
Thx
View 1 Replies
View Related
Mar 3, 2014
I have a master detail reports in one page. Master report displays all the products and details report shows their orders... What I want is, when user clicks on products graph its detail graph show the orders of that particular record on the same page. Both reports would remain visible on the same page side by side.
View 4 Replies
View Related
Sep 4, 2014
CREATE TABLE DHS(CUSTOMERNBR VARCHAR(20), CONTRACT VARCHAR(20), SUBCONTRACT VARCHAR(20) , STARTDATE DATETIME, ENDDATE DATETIME, EFLAG VARCHAR(20), HFLAG VARCHAR(20))
The data which will be going into this table is from two table which have a 1 to many relationship:
Here is the 1 side and data:
CREATE TABLE CUSTOMERS(
CUSTOMERNBR VARCHAR(20),
CONTRACT VARCHAR(20),
SUBCONTRACT VARCHAR(20),
STARTDATE DATETIME,
ENDDATE DATETIME DEFAULT '12/31/2099')
[Code] ....
Here is the Many side and data:
CREATE TABLE FLAGS(CUSTOMERNBR VARCHAR(20), FLAGCODE VARCHAR(20), STARTDATE DATETIME, ENDDATE DATETIME DEFAULT '12/31/2099')
INSERT INTO FLAGS(CUSTOMERNBR, FLAGCODE, STARTDATE, ENDDATE) VALUES('10001000101', 'H', '02/01/2014', '03/31/2014')
[Code] ....
The CUSTOMERS table holds the record date span into which the FLAGS table records have to "fit". In no case will the date spans from the FLAGS table fall outside the STARTDATE - ENDDATE span in the CUSTOMERS table for any CUSTOMERNBR. Here is an example of the final expected output in the DHS table from combining records in the CUSTOMERS and FLAGS tables:
CUSTOMERNBRCONTRACTSUBCONTRACTSTARTDATEENDDATEEFLAGHFLAG
10001000101A910400801/01/201401/31/2014
10001000101A910400802/01/201403/21/2014H
10001000101A910400804/01/201404/30/2014
10001000101A910400805/01/201405/31/2014H
10001000101A910400806/01/201412/31/2099E
10001000102A555500101/01/201403/31/2014E
10001000102A555500104/01/201404/30/2014EH
10001000102A555500105/01/201406/30/2014E
10001000102A555500107/01/201412/31/2099
10001000103A666600401/01/201410/01/2014
View 1 Replies
View Related
Mar 14, 2015
I have two tables. Order table and order detail table.
What I want to do is send notification to a service when an order is created. And I want to include both header and detail of the order.
I can't get working with on insert trigger . How to go around with trigger ?
View 1 Replies
View Related
Jul 29, 2005
During a unit test, I was surprised to find a data access error that traces back to a particular master stored procedures missing execute permissions for the dbo. I wonder if there's somewhere a sample script that globally grants execute permision to stored procedures. Better still can the permission granting be automated each time a new stored procedure is created?.Thanks much.
View 3 Replies
View Related
