[RESOLVED] Transfering Data from one part to other of database to other!!!

[gautamshaw]

The form is like this:

A database contains six columns:
"From Bank","From Account","From Balance","To Bank","To Account","To Balance"

On transfer button click,The balanced will be transfered and added to the "To Balance" of "To Account"
i did the code for form load but i cant get throught the transfer money code:

Imports System.Data
Imports System.Data.OleDb
Public Class Form1
    Dim con As New OleDbConnection
    Dim cmd As New OleDbCommand
    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        con = New OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;Data Source=C:\Users\Gautam\Documents\Visual Studio 2005\Projects\Transfer\Transfer\db1.mdb")
        con.Open()
        cmd = New OleDbCommand("Select * from table1", con)
        Dim da As New OleDbDataAdapter(cmd)
        Dim dt As New DataTable
        da.Fill(dt)
        con.Close()
        ComboBox1.DataSource = dt
        ComboBox1.DisplayMember = "From_Bank_Name"
        ComboBox1.Text = ""
        ComboBox2.DataSource = dt
        ComboBox2.DisplayMember = "From_Account_No"
        ComboBox2.Text = ""
        ComboBox3.DataSource = dt
        ComboBox3.DisplayMember = "To_Bank_Name"
        ComboBox3.Text = ""
        ComboBox4.DataSource = dt
        ComboBox4.DisplayMember = "To_Account_No"
        ComboBox4.Text = ""
    End Sub

    Private Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click

    End Sub
End Class

now in button click i want to perform this:
Original balance(Sender)=Original balance-From_Balance
Original balance(Receiver)=Original balance+TO_balance
then
From_Balance=Original balance(Sender)
To_Balance=Original balance(Receiver)

Am I getting it right?

Please help!!!

[keystone_paul]

I would create and execute two SQL statement something along the lines of :

UDPATE table1 SET balance = balance + @sum WHERE BankName = @RecipientBankName AND AccountNo = @RecipientAccountNo

and

UDPATE table1 SET balance = balance - @sum WHERE BankName = @SenderBankName AND AccountNo = @SenderAccountNo

Where you replace the parameters @sum, @senderbankname, @senderaccountno, @recipientbankname and @recipientaccountno with the values from the controls on your form.

[gautamshaw]

 Private Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        Try
            con = New OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;Data Source= C:\Users\Gautam\Documents\Visual Studio 2005\Projects\Transfer\Transfer\db1.mdb")
            con.Open()
            cmd = New OleDbCommand(("Update Table1 set From_Balance= From_Balance - ' " & TextBox1.Text.Trim() & " ' where From_Bank_Name='" & ComboBox1.Text.Trim() & "' and From_Account_No='" & ComboBox2.Text.Trim() & "' ") _
And ("Update Table1 set To_Balance= To_Balance + ' " & TextBox2.Text.Trim() & " ' where To_Bank_Name= '" & ComboBox3.Text.Trim() & "' and To_Account_No='" & ComboBox4.Text.Trim() & "' "), con)
            cmd.ExecuteNonQuery()
            Dim rowsaffected As Integer = cmd.ExecuteNonQuery
            MsgBox("Number of rows affected : " + cmd.ExecuteNonQuery.ToString)
        Catch ex As Exception

        End Try
    End Sub

Its giving an exceotion:
Attachment 72095

Please hlp me.....

[keystone_paul]

You should be using parameters in your SQL rather than concatenating strings.

If you do that then it will be easier to work out what is wrong.

[GaryMazzone]

