Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 270
  • Last Modified:

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?

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
Yves Mellet
Asked:
Yves Mellet
  • 6
  • 4
  • 3
  • +1
1 Solution
 
appariCommented:
can you post the stored procedure source
0
 
Yves MelletAuthor Commented:
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
 
appariCommented:
also can you post sample data from arItems array
0
Industry Leaders: 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!

 
Yves MelletAuthor Commented:
      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
 
Yves MelletAuthor Commented:
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
 
jpaulinoCommented:
You're indicating the parameters but not the values
You should do something like
.Parameters.Add(arItems(0, i), SqlDbType.Int).Value = 9999
0
 
Yves MelletAuthor Commented:
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
 
jpaulinoCommented:
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
 
Anthony PerkinsCommented:
Or you can upgrade to SQL Server 2008 and use new Table Value Parameter feature, where you can pass in a DataTable.
0
 
Anthony PerkinsCommented:
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
 
jpaulinoCommented:
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
 
appariCommented:
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
 
Anthony PerkinsCommented:
>>But there is nocessity to loop.<<
And this should have read:
But there is no necessity to loop.
0
 
Yves MelletAuthor Commented:
That's exactly what I was missing. Thank you.
0
 
jpaulinoCommented:
LOL that's funny
0
 
Yves MelletAuthor Commented:
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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 6
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now