Inserting Into Tables Using Stored Procedure

Dec 7, 2007

I am currently building an electricity quoting facility on my website. I am trying to insert the data which the user enters into 3 linked tables within my database. My stored procedure therefore, includes 3 inserts, and uses the @@Identity, to retrieve the primary keys from the first 2 inserts and put it as a foreign key into the other table.

When the user comes to the quoting page, they enter their contact details which goes into a client_details table, then they enter the supply details for their electric meter these get inserted into the meter table which is linked to client_details. The supply details which the users enters are used to calculate a price. The calculated price, then gets put into a quote table which is linked to the meter table. This all seems to work fine with my stored procedure.

However I want to be able to allow a user to enter more than one meter supply details and insert this into the meter table, with the same client_id for the foreign key. This will also generate another quote to insert into the quoting table. However I do not know how to get this to work.

Should I be looking at using Sessions and putting a SessionParameter on the client_id for the inserts for these additional meters??

 

 

View 4 Replies


ADVERTISEMENT

Inserting Simultaneous Tables Using Stored Procedure

Feb 8, 2007

I am trying to insert into two tables simultaneously from a formview. I read a few posts regarding this, and that is how I've gotten this far. But VWD 2005 won't let me save the following stored procedure. The error I get says “Incorrect syntax near ‘@WIP’. Must declare the scalar variable “@ECReasonâ€? and “@WIPâ€?.â€? I'm probably doing something stupid, but hopefully someone else will be able to save me the days of frustration in finding it. Thanks, the tables and procedures are below.

I made up the two tables just for testing, they are:
tbltest


ECID – int (PK)

View 2 Replies View Related

Stored Procedure Format Incorrect - Inserting To Two Tables

Dec 9, 2007

Hi can anyone help me with the format of my stored procedure below.
I have two tables (Publication and PublicationAuthors). PublicaitonAuthors is the linking table containing foreign keys PublicaitonID and AuthorID. Seeming as one Publication can have many authors associated with it, i need the stored procedure to create the a single row in the publication table and then recognise that multiple authors need to be inserted into the linking table for that single PublicationID. For this i have a listbox with multiple selection =true.
At the moment with the storedprocedure below it is creating two rows in PublicaitonID, and then inserting two rows into PublicationAuthors with only the first selected Author from the listbox??? Can anyone help???ALTER PROCEDURE dbo.StoredProcedureTest2
@publicationID Int=null,@typeID smallint=null,
@title nvarchar(MAX)=null,@authorID smallint=null
AS
BEGIN TRANSACTION
SET NOCOUNT ON
DECLARE @ERROR Int
--Create a new publication entry
INSERT INTO Publication (typeID, title)
VALUES (@typeID, @title)
--Obtain the ID of the created publication
SET @publicationID = @@IDENTITY
SET @ERROR = @@ERROR
--Create new entry in linking table PublicationAuthors
INSERT INTO PublicationAuthors (publicationID, authorID)
VALUES (@publicationID, @authorID)
SET @ERROR = @@ERROR
IF (@ERROR<>0)
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION

View 1 Replies View Related

Stored Procedure Not Inserting Into Linking Table Properly - Two Tables - Two Insert Statements

Dec 9, 2007

Hi can anyone help me with the format of my stored procedure below.
I have two tables (Publication and PublicationAuthors). PublicaitonAuthors is the linking table containing foreign keys PublicaitonID and AuthorID. Seeming as one Publication can have many authors associated with it, i need the stored procedure to create the a single row in the publication table and then recognise that multiple authors need to be inserted into the linking table for that single PublicationID. For this i have a listbox with multiple selection =true.
At the moment with the storedprocedure below it is creating two rows in PublicaitonID, and then inserting two rows into PublicationAuthors with only the first selected Author from the listbox??? Can anyone help???ALTER PROCEDURE dbo.StoredProcedureTest2
@publicationID Int=null,@typeID smallint=null,
@title nvarchar(MAX)=null,@authorID smallint=null
AS
BEGIN TRANSACTION
SET NOCOUNT ON
DECLARE @ERROR Int
--Create a new publication entry
INSERT INTO Publication (typeID, title)
VALUES (@typeID, @title)
--Obtain the ID of the created publication
SET @publicationID = @@IDENTITY
SET @ERROR = @@ERROR
--Create new entry in linking table PublicationAuthors
INSERT INTO PublicationAuthors (publicationID, authorID)
VALUES (@publicationID, @authorID)
SET @ERROR = @@ERROR
IF (@ERROR<>0)
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION

