none
coding for vb 2010 to save changes to a database RRS feed

  • Question

  • This should be a "nobrainer" buet I am not able to determine the conding that should to used in vb 2010 to save changes made to a databae. Any help would be appricated
    Saturday, April 6, 2019 8:03 AM

All replies

  • Hello,

    Can you provide details e.g. what type of database, how are you currently interacting with data (TableAdapter, DataAdapter, OleDb etc)? Providing these details and anything else that would be important can get things going.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Saturday, April 6, 2019 9:37 AM
    Moderator
  • Hi,

    You can use the following way:

    SqlDataAdapter 

    Public Shared Function CreateCustomerAdapter(ByVal connection As SqlConnection) As SqlDataAdapter
        Dim adapter As SqlDataAdapter = New SqlDataAdapter()
        Dim command As SqlCommand = New SqlCommand("SELECT * FROM Customers " & "WHERE Country = @Country AND City = @City", connection)
        command.Parameters.Add("@Country", SqlDbType.NVarChar, 15)
        command.Parameters.Add("@City", SqlDbType.NVarChar, 15)
        adapter.SelectCommand = command
        command = New SqlCommand("INSERT INTO Customers (CustomerID, CompanyName) " & "VALUES (@CustomerID, @CompanyName)", connection)
        command.Parameters.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID")
        command.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 40, "CompanyName")
        adapter.InsertCommand = command
        command = New SqlCommand("UPDATE Customers SET CustomerID = @CustomerID, CompanyName = @CompanyName " & "WHERE CustomerID = @oldCustomerID", connection)
        command.Parameters.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID")
        command.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 40, "CompanyName")
        Dim parameter As SqlParameter = command.Parameters.Add("@oldCustomerID", SqlDbType.NChar, 5, "CustomerID")
        parameter.SourceVersion = DataRowVersion.Original
        adapter.UpdateCommand = command
        command = New SqlCommand("DELETE FROM Customers WHERE CustomerID = @CustomerID", connection)
        parameter = command.Parameters.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID")
        parameter.SourceVersion = DataRowVersion.Original
        adapter.DeleteCommand = command
        Return adapter
    End Function

    or SqlCommandBuilder

    Public Shared Function SelectSqlRows(ByVal connectionString As String, ByVal queryString As String, ByVal tableName As String) As DataSet
        Using connection As SqlConnection = New SqlConnection(connectionString)
            Dim adapter As SqlDataAdapter = New SqlDataAdapter()
            adapter.SelectCommand = New SqlCommand(queryString, connection)
            Dim builder As SqlCommandBuilder = New SqlCommandBuilder(adapter)
            connection.Open()
            Dim dataSet As DataSet = New DataSet()
            adapter.Fill(dataSet, tableName)
            builder.GetUpdateCommand()
            adapter.Update(dataSet, tableName)
            Return dataSet
        End Using
    End Function

    Best Regards,

    Alex



    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, April 8, 2019 9:50 AM