Running Stored Procedure in VB.Net but not working

[thepooh]

I created a stored procedure during the development of the vb.net windows application.
When I run the program, there's no error. But when I check in sqlserver7.0, the data is not inserted. I've already made sure that in the SQL Enterprise Manager, Permission, the Exec box is checked. Below is the code which I wrote:

Dim CreditCard_ACFConn As New SqlConnection("workstation id=""MyServer"";packet size=4096;user id=sa;data source=""MyServer"";persist security info=False;initial catalog=CreditCard_ACF")

Dim InsertCHCmd2 As New SqlCommand("spInsertCardHolderNonAgent", CreditCard_ACFConn)
InsertCHCmd2.CommandType = CommandType.StoredProcedure
Dim parmCHName2 As New SqlParameter("@CardHolderName", SqlDbType.VarChar, 255)
parmCHName2.Value = txtCHName.Text
InsertCHCmd2.Parameters.Add(parmCHName2)
CreditCard_ACFConn.Open()
InsertCHCmd2.ExecuteNonQuery()
CreditCard_ACFConn.Close()

If I replace the Stored Procedure name with SQL command, the same code can run perfectly!

Dim CreditCard_ACFConn As New SqlConnection("workstation id=""MyServer"";packet size=4096;user id=sa;data source=""MyServer"";persist security info=False;initial catalog=CreditCard_ACF")

Dim InsertCHCmd2 As New SqlCommand("Insert into CardHolder (CardHolderName) Values (@CardHolderName)", CreditCard_ACFConn)
InsertCHCmd2.CommandType = CommandType.Text
Dim parmCHName2 As SqlParameter = InsertCHCmd2.Parameters.Add("@CardHolderName", SqlDbType.VarChar, 255)
parmCHName2.Value = txtCHName.Text
CreditCard_ACFConn.Open()
InsertCHCmd2.ExecuteNonQuery()
CreditCard_ACFConn.Close()

Please help. thanks

[ammar_ar]

hi,

the problem is from your stored procedure so try to use different datatype because some time its effect the data


also check your code that your are correctly executing the coomand itself


and tell me what will happen


Ammar

[coolbiz]

When debugging stored procedure, the first thing to do is test your SP directly (via Query Analyzer or Enterprise Manager). Once you're assured that works, then you can start debugging your code.

Use the step through feature (set a break point at the beggining of the routine that contains the code you mentioned above) and evaluate each input that you're passing to the stored procedure. Also, it does help to trap and display error(s) (TRY...CATCH...END TRY).

Good Luck,
-Cool Bizs

[riscoh]

Try placing a source column on the command parameter

try this...................

Dim parmCHName2 As New SqlParameter("@CardHolderName", SqlDbType.VarChar, 255,"CardHolderName")