Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
Im fairly new to VB.net and SQLite...I have a subroutine which writes data to a db using data adapters and datasets.
 
This whole sub below works fine. It just looks very long and time consuming for such a simple task. I might want to do this 1000's of times and each time I have to create a new dataset, dataadapter , fill it and do the updates.
 
Am I doing too much?
Is there a better way to update a db or is this normal?
 
Thanks for any advice you can provide
Al
 

Public Shared Sub SaveSumm(ByVal TestNo As Integer, ByVal Valx As Integer)
 
    Dim Tab1 As String = "USummary"
    Dim Conn1 As New SQLiteConnection(ConnMySys)
 
        Dim z As String = Trim(TestNo.ToString)
        Dim strSQL As String = "SELECT * FROM " & Tab1 & " WHERE SumTestNo ='" & z & "'"
 
        Dim da As New SQLiteDataAdapter(strSQL, Conn1)
        Dim ds1 As New DataSet(Tab1)
        da.FillSchema(ds1, SchemaType.Source, Tab1)
        da.Fill(ds1, Tab1)
        da.AcceptChangesDuringFill = False
 
        Dim foundRows() As DataRow
        foundRows = ds1.Tables(Tab1).Select("SumTestNo = " & z)
        Dim cb As New SQLiteCommandBuilder(da)  'needed

        If foundRows.Count = 0 Then
            'add new row
            Dim dsNewRow As DataRow
            dsNewRow = ds1.Tables(Tab1).NewRow()
            dsNewRow.SetField("SummTestNo", TestNo)
            dsNewRow.SetField("SummCount1", Valx)
            dsNewRow.SetField("SummTested", True)
            dsNewRow.SetField("SummImpScore", GetTestImpScore(TestNo))
            ds1.Tables(Tab1).Rows.Add(dsNewRow)
        Else
            'update old row even if its already the same
            foundRows(0).SetField("SummTestNo", TestNo)
            foundRows(0).SetField("SummCount1", Valx)
            foundRows(0).SetField("SummTested", True)
            foundRows(0).SetField("SummImpScore", GetTestImpScore(TestNo))
        End If
        da.Update(ds1, Tab1)   'used by both

end sub
Posted 1-Oct-12 0:50am
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

I'm not sure what you really want to achieve...
 
Please, start here: http://www.dreamincode.net/forums/topic/157830-using-sqlite-with-c%23/[^]
Then read this article on CP: Using SQLite in your C# Application[^]
More about SQLite you'll find at: http://www.sqlite.org/[^]
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Use INSERT INTO Query to insert single row in datatable insted of DataAdapters
 
Visit This Link to know more on INSERT INTO Syntax
 
Click Here
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 7,800
1 Sergey Alexandrovich Kryukov 7,072
2 DamithSL 5,604
3 Manas Bhardwaj 4,986
4 Maciej Los 4,790


Advertise | Privacy | Mobile
Web04 | 2.8.1411023.1 | Last Updated 9 Nov 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100