View 9 Replies View Related

Stored Procedure Not Inserting Into Linking Table Properly - Two Tables - Two Insert Statements

Dec 9, 2007

Hi can anyone help me with the format of my stored procedure below.
I have two tables (Publication and PublicationAuthors). PublicaitonAuthors is the linking table containing foreign keys PublicaitonID and AuthorID. Seeming as one Publication can have many authors associated with it, i need the stored procedure to create the a single row in the publication table and then recognise that multiple authors need to be inserted into the linking table for that single PublicationID. For this i have a listbox with multiple selection =true.
At the moment with the storedprocedure below it is creating two rows in PublicaitonID, and then inserting two rows into PublicationAuthors with only the first selected Author from the listbox??? Can anyone help???ALTER PROCEDURE dbo.StoredProcedureTest2
@publicationID Int=null,@typeID smallint=null,
@title nvarchar(MAX)=null,@authorID smallint=null
AS
BEGIN TRANSACTION
SET NOCOUNT ON
DECLARE @ERROR Int
--Create a new publication entry
INSERT INTO Publication (typeID, title)
VALUES (@typeID, @title)
--Obtain the ID of the created publication
SET @publicationID = @@IDENTITY
SET @ERROR = @@ERROR
--Create new entry in linking table PublicationAuthors
INSERT INTO PublicationAuthors (publicationID, authorID)
VALUES (@publicationID, @authorID)
SET @ERROR = @@ERROR
IF (@ERROR<>0)
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION

View 12 Replies View Related

SQL 2012 :: Store Procedure - Inserting Same Data Into Two Tables

Nov 13, 2014

In one store procedure I do insert same data into two tables (They have the same structure): OrderA and OrderB

insert into OrderA select * from OrderTemp
insert into OrderB select * from OrderTemp

And then got an error for code below.

"Multi-part identifier "dbo.orderB.OrderCity" could not be bound

IF dbo.OrderB.OrderCity=''
BEGIN
update dbo.OrderB
set dbo.orderB.OrderCity='London'
END

View 5 Replies View Related

Tables, Stored Procedures, Inserting, Retrieving....help :-)

Apr 8, 2008

Hello there!
I've been sitting here thinking how to work through this problem but I am stuck. First off I have two tables I am working with.
Request TablerqstKey (this is the primary key and has the identity set to yes)entryDte                  datetimesummary                 nvarchar(50)etryUserID               nvarchar(50)rqstStatusCdeKey    bigint
ReqStatusCode TablerqstStatusCdeKey   bigintstatusCode             nvarchar(50)
I have webforms that are using Session variables to store information from the webpages into those variables and insert them into these tables. However, I am wanting to insert the rqstStatusCdeKey (from the ReqStatusCode Table) value into the Request Table. The rqstStatusCdeKey value i am wanting to insert is '3' which stands for "in Design" (((this is going to be the default value))). I need help :-/ If you need me to explain more/ clarify, and show more information I can. Thank you so much!!!

View 4 Replies View Related

About Inserting Some Parameters To A Stored Procedure.

Feb 4, 2007

Hello everyone,
I am having problem with a program that gets some input from a webform and inserts to a stored procedure, I am getting the two error Error messages below, can somebdoy have a look my code below and put me in the right direction. thanks in advance
Errors
'System.Data.SqlClient.SqlCommand' does not contain a definition for 'InsertCommandType' 
'System.Data.SqlClient.SqlCommand' does not contain a definition for 'InsertCommand' 
 protected void Button1_Click(object sender, EventArgs e)
{
/* These two variables get the values of the textbox (i.e user input) and assign two local
* variables, This is also a good strategy against any Sql Injection Attacks.
*
*/
string Interview1 = TextBox1.Text;
string Interview2 = TextBox2.Text;

string Interview3 = TextBox3.Text;
string ProdMentioned = TextBox4.Text;

string ProdSeen = TextBox5.Text;
string Summary = TextBox6.Text;

string Compere = TextBox7.Text;
string Duration = TextBox8.Text;


//Create Sql connection variable that call the connection string
SqlConnection SqlConnection = new SqlConnection(GetConnectionString());

//Create a sql command to excute SQL statement against SQL server
SqlCommand Command = new SqlCommand();

// Set the command type as one that calls a Stored Procedure.
Command.InsertCommandType = CommandType.StoredProcedure;

//Call the stored procedure so we can pass it on the user input to retrieve user details
Command.InsertCommand = "Summaries";

//open the command connection with the connection string
Command.Connection = SqlConnection;

// Pass the user input to the Stored Procedure to check if user exists in our system.
Command.InsertParameters.Add("interview1", interview1);
Command.InsertParameters.Add("interview2", interview2);
Command.InsertParameters.Add("interview3", interview3);

Command.InsertParameters.Add("ProdMentioned", ProdMentioned);
Command.InsertParameters.Add("ProdSeen", ProdSeen);

Command.InsertParameters.Add("Compere", Compere);
Command.InsertParameters.Add("Duration", Duration);



int rowsAffected = 0;

try
{
rowsAffected = Command.Insert();

}
catch (Exception ex)
{
Resonse.Redirect("InsertSuccessfull.aspx");

}



// open the connection with the command
//Command.Connection.Open();

}

