By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,636 Members | 1,945 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,636 IT Pros & Developers. It's quick & easy.

Executing stored procedure from VS IDE

P: n/a
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.New SelectCAndNAccountsRow()
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.Add SelectCAndNAccountsRow(drNewAcct)
this.daChartOfAccounts.Update(this.dsChartOfAccoun tsForm, "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.dsChartOfAccou ntsForm, "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

Nov 16 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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" <ba***@siliconpro.net> wrote in message
news:C8**********************************@microsof t.com...
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.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, Description, DateCreated, Remarks) VALUES(@AccountNo, @AccountName,
@AccountType, @Description, @DateCreated, @Remarks); SELECT AccountNo, AccountName, AccountType, Description, DateCreated, Remarks From ChartOfAccounts WHERE (TID = @@IDENTITY)
IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
RETURN
END

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 = @@IDENTITY

IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
RETURN
END
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 <> 0
BEGIN
ROLLBACK TRANSACTION
RETURN
END
COMMIT TRANSACTION
GO
The following is the code:
private void SaveNewAccount()
{
try
{
ChartOfAccountsFormDataSet.SelectCAndNAccountsRow drNewAcct =
this.dsChartOfAccountsForm.SelectCAndNAccounts.New SelectCAndNAccountsRow(); 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.Add SelectCAndNAccountsRow(drN
ewAcct); this.daChartOfAccounts.Update(this.dsChartOfAccoun tsForm, "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.dsChartOfAccou ntsForm,
"SelectCAndNAccounts"); " is highlighted.
An unhandled exception of type 'System.InvalidOperationException' occurred in system.data.dll
Additional information: Update requires a valid InsertCommand when passed DataRow collection with new rows.
I need help.


Nov 16 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.