Execute a stored procedure using VB.NET (update table)

Question

My SQL Server stored procedure is

ALTER PROCEDURE [dbo].[usp_updateBInfo]
   @WhatTheyDo nVARCHAR(max), 
   @WhereTheyDo nVARCHAR(max), 
   @ID varchar
AS
begin
    update tblBInfo
    set 
       WhatTheyDo = @WhatTheyDo,
       WhereTheyDo = @WhereTheyDo
    where 
       ID = @ID
end

My VB code is

  Dim SQLCONN As New SqlConnection
  SQLCONN = New SqlConnection("Data Source=xxxxxx;Initial Catalog=xxxxx;Integrated Security=True")

  Dim SQLCMD As New SqlCommand("usp_updateBInfo", SQLCONN)

  SQLCMD.CommandType = CommandType.StoredProcedure
  SQLCMD.Parameters.Add("@ID", SqlDbType.VarChar).Value = "1C485D2C-F34D-45CE-8694-C74445DD108D"
  SQLCMD.Parameters.AddWithValue("@WhatTheyDo", "abcdefg")
  SQLCMD.Parameters.Add("@WhereTheyDo", SqlDbType.NVarChar).Value = "abcdefg"

  SQLCMD.Connection.Open()
  SQLCMD.ExecuteNonQuery()

  SQLCMD.Connection.Close()

When I hit the "update" button, no change occurs, abcdefg is not passed to whattheydo or wheretheydo with primary key 1C485D2C-F34D-45CE-8694-C74445DD108D

May I ask where is wrong with my code? I'm pretty sure the button is linked to VB code.

Thanks for any suggestions!

Steve · Accepted Answer · 2014-02-24 07:32:26Z

4

You don't set the size of the parameter @ID, this means that only a single char is passed to the stored procedure, and, of course, no record will be found to update

In T-SQL, if you define a parameter NVARCHAR without a size and assign a string to it, is not like declaring and then assigning a string in VB.NET. The string will not be automatically expanded to whatever size the string is.

To correct the problem change your stored procedure to something like this

ALTER PROCEDURE [dbo].[usp_updateBInfo]
@WhatTheyDo nVARCHAR(max), @WhereTheyDo nVARCHAR(max), @ID nvarchar(30)
AS
   ....

or whatever size you have defined the field ID ( and use the correct type nvarchar/varchar)

edited Feb 24, 2014 at 7:32

answered Feb 23, 2014 at 22:07

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Execute a stored procedure using VB.NET (update table)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related