private static string GetConnectionString()
{

return ConfigurationManager.ConnectionStrings["BroadcastTestConnectionString1"].ConnectionString;

}
}  
 

View 1 Replies View Related

Passing Xml To Stored Procedure For Inserting

Feb 16, 2007

Anyone got an example of passing xml to a stored procedure and within that procedure, grabbing values out of the xml to perform an insert ?

View 1 Replies View Related

Inserting Records Via Stored Procedure

Mar 23, 2006

I am trying to insert a record in a SQL2005 Express database. I can use the sp fine and it works inside of the database, but when I try to launch it via ASP.NET it fails...
here is the code. I realize it is not complete, but the only required field is defined via hard code. The error I am getting states it cannot find "sp_InserOrder"
 
===
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim conn As SqlConnection = Nothing
Dim trans As SqlTransaction = Nothing
Dim cmd As SqlCommand
conn = New SqlConnection(ConfigurationManager.ConnectionStrings("PartsConnectionString").ConnectionString)
conn.Open()
trans = conn.BeginTransaction
cmd = New SqlCommand()
cmd.Connection = conn
cmd.Transaction = trans
cmd.CommandText = "usp_InserOrder"
cmd.CommandType = Data.CommandType.StoredProcedure
cmd.Parameters.Add("@MaterialID", Data.SqlDbType.Int)
cmd.Parameters.Add("@OpenItem", Data.SqlDbType.Bit)
cmd.Parameters("@MaterialID").Value = 3
cmd.ExecuteNonQuery()
trans.Commit()
=====
 
I get an error stating cannot find stored procedure. I added the Network Service account full access to the Web Site Directory, which is currently running locally on Windows XP Pro SP2.
 
Please help, I am a newb and lost...as you can tell from my code...

View 4 Replies View Related

Inserting BLOB Using Stored Procedure

Sep 14, 2007

Hi,

Can we insert a blob in the database(eg: doc, jpeg, pdf, etc) from the sqlcmd prompt. I want to insert few files into my table having varbinary(max) column and i dont want to use any front end tool for making such insertions. What i am thinking of is providing a file system path for a particular file(eg: doc, jpeg, pdf, etc) to a stored procedure so that it can be inserted into the database, something that we can do via Oracle's sqlldr tool.

Regards
Salil

View 1 Replies View Related

Can Anybody Tell Me Why The Following Stored Procedure Is Not Inserting Into My Database Table?....

Sep 7, 2007

ALTER PROCEDURE AddListAndReturnNewIDValue (
@EditorId int,@CategoryID int,
@ListTitle nvarchar(50),@Blurb nvarchar(250),
@FileName nvarchar(50),@ByLine nvarchar(50),
@HTMLCopy nvarchar(MAX),@MainStory bit,
@MainStoryImageFile nvarchar(50),@Publish bit,
@PublishDate smalldatetime,
@ListId int OUTPUT
)
AS
-- Insert the record into the database
INSERT INTO shortlist (EditorId,CategoryID,ListTitle,Blurb,FileName,ByLine,HTMLCopy,MainStory,MainStoryImageFile,Publish,PublishDate)
VALUES (@EditorID,@CategoryID,@ListTitle,@Blurb, @FileName, @ByLine, @HTMLCopy, @MainStory, @MainStoryImageFile,@Publish,@PublishDate)
-- Read the just-inserted ProductID into @NewProductID
SET @ListId = SCOPE_IDENTITY()
 
