使用 VB.NET 执行存储过程 [英] Execute a stored procedure using VB.NET

问题描述

这是我的程序

ALTER PROCEDURE sp_addUser
     @UserName nvarchar(50),    
     @Prenom nvarchar(50),  
     @Nom nvarchar(50), 
     @Mail nvarchar(50),    
     @Password char(8), 
     @Addresse nvarchar(100),   
     @Ville nvarchar(50),   
     @Province nvarchar(50),    
     @PostalCode char(6),
     @Pays nvarchar(50),    
     @AnimalGenre nvarchar(50), 
     @NomAnimal nvarchar(50),   
     @Race nvarchar(50) 
AS
BEGIN
   INSERT INTO Client
   VALUES (@UserName,@Prenom,@Nom,@Mail,@Password,@Addresse,@Ville,@Province,@PostalCode,@Pays,@AnimalGenre,@NomAnimal,@Race);
END 

我认为这对于存储过程来说没问题

I think this ok for the stored Procedure

现在是在数据库中添加值的代码

Now the code to add value in Data Base

Sub sp_addUser()

    Dim intRowsAff As Integer

    lblErrMsg.Text = ""
    lblRecsAff.Text = ""

    Dim connectionString As String = WebConfigurationManager.ConnectionStrings("BecsEtMuseauxSQL").ConnectionString
    Dim con As SqlConnection = New SqlConnection(connectionString)

    Dim cmd As New SqlCommand("sp_addUser", con)

    cmd.CommandType = CommandType.StoredProcedure
    cmd.Parameters.Add("@UserName", SqlDbType.VarChar, 50).Value = txtUserName.Text
    cmd.Parameters.Add("@Prenom", SqlDbType.VarChar, 50).Value = txtPrenom.Text
    cmd.Parameters.Add("@Nom", SqlDbType.NVarChar, 50).Value = txtNom.Text
    cmd.Parameters.Add("@Mail", SqlDbType.NVarChar, 50).Value = txtMail.Text
    cmd.Parameters.Add("@Password", SqlDbType.Char, 8).Value = txtPass.Text
    cmd.Parameters.Add("@Addresse", SqlDbType.NVarChar, 100).Value = txtAdresse.Text
    cmd.Parameters.Add("@Ville", SqlDbType.NVarChar, 50).Value = txtVille.Text
    cmd.Parameters.Add("@Province", SqlDbType.NVarChar, 50).Value = txtProvince.Text
    cmd.Parameters.Add("@PostalCode", SqlDbType.Char, 6).Value = txtPostal.Text
    cmd.Parameters.Add("@Pays", SqlDbType.NVarChar, 50).Value = txtPays.Text
    cmd.Parameters.Add("@AnimalGenre", SqlDbType.NVarChar, 50).Value = rblAnimal.Text
    cmd.Parameters.Add("@NomAnimal", SqlDbType.NVarChar, 50).Value = txtAnimal.Text
    cmd.Parameters.Add("@Race", SqlDbType.NVarChar, 50).Value = txtRace.Text

    Try
        cmd.Connection.Open()
        intRowsAff = cmd.ExecuteNonQuery()
    Catch ex As Exception
        lblErrMsg.Text = ex.Message & ex.Source
    End Try
    lblRecsAff.Text = intRowsAff & " record(s) inserted"
    cmd.Connection.Close()
End Sub

在我执行方法之后 VS 说我必须为过程 sp_addUser 指定很多参数

After I execute the method VS said that I have to much specified arguments for the procedure sp_addUser

我不明白为什么会产生错误！

I don't understand why this generate error!

推荐答案

您发布的代码和存储过程似乎是同步的.

The code and stored procedure you posted appear to be in sync.

这强烈表明数据库中的存储过程与您在代码中期望的不同步.建议您检查您发布的内容实际上是DB中的存储过程.

That strongly suggests that the stored procedure in the Database is out of sync with what you expect in your code. Suggest you check what you posted is actually the stored procedure in DB.

另外注意:你不应该在你的 proc 前面加上 'sp_' ，除非它自然存在于 master 中.

Also Note: you should not prefix your proc with 'sp_' unless it natually resides in master.

这篇关于使用 VB.NET 执行存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！