how to pass vb.net 2 dimensional array to a sql stored procedure

I am developing a online shoing cart for a ISP company. according to the business when we add some products we keep set of records for products such as domain name, address , tel, hosting option and so on so my basket contains a list of items as follow

i would prefer to convert these in to datatable and pass to a stored procedure

item     tel           name        add1      add2       add3      postcode      domainname        connectionspeed            .......

1       01254      snfanfasf   jsnsjd    sdsdf                      sn2 3ds        sfsdfsdf.co.uk       

2    123123123      wf                                                                            sdsfdsfs                   sdfsdfsf

3       100152        sdf         sdf          sd

 

Inserting procedure update 2 table mainly and i use identity field as primary key. Table1 store order no and user account no .... Details and return identity as order no. for that order no as foreign key i insert item details to table2 from the above data table.Can any body guide me hoe to write the stored procedure for this scenario. I have read some examples but could not sort out properly with my case.Any one please helps me to write this. Also I have knowledge of using cursor inside stored procedure.

thank you in advance.

0
Pathiya007
8/12/2008 9:52:48 AM
asp.net.getting-started 91979 articles. 4 followers. Follow

4 Replies
918 Views

Similar Articles

[PageSpeed] 52

Sql Server 2008 has a new table data column type you can pass datatables to. 

http://www.code-magazine.com/article.aspx?quickid=0807041&page=2


Silverlight-help
Vb Tips
Space Coast .Net User Group
0
Ken
8/12/2008 11:00:20 AM

I am using sql server 2005. any further ideas please

0
Pathiya007
8/12/2008 12:32:57 PM

the above problem...

 if i have convert this data in to a 2 dimensional array.

then is there any way of passing that to sql stored procedure?

 

0
Pathiya007
8/14/2008 9:19:21 AM

the simplest way is to convert your data into xml string and pass it to the SP as varchar paramter

in the SP you can convert the xml string into table

use curour to loop in the table and do what ever you want

 

how to covert the xml string in to table in SqlServer 2005

use this a SP i wrote to insert multi recourds

 

-- =============================================

-- Author: <Author,,Name>

-- Create date: <Create Date,,>

-- Description: <Description,,>

-- =============================================

ALTER PROCEDURE [dbo].[ES_AddBooksMultiFromXmlSource]

@pXMLDocument varchar(1000),

@pErroItemsList xml-- varchar(1000) output

AS

BEGIN

SET NOCOUNT ON;

-- Declare Table For Error List

DECLARE @ErrorList TABLE

(

ProductName varchar(255),

ProductDescription varchar(1000),

UnitPrice decimal(18, 3),

AvailableQty int,

ImagePath varchar(255),

Active bit,

ProductType bit,

ISBN varchar(50),

Auther varchar(255),

Publisher varchar(255)

)

 

-- Declare Temp Variables

DECLARE @ProductName varchar(255)

DECLARE @ProductDescription varchar(1000)

DECLARE @UnitPrice decimal(18, 3)

DECLARE @AvailableQty int

DECLARE @ImagePath varchar(255)

DECLARE @Active bit

DECLARE @ProductType bit

DECLARE @ISBN varchar(50)

DECLARE @Auther varchar(255)

DECLARE @Publisher varchar(255)

declare @DocHandler int

set @DocHandler = 1

EXEC sp_xml_preparedocument @DocHandler output, @pXMLDocument

DECLARE Books_cursor CURSOR FOR

select * from OPENXML (@DocHandler, '/Products/Books', 10)

with (

ProductName varchar(255),

ProductDescription varchar(1000),

UnitPrice decimal(18, 3),

AvailableQty int,

ImagePath varchar(255),

Active bit,

ProductType bit,

ISBN varchar(50),

Auther varchar(255),

Publisher varchar(255)

)

OPEN Books_cursor

FETCH NEXT FROM Books_cursor

INTO @ProductName,

@ProductDescription,

@UnitPrice,

@AvailableQty,

@ImagePath,

@Active,

@ProductType,

@ISBN,

@Auther,

@Publisher

while @@FETCH_STATUS = 0

BEGIN

-- Validation Here

-- 1 : Book name is unique