here is the sqlDataSource
<asp:SqlDataSource
id="srcShortList"
ConnectionString="<%$ ConnectionStrings:ShortList %>"
SelectCommand="SELECT Id,EditorId,CategoryID,ListTitle,Blurb,FileName, ByLine, HTMLCopy, MainStory, MainStoryImageFile, Publish,PublishDate,Date,Deleted FROM shortlist"
InsertCommand="AddProductAndReturnNewProductIDValue"SelectCommandType="StoredProcedure"
UpdateCommand="UPDATE shortlist SET CategoryID=@CategoryID,ListTitle=@ListTitle,Blurb=@Blurb,ByLine=@ByLine,HTMLCopy=@HTMLCopy,MainStory=@MainStory,Publish=@Publish,PublishDate=@PublishDate WHERE Id=@Id"
Runat="server" >
<SelectParameters>
<asp:QueryStringParameter
Name="Id"
QueryStringField="Id" />
</SelectParameters>
 
</asp:SqlDataSource>

View 2 Replies View Related

Regarding Stored Procedure For Selecting A Value From One Table And Inserting It To Another

Sep 19, 2007

Hi iam Prameela,
I want to select some dynamic values from a table and store them to another table.
Let me give u an example,its like:
I have UID,QID,Option1,Option2,Survey Name in one table called Survey Answers and i must select these values and insert them into Surevy Count table which contains some fields as QID,Opt1Cnt,Opt2Cnt,Survey Name. this is an online survey and when ever an user participate in the survey then values will be changed in Survey Answers like:
Surevy Answers Table:
UID   QID        Option1             Option2          Survey Name---------These are the fields
1         1             1                       0                    Articles
1          2            0                       1                    Articles
2           1            1                        0                      Articles
2           2             0                       1                       articles
I need to add all these Options of particular QID and store them in Survey Count table,like
QID          Opt1Cnt          Opt2Cnt          Survey Name
1                 2                     0                     Articles
2                  0                      2                   Articles
When ever the user participate in survey then there will be change in Survey answers table i.e the option count will be increased
So this count should be modified in Survey Count Table,like:
If another user participated in survey and if he voted for Option1 of QID1,Option1 of QID2 then the survey count table should be modified as:
QID           Opt1Cnt                Opt2Cnt             Survey Name
1                   3                         0                            Articles
2                   1                          2                             Articles
I need a Stored Procedure for this.
Please help me with this query.
 

View 7 Replies View Related

Question About Inserting A Row Before The First Row Of The Table ----Stored Procedure

Jan 31, 2008

Hi, I have a stored procedure. In the stored procedure, I have a table called "#temp", this table contains 50 rows that I select from other tables. Now I want to insert a row before the first row. How can I do it in SQL Server 2005? Thanks in advance.

View 2 Replies View Related

Inserting Request.Querystring Into The Stored Procedure

Feb 2, 2008

Hi i am trying to insert the value of my Request.Querystring into my stored procedure, but i am having trouble with it, how would i insert the id as a parameter which is expected from the stored procedure this is what i have doen so far;string strID = Request.QueryString["id"];
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["streamConnectionString"].ConnectionString);SqlCommand comm = new SqlCommand("stream_PersonnelDetails", conn);comm.CommandType = CommandType.StoredProcedure;
conn.Open();SqlDataReader reader = comm.ExecuteReader(CommandBehavior.CloseConnection);
DataList1.DataSource = reader;
DataList1.DataBind();
conn.Close();
 
Thank you

View 2 Replies View Related

Inserting A Record Using Values From Another Stored Procedure

Aug 2, 2006

Hello, I'm trying to accomplish 3 things with one stored procedure.I'm trying to search for a record in table X, use the outcome of thatsearch to insert another record in table Y and then exec another storedprocedure and use the outcome of that stored procedure to update therecord in table Y.I have this stored procedure (stA)CREATE PROCEDURE procstA (@SSNum varchar(9) = NULL)ASSET NOCOUNT ONSELECT OType, Status, SSN, FName, LNameFROM CustomersWHERE (OType = 'D') AND (Status = 'Completed') AND (SSN = @SSNum)GO.Then, I need to create a new record in another table (Y) using the SSN,FName and Lname fields from this stored procedure.After doing so, I need to run the second stored procedure (stB) Here itis:CREATE PROCEDURE procstB( @SSNum varchar(9) = NULL)ASSET NOCOUNT ON-- select the recordSELECT OrderID, OrderDate, SSNFROM OrdersGROUP BY OrderID, OrderDate, SSNHAVING (ProductType = 'VVSS') AND (MIN(SSN) = @SSNum)GO.After running this, I need to update the record I created a moment agoin table Y with the OrderDate and OrderID from the second storedprocedure.Do you guys think that it can be done within a single stored procedure?Like for example, at the end of store procedure A creating an insertstatement for the new record, and then placing something like execprocstB 'SSN value'? to run stored procedure B and then having aupdate statement to update that new record?Thanks for all your help.

