Solved

stored procedure sqlserver

Posted on 2016-07-18
5
60 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 11

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now