if exists (select 1 from Products where ProductName = LTRIM(RTRIM(@ProductName)) )

BEGIN

insert into @ErrorList values

(

@ProductName,

@ProductDescription,

@UnitPrice,

@AvailableQty,

@ImagePath,

@Active,

@ProductType,

@ISBN,

@Auther,

@Publisher

)

END

-- 2 : 2. ISBN is unique

else if exists ( select 1 from Books where ISBN = LTRIM(RTRIM(@ISBN)))

BEGIN

insert into @ErrorList values

(

@ProductName,

@ProductDescription,

@UnitPrice,

@AvailableQty,

@ImagePath,

@Active,

@ProductType,

@ISBN,

@Auther,

@Publisher

)

END

-- 3 : Quantity available should be greater than 0 if the book is active.

else if ( @Active = 1 And @AvailableQty <= 0)

BEGIN

insert into @ErrorList values

(

@ProductName,

@ProductDescription,

@UnitPrice,

@AvailableQty,

@ImagePath,

@Active,

@ProductType,

@ISBN,

@Auther,

@Publisher

)

END

else

BEGIN

EXEC dbo.ES_InsertNewBook 0,

@ProductName,

@ProductDescription,

@UnitPrice,

@AvailableQty,

@ImagePath,

@Active,

@ISBN,

@Auther,

@Publisher

END

-- Insert Here

 

FETCH NEXT FROM Books_cursor

INTO @ProductName,

@ProductDescription,

@UnitPrice,

@AvailableQty,

@ImagePath,

@Active,

@ProductType,

@ISBN,

@Auther,

@Publisher

END

CLOSE Books_cursor

DEALLOCATE Books_cursor

-- Release Resourses

EXEC sp_xml_removedocument @DocHandler

 

set @pErroItemsList = ( select * from @ErrorList for xml auto, type)

 

/*

<Products>

<Books>

<ProductName>"How To"</ProductName>

<ProductDescription>"How To"</ProductDescription>

<UnitPrice>"2.200"</UnitPrice>

<AvailableQty>"4"</AvailableQty>

<ImagePath>"c:\asdf.jpg"</ImagePath>

<Active>"1"</Active>

<ProductType>0</ProductType>

<ISBN>"456-ABC"</ISBN>

<Auther>"Samer"</Auther>

<Publisher>"Noor Group</Publisher>

</Books>

</Products>

*/

END

 

Regards

Mohammad Jaber

0
mjaber7
8/14/2008 9:36:16 AM
Reply:

Similar Artilces:

VB.NET SQL stored procedure: procedure has no parameters and arguments were supplied
VB.NET SQL stored procedure: procedure has no parameters and arguments were supplied Please assist me:This erorr message is produce when calling the stored procedure in vb.netProcedure AutomateMatterNumber has no parameters and arguments were supplied."MS SQL 2000stored procedure:*/CREATE PROCEDURE dbo.AutomateMatterNumber ASDECLARE @nextMtr AS BIGINTDECLARE @dtToday AS DATETIMEIF NOT EXISTS(SELECT * FROM tempMatter WHERE DATEDIFF(dd,DateSet,GETDATE())=0 )BEGINDELETE FROM tempMatter-- incase there are some old recordsSELECT TOP 1 @nextMtr= CONVERT(BIGINT, MatterNumber) + 1 ,@dtTod...

SQL Server Stored Procedures/VB.Net
IS there a way inside code for vb.net to programmatically change sorting in a stored procedure???Reason is I need a two way sort and did not want to have to write a SP for each way ....If so Do you have an example..... Most likely the easiest way, is, when you retrieve the data from the sProc, put it in a DataView.. That way, you can sort The DataView by any field, ASC or DESC that you wantDavid WierMCP/ASPInsiderASPNet101.com - where to look first!Control Grouper - easily control properties for multiple controls with one control!Calendar Express - The Best HTML Calendar Generator on th...

how to insert sql stored procedure into vb.net code??
I am trying to create a paged web site from my database file. for that i found an appropriate sql code. the only thing i don't know is how to declare this sql inside the vb. is it supposed to be in the same .aspx file or in another one?? pls help if you know. thank you so much. here's the sql stored procedure: ------------------------------------------------------------ CREATE PROCEDURE sp_PagedItems ( @Page int, @RecsPerPage int ) AS -- We don't want to return the # of rows inserted -- into our temporary table, so turn NOCOUNT ON SET NOCOUNT ON --Cr...