View 1 Replies View Related

Inserting 1:M Relationship Data Via One Stored Procedure

Sep 2, 2006

Hi,

Uses: SQL Server 2000, ASP.NET 1.1;

I've the following tables which has a 1:M relationship within them:

Contact(ContactID, LastName, FirstName, Address, Email, Fax)
ContactTelephone(ContactID, TelephoneNos)

I have a webform made with asp.net, and have given the user to add maximum of 3 telephone nos for a contact (Telephone Nos can be either Mobile or Land phones). So I've used Textbox's in the following way for the appropriate fields:

LastName,
FirstName,
Address,
Fax,
Email,
MobileNo,
PhoneNo1,
PhoneNo2,
PhoneNo3.

Once the submit button is pressed, I need to take all of this values and insert them in the tables via a Single Stored Procedure. I need to know could this be done and How?

Eagerly awaiting a response.

Thanks,

View 3 Replies View Related

Stored Procedure Inserting Duplicate Records Randomly

Feb 1, 2005

I have a web app that calculates tax filing status and then stores data about the person.

Facts
The insert is done through a stored procedure.
All the sites that this program is being used are connecting through a VPN so this is not an external site.
The duplicate records are coming from multiple sites (I am capturing there IP address).
I am getting a duplicate about 3 or 4 times a day out of maybe 300 record inserts.

Any help would be greatly appreciated.

There are many sqlcmdInsert.Parameters("@item").Value =

cnTaxInTake.Open()
sqlcmdInsert.ExecuteNonQuery()
cnTaxInTake.Close()

And that is it.

View 6 Replies View Related

Error Inserting Image Into SQL Server2000 Table From Pocket PC Application Only When Using Stored Procedure In Table Adapter Wiz

Apr 24, 2008

My Pocket PC application exports signature as an image. Everything is fine when choose Use SQL statements in TableAdapter Configuration Wizard.


main.ds.MailsSignature.Clear();

main.ds.MailsSignature.AcceptChanges();


string[] signFiles = Directory.GetFiles(Settings.signDirectory);


foreach (string signFile in signFiles)

{


mailsSignatureRow = main.ds.MailsSignature.NewMailsSignatureRow();

mailsSignatureRow.Singnature = GetImageBytes(signFile); //return byte[] array of the image.

main.ds.MailsSignature.Rows.Add(mailsSignatureRow);

}


mailsSignatureTableAdapter.Update(main.ds.MailsSignature);

But now I am getting error "General Network Error. Check your network documentation" after specifying Use existing stored procedure in TableAdpater Configuration Wizard.


ALTER PROCEDURE dbo.Insert_MailSignature( @Singnature image )

AS

SET NOCOUNT OFF;

INSERT INTO MailsSignature (Singnature) VALUES (@Singnature);



SELECT Id, Singnature FROM MailsSignature WHERE (Id = SCOPE_IDENTITY())

For testing I created a desktop application and found that the same Code, same(Use existing stored procedure in TableAdpater Configuration Wizard) and same stored procedure is working fine in inserting image into the table.

Is there any limitation in CF?

Regards,
Professor Corrie.

View 3 Replies View Related

How To Search In 2 Tables By Using SQL Stored Procedure??

Mar 7, 2008

Hello,
Is it possible to search in two tables, let's say table # 1 in specific field e.g. (age). If that field is empty then retrieve the data from table #1, if not retrieve the data from table # 2.
Is it possible to do it by using SQL Stored Procedure??
Thank you

View 4 Replies View Related

Stored Procedure Checking 2 Tables

May 12, 2004

ok, ill lay it out like this, and try not to be too confusing.

I need to check my trasaction table to see that the user with a UserID bought, keeping track(adding up) of how many times they bought the Product with a productID, , then i need to selcect items from product table with the productID for the items the user bought,


i know how to return the product information, and how to check the tranaction table and i think how to add up the amount of tranastions for that item


