stored procedure sqlserver

I was trying an alternative way to update a table column but it doesnt update but i cannot see why. draccess looks ok and loops around the dataset in access.

the parameters from draccess has record data.

the chars all match

this is the call in vb.net i wonder if you could point me in the right direction

I have this SP


'stored P
USE [Dictionary]
GO
/****** Object:  StoredProcedure [dbo].[usp_UpdateVehCat]    Script Date: 18/07/2016 19:02:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_UpdateVehCat]

@ClientCodeIn nvarchar(50)
, @VehCatIn nvarchar(Max)

AS
BEGIN
UPDATE dbo.TblWords
SET VehCategory=@VehCatIn
WHERE ClientCode= @ClientCodeIn
END

'vb.net
   Public Sub UpdateVehicleCat()
        Dim StrClientName As String
        Dim x As Boolean
        Dim con As New SqlConnection
        Dim cmd As New SqlCommand
        Dim dtRecordsFromAccessSmmt As DataTable

        Dim VehCatValue As String
        Using cnAccess As New OleDb.OleDbConnection("Provider = Microsoft.Jet.OLEDB.4.0; Data Source=N:\Data\SMMT\smmt master.mdb")
            Using cmdSelect As New OleDb.OleDbCommand
                cmdSelect.Connection = cnAccess
                cnAccess.Open()

                cmdSelect.CommandText = "SELECT [MVRIS CODE], [Vehicle Category Code] FROM smmt;"

                Dim dtAdpt As New OleDb.OleDbDataAdapter(cmdSelect)
                dtRecordsFromAccessSmmt = New DataTable
                dtAdpt.Fill(dtRecordsFromAccessSmmt)
            End Using
        End Using

        '************
        Dim StrProcName As String = "usp_UpdateVehCat"
        Using cnSqlSmmt As New SqlClient.SqlConnection("Data Source=MAIN-PC\SQLEXPRESS;Initial Catalog=Dictionary;Integrated Security=True;MultipleActiveResultSets=True")

            Using cmdInsertSmmt As New SqlCommand(StrProcName, cnSqlSmmt)
                cmdInsertSmmt.CommandType = CommandType.StoredProcedure
                cnSqlSmmt.Open()
                Dim y As Long

                y = dtRecordsFromAccessSmmt.Rows.Count
                For Each drAccessRecord As DataRow In dtRecordsFromAccessSmmt.Rows ' each smmt veh cat record



                    cmdInsertSmmt.Parameters.AddWithValue("@ClientCodeIn", drAccessRecord("MVRIS CODE"))
                    cmdInsertSmmt.Parameters.AddWithValue("@VehCatIn", drAccessRecord("Vehicle Category Code"))

                Next

            End Using
            cnSqlSmmt.Close()
        End Using

    End Sub

Open in new window

PeterBaileyUkAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Nakul VachhrajaniConnect With a Mentor Technical Architect, Capgemini IndiaCommented:
It looks like the "cmdInsertSmmt" SqlCommand is being built, but is not being executed. You can use the BeginExecuteNonQuery method (https://msdn.microsoft.com/en-us/library/ca56w9se(v=vs.110).aspx). If I am not mistaken in reading the code, it should be executed just before you call cnSqlSmmt.Close()
0
 
PeterBaileyUkAuthor Commented:
I got beyond that by adding:
cmdInsertSmmt.ExecuteNonQuery()
 now its saying at that point
{"Procedure or function usp_UpdateVehCat has too many arguments specified."}
0
 
PeterBaileyUkAuthor Commented:
it looks ok
ALTER PROCEDURE [dbo].[usp_UpdateVehCat]

@ClientCodeIn nvarchar(50)
, @VehCatIn nvarchar(Max)

AS
BEGIN
UPDATE dbo.TblWords
SET VehCategory=@VehCatIn
WHERE ClientCode Like @ClientCodeIn
END

Open in new window

0
 
PeterBaileyUkAuthor Commented:
I moved away from the stored procedure as it wasnt working

I did the alternative but it wont run it says the parameters are already declared so not having much luck with this part of learning:


 For Each drAccessRecord As DataRow In dtRecordsFromAccessSmmt.Rows ' each smmt veh cat record

                    '********************************************************************************************************

                    cmdInsertSmmt.CommandText = "UPDATE dbo.TblWords SET VehCategory = @VehCategory WHERE ClientCode = @ClientCode "

                    cmdInsertSmmt.Parameters.AddWithValue("@ClientCode", drAccessRecord("MVRIS CODE"))
                    cmdInsertSmmt.Parameters.AddWithValue("@VehCategory", drAccessRecord("Vehicle Category Code"))
                    cmdInsertSmmt.ExecuteNonQuery()



                Next

Open in new window


I did run the sql manually in sqlserver and it did an update fine with the insert statement and values manually added
0
 
PeterBaileyUkAuthor Commented:
It occurred to me that the question was answered so am closing off thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.