Click here to Skip to main content
11,410,365 members (42,445 online)
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 Sergey Alexandrovich Kryukov 129
1 Sascha Lefévre 95
2 Maciej Los 68
3 BillWoodruff 60
4 OriginalGriff 60
0 Sergey Alexandrovich Kryukov 8,755
1 OriginalGriff 6,915
2 Maciej Los 3,390
3 Abhinav S 3,248
4 Peter Leow 3,059


Advertise | Privacy | Mobile
Web03 | 2.8.150414.5 | Last Updated 9 Nov 2012
Copyright © CodeProject, 1999-2015
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