so bacially i need to select data from a product table, for the products that the user bought tahts stored in the transaction table.

well i tried not to be confusing, but i think i failed

View 1 Replies View Related

Using Temporary Tables Within The Stored Procedure

Nov 15, 2006

Hi,If one uses a temporary table/ table variable within a storedprocedure, will it use the compiled plan each time the stored procedureis executed or will it recompile for each execution?Thanks in advance,Thyagu

View 1 Replies View Related

Temp Tables In Stored Procedure.

Oct 22, 2007

Hi

I have one stored procedure which uses temp tables in it .
I am trying to use this stored procedure in my SSRS report as the source.

I am getting an error which says that the temporary table doesnot exist while validating the sql syntax in report.

Also, this stored proc executes in SSMS ithout any problem and giving the result as desired.

Can we use Temp tables in Stored proc and call that in SSRS?
If not, what is the workaround for that.


Thanks

View 13 Replies View Related

Creation Of Two Tables In A Stored Procedure

Oct 17, 2006

Hi,

I was wondering if there was a way to create two temp tables within the same stored procedure.

Can we have two create statements one after the other?

Thanks


View 2 Replies View Related

Joining Two Tables In Stored Procedure

Apr 28, 2008

Hi,
I want write STORED PROCEDURE to join TWO tables.
I am displaying all the details here. Please help me.


INPUT DATA:
Table1
(OLD DATA)
SYMBOL CATEGORY SHARES Prev.Close Last Trade






ABB
ACC
AMBUJACEM
BHARTIARTL
BHEL
BPCL
CAIRN
CIPLA
DLF
DRREDDY

electrical
cement
cement
telecom
electrical
refinery
petrochemical
pharma
construction
pharma

211908375
187634983
1522375422
1897907446
489520000
361542124
1778399420
777291357
1704832680
168172746

1181.25
799.00
115.25
843.00
1850.70
382.60
256.20
226.60
676.00
613.35

1170.15
782.40
115.45
922.40
1870.00
392.90
255.80
224.20
667.85
616.55

Table2
(NEW DATA)
SYMBOL LAST TRADE






ABB
ACC
AMBUJACEM
BHARTIARTL
BHEL
BPCL
CAIRN
CIPLA
DLF
DRREDDY

1153.25
766.95
113.45
928.05
1866.30
390.90
258.70
214.55
668.50
625.20

OUTPUT DATA:
(OLD DATA) (NEW DATA)










as
SYMBOL CATEGORY SHARES Prev.Close Last Trade






ABB
ACC
AMBUJACEM
BHARTIARTL
BHEL
BPCL
CAIRN
CIPLA
DLF
DRREDDY

electrical
cement
cement
telecom
electrical
refinery
petrochemical
pharma
construction
pharma

211908375
187634983
1522375422
1897907446
489520000
361542124
1778399420
777291357
1704832680
168172746

1170.15
782.40
115.45
922.40
1870.00
392.90
255.80
224.20
667.85
616.55

1153.25
766.95
113.45
928.05
1866.30
390.90
258.70
214.55
668.50
625.20




View 1 Replies View Related

Inserting Record Into Second Database From A Stored Procedure In First Database

Aug 10, 2005

is it possible to insert record into second database from a stored procedure which is in first database?

View 1 Replies View Related

Linking Tables In Data Set Or Stored Procedure

Mar 10, 2008

Im currently in the process of developing a new system in ASP.net. The system uses multiple tables from a SQL database. To link information from say the products table to the suppliers table a unique key from the suppliers table is stored in the products table to show which products each supplier has bought...simple so far.

From this I can then pull out any of the information relating to that product instead of just displaying the ID. Currently this is done by creating a stored procedure and then dragging the stored procedure onto the dataset layer to create the data adapter.

Im not 100% this is the best way to be doing this. Is it possible to simply just link the tables in the dataset layer to display a string from another table instead of an ID referencing number?!

any help on this would be much appreciated as I cant really continue untill I have sorted out the data structure problems.Thanks in advancedmowfo 

View 2 Replies View Related

Insert Stored Procedure For Related Tables

Feb 24, 2005

I have two sets of related tables: Quote - QuoteDetail and Order - OrderItem

I need to copy Quote - QuoteDetail records to Order - OrderItem tables

I have the stored procedure up to this point: Insert a Quote in the Order table and get the new Order @@Identity.

