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
Solved

i am typint this procedure and i get an error that my SP  is expectint to receive a parameter that I am not supplying. What is the format for my DataTable?

Posted on 2009-03-30
16
248 Views
Last Modified: 2013-11-26
My DataTable has the name of the parameters in the column name. What must I do to get the DataAdapter.Update execute the Store Procedure and recognize the parameters?
Dim cmd As New SqlClient.SqlCommand
        Dim dt As New DataTable, ds As New DataSet, da As New SqlClient.SqlDataAdapter
        Dim i, x As Integer
        Dim rw As DataRow
        Dim colDataType As New System.Data.DataColumn
 
 
        For i = 0 To arItems.GetUpperBound(1)
            If Not IsNothing(arItems) Then
                dt.Columns.Add(arItems(0, i))
            End If
        Next
        dt.AcceptChanges()
        For i = 1 To arItems.GetUpperBound(0)
            rw = dt.NewRow
            For x = 0 To dt.Columns.Count - 1
                rw(x) = arItems(i, x)
            Next
            dt.Rows.Add(rw)
        Next
        dt.TableName = "Items"
        With cmd
            .Connection = DataConn
            .CommandText = strSP
            .CommandType = CommandType.StoredProcedure
            DataConn.Open()
            For i = 0 To dt.Columns.Count - 1
                If i = 0 Then
                    .Parameters.Add(arItems(0, i), SqlDbType.Int)
                Else
                    .Parameters.Add(arItems(0, i), SqlDbType.VarChar)
                End If
            Next
            da.InsertCommand = cmd
            i = da.Update(dt)
            MsgBox(i)
            '.Dispose()
        End With
 
        DataConn.Close()
        Return True

Open in new window

0
Comment
Question by:Yves Mellet
  • 6
  • 4
  • 3
  • +1
16 Comments
 
LVL 39

Expert Comment

by:appari
ID: 24025213
can you post the stored procedure source
0
 

Author Comment

by:Yves Mellet
ID: 24025296
ALTER PROCEDURE [STF].[ERRORLOG_INSERT]
      @ErrorNumber INT,
      @ErrorDescription VARCHAR(2048),
      @Activity_Type VARCHAR(10),
      @Operation VARCHAR(10),
      @Update_User VARCHAR(50)

AS
BEGIN

INSERT INTO STF.Error_Log
            (ErrorNumber
           ,Activity_Type
           ,Operation
           ,ErrorDescription
           ,Insert_User)
     VALUES
            (@ErrorNumber
           ,@Activity_Type
           ,@Operation
           ,@ErrorDescription
           ,@Update_User)
END
0
 
LVL 39

Expert Comment

by:appari
ID: 24025334
also can you post sample data from arItems array
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:Yves Mellet
ID: 24025489
      Dim arErrors(6, 4) As String
        arErrors(0, 0) = "@ErrorNumber"
        arErrors(0, 1) = "@ErrorDescription"
        arErrors(0, 2) = "@Activity_Type"
        arErrors(0, 3) = "@Operation"
        arErrors(0, 4) = "@Update_User"
        arErrors(1, 0) = 9999
        arErrors(1, 1) = "Error 1"
        arErrors(1, 2) = "Activity 1"
        arErrors(1, 3) = "Operation1"
        arErrors(1, 4) = "ymellet"
        arErrors(2, 0) = 9998
        arErrors(2, 1) = "Error 2"
        arErrors(2, 2) = "Activity 2"
        arErrors(2, 3) = "Operation2"
        arErrors(2, 4) = "ymellet"
        arErrors(3, 0) = 9997
        arErrors(3, 1) = "Error 3"
        arErrors(3, 2) = "Activity 3"
        arErrors(3, 3) = "Operation3"
        arErrors(3, 4) = "ymellet"
        arErrors(4, 0) = 9996
        arErrors(4, 1) = "Error 4"
        arErrors(4, 2) = "Activity 4"
        arErrors(4, 3) = "Operation4"
        arErrors(4, 4) = "ymellet"
        arErrors(5, 0) = 9995
        arErrors(5, 1) = "Error 5"
        arErrors(5, 2) = "Activity 5"
        arErrors(5, 3) = "Operation5"
        arErrors(5, 4) = "ymellet"
        arErrors(6, 0) = 9994
        arErrors(6, 1) = "Error 6"
        arErrors(6, 2) = "Activity 6"
        arErrors(6, 3) = "Operation6"
        arErrors(6, 4) = "ymellet"

0
 

Author Comment

by:Yves Mellet
ID: 24040565
Any taker?
If the name of the sp parameters are not supposed to be the column names of the DataTable I am passing to the sp, where do they go?
0
 
LVL 48

Expert Comment

by:jpaulino
ID: 24040721
You're indicating the parameters but not the values
You should do something like
.Parameters.Add(arItems(0, i), SqlDbType.Int).Value = 9999
0
 

Author Comment

by:Yves Mellet
ID: 24041008
Then there is no reason to pass the entire DataTable. The idea is to update the table with multiple records on one call to the stored procedure.
0
 
LVL 48

Expert Comment

by:jpaulino
ID: 24043880
For that you need to pass an Xml document
"You need to pass an Xml document that lists all the rows that need to be updated to the stored procedure just once and then use OPENXML to join against the table you intend to update."
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_22150393.html
 
Still I don't understand why delete this question ... if you decide to use other method, like an xml document, you will have to do the loop in the SQL Server (with a function or other way). It is more easy to do this way (by code)
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24045214
Or you can upgrade to SQL Server 2008 and use new Table Value Parameter feature, where you can pass in a DataTable.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24045237
jpaulino,

>>if you decide to use other method, like an xml document, you will have to do the loop in the SQL Server (with a function or other way). It is more easy to do this way (by code)<<
Thank you for the quote.  But there is nocessity to loop.  That is the whole point of using Xml.  Incidentally, with SQL Server 2005 you can use the new Xml methods instead of OPENXML()
0
 
LVL 48

Expert Comment

by:jpaulino
ID: 24045270
Hi acperkins,
To be honest with you I never used XML methods and I have only searched for this topic. Thanks for the clarification!
0
 
LVL 39

Accepted Solution

by:
appari earned 250 total points
ID: 24045576
I was unable to followup the question for the last four days,
the problem is everything else is correct execpt you are not assigning the source column name.

try changing

            For i = 0 To dt.Columns.Count - 1
                If i = 0 Then
                    .Parameters.Add(arItems(0, i), SqlDbType.Int)
                Else
                    .Parameters.Add(arItems(0, i), SqlDbType.VarChar)
                End If
            Next

to

                For i = 0 To dt.Columns.Count - 1
                    If i = 0 Then
                        .Parameters.Add(arErrors(0, i), SqlDbType.Int)
                        .Parameters(i).SourceColumn = arErrors(0, i)
                    Else
                        .Parameters.Add(arErrors(0, i), SqlDbType.VarChar)
                        .Parameters(i).SourceColumn = arErrors(0, i)
                    End If
                Next
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24045661
>>But there is nocessity to loop.<<
And this should have read:
But there is no necessity to loop.
0
 

Author Closing Comment

by:Yves Mellet
ID: 31564659
That's exactly what I was missing. Thank you.
0
 
LVL 48

Expert Comment

by:jpaulino
ID: 24049104
LOL that's funny
0
 

Author Comment

by:Yves Mellet
ID: 24049150
I would like to make sure appari gets the points for his answer to this question. I closed this question thinking it couldn't be done that way but appari got it.
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

856 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