SQL Stored Procedure problem
SQL Stored Procedure problem
SQL Stored Procedure problem
SQL Stored Procedure problem
SQL Stored Procedure problem
SQL Stored Procedure problem SQL Stored Procedure problem SQL Stored Procedure problem SQL Stored Procedure problem SQL Stored Procedure problem SQL Stored Procedure problem SQL Stored Procedure problem SQL Stored Procedure problem
SQL Stored Procedure problem SQL Stored Procedure problem
SQL Stored Procedure problem
Go Back  Xtreme Visual Basic Talk > > > SQL Stored Procedure problem


Reply
 
Thread Tools Display Modes
  #21  
Old 08-29-2012, 02:22 PM
FabioRochaPT FabioRochaPT is offline
Newcomer
 
Join Date: Mar 2010
Posts: 22
Default


Quote:
Originally Posted by DrPunk View Post
Good point, I didn't even spot that.

Yeah, you'd have to go dynamic for that. You don't necessarily need to declare the @query variable and set it though. You could just have whatever you set @query to in the Exec instead.

Just be aware that you lose the benefits of using stored procedures by using dynamic SQL. Also it's quite a large security risk, what you're doing. It's probably worth reading up on SQL injection.
Thats how i got here, i used to code with concatenation in vb. Then read about sql injection and go with parameters and then finally stored procedures for all its benefits.

Anyway it didnt work. So i need to change the way i handle the vb code? In SQL there is no other way?

Edit: I guess i could use if's as in:

If @tableparameter = Bank Account
Update bankaccount
if @tableparameter = cash
update cash
and so on?

I will have many lines of code but i dont see the problem in it.

Last edited by FabioRochaPT; 08-29-2012 at 02:29 PM.
Reply With Quote
  #22  
Old 08-29-2012, 02:42 PM
DrPunk's Avatar
DrPunkSQL Stored Procedure problem DrPunk is offline
Senior Contributor

* Expert *
 
Join Date: Apr 2003
Location: Never where I want to be
Posts: 1,403
Default

That's a much safer way of doing it.

But I don't really understand why you can't have an UpdateBankAccount stored procedure and an UpdateCash stored procedure. Seems like you're maybe trying to make the application code a bit simpler, less code to write with less procedures to call. But if you're gonna do something right...
__________________
There are no computers in heaven!
Reply With Quote
  #23  
Old 08-29-2012, 02:59 PM
FabioRochaPT FabioRochaPT is offline
Newcomer
 
Join Date: Mar 2010
Posts: 22
Default

Quote:
Originally Posted by DrPunk View Post
That's a much safer way of doing it.

But I don't really understand why you can't have an UpdateBankAccount stored procedure and an UpdateCash stored procedure. Seems like you're maybe trying to make the application code a bit simpler, less code to write with less procedures to call. But if you're gonna do something right...
Im gonna try to be clear:

I have 3 tables: Cash, Accounts and Credit Cards.
I have 1 form for all the transferences.

So if you make a bank deposit, on the Main form ive this:
Code:
Private Sub DepositToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles DepositToolStripMenuItem.Click
        tabelafrom = "Cash"
        tabelato = "Account"
        Documento = "Bank Deposit"
        Documento2 = "Deposit your money into a bank account"
        frmTransfer.ShowDialog()
    End Sub
So on the Transfer forms im gonna load 2 combobox's: Tabelafrom and Tabelato.

User is gonna fill in all the textbox's (amount and so) and press Save.

Then i need to insert the record into table TRANSACTIONS, and update the balance on the table cash and the table Account.

ive 5 operations (for now):

Cash -> Bank Account
Bank Account -> Cash
Credit card -> Cash
Cash -> Credit Card
Bank Account -> Bank Account

All working on the same form.

Problem: If somehow i have an error updating the balance of a table ive already inserted the data into the Transactions table.

So the stored procedure comes in handy to avoid errors, as i told you, if something goes wrong it does rollback without updating or inserting any data into any table.

Please be patient with me, i know its complicated and that im just a newbie, but to me this is an amazing process of learning.

All your help is greatly appreciated.
Reply With Quote
  #24  
Old 08-29-2012, 03:13 PM
DrPunk's Avatar
DrPunkSQL Stored Procedure problem DrPunk is offline
Senior Contributor

* Expert *
 
Join Date: Apr 2003
Location: Never where I want to be
Posts: 1,403
Default

It's basically about where you want to put the logic.