I need to insert the QuoteDetail records into the OrderItem table using the new OrderID

Thank you for your help.

View 4 Replies View Related

Querying Temporary Tables From A Stored Procedure

Jun 7, 2005

What I am trying to do now is combine multiple complex queries into one table
and query it showing the results on an ASP.net page.

I am currently using SQL Server 2000 backend.  Each one of these queries are
pretty complex so I created each query as a Stored Procedure.  These queries are
dynamic by each user, so the results will never be the same globally.

What I have done so far was created a master stored procedure passing the
current user's username as a parameter.  Within the master SP (Stored Procedure)
it creates a temporary table inserts and executes multiple stored procedures and
inserts into the temporary directory.  Each of the sub stored procedures all
have the same columns.  After the insert to the temp tables, I then query the
temp table and return it to the function it was executed in code and fill it as
a System.Data.DataTable.  I then bind the DataTable to a
Repeater.DataSource.

Problem: When the page is rendered, it returns nothing.  I
tested the master SP in the data environment and it works fine.

Suspect: ASP.net when the SP is executed, it sees that the
data is a disconnected datasource?  Perhaps the session in the SQL Server when
the temp table is created isn't in SYSOBJECTS system table?

Here is an example of the code from the master SP:


CREATE PROCEDURE dbo.TM_getAlerts      @Username
varchar(32)ASCREATE TABLE #MyAlerts ([DATE] datetime, [TEXT]
varchar(200), [LINK] varchar(200) )
INSERT INTO #MyAlertsEXEC
TM_getAlertsNewTasks @Username
INSERT INTO #MyAlertsEXEC
TM_getAlertsLateTasks @Username
SELECT [DATE] AS 'DATE', [TEXT]
AS 'TEXT', [LINK] AS LINK FROM #MyAlerts
GO

It is a fairly simple call... but why doesn't ASP.net doesn't see it when the
the DataTable is filled.  I tried just executing one of the sub SP without
creating temporary tables and it works flawlessly.  But the query in one of the
sub SP is a normal but complex select.

View 5 Replies View Related

How To Get Data From All Related Tables Using Stored Procedure

Sep 16, 2005

I have a situation where I want to load some entities from one table lets say the table is customers and i would like to load all the customers with first name = dummy, not only this i would like to load all the orders  and order details for these specific customers (these are two different separate tables) . I want all this within one stored procedure that return me three results for three different tables. Please tell me whether it is possible and how.

View 1 Replies View Related

Bind To Multiple Tables From Stored Procedure

Dec 4, 2005

I know a sql stored procedure can return >1 tables. How can I use .Net 2.0 to read these tables one at a time, for example the first one could iterate Forum entries and the second one all internal links used in these forums... The idea is to use fewer backtrips to the sql server?

View 2 Replies View Related

Stored Procedure Referencing Moved Tables

Mar 12, 2001

Hi everyone

I am totally confused, please help me. I am new to this

I am trying to split one DB (A) into two databases original (A) and new-Blank(B) by moving some tables from DB (A) to the new DB (B) however some of the tables has FK and Stored procedures referencing other tables that need to stay in DB (B), The questions are

1. after scritping these tables while they are in DB (A), and runing the script in DB (B) to re-create them, do I delete these table from DB (A), and the FK that references them.

2. What shall I do with the stored procedures. Turn them into trigers or else if turn them into trigers, in which DB should the triggers run (DB A of DB B),if these are becoming triggers do I delete the stored procedures from DB (A)

Any reply is appreciated

View 2 Replies View Related

Stored Procedure To List Values From Different Tables

Jan 19, 2006

I have several tables that are related with the same primary key that I need to make a consolidated list of values for. The schema of each table is similar but some have more or less fields than others. I want to make a list of all table names, field names, and the value of that field. I can select all tables included from sysobjects. The existence of the key is probable but not guaranteed in all tables. The basic table format is: Table1------ID Field1 Field2 Field31 0.0 4.1 3.92 0.5 1.3 0.23 7.1 8.8 9.3 Table2------ID Field10 0.41 3.32 2.73 5.7 Table3------ID Field1 Field22 2.4 4.63 4.3 8.1 Format of the result set:(specifying ID = 2) Table_Name Table_Field ValueTable1 Field1 0.5Table1 Field2 1.3Table1 Field3 0.2Table2 Field1 2.7Table3 Field1 2.4Table3 Field2 4.6

View 8 Replies View Related







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