Solved the problem with parameterized queries as below, without parameterized queries the update statement doesn't work on SQLite.
Private Sub ReplaceRGrid() ''On Error Resume Next Dim SQLITEcons As New SQLite.SQLiteConnection Dim SQLITEcmd As New SQLite.SQLiteCommand Dim SQLITEcmd2 As New SQLite.SQLiteCommand Dim SQLITEcmd200 As New SQLite.SQLiteCommand connection.Open() Dim cmd2 As New MySqlCommand("set net_write_timeout=99999; set net_read_timeout=99999", connection) 'Setting tiimeout on mysqlServer cmd2.ExecuteNonQuery() cmd2.Dispose() connection.Close() SQLITEcons.ConnectionString = "Data Source=\\\\172.16.0.14\Dexter14\44576\" & ComboBox1.Text & "\Data\" & ComboBox1.Text & ".db3; Version=3;" Dim query As String = "SELECT * FROM ancestry44576coods where batch_no like '" & ComboBox1.Text & "' order by Image,section,orderKwa" Dim cmd As New MySqlCommand(query, connection) Dim reade As MySqlDataReader connection.Open() reade = cmd.ExecuteReader While reade.Read 'Dim chombo As String = reade.GetString(10) & reade.GetString(12) & reade.GetString(11) 'MsgBox(chombo) Try SQLITEcons.Open() SQLITEcmd = SQLITEcons.CreateCommand() 'SQLITEcmd2 = SQLITEcons.CreateCommand() SQLITEcmd.CommandText = "REPLACE INTO RecordGrid (RecordGridID,ImageID,LineNumber) VALUES ('" & reade.GetInt32(10) & reade.GetInt32(12) & reade.GetInt32(11) & "','" & reade.GetInt32(10) & "','" & reade.GetInt32(11) & "')" SQLITEcmd.CommandType = CommandType.Text 'SQLITEcmd2.CommandType = CommandType.Text SQLITEcmd.ExecuteNonQuery() ' SQLITEcmd2.ExecuteNonQuery() SQLITEcmd.Dispose() 'SQLITEcmd2.Dispose() SQLITEcons.Close() 'SQLITEcons.Close() Catch ex As Exception MsgBox(ex.Message, MsgBoxStyle.Information) connection.Close() End Try End While reade.Close() connection.Close() Dim query300 As String = "SELECT * FROM ancestry44576coods where batch_no like '" & ComboBox1.Text & "' order by Image,section,orderKwa" Dim cmd300 As New MySqlCommand(query300, connection) Dim reade300 As MySqlDataReader connection.Open() reade300 = cmd300.ExecuteReader While reade300.Read Try SQLITEcons.Open() SQLITEcmd200 = SQLITEcons.CreateCommand() If reade300.GetString(21) = "TextBox1" Then SQLITEcmd200.CommandText = "UPDATE RecordGrid SET LineNumber=@LineNumber, self_empty_info_gender_PRect=@self_empty_info_gender_PRect WHERE RecordGridID=@RecordGridID" 'Dim SQLITEcmd200 As New SQLiteCommand(query204, SQLITEcons) SQLITEcmd200.Parameters.AddWithValue("@LineNumber", reade300.GetInt32(11)) SQLITEcmd200.Parameters.AddWithValue("@self_empty_info_gender_PRect", IIf(reade300.IsDBNull(22), String.Empty, reade300.GetString(22)) & "," & IIf(reade300.IsDBNull(23), String.Empty, reade300.GetString(23)) & "," & IIf(reade300.IsDBNull(24), String.Empty, reade300.GetString(24)) & "," & IIf(reade300.IsDBNull(25), String.Empty, reade300.GetString(25))) SQLITEcmd200.Parameters.AddWithValue("@RecordGridID", reade300.GetString(10) & reade300.GetString(12) & reade300.GetString(11)) SQLITEcmd200.ExecuteNonQuery() 'SQLITEcmd200.Dispose() ElseIf reade300.GetString(21) = "TextBox2" Then SQLITEcmd200.CommandText = "UPDATE RecordGrid SET LineNumber=@LineNumber, self_empty_info_gender_PRect=@self_empty_info_gender_PRect WHERE RecordGridID=@RecordGridID" 'Dim SQLITEcmd200 As New SQLiteCommand(query204, SQLITEcons) SQLITEcmd200.Parameters.AddWithValue("@LineNumber", reade300.GetInt32(11)) SQLITEcmd200.Parameters.AddWithValue("@self_empty_info_gender_PRect", IIf(reade300.IsDBNull(22), String.Empty, reade300.GetString(22)) & "," & IIf(reade300.IsDBNull(23), String.Empty, reade300.GetString(23)) & "," & IIf(reade300.IsDBNull(24), String.Empty, reade300.GetString(24)) & "," & IIf(reade300.IsDBNull(25), String.Empty, reade300.GetString(25))) SQLITEcmd200.Parameters.AddWithValue("@RecordGridID", reade300.GetString(10) & reade300.GetString(12) & reade300.GetString(11)) SQLITEcmd200.ExecuteNonQuery() 'SQLITEcmd200.Dispose() ElseIf reade300.GetString(21) = "TextBox3" Then SQLITEcmd200.CommandText = "UPDATE RecordGrid SET LineNumber=@LineNumber, self_empty_info_gender_PRect=@self_empty_info_gender_PRect WHERE RecordGridID=@RecordGridID" 'Dim SQLITEcmd200 As New SQLiteCommand(query204, SQLITEcons) SQLITEcmd200.Parameters.AddWithValue("@LineNumber", reade300.GetInt32(11)) SQLITEcmd200.Parameters.AddWithValue("@self_empty_info_gender_PRect", IIf(reade300.IsDBNull(22), String.Empty, reade300.GetString(22)) & "," & IIf(reade300.IsDBNull(23), String.Empty, reade300.GetString(23)) & "," & IIf(reade300.IsDBNull(24), String.Empty, reade300.GetString(24)) & "," & IIf(reade300.IsDBNull(25), String.Empty, reade300.GetString(25))) SQLITEcmd200.Parameters.AddWithValue("@RecordGridID", reade300.GetString(10) & reade300.GetString(12) & reade300.GetString(11)) SQLITEcmd200.ExecuteNonQuery() 'SQLITEcmd200.Dispose() ElseIf reade300.GetString(21) = "TextBox4" Then SQLITEcmd200.CommandText = "UPDATE RecordGrid SET LineNumber=@LineNumber, self_empty_info_gender_PRect=@self_empty_info_gender_PRect WHERE RecordGridID=@RecordGridID" 'Dim SQLITEcmd200 As New SQLiteCommand(query204, SQLITEcons) SQLITEcmd200.Parameters.AddWithValue("@LineNumber", reade300.GetInt32(11)) SQLITEcmd200.Parameters.AddWithValue("@self_empty_info_gender_PRect", IIf(reade300.IsDBNull(22), String.Empty, reade300.GetString(22)) & "," & IIf(reade300.IsDBNull(23), String.Empty, reade300.GetString(23)) & "," & IIf(reade300.IsDBNull(24), String.Empty, reade300.GetString(24)) & "," & IIf(reade300.IsDBNull(25), String.Empty, reade300.GetString(25))) SQLITEcmd200.Parameters.AddWithValue("@RecordGridID", reade300.GetString(10) & reade300.GetString(12) & reade300.GetString(11)) SQLITEcmd200.ExecuteNonQuery() 'SQLITEcmd200.Dispose() ElseIf reade300.GetString(21) = "TextBox5" Then SQLITEcmd200.CommandText = "UPDATE RecordGrid SET LineNumber=@LineNumber, self_empty_info_gender_PRect=@self_empty_info_gender_PRect WHERE RecordGridID=@RecordGridID" 'Dim SQLITEcmd200 As New SQLiteCommand(query204, SQLITEcons) SQLITEcmd200.Parameters.AddWithValue("@LineNumber", reade300.GetInt32(11)) SQLITEcmd200.Parameters.AddWithValue("@self_empty_info_gender_PRect", IIf(reade300.IsDBNull(22), String.Empty, reade300.GetString(22)) & "," & IIf(reade300.IsDBNull(23), String.Empty, reade300.GetString(23)) & "," & IIf(reade300.IsDBNull(24), String.Empty, reade300.GetString(24)) & "," & IIf(reade300.IsDBNull(25), String.Empty, reade300.GetString(25))) SQLITEcmd200.Parameters.AddWithValue("@RecordGridID", reade300.GetString(10) & reade300.GetString(12) & reade300.GetString(11)) SQLITEcmd200.ExecuteNonQuery() 'SQLITEcmd200.Dispose() ElseIf reade300.GetString(21) = "TextBox6" Then SQLITEcmd200.CommandText = "UPDATE RecordGrid SET LineNumber=@LineNumber, self_empty_info_gender_PRect=@self_empty_info_gender_PRect WHERE RecordGridID=@RecordGridID" 'Dim SQLITEcmd200 As New SQLiteCommand(query204, SQLITEcons) SQLITEcmd200.Parameters.AddWithValue("@LineNumber", reade300.GetInt32(11)) SQLITEcmd200.Parameters.AddWithValue("@self_empty_info_gender_PRect", IIf(reade300.IsDBNull(22), String.Empty, reade300.GetString(22)) & "," & IIf(reade300.IsDBNull(23), String.Empty, reade300.GetString(23)) & "," & IIf(reade300.IsDBNull(24), String.Empty, reade300.GetString(24)) & "," & IIf(reade300.IsDBNull(25), String.Empty, reade300.GetString(25))) SQLITEcmd200.Parameters.AddWithValue("@RecordGridID", reade300.GetString(10) & reade300.GetString(12) & reade300.GetString(11)) SQLITEcmd200.ExecuteNonQuery() 'SQLITEcmd200.Dispose() ElseIf reade300.GetString(21) = "TextBox7" Then SQLITEcmd200.CommandText = "UPDATE RecordGrid SET LineNumber=@LineNumber, self_empty_info_gender_PRect=@self_empty_info_gender_PRect WHERE RecordGridID=@RecordGridID" 'Dim SQLITEcmd200 As New SQLiteCommand(query204, SQLITEcons) SQLITEcmd200.Parameters.AddWithValue("@LineNumber", reade300.GetInt32(11)) SQLITEcmd200.Parameters.AddWithValue("@self_empty_info_gender_PRect", IIf(reade300.IsDBNull(22), String.Empty, reade300.GetString(22)) & "," & IIf(reade300.IsDBNull(23), String.Empty, reade300.GetString(23)) & "," & IIf(reade300.IsDBNull(24), String.Empty, reade300.GetString(24)) & "," & IIf(reade300.IsDBNull(25), String.Empty, reade300.GetString(25))) SQLITEcmd200.Parameters.AddWithValue("@RecordGridID", reade300.GetString(10) & reade300.GetString(12) & reade300.GetString(11)) SQLITEcmd200.ExecuteNonQuery() 'SQLITEcmd200.Dispose() ElseIf reade300.GetString(21) = "TextBox8" Then 'SQLITEcmd.CommandText = "REPLACE INTO RecordGrid (RecordGridID,ImageID,LineNumber,self_civil_date_day_PRect) VALUES('" & reade300.GetInt32(10) & reade300.GetInt32(12) & reade300.GetInt32(11) & "','" & reade300.GetInt32(10) & "','" & reade300.GetInt32(11) & "', '" & IIf(reade300.IsDBNull(22), String.Empty, reade300.GetString(22)) & "," & IIf(reade300.IsDBNull(23), String.Empty, reade300.GetString(23)) & "," & IIf(reade300.IsDBNull(24), String.Empty, reade300.GetString(24)) & "," & IIf(reade300.IsDBNull(25), String.Empty, reade300.GetString(25)) & "')" SQLITEcmd2.CommandText = "UPDATE RecordGrid SET LineNumber='" & reade300.GetInt32(11) & "',self_empty_info_gender_PRect= '" & IIf(reade300.IsDBNull(22), String.Empty, reade300.GetString(22)) & "," & IIf(reade300.IsDBNull(23), String.Empty, reade300.GetString(23)) & "," & IIf(reade300.IsDBNull(24), String.Empty, reade300.GetString(24)) & "," & IIf(reade300.IsDBNull(25), String.Empty, reade300.GetString(25)) & "' WHERE RecordGridID=@StudentNumber" ElseIf reade300.GetString(21) = "TextBox9" Then SQLITEcmd200.CommandText = "UPDATE RecordGrid SET LineNumber=@LineNumber, self_empty_info_gender_PRect=@self_empty_info_gender_PRect WHERE RecordGridID=@RecordGridID" 'Dim SQLITEcmd200 As New SQLiteCommand(query204, SQLITEcons) SQLITEcmd200.Parameters.AddWithValue("@LineNumber", reade300.GetInt32(11)) SQLITEcmd200.Parameters.AddWithValue("@self_empty_info_gender_PRect", IIf(reade300.IsDBNull(22), String.Empty, reade300.GetString(22)) & "," & IIf(reade300.IsDBNull(23), String.Empty, reade300.GetString(23)) & "," & IIf(reade300.IsDBNull(24), String.Empty, reade300.GetString(24)) & "," & IIf(reade300.IsDBNull(25), String.Empty, reade300.GetString(25))) SQLITEcmd200.Parameters.AddWithValue("@RecordGridID", reade300.GetString(10) & reade300.GetString(12) & reade300.GetString(11)) SQLITEcmd200.ExecuteNonQuery() 'SQLITEcmd200.Dispose() ElseIf reade300.GetString(21) = "TextBox10" Then SQLITEcmd200.CommandText = "UPDATE RecordGrid SET LineNumber=@LineNumber, self_empty_info_gender_PRect=@self_empty_info_gender_PRect WHERE RecordGridID=@RecordGridID" 'Dim SQLITEcmd200 As New SQLiteCommand(query204, SQLITEcons) SQLITEcmd200.Parameters.AddWithValue("@LineNumber", reade300.GetInt32(11)) SQLITEcmd200.Parameters.AddWithValue("@self_empty_info_gender_PRect", IIf(reade300.IsDBNull(22), String.Empty, reade300.GetString(22)) & "," & IIf(reade300.IsDBNull(23), String.Empty, reade300.GetString(23)) & "," & IIf(reade300.IsDBNull(24), String.Empty, reade300.GetString(24)) & "," & IIf(reade300.IsDBNull(25), String.Empty, reade300.GetString(25))) SQLITEcmd200.Parameters.AddWithValue("@RecordGridID", reade300.GetString(10) & reade300.GetString(12) & reade300.GetString(11)) SQLITEcmd200.ExecuteNonQuery() SQLITEcmd200.Dispose() ElseIf reade300.GetString(21) = "TextBox11" Then SQLITEcmd200.CommandText = "UPDATE RecordGrid SET LineNumber=@LineNumber, self_empty_info_gender_PRect=@self_empty_info_gender_PRect WHERE RecordGridID=@RecordGridID" 'Dim SQLITEcmd200 As New SQLiteCommand(query204, SQLITEcons) SQLITEcmd200.Parameters.AddWithValue("@LineNumber", reade300.GetInt32(11)) SQLITEcmd200.Parameters.AddWithValue("@self_empty_info_gender_PRect", IIf(reade300.IsDBNull(22), String.Empty, reade300.GetString(22)) & "," & IIf(reade300.IsDBNull(23), String.Empty, reade300.GetString(23)) & "," & IIf(reade300.IsDBNull(24), String.Empty, reade300.GetString(24)) & "," & IIf(reade300.IsDBNull(25), String.Empty, reade300.GetString(25))) SQLITEcmd200.Parameters.AddWithValue("@RecordGridID", reade300.GetString(10) & reade300.GetString(12) & reade300.GetString(11)) SQLITEcmd200.ExecuteNonQuery() 'SQLITEcmd200.Dispose() ElseIf reade300.GetString(21) = "TextBox12" Then SQLITEcmd200.CommandText = "UPDATE RecordGrid SET LineNumber=@LineNumber, self_empty_info_gender_PRect=@self_empty_info_gender_PRect WHERE RecordGridID=@RecordGridID" 'Dim SQLITEcmd200 As New SQLiteCommand(query204, SQLITEcons) SQLITEcmd200.Parameters.AddWithValue("@LineNumber", reade300.GetInt32(11)) SQLITEcmd200.Parameters.AddWithValue("@self_empty_info_gender_PRect", IIf(reade300.IsDBNull(22), String.Empty, reade300.GetString(22)) & "," & IIf(reade300.IsDBNull(23), String.Empty, reade300.GetString(23)) & "," & IIf(reade300.IsDBNull(24), String.Empty, reade300.GetString(24)) & "," & IIf(reade300.IsDBNull(25), String.Empty, reade300.GetString(25))) SQLITEcmd200.Parameters.AddWithValue("@RecordGridID", reade300.GetString(10) & reade300.GetString(12) & reade300.GetString(11)) SQLITEcmd200.ExecuteNonQuery() 'SQLITEcmd200.Dispose() ElseIf reade300.GetString(21) = "TextBox13" Then SQLITEcmd200.CommandText = "UPDATE RecordGrid SET LineNumber=@LineNumber, self_empty_info_gender_PRect=@self_empty_info_gender_PRect WHERE RecordGridID=@RecordGridID" 'Dim SQLITEcmd200 As New SQLiteCommand(query204, SQLITEcons) SQLITEcmd200.Parameters.AddWithValue("@LineNumber", reade300.GetInt32(11)) SQLITEcmd200.Parameters.AddWithValue("@self_empty_info_gender_PRect", IIf(reade300.IsDBNull(22), String.Empty, reade300.GetString(22)) & "," & IIf(reade300.IsDBNull(23), String.Empty, reade300.GetString(23)) & "," & IIf(reade300.IsDBNull(24), String.Empty, reade300.GetString(24)) & "," & IIf(reade300.IsDBNull(25), String.Empty, reade300.GetString(25))) SQLITEcmd200.Parameters.AddWithValue("@RecordGridID", reade300.GetString(10) & reade300.GetString(12) & reade300.GetString(11)) SQLITEcmd200.ExecuteNonQuery() 'SQLITEcmd200.Dispose() ElseIf reade300.GetString(21) = "TextBox16" Then SQLITEcmd200.CommandText = "UPDATE RecordGrid SET LineNumber=@LineNumber, self_empty_info_gender_PRect=@self_empty_info_gender_PRect WHERE RecordGridID=@RecordGridID" 'Dim SQLITEcmd200 As New SQLiteCommand(query204, SQLITEcons) SQLITEcmd200.Parameters.AddWithValue("@LineNumber", reade300.GetInt32(11)) SQLITEcmd200.Parameters.AddWithValue("@self_empty_info_gender_PRect", IIf(reade300.IsDBNull(22), String.Empty, reade300.GetString(22)) & "," & IIf(reade300.IsDBNull(23), String.Empty, reade300.GetString(23)) & "," & IIf(reade300.IsDBNull(24), String.Empty, reade300.GetString(24)) & "," & IIf(reade300.IsDBNull(25), String.Empty, reade300.GetString(25))) SQLITEcmd200.Parameters.AddWithValue("@RecordGridID", reade300.GetString(10) & reade300.GetString(12) & reade300.GetString(11)) SQLITEcmd200.ExecuteNonQuery() 'SQLITEcmd200.Dispose() ElseIf reade300.GetString(21) = "TextBox17" Then SQLITEcmd200.CommandText = "UPDATE RecordGrid SET LineNumber=@LineNumber, self_empty_info_gender_PRect=@self_empty_info_gender_PRect WHERE RecordGridID=@RecordGridID" 'Dim SQLITEcmd200 As New SQLiteCommand(query204, SQLITEcons) SQLITEcmd200.Parameters.AddWithValue("@LineNumber", reade300.GetInt32(11)) SQLITEcmd200.Parameters.AddWithValue("@self_empty_info_gender_PRect", IIf(reade300.IsDBNull(22), String.Empty, reade300.GetString(22)) & "," & IIf(reade300.IsDBNull(23), String.Empty, reade300.GetString(23)) & "," & IIf(reade300.IsDBNull(24), String.Empty, reade300.GetString(24)) & "," & IIf(reade300.IsDBNull(25), String.Empty, reade300.GetString(25))) SQLITEcmd200.Parameters.AddWithValue("@RecordGridID", reade300.GetString(10) & reade300.GetString(12) & reade300.GetString(11)) SQLITEcmd200.ExecuteNonQuery() 'SQLITEcmd200.Dispose() ElseIf reade300.GetString(21) = "TextBox18" Then SQLITEcmd200.CommandText = "UPDATE RecordGrid SET LineNumber=@LineNumber, self_empty_info_gender_PRect=@self_empty_info_gender_PRect WHERE RecordGridID=@RecordGridID" 'Dim SQLITEcmd200 As New SQLiteCommand(query204, SQLITEcons) SQLITEcmd200.Parameters.AddWithValue("@LineNumber", reade300.GetInt32(11)) SQLITEcmd200.Parameters.AddWithValue("@self_empty_info_gender_PRect", IIf(reade300.IsDBNull(22), String.Empty, reade300.GetString(22)) & "," & IIf(reade300.IsDBNull(23), String.Empty, reade300.GetString(23)) & "," & IIf(reade300.IsDBNull(24), String.Empty, reade300.GetString(24)) & "," & IIf(reade300.IsDBNull(25), String.Empty, reade300.GetString(25))) SQLITEcmd200.Parameters.AddWithValue("@RecordGridID", reade300.GetString(10) & reade300.GetString(12) & reade300.GetString(11)) SQLITEcmd200.ExecuteNonQuery() 'SQLITEcmd200.Dispose() ElseIf reade300.GetString(21) = "TextBox19" Then SQLITEcmd200.CommandText = "UPDATE RecordGrid SET LineNumber=@LineNumber, self_empty_info_gender_PRect=@self_empty_info_gender_PRect WHERE RecordGridID=@RecordGridID" 'Dim SQLITEcmd200 As New SQLiteCommand(query204, SQLITEcons) SQLITEcmd200.Parameters.AddWithValue("@LineNumber", reade300.GetInt32(11)) SQLITEcmd200.Parameters.AddWithValue("@self_empty_info_gender_PRect", IIf(reade300.IsDBNull(22), String.Empty, reade300.GetString(22)) & "," & IIf(reade300.IsDBNull(23), String.Empty, reade300.GetString(23)) & "," & IIf(reade300.IsDBNull(24), String.Empty, reade300.GetString(24)) & "," & IIf(reade300.IsDBNull(25), String.Empty, reade300.GetString(25))) SQLITEcmd200.Parameters.AddWithValue("@RecordGridID", reade300.GetString(10) & reade300.GetString(12) & reade300.GetString(11)) SQLITEcmd200.ExecuteNonQuery() 'SQLITEcmd200.Dispose() ElseIf reade300.GetString(21) = "TextBox20" Then SQLITEcmd200.CommandText = "UPDATE RecordGrid SET LineNumber=@LineNumber, self_empty_info_gender_PRect=@self_empty_info_gender_PRect WHERE RecordGridID=@RecordGridID" 'Dim SQLITEcmd200 As New SQLiteCommand(query204, SQLITEcons) SQLITEcmd200.Parameters.AddWithValue("@LineNumber", reade300.GetInt32(11)) SQLITEcmd200.Parameters.AddWithValue("@self_empty_info_gender_PRect", IIf(reade300.IsDBNull(22), String.Empty, reade300.GetString(22)) & "," & IIf(reade300.IsDBNull(23), String.Empty, reade300.GetString(23)) & "," & IIf(reade300.IsDBNull(24), String.Empty, reade300.GetString(24)) & "," & IIf(reade300.IsDBNull(25), String.Empty, reade300.GetString(25))) SQLITEcmd200.Parameters.AddWithValue("@RecordGridID", reade300.GetString(10) & reade300.GetString(12) & reade300.GetString(11)) SQLITEcmd200.ExecuteNonQuery() 'SQLITEcmd200.Dispose() ElseIf reade300.GetString(21) = "TextBox21" Then SQLITEcmd200.CommandText = "UPDATE RecordGrid SET LineNumber=@LineNumber, self_empty_info_gender_PRect=@self_empty_info_gender_PRect WHERE RecordGridID=@RecordGridID" 'Dim SQLITEcmd200 As New SQLiteCommand(query204, SQLITEcons) SQLITEcmd200.Parameters.AddWithValue("@LineNumber", reade300.GetInt32(11)) SQLITEcmd200.Parameters.AddWithValue("@self_empty_info_gender_PRect", IIf(reade300.IsDBNull(22), String.Empty, reade300.GetString(22)) & "," & IIf(reade300.IsDBNull(23), String.Empty, reade300.GetString(23)) & "," & IIf(reade300.IsDBNull(24), String.Empty, reade300.GetString(24)) & "," & IIf(reade300.IsDBNull(25), String.Empty, reade300.GetString(25))) SQLITEcmd200.Parameters.AddWithValue("@RecordGridID", reade300.GetString(10) & reade300.GetString(12) & reade300.GetString(11)) SQLITEcmd200.ExecuteNonQuery() 'SQLITEcmd200.Dispose() ElseIf reade300.GetString(21) = "TextBox22" Then SQLITEcmd200.CommandText = "UPDATE RecordGrid SET LineNumber=@LineNumber, self_empty_info_gender_PRect=@self_empty_info_gender_PRect WHERE RecordGridID=@RecordGridID" 'Dim SQLITEcmd200 As New SQLiteCommand(query204, SQLITEcons) SQLITEcmd200.Parameters.AddWithValue("@LineNumber", reade300.GetInt32(11)) SQLITEcmd200.Parameters.AddWithValue("@self_empty_info_gender_PRect", IIf(reade300.IsDBNull(22), String.Empty, reade300.GetString(22)) & "," & IIf(reade300.IsDBNull(23), String.Empty, reade300.GetString(23)) & "," & IIf(reade300.IsDBNull(24), String.Empty, reade300.GetString(24)) & "," & IIf(reade300.IsDBNull(25), String.Empty, reade300.GetString(25))) SQLITEcmd200.Parameters.AddWithValue("@RecordGridID", reade300.GetString(10) & reade300.GetString(12) & reade300.GetString(11)) SQLITEcmd200.ExecuteNonQuery() 'SQLITEcmd200.Dispose() ElseIf reade300.GetString(21) = "TextBox23" Then SQLITEcmd200.CommandText = "UPDATE RecordGrid SET LineNumber=@LineNumber, self_empty_info_gender_PRect=@self_empty_info_gender_PRect WHERE RecordGridID=@RecordGridID" 'Dim SQLITEcmd200 As New SQLiteCommand(query204, SQLITEcons) SQLITEcmd200.Parameters.AddWithValue("@LineNumber", reade300.GetInt32(11)) SQLITEcmd200.Parameters.AddWithValue("@self_empty_info_gender_PRect", IIf(reade300.IsDBNull(22), String.Empty, reade300.GetString(22)) & "," & IIf(reade300.IsDBNull(23), String.Empty, reade300.GetString(23)) & "," & IIf(reade300.IsDBNull(24), String.Empty, reade300.GetString(24)) & "," & IIf(reade300.IsDBNull(25), String.Empty, reade300.GetString(25))) SQLITEcmd200.Parameters.AddWithValue("@RecordGridID", reade300.GetString(10) & reade300.GetString(12) & reade300.GetString(11)) SQLITEcmd200.ExecuteNonQuery() 'SQLITEcmd200.Dispose() ElseIf reade300.GetString(21) = "TextBox24" Then SQLITEcmd200.CommandText = "UPDATE RecordGrid SET LineNumber=@LineNumber, self_empty_info_gender_PRect=@self_empty_info_gender_PRect WHERE RecordGridID=@RecordGridID" 'Dim SQLITEcmd200 As New SQLiteCommand(query204, SQLITEcons) SQLITEcmd200.Parameters.AddWithValue("@LineNumber", reade300.GetInt32(11)) SQLITEcmd200.Parameters.AddWithValue("@self_empty_info_gender_PRect", IIf(reade300.IsDBNull(22), String.Empty, reade300.GetString(22)) & "," & IIf(reade300.IsDBNull(23), String.Empty, reade300.GetString(23)) & "," & IIf(reade300.IsDBNull(24), String.Empty, reade300.GetString(24)) & "," & IIf(reade300.IsDBNull(25), String.Empty, reade300.GetString(25))) SQLITEcmd200.Parameters.AddWithValue("@RecordGridID", reade300.GetString(10) & reade300.GetString(12) & reade300.GetString(11)) SQLITEcmd200.ExecuteNonQuery() 'SQLITEcmd200.Dispose() ElseIf reade300.GetString(21) = "TextBox25" Then SQLITEcmd200.CommandText = "UPDATE RecordGrid SET LineNumber=@LineNumber, self_empty_info_gender_PRect=@self_empty_info_gender_PRect WHERE RecordGridID=@RecordGridID" 'Dim SQLITEcmd200 As New SQLiteCommand(query204, SQLITEcons) SQLITEcmd200.Parameters.AddWithValue("@LineNumber", reade300.GetInt32(11)) SQLITEcmd200.Parameters.AddWithValue("@self_empty_info_gender_PRect", IIf(reade300.IsDBNull(22), String.Empty, reade300.GetString(22)) & "," & IIf(reade300.IsDBNull(23), String.Empty, reade300.GetString(23)) & "," & IIf(reade300.IsDBNull(24), String.Empty, reade300.GetString(24)) & "," & IIf(reade300.IsDBNull(25), String.Empty, reade300.GetString(25))) SQLITEcmd200.Parameters.AddWithValue("@RecordGridID", reade300.GetString(10) & reade300.GetString(12) & reade300.GetString(11)) SQLITEcmd200.ExecuteNonQuery() 'SQLITEcmd200.Dispose() ElseIf reade300.GetString(21) = "TextBox26" Then SQLITEcmd200.CommandText = "UPDATE RecordGrid SET LineNumber=@LineNumber, self_empty_info_gender_PRect=@self_empty_info_gender_PRect WHERE RecordGridID=@RecordGridID" 'Dim SQLITEcmd200 As New SQLiteCommand(query204, SQLITEcons) SQLITEcmd200.Parameters.AddWithValue("@LineNumber", reade300.GetInt32(11)) SQLITEcmd200.Parameters.AddWithValue("@self_empty_info_gender_PRect", IIf(reade300.IsDBNull(22), String.Empty, reade300.GetString(22)) & "," & IIf(reade300.IsDBNull(23), String.Empty, reade300.GetString(23)) & "," & IIf(reade300.IsDBNull(24), String.Empty, reade300.GetString(24)) & "," & IIf(reade300.IsDBNull(25), String.Empty, reade300.GetString(25))) SQLITEcmd200.Parameters.AddWithValue("@RecordGridID", reade300.GetString(10) & reade300.GetString(12) & reade300.GetString(11)) SQLITEcmd200.ExecuteNonQuery() 'SQLITEcmd200.Dispose() ElseIf reade300.GetString(21) = "TextBox27" Then SQLITEcmd200.CommandText = "UPDATE RecordGrid SET LineNumber=@LineNumber, self_empty_info_gender_PRect=@self_empty_info_gender_PRect WHERE RecordGridID=@RecordGridID" 'Dim SQLITEcmd200 As New SQLiteCommand(query204, SQLITEcons) SQLITEcmd200.Parameters.AddWithValue("@LineNumber", reade300.GetInt32(11)) SQLITEcmd200.Parameters.AddWithValue("@self_empty_info_gender_PRect", IIf(reade300.IsDBNull(22), String.Empty, reade300.GetString(22)) & "," & IIf(reade300.IsDBNull(23), String.Empty, reade300.GetString(23)) & "," & IIf(reade300.IsDBNull(24), String.Empty, reade300.GetString(24)) & "," & IIf(reade300.IsDBNull(25), String.Empty, reade300.GetString(25))) SQLITEcmd200.Parameters.AddWithValue("@RecordGridID", reade300.GetString(10) & reade300.GetString(12) & reade300.GetString(11)) SQLITEcmd200.ExecuteNonQuery() 'SQLITEcmd200.Dispose() Else 'SQLITEcmd.CommandText = "INSERT INTO RecordGrid (RecordGridID,ImageID,LineNumber,self_empty_name_given_PRect, self_empty_name_surname_PRect, self_empty_info_gender_PRect, father_empty_name_given_PRect, father_empty_name_surname_PRect, mother_empty_name_given_PRect, mother_empty_name_surname_PRect, self_civil_date_day_PRect, self_civil_date_month_PRect, self_civil_date_year_PRect, eventtype_PRect, general_page_PRect, general_entrynumber_PRect, self_vital_date_day_PRect, self_vital_date_month_PRect, self_vital_date_year_PRect, self_vital_place_city_PRect, spouse_empty_name_given_PRect, spouse_empty_name_surname_PRect, self_vital_info_age_PRect, spouse_marriage_info_age_PRect, spousefather_empty_name_given_PRect, spousefather_empty_name_surname_PRect, spousemother_empty_name_given_PRect, spousemother_empty_name_surname_PRect,) VALUES('" & reade300.GetInt32(10) & reade300.GetInt32(12) & reade300.GetInt32(11) & "','" & reade300.GetInt32(10) & reade300.GetInt32(12) & "', '" & reade300.GetInt32(11) & "', '" & IIf(reade300.IsDBNull(22), String.Empty, reade300.GetString(22)) & "," & IIf(reade300.IsDBNull(23), String.Empty, reade300.GetString(23)) & "," & IIf(reade300.IsDBNull(24), String.Empty, reade300.GetString(24)) & "," & IIf(reade300.IsDBNull(25), String.Empty, reade300.GetString(25)) & "', '" & IIf(reade300.IsDBNull(22), String.Empty, reade300.GetString(22)) & "," & IIf(reade300.IsDBNull(23), String.Empty, reade300.GetString(23)) & "," & IIf(reade300.IsDBNull(24), String.Empty, reade300.GetString(24)) & "," & IIf(reade300.IsDBNull(25), String.Empty, reade300.GetString(25)) & "', '" & IIf(reade300.IsDBNull(22), String.Empty, reade300.GetString(22)) & "," & IIf(reade300.IsDBNull(23), String.Empty, reade300.GetString(23)) & "," & IIf(reade300.IsDBNull(24), String.Empty, reade300.GetString(24)) & "," & IIf(reade300.IsDBNull(25), String.Empty, reade300.GetString(25)) & "', '" & IIf(reade300.IsDBNull(22), String.Empty, reade300.GetString(22)) & "," & IIf(reade300.IsDBNull(23), String.Empty, reade300.GetString(23)) & "," & IIf(reade300.IsDBNull(24), String.Empty, reade300.GetString(24)) & "," & IIf(reade300.IsDBNull(25), String.Empty, reade300.GetString(25)) & "', '" & IIf(reade300.IsDBNull(22), String.Empty, reade300.GetString(22)) & "," & IIf(reade300.IsDBNull(23), String.Empty, reade300.GetString(23)) & "," & IIf(reade300.IsDBNull(24), String.Empty, reade300.GetString(24)) & "," & IIf(reade300.IsDBNull(25), String.Empty, reade300.GetString(25)) & "', '" & IIf(reade300.IsDBNull(22), String.Empty, reade300.GetString(22)) & "," & IIf(reade300.IsDBNull(23), String.Empty, reade300.GetString(23)) & "," & IIf(reade300.IsDBNull(24), String.Empty, reade300.GetString(24)) & "," & IIf(reade300.IsDBNull(25), String.Empty, reade300.GetString(25)) & "', '" & IIf(reade300.IsDBNull(22), String.Empty, reade300.GetString(22)) & "," & IIf(reade300.IsDBNull(23), String.Empty, reade300.GetString(23)) & "," & IIf(reade300.IsDBNull(24), String.Empty, reade300.GetString(24)) & "," & IIf(reade300.IsDBNull(25), String.Empty, reade300.GetString(25)) & "', '" & IIf(reade300.IsDBNull(22), String.Empty, reade300.GetString(22)) & "," & IIf(reade300.IsDBNull(23), String.Empty, reade300.GetString(23)) & "," & IIf(reade300.IsDBNull(24), String.Empty, reade300.GetString(24)) & "," & IIf(reade300.IsDBNull(25), String.Empty, reade300.GetString(25)) & "', '" & IIf(reade300.IsDBNull(22), String.Empty, reade300.GetString(22)) & "," & IIf(reade300.IsDBNull(23), String.Empty, reade300.GetString(23)) & "," & IIf(reade300.IsDBNull(24), String.Empty, reade300.GetString(24)) & "," & IIf(reade300.IsDBNull(25), String.Empty, reade300.GetString(25)) & "', '" & IIf(reade300.IsDBNull(22), String.Empty, reade300.GetString(22)) & "," & IIf(reade300.IsDBNull(23), String.Empty, reade300.GetString(23)) & "," & IIf(reade300.IsDBNull(24), String.Empty, reade300.GetString(24)) & "," & IIf(reade300.IsDBNull(25), String.Empty, reade300.GetString(25)) & "', '" & IIf(reade300.IsDBNull(22), String.Empty, reade300.GetString(22)) & "," & IIf(reade300.IsDBNull(23), String.Empty, reade300.GetString(23)) & "," & IIf(reade300.IsDBNull(24), String.Empty, reade300.GetString(24)) & "," & IIf(reade300.IsDBNull(25), String.Empty, reade300.GetString(25)) & "', '" & IIf(reade300.IsDBNull(22), String.Empty, reade300.GetString(22)) & "," & IIf(reade300.IsDBNull(23), String.Empty, reade300.GetString(23)) & "," & IIf(reade300.IsDBNull(24), String.Empty, reade300.GetString(24)) & "," & IIf(reade300.IsDBNull(25), String.Empty, reade300.GetString(25)) & "', '" & IIf(reade300.IsDBNull(22), String.Empty, reade300.GetString(22)) & "," & IIf(reade300.IsDBNull(23), String.Empty, reade300.GetString(23)) & "," & IIf(reade300.IsDBNull(24), String.Empty, reade300.GetString(24)) & "," & IIf(reade300.IsDBNull(25), String.Empty, reade300.GetString(25)) & "', '" & IIf(reade300.IsDBNull(22), String.Empty, reade300.GetString(22)) & "," & IIf(reade300.IsDBNull(23), String.Empty, reade300.GetString(23)) & "," & IIf(reade300.IsDBNull(24), String.Empty, reade300.GetString(24)) & "," & IIf(reade300.IsDBNull(25), String.Empty, reade300.GetString(25)) & "', '" & IIf(reade300.IsDBNull(22), String.Empty, reade300.GetString(22)) & "," & IIf(reade300.IsDBNull(23), String.Empty, reade300.GetString(23)) & "," & IIf(reade300.IsDBNull(24), String.Empty, reade300.GetString(24)) & "," & IIf(reade300.IsDBNull(25), String.Empty, reade300.GetString(25)) & "', '" & IIf(reade300.IsDBNull(22), String.Empty, reade300.GetString(22)) & "," & IIf(reade300.IsDBNull(23), String.Empty, reade300.GetString(23)) & "," & IIf(reade300.IsDBNull(24), String.Empty, reade300.GetString(24)) & "," & IIf(reade300.IsDBNull(25), String.Empty, reade300.GetString(25)) & "', '" & IIf(reade300.IsDBNull(22), String.Empty, reade300.GetString(22)) & "," & IIf(reade300.IsDBNull(23), String.Empty, reade300.GetString(23)) & "," & IIf(reade300.IsDBNull(24), String.Empty, reade300.GetString(24)) & "," & IIf(reade300.IsDBNull(25), String.Empty, reade300.GetString(25)) & "', '" & IIf(reade300.IsDBNull(22), String.Empty, reade300.GetString(22)) & "," & IIf(reade300.IsDBNull(23), String.Empty, reade300.GetString(23)) & "," & IIf(reade300.IsDBNull(24), String.Empty, reade300.GetString(24)) & "," & IIf(reade300.IsDBNull(25), String.Empty, reade300.GetString(25)) & "', '" & IIf(reade300.IsDBNull(22), String.Empty, reade300.GetString(22)) & "," & IIf(reade300.IsDBNull(23), String.Empty, reade300.GetString(23)) & "," & IIf(reade300.IsDBNull(24), String.Empty, reade300.GetString(24)) & "," & IIf(reade300.IsDBNull(25), String.Empty, reade300.GetString(25)) & "', '" & IIf(reade300.IsDBNull(22), String.Empty, reade300.GetString(22)) & "," & IIf(reade300.IsDBNull(23), String.Empty, reade300.GetString(23)) & "," & IIf(reade300.IsDBNull(24), String.Empty, reade300.GetString(24)) & "," & IIf(reade300.IsDBNull(25), String.Empty, reade300.GetString(25)) & "', '" & IIf(reade300.IsDBNull(22), String.Empty, reade300.GetString(22)) & "," & IIf(reade300.IsDBNull(23), String.Empty, reade300.GetString(23)) & "," & IIf(reade300.IsDBNull(24), String.Empty, reade300.GetString(24)) & "," & IIf(reade300.IsDBNull(25), String.Empty, reade300.GetString(25)) & "', '" & IIf(reade300.IsDBNull(22), String.Empty, reade300.GetString(22)) & "," & IIf(reade300.IsDBNull(23), String.Empty, reade300.GetString(23)) & "," & IIf(reade300.IsDBNull(24), String.Empty, reade300.GetString(24)) & "," & IIf(reade300.IsDBNull(25), String.Empty, reade300.GetString(25)) & "', '" & IIf(reade300.IsDBNull(22), String.Empty, reade300.GetString(22)) & "," & IIf(reade300.IsDBNull(23), String.Empty, reade300.GetString(23)) & "," & IIf(reade300.IsDBNull(24), String.Empty, reade300.GetString(24)) & "," & IIf(reade300.IsDBNull(25), String.Empty, reade300.GetString(25)) & "', '" & IIf(reade300.IsDBNull(22), String.Empty, reade300.GetString(22)) & "," & IIf(reade300.IsDBNull(23), String.Empty, reade300.GetString(23)) & "," & IIf(reade300.IsDBNull(24), String.Empty, reade300.GetString(24)) & "," & IIf(reade300.IsDBNull(25), String.Empty, reade300.GetString(25)) & "', '" & IIf(reade300.IsDBNull(22), String.Empty, reade300.GetString(22)) & "," & IIf(reade300.IsDBNull(23), String.Empty, reade300.GetString(23)) & "," & IIf(reade300.IsDBNull(24), String.Empty, reade300.GetString(24)) & "," & IIf(reade300.IsDBNull(25), String.Empty, reade300.GetString(25)) & "')" 'SQLITEcmd2.CommandText = "UPDATE RecordGrid SET LineNumber='" & reade300.GetInt32(11) & "',self_empty_info_gender_PRect= '" & IIf(reade300.IsDBNull(22), String.Empty, reade300.GetString(22)) & "," & IIf(reade300.IsDBNull(23), String.Empty, reade300.GetString(23)) & "," & IIf(reade300.IsDBNull(24), String.Empty, reade300.GetString(24)) & "," & IIf(reade300.IsDBNull(25), String.Empty, reade300.GetString(25)) & "' WHERE RecordGridID=@StudentNumber" End If SQLITEcmd200.CommandType = CommandType.Text SQLITEcmd200.ExecuteNonQuery() SQLITEcmd200.Dispose() SQLITEcons.Close() Catch ex As Exception MsgBox(ex.Message, MsgBoxStyle.Information) connection.Close() End Try End While reade300.Close() connection.Close() End Sub On Mon, Aug 18, 2014 at 10:00 PM, Wycliff Mabwai < wycliff.mab...@digitaldividedata.com> wrote: > This line* [SQLITEcmd2.Parameters.AddWithValue(chombo)]* isn't actually > used, I had just added it trying to figure out how to use parameterized > queries. You can comment it or ignore it out of the problem. > > Thanks > > > On Mon, Aug 18, 2014 at 9:54 PM, Wycliff Mabwai < > wycliff.mab...@digitaldividedata.com> wrote: > >> Hi, (Don't know as yet who to salute) >> I have a problem, am populating data into a db3 file. Inserts, Replace >> all work fine. But when I update with a condition, it fails and tells me to >> check the syntax near "Where". >> >> The code itself is VB.Net, am trying to update certain fields. >> >> Below is the full code for the routine,everything in it works fine with >> insert as long as I don't use where, I think even update would work if I >> don't use the where clause which would be senseless. I tried looking at >> paramaterized queries I couldn't figure it out quite how I would use them. >> >> Any assistance would be highly appreciated. >> Thanks >> >> Private Sub ReplaceRGrid() >> ''On Error Resume Next >> Dim SQLITEcons As New SQLite.SQLiteConnection >> Dim SQLITEcmd As New SQLite.SQLiteCommand >> Dim SQLITEcmd2 As New SQLite.SQLiteCommand >> Dim SQLITEcmd3 As New SQLite.SQLiteCommand >> connection.Open() >> Dim cmd2 As New MySqlCommand("set net_write_timeout=99999; set >> net_read_timeout=99999", connection) 'Setting tiimeout on mysqlServer >> cmd2.ExecuteNonQuery() >> cmd2.Dispose() >> connection.Close() >> SQLITEcons.ConnectionString = "Data >> Source=\\\\172.16.0.14\Dexter14\44576\" & ComboBox1.Text & "\Data\" & >> ComboBox1.Text & ".db3; Version=3;" >> >> >> Dim query20 As String = "SELECT * FROM ancestry44576coods where >> batch_no like '" & ComboBox1.Text & "' order by Image,section,orderKwa" >> Dim cmd20 As New MySqlCommand(query20, connection) >> Dim reade20 As MySqlDataReader >> >> connection.Open() >> reade20 = cmd.ExecuteReader >> While reade20.Read >> Dim chombo As Integer = reade20.GetString(10) & >> reade20.GetString(12) & reade20.GetString(11) >> >> Try >> SQLITEcons.Open() >> SQLITEcmd2 = SQLITEcons.CreateCommand() >> SQLITEcmd2.Parameters.AddWithValue(chombo) >> If reade20.GetString(21) = "TextBox1" Then >> >> SQLITEcmd2.CommandText = "UPDATE RecordGrid SET >> LineNumber='" & reade20.GetInt32(11) & "',self_empty_info_gender_PRect= '" >> & IIf(reade20.IsDBNull(22), String.Empty, reade20.GetString(22)) & "," & >> IIf(reade20.IsDBNull(23), String.Empty, reade20.GetString(23)) & "," & >> IIf(reade20.IsDBNull(24), String.Empty, reade20.GetString(24)) & "," & >> IIf(reade20.IsDBNull(25), String.Empty, reade20.GetString(25)) & "' WHERE >> RecordGridID='"chombo"'" >> ElseIf reade20.GetString(21) = "TextBox2" Then >> >> SQLITEcmd2.CommandText = "UPDATE RecordGrid SET >> LineNumber='" & reade20.GetInt32(11) & "',self_empty_info_gender_PRect= '" >> & IIf(reade20.IsDBNull(22), String.Empty, reade20.GetString(22)) & "," & >> IIf(reade20.IsDBNull(23), String.Empty, reade20.GetString(23)) & "," & >> IIf(reade20.IsDBNull(24), String.Empty, reade20.GetString(24)) & "," & >> IIf(reade20.IsDBNull(25), String.Empty, reade20.GetString(25)) & "' WHERE >> RecordGridID='"chombo"'" >> >> End If >> SQLITEcmd2.CommandType = CommandType.Text >> SQLITEcmd2.ExecuteNonQuery() >> SQLITEcmd2.Dispose() >> SQLITEcons.Close() >> >> Catch ex As Exception >> MsgBox(ex.Message, MsgBoxStyle.Information) >> connection.Close() >> End Try >> >> End While >> reade20.Close() >> connection.Close() >> End Sub >> >> >> >> -- >> Mabwai Wycliff >> >> Digital Divide Data >> 7th Flr, Paramount Plaza - Kipande Rd >> off Globe Cinema Roundabout >> P.O Box 4282-00506 >> Nairobi >> wycliff.mab...@digitaldividedata.com >> +254-772 191 795 Tel (Office) >> +254-727 445 985 (Mobile) >> >> >> >> Creating a World of Digital Possibilities >> http://www.digitaldividedata.co.ke >> >> Skype: wycliff.mabwai >> >> >> >> > > > > -- > Mabwai Wycliff > > Digital Divide Data > 7th Flr, Paramount Plaza - Kipande Rd > off Globe Cinema Roundabout > P.O Box 4282-00506 > Nairobi > wycliff.mab...@digitaldividedata.com > +254-772 191 795 Tel (Office) > +254-727 445 985 (Mobile) > > > > Creating a World of Digital Possibilities > http://www.digitaldividedata.co.ke > > Skype: wycliff.mabwai > > > > -- Mabwai Wycliff Digital Divide Data 7th Flr, Paramount Plaza - Kipande Rd off Globe Cinema Roundabout P.O Box 4282-00506 Nairobi wycliff.mab...@digitaldividedata.com +254-772 191 795 Tel (Office) +254-727 445 985 (Mobile) Creating a World of Digital Possibilities http://www.digitaldividedata.co.ke Skype: wycliff.mabwai _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users