Retrieving and Saving Data in Databases - Page 2-VBForums
Page 2 of 3 FirstFirst 123 LastLast
Results 41 to 80 of 97

Thread: Retrieving and Saving Data in Databases

  1. #41
    ASP.NET Moderator gep13's Avatar
    Join Date
    Nov 2004
    Location
    The Granite City
    Posts
    21,961

    Re: Retrieving and Saving Data in Databases

    In which case, go back and read jmcilhinney's post, #36. It tells you exactly what you need to do.

    Download MySql Connector for .Net.
    Reference the assembly in your project.
    Update the Imports statements at the top of your code file.
    Change all SqlConnection to MySqlConnection, all SqlCommand to MySqlCommand etc.
    Update your code to use Parameters.

    Gary

  2. #42
    ASP.NET Moderator gep13's Avatar
    Join Date
    Nov 2004
    Location
    The Granite City
    Posts
    21,961

    Re: Retrieving and Saving Data in Databases

    Quote Originally Posted by Dsnowdon View Post
    I dont kn
    Hold on. Are you saying you are, or aren't using MySql? Your posts seems contradictory?!?!

    Gary

  3. #43
    Junior Member
    Join Date
    Sep 2010
    Posts
    21

    Re: Retrieving and Saving Data in Databases

    No i am uing MYSQL and im using
    Imports MySql.Data.MySqlClient
    Imports System.Data.SqlClient
    at the top of my code

    I need help with this statment i have changed my code and i get the error on
    vb Code:
    1. Online = CDbl(command.ExecuteScalar())

    This is the whole code

    vb Code:
    1. Dim MySQLConnection As MySqlConnection
    2.         MySqlConnection = New MySqlConnection
    3.         MySqlConnection.ConnectionString = "server=dddd.net;Port=3306; User ID=dddddd; password=ddddd; database=dddddd"
    4.         MySqlConnection.Open()
    5.         Using command As New SqlCommand("SELECT * online FROM users WERE username='" & LoginForm1.UsernameTextBox.Text & "' AND password = '" & LoginForm1.PasswordTextBox.Text & "';")
    6.  
    7.             Online = CDbl(command.ExecuteScalar())
    8.  
    9.         End Using

    Sorry to be a pain

    Thanks dale

  4. #44
    ASP.NET Moderator gep13's Avatar
    Join Date
    Nov 2004
    Location
    The Granite City
    Posts
    21,961

    Re: Retrieving and Saving Data in Databases

    Hey,

    Since you are using MySql, you will not need this:

    Code:
    Imports System.Data.SqlClient
    Okay, you are going to have to help us here...

    What is the error that you are getting?

    Gary

  5. #45
    ASP.NET Moderator gep13's Avatar
    Join Date
    Nov 2004
    Location
    The Granite City
    Posts
    21,961

    Re: Retrieving and Saving Data in Databases

    I am going to guess that is has to do with this:

    Code:
    Using command As New SqlCommand("SELECT * online FROM users WERE username='" & LoginForm1.UsernameTextBox.Text & "' AND password = '" & LoginForm1.PasswordTextBox.Text & "';")
    You are still using SqlCommand.

    Also, you haven't still not using Parameters. I HIGHLY recommend that you start using them. There is a link in my signature explaining why you should use them.

    Gary

  6. #46
    Junior Member
    Join Date
    Sep 2010
    Posts
    21

    Re: Retrieving and Saving Data in Databases

    OK so what should i change SQLCommand to?

  7. #47
    ASP.NET Moderator gep13's Avatar
    Join Date
    Nov 2004
    Location
    The Granite City
    Posts
    21,961

    Re: Retrieving and Saving Data in Databases

    Quote Originally Posted by Dsnowdon View Post
    OK so what should i change SQLCommand to?
    Did you read post #41?

  8. #48
    Junior Member
    Join Date
    Sep 2010
    Posts
    21

    Re: Retrieving and Saving Data in Databases

    OK i have changed that but when it runs it still get a error with




    The Error is
    Code:
    System.InvalidOperationException was unhandled
      Message=Connection must be valid and open.
      Source=MySql.Data

  9. #49
    Junior Member
    Join Date
    Sep 2010
    Posts
    21

    Re: Retrieving and Saving Data in Databases

    It is connected becaue i have or the user has already loged in before this code is ran

  10. #50
    ASP.NET Moderator gep13's Avatar
    Join Date
    Nov 2004
    Location
    The Granite City
    Posts
    21,961

    Re: Retrieving and Saving Data in Databases

    Hey,

    Ok, a slight suggestion, using MySqlConnection as your variable name is a poor choice, since this is the name of the class that you are instantiating.

    Try this:

    Code:
    Using connection As New MySqlConnection("server=dddd.net;Port=3306; User ID=dddddd; password=ddddd; database=dddddd")
        Using command As New MySqlCommand("SELECT * online FROM users WERE username='" & LoginForm1.UsernameTextBox.Text & "' AND password = '" & LoginForm1.PasswordTextBox.Text & "';", connection)
            command.CommandType = CommandType.Text
            connection.Open()
            Online = CDbl(command.ExecuteScalar())
        End Using
    End Using
    Notice that I have passed the MySqlConnection object as the second parameter to the constructor for the MySqlCommand object.

    P.S. Bear in mind that the above was written outside of Visual Studio, and I can't guarantee that it is 100% correct.

    Gary

  11. #51
    Junior Member
    Join Date
    Sep 2010
    Posts
    21

    Re: Retrieving and Saving Data in Databases

    I think that worked but now i have another
    sorry about this its the first timei have used MySQl qith vb.net

    The error i have it
    Code:
    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'online FROM users WERE username='dale' AND password = '*******'' at line 1

  12. #52
    ASP.NET Moderator gep13's Avatar
    Join Date
    Nov 2004
    Location
    The Granite City
    Posts
    21,961

    Re: Retrieving and Saving Data in Databases

    Hey,

    This is basically an error with your actual SQL query. You need to first make sure that the query works directly against the database, before trying to run it through your code. Do you have the MySql Tools installed, so that you can run the query in Query Browser? If not, I would highly recommend that you do this. Make sure you can run the query, and get the result you expect, before trying to run your code.

    Also, this would be a good opportunity to change you MySqlCommand object to use Parameters.

    I am away to sign off now, but I will try and check back in the next couple days to see how you are getting on.

    As a quick clue though, I think this is going to be part of your problem:

    Code:
    WERE
    Gary

  13. #53
    New Member
    Join Date
    Mar 2011
    Posts
    3

    Re: Retrieving and Saving Data in Databases

    Quote Originally Posted by dethredic View Post
    I am having some problems:

    vb Code:
    1. Using connection As New OleDbConnection(myConnectionString)
    2.             Using adapter As New OleDbDataAdapter("SELECT NameFirst, NameLast FROM Employees", _
    3.                                               connection)
    4.                 Dim insert As New OleDbCommand("INSERT INTO Employees (NameFirst, NameLast) VALUES (@NameFirst, @NameLast)", _
    5.                                            connection)
    6.                 adapter.InsertCommand = insert
    7.                 adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
    8.  
    9.                 Dim table As New DataTable
    10.                 adapter.FillSchema(table, SchemaType.Source)
    11.                 Dim row As DataRow = table.NewRow()
    12.  
    13.                 row("NameFirst") = aName(0)
    14.                 row("NameLast") = aName(1)
    15.                 table.Rows.Add(row)
    16.  
    17.                 adapter.Update(table)
    18.             End Using
    19.         End Using

    I am getting an error with this line:
    adapter.Update(table)
    error:
    No value given for one or more required parameters.
    Hi,
    I'm using DotNetNuke CMS and working on some modules using VB.Net. I have a Datagrid "telerik:RadGrid" and I'm trying to add textbox to filter the rows base on the value that is written in the text box:
    I defined the following:
    Dim dv As DataView = New DataView()
    dv.Table = ?

    dv.RowFilter = "Name like '" & TextBox2.Text & "%'"

    RadGrid1.DataSource = dv
    I have a problem in setting the value of the dv.Table=?

    Can you please guide me to the value that should be there
    Thanks

  14. #54
    Fanatic Member ksuwanto8ksd's Avatar
    Join Date
    Apr 2005
    Posts
    636

    Re: Retrieving and Saving Data in Databases

    hi all expert, i need help to fill in textbox1 and textbox2 with sendernumber and textdecoded, but when run, both textbox is fill-n with last record(not from the beginning record)
    what command should i do? here is the code is working with messagebox, thanks !
    Code:
    populate textbox from database
    'this working
        Private Sub cmdLogin_Click(ByVal sender As System.Object, _
                                   ByVal e As System.EventArgs) Handles cmdLogin.Click
            Using conn As New MySqlConnection("server=" & txtServer.Text & ";" _
              & "user id=" & txtUsername.Text & ";" _
               & "password=" & txtPassword.Text & ";" _
                & "database=smsd_telkom")
                Using command As New MySqlCommand("SELECT sendernumber,textdecoded FROM inbox", _
                conn)
                    conn.Open()
                    Using reader As MySqlDataReader = command.ExecuteReader()
                        While reader.Read()
                            'TextBox10.Text = reader("sendernumber")
                            'TextBox11.Text = reader("textdecoded")
    
                            MessageBox.Show(String.Format("There are {0} {1}in stock.", _
                            reader("sendernumber"), _
                            reader("textdecoded")))
                        End While
                    End Using
                End Using
            End Using
        end sub

  15. #55

    Thread Starter
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    86,084

    Re: Retrieving and Saving Data in Databases

    Quote Originally Posted by ksuwanto8ksd View Post
    hi all expert, i need help to fill in textbox1 and textbox2 with sendernumber and textdecoded, but when run, both textbox is fill-n with last record(not from the beginning record)
    what command should i do? here is the code is working with messagebox, thanks !
    It's displaying the data from the last record because you are using a loop. Every iteration of the loop, what's currently in the TextBoxes is replaced with the data from the current record. The only data that doesn't get replaced is the data from the last row, so that's what you see.

    If you only want data from the first row then why are you using a loop at all. Loops are for doing something multiple times. If you only want to do something once then why would you use a loop? Presumably because you have copied and pasted code without considering what it actually does. I would question why you're executing a query that can return multiple records at all if all you want is one record.

    Anyway, if all you want is to read one record then first test the HasRows property to make sure that there is at least one row present, then call Read once and once only, to read just one row.

    2007-2014

    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts) | WP8 Turnstile Feather Transition with Pivot Control
    Beginner Tutorials: VB | C# | SQL

  16. #56
    vb Coda .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    18,338

    Re: Retrieving and Saving Data in Databases

    i followed your advice + followed the link to this thread, hoping to find how to update 2 tables using 2 adapters + 2 datatables, but there's nothing here that answers how to keep both tables synchronized.
    here's my code... can you help?

    vb Code:
    1. Public Class dataLayer
    2.  
    3.     Private Shared connection As OleDb.OleDbConnection
    4.     Private Shared updatableAdapter(1) As OleDb.OleDbDataAdapter
    5.     Private Shared transaction As OleDb.OleDbTransaction
    6.  
    7.     Private Shared Sub connect()
    8.         connection = New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & IO.Path.Combine(My.Application.Info.DirectoryPath, "details.accdb") & ";Persist Security Info=False;")
    9.         connection.Open()
    10.     End Sub
    11.  
    12.     Private Shared Sub disConnect()
    13.         connection.Close()
    14.     End Sub
    15.  
    16.     Public Shared Function getJoinedTable() As DataTable
    17.         connect()
    18.         updatableAdapter(0) = New OleDb.OleDbDataAdapter("SELECT a.id, a.number, a.subjectName, a.years, a.birthYear, b.note FROM Table1 AS a INNER JOIN Table2 AS b ON a.id = b.id", connection)
    19.         updatableAdapter(1) = New OleDb.OleDbDataAdapter("SELECT * FROM Table2", connection) 'contains id, note
    20.  
    21.         ' Create the InsertCommand.
    22.         Dim command As New OleDb.OleDbCommand("INSERT INTO Table1 VALUES(@id, @number, @subjectName, @years, @birthYear)", connection)
    23.  
    24.         ' Add the parameters for the InsertCommand.
    25.         command.Parameters.Add("@id", OleDb.OleDbType.Integer, 5, "id")
    26.         command.Parameters.Add("@number", OleDb.OleDbType.VarChar, 50, "number")
    27.         command.Parameters.Add("@subjectName", OleDb.OleDbType.VarChar, 50, "subjectName")
    28.         command.Parameters.Add("@years", OleDb.OleDbType.Integer, 3, "years")
    29.         command.Parameters.Add("@birthYear", OleDb.OleDbType.Integer, 4, "birthYear")
    30.         'command.Parameters.Add("@note", OleDb.OleDbType.VarChar, 100, "note")
    31.  
    32.         updatableAdapter(0).InsertCommand = command
    33.  
    34.         command = New OleDb.OleDbCommand("INSERT INTO Table2 VALUES(@id, @note)", connection)
    35.         ' Add the parameters for the InsertCommand.
    36.         command.Parameters.Add("@id", OleDb.OleDbType.Integer, 5, "id")
    37.         command.Parameters.Add("@note", OleDb.OleDbType.VarChar, 100, "note")
    38.  
    39.         updatableAdapter(1).InsertCommand = command
    40.  
    41.         ' Create the UpdateCommand.
    42.         command = New OleDb.OleDbCommand( _
    43.         "UPDATE Table1 SET id = @id, [number] = @number, subjectName = @subjectName, years = @years, " & _
    44.             "birthYear = @birthYear WHERE id = @oldID", connection)
    45.  
    46.         ' Add the parameters for the UpdateCommand.
    47.         command.Parameters.Add("@id", OleDb.OleDbType.Integer, 5, "id")
    48.         command.Parameters.Add("@number", OleDb.OleDbType.VarChar, 50, "number")
    49.         command.Parameters.Add("@subjectName", OleDb.OleDbType.VarChar, 50, "subjectName")
    50.         command.Parameters.Add("@years", OleDb.OleDbType.Integer, 3, "years")
    51.         command.Parameters.Add("@birthYear", OleDb.OleDbType.Integer, 4, "birthYear")
    52.         Dim parameter As OleDb.OleDbParameter = command.Parameters.Add( _
    53.             "@oldID", OleDb.OleDbType.Integer, 5, "id")
    54.         parameter.SourceVersion = DataRowVersion.Original
    55.  
    56.         updatableAdapter(0).UpdateCommand = command
    57.  
    58.         ' Create the UpdateCommand.
    59.         command = New OleDb.OleDbCommand( _
    60.         "UPDATE Table2 SET id = @idnote = @note WHERE id = @oldID", connection)
    61.  
    62.         ' Add the parameters for the UpdateCommand.
    63.         command.Parameters.Add("@id", OleDb.OleDbType.Integer, 5, "id")
    64.         command.Parameters.Add("@note", OleDb.OleDbType.VarChar, 100, "note")
    65.         parameter = command.Parameters.Add( _
    66.             "@oldID", OleDb.OleDbType.Integer, 5, "id")
    67.         parameter.SourceVersion = DataRowVersion.Original
    68.  
    69.         updatableAdapter(1).UpdateCommand = command
    70.  
    71.         ' Create the DeleteCommand.
    72.         command = New OleDb.OleDbCommand( _
    73.             "DELETE FROM Table1 WHERE id = @id", connection)
    74.  
    75.         ' Add the parameters for the DeleteCommand.
    76.         parameter = command.Parameters.Add( _
    77.             "@id", OleDb.OleDbType.Integer, 5, "id")
    78.         parameter.SourceVersion = DataRowVersion.Original
    79.  
    80.         updatableAdapter(0).DeleteCommand = command
    81.  
    82.         ' Create the DeleteCommand.
    83.         command = New OleDb.OleDbCommand( _
    84.             "DELETE FROM Table2 WHERE id = @id", connection)
    85.  
    86.         ' Add the parameters for the DeleteCommand.
    87.         parameter = command.Parameters.Add( _
    88.             "@id", OleDb.OleDbType.Integer, 5, "id")
    89.         parameter.SourceVersion = DataRowVersion.Original
    90.  
    91.         updatableAdapter(1).DeleteCommand = command
    92.  
    93.         Dim dt As New DataTable
    94.         updatableAdapter(0).Fill(dt)
    95.  
    96.         Return dt
    97.  
    98.     End Function
    99.  
    100.     Public Shared Sub update(ByVal dt As DataTable, ByVal adapterIndex As Integer)
    101.         updatableAdapter(adapterIndex).Update(dt)
    102.         connection.Close()
    103.     End Sub
    104.  
    105. End Class

  17. #57

    Thread Starter
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    86,084

    Re: Retrieving and Saving Data in Databases

    Quote Originally Posted by .paul. View Post
    i followed your advice + followed the link to this thread, hoping to find how to update 2 tables using 2 adapters + 2 datatables, but there's nothing here that answers how to keep both tables synchronized.
    here's my code... can you help?
    I'm not sure I understand. Do you actually mean two database tables, two DataAdapters and ONE DataTable? If it's 2, 2 and 2 then that's just 1, 1 and 1 twice, so it's no different to what you normally do.

    2007-2014

    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts) | WP8 Turnstile Feather Transition with Pivot Control
    Beginner Tutorials: VB | C# | SQL

  18. #58
    vb Coda .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    18,338

    Re: Retrieving and Saving Data in Databases

    it's 2 database tables, two DataAdapters and (should be) 2 DataTables

    the 1st dataadapter queries both tables + fills the 1st datatable which i then bind to a dgv
    the 2nd dataadapter should fill a 2nd datatable with just 2 fields (the id field is the primarykey in the 1st database table + the foreign key in the 2nd database table. the 2 fields are id, + note)

    the problem is, changing values in the dgv alters the 1st datatable but the 2nd datatable remains the same. i need to synchronize the 2 datatables so when i update the 2nd dataadapter, it has the latest values

  19. #59

    Thread Starter
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    86,084

    Re: Retrieving and Saving Data in Databases

    Quote Originally Posted by .paul. View Post
    it's 2 database tables, two DataAdapters and (should be) 2 DataTables

    the 1st dataadapter queries both tables + fills the 1st datatable which i then bind to a dgv
    the 2nd dataadapter should fill a 2nd datatable with just 2 fields (the id field is the primarykey in the 1st database table + the foreign key in the 2nd database table. the 2 fields are id, + note)

    the problem is, changing values in the dgv alters the 1st datatable but the 2nd datatable remains the same. i need to synchronize the 2 datatables so when i update the 2nd dataadapter, it has the latest values
    I don't think there's any way to do that other than to handle events of the first DataTable and update the second DataTable manually.

    2007-2014

    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts) | WP8 Turnstile Feather Transition with Pivot Control
    Beginner Tutorials: VB | C# | SQL

  20. #60
    vb Coda .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    18,338

    Re: Retrieving and Saving Data in Databases

    i asked on MSDN + i was told that i could synchronize 2 datatables with an oledbTransaction but i can't find a clear example of how to do that

  21. #61

    Thread Starter
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    86,084

    Re: Retrieving and Saving Data in Databases

    Quote Originally Posted by .paul. View Post
    i asked on MSDN + i was told that i could synchronize 2 datatables with an oledbTransaction but i can't find a clear example of how to do that
    I don't know who told you that but they must not have understood the question. What a transaction will allow you to do is save the two DataTables to the database and ensure that they either both succeed or both fail.

    I still don't see the need for the second DataTable. If you're making changes to the first DataTable then why don't you just save the changes from the first DataTable to both database tables? By default, calling Update on one DataAdapter would implicitly call AcceptChanges on the DataTable, so there'd be nothing left to save with the second. For that reason, you just have to set AcceptChangesDuringUpdate to False so that the changes are maintained for the second DataAdapter.

    2007-2014

    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts) | WP8 Turnstile Feather Transition with Pivot Control
    Beginner Tutorials: VB | C# | SQL

  22. #62
    vb Coda .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    18,338

    Thumbs up Re: Retrieving and Saving Data in Databases

    Quote Originally Posted by jmcilhinney View Post
    I don't know who told you that but they must not have understood the question. What a transaction will allow you to do is save the two DataTables to the database and ensure that they either both succeed or both fail.

    I still don't see the need for the second DataTable. If you're making changes to the first DataTable then why don't you just save the changes from the first DataTable to both database tables? By default, calling Update on one DataAdapter would implicitly call AcceptChanges on the DataTable, so there'd be nothing left to save with the second. For that reason, you just have to set AcceptChangesDuringUpdate to False so that the changes are maintained for the second DataAdapter.
    that appears to be the answer but i was + still am struggling with it because using 1 datatable with 2 dataadapters works fine for update + delete, but insert will only insert into the 1st database table. it's as if the AcceptChangesDuringUpdate is ignored for the 1st insert command

    edit:that was happening... calling Update on the 1st DataAdapter was calling AcceptChanges on the DataTable
    the solution is:

    vb Code:
    1. Dim tempDt As DataTable = dt.GetChanges
    2. DataAdapter1.update(dt)
    3. If tempDt IsNot Nothing Then
    4.    DataAdapter2.update(tempDt)
    5. End If

    thanks for the help
    Last edited by .paul.; Jun 5th, 2011 at 02:23 PM.

  23. #63
    Fanatic Member coolcurrent4u's Avatar
    Join Date
    Apr 2008
    Location
    *****
    Posts
    969

    Re: Retrieving and Saving Data in Databases

    Hello jmc

    if i need to batch insert into database with transaction support, so that i can roll back, how do i do it?
    Programming is all about good logic. Spend more time here


    (vHost for Apache) (Generate pronounceable password) (Generate random number c#) (Filter array with another array)

  24. #64

    Thread Starter
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    86,084

    Re: Retrieving and Saving Data in Databases

    Quote Originally Posted by coolcurrent4u View Post
    Hello jmc

    if i need to batch insert into database with transaction support, so that i can roll back, how do i do it?
    A DataAdapter does that inherently. A single call to Update will either succeed entirely or fail entirely. If you want to call Update on multiple DataAdapters and/or call ExecuteNonQuery on multiple Commands, then you would want to use a transaction explicitly. In that case, you have two choices:

    1. Use the transaction class from your ADO.NET provider, e.g.
    vb.net Code:
    1. Using connection As New SqlConnection("connection string here")
    2.     connection.Open()
    3.  
    4.     Dim transaction As SqlTransaction = connection.BeginTransaction()
    5.  
    6.     Using command1 As New SqlCommand("INSERT statement here", connection) With {.Transaction = transaction},
    7.           command2 As New SqlCommand("INSERT statement here", connection) With {.Transaction = transaction},
    8.           adapter1 As New SqlDataAdapter With {.InsertCommand = command1},
    9.           adapter2 As New SqlDataAdapter With {.InsertCommand = command2}
    10.         'Add appropriate parameters command1 and command2 here.
    11.  
    12.         Try
    13.             adapter1.Update(table1)
    14.             adapter2.Update(table2)
    15.  
    16.             'All operations succeeded so commit.
    17.             transaction.Commit()
    18.         Catch ex As Exception
    19.             'At least one operation failed so rollback.
    20.             transaction.Rollback()
    21.         End Try
    22.     End Using
    23. End Using
    2. Use a TransactionScope object, e.g.
    vb.net Code:
    1. Using transaction As New TransactionScope
    2.     Using connection As New SqlConnection("connection string here")
    3.         connection.Open()
    4.  
    5.         Using command1 As New SqlCommand("INSERT statement here", connection),
    6.               command2 As New SqlCommand("INSERT statement here", connection),
    7.               adapter1 As New SqlDataAdapter With {.InsertCommand = command1},
    8.               adapter2 As New SqlDataAdapter With {.InsertCommand = command2}
    9.             'Add appropriate parameters command1 and command2 here.
    10.  
    11.             Try
    12.                 adapter1.Update(table1)
    13.                 adapter2.Update(table2)
    14.  
    15.                 'All operations succeeded so commit.
    16.                 transaction.Complete()
    17.             Catch ex As Exception
    18.                 'At least one operation failed.
    19.                 'There is no need to rollback explicitly as it will be done implicitly at the End of
    20.                 'the Using block that created the TransactionScope if Complete was not called.
    21.             End Try
    22.         End Using
    23.     End Using
    24. End Using

    2007-2014

    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts) | WP8 Turnstile Feather Transition with Pivot Control
    Beginner Tutorials: VB | C# | SQL

  25. #65
    New Member
    Join Date
    Apr 2009
    Posts
    7

    Re: Retrieving and Saving Data in Databases

    Thanks for posting the connection methods, they have been a great help. However I have run into a issue.

    vb Code:
    1. Try
    2.             Using Command As New OleDbCommand("INSERT INTO USERTABLE " _
    3.                 + "(UserInitials, Ulastname, UFirstName, UMidInit, UPassw, UsecLevel," _
    4.                 + " UDept, Userstatus, UValid,UPhone, SuperID, UserName) VALUES " _
    5.                 + "(@UserInit,@ULastName,@UFirstName,@UMidInit,@UPass, " _
    6.                 + " @UsecLvl,@UDept,@UStatus,@UValid,@UPhone,@SuperID, @UserName)", cnn)
    7.                 '+ "('" + strFirstName.Substring(0, 1) + strMiddle + strLastName.Substring(0, 1) _
    8.                 '+ "','" + strLastName + "','" + strFirstName + "','" + strMiddle + "','" _
    9.                 '+ strPass + "','" + strSecLvl + "','" + strDept + "','" + strUserStatus + _
    10.                 '"','" + strValid + "','" + strPhone + "','" + strSuper + "','" + strUserName + "')", cnn)
    11.  
    12.                 Command.Parameters.AddWithValue("@UserInit", strFirstName.Substring(1, 1) + strMiddle + strFirstName.Substring(1, 1))
    13.                 Command.Parameters.AddWithValue("@ULastName", strLastName)
    14.                 Command.Parameters.AddWithValue("@UFirstName", strFirstName)
    15.                 Command.Parameters.AddWithValue("@UMidInit", strMiddle)
    16.                 Command.Parameters.AddWithValue("@UsecLvl", strSecLvl)
    17.                 Command.Parameters.AddWithValue("@UPass", strPass)
    18.                 Command.Parameters.AddWithValue("@Dept", strDept)
    19.                 Command.Parameters.AddWithValue("@UStatus", strUserStatus)
    20.                 Command.Parameters.AddWithValue("@UValid", strValid)
    21.                 Command.Parameters.AddWithValue("@Uphone", strPhone)
    22.                 Command.Parameters.AddWithValue("@USuperid", strSuper)
    23.                 Command.Parameters.AddWithValue("@UserName", strUserName)
    24.                 cnn.Open()
    25.                 Command.ExecuteNonQuery()
    26.             End Using
    27.             cnn.Close()
    28.             fAddUSer = True
    29.         Catch ex As Exception
    30.             MsgBox("Couldnt connect.")
    31.             cnn.Close()
    32.             fAddUSer = False
    33.         End Try

    When I use the code with the commented out string it works, but when I try to use it with the parameters, I end up with and error message ORA-00936: missing expression. I obiously have something wrong, but since it works with the commented out section, I am a bit stumped.

    Any help is greatly appreciated.

  26. #66
    vb Coda .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    18,338

    Re: Retrieving and Saving Data in Databases

    try listing your AddWithValue statements in the order you use them in the INSERT statement

  27. #67
    vb Coda .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    18,338

    Re: Retrieving and Saving Data in Databases

    also have you noticed your commented out string has 13 values + your parameters only 12?

  28. #68
    New Member
    Join Date
    Apr 2009
    Posts
    7

    Re: Retrieving and Saving Data in Databases

    Thanks for trying to help. I did move them in order and also made sure they all have the right name @..... but I still get the same error.

    In regards to the 13 value vs 12, I only found 12, you may have counted the first line as 2 values as I'm combining the last, first and middle into the initials or the person.

    Also I'm connecting to Oracle db, and I know they are picking in regards to qhoutatins for strings etc... so that 'Super' would work, while just Super would not... I don't know if the parameters automatically does that or not.

    Thanks
    Last edited by BLUMONKEY; Jun 8th, 2011 at 03:04 PM.

  29. #69

    Thread Starter
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    86,084

    Re: Retrieving and Saving Data in Databases

    @BLUMONKEY:

    Oracle doesn't use '@' as a parameter prefix. You need to use ':' as a parameter prefix for Oracle.

    Also, rather than using OleDb, I would suggest installing the Oracle-specific ADO.NET provider from Oracle.

    2007-2014

    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts) | WP8 Turnstile Feather Transition with Pivot Control
    Beginner Tutorials: VB | C# | SQL

  30. #70
    ASP.NET Moderator gep13's Avatar
    Join Date
    Nov 2004
    Location
    The Granite City
    Posts
    21,961

    Re: Retrieving and Saving Data in Databases

    As a starting point for John's recommendation to use ODP.Net, you might want to take a look here:

    http://www.stanford.edu/dept/itss/do...7/intro003.htm

    Specifically:

    http://www.stanford.edu/dept/itss/do...2.htm#i1010814

    Gary

  31. #71
    Fanatic Member coolcurrent4u's Avatar
    Join Date
    Apr 2008
    Location
    *****
    Posts
    969

    Re: Retrieving and Saving Data in Databases

    @Jmc thanks see the code am using already, is there any issue in operformance with it, am using sqlite.net
    vb Code:
    1. Dim conn As New SQLiteConnection("Data Source=" & dbPath)
    2.         'conn.SetPassword(dbPassword)
    3.         conn.Open()
    4.         Dim cmd As New SQLiteCommand(conn)
    5.         Try
    6.  
    7.             mytransaction = conn.BeginTransaction()
    8.             '// insert directory //
    9.             For i = 0 To UBound(oDir)
    10.                 If oDir(i).Url IsNot Nothing And DirExist(oDir(i).Url) = False Then
    11.                     cmd = New SQLiteCommand("INSERT INTO directory ([name]," & _
    12.                     "[category],[url],[type]) VALUES (@name,@category,@url,@type)")
    13.                     cmd.Parameters.AddWithValue("@name", oDir(i).Name.ToLower)
    14.                     cmd.Parameters.AddWithValue("@category", oDir(i).Category.ToLower)
    15.                     cmd.Parameters.AddWithValue("@type", oDir(i).Type.ToLower)
    16.                     cmd.Parameters.AddWithValue("@url", oDir(i).Url.ToLower)
    17.                     'Debug.Print(cmd.CommandText)
    18.                     cmd.ExecuteNonQuery()
    19.                     With tmpDir
    20.                         .Category = oDir(i).Category.ToLower
    21.                         .Name = oDir(i).Name.ToLower
    22.                         .Type = oDir(i).Type.ToLower
    23.                         .Url = oDir(i).Url.ToLower
    24.                     End With
    25.                     lstDirs.Add(tmpDir)
    26.                 End If
    27.             Next
    28.             mytransaction.Commit()
    29.             ListDirectories.AddRange(lstDirs)
    30.             Return True
    31.         Catch ex As Exception
    32.             strLastError = ex.Message
    33.             Debug.Print(strLastError)
    34.             Return False
    35.         Finally
    36.             cmd.Dispose()
    37.             conn.Dispose()
    38.         End Try
    Programming is all about good logic. Spend more time here


    (vHost for Apache) (Generate pronounceable password) (Generate random number c#) (Filter array with another array)

  32. #72
    New Member
    Join Date
    Apr 2009
    Posts
    7

    Re: Retrieving and Saving Data in Databases

    Quote Originally Posted by jmcilhinney View Post
    @BLUMONKEY:

    Oracle doesn't use '@' as a parameter prefix. You need to use ':' as a parameter prefix for Oracle.

    Also, rather than using OleDb, I would suggest installing the Oracle-specific ADO.NET provider from Oracle.

    Thank you! that did the trick.

    In regards to the Oracle Specific ADO.NET (this may be a newbie question too) would that have to be installed on every machine that will be using this app? The reason I am asking is that technology may have to approve something like that, while the oledb is already on the machines.

    Thanks again!
    Last edited by BLUMONKEY; Jun 9th, 2011 at 08:32 AM. Reason: Added some.

  33. #73

    Thread Starter
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    86,084

    Re: Retrieving and Saving Data in Databases

    Quote Originally Posted by BLUMONKEY View Post
    Thank you! that did the trick.

    In regards to the Oracle Specific ADO.NET (this may be a newbie question too) would that have to be installed on every machine that will be using this app? The reason I am asking is that technology may have to approve something like that, while the oledb is already on the machines.

    Thanks again!
    You can install the provider on each machine, or you can just deploy the required DLLs with your EXE.

    2007-2014

    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts) | WP8 Turnstile Feather Transition with Pivot Control
    Beginner Tutorials: VB | C# | SQL

  34. #74
    New Member
    Join Date
    Apr 2009
    Posts
    7

    Re: Retrieving and Saving Data in Databases

    Thank you again!

    If you have the time... what is the biggest difference between oledb and the oracle driver? Will there be a performance gain?

    Thank you for all the help you have given me!
    Ivar

  35. #75

    Thread Starter
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    86,084

    Re: Retrieving and Saving Data in Databases

    Quote Originally Posted by BLUMONKEY View Post
    Thank you again!

    If you have the time... what is the biggest difference between oledb and the oracle driver? Will there be a performance gain?

    Thank you for all the help you have given me!
    Ivar
    That is well beyond the scope of this CodeBank thread. Please keep discussion in the CodeBank specifically to the topic of that thread. If you have questions on other topics then you can start a new thread in the appropriate form. Otherwise CodeBank threads get cluttered and less useful.

    2007-2014

    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts) | WP8 Turnstile Feather Transition with Pivot Control
    Beginner Tutorials: VB | C# | SQL

  36. #76
    New Member
    Join Date
    Apr 2009
    Posts
    7

    Re: Retrieving and Saving Data in Databases

    No problem... thanks again.

  37. #77
    Member
    Join Date
    Oct 2011
    Posts
    41

    Re: Retrieving and Saving Data in Databases

    JMC you are a Champion............

  38. #78
    Member
    Join Date
    Oct 2011
    Posts
    41

    Re: Retrieving and Saving Data in Databases

    hello JMC sir can you update this codes with LinQ and LambdA ...
    .sorry for this asking because i'm trying to learn this stuffs
    thanks........

  39. #79
    Junior Member
    Join Date
    Jan 2012
    Posts
    27

    Re: Retrieving and Saving Data in Databases

    nice and helpful
    Last edited by Jack Tomy; Jan 16th, 2012 at 03:40 PM.

  40. #80
    Junior Member
    Join Date
    Jan 2012
    Posts
    27

    Re: Retrieving and Saving Data in Databases

    worked perfect, thank you

Page 2 of 3 FirstFirst 123 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.