asked on

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

ASKER CERTIFIED SOLUTION

Nakul Vachhrajani

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PeterBaileyUk

ASKER

I got beyond that by adding:
cmdInsertSmmt.ExecuteNonQuery()
now its saying at that point
{"Procedure or function usp_UpdateVehCat has too many arguments specified."}

PeterBaileyUk

ASKER

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

PeterBaileyUk

ASKER

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

PeterBaileyUk

ASKER

It occurred to me that the question was answered so am closing off thank you