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 2211
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 discussion thread is closed Replies have been disabled for this discussion. Similar topics
reply
views
Thread by Larry |
last post: by
|
7 posts
views
Thread by Alex Vorobiev |
last post: by
|
reply
views
Thread by Rhino |
last post: by
|
4 posts
views
Thread by Rhino |
last post: by
|
6 posts
views
Thread by harborboy76 |
last post: by
|
2 posts
views
Thread by Eli |
last post: by
|
3 posts
views
Thread by mahajanvit |
last post: by
| |
reply
views
Thread by SOI_0152 |
last post: by
| | | | | | | | | | | |