Executing stored procedure from VS IDE

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am developing a c# program VS 2003 and I have created my own stored procedure to insert data into 3 tables
The following is the stored procedure, the code and the error message from visual studio ide

The following the stored procedure
CREATE PROCEDURE dbo.NewControlAndNormalAccoun


@AccountNo numeric(18)
@AccountName nvarchar(50)
@AccountType char(16)
@Description char(16)
@DebitBalanceBF bigint
@CreditBalanceBF bigint
@Debit bigint
@Credit bigint
@Remarks nvarchar(100)
@OpenBalanceDate datetime
@DateCreated datetime
@NewControlAccountNo numeric OUTPU


A
SET NOCOUNT OF

BEGIN TRANSACTIO

INSERT INTO ChartOfAccounts(AccountNo, AccountName, AccountType, Description, DateCreated, Remarks) VALUES(@AccountNo, @AccountName, @AccountType, @Description, @DateCreated, @Remarks)
SELECT AccountNo, AccountName, AccountType, Description, DateCreated, Remarks From ChartOfAccounts WHERE (TID = @@IDENTITY

IF @@ERROR <>
BEGI
ROLLBACK TRANSACTIO
RETUR
EN

INSERT INTO ControlAccounts(AccountNo, DebitBalanceBF, CreditBalanceBF, Debit, Credit, Remarks, OpenBalanceDate) VALUES(@AccountNo, @DebitBalanceBF, @CreditBalanceBF, @Debit, @Credit, @Remarks, @OpenBalanceDate)
SELECT ControlAccountNo, AccountNo, DebitBalanceBF, CreditBalanceBF, Debit, Credit, Remarks, OpenBalanceDate FROM ControlAccounts WHERE (ControlAccountNo = @@IDENTITY
SET @NewControlAccountNo = @@IDENTIT

IF @@ERROR <>
BEGI
ROLLBACK TRANSACTIO
RETUR
EN


INSERT INTO NormalAccounts(AccountNo, ControlAccountNo, DebitBalanceBF, CreditBalanceBF, Debit, Credit, Remarks, OpenBalanceDate) VALUES(@AccountNo, @NewControlAccountNo, @DebitBalanceBF, @CreditBalanceBF, @Debit, @Credit, @Remarks, @OpenBalanceDate)
SELECT AccountNo, ControlAccountNo, DebitBalanceBF, CreditBalanceBF, Debit, Credit, Remarks, OpenBalanceDate FROM NormalAccounts WHERE (TID = @@IDENTITY

IF @@ERROR <>
BEGI
ROLLBACK TRANSACTIO
RETUR
EN
COMMIT TRANSACTIO
G

The following is the code
private void SaveNewAccount(

tr

ChartOfAccountsFormDataSet.SelectCAndNAccountsRow drNewAcct
this.dsChartOfAccountsForm.SelectCAndNAccounts.NewSelectCAndNAccountsRow()
drNewAcct.AccountName = this.txtAcctName.Text
drNewAcct.AccountNo = Convert.ToDecimal(this.txtAcctNumber.Text)
drNewAcct.AccountType = this.cboAcctType.Text
drNewAcct.Description = this.cboAcctDescription.Text
drNewAcct.Credit = 0
drNewAcct.CreditBalanceBF = 0
drNewAcct.DateCreated = this.dtpAccountDate.Value
drNewAcct.Debit = 0
drNewAcct.DebitBalanceBF = 0
drNewAcct.OpenBalanceDate = this.dtpAccountDate.Value
drNewAcct.Remarks = this.txtRemarks.Text
this.dsChartOfAccountsForm.SelectCAndNAccounts.AddSelectCAndNAccountsRow(drNewAcct)
this.daChartOfAccounts.Update(this.dsChartOfAccountsForm, "SelectCAndNAccounts")

catch(ApplicationException e

MessageBox.Show(e.Message, e.GetType().ToString()
MessageBoxButtons.OK, MessageBoxIcon.Hand)



The following is the error message and the "this.daChartOfAccounts.Update(this.dsChartOfAccountsForm, "SelectCAndNAccounts"); " is highlighted

An unhandled exception of type 'System.InvalidOperationException' occurred in system.data.dl

Additional information: Update requires a valid InsertCommand when passed DataRow collection with new rows

I need help
 
I can't see anywhere where you are telling the adapter to use the insert
procedure, is this being done and I'm just missing it or is this the problem
(from the exception it's a likely culprit.)

HTH,

Bill

www.devbuzz.com
www.knowdotnet.com

Belee said:
I am developing a c# program VS 2003 and I have created my own stored
Click to expand...
procedure to insert data into 3 tables.
The following is the stored procedure, the code and the error message from visual studio ide.

The following the stored procedure:
CREATE PROCEDURE dbo.NewControlAndNormalAccount

(
@AccountNo numeric(18),
@AccountName nvarchar(50),
@AccountType char(16),
@Description char(16),
@DebitBalanceBF bigint ,
@CreditBalanceBF bigint ,
@Debit bigint,
@Credit bigint,
@Remarks nvarchar(100),
@OpenBalanceDate datetime,
@DateCreated datetime,
@NewControlAccountNo numeric OUTPUT
)

AS
SET NOCOUNT OFF

BEGIN TRANSACTION

INSERT INTO ChartOfAccounts(AccountNo, AccountName, AccountType,
Click to expand...
Description, DateCreated, Remarks) VALUES(@AccountNo, @AccountName,
@AccountType, @Description, @DateCreated, @Remarks);
SELECT AccountNo, AccountName, AccountType, Description, DateCreated,
Click to expand...
Remarks From ChartOfAccounts WHERE (TID = @@IDENTITY)
IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
RETURN
END

INSERT INTO ControlAccounts(AccountNo, DebitBalanceBF, CreditBalanceBF,
Click to expand...
Debit, Credit, Remarks, OpenBalanceDate) VALUES(@AccountNo, @DebitBalanceBF,
@CreditBalanceBF, @Debit, @Credit, @Remarks, @OpenBalanceDate);
SELECT ControlAccountNo, AccountNo, DebitBalanceBF, CreditBalanceBF,
Click to expand...
Debit, Credit, Remarks, OpenBalanceDate FROM ControlAccounts WHERE
(ControlAccountNo = @@IDENTITY)
SET @NewControlAccountNo = @@IDENTITY

IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
RETURN
END


INSERT INTO NormalAccounts(AccountNo, ControlAccountNo, DebitBalanceBF,
Click to expand...
CreditBalanceBF, Debit, Credit, Remarks, OpenBalanceDate) VALUES(@AccountNo,
@NewControlAccountNo, @DebitBalanceBF, @CreditBalanceBF, @Debit, @Credit,
@Remarks, @OpenBalanceDate);
SELECT AccountNo, ControlAccountNo, DebitBalanceBF, CreditBalanceBF,
Click to expand...
Debit, Credit, Remarks, OpenBalanceDate FROM NormalAccounts WHERE (TID =
@@IDENTITY)
IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
RETURN
END
COMMIT TRANSACTION
GO


The following is the code:
private void SaveNewAccount()
{
try
{
ChartOfAccountsFormDataSet.SelectCAndNAccountsRow drNewAcct =
this.dsChartOfAccountsForm.SelectCAndNAccounts.NewSelectCAndNAccountsRow();
drNewAcct.AccountName = this.txtAcctName.Text;
drNewAcct.AccountNo = Convert.ToDecimal(this.txtAcctNumber.Text);
drNewAcct.AccountType = this.cboAcctType.Text;
drNewAcct.Description = this.cboAcctDescription.Text;
drNewAcct.Credit = 0;
drNewAcct.CreditBalanceBF = 0;
drNewAcct.DateCreated = this.dtpAccountDate.Value;
drNewAcct.Debit = 0;
drNewAcct.DebitBalanceBF = 0;
drNewAcct.OpenBalanceDate = this.dtpAccountDate.Value;
drNewAcct.Remarks = this.txtRemarks.Text;
this.dsChartOfAccountsForm.SelectCAndNAccounts.AddSelectCAndNAccountsRow(drN
ewAcct);
"SelectCAndNAccounts");
}
catch(ApplicationException e)
{
MessageBox.Show(e.Message, e.GetType().ToString(),
MessageBoxButtons.OK, MessageBoxIcon.Hand);
}
}

The following is the error message and the
Click to expand...
"this.daChartOfAccounts.Update(this.dsChartOfAccountsForm,
"SelectCAndNAccounts"); " is highlighted.
An unhandled exception of type 'System.InvalidOperationException' occurred in system.data.dll

Additional information: Update requires a valid InsertCommand when passed
Click to expand...
DataRow collection with new rows.
 
Back
Top