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

stored procedure sqlserver

Posted on 2016-07-18
5
76 Views
Last Modified: 2016-07-19
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
Comment
Question by:PeterBaileyUk
  • 4
5 Comments
 
LVL 13

Accepted Solution

by:
Nakul Vachhrajani earned 500 total points
ID: 41717782
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
 

Author Comment

by:PeterBaileyUk
ID: 41718242
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
 

Author Comment

by:PeterBaileyUk
ID: 41718244
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
 

Author Comment

by:PeterBaileyUk
ID: 41718982
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
 

Author Closing Comment

by:PeterBaileyUk
ID: 41719038
It occurred to me that the question was answered so am closing off thank you
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

790 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