none
update database in a single query RRS feed

  • Question

  • Dar Sir,

    I m using VS2005 and Access 2003 database.

    I have a datagridview in a for which contains more than 20000 row which i get from a text file everyday (no datasource or binding)

     

            For v As Integer = 0 To Me.dgv_TimeRawData.Rows.Count - 1


                strSQL = "INSERT INTO Time_Office_Data(Machine_Name, Punch_Date, Punch_Time, IN_OUT, AStatus, ID, Default1, Default2, Default3, Default4, Default5, Media"
                strSQL = strSQL & ", Encoded_By, Encoded_Date)"
                strSQL = strSQL & " VALUES (" & IIf(Me.dgv_TimeRawData.Item(0, v).Value.ToString = "", "NULL", "'" & Me.dgv_TimeRawData.Item(0, v).Value.ToString & "'")
                strSQL = strSQL & ", " & IIf(Me.dgv_TimeRawData.Item(1, v).Value.ToString = "", "NULL", "'" & Me.dgv_TimeRawData.Item(1, v).Value.ToString & "'")
                strSQL = strSQL & ", " & IIf(Me.dgv_TimeRawData.Item(2, v).Value.ToString = "", "NULL", "'" & Me.dgv_TimeRawData.Item(2, v).Value.ToString & "'")
                strSQL = strSQL & ", " & IIf(Me.dgv_TimeRawData.Item(3, v).Value.ToString = "", "NULL", "'" & Me.dgv_TimeRawData.Item(3, v).Value.ToString & "'")
                strSQL = strSQL & ", " & IIf(Me.dgv_TimeRawData.Item(4, v).Value.ToString = "", "NULL", "'" & Me.dgv_TimeRawData.Item(4, v).Value.ToString & "'")
                strSQL = strSQL & ", " & IIf(Me.dgv_TimeRawData.Item(5, v).Value.ToString = "", "NULL", "'" & Me.dgv_TimeRawData.Item(5, v).Value.ToString & "'")
                strSQL = strSQL & ", " & IIf(Me.dgv_TimeRawData.Item(6, v).Value.ToString = "", "NULL", "'" & Me.dgv_TimeRawData.Item(6, v).Value.ToString & "'")
                strSQL = strSQL & ", " & IIf(Me.dgv_TimeRawData.Item(7, v).Value.ToString = "", "NULL", "'" & Me.dgv_TimeRawData.Item(7, v).Value.ToString & "'")
                strSQL = strSQL & ", " & IIf(Me.dgv_TimeRawData.Item(8, v).Value.ToString = "", "NULL", "'" & Me.dgv_TimeRawData.Item(8, v).Value.ToString & "'")
                strSQL = strSQL & ", " & IIf(Me.dgv_TimeRawData.Item(9, v).Value.ToString = "", "NULL", "'" & Me.dgv_TimeRawData.Item(9, v).Value.ToString & "'")
                strSQL = strSQL & ", " & IIf(Me.dgv_TimeRawData.Item(10, v).Value.ToString = "", "NULL", "'" & Me.dgv_TimeRawData.Item(10, v).Value.ToString & "'")
                strSQL = strSQL & ", " & IIf(Me.dgv_TimeRawData.Item(11, v).Value.ToString = "", "NULL", "'" & Me.dgv_TimeRawData.Item(11, v).Value.ToString & "'")
                strSQL = strSQL & ", '" & UserID & "'"
                strSQL = strSQL & ", '" & GetServerDateTime() & "')"
                ExecSqlStatement(strSQL, Trans)


            Next

     

    by this way it take too long time to save. some times it shows timeout. Not only this, there are some others like this  so the system is getting very slow.

     

    Please help me how can I add this datagridview data to temptable and update database easily or is there any way to do this thing easily?

     

     

     


    Shakhawat

    Shakhawat
    Tuesday, December 27, 2011 3:28 AM

Answers

  • Hello Shakhawat,

    I'd recommend you to build a SQL query with INSERT statements for inserting multiple values and execute that once.

    Note that your 'ExecSqlStatement(strSQL, Trans)' statement will be executed only once. This way you'll touch the database only once. It should boost you the performance!

    Your Insert statement should be something as shown below, this inserts multiple rows at a time with a single SQL statement:

    INSERT INTO TABLE (column-a, [column-b, ...])
    VALUES ('value-1a', ['value-1b', ...]),
           ('value-2a', ['value-2b', ...]),
    

    Hope it helps.

    -Mohammed Ghouse Ibne Barq Kadapavi
    http://www.ProgrammersVision.blogspot.com
    https://sites.google.com/site/BarqKadapavi
    Please mark the post answered your question as the answer, and mark other helpful posts as helpful.



    Tuesday, December 27, 2011 1:07 PM