Stored Procedure not updating or inserting

I'm running a stored procedure with parameters that is not inserting one table and updating another table (two statements) . I am not getting any asp.net errors, and when testing in sql studio manager query window, it is working, but it isnt inserting/updating via my scripts:

procedure code:

USE [thedatabase]
GO
/****** Object: StoredProcedure [dbo].[sproRegRW] Script Date: 5/8/2012 6:42:52 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sproRegRW]
--this will go to the players table, so we are not going to test the userss table here, so no password
(

@compname VARCHAR(50) = NULL,
                @Address varchar(80) = NULL,
                @Address2 varchar(80) = NULL,
                @Email varchar(80) = NULL,
                @Web nvarchar(80) = NULL,
                @Phone varchar(17) = NULL,
                @Fax varchar(17) = NULL,
                @City varchar(60) = NULL,
                @State char(2) = NULL,
                @Zip char(12) = NULL,
                @CompDescription VARCHAR(500) = NULL,
                @BusLicense VARCHAR(20) = NULL,
                @CompLicense VARCHAR(20) = NULL,
    @RefName VARCHAR(50) = NULL,
             @RefNo VARCHAR(20) = NULL,
                @Parcel VARCHAR(60) = NULL,
    @home VARCHAR(20) = NULL,
    @qid int
)
AS

BEGIN
--update profiles set city = @Location,BusLicense = @BusLicense,CompWeb = @CompWeb,CompLicense = --@CompLicense,RefName = @RefName,RefNo = @RefNo,home = @home,Parcel = @Parcel where uid = @qid

INSERT INTO Company
(Compname,Address,Address2, Email,Web,Phone,Fax,City,State,Zip,CompDescription,BusLicense,CompLicense,Parcel)
VALUES (@compname,@Address, @Address2, @Email, @Web, @Phone,@Fax,@City,@State,@Zip,@CompDescription,@BusLicense,@CompLicense,@Parcel)
update Profiles set RefName = @RefName,RefNo = @RefNo, HomePage = @home where uid = @qid

END
RETURN

------------- code in my aspx.vb codefile that points to storeprocedure command:
.....

Dim MyConn As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnString"))

Dim MyCmd As New SqlCommand("sproRegRW", MyConn)

MyCmd.CommandType =CommandType.StoredProcedure

-------------
thanks in advance for your help
Nork

Re: Stored Procedure not updating or inserting

Hi Norkle,

I'm not sure if you haven't posted all of your code or not, so I will assume the VB you've posted is everything. I also assume you have a web form that allows the input of the data to be inserted into your database?

Assuming the above, you need to add your data to your parameters, such as:

MyCmd.Parameters.AddWithValue("@Address1", Server.HtmlEncode(tbAddress1.Text.Trim))

Of course, you will need to do this for all of your paramenters (I added in the HtmlEncode for some basic security, but you can do your own methodology to improve security of user entered text to your database).

You will also need to execute the SqlCommand before it will do anything, such as:

MyConn.Open()
MyCmd.ExecuteNonQuery()
MyConn.Close()

This should call your stored procedure and "submit" all data entered on to your web form (added as parameters).

Hope this helps somewhat.

Re: Stored Procedure not updating or inserting

I'm calling my AddMiscProf sub inside my button clickevent func to grab the textbox/ddlist data from the aspx page:

AddMiscProf(txtCompName.Text.Trim(),

txtAddress.Text.Trim(),

txtAddress2.Text.Trim(),..................

here's the AddMiscProf sub with the arguments representing all the textbox.ddlist vars:

Sub AddMiscProf(ByVal strCompName As String, ByVal strAddress As String, ByVal strAddress2 As String...

the AddMiscProf sub also intiates parameter objects:

Dim objParam1, objParam2, objParam3....

...and assigns the parameters , commands , stored procedure calls (with what you prescribed with the MyCmd.ExecuteNonQuery() ):

objParam1 = MyCmd.Parameters.Add("@compname", SqlDbType.VarChar)

objParam2 = MyCmd.Parameters.Add("@Address", SqlDbType.VarChar)

objParam3 = MyCmd.Parameters.Add("@Address2", SqlDbType.VarChar).....
....................

objParam1.Value = strCompName

objParam2.Value = strAddress

objParam3.Value = strAddress2 .......................
........................
Try

MyConn.Open()

MyCmd.ExecuteNonQuery()

MyConn.Close()

Re: Stored Procedure not updating or inserting

Hi, please set breakpoints and debug them to make sure strCompName, strAddress, strAddress2 have correct values. Additionally, there is an example on the following link, you can refer to it:

http://support.microsoft.com/kb/306574

Re: Stored Procedure not updating or inserting

check your stored procedure

INSERT INTO Company
(Compname,Address,Address2, Email,Web,Phone,Fax,City,State,Zip,CompDescription,BusLicense,CompLicense,Parcel)
VALUES (@compname,@Address, @Address2, @Email, @Web, @Phone,@Fax,@City,@State,@Zip,@CompDescription,@BusLicense,@CompLicense,@Parcel)
update Profiles set RefName = @RefName,RefNo = @RefNo, HomePage = @home where uid = @qid

where close, here u r inserting so why using where close where uid = @qid?????

Re: Stored Procedure not updating or inserting

hai

remove all null values in u r variable from stored procedure....

then check pls

@compname VARCHAR(50) ,
                @Address varchar(80) ,
                @Address2 varchar(80) ,
                @Email varchar(80) ,
                @Web nvarchar(80) ,
                @Phone varchar(17) ,
                @Fax varchar(17),
                @City varchar(60) ,.

thank u