VB.net connect to SQL Express Stored Procedure
Dear Experts, i am writing a VB.net window application. I created a SQL Express database inside the project. Inside the SQLExpress database, i created a stored procedure. I would like to know how to call that SP. Below is what i did and it raised a error "Missing provider" I tried to add "'Provider=SQLOLEDB;'", the missing provider error message gone but another error raised as below.. No error message available, result code: DB_E_ERRORSOCCURRED(0x80040E21). Sub PushtoDB() Dim con As OleDbConnection Dim cmd As OleDbCommand = New OleD...

Problem with Oracle Stored Procedures using ODP.NET in VB.NET (VS 2003)
This is the stored procedure,Procedure RetrieveReservations(pProperty In Varchar2,pResHeaderId In Varchar2,pEmail In Varchar2,pPasswd In Varchar2,ResultSet In Out ResCursor);This is the old .asp code (part of it)objConn.ConnectionString = Application("BackEndConnection_ConnectionString")objConn.Open objComm.ActiveConnection = objConn objComm.CommandText = "internet_package.RetrieveReservations"objComm.CommandType = adCmdStoredProc objComm.Parameters.Append objComm.CreateParameter("pProperty", adVarChar , adParamInput, 8, Request.QueryString("propid"))objComm.Parameters.Append objComm.Crea...

.Net 2.0 Web application using Vb.net is unable to create object of another dll writen in C# .net 2.0
Hi, I habe created one ASP.net web application using Vb.net which is adding reference of dlls written in C# and .net 2.0. But whenever trying to create object of referencing dll, it is throwing error :Object reference does not set to an object. But, locally it is working fine,. In the server i have deployed the .aspx files and dll files in the bin. There were already an web.config in the server which is of .net 1.1. But checked it is retrieving value for that web.config correctly. Should i have to deply any other files and if not what can be the solution for this? Please help. Thanks, So...

Sending SMS using vb.net or C#.net using vb.net or c#.net
Hi  My requirement is I hav one csv file with these fields id,mobilenum,messgae,status.intiallu staus is 0. once i read the all fileds and take that mobile number.using tat mobile number  i need to send sms .after sending sms i shuld change status as 1 How to send sms thru coding (please dont provide any links.if it is provide also please give working links becox i checked codeproject .i didnt get any nice link. and also provide the how to update the status field im csv file   Thank ssandhya   To send SMS, you need some third party SMS providers. if you consul...

Using sql Stored procedure from vb.net to output data
Can using a stored procedure from vb.net I get a report converted to xml for printing purposes.....ANy coding samples...

Can somebody help
I Seem to be going nuts ! I know it has to be simple but I am missing the link. I haven't used crystal reports with asp.net before. So pardom me if my problem sounds silly. My requirement is very simple actually. I am using asp.net 1.1 with vb.net in vs.net 2003 with the inbuilt crystal reports control. I have to connect to a sql server 2000 database to extract rows from a stored procedure which accepts a parameter and the report has to be in Mail label format. I referred various books and web sites and according to that , added a crystal reports file to the project and selected the store...

How to execute stored procedure from vb.net CodeBehind instead of a sql statement?
I've learned how to exeucte a simple sql statement from my vb.net codebehind (see sample below), but I want to go a step further and learn how to execute a stored procedure from my code behind.  I've already written the stored proc, just don't know how to reference it and the parameters in my codebehind..Dim conn As SqlConnectionDim comm As SqlCommandDim reader As SqlDataReaderDim connectionString As String = ConfigurationManager.ConnectionStrings("xxx").ConnectionStringconn = New SqlConnection(connectionString) comm = New SqlCommand("INSERT into xxx (a...

Differences between .net 1, .net 1.1, .net 2.0 and .net 3.0 #2
Hi, This seems to be a common question, but i havent got an answer yet:(Can, any one please explain me the differences between these versions.If you keep your feet firmly on the ground, you'll have trouble putting on your pants! There are too many differences for one email - - from 1.0 to 1.1 (not a whole lot of real change, other than fixes, at least compared to 1.1 to 2.0) With 2.0, there were many new declarative controls, with many new ideas added in With 3.0, it's a superset of 2.0 - instead of replacing the installation completely, it just 'added on' new functionality - I would...

Getting only the first paragraph of data from sql server 2005 in vb.net 2.0
Hi all, I have a table in sql server which consists of two colums. column 1 contain "id" integer and column 2 contain "body" varchar(max). For example: id - 1 body - French Industrial production sharply decreased 1.5% month-on-month in November, reversing from a 2.1% increase seen in October, the statistical office Insee said in a report Thursday. Economists were looking for a 0.6% fall. Production in the automotive industry fell steeply 5.3% versus 7.5% rise in October, while energy output declined 2% from 2.1% recorded in the previous month. In the construction...

How to transfer a GUID created using vb.net into a SQL database using a stored procedure
I am able to create a guid using: Public Function GetGUID() As String ' Returns a new GUID Return System.Guid.NewGuid.ToString End Function however when I try to add this to a parameter using the following: Me.cmdSpAddOptions.Parameters("@QuoteDetailID").Value = GetGUID() I get an error, I have also tried this: Dim uidQuoteDetail As String = GetGUID() Dim myuid = New System.Guid(uidQuoteDetail) Me.cmdSpAddOptions.Parameters("@QuoteDetailID").Value = myuid but get the error "Object must implement IConvertible" A...

ADO.NET 2.0 - Three-tier application - connection string error
Using ASP.NET 2.0 beta, VS2005 beta 1, W2k3 server with IIS I try to build a three-tier app structure as followed: - the store procedure in SQL with a input parameter as Username and retrieve the UserId as output parameter. - the business logic (class) with connection string defined in web.config (“AppConnectionStringASPNETapp”) - the presentation aspx file after the user has logged in through login.aspx I have work out 2 scenarios with 2 different errors. Scenario 1: Here is the code is business logic (class) (catalog.cs) using System; using System.Data; usi...

Web resources about - how to pass vb.net 2 dimensional array to a sql stored procedure - asp.net.getting-started

Dimensional analysis - Wikipedia, the free encyclopedia
In engineering and science , dimensional analysis is the analysis of the relationships between different physical quantities by identifying their ...

Conversations with Doc are like multi-dimensional tennis.
... Searls, and after the game we went out for a bite to eat. And we had a long talk, as we always do. Conversations with Doc are like multi-dimensional ...

Three Dimensional Model Viewer Lite for iPhone 3GS, iPhone 4, iPhone 4S, iPhone 5, iPod touch (3rd generation) ...
Get Three Dimensional Model Viewer Lite on the App Store. See screenshots and ratings, and read customer reviews.

Ep 1 - The Super Dimensional Quantum Learning's Problems and Solutions Gametime Spectacular!! - YouTube ...
Ep. 1 - QUANTUM CONUNDRUM presents 'The Super Dimensional Quantum Learning's Problems and Solutions Gametime Spectacular!!' On this first episode, ...

AFL 2015: Demise of the one-dimensional tagger
Bernie Vince played an outstanding game in round eight against the Western Bulldogs.

Housing mix will save Sydney from 'one-dimensional' future, researcher says
Sydney's "misplaced" fixation on apartments risks squeezing out other types of housing that might make for a better city, according to the author ...

007 not stirred by 3-D, no plans for three-dimensional Bond film
Celebrating his 50th birthday, James Bond has been learning some new tricks but 3-D isn't one of them.

Chinese vice premier emphasizes multi-dimensional cooperation with North ...
Chinese vice premier emphasizes multi-dimensional cooperation with North ... People's Daily Online Liu said he hopes both sides will enhance ...

Apple Inc. : Patent Application Titled "Inkjet Printer for Printing on a Three-Dimensional Object and ...
By a News Reporter-Staff News Editor at Politics & Government Week According to news reporting originating from Washington, D.C., by VerticalNews ...

The one-dimensional Muslim
Why the United States needs the Muslim assassin

Resources last updated: 12/3/2015 3:58:47 PM