473,237 Members | 1,281 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,237 software developers and data experts.

insert with stored procedure - no errors, but no insert

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,EnclosedGar age,
Pool,Comments,Picture1,Picture2,Picture3) values
(@id,@Projectname,@PropertyAddress,@ProjectBudget,
@PropertyType,@NumberBedrooms,@NumberBathrooms,
@LandSqFeet,@InteriorSqFeet,@Heating,@AirCondition ing,
@EnclosedGarage,@Pool,@Comments,@Picture1,@Picture 2,
@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
Feb 19 '06 #1
6 2321

Are you using the right connection string?

That is, are you looking in the right database for the inserted record?
SandySears wrote:
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,EnclosedGar age,
Pool,Comments,Picture1,Picture2,Picture3) values
(@id,@Projectname,@PropertyAddress,@ProjectBudget,
@PropertyType,@NumberBedrooms,@NumberBathrooms,
@LandSqFeet,@InteriorSqFeet,@Heating,@AirCondition ing,
@EnclosedGarage,@Pool,@Comments,@Picture1,@Picture 2,
@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

Feb 19 '06 #2
Yes. In the form load, I am populating a combo box using another stored
procedure which works.

"John Bailo" wrote:

Are you using the right connection string?

That is, are you looking in the right database for the inserted record?
SandySears wrote:
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,EnclosedGar age,
Pool,Comments,Picture1,Picture2,Picture3) values
(@id,@Projectname,@PropertyAddress,@ProjectBudget,
@PropertyType,@NumberBedrooms,@NumberBathrooms,
@LandSqFeet,@InteriorSqFeet,@Heating,@AirCondition ing,
@EnclosedGarage,@Pool,@Comments,@Picture1,@Picture 2,
@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

Feb 19 '06 #3
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.

"John Bailo" wrote:

Are you using the right connection string?

That is, are you looking in the right database for the inserted record?
SandySears wrote:
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,EnclosedGar age,
Pool,Comments,Picture1,Picture2,Picture3) values
(@id,@Projectname,@PropertyAddress,@ProjectBudget,
@PropertyType,@NumberBedrooms,@NumberBathrooms,
@LandSqFeet,@InteriorSqFeet,@Heating,@AirCondition ing,
@EnclosedGarage,@Pool,@Comments,@Picture1,@Picture 2,
@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

Feb 19 '06 #4

Just to be double sure, I would set a breakpoint at:
conn.ConnectionString = connectString
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
conn.Open()

inside the try/catch.

SandySears wrote: 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.

"John Bailo" wrote:
Are you using the right connection string?

That is, are you looking in the right database for the inserted record?
SandySears wrote:
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,EnclosedGar age,
Pool,Comments,Picture1,Picture2,Picture3) values
(@id,@Projectname,@PropertyAddress,@ProjectBudget,
@PropertyType,@NumberBedrooms,@NumberBathrooms,
@LandSqFeet,@InteriorSqFeet,@Heating,@AirCondition ing,
@EnclosedGarage,@Pool,@Comments,@Picture1,@Picture 2,
@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

Feb 19 '06 #5
SandySears wrote:
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. <snip> cmd.CommandType = CommandType.StoredProcedure
Dim paramname As New SqlClient.SqlParameter
paramname.ParameterName = "@ProjectName"
paramname.DbType = DbType.String
paramname.Size = txtProjectName.Text.Length
<snip to SP definition> @ProjectName nvarchar(100),


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
Feb 20 '06 #6
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

"John Bailo" wrote:

Just to be double sure, I would set a breakpoint at:
>>> conn.ConnectionString = connectString
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
>>> conn.Open()


inside the try/catch.

SandySears wrote:
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.

"John Bailo" wrote:
Are you using the right connection string?

That is, are you looking in the right database for the inserted record?
SandySears wrote:
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,EnclosedGar age,
Pool,Comments,Picture1,Picture2,Picture3) values
(@id,@Projectname,@PropertyAddress,@ProjectBudget,
@PropertyType,@NumberBedrooms,@NumberBathrooms,
@LandSqFeet,@InteriorSqFeet,@Heating,@AirCondition ing,
@EnclosedGarage,@Pool,@Comments,@Picture1,@Picture 2,
@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

Feb 20 '06 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Larry | last post by:
I know how to detect errors if a stored procedure is used, by adding a ReturnValue parameter to the command object and then in the stored procedure having a RETURN @@ERROR But, If I am using...
7
by: Alex Vorobiev | last post by:
hi there, i am using sql server 7. below is the stored procedure that is giving me grief. its purpose it two-fold, depending on how it is called: either to return a pageset (based on page...
0
by: Rhino | last post by:
I've written several Java stored procedures now (DB2 V7.2) and I'd like to write down a few "best practices" for reference so that I will have them handy for future development. Would the...
4
by: Rhino | last post by:
Is it possible for a Java Stored Procedure in DB2 V7.2 (Windows) to pass a Throwable back to the calling program as an OUT parameter? If yes, what datatype should I use when registering the...
6
by: harborboy76 | last post by:
Hi, I am trying to insert a large number of rows into a table inside a SPL. But every time, I run the SPL, the table is locked because of the INSERT. When I tried to issue a COMMIT, right after...
2
by: Eli | last post by:
Hi all We currently have a strange problem with calling a Stored Procedure (SQL Database) in our C# Project. The only error I get is "System error" which says a lot :) Background: We have...
3
by: mahajanvit | last post by:
Hi one and all I got this problem during my project. So in order to solve this I made a very small application. I am trying to insert using SP and sqldatasource control. I know that while using...
2
by: fperri | last post by:
I am using SQL Server 2005 and SQL Server Management Studio Express. I'm new to stored procedures and I was creating this one to test out the BULK INSERT sql command. When I execute it says...
0
by: SOI_0152 | last post by:
Hi all! Happy New Year 2008. Il hope it will bring you love and happyness I'm new on this forum. I wrote a stored procedure on mainframe using DB2 7.1.1 and IBM language c. Everything works...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.