471,066 Members | 1,214 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,066 software developers and data experts.

Stored procedure help for VB .NET Win App

I have a very simple Insert Stored Procedure. When I run it from the Server Explorer, it inserts the records just fine. However, when I try to call it from my form, it does not update the table. The weird thing is that it does return one row effected and if I try it again, I am told my primary key is not unique (meaning the first record is in there somewhere). When I exit my app, my table is just the same as it was before the inserts. Do I need to "save" or "update" the table before closing a connection? Thanks

Expand|Select|Wrap|Line Numbers
  1. CREATE PROCEDURE dbo.InsertNew
  2.     (
  3.     @A       nchar(10),
  4.     @B      nvarchar(50),
  5.     @C       nvarchar(50),
  6.     @D     nvarchar(50),
  7.     @E         nvarchar(50),
  8.     @F           nvarchar(50),
  9.     @G           nvarchar(50),
  10.     @H       nvarchar(50)
  11.     )
  12. AS
  13. INSERT INTO Users
  14.     (
  15.         A,
  16.         B,
  17.         C,
  18.         D,
  19.         E,
  20.         F,
  21.         G,
  22.         H
  23.     )
  24.     VALUES
  25.     (
  26.     @A,
  27.     @B,
  28.     @C,
  29.     @D,
  30.     @E,
  31.     @F,
  32.     @G,
  33.     @H
  34.     )
  35.     RETURN
  36.  
  37.  
  38.  
  39.  
  40.  
  41. Dim connection As SqlConnection
  42. connection = New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True;User Instance=True")
  43. connection.Open()
  44. Dim command As SqlCommand = New SqlCommand("InsertNew", connection)
  45. command.CommandType = CommandType.StoredProcedure
  46.  command.Parameters.AddWithValue("@A", "99")
  47.  command.Parameters.AddWithValue("@B", "test")
  48.  command.Parameters.AddWithValue("@C", "test")
  49.  command.Parameters.AddWithValue("@D", "test")
  50.  command.Parameters.AddWithValue("@E", "test")
  51.  command.Parameters.AddWithValue("@F", "test")
  52.  command.Parameters.AddWithValue("@G", "test")
  53.  command.Parameters.AddWithValue("@H", "test")
  54. Dim rows
  55. rows = command.ExecuteNonQuery
  56. connection.Close()
  57.  
  58.         If (rows = 1) Then
  59.            MessageBox.Show("Create new user SUCCESS!")
  60.         Else
  61.             MessageBox.Show("Create new user FAILED!")
  62.         End If
  63.  
  64.  
May 4 '07 #1
2 894
Bumping for response
May 10 '07 #2
Motoma
3,237 Expert 2GB
This may have to do with some database settings, dealing with Transactions and Auto-commit settings.

I don't know much beyond that.
May 23 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by Rhino | last post: by
2 posts views Thread by Dino L. | last post: by
2 posts views Thread by jed | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.