A couple of thoughts
First
(("Update Table1 set From_Balance= From_Balance - ' " & TextBox1.Text.Trim() & " ' where

There should not be a single qoute around the textbox1.Text.Trim(). This should be a number not a string.

Second you should be testing for a valid number being in the textbox before attempting to use that value in an SQL Statement.

Second point

There shoud not be an AND between the SQL Statements. That is not a vaild statement. What is the backend database? If MS Access then you must perform 2 seperate command.ExecutesNonQuery with two seperate SQL strings. If MS SQL server then seperate the two Update statements using a semi-colin (

[gautamshaw]

Imports System.Data
Imports System.Data.OleDb
Public Class Form1
    Dim con As New OleDbConnection
    Dim cmd As New OleDbCommand
    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        con = New OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;Data Source=C:\Users\Gautam\Documents\Visual Studio 2005\Projects\Transfer\Transfer\db1.mdb")
        con.Open()
        cmd = New OleDbCommand("Select * from Table1", con)
        Dim da As New OleDbDataAdapter(cmd)
        Dim dt As New DataTable
        da.Fill(dt)
        con.Close()
        ComboBox1.DataSource = dt
        ComboBox1.DisplayMember = "From_Bank_Name"
        ComboBox1.Text = ""
        ComboBox2.DataSource = dt
        ComboBox2.DisplayMember = "From_Account_No"
        ComboBox2.Text = ""
        ComboBox3.DataSource = dt
        ComboBox3.DisplayMember = "To_Bank_Name"
        ComboBox3.Text = ""
        ComboBox4.DataSource = dt
        ComboBox4.DisplayMember = "To_Account_No"
        ComboBox4.Text = ""
    End Sub

    Private Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        Try
            con = New OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;Data Source= C:\Users\Gautam\Documents\Visual Studio 2005\Projects\Transfer\Transfer\db1.mdb")
            con.Open()
            ' cmd = New OleDbCommand(("Update Table1 set From_Balance= From_Balance - ' " & TextBox1.Text.Trim() & " ' where From_Bank_Name='" & ComboBox1.Text.Trim() & "' and From_Account_No='" & ComboBox2.Text.Trim() & "' ") _
            'And ("Update Table1 set To_Balance= To_Balance + ' " & TextBox2.Text.Trim() & " ' where To_Bank_Name= '" & ComboBox3.Text.Trim() & "' and To_Account_No='" & ComboBox4.Text.Trim() & "' "), con)
            cmd = New OleDbCommand("Update Table1 set From_Balance= From_Balance-@Fsum where From_Bank_Name=@FBankName and  From_Account_No=@FAccountNo", con)
            cmd.Parameters.AddWithValue("@Fsum", TextBox1.Text)
            cmd.Parameters.AddWithValue("@FBankName", ComboBox1.Text)
            cmd.Parameters.AddWithValue("@FAccountNo", ComboBox2.Text)
            cmd = New OleDbCommand("Update Table1 set To_Balance= To_Balance+@Tsum where To_Bank_Name=@TBankName and  To_Account_No=@TAccountNo", con)
            cmd.Parameters.AddWithValue("@Tsum", TextBox2.Text)
            cmd.Parameters.AddWithValue("@TBankName", ComboBox3.Text)
            cmd.Parameters.AddWithValue("@TAccountNo", ComboBox4.Text)
            cmd.ExecuteNonQuery()
            Dim rowsaffected As Integer = cmd.ExecuteNonQuery
            MsgBox("Number of rows affected : " + cmd.ExecuteNonQuery.ToString)
        Catch ex As Exception

        End Try
    End Sub
End Class

I did this sir.........
It runs well..........msgbox displays 1 row affected..............

But sir no change in the database............

Help me out of this sir........

[GaryMazzone]

You do realize you are only performing the last update right? What is the backend database and how are you checking that no change is occuring. What is the resutlr of rowsaffected?

[gautamshaw]

At the right hand end tere you can see the db1.mdb....
Attachment 72096
After the msgbox displays.......i just double click that db1.mdb and check the output......
How to get the desired output sir........
should i change the update query.........
i cant guess where is my mistake sir..........
Please help me sir...........

[gautamshaw]

the result of rows affected is 1 but no change in the data base.....
how to solve this problem?

[GaryMazzone]

Look at this:

http://go.internet.com/?id=474X1146&...8VS.80%29.aspx

[gautamshaw]

Reading the site sir I think i have done things right............

Copies the database and pasted in the correct place........the db1 even appears in the bin folder......

Then why is my above code not working?

Please help me sir......

[GaryMazzone]

Please print out and show the sql statement that is generated before the execution of that statement on the database

[gautamshaw]

I cant follow what are you asking for........

cmd = New OleDbCommand("Update Table1 set From_Balance= From_Balance-@Fsum where From_Bank_Name=@FBankName and  From_Account_No=@FAccountNo", con)
            cmd.Parameters.AddWithValue("@Fsum", TextBox1.Text)
            cmd.Parameters.AddWithValue("@FBankName", ComboBox1.Text)
            cmd.Parameters.AddWithValue("@FAccountNo", ComboBox2.Text)
            cmd = New OleDbCommand("Update Table1 set To_Balance= To_Balance+@Tsum where To_Bank_Name=@TBankName and  To_Account_No=@TAccountNo", con)
            cmd.Parameters.AddWithValue("@Tsum", TextBox2.Text)
            cmd.Parameters.AddWithValue("@TBankName", ComboBox3.Text)
            cmd.Parameters.AddWithValue("@TAccountNo", ComboBox4.Text)
            cmd.ExecuteNonQuery()
            Dim rowsaffected As Integer = cmd.ExecuteNonQuery
            MsgBox("Number of rows affected : " + cmd.ExecuteNonQuery.ToString)
        Catch ex As Exception

[GaryMazzone]

First off why are you executing the statement twice?

cmd.ExecuteNonQuery() 1st execure
Dim rowsaffected As Integer = cmd.ExecuteNonQuery secondExecute

Comment out the first on and only run the second.

[gautamshaw]

I commented the firs statement sir...........
Now what to do?

[keystone_paul]

OK part of the problem is that because you are using Access not SQL Server you can't use named parameters so you need to make those @Fsum etc just "?" instead.

But that said if the query says it updated 1 row then it updated 1 row and the odds are you are looking at the wrong database, or the database is getting overwritten.

[GaryMazzone]

The other issue with parameters and Access is that they must be added in the same order as they appear in the statement. Since they are not named they are ordinal in how the load.

[gautamshaw]

you are using Access not SQL Server you can't use named parameters so you need to make those @Fsum etc just "?" instead.

I did this sir but still no result.................

I think the first update query is over written by the 2nd one..........hence no output........

Am i right sir?

[keystone_paul]

I think the first update query is over written by the 2nd one..........hence no output........

You are right... you aren't executing the first one - you set it up and then you overwrite it without executing it.

[gautamshaw]

This is my daabase:
Attachment 72098
Here lies my code:

Private Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        Try
            con = New OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;Data Source= C:\Users\Gautam\Documents\Visual Studio 2005\Projects\Transfer\Transfer\db1.mdb")
            con.Open()
            'cmd = New OleDbCommand(("Update Table1 set From_Balance= From_Balance -  " & TextBox1.Text.Trim() & "  where From_Bank_Name='" & ComboBox1.Text.Trim() & "' and From_Account_No='" & ComboBox2.Text.Trim() & "' ") _
            'And ("Update Table1 set To_Balance= To_Balance +  " & TextBox2.Text.Trim() & "  where To_Bank_Name= '" & ComboBox3.Text.Trim() & "' and To_Account_No='" & ComboBox4.Text.Trim() & "' "), con)
            cmd = New OleDbCommand("Update Table1 set From_Balance= From_Balance - ?Fsum where From_Bank_Name = ?FBankName and  From_Account_No = ?FAccountNo", con)
            cmd.Parameters.AddWithValue("?Fsum", TextBox1.Text)
            cmd.Parameters.AddWithValue("?FBankName", ComboBox1.Text)
            cmd.Parameters.AddWithValue("?FAccountNo", ComboBox2.Text)
            cmd = New OleDbCommand("Update Table1 set To_Balance= To_Balance + ?Fsum where To_Bank_Name = ?TBankName and  To_Account_No = ?TAccountNo", con)
            ' cmd.Parameters.AddWithValue("?Tsum", TextBox2.Text)
            cmd.Parameters.AddWithValue("?TBankName", ComboBox3.Text)
            cmd.Parameters.AddWithValue("?TAccountNo", ComboBox4.Text)
            'cmd.ExecuteNonQuery()
            Dim rowsaffected As Integer = cmd.ExecuteNonQuery
            MsgBox("Number of rows affected : " + cmd.ExecuteNonQuery.ToString)
        Catch ex As Exception

        End Try
    End Sub
End Class

Where lies the defect?

[GaryMazzone]

Try this and what happend?\

cmd = New OleDbCommand("Update Table1 set From_Balance= From_Balance-@Fsum where From_Bank_Name=@FBankName and  From_Account_No=@FAccountNo", con)
cmd.Parameters.AddWithValue("@Fsum", TextBox1.Text)
cmd.Parameters.AddWithValue("@FBankName", ComboBox1.Text)
cmd.Parameters.AddWithValue("@FAccountNo", ComboBox2.Text)
Dim rowseffected1 as Integer - cmd.ExecuteNonQuery()
cmd2 = New OleDbCommand("Update Table1 set To_Balance= To_Balance+@Tsum where To_Bank_Name=@TBankName and  To_Account_No=@TAccountNo", con)
cmd2.Parameters.AddWithValue("@Tsum", TextBox2.Text)
cmd2.Parameters.AddWithValue("@TBankName", ComboBox3.Text)
cmd2.Parameters.AddWithValue("@TAccountNo", ComboBox4.Text)
Dim rowsaffected As Integer = cmd2.ExecuteNonQuery
cmd.Dispose()
cmd2.Dispose()
MsgBox("Number of rows affected from first Query are: " & rowseffected1.ToString())
MsgBox("Number of rows affected from second Query are: " & rowseffected.ToString())

ps Paul I tried to tell him that at least once already

[gautamshaw]

Then sir how to modify this so that both the updation take place at the same time?
Should i do it using the and clause between the two update statements?
What should i do sir?

[GaryMazzone]

Look at post 21. That is the only way using Access

[keystone_paul]

Try this and what happend?\

cmd = New OleDbCommand("Update Table1 set From_Balance= From_Balance-@Fsum where From_Bank_Name=@FBankName and  From_Account_No=@FAccountNo", con)
cmd.Parameters.AddWithValue("@Fsum", TextBox1.Text)
cmd.Parameters.AddWithValue("@FBankName", ComboBox1.Text)
cmd.Parameters.AddWithValue("@FAccountNo", ComboBox2.Text)
Dim rowseffected1 as Integer = cmd.ExecuteNonQuery()
cmd2 = New OleDbCommand("Update Table1 set To_Balance= To_Balance+@Tsum where To_Bank_Name=@TBankName and  To_Account_No=@TAccountNo", con)
cmd2.Parameters.AddWithValue("@Tsum", TextBox2.Text)
cmd2.Parameters.AddWithValue("@TBankName", ComboBox3.Text)
cmd2.Parameters.AddWithValue("@TAccountNo", ComboBox4.Text)
Dim rowsaffected As Integer = cmd2.ExecuteNonQuery
cmd.Dispose()
cmd2.Dispose()
MsgBox("Number of rows affected from first Query are: " & rowseffected1.ToString())
MsgBox("Number of rows affected from second Query are: " & rowseffected.ToString())

ps Paul I tried to tell him that at least once already

Slight typo above - corrected in green above.

I think also the problem is that the database table isn't what I would expect, surely it should just have bank name, account number and balance only. Instead it seems to have the to and from halves of the equation.

And with that I am saying goodnight - don't want to get trapped in another 4 page marathon like yesterday!

[gautamshaw]

Errors in:

Dim rowseffected1 as Integer - cmd.ExecuteNonQuery()

End of statement expected

 cmd2 = New OleDbCommand("Update Table1 set To_Balance= To_Balance+@Tsum where To_Bank_Name=@TBankName and  To_Account_No=@TAccountNo", con)

cmd2 not declared

MsgBox("Number of rows affected from first Query are: " & rowsaffected1.ToString())

rowsaffected1 not declared...........


How to rectify these errors sir!!!

[gautamshaw]

I removed the 2nd one.........
how to remove the 1st and 3rd one?

[gautamshaw]

Dim rowsaffected As Integer = cmd2.ExecuteNonQuerycmd.Dispose()

The only error lies in this statement sir.....I resolved the rest

[gautamshaw]

Dim rowsaffected As Integer = cmd2.ExecuteNonQuerycmd.Dispose()

The error is:
ExecuteNonQuerycmd is not a member of system.data.oledb.oledbcommand....
Please help me sir......

[gautamshaw]

I corrected all the errors......
still the code did nt work.......
no msgbox displayed.........
even no change in the database........

Now what to do sir?

[keystone_paul]

Dim rowsaffected As Integer = cmd2.ExecuteNonQuerycmd.Dispose()

The error is:
ExecuteNonQuerycmd is not a member of system.data.oledb.oledbcommand....
Please help me sir......

You have two lines together. It should be

Dim rowsaffected As Integer = cmd2.ExecuteNonQuery
cmd.Dispose()

[GaryMazzone]

That must tell you something...... Like there is an error in the code that you are absoultely ignoring.....

Try 
   .......
Catch ex As Exception
 
End Try

You are placing your hands over your ear and yelling LALA-LALA-LALA All is perfect. Meanwhile VB is trying to tell you something is wrong

Ps this is my last post to the thread

[gautamshaw]

Now it worked sir..............
Thanks a lot sir...............


I need the concept of the process that you did...........and what was wrong with my code sir?

Please remove my confusions a bit sir......

[gautamshaw]

cmd.Dispose()

What is the use of dispose() sir?