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
(
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:
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:
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:
Norkle
0 Points
41 Posts
Stored Procedure not updating or inserting
May 08, 2012 06:24 PM|LINK
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
danjackson10...
Member
88 Points
14 Posts
Re: Stored Procedure not updating or inserting
May 08, 2012 07:03 PM|LINK
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:
This should call your stored procedure and "submit" all data entered on to your web form (added as parameters).
Hope this helps somewhat.
Norkle
0 Points
41 Posts
Re: Stored Procedure not updating or inserting
May 09, 2012 03:27 PM|LINK
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()
Allen Li - M...
Star
10411 Points
1196 Posts
Re: Stored Procedure not updating or inserting
May 10, 2012 06:31 AM|LINK
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
If you have any feedback about my replies, please contact msdnmg@microsoft.com
Microsoft One Code Framework
jamshed alam
Member
319 Points
105 Posts
Re: Stored Procedure not updating or inserting
May 10, 2012 06:36 AM|LINK
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?????
sriramabi
Contributor
4351 Points
1277 Posts
Re: Stored Procedure not updating or inserting
May 10, 2012 07:03 AM|LINK
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