Solved

Passing DataTable to SP with SQL Server 2008 & .NET 1.1

Posted on 2012-12-27
6
642 Views
Last Modified: 2013-01-16
Hey experts,

we have a SP on SQL Server 2008 that takes a user-defined table type (a 2008 feature). Of course from .NET 4.0 (Visual Studio 2010), I'm able to call it normally passing to it a .NET DataTable.
Now the issue is that I need to call it from a .NET 1.1 application (VS 2003); is there anyway this can be done using some add-in?
0
Comment
Question by:mte01
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
6 Comments
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 38723432
have you tried passing .net datatable . I think that will work

Found a different way to go about doing it. This way uses the System.Data.SqlClient libraries to create a connection to the database, specify the stored procedure name, and then pass a parameter in as a DataTable that serves as the SQL Server user-defined table type.

using (SqlConnection conn = new SqlConnection(connStr)) {
    SqlCommand cmd = conn.CreateCommand();
    cmd.CommandType = System.Data.CommandType.StoredProcedure;
    cmd.CommandText = "dbo.usp_Test";
    cmd.Parameters.AddWithValue("@ids", dt);
    conn.Open();

    using (SqlDataReader sqlReader = cmd.ExecuteReader()) {
        DataTable retTbl = new DataTable();
        retTbl.Load(sqlReader);
    }
refer
http://stackoverflow.com/questions/10624394/sql-server-user-defined-table-type-and-net
0
 
LVL 3

Author Comment

by:mte01
ID: 38723471
>>pratima_mcs

.NET 1.1 does not have AddWithValue; it has an overload of .Add that takes name & value (without dbtype); we tried this, but it errored out at the .Add command with:

Index (zero based) must be greater than or equal to zero and less than the size of the argument list
0
 

Expert Comment

by:mmsi
ID: 38723625
You could try:

SqlParameter param = New SqlParameter();
param = cmd.Parameters.Add("@ids", SqlType.Structured);
param.Value = "your dataTable";

If you receive the message "sqlDBType.Structured" is not part of System.Data.SqlDbType

Then you may need to install a more recent version the the .net framework, because .Structured was added after 2003.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 3

Author Comment

by:mte01
ID: 38723639
>>mmsi

This was my point in the 1st place :) I cannot upgrade my current project from 1.1 because it's very large, and I need - if possible - some add-on library that would allow me to do this SP call to SQL Server 2008
0
 
LVL 3

Accepted Solution

by:
mte01 earned 0 total points
ID: 38768696
Apparently, there's no way to do it, but using .NET 3.5 (VS 2008) - it can't be done using .NET 1.1
0
 
LVL 3

Author Closing Comment

by:mte01
ID: 38782013
Came down to it after research
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
asp web application 3 39
Problem to Office 1 42
Access is denied error from google drive api v2 in asp.net 1 45
How can I use this function? 3 32
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question