• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 91
  • Last Modified:

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

0
PeterBaileyUk
Asked:
PeterBaileyUk
  • 4
1 Solution
 
Nakul VachhrajaniTechnical 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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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