All those Ifs that you were talking about in the stored procedure could just as easily be part of the application instead (although technically speaking I'm not sure Ifs on a tablename can be classed as logic). As I mentioned before, the application can also do transactions on any number of Commands and roll back if any one of them goes wrong so it's not like stored procedures are really gaining you much in that respect.

That's not to say that the logic shouldn't be in the stored procedures. It's down to however you want to do it. But if security somehow becomes compromised then there's probably a better way of doing it. It'll probably mean a bit more work too.

5 actions sounds like 5 stored procedures to me. I wouldn't have thought those 5 actions need telling what table names to update. If the actions share a common step (like adding to the transaction table) then that would be a stored procedure in itself which the 5 actions call.
__________________
There are no computers in heaven!

Last edited by DrPunk; 08-29-2012 at 03:28 PM.
Reply With Quote
  #25  
Old 08-29-2012, 03:32 PM
FabioRochaPT FabioRochaPT is offline
Newcomer
 
Join Date: Mar 2010
Posts: 22
Default

But then i would have to have 5 different forms? I need to think it through and its getting late here. Today was fun tomorrow is a new day.

Thanks for your time DrPunk, if i get to any conclusion ill come back.
Reply With Quote
  #26  
Old 08-29-2012, 04:31 PM
DrPunk's Avatar
DrPunkSQL Stored Procedure problem DrPunk is offline
Senior Contributor

* Expert *
 
Join Date: Apr 2003
Location: Never where I want to be
Posts: 1,403
Default

Quote:
Originally Posted by FabioRochaPT View Post
But then i would have to have 5 different forms?
No, not if you don't want it to. Your form does the Ifs you were talking about, rather than the stored procedure, and runs the appropriate stored procedure. But it could be 5 forms all the same. Doesn't matter. Not as far as the stored procedures are concerned. That's down to the design of the interface, not the "Bank".

The single form's logic could be along the lines of...
Code:
Dim cmd as New sqlCommand

cmd.commandtype = storedprocedure

If tableAFrom = "Cash" and tableATo = "Account" Then

    cmd.commandtext = "CashToAccount"

    ' set up this command's parameters and values
    cmd.parameters.add("@origin", varchar, 100).value = origin
    cmd.parameters.add("@amount", decimal, 9).value = amount

elseif tableAFrom = "Account" and tableATo = "Cash" then
    
   cmd.commandtext = "AccountToCash"

   ' values and parameters for this command...

elseif etc. ' another 3 to do if there's 5 actions
   
endif

dim affect as integer
' run the command, yes I know it doesn't have a connection here
affect = cmd.executenonquery

' clean up afterwards...
Your program could use better variables to store the various options in than strings that you're currently using to send to the stored procedure.

I'd tend to look at it that the program shouldn't be telling the Bank what to do (i.e. what table names to update) but the Bank should be telling the program what it can do (i.e. what stored procedures can be called). How the program calls them is up to whoever writes the program.
__________________
There are no computers in heaven!

Last edited by DrPunk; 08-29-2012 at 05:30 PM.
Reply With Quote
  #27  
Old 08-31-2012, 05:07 AM
FabioRochaPT FabioRochaPT is offline
Newcomer
 
Join Date: Mar 2010
Posts: 22
Default

But doing as you say i won't be able to handle the errors.

Bank Deposit = Cash -> Bank Account

Stored Procedure to insert into Transactions table
Stored Procedure to Update Cash Balance
Stored Procedure to Update Bank Account Balance

Lets say that cash doesnt have money, so i insert the transaction into the table and then i get an error (field balance can't be negative).

I will already have the record inserted into the transaction table but it didnt happen because there was an error.


Edit: Unless there is not a stored procedure to insert into transaction table, but every procedure will do that.

That way it will always only execute one stored procedure making it perfect to commit / rollback.
Reply With Quote
  #28  
Old 08-31-2012, 05:25 AM
DrPunk's Avatar
DrPunkSQL Stored Procedure problem DrPunk is offline
Senior Contributor

* Expert *
 
Join Date: Apr 2003
Location: Never where I want to be
Posts: 1,403
Default

Nothing I've said so far stops anything handling errors. The structure that I'm talking about, each of the 5 stored procecudure actions handles its own errors.

For example...
Code:
Procedure CashToAccount

    BEGIN TRANSACTION

    BEGIN TRY
        InsertTransaction
        UpdateCash
        UpdateAccount

        COMMIT
    END TRY

    BEGIN CATCH
        ROLLBACK

        THROW
    END CATCH
InsertTransaction, UpdateCash and UpdateAccount don't have to have any error handling in them in this setup.

You haven't mentioned checking balances before. That's more logic that needs to go somewhere. But that probably shouldn't be part of the insert in transactions table. You should really be checking that yourself, and it's part of the CashToAccount's routine.
Code:
Procedure CashToAccount

     IF NoMoneyInCash THEN
         ' Can't transfer anything from Cash To Account because there is nothing
         ' in cash

         ' You could throw a custom error here for the calling application to handle

     ELSE
         ' The transfer can be done

         BEGIN TRANSACTION

         BEGIN TRY
             InsertTransaction
             UpdateCash
             UpdateAccount

             COMMIT
         END TRY

         BEGIN CATCH
             ROLLBACK

             THROW
         END CATCH
    END IF
__________________
There are no computers in heaven!
Reply With Quote
  #29  
Old 08-31-2012, 05:28 AM
FabioRochaPT FabioRochaPT is offline
Newcomer
 
Join Date: Mar 2010
Posts: 22
Default

It's working good. My problem now is: How does VB knows if all went well?

Quote:
Dim x As Integer = cmd.ExecuteNonQuery() ' Se dados introduzidos na tabela transaccoes entao faz update ao balance das tabelas utilizadas
If x = 1 Then
MessageBox.Show(" Transference executed.", "Family Budget 2012", MessageBoxButtons.OK, MessageBoxIcon.Information)
I was doing this but the Executenonquery is return different values.

Any idea?
Reply With Quote
  #30  
Old 08-31-2012, 05:35 AM
DrPunk's Avatar
DrPunkSQL Stored Procedure problem DrPunk is offline
Senior Contributor

* Expert *
 
Join Date: Apr 2003
Location: Never where I want to be
Posts: 1,403
Default

You could check if x > 0 instead.

But if you are throwing errors from the stored procedures, then ideally you should be catching them in your program's code and then if no errors are thrown then you can assume that everything went OK.

Code:
Try
    cmd.ExecuteNonQuery ' If this throws an error then it jumps straight to catch ex, missing anything out in between

    messagebox.show("Success")

Catch ex as exception
    messagebox.show("Failed")

End Catch
You could return your own stuff from the stored procedure too.
__________________
There are no computers in heaven!
Reply With Quote
  #31  
Old 08-31-2012, 05:42 AM
FabioRochaPT FabioRochaPT is offline
Newcomer
 
Join Date: Mar 2010
Posts: 22
Default

Code:
    cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.Add("@ID", SqlDbType.Int).Value = Identify
                cmd.Parameters.Add("@Document", SqlDbType.NChar).Value = frmMain.Documento
                cmd.Parameters.Add("@Doc_date", SqlDbType.Date).Value = DPdate.Value.Date
                cmd.Parameters.Add("@Origin", SqlDbType.NChar).Value = accnamefrom
                cmd.Parameters.Add("@Destiny", SqlDbType.NChar).Value = accnameto
                cmd.Parameters.Add("@Amount", SqlDbType.Decimal, 9).Value = txtAmount.Text
                cmd.Parameters.Add("@Info", SqlDbType.NChar).Value = txtInfo.Text

                If frmMain.tabelafrom = "CC" Then
                    cmd.CommandText = "ATMDeposit"
                ElseIf frmMain.tabelato = "CC" Then
                    cmd.CommandText = "ATMCollect"
                ElseIf frmMain.tabelafrom = "Account" And frmMain.tabelato = "Account" Then
                    cmd.CommandText = "BankTransfer"
                ElseIf frmMain.tabelafrom = "Cash" And frmMain.tabelato = "Account" Then
                    cmd.CommandText = "BankDeposit"
                ElseIf frmMain.tabelafrom = "Account" And frmMain.tabelato = "Cash" Then
                    cmd.CommandText = "BankCollect"
                End If

                Try
                    cmd.ExecuteNonQuery() ' Se as Stored Procedure enviar erro ele salta para o catch
                    MessageBox.Show(" Transference executed.", "Family Budget 2012", MessageBoxButtons.OK, MessageBoxIcon.Information)
                Catch Ex As Exception
                MessageBox.Show("Error found: " & vbCrLf & Ex.Message)
            End Try
So far its all working good.

Thank you very much for all your help and knowledge. Now gonna start working on Payments, try to work with Payments header and lines (using editable datagrid).

Meaning i'll probably be seeing your around

Once again thanks for the help.
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
SQL Stored Procedure problem
SQL Stored Procedure problem
SQL Stored Procedure problem SQL Stored Procedure problem
SQL Stored Procedure problem
SQL Stored Procedure problem
SQL Stored Procedure problem SQL Stored Procedure problem SQL Stored Procedure problem SQL Stored Procedure problem SQL Stored Procedure problem SQL Stored Procedure problem SQL Stored Procedure problem
SQL Stored Procedure problem
SQL Stored Procedure problem
 
SQL Stored Procedure problem
SQL Stored Procedure problem
 
-->