insert with stored procedure - no errors, but no insert

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to use a stored procedure to insert a record using VS 2005, VB
and SQL Server Express. The code runs without errors or exceptions, and
returns the new identifer in the output parameters. It returns my error text
message in another output parameter as "ok", which is the value that is set
in the stored procedure prior to doing the insert. It returns my var for
@@rowcount as 1. However, the record does not get into the table.

Even after creating the parameters the long way, it still does not work. I
hope someone can help -- thank you.

Sandy

VB code:

Private Sub btnAddProject_Click(ByVal sender As Object, ByVal e As
System.EventArgs) Handles btnAddProject.Click
Dim Picture1 As Byte()
Dim Picture2 As Byte()
Dim Picture3 As Byte()
Dim connectString As String = My.Settings.RemodelGenieConnectionString
Dim conn As New SqlClient.SqlConnection
conn.ConnectionString = connectString
conn.Open()
Dim cmd As New SqlClient.SqlCommand("dbo.usp_AddProject", conn)

Dim m As New Misc
If FileExists(txtPicture1Path.Text) Then
Picture1 = m.ReadBinary(txtPicture1Path.Text)
Else
Picture1 = Nothing
End If
If FileExists(txtPicture2Path.Text) Then
Picture2 = m.ReadBinary(txtPicture2Path.Text)
Else
Picture2 = Nothing
End If
If FileExists(txtPicture3Path.Text) Then
Picture3 = m.ReadBinary(txtPicture3Path.Text)
Else
Picture3 = Nothing
End If
cmd.CommandType = CommandType.StoredProcedure
Dim paramname As New SqlClient.SqlParameter
paramname.ParameterName = "@ProjectName"
paramname.DbType = DbType.String
paramname.Size = txtProjectName.Text.Length
paramname.Value = txtProjectName.Text.ToString
paramname = cmd.Parameters.Add(paramname)
Dim paramaddr As New SqlClient.SqlParameter
paramaddr.ParameterName = "@PropertyAddress"
paramaddr.DbType = DbType.String
paramaddr.Size = txtPropertyAddress.Text.Length
paramaddr.Value = txtPropertyAddress.Text
paramaddr = cmd.Parameters.Add(paramaddr)
Dim parambudget As New SqlClient.SqlParameter
parambudget.ParameterName = "@ProjectBudget"
parambudget.DbType = DbType.Currency
parambudget.Value = System.Convert.ToDecimal(txtProjectBudget.Text)
parambudget = cmd.Parameters.Add(parambudget)
Dim paramtype As New SqlClient.SqlParameter
paramtype.ParameterName = "@PropertyType"
paramtype.DbType = DbType.String
paramtype.Size = cmbPropertyType.Text.Length
paramtype.Value = cmbPropertyType.Text
paramtype = cmd.Parameters.Add(paramtype)
Dim parambed As New SqlClient.SqlParameter
parambed.ParameterName = "@NumberBedrooms"
parambed.DbType = DbType.Int16
parambed.Value = System.Convert.ToInt16(txtNumberBedrooms.Text)
parambed = cmd.Parameters.Add(parambed)
Dim parambath As New SqlClient.SqlParameter
parambath.ParameterName = "@NumberBathrooms"
parambath.DbType = DbType.Int16
parambath.Value = System.Convert.ToInt16(txtNumberBathrooms.Text)
parambed = cmd.Parameters.Add(parambath)
Dim paramland As New SqlClient.SqlParameter
paramland.ParameterName = "@LandSqFeet"
paramland.DbType = DbType.Double
paramland.Value = System.Convert.ToDecimal(txtLandSqFeet.Text)
paramland = cmd.Parameters.Add(paramland)
Dim paramint As New SqlClient.SqlParameter
paramint.ParameterName = "@InteriorSqFeet"
paramint.DbType = DbType.Double
paramint.Value = System.Convert.ToDecimal(txtInteriorSqFeet.Text)
paramint = cmd.Parameters.Add(paramint)
Dim paramheat As New SqlClient.SqlParameter
paramheat.ParameterName = "@Heating"
paramheat.DbType = DbType.Boolean
If chkHeating.Checked Then
paramheat.Value = True
Else
paramheat.Value = 0
End If
cmd.Parameters.Add(paramheat)
Dim paramair As New SqlClient.SqlParameter
paramair.ParameterName = "@AirConditioning"
paramair.DbType = DbType.Boolean
If chkAirConditioning.Checked Then
paramair.Value = 1
Else
paramair.Value = 0
End If
cmd.Parameters.Add(paramair)
Dim paramgarage As New SqlClient.SqlParameter
paramgarage.ParameterName = "@EnclosedGarage"
paramgarage.DbType = DbType.Boolean
If chkEnclosedGarage.Checked Then
paramgarage.Value = 1
Else
paramgarage.Value = 0
End If
cmd.Parameters.Add(paramgarage)
Dim parampool As New SqlClient.SqlParameter
parampool.ParameterName = "@Pool"
parampool.DbType = DbType.Boolean
If chkPool.Checked Then
parampool.Value = 1
Else
parampool.Value = 0
End If
cmd.Parameters.Add(parampool)
Dim paramcomments As New SqlClient.SqlParameter
paramcomments.ParameterName = "@Comments"
paramcomments.DbType = DbType.String
paramcomments.Value = txtComments.Text
paramcomments.Size = txtComments.Text.Length
paramcomments = cmd.Parameters.Add(paramcomments)
Dim parampic1 As New SqlClient.SqlParameter
parampic1.ParameterName = "@Picture1"
parampic1.DbType = DbType.Object
parampic1.IsNullable = True
If Picture1 IsNot Nothing Then
parampic1.Size = Picture1.Length
End If
parampic1.Value = Picture1

cmd.Parameters.Add(parampic1)
Dim parampic2 As New SqlClient.SqlParameter
parampic2.ParameterName = "@Picture2"
parampic2.DbType = DbType.Object
parampic2.IsNullable = True
If Picture2 IsNot Nothing Then
parampic2.Size = Picture2.Length
End If
parampic2.Value = Picture2
cmd.Parameters.Add(parampic2)
Dim parampic3 As New SqlClient.SqlParameter
parampic3.ParameterName = "@Picture3"
parampic3.DbType = DbType.Object
parampic3.IsNullable = True
If Picture3 IsNot Nothing Then
parampic3.Size = Picture3.Length
End If
parampic3.Value = Picture3
cmd.Parameters.Add(parampic3)

'parampic1 = cmd.Parameters.Add("@Picture1", SqlDbType.Image).Value
= Picture1
'Dim parampic2 As New SqlClient.SqlParameter
'parampic2 = cmd.Parameters.Add("@Picture2", SqlDbType.Image).Value
= Picture2
'Dim parampic3 As New SqlClient.SqlParameter
'parampic3 = cmd.Parameters.Add("@Picture3", SqlDbType.Image).Value
= Picture3
Dim paramid As New SqlClient.SqlParameter
paramid = cmd.Parameters.Add("@id", SqlDbType.UniqueIdentifier)
paramid.Direction = ParameterDirection.Output
Dim paramrows As New SqlClient.SqlParameter
paramrows = cmd.Parameters.Add("@rowcount_var", SqlDbType.Int)
paramrows.Direction = ParameterDirection.Output
Dim paramerr As New SqlClient.SqlParameter
paramerr = cmd.Parameters.Add("@error_text", SqlDbType.NVarChar, 255)
paramerr.Direction = ParameterDirection.Output
Dim result As Boolean
Try
result = cmd.ExecuteNonQuery()
Catch ex As SqlClient.SqlException
MsgBox(ex.Message)
End Try
MsgBox("error text = " &
cmd.Parameters("@error_text").Value.ToString & vbCr & "id = " &
cmd.Parameters("@id").Value.ToString & vbCr & "rowcount = " &
cmd.Parameters("@rowcount_var").Value.ToString)
conn.Close()
conn = Nothing

End Sub



stored procedure code:


ALTER PROCEDURE dbo.usp_AddProject
/*
(
@parameter1 int = 5,
@parameter2 datatype OUTPUT
)
*/
(
@ProjectName nvarchar(100),
@PropertyAddress nvarchar(150),
@ProjectBudget money,
@PropertyType nvarchar(50),
@NumberBedrooms smallint,
@NumberBathrooms smallint,
@LandSqFeet float,
@InteriorSqFeet float,
@Heating bit = 0,
@AirConditioning bit = 0,
@EnclosedGarage bit = 0,
@Pool bit = 0,
@Comments nvarchar(2000),
@Picture1 image = null,
@Picture2 image = null,
@Picture3 image = null,
@id uniqueidentifier = default out,
@rowcount_var int = 0 out,
@error_text nvarchar(255) ='ok' out
)
AS
/* SET NOCOUNT ON */
declare @error_var int
set @id = newid()
set @error_text = N'ok'
/* insert */

insert into Projects (ProjectId,ProjectName,
PropertyAddress,ProjectBudget,PropertyType,
NumberBedrooms,NumberBathrooms,LandSqFeet,
InteriorSqFeet,Heating,AirConditioning,EnclosedGarage,
Pool,Comments,Picture1,Picture2,Picture3) values
(@id,@Projectname,@PropertyAddress,@ProjectBudget,
@PropertyType,@NumberBedrooms,@NumberBathrooms,
@LandSqFeet,@InteriorSqFeet,@Heating,@AirConditioning,
@EnclosedGarage,@Pool,@Comments,@Picture1,@Picture2,
@Picture3)
set @error_var = @@error;
set @rowcount_var = @@rowcount;
if @error_var <> 0
begin
select @error_text = (select description from sysmessages where
error=@error_var)
end


RETURN
 
Are you using the right connection string?

That is, are you looking in the right database for the inserted record?
 
Yes. In the form load, I am populating a combo box using another stored
procedure which works.
 
To be more clear, I am using the same connection string to populate the combo
box. I am looking at the database in Server Explorer. If I test the stored
procedure with Server Explorer by right-clicking on the stored procedure and
selecting execute, a record gets inserted.
 
Just to be double sure, I would set a breakpoint at:

and check the value of connectString in the Watch area.

Then, I see that you have a try/catch block, and it's set up to throw a
MsgBox, I would actually use a Debug.WriteLine() here and I would also
put a break point inside the catch just to make sure an exception wasn't
being thrown.

Also, put the

inside the try/catch.
 
SandySears said:
I am trying to use a stored procedure to insert a record using VS
2005, VB and SQL Server Express. The code runs without errors or
exceptions, and returns the new identifer in the output parameters.
It returns my error text message in another output parameter as "ok",
which is the value that is set in the stored procedure prior to doing
the insert. It returns my var for @@rowcount as 1. However, the
record does not get into the table.
Click to expand...
cmd.CommandType = CommandType.StoredProcedure
Dim paramname As New SqlClient.SqlParameter
paramname.ParameterName = "@ProjectName"
paramname.DbType = DbType.String
paramname.Size = txtProjectName.Text.Length
Click to expand...

@ProjectName nvarchar(100),
Click to expand...

Try setting the length of each parameter to the same length as declared in
the stored procedure.

<moan>I'm sure that somewhere inside it could tell you "size of parameter
<ParameterName, size> does not match declaration <size in declaration>", but
do they want to help you get your program working easily? Noooo....</moan>

Andrew
 
John,

I really appreciate your taking the time to look at my code and try to help
me. However, due to time constraints, I have decided to abandon this
approach for this project and use an Access database instead.

Thanks again!

Sandy
 
Back
Top