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
6  2332 
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
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
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
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
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
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
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
| | |
