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_C lick(ByVal sender As Object, ByVal e As
System.EventArg s) Handles btnAddProject.C lick
Dim Picture1 As Byte()
Dim Picture2 As Byte()
Dim Picture3 As Byte()
Dim connectString As String = My.Settings.Rem odelGenieConnec tionString
Dim conn As New SqlClient.SqlCo nnection
conn.Connection String = connectString
conn.Open()
Dim cmd As New SqlClient.SqlCo mmand("dbo.usp_ AddProject", conn)
Dim m As New Misc
If FileExists(txtP icture1Path.Tex t) Then
Picture1 = m.ReadBinary(tx tPicture1Path.T ext)
Else
Picture1 = Nothing
End If
If FileExists(txtP icture2Path.Tex t) Then
Picture2 = m.ReadBinary(tx tPicture2Path.T ext)
Else
Picture2 = Nothing
End If
If FileExists(txtP icture3Path.Tex t) Then
Picture3 = m.ReadBinary(tx tPicture3Path.T ext)
Else
Picture3 = Nothing
End If
cmd.CommandType = CommandType.Sto redProcedure
Dim paramname As New SqlClient.SqlPa rameter
paramname.Param eterName = "@ProjectNa me"
paramname.DbTyp e = DbType.String
paramname.Size = txtProjectName. Text.Length
paramname.Value = txtProjectName. Text.ToString
paramname = cmd.Parameters. Add(paramname)
Dim paramaddr As New SqlClient.SqlPa rameter
paramaddr.Param eterName = "@PropertyAddre ss"
paramaddr.DbTyp e = DbType.String
paramaddr.Size = txtPropertyAddr ess.Text.Length
paramaddr.Value = txtPropertyAddr ess.Text
paramaddr = cmd.Parameters. Add(paramaddr)
Dim parambudget As New SqlClient.SqlPa rameter
parambudget.Par ameterName = "@ProjectBudget "
parambudget.DbT ype = DbType.Currency
parambudget.Val ue = System.Convert. ToDecimal(txtPr ojectBudget.Tex t)
parambudget = cmd.Parameters. Add(parambudget )
Dim paramtype As New SqlClient.SqlPa rameter
paramtype.Param eterName = "@PropertyT ype"
paramtype.DbTyp e = DbType.String
paramtype.Size = cmbPropertyType .Text.Length
paramtype.Value = cmbPropertyType .Text
paramtype = cmd.Parameters. Add(paramtype)
Dim parambed As New SqlClient.SqlPa rameter
parambed.Parame terName = "@NumberBedroom s"
parambed.DbType = DbType.Int16
parambed.Value = System.Convert. ToInt16(txtNumb erBedrooms.Text )
parambed = cmd.Parameters. Add(parambed)
Dim parambath As New SqlClient.SqlPa rameter
parambath.Param eterName = "@NumberBathroo ms"
parambath.DbTyp e = DbType.Int16
parambath.Value = System.Convert. ToInt16(txtNumb erBathrooms.Tex t)
parambed = cmd.Parameters. Add(parambath)
Dim paramland As New SqlClient.SqlPa rameter
paramland.Param eterName = "@LandSqFee t"
paramland.DbTyp e = DbType.Double
paramland.Value = System.Convert. ToDecimal(txtLa ndSqFeet.Text)
paramland = cmd.Parameters. Add(paramland)
Dim paramint As New SqlClient.SqlPa rameter
paramint.Parame terName = "@InteriorSqFee t"
paramint.DbType = DbType.Double
paramint.Value = System.Convert. ToDecimal(txtIn teriorSqFeet.Te xt)
paramint = cmd.Parameters. Add(paramint)
Dim paramheat As New SqlClient.SqlPa rameter
paramheat.Param eterName = "@Heating"
paramheat.DbTyp e = DbType.Boolean
If chkHeating.Chec ked Then
paramheat.Value = True
Else
paramheat.Value = 0
End If
cmd.Parameters. Add(paramheat)
Dim paramair As New SqlClient.SqlPa rameter
paramair.Parame terName = "@AirConditioni ng"
paramair.DbType = DbType.Boolean
If chkAirCondition ing.Checked Then
paramair.Value = 1
Else
paramair.Value = 0
End If
cmd.Parameters. Add(paramair)
Dim paramgarage As New SqlClient.SqlPa rameter
paramgarage.Par ameterName = "@EnclosedGarag e"
paramgarage.DbT ype = DbType.Boolean
If chkEnclosedGara ge.Checked Then
paramgarage.Val ue = 1
Else
paramgarage.Val ue = 0
End If
cmd.Parameters. Add(paramgarage )
Dim parampool As New SqlClient.SqlPa rameter
parampool.Param eterName = "@Pool"
parampool.DbTyp e = DbType.Boolean
If chkPool.Checked Then
parampool.Value = 1
Else
parampool.Value = 0
End If
cmd.Parameters. Add(parampool)
Dim paramcomments As New SqlClient.SqlPa rameter
paramcomments.P arameterName = "@Comments"
paramcomments.D bType = DbType.String
paramcomments.V alue = txtComments.Tex t
paramcomments.S ize = txtComments.Tex t.Length
paramcomments = cmd.Parameters. Add(paramcommen ts)
Dim parampic1 As New SqlClient.SqlPa rameter
parampic1.Param eterName = "@Picture1"
parampic1.DbTyp e = DbType.Object
parampic1.IsNul lable = True
If Picture1 IsNot Nothing Then
parampic1.Size = Picture1.Length
End If
parampic1.Value = Picture1
cmd.Parameters. Add(parampic1)
Dim parampic2 As New SqlClient.SqlPa rameter
parampic2.Param eterName = "@Picture2"
parampic2.DbTyp e = DbType.Object
parampic2.IsNul lable = True
If Picture2 IsNot Nothing Then
parampic2.Size = Picture2.Length
End If
parampic2.Value = Picture2
cmd.Parameters. Add(parampic2)
Dim parampic3 As New SqlClient.SqlPa rameter
parampic3.Param eterName = "@Picture3"
parampic3.DbTyp e = DbType.Object
parampic3.IsNul lable = 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.SqlPa rameter
'parampic2 = cmd.Parameters. Add("@Picture2" , SqlDbType.Image ).Value
= Picture2
'Dim parampic3 As New SqlClient.SqlPa rameter
'parampic3 = cmd.Parameters. Add("@Picture3" , SqlDbType.Image ).Value
= Picture3
Dim paramid As New SqlClient.SqlPa rameter
paramid = cmd.Parameters. Add("@id", SqlDbType.Uniqu eIdentifier)
paramid.Directi on = ParameterDirect ion.Output
Dim paramrows As New SqlClient.SqlPa rameter
paramrows = cmd.Parameters. Add("@rowcount_ var", SqlDbType.Int)
paramrows.Direc tion = ParameterDirect ion.Output
Dim paramerr As New SqlClient.SqlPa rameter
paramerr = cmd.Parameters. Add("@error_tex t", SqlDbType.NVarC har, 255)
paramerr.Direct ion = ParameterDirect ion.Output
Dim result As Boolean
Try
result = cmd.ExecuteNonQ uery()
Catch ex As SqlClient.SqlEx ception
MsgBox(ex.Messa ge)
End Try
MsgBox("error text = " &
cmd.Parameters( "@error_text"). Value.ToString & vbCr & "id = " &
cmd.Parameters( "@id").Value.To String & vbCr & "rowcount = " &
cmd.Parameters( "@rowcount_var" ).Value.ToStrin g)
conn.Close()
conn = Nothing
End Sub
stored procedure code:
ALTER PROCEDURE dbo.usp_AddProj ect
/*
(
@parameter1 int = 5,
@parameter2 datatype OUTPUT
)
*/
(
@ProjectName nvarchar(100),
@PropertyAddres s nvarchar(150),
@ProjectBudget money,
@PropertyType nvarchar(50),
@NumberBedrooms smallint,
@NumberBathroom s smallint,
@LandSqFeet float,
@InteriorSqFeet float,
@Heating bit = 0,
@AirConditionin g bit = 0,
@EnclosedGarage bit = 0,
@Pool bit = 0,
@Comments nvarchar(2000),
@Picture1 image = null,
@Picture2 image = null,
@Picture3 image = null,
@id uniqueidentifie r = 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,Proj ectName,
PropertyAddress ,ProjectBudget, PropertyType,
NumberBedrooms, NumberBathrooms ,LandSqFeet,
InteriorSqFeet, Heating,AirCond itioning,Enclos edGarage,
Pool,Comments,P icture1,Picture 2,Picture3) values
(@id,@Projectna me,@PropertyAdd ress,@ProjectBu dget,
@PropertyType,@ NumberBedrooms, @NumberBathroom s,
@LandSqFeet,@In teriorSqFeet,@H eating,@AirCond itioning,
@EnclosedGarage ,@Pool,@Comment s,@Picture1,@Pi cture2,
@Picture3)
set @error_var = @@error;
set @rowcount_var = @@rowcount;
if @error_var <> 0
begin
select @error_text = (select description from sysmessages where
error=@error_va r)
end
RETURN
6  2346 
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_C lick(ByVal sender As Object, ByVal e As
System.EventArg s) Handles btnAddProject.C lick
Dim Picture1 As Byte()
Dim Picture2 As Byte()
Dim Picture3 As Byte()
Dim connectString As String = My.Settings.Rem odelGenieConnec tionString
Dim conn As New SqlClient.SqlCo nnection
conn.Connection String = connectString
conn.Open()
Dim cmd As New SqlClient.SqlCo mmand("dbo.usp_ AddProject", conn)
Dim m As New Misc
If FileExists(txtP icture1Path.Tex t) Then
Picture1 = m.ReadBinary(tx tPicture1Path.T ext)
Else
Picture1 = Nothing
End If
If FileExists(txtP icture2Path.Tex t) Then
Picture2 = m.ReadBinary(tx tPicture2Path.T ext)
Else
Picture2 = Nothing
End If
If FileExists(txtP icture3Path.Tex t) Then
Picture3 = m.ReadBinary(tx tPicture3Path.T ext)
Else
Picture3 = Nothing
End If
cmd.CommandType = CommandType.Sto redProcedure
Dim paramname As New SqlClient.SqlPa rameter
paramname.Param eterName = "@ProjectNa me"
paramname.DbTyp e = DbType.String
paramname.Size = txtProjectName. Text.Length
paramname.Value = txtProjectName. Text.ToString
paramname = cmd.Parameters. Add(paramname)
Dim paramaddr As New SqlClient.SqlPa rameter
paramaddr.Param eterName = "@PropertyAddre ss"
paramaddr.DbTyp e = DbType.String
paramaddr.Size = txtPropertyAddr ess.Text.Length
paramaddr.Value = txtPropertyAddr ess.Text
paramaddr = cmd.Parameters. Add(paramaddr)
Dim parambudget As New SqlClient.SqlPa rameter
parambudget.Par ameterName = "@ProjectBudget "
parambudget.DbT ype = DbType.Currency
parambudget.Val ue = System.Convert. ToDecimal(txtPr ojectBudget.Tex t)
parambudget = cmd.Parameters. Add(parambudget )
Dim paramtype As New SqlClient.SqlPa rameter
paramtype.Param eterName = "@PropertyT ype"
paramtype.DbTyp e = DbType.String
paramtype.Size = cmbPropertyType .Text.Length
paramtype.Value = cmbPropertyType .Text
paramtype = cmd.Parameters. Add(paramtype)
Dim parambed As New SqlClient.SqlPa rameter
parambed.Parame terName = "@NumberBedroom s"
parambed.DbType = DbType.Int16
parambed.Value = System.Convert. ToInt16(txtNumb erBedrooms.Text )
parambed = cmd.Parameters. Add(parambed)
Dim parambath As New SqlClient.SqlPa rameter
parambath.Param eterName = "@NumberBathroo ms"
parambath.DbTyp e = DbType.Int16
parambath.Value = System.Convert. ToInt16(txtNumb erBathrooms.Tex t)
parambed = cmd.Parameters. Add(parambath)
Dim paramland As New SqlClient.SqlPa rameter
paramland.Param eterName = "@LandSqFee t"
paramland.DbTyp e = DbType.Double
paramland.Value = System.Convert. ToDecimal(txtLa ndSqFeet.Text)
paramland = cmd.Parameters. Add(paramland)
Dim paramint As New SqlClient.SqlPa rameter
paramint.Parame terName = "@InteriorSqFee t"
paramint.DbType = DbType.Double
paramint.Value = System.Convert. ToDecimal(txtIn teriorSqFeet.Te xt)
paramint = cmd.Parameters. Add(paramint)
Dim paramheat As New SqlClient.SqlPa rameter
paramheat.Param eterName = "@Heating"
paramheat.DbTyp e = DbType.Boolean
If chkHeating.Chec ked Then
paramheat.Value = True
Else
paramheat.Value = 0
End If
cmd.Parameters. Add(paramheat)
Dim paramair As New SqlClient.SqlPa rameter
paramair.Parame terName = "@AirConditioni ng"
paramair.DbType = DbType.Boolean
If chkAirCondition ing.Checked Then
paramair.Value = 1
Else
paramair.Value = 0
End If
cmd.Parameters. Add(paramair)
Dim paramgarage As New SqlClient.SqlPa rameter
paramgarage.Par ameterName = "@EnclosedGarag e"
paramgarage.DbT ype = DbType.Boolean
If chkEnclosedGara ge.Checked Then
paramgarage.Val ue = 1
Else
paramgarage.Val ue = 0
End If
cmd.Parameters. Add(paramgarage )
Dim parampool As New SqlClient.SqlPa rameter
parampool.Param eterName = "@Pool"
parampool.DbTyp e = DbType.Boolean
If chkPool.Checked Then
parampool.Value = 1
Else
parampool.Value = 0
End If
cmd.Parameters. Add(parampool)
Dim paramcomments As New SqlClient.SqlPa rameter
paramcomments.P arameterName = "@Comments"
paramcomments.D bType = DbType.String
paramcomments.V alue = txtComments.Tex t
paramcomments.S ize = txtComments.Tex t.Length
paramcomments = cmd.Parameters. Add(paramcommen ts)
Dim parampic1 As New SqlClient.SqlPa rameter
parampic1.Param eterName = "@Picture1"
parampic1.DbTyp e = DbType.Object
parampic1.IsNul lable = True
If Picture1 IsNot Nothing Then
parampic1.Size = Picture1.Length
End If
parampic1.Value = Picture1
cmd.Parameters. Add(parampic1)
Dim parampic2 As New SqlClient.SqlPa rameter
parampic2.Param eterName = "@Picture2"
parampic2.DbTyp e = DbType.Object
parampic2.IsNul lable = True
If Picture2 IsNot Nothing Then
parampic2.Size = Picture2.Length
End If
parampic2.Value = Picture2
cmd.Parameters. Add(parampic2)
Dim parampic3 As New SqlClient.SqlPa rameter
parampic3.Param eterName = "@Picture3"
parampic3.DbTyp e = DbType.Object
parampic3.IsNul lable = 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.SqlPa rameter
'parampic2 = cmd.Parameters. Add("@Picture2" , SqlDbType.Image ).Value
= Picture2
'Dim parampic3 As New SqlClient.SqlPa rameter
'parampic3 = cmd.Parameters. Add("@Picture3" , SqlDbType.Image ).Value
= Picture3
Dim paramid As New SqlClient.SqlPa rameter
paramid = cmd.Parameters. Add("@id", SqlDbType.Uniqu eIdentifier)
paramid.Directi on = ParameterDirect ion.Output
Dim paramrows As New SqlClient.SqlPa rameter
paramrows = cmd.Parameters. Add("@rowcount_ var", SqlDbType.Int)
paramrows.Direc tion = ParameterDirect ion.Output
Dim paramerr As New SqlClient.SqlPa rameter
paramerr = cmd.Parameters. Add("@error_tex t", SqlDbType.NVarC har, 255)
paramerr.Direct ion = ParameterDirect ion.Output
Dim result As Boolean
Try
result = cmd.ExecuteNonQ uery()
Catch ex As SqlClient.SqlEx ception
MsgBox(ex.Messa ge)
End Try
MsgBox("error text = " &
cmd.Parameters( "@error_text"). Value.ToString & vbCr & "id = " &
cmd.Parameters( "@id").Value.To String & vbCr & "rowcount = " &
cmd.Parameters( "@rowcount_var" ).Value.ToStrin g)
conn.Close()
conn = Nothing
End Sub
stored procedure code:
ALTER PROCEDURE dbo.usp_AddProj ect
/*
(
@parameter1 int = 5,
@parameter2 datatype OUTPUT
)
*/
(
@ProjectName nvarchar(100),
@PropertyAddres s nvarchar(150),
@ProjectBudget money,
@PropertyType nvarchar(50),
@NumberBedrooms smallint,
@NumberBathroom s smallint,
@LandSqFeet float,
@InteriorSqFeet float,
@Heating bit = 0,
@AirConditionin g bit = 0,
@EnclosedGarage bit = 0,
@Pool bit = 0,
@Comments nvarchar(2000),
@Picture1 image = null,
@Picture2 image = null,
@Picture3 image = null,
@id uniqueidentifie r = 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,Proj ectName,
PropertyAddress ,ProjectBudget, PropertyType,
NumberBedrooms, NumberBathrooms ,LandSqFeet,
InteriorSqFeet, Heating,AirCond itioning,Enclos edGarage,
Pool,Comments,P icture1,Picture 2,Picture3) values
(@id,@Projectna me,@PropertyAdd ress,@ProjectBu dget,
@PropertyType,@ NumberBedrooms, @NumberBathroom s,
@LandSqFeet,@In teriorSqFeet,@H eating,@AirCond itioning,
@EnclosedGarage ,@Pool,@Comment s,@Picture1,@Pi cture2,
@Picture3)
set @error_var = @@error;
set @rowcount_var = @@rowcount;
if @error_var <> 0
begin
select @error_text = (select description from sysmessages where
error=@error_va r)
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_C lick(ByVal sender As Object, ByVal e As
System.EventArg s) Handles btnAddProject.C lick
Dim Picture1 As Byte()
Dim Picture2 As Byte()
Dim Picture3 As Byte()
Dim connectString As String = My.Settings.Rem odelGenieConnec tionString
Dim conn As New SqlClient.SqlCo nnection
conn.Connection String = connectString
conn.Open()
Dim cmd As New SqlClient.SqlCo mmand("dbo.usp_ AddProject", conn)
Dim m As New Misc
If FileExists(txtP icture1Path.Tex t) Then
Picture1 = m.ReadBinary(tx tPicture1Path.T ext)
Else
Picture1 = Nothing
End If
If FileExists(txtP icture2Path.Tex t) Then
Picture2 = m.ReadBinary(tx tPicture2Path.T ext)
Else
Picture2 = Nothing
End If
If FileExists(txtP icture3Path.Tex t) Then
Picture3 = m.ReadBinary(tx tPicture3Path.T ext)
Else
Picture3 = Nothing
End If
cmd.CommandType = CommandType.Sto redProcedure
Dim paramname As New SqlClient.SqlPa rameter
paramname.Param eterName = "@ProjectNa me"
paramname.DbTyp e = DbType.String
paramname.Size = txtProjectName. Text.Length
paramname.Value = txtProjectName. Text.ToString
paramname = cmd.Parameters. Add(paramname)
Dim paramaddr As New SqlClient.SqlPa rameter
paramaddr.Param eterName = "@PropertyAddre ss"
paramaddr.DbTyp e = DbType.String
paramaddr.Size = txtPropertyAddr ess.Text.Length
paramaddr.Value = txtPropertyAddr ess.Text
paramaddr = cmd.Parameters. Add(paramaddr)
Dim parambudget As New SqlClient.SqlPa rameter
parambudget.Par ameterName = "@ProjectBudget "
parambudget.DbT ype = DbType.Currency
parambudget.Val ue = System.Convert. ToDecimal(txtPr ojectBudget.Tex t)
parambudget = cmd.Parameters. Add(parambudget )
Dim paramtype As New SqlClient.SqlPa rameter
paramtype.Param eterName = "@PropertyT ype"
paramtype.DbTyp e = DbType.String
paramtype.Size = cmbPropertyType .Text.Length
paramtype.Value = cmbPropertyType .Text
paramtype = cmd.Parameters. Add(paramtype)
Dim parambed As New SqlClient.SqlPa rameter
parambed.Parame terName = "@NumberBedroom s"
parambed.DbType = DbType.Int16
parambed.Value = System.Convert. ToInt16(txtNumb erBedrooms.Text )
parambed = cmd.Parameters. Add(parambed)
Dim parambath As New SqlClient.SqlPa rameter
parambath.Param eterName = "@NumberBathroo ms"
parambath.DbTyp e = DbType.Int16
parambath.Value = System.Convert. ToInt16(txtNumb erBathrooms.Tex t)
parambed = cmd.Parameters. Add(parambath)
Dim paramland As New SqlClient.SqlPa rameter
paramland.Param eterName = "@LandSqFee t"
paramland.DbTyp e = DbType.Double
paramland.Value = System.Convert. ToDecimal(txtLa ndSqFeet.Text)
paramland = cmd.Parameters. Add(paramland)
Dim paramint As New SqlClient.SqlPa rameter
paramint.Parame terName = "@InteriorSqFee t"
paramint.DbType = DbType.Double
paramint.Value = System.Convert. ToDecimal(txtIn teriorSqFeet.Te xt)
paramint = cmd.Parameters. Add(paramint)
Dim paramheat As New SqlClient.SqlPa rameter
paramheat.Param eterName = "@Heating"
paramheat.DbTyp e = DbType.Boolean
If chkHeating.Chec ked Then
paramheat.Value = True
Else
paramheat.Value = 0
End If
cmd.Parameters. Add(paramheat)
Dim paramair As New SqlClient.SqlPa rameter
paramair.Parame terName = "@AirConditioni ng"
paramair.DbType = DbType.Boolean
If chkAirCondition ing.Checked Then
paramair.Value = 1
Else
paramair.Value = 0
End If
cmd.Parameters. Add(paramair)
Dim paramgarage As New SqlClient.SqlPa rameter
paramgarage.Par ameterName = "@EnclosedGarag e"
paramgarage.DbT ype = DbType.Boolean
If chkEnclosedGara ge.Checked Then
paramgarage.Val ue = 1
Else
paramgarage.Val ue = 0
End If
cmd.Parameters. Add(paramgarage )
Dim parampool As New SqlClient.SqlPa rameter
parampool.Param eterName = "@Pool"
parampool.DbTyp e = DbType.Boolean
If chkPool.Checked Then
parampool.Value = 1
Else
parampool.Value = 0
End If
cmd.Parameters. Add(parampool)
Dim paramcomments As New SqlClient.SqlPa rameter
paramcomments.P arameterName = "@Comments"
paramcomments.D bType = DbType.String
paramcomments.V alue = txtComments.Tex t
paramcomments.S ize = txtComments.Tex t.Length
paramcomments = cmd.Parameters. Add(paramcommen ts)
Dim parampic1 As New SqlClient.SqlPa rameter
parampic1.Param eterName = "@Picture1"
parampic1.DbTyp e = DbType.Object
parampic1.IsNul lable = True
If Picture1 IsNot Nothing Then
parampic1.Size = Picture1.Length
End If
parampic1.Value = Picture1
cmd.Parameters. Add(parampic1)
Dim parampic2 As New SqlClient.SqlPa rameter
parampic2.Param eterName = "@Picture2"
parampic2.DbTyp e = DbType.Object
parampic2.IsNul lable = True
If Picture2 IsNot Nothing Then
parampic2.Size = Picture2.Length
End If
parampic2.Value = Picture2
cmd.Parameters. Add(parampic2)
Dim parampic3 As New SqlClient.SqlPa rameter
parampic3.Param eterName = "@Picture3"
parampic3.DbTyp e = DbType.Object
parampic3.IsNul lable = 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.SqlPa rameter
'parampic2 = cmd.Parameters. Add("@Picture2" , SqlDbType.Image ).Value
= Picture2
'Dim parampic3 As New SqlClient.SqlPa rameter
'parampic3 = cmd.Parameters. Add("@Picture3" , SqlDbType.Image ).Value
= Picture3
Dim paramid As New SqlClient.SqlPa rameter
paramid = cmd.Parameters. Add("@id", SqlDbType.Uniqu eIdentifier)
paramid.Directi on = ParameterDirect ion.Output
Dim paramrows As New SqlClient.SqlPa rameter
paramrows = cmd.Parameters. Add("@rowcount_ var", SqlDbType.Int)
paramrows.Direc tion = ParameterDirect ion.Output
Dim paramerr As New SqlClient.SqlPa rameter
paramerr = cmd.Parameters. Add("@error_tex t", SqlDbType.NVarC har, 255)
paramerr.Direct ion = ParameterDirect ion.Output
Dim result As Boolean
Try
result = cmd.ExecuteNonQ uery()
Catch ex As SqlClient.SqlEx ception
MsgBox(ex.Messa ge)
End Try
MsgBox("error text = " &
cmd.Parameters( "@error_text"). Value.ToString & vbCr & "id = " &
cmd.Parameters( "@id").Value.To String & vbCr & "rowcount = " &
cmd.Parameters( "@rowcount_var" ).Value.ToStrin g)
conn.Close()
conn = Nothing
End Sub
stored procedure code:
ALTER PROCEDURE dbo.usp_AddProj ect
/*
(
@parameter1 int = 5,
@parameter2 datatype OUTPUT
)
*/
(
@ProjectName nvarchar(100),
@PropertyAddres s nvarchar(150),
@ProjectBudget money,
@PropertyType nvarchar(50),
@NumberBedrooms smallint,
@NumberBathroom s smallint,
@LandSqFeet float,
@InteriorSqFeet float,
@Heating bit = 0,
@AirConditionin g bit = 0,
@EnclosedGarage bit = 0,
@Pool bit = 0,
@Comments nvarchar(2000),
@Picture1 image = null,
@Picture2 image = null,
@Picture3 image = null,
@id uniqueidentifie r = 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,Proj ectName,
PropertyAddress ,ProjectBudget, PropertyType,
NumberBedrooms, NumberBathrooms ,LandSqFeet,
InteriorSqFeet, Heating,AirCond itioning,Enclos edGarage,
Pool,Comments,P icture1,Picture 2,Picture3) values
(@id,@Projectna me,@PropertyAdd ress,@ProjectBu dget,
@PropertyType,@ NumberBedrooms, @NumberBathroom s,
@LandSqFeet,@In teriorSqFeet,@H eating,@AirCond itioning,
@EnclosedGarage ,@Pool,@Comment s,@Picture1,@Pi cture2,
@Picture3)
set @error_var = @@error;
set @rowcount_var = @@rowcount;
if @error_var <> 0
begin
select @error_text = (select description from sysmessages where
error=@error_va r)
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_C lick(ByVal sender As Object, ByVal e As
System.EventArg s) Handles btnAddProject.C lick
Dim Picture1 As Byte()
Dim Picture2 As Byte()
Dim Picture3 As Byte()
Dim connectString As String = My.Settings.Rem odelGenieConnec tionString
Dim conn As New SqlClient.SqlCo nnection
conn.Connection String = connectString
conn.Open()
Dim cmd As New SqlClient.SqlCo mmand("dbo.usp_ AddProject", conn)
Dim m As New Misc
If FileExists(txtP icture1Path.Tex t) Then
Picture1 = m.ReadBinary(tx tPicture1Path.T ext)
Else
Picture1 = Nothing
End If
If FileExists(txtP icture2Path.Tex t) Then
Picture2 = m.ReadBinary(tx tPicture2Path.T ext)
Else
Picture2 = Nothing
End If
If FileExists(txtP icture3Path.Tex t) Then
Picture3 = m.ReadBinary(tx tPicture3Path.T ext)
Else
Picture3 = Nothing
End If
cmd.CommandType = CommandType.Sto redProcedure
Dim paramname As New SqlClient.SqlPa rameter
paramname.Param eterName = "@ProjectNa me"
paramname.DbTyp e = DbType.String
paramname.Size = txtProjectName. Text.Length
paramname.Value = txtProjectName. Text.ToString
paramname = cmd.Parameters. Add(paramname)
Dim paramaddr As New SqlClient.SqlPa rameter
paramaddr.Param eterName = "@PropertyAddre ss"
paramaddr.DbTyp e = DbType.String
paramaddr.Size = txtPropertyAddr ess.Text.Length
paramaddr.Value = txtPropertyAddr ess.Text
paramaddr = cmd.Parameters. Add(paramaddr)
Dim parambudget As New SqlClient.SqlPa rameter
parambudget.Par ameterName = "@ProjectBudget "
parambudget.DbT ype = DbType.Currency
parambudget.Val ue = System.Convert. ToDecimal(txtPr ojectBudget.Tex t)
parambudget = cmd.Parameters. Add(parambudget )
Dim paramtype As New SqlClient.SqlPa rameter
paramtype.Param eterName = "@PropertyT ype"
paramtype.DbTyp e = DbType.String
paramtype.Size = cmbPropertyType .Text.Length
paramtype.Value = cmbPropertyType .Text
paramtype = cmd.Parameters. Add(paramtype)
Dim parambed As New SqlClient.SqlPa rameter
parambed.Parame terName = "@NumberBedroom s"
parambed.DbType = DbType.Int16
parambed.Value = System.Convert. ToInt16(txtNumb erBedrooms.Text )
parambed = cmd.Parameters. Add(parambed)
Dim parambath As New SqlClient.SqlPa rameter
parambath.Param eterName = "@NumberBathroo ms"
parambath.DbTyp e = DbType.Int16
parambath.Value = System.Convert. ToInt16(txtNumb erBathrooms.Tex t)
parambed = cmd.Parameters. Add(parambath)
Dim paramland As New SqlClient.SqlPa rameter
paramland.Param eterName = "@LandSqFee t"
paramland.DbTyp e = DbType.Double
paramland.Value = System.Convert. ToDecimal(txtLa ndSqFeet.Text)
paramland = cmd.Parameters. Add(paramland)
Dim paramint As New SqlClient.SqlPa rameter
paramint.Parame terName = "@InteriorSqFee t"
paramint.DbType = DbType.Double
paramint.Value = System.Convert. ToDecimal(txtIn teriorSqFeet.Te xt)
paramint = cmd.Parameters. Add(paramint)
Dim paramheat As New SqlClient.SqlPa rameter
paramheat.Param eterName = "@Heating"
paramheat.DbTyp e = DbType.Boolean
If chkHeating.Chec ked Then
paramheat.Value = True
Else
paramheat.Value = 0
End If
cmd.Parameters. Add(paramheat)
Dim paramair As New SqlClient.SqlPa rameter
paramair.Parame terName = "@AirConditioni ng"
paramair.DbType = DbType.Boolean
If chkAirCondition ing.Checked Then
paramair.Value = 1
Else
paramair.Value = 0
End If
cmd.Parameters. Add(paramair)
Dim paramgarage As New SqlClient.SqlPa rameter
paramgarage.Par ameterName = "@EnclosedGarag e"
paramgarage.DbT ype = DbType.Boolean
If chkEnclosedGara ge.Checked Then
paramgarage.Val ue = 1
Else
paramgarage.Val ue = 0
End If
cmd.Parameters. Add(paramgarage )
Dim parampool As New SqlClient.SqlPa rameter
parampool.Param eterName = "@Pool"
parampool.DbTyp e = DbType.Boolean
If chkPool.Checked Then
parampool.Value = 1
Else
parampool.Value = 0
End If
cmd.Parameters. Add(parampool)
Dim paramcomments As New SqlClient.SqlPa rameter
paramcomments.P arameterName = "@Comments"
paramcomments.D bType = DbType.String
paramcomments.V alue = txtComments.Tex t
paramcomments.S ize = txtComments.Tex t.Length
paramcomments = cmd.Parameters. Add(paramcommen ts)
Dim parampic1 As New SqlClient.SqlPa rameter
parampic1.Param eterName = "@Picture1"
parampic1.DbTyp e = DbType.Object
parampic1.IsNul lable = True
If Picture1 IsNot Nothing Then
parampic1.Size = Picture1.Length
End If
parampic1.Value = Picture1
cmd.Parameters. Add(parampic1)
Dim parampic2 As New SqlClient.SqlPa rameter
parampic2.Param eterName = "@Picture2"
parampic2.DbTyp e = DbType.Object
parampic2.IsNul lable = True
If Picture2 IsNot Nothing Then
parampic2.Size = Picture2.Length
End If
parampic2.Value = Picture2
cmd.Parameters. Add(parampic2)
Dim parampic3 As New SqlClient.SqlPa rameter
parampic3.Param eterName = "@Picture3"
parampic3.DbTyp e = DbType.Object
parampic3.IsNul lable = 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.SqlPa rameter
'parampic2 = cmd.Parameters. Add("@Picture2" , SqlDbType.Image ).Value
= Picture2
'Dim parampic3 As New SqlClient.SqlPa rameter
'parampic3 = cmd.Parameters. Add("@Picture3" , SqlDbType.Image ).Value
= Picture3
Dim paramid As New SqlClient.SqlPa rameter
paramid = cmd.Parameters. Add("@id", SqlDbType.Uniqu eIdentifier)
paramid.Directi on = ParameterDirect ion.Output
Dim paramrows As New SqlClient.SqlPa rameter
paramrows = cmd.Parameters. Add("@rowcount_ var", SqlDbType.Int)
paramrows.Direc tion = ParameterDirect ion.Output
Dim paramerr As New SqlClient.SqlPa rameter
paramerr = cmd.Parameters. Add("@error_tex t", SqlDbType.NVarC har, 255)
paramerr.Direct ion = ParameterDirect ion.Output
Dim result As Boolean
Try
result = cmd.ExecuteNonQ uery()
Catch ex As SqlClient.SqlEx ception
MsgBox(ex.Messa ge)
End Try
MsgBox("error text = " &
cmd.Parameters( "@error_text"). Value.ToString & vbCr & "id = " &
cmd.Parameters( "@id").Value.To String & vbCr & "rowcount = " &
cmd.Parameters( "@rowcount_var" ).Value.ToStrin g)
conn.Close()
conn = Nothing
End Sub
stored procedure code:
ALTER PROCEDURE dbo.usp_AddProj ect
/*
(
@parameter1 int = 5,
@parameter2 datatype OUTPUT
)
*/
(
@ProjectName nvarchar(100),
@PropertyAddres s nvarchar(150),
@ProjectBudget money,
@PropertyType nvarchar(50),
@NumberBedrooms smallint,
@NumberBathroom s smallint,
@LandSqFeet float,
@InteriorSqFeet float,
@Heating bit = 0,
@AirConditionin g bit = 0,
@EnclosedGarage bit = 0,
@Pool bit = 0,
@Comments nvarchar(2000),
@Picture1 image = null,
@Picture2 image = null,
@Picture3 image = null,
@id uniqueidentifie r = 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,Proj ectName,
PropertyAddress ,ProjectBudget, PropertyType,
NumberBedrooms, NumberBathrooms ,LandSqFeet,
InteriorSqFeet, Heating,AirCond itioning,Enclos edGarage,
Pool,Comments,P icture1,Picture 2,Picture3) values
(@id,@Projectna me,@PropertyAdd ress,@ProjectBu dget,
@PropertyType,@ NumberBedrooms, @NumberBathroom s,
@LandSqFeet,@In teriorSqFeet,@H eating,@AirCond itioning,
@EnclosedGarage ,@Pool,@Comment s,@Picture1,@Pi cture2,
@Picture3)
set @error_var = @@error;
set @rowcount_var = @@rowcount;
if @error_var <> 0
begin
select @error_text = (select description from sysmessages where
error=@error_va r)
end
RETURN
Just to be double sure, I would set a breakpoint at: conn.Connection String = 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_C lick(ByVal sender As Object, ByVal e As
System.EventArg s) Handles btnAddProject.C lick
Dim Picture1 As Byte()
Dim Picture2 As Byte()
Dim Picture3 As Byte()
Dim connectString As String = My.Settings.Rem odelGenieConnec tionString
Dim conn As New SqlClient.SqlCo nnection
conn.Connection String = connectString
conn.Open()
Dim cmd As New SqlClient.SqlCo mmand("dbo.usp_ AddProject", conn)
Dim m As New Misc
If FileExists(txtP icture1Path.Tex t) Then
Picture1 = m.ReadBinary(tx tPicture1Path.T ext)
Else
Picture1 = Nothing
End If
If FileExists(txtP icture2Path.Tex t) Then
Picture2 = m.ReadBinary(tx tPicture2Path.T ext)
Else
Picture2 = Nothing
End If
If FileExists(txtP icture3Path.Tex t) Then
Picture3 = m.ReadBinary(tx tPicture3Path.T ext)
Else
Picture3 = Nothing
End If
cmd.CommandType = CommandType.Sto redProcedure
Dim paramname As New SqlClient.SqlPa rameter
paramname.Param eterName = "@ProjectNa me"
paramname.DbTyp e = DbType.String
paramname.Size = txtProjectName. Text.Length
paramname.Value = txtProjectName. Text.ToString
paramname = cmd.Parameters. Add(paramname)
Dim paramaddr As New SqlClient.SqlPa rameter
paramaddr.Param eterName = "@PropertyAddre ss"
paramaddr.DbTyp e = DbType.String
paramaddr.Size = txtPropertyAddr ess.Text.Length
paramaddr.Value = txtPropertyAddr ess.Text
paramaddr = cmd.Parameters. Add(paramaddr)
Dim parambudget As New SqlClient.SqlPa rameter
parambudget.Par ameterName = "@ProjectBudget "
parambudget.DbT ype = DbType.Currency
parambudget.Val ue = System.Convert. ToDecimal(txtPr ojectBudget.Tex t)
parambudget = cmd.Parameters. Add(parambudget )
Dim paramtype As New SqlClient.SqlPa rameter
paramtype.Param eterName = "@PropertyT ype"
paramtype.DbTyp e = DbType.String
paramtype.Size = cmbPropertyType .Text.Length
paramtype.Value = cmbPropertyType .Text
paramtype = cmd.Parameters. Add(paramtype)
Dim parambed As New SqlClient.SqlPa rameter
parambed.Parame terName = "@NumberBedroom s"
parambed.DbType = DbType.Int16
parambed.Value = System.Convert. ToInt16(txtNumb erBedrooms.Text )
parambed = cmd.Parameters. Add(parambed)
Dim parambath As New SqlClient.SqlPa rameter
parambath.Param eterName = "@NumberBathroo ms"
parambath.DbTyp e = DbType.Int16
parambath.Value = System.Convert. ToInt16(txtNumb erBathrooms.Tex t)
parambed = cmd.Parameters. Add(parambath)
Dim paramland As New SqlClient.SqlPa rameter
paramland.Param eterName = "@LandSqFee t"
paramland.DbTyp e = DbType.Double
paramland.Value = System.Convert. ToDecimal(txtLa ndSqFeet.Text)
paramland = cmd.Parameters. Add(paramland)
Dim paramint As New SqlClient.SqlPa rameter
paramint.Parame terName = "@InteriorSqFee t"
paramint.DbType = DbType.Double
paramint.Value = System.Convert. ToDecimal(txtIn teriorSqFeet.Te xt)
paramint = cmd.Parameters. Add(paramint)
Dim paramheat As New SqlClient.SqlPa rameter
paramheat.Param eterName = "@Heating"
paramheat.DbTyp e = DbType.Boolean
If chkHeating.Chec ked Then
paramheat.Value = True
Else
paramheat.Value = 0
End If
cmd.Parameters. Add(paramheat)
Dim paramair As New SqlClient.SqlPa rameter
paramair.Parame terName = "@AirConditioni ng"
paramair.DbType = DbType.Boolean
If chkAirCondition ing.Checked Then
paramair.Value = 1
Else
paramair.Value = 0
End If
cmd.Parameters. Add(paramair)
Dim paramgarage As New SqlClient.SqlPa rameter
paramgarage.Par ameterName = "@EnclosedGarag e"
paramgarage.DbT ype = DbType.Boolean
If chkEnclosedGara ge.Checked Then
paramgarage.Val ue = 1
Else
paramgarage.Val ue = 0
End If
cmd.Parameters. Add(paramgarage )
Dim parampool As New SqlClient.SqlPa rameter
parampool.Param eterName = "@Pool"
parampool.DbTyp e = DbType.Boolean
If chkPool.Checked Then
parampool.Value = 1
Else
parampool.Value = 0
End If
cmd.Parameters. Add(parampool)
Dim paramcomments As New SqlClient.SqlPa rameter
paramcomments.P arameterName = "@Comments"
paramcomments.D bType = DbType.String
paramcomments.V alue = txtComments.Tex t
paramcomments.S ize = txtComments.Tex t.Length
paramcomments = cmd.Parameters. Add(paramcommen ts)
Dim parampic1 As New SqlClient.SqlPa rameter
parampic1.Param eterName = "@Picture1"
parampic1.DbTyp e = DbType.Object
parampic1.IsNul lable = True
If Picture1 IsNot Nothing Then
parampic1.Size = Picture1.Length
End If
parampic1.Value = Picture1
cmd.Parameters. Add(parampic1)
Dim parampic2 As New SqlClient.SqlPa rameter
parampic2.Param eterName = "@Picture2"
parampic2.DbTyp e = DbType.Object
parampic2.IsNul lable = True
If Picture2 IsNot Nothing Then
parampic2.Size = Picture2.Length
End If
parampic2.Value = Picture2
cmd.Parameters. Add(parampic2)
Dim parampic3 As New SqlClient.SqlPa rameter
parampic3.Param eterName = "@Picture3"
parampic3.DbTyp e = DbType.Object
parampic3.IsNul lable = 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.SqlPa rameter
'parampic2 = cmd.Parameters. Add("@Picture2" , SqlDbType.Image ).Value
= Picture2
'Dim parampic3 As New SqlClient.SqlPa rameter
'parampic3 = cmd.Parameters. Add("@Picture3" , SqlDbType.Image ).Value
= Picture3
Dim paramid As New SqlClient.SqlPa rameter
paramid = cmd.Parameters. Add("@id", SqlDbType.Uniqu eIdentifier)
paramid.Directi on = ParameterDirect ion.Output
Dim paramrows As New SqlClient.SqlPa rameter
paramrows = cmd.Parameters. Add("@rowcount_ var", SqlDbType.Int)
paramrows.Direc tion = ParameterDirect ion.Output
Dim paramerr As New SqlClient.SqlPa rameter
paramerr = cmd.Parameters. Add("@error_tex t", SqlDbType.NVarC har, 255)
paramerr.Direct ion = ParameterDirect ion.Output
Dim result As Boolean
Try
result = cmd.ExecuteNonQ uery()
Catch ex As SqlClient.SqlEx ception
MsgBox(ex.Messa ge)
End Try
MsgBox("error text = " &
cmd.Parameters( "@error_text"). Value.ToString & vbCr & "id = " &
cmd.Parameters( "@id").Value.To String & vbCr & "rowcount = " &
cmd.Parameters( "@rowcount_var" ).Value.ToStrin g)
conn.Close()
conn = Nothing
End Sub
stored procedure code:
ALTER PROCEDURE dbo.usp_AddProj ect
/*
(
@parameter1 int = 5,
@parameter2 datatype OUTPUT
)
*/
(
@ProjectName nvarchar(100),
@PropertyAddres s nvarchar(150),
@ProjectBudget money,
@PropertyType nvarchar(50),
@NumberBedrooms smallint,
@NumberBathroom s smallint,
@LandSqFeet float,
@InteriorSqFeet float,
@Heating bit = 0,
@AirConditionin g bit = 0,
@EnclosedGarage bit = 0,
@Pool bit = 0,
@Comments nvarchar(2000),
@Picture1 image = null,
@Picture2 image = null,
@Picture3 image = null,
@id uniqueidentifie r = 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,Proj ectName,
PropertyAddress ,ProjectBudget, PropertyType,
NumberBedrooms, NumberBathrooms ,LandSqFeet,
InteriorSqFeet, Heating,AirCond itioning,Enclos edGarage,
Pool,Comments,P icture1,Picture 2,Picture3) values
(@id,@Projectna me,@PropertyAdd ress,@ProjectBu dget,
@PropertyType,@ NumberBedrooms, @NumberBathroom s,
@LandSqFeet,@In teriorSqFeet,@H eating,@AirCond itioning,
@EnclosedGarage ,@Pool,@Comment s,@Picture1,@Pi cture2,
@Picture3)
set @error_var = @@error;
set @rowcount_var = @@rowcount;
if @error_var <> 0
begin
select @error_text = (select description from sysmessages where
error=@error_va r)
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.Sto redProcedure
Dim paramname As New SqlClient.SqlPa rameter
paramname.Param eterName = "@ProjectNa me"
paramname.DbTyp e = 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
<ParameterNam e, 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.Connection String = 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_C lick(ByVal sender As Object, ByVal e As
System.EventArg s) Handles btnAddProject.C lick
Dim Picture1 As Byte()
Dim Picture2 As Byte()
Dim Picture3 As Byte()
Dim connectString As String = My.Settings.Rem odelGenieConnec tionString
Dim conn As New SqlClient.SqlCo nnection
conn.Connection String = connectString
conn.Open()
Dim cmd As New SqlClient.SqlCo mmand("dbo.usp_ AddProject", conn)
Dim m As New Misc
If FileExists(txtP icture1Path.Tex t) Then
Picture1 = m.ReadBinary(tx tPicture1Path.T ext)
Else
Picture1 = Nothing
End If
If FileExists(txtP icture2Path.Tex t) Then
Picture2 = m.ReadBinary(tx tPicture2Path.T ext)
Else
Picture2 = Nothing
End If
If FileExists(txtP icture3Path.Tex t) Then
Picture3 = m.ReadBinary(tx tPicture3Path.T ext)
Else
Picture3 = Nothing
End If
cmd.CommandType = CommandType.Sto redProcedure
Dim paramname As New SqlClient.SqlPa rameter
paramname.Param eterName = "@ProjectNa me"
paramname.DbTyp e = DbType.String
paramname.Size = txtProjectName. Text.Length
paramname.Value = txtProjectName. Text.ToString
paramname = cmd.Parameters. Add(paramname)
Dim paramaddr As New SqlClient.SqlPa rameter
paramaddr.Param eterName = "@PropertyAddre ss"
paramaddr.DbTyp e = DbType.String
paramaddr.Size = txtPropertyAddr ess.Text.Length
paramaddr.Value = txtPropertyAddr ess.Text
paramaddr = cmd.Parameters. Add(paramaddr)
Dim parambudget As New SqlClient.SqlPa rameter
parambudget.Par ameterName = "@ProjectBudget "
parambudget.DbT ype = DbType.Currency
parambudget.Val ue = System.Convert. ToDecimal(txtPr ojectBudget.Tex t)
parambudget = cmd.Parameters. Add(parambudget )
Dim paramtype As New SqlClient.SqlPa rameter
paramtype.Param eterName = "@PropertyT ype"
paramtype.DbTyp e = DbType.String
paramtype.Size = cmbPropertyType .Text.Length
paramtype.Value = cmbPropertyType .Text
paramtype = cmd.Parameters. Add(paramtype)
Dim parambed As New SqlClient.SqlPa rameter
parambed.Parame terName = "@NumberBedroom s"
parambed.DbType = DbType.Int16
parambed.Value = System.Convert. ToInt16(txtNumb erBedrooms.Text )
parambed = cmd.Parameters. Add(parambed)
Dim parambath As New SqlClient.SqlPa rameter
parambath.Param eterName = "@NumberBathroo ms"
parambath.DbTyp e = DbType.Int16
parambath.Value = System.Convert. ToInt16(txtNumb erBathrooms.Tex t)
parambed = cmd.Parameters. Add(parambath)
Dim paramland As New SqlClient.SqlPa rameter
paramland.Param eterName = "@LandSqFee t"
paramland.DbTyp e = DbType.Double
paramland.Value = System.Convert. ToDecimal(txtLa ndSqFeet.Text)
paramland = cmd.Parameters. Add(paramland)
Dim paramint As New SqlClient.SqlPa rameter
paramint.Parame terName = "@InteriorSqFee t"
paramint.DbType = DbType.Double
paramint.Value = System.Convert. ToDecimal(txtIn teriorSqFeet.Te xt)
paramint = cmd.Parameters. Add(paramint)
Dim paramheat As New SqlClient.SqlPa rameter
paramheat.Param eterName = "@Heating"
paramheat.DbTyp e = DbType.Boolean
If chkHeating.Chec ked Then
paramheat.Value = True
Else
paramheat.Value = 0
End If
cmd.Parameters. Add(paramheat)
Dim paramair As New SqlClient.SqlPa rameter
paramair.Parame terName = "@AirConditioni ng"
paramair.DbType = DbType.Boolean
If chkAirCondition ing.Checked Then
paramair.Value = 1
Else
paramair.Value = 0
End If
cmd.Parameters. Add(paramair)
Dim paramgarage As New SqlClient.SqlPa rameter
paramgarage.Par ameterName = "@EnclosedGarag e"
paramgarage.DbT ype = DbType.Boolean
If chkEnclosedGara ge.Checked Then
paramgarage.Val ue = 1
Else
paramgarage.Val ue = 0
End If
cmd.Parameters. Add(paramgarage )
Dim parampool As New SqlClient.SqlPa rameter
parampool.Param eterName = "@Pool"
parampool.DbTyp e = DbType.Boolean
If chkPool.Checked Then
parampool.Value = 1
Else
parampool.Value = 0
End If
cmd.Parameters. Add(parampool)
Dim paramcomments As New SqlClient.SqlPa rameter
paramcomments.P arameterName = "@Comments"
paramcomments.D bType = DbType.String
paramcomments.V alue = txtComments.Tex t
paramcomments.S ize = txtComments.Tex t.Length
paramcomments = cmd.Parameters. Add(paramcommen ts)
Dim parampic1 As New SqlClient.SqlPa rameter
parampic1.Param eterName = "@Picture1"
parampic1.DbTyp e = DbType.Object
parampic1.IsNul lable = True
If Picture1 IsNot Nothing Then
parampic1.Size = Picture1.Length
End If
parampic1.Value = Picture1
cmd.Parameters. Add(parampic1)
Dim parampic2 As New SqlClient.SqlPa rameter
parampic2.Param eterName = "@Picture2"
parampic2.DbTyp e = DbType.Object
parampic2.IsNul lable = True
If Picture2 IsNot Nothing Then
parampic2.Size = Picture2.Length
End If
parampic2.Value = Picture2
cmd.Parameters. Add(parampic2)
Dim parampic3 As New SqlClient.SqlPa rameter
parampic3.Param eterName = "@Picture3"
parampic3.DbTyp e = DbType.Object
parampic3.IsNul lable = 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.SqlPa rameter
'parampic2 = cmd.Parameters. Add("@Picture2" , SqlDbType.Image ).Value
= Picture2
'Dim parampic3 As New SqlClient.SqlPa rameter
'parampic3 = cmd.Parameters. Add("@Picture3" , SqlDbType.Image ).Value
= Picture3
Dim paramid As New SqlClient.SqlPa rameter
paramid = cmd.Parameters. Add("@id", SqlDbType.Uniqu eIdentifier)
paramid.Directi on = ParameterDirect ion.Output
Dim paramrows As New SqlClient.SqlPa rameter
paramrows = cmd.Parameters. Add("@rowcount_ var", SqlDbType.Int)
paramrows.Direc tion = ParameterDirect ion.Output
Dim paramerr As New SqlClient.SqlPa rameter
paramerr = cmd.Parameters. Add("@error_tex t", SqlDbType.NVarC har, 255)
paramerr.Direct ion = ParameterDirect ion.Output
Dim result As Boolean
Try
result = cmd.ExecuteNonQ uery()
Catch ex As SqlClient.SqlEx ception
MsgBox(ex.Messa ge)
End Try
MsgBox("error text = " &
cmd.Parameters( "@error_text"). Value.ToString & vbCr & "id = " &
cmd.Parameters( "@id").Value.To String & vbCr & "rowcount = " &
cmd.Parameters( "@rowcount_var" ).Value.ToStrin g)
conn.Close()
conn = Nothing
End Sub
stored procedure code:
ALTER PROCEDURE dbo.usp_AddProj ect
/*
(
@parameter1 int = 5,
@parameter2 datatype OUTPUT
)
*/
(
@ProjectName nvarchar(100),
@PropertyAddres s nvarchar(150),
@ProjectBudget money,
@PropertyType nvarchar(50),
@NumberBedrooms smallint,
@NumberBathroom s smallint,
@LandSqFeet float,
@InteriorSqFeet float,
@Heating bit = 0,
@AirConditionin g bit = 0,
@EnclosedGarage bit = 0,
@Pool bit = 0,
@Comments nvarchar(2000),
@Picture1 image = null,
@Picture2 image = null,
@Picture3 image = null,
@id uniqueidentifie r = 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,Proj ectName,
PropertyAddress ,ProjectBudget, PropertyType,
NumberBedrooms, NumberBathrooms ,LandSqFeet,
InteriorSqFeet, Heating,AirCond itioning,Enclos edGarage,
Pool,Comments,P icture1,Picture 2,Picture3) values
(@id,@Projectna me,@PropertyAdd ress,@ProjectBu dget,
@PropertyType,@ NumberBedrooms, @NumberBathroom s,
@LandSqFeet,@In teriorSqFeet,@H eating,@AirCond itioning,
@EnclosedGarage ,@Pool,@Comment s,@Picture1,@Pi cture2,
@Picture3)
set @error_var = @@error;
set @rowcount_var = @@rowcount;
if @error_var <> 0
begin
select @error_text = (select description from sysmessages where
error=@error_va r)
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 the follwoing code in VB 6,
How can I tell if the insert executed ok?
I have to add error checking to a project that uses this
|
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 number and page size), or to
return IDs of previous and next records (based on current record id).
the problem is, that the order in which records are...
|
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 experts here agree with the following? Would they add any other
points?
1. If the shop standard calls for logging of application errors, a stored...
|
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 Throwable as an OUT
parameter and what datatype should I use in the CREATE PROCEDURE and DROP
PROCEDURE statements?
Here's what I tried:
- the method...
|
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 the INSERT in the SPL. I got an error stating that
there was no open cursor.
I have done some research on here and found that it's not
possible to...
| | |
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 several stored procedures to Insert and update datas in our
SQL database. Some stored procedures are smaller (insert datas in only
one table) and some...
|
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 sqldatasource control,
there is no need of opening and closing a connection. Also there is no
need to write connection string. When i am selecting...
|
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 "Command(s) Completed Successfully" but when I open the table there are no records in it. The csv file has two rows in it. The first one is the column names...
|
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 fine.
Now we decided to move from mainframe IMS-DB2 to Windows 2003 server-DB2 UDB
for LUW 9.5.
|
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 effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
|
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, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
| | |
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 tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
|
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 protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
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 then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert...
|
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 last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| | |
