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?
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
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
GLCode_CH(10) (PK) -primary key to hold code of accounts
GLDescription_VC(75) -the accounts description such as CASH, ACCOUNTS
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
myTrans = myConnection.BeginTransaction
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
ParamParticulars.Value = Particulars 'came from class
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)
TAmount = myTable.Rows(i)(2)
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
Catch ex As Exception
MsgBox(”Transaction Not Added”)
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
Free Guide: Managing storage for virtual environments
Complete a brief survey to get a complimentary 70-page whitepaper featuring the best methods and solutions for your virtual environment, as well as hypervisor-specific management advice from TechTarget experts. Don’t miss out on this exclusive content!
No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.
Your password has been sent to:email@example.com
To follow this tag...
Thanks! We'll email you when relevant content is added and updated.
Share this item with your network: