Re: Insert data
- From: BMW <BMW@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Thu, 11 Jan 2007 11:31:01 -0800
Hi Arnie,
There is the script to create table, store procedure and the vb.net code.
The sink component can send the email to me indicate the reuslt value is 1.
However, the data is not added on the table. The SQL server and exchange
server are installed in different PC with same domain.
************************************************************
table script:
/****** Object: Table [dbo].[Result] Script Date: 01/11/2007 11:15:12
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Result](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Result] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Subject] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_Result] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
************************************************************
stroe procedure:
/****** Object: StoredProcedure [dbo].[spDataTest] Script Date:
01/11/2007 11:17:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spDataTest]
-- Add the parameters for the stored procedure here
@Subject varchar(50),
@Result nvarchar(100)
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN TRANSACTION
declare @row int
insert into dbo.Result (Subject, Result)values(@subject, @Result)
set @row=@@rowcount
COMMIT TRANSACTION
return @row
***************************************************************************************
visual code:
Using scope As New TransactionScope()
Using cn As New SqlConnection(strConnection)
cn.Open()
Call testcurrentTransaction(Transaction.Current)
Using cmd As New SqlCommand("dbo.spDataTest", cn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@Subject", SqlDbType.VarChar,
50).Value = "cdo_msg.Subject"
cmd.Parameters.Add("@Result", SqlDbType.VarChar,
100).Value = "testOct 12"
Dim par2 As New SqlParameter("@RowCount",
SqlDbType.Int)
par2.Direction = ParameterDirection.ReturnValue
cmd.Parameters.Add(par2)
cmd.ExecuteNonQuery()
Dim rowsAffected As Integer =
cmd.Parameters("@RowCount").Value
If (rowsAffected > 0) Then
Dim strResult As New StringBuilder
strResult.Append(bstrSubject).Append("Transaction was committed.
rowsAffected=").Append(rowsAffected)
Call sendMessage(strResult.ToString,
cdo_msg.Subject)
scope.Complete()
Else
Dim str As New StringBuilder
str.Append("Failed").Append("
rowsAffected=").Append(rowsAffected)
Call sendMessage(str.ToString, cdo_msg.Subject)
scope.Dispose()
End If
End Using ' Dispose the first command object.
End Using ' Dispose (close) the first connection.
End Using ' Dispose TransactionScope object, to commit or
rollback transaction.
Thanks.
************************************************************
"Arnie Rowland" wrote:
It would help us better assist you if you could include table DDL, query.
strategy used so far, sample data in the form of INSERT statements, and an
illustration of the desired results. (For help with that refer to:
http://www.aspfaq.com/5006 and to
http://classicasp.aspfaq.com/general/how-do-i-make-sure-my-asp-question-gets-answered.html )
The less 'set up' work we have to do, the more likely you are going to have
folks tackle your problem and help you. Without this effort from you, we are
just playing guessing games.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"BMW" <BMW@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:4B998D1D-D459-4374-BFE0-92D1A490DFF5@xxxxxxxxxxxxxxxx
Hi,
I haven't figure out why the store procedure execute withour error, but
the
data doesn't on the table.
I have a store procedure which is call by the component in the exchange
server.
I saw the store procedure is compelted on SQL Server Profiler and the
writer
comlun display '1'. However the data didn't insert on the table. Also the
store procedure returns the value to indicate there were success.
The exchange server connects to the server sql 2005 by using TCp/IP
protocol.
The user is domain admin.
I succeed add one new record only suddenly even the write column did show
'0'. For all tests, the data didn't insert on the table.
Hope someone help me to solve it.
Thanks.
- Prev by Date: Re: Cannot add the data into the table
- Next by Date: Re: Cannot add the data into the table
- Previous by thread: Re: Cannot add the data into the table
- Next by thread: Re: How to retieve previous rows based on query results
- Index(es):
Relevant Pages
|
|
