.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Help!, Problem with creating my store procedure.

Posted By:      Posted Date: August 29, 2010    Points: 0   Category :ASP.Net
I have two tables. Product_Table with ProductID as the primary key. My other table, ProductSKU_Table which has SKU as the primary key and ProductID as the foreign key. I am trying to create a store procedure to insert into both tables. How do I insert the primary key from the Product_Table into the foreign key of the ProductSKU_Table? This is what I have so far:ALTER PROCEDURE [dbo].[sp_addNewProduct] ( @productName varchar(50) = null, @shortDescription varchar(100) = null, @longDescription varchar(4000) = null, @active bit = null, @category int = null, @sku varchar(11), @skuDescription varchar(200) = null, @orginalPrice money = null, @sellingPrice money = null, @specialPrice money = null, @qtyOnHand int = null, @qtyAtSupplier int = null, @imagePath varchar(200) = null, @releaseDate datetime ) AS BEGIN DECLARE @lastIDInserted int INSERT INTO Product_Table(Name, ShortDescription, LongDescription, Active, CategoryId) VALUES(@productName, @shortDescription, @longDescription, @active, @category) SET @lastIDInserted = @@IDENTITY INSERT INTO ProductSKU_Table(ProductId, SKU, Description, OrginalPrice, SellingPrice, SpecialPrice, QtyOnHand, QtyAtSupplier, ReleaseDate, imagePath, CreatedDate, ModifiedDate) VALUES(@lastIDInserted, @sku, @skuDescription, @orginalPrice, @sellingPrice, @specialP

View Complete Post

More Related Resource Links

Insert into and Store procedure problem

Hello I am trying to create store procedure wich will insert data in temp table CREATE PROCEDURE GetDataForUpdate AS if exists(select * from sys.objects where name='GetDataForUpdate_temp_tb') begin DROP TABLE GetDataForUpdate_temp_tb end go WITH cte as (select Sp.[Item No_], Sp.[Starting Date], It.[No_], It.[Manufacturer Code], It.[Description], Sp.[Unit Price], row_number () over (partition by Sp.[Item No_] order by Sp.[Starting Date] desc) as rn from dbo.[Main-db$Sales Price] AS Sp JOIN dbo.[Main-db$Item] AS It ON Sp.[Item No_] = It.[No_] WHERE Sp.[Sales Code]='RETAIL' AND Sp.[Item No_] LIKE 'I%' ), cte2 as ( SELECT [Item No_],SUM(ISNULL(Quantity, 0)) AS Qty FROM [Main-db$Item Ledger Entry] WHERE [Item No_] LIKE 'I%' AND ( [Location Code] = 'WH-AB-#2' ) GROUP BY [Item No_] ) SELECT cte.[Manufacturer Code],cte.[Description], CONVERT(int, cte2.[Qty]) AS 'Qty',CONVERT(int, cte.[Unit Price]) AS 'Unit Price' INTO GetDataForUpdate_temp_tb FROM cte JOIN cte2 ON cte.[Item No_]=cte2.[Item No_] WHERE rn = 1 ORDER BY cte.[Item No_],[Starting Date] But after execute, lefts only this part of query in store procedure: set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER PROCEDURE [dbo].[GetDataForUpdate] AS if exists(select * from sys.objects where name='GetDataForUpdate_temp_tb') begin DROP TABLE GetDa

Problem with creating table by procedure


I have code like this

CREATE PROCEDURE prc_CreateDrawsMatrixTable (
	@GameName nvarchar(64),
	@Numbers int=0)
	IF @Numbers<1 
	DECLARE @Command nvarchar(4000)
	SET @Command='CREATE TABLE [dbo].[tblDrawsMatrix_'+@GameName+'] [Index] [int] IDENTITY(1,1) NOT NULL,
				 [DrawIndex] [int] NOT NULL,[DrawDate] [date] NOT NULL,'
	DECLARE @Counter int=1
	WHILE @Counter<=@Numbers 
			SET @Command=@Command + '[L' + LTRIM(STR(@Counter)) + '] [SMALLINT] NULL DEFAULT(0)'
			IF @Counter<@Numbers 

Store Procedure Debugging Problem.

I am trying to debug the store procedure in SQL server 2005 . I have windows 7 64-Bit on my client computer and server is running SQL server 2005 standard edition. I am trying to debug store procedures in visual studio 2005 .  I followed the below steps. 

I have followed all the articales however still i am unable to debug the store proc in my application . I did the following.

1: My local machnice user has administrator rights on my local machine and in SQL server it has sys admin rights. 

2: On my client machine i am running windwos 7 64-bit version , database server is on windows Server 2003 64-bit edition. 

3: I configured the remote debugger on windows server then i start the remote debugger 64 bit .

Now when i try to debug the store procedure i got the following error message.

Unable to start T-SQL Debugging. Could not connect to computer eicsrv01 . The Microsoft visual studio Remote Debugging

Monitor on the remote computer cannot connect to the local computer . A firewall may be preventing communication via 

Creating A Stored Procedure Which Searches Team Names



I'm have on my web page a text search box which I want users to type in there favourite football team and this will display a gridview of the teams with the replica shirts I offer.

This is where I thought about creating a stored procedure to carry out this task.

I looked online for ideas but I not found anything as yet.

If anyone done anything similar to my request please let me know.

Unable to select any stored procedure while creating TableAdapters in wizard


I'm using VS 2008 and SQL 2008.

I have created the tables and the stored procedures in SQL 2008.

In VS 2008, I created DataSet1.xsd in App_Code and created the connectionString in web.config file.

Then when I go into the DataSet1.xsd and try to add a TableAdapter, strange things happened.

First I chose the data connection, then selected "Use existing stored procedure", then there was nothing listed in the dropdownlists (in Select, Insert, Update, or Delete). 

I'm sure the connectionString is correct because if I choose "Use SQL statement" and type in a "select * from mytable1", the TableAdapter can be created without any problem.

Any suggestions?

Problem while creating process memory dumps.

Hi, I have following piece of code: Imports System Imports System.IO Imports System.Runtime.InteropServices Imports Microsoft.Win32.SafeHandles Imports System.ComponentModel Module Module1 <Flags()> Friend Enum NativeMiniDumpType MiniDumpNormal = &H0 MiniDumpWithDataSegs = &H1 MiniDumpWithFullMemory = &H2 MiniDumpWithHandleData = &H4 MiniDumpFilterMemory = &H8 MiniDumpScanMemory = &H10 MiniDumpWithUnloadedModules = &H20 MiniDumpWithIndirectlyReferencedMemory = &H4 MiniDumpFilterModulePaths = &H80 MiniDumpWithProcessThreadData = &H100 MiniDumpWithPrivateReadWriteMemory = &H200 MiniDumpWithoutOptionalData = &H400 MiniDumpWithFullMemoryInfo = &H800 MiniDumpWithThreadInfo = &H1000 MiniDumpWithCodeSegs = &H2000 MiniDumpWithoutAuxiliaryState = &H4000 MiniDumpWithFullAuxiliaryState = &H8000 End Enum <DllImport("dbghelp.dll", CharSet:=CharSet.Auto, SetLastError:=True)> Friend Function MiniDumpWriteDump(ByVal hProcess As IntPtr, ByVal processId As Int32, ByVal hFile As SafeFileHandle, ByVal dumpType As NativeMiniDumpType, ByVal exceptionParam As IntPtr, ByVal userStreamParam As IntPtr,

Creating DLL file for Extended Stored procedure in C#

Hi All,   Is there any way that I can create a dll file for Extended stored procedure(SQL server) in C# ?   I an able to create that in VC++. in VC++ there is DLLMain which serve as entry point and a paramter can be passed to it using Extended stored procedure. But since C# does not have any such entry point. Hence is there any way that I can achieve this?   Any pointers to this will be highly appreciated.   Thanks Sid

New Webpart Creating Problem

hi, after the successfully created one webpart in visual studio2008,wen i m creating second webpart then it is showing following error. Error 1 The feature name WebParts already exists in SharePoint. You need to rename the feature before solution deployment can succeed.  I tried to rename the webpart name and deleted from C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\FEATURES location.But still the same error exist.  

problem for using webservice (sql parameter stored Procedure)

i want to put my database layer in the web service. This works fine BUT there is a problem when i am access these methods GetSPData(Byval arg As SqlComman, argCmdText as string, ByVal argSqlConnection as SqlConnection) As DataTable I want to acces the aforesaid method in my Desktop application and i have to send SqlCommand as parament. When i try to acces this (GetSPData) method the follow exception occured "There was an error generating the XML document." Now the problem is to converting DataTable in xmlDocument Or xml node (i dont know about it very much). Can anyone tell me How to send SqlCommand as parament ?? How to convert DataTable into XmlDocument?? When i try to test this method the following Error occurs " test form is only available for methods with primitive types as parameters. " The following are my Web Service Method code in which i try to Get Data Using Stored Procedure and then convert DataTable into XmlDocument and return. Dim XmlDoc As New XmlDocument Dim ds As New DataSet Dim oDBLibrary As New DataBaseLibrary.DatabaseClass Dim dt As DataTable = oDBLibrary.GetSPData(objCommand1, cmdText, msqlConnection) ds.Tables.Add(dt) Dim sw As New StringWriter ds.Tables(0).WriteXml(sw) sw.Close() Dim tableXml As String = sw.ToString XmlDoc.Load(tableXml) Return XmlDoc  Now can anyone tell me what is wrong with that c

namespace of fields problem when creating form template from xml?

I created a form template from a predefined xml, where I do not have any namespace. Later I added more fields. The output xml of the form is for the fields from the xml they have not prefix, for those added later, they have prefix my. I already have them all done. How can I make them consistent to have prefix my? Is there any options in InfoPath to turn on or off the prefix my?   thanks,

SSIS - Call a Store procedure

Can someone show me an example of how to call a store procedure that checks for empty fields in a SQL database table, by returning a value when fields are empty. then send an email to the adminstratore.

How to use Store Procedure to execute the Function Of COM DLL (the DLL is Coded By C#)

Hi All, I find a question. How to use Store Procedure to execute the function of Dll? I tried as the steps: 1. Code a dll 2. Register the dll by "regasm XXX.dll /codebase" 3. Use the dll in SP But when I do it in step 3, I face some error, which is "Invalid class string" and the error ID is "0x800401F3" Could you help me solve this problem? Thank you very much.

Store Procedure

How can i insert and delete a data with use of one store procedure ........... SP File:- ALTER PROCEDURE dbo.One @id int, @Name Varchar(50), @select char(1) AS begin  if @select ='i' insert into j (id,Name) values (@id ,@Name) end begin if @select ='d' delete from j  where id= @id end RETURN How can i called tis two query

Creating Login Page Problem

I doing a login page using visual c# and encounter some problem on it. my database got Username, Password, and UserType I want to create a login page redirect the user to different page based on their roles. It show that there is an error on the code (line 24). Any idea on create a login page that redirect the user to different page based on their roles?It shows this message :   this type or namespace name 'DataView' could not be found(are you missing a using directive or an assembly reference)Here the code, protected void Page_Load(object sender, EventArgs e) { string connectionString = "Data Source =.\\SQLEXPRESS;" + "AttachDbFilename=\"C:\\Program Files\\Microsoft SQL Server\\MSSQL10.SQLEXPRESS\\MSSQL\\DATA\\UserDB.mdf\";" + "Integrated Security=True;" + "Connect Timeout=30;" + "User Instance=True;"; System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connectionString ); System.Data.SqlClient.SqlCommand cmd = conn.CreateCommand(); conn.Open(); } protected void Button1_Click(object sender, EventArgs e) { SqlDataSource sds = new SqlDataSource(); sds.SelectParameters.Add("Username", TypeCode.String, this.TextBox1.Text); sds.

Cursor problem with Stored Procedure and PHP

I'm struggling with accessing the result set of a stored procedure using the SQL Server Driver for PHP 1.1. I've already got a few of them running, but this one is special in that it has an XML input parameter which is parsed into a table variable. This table variable is then used within a select statement. When I execute the SP within Management Studio, it runs fine and returns exactly one result set with multiple rows. But when I execute it from PHP, I get the error message "Executing SQL directly; no cursor. ". I've tried every cursor type out there, the only one that doesn't fail is SQLSRV_CURSOR_FORWARD, but with that I'm not getting any results at all. The original SP is enormous, so I have stripped it down as far as I could so it still throws the error. If I remove the bold part, my PHP code executes fine.   USE [testsite] GO SET  ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[getCrossSellingItems]     @InputIds XML AS     SET NOCOUNT ON ;     DECLARE @tblInputIds TABLE     (         Id INT     );     INSERT INTO @tblInputIds (Id)     (         SELECT         T.Item.value('@Id', 'INT') FROM @InputIds.nodes('/Root/Item') AS T(

Problem Creating Asymmetric Key from Executable File

In trying to create an asymmetric key from an executable file, I get an error if the path length to the dll is very long, while a short path length works perfectly.  Does anyone know what the limitation on the path length is?

How I can to check if Store Procedure altered(been called )?

I have ado.net connection to SP               conn = new SqlConnection("Data Source=DY;Initial Catalog=DMSA;Integrated Security=True");                 conn.Open();                   // 1.  create a command object identifying                 //     the stored procedure                 SqlCommand cmd = new SqlCommand("dbo.UpdateCorporationFile", conn);                   // 2. set the command object so it knows                 //    to execute a stored procedure                 cmd.CommandType = CommandType.StoredProcedure;                 cmd.Parameters.Add(new SqlParameter("@count_create_C_W", DBNull.Value));        &nb
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend