Inserting rows and multiple rows in SQL Server 2005 Express

5 pts.
Tags:
INSERT statement
SQL Server 2005
SQL Server 2005 error messages
SQL Server 2005 Express
SQL Server stored procedures
Stored Procedures
VB.NET
Visual Basic .NET
Anyone there who can help me solve my problem? I can’t continue my program because of this problem which I know someone can solve or can suggest the best possible solution. I have a project in my study doing simple accounting program. To facilitate the discussion, I will just show you the three tables where I want to insert records - the TransactionTable and the TransactionDetailsTable. I am very confused of how to create a stored procedures to insert a single row of record in TransactionDetailsTable and multiple rows of record in TransactionTable. The GLTable only holds GLCode and its Description. Can you please help me to create the perfect one for my problem? TRANSDETAILSTABLE TransRef_VC(15) PK - the primary key to store the voucher No. TransDate_DT - this will store the date of transaction Particulars_VC(200) - this will store the explanation of transaction TRANSACTIONTABLE TransEntryNum(BigInt) PK -the primary key, autoincrement TransRef_VC(15) FK -the foreign key, this will store the voucher no. GLCode_CH(10) (FK) -the account code such as 1010010000 for CASH TransAmount_MN -the amount of transaction, positive for debit and negative for credit GLTABLE GLCode_CH(10) (PK) -primary key to hold code of accounts GLDescription_VC(75) -the accounts description such as CASH, ACCOUNTS RECEIVABLES, etc. Below is a sample transaction Transaction Reference Number: CR12345 Transaction Date: 12/14/2008 GLCode GL Description Debit Credit ———– ——————— —---——– ---———– 1010010000 CASH 1,500.00 1050020000 Sales Discount 30.00 1050010000 SALES 1,500.00 Particulars: To record cash sales from customers. I created a function under the Class as follows Public Sub AddTransaction(ByVal myTransTable As DataTable, ByVal iret As Integer) I have strConnectionString declared. Dim myConnection As sqlConnection = New SqlConnection(strConnectionString) Dim cmdAddTransEntry, cmdAddTransRef As SqlCommand Dim ParamTransRef, ParamRef, ParamTransDate, ParamParticulars, ParamUserID, ParamGLCode, ParamSLCode, ParamTransAmount, ParamTransCat As SqlParameter Dim myTrans As SqlTransaction Dim strAddTrans As String myConnection.Open() myTrans = myConnection.BeginTransaction Try strAddTrans = “INSERT INTO TransactionDetailsTable(TransRef, TransDate, TransParticulars)” & _ “VALUES(@TransRef, @TransDate, @TransParticulars)” cmdAddTransRef = New SqlCommand(strAddTrans, myConnection) cmdAddTransRef.Transaction = myTrans ParamTransRef = cmdAddTransRef.Parameters.Add(”@TransRef”, SqlDbType.VarChar, 16) ParamTransDate = cmdAddTransRef.Parameters.Add(”@TransDate”, SqlDbType.SmallDateTime, 8) ParamParticulars = cmdAddTransRef.Parameters.Add(”@TransParticulars”, SqlDbType.VarChar, 300) ParamTransRef.Value = VoucherNo 'came from class ParamTransDate.Value = dtSystemDate ‘dtSystemDate is a variable given under Global Module ParamParticulars.Value = Particulars 'came from class cmdAddTransRef.ExecuteNonQuery() strAddTrans = “INSERT INTO TransEntryTable(TransRef, GLCode, SLCode, TransAmount)” & _ “VALUES(@TransRef, @GLCode, @SLCode, @TransAmount)” cmdAddTransEntry = New SqlCommand(strAddTrans, myConnection) cmdAddTransEntry.Transaction = myTrans Dim TAmount As Double = 0 For i = 0 To myTransTable.Rows.Count - 1 ‘ Came from Dataset ‘to determine if debit or credit and if credit make it negative(-) If Not IsNumeric(myTransTable.Rows(i)(2)) Then TAmount = myTable.Rows(i)(3) * (-1) Else TAmount = myTable.Rows(i)(2) End If ParamRef = cmdAddTransEntry.Parameters.Add(”@TransRef”, SqlDbType.VarChar, 16) ParamGLCode = cmdAddTransEntry.Parameters.Add(”@GLCode”, SqlDbType.Char, 10) ParamTransAmount = cmdAddTransEntry.Parameters.Add(”@TransAmount”, SqlDbType.Money, 10) ParamRef.Value = VoucherNo ParamGLCode.Value = myTable.Rows(i)(0) ParamTransAmount.Value = TAmount cmdAddTransEntry.ExecuteNonQuery() Next myTrans.Commit() Catch ex As Exception myTrans.Rollback() MsgBox(”Transaction Not Added”) Finally myConnection.Close() End Try End Sub executing this will return error as follows under cmdAddTransEntry.ExecuteQuery() The variable name ‘@TransRef’ has already been declared. Variable names must be unique within a query batch or stored procedure. I observed that i just encountered an error for the 2nd iteration of the for each loop. Please help me solve this problem. Thanks in advance and more power
ASKED: December 14, 2008  2:16 PM
UPDATED: December 15, 2008  3:18 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following