|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Why does INSERT statement delete previous records?I have an insert command which works fine, however, it overwrites anything that was in the database before. Looking for a little help: (the database starts off empty) Here is my code: Dim x As Integer = rtf4.Find("<sdr_string>") Dim y As Integer = rtf4.Find("</sdr_string>") Try rtf4.Select(x + 4, ((y - 1) - (x + 4)) + 10) rtf4.ScrollToCaret() rtf4.Focus() rtf4.Cut() Dim j As String = Clipboard.GetText.Trim.ToString Dim t As String = j.Substring(8, j.Length - 17).Trim Dim command As String command = "INSERT into ParentChild(signal, FileName, Sheet) values('" & t & "','" & My.Forms.Form1.Title.Replace(".edit", "") & "','" & My.Forms.Form1.Title.Substring(My.Forms.Form1.Title.Length - 8, 3) & "'" & ")" If cn.State = ConnectionState.Closed Then cn.Open() End If Dim cmd As New OleDb.OleDbCommand(command, cn) cmd.CommandType = CommandType.Text cmd.ExecuteNonQuery() cn.Close() Button2.PerformClick() Catch ex As Exception If MessageBox.Show("Database has been prepped", "Database Entries", MessageBoxButtons.OK, MessageBoxIcon.Information) = Windows.Forms.DialogResult.OK Then Me.Close() End If Exit Sub End Try What do you mean it overwrites stuff, as in an Update when it should be an
insert? Also, the sql statment you're using is dangerous, paramaterize it or be prepared to deal with all the h3ll associated with not paramaterizing. I'd also recommend moving the cn.Close() down to a finally block, otherwise it may never be excecuted. Lemme know the answer to the first and hopefully we can help nail it down. <the_grove_***@yahoo.com> wrote in message Show quote news:1139869246.100141.78570@f14g2000cwb.googlegroups.com... >I am writing an application that populates a database from text files. > I have an insert command which works fine, however, it overwrites > anything that was in the database before. > > Looking for a little help: (the database starts off empty) > > Here is my code: > > Dim x As Integer = rtf4.Find("<sdr_string>") > Dim y As Integer = rtf4.Find("</sdr_string>") > > Try > > rtf4.Select(x + 4, ((y - 1) - (x + 4)) + 10) > rtf4.ScrollToCaret() > rtf4.Focus() > rtf4.Cut() > Dim j As String = Clipboard.GetText.Trim.ToString > Dim t As String = j.Substring(8, j.Length - 17).Trim > > Dim command As String > command = "INSERT into ParentChild(signal, FileName, Sheet) > values('" & t & "','" & My.Forms.Form1.Title.Replace(".edit", "") & > "','" & My.Forms.Form1.Title.Substring(My.Forms.Form1.Title.Length - 8, > 3) & "'" & ")" > > If cn.State = ConnectionState.Closed Then > cn.Open() > End If > > Dim cmd As New OleDb.OleDbCommand(command, cn) > cmd.CommandType = CommandType.Text > cmd.ExecuteNonQuery() > cn.Close() > Button2.PerformClick() > > Catch ex As Exception > If MessageBox.Show("Database has been prepped", "Database > Entries", MessageBoxButtons.OK, MessageBoxIcon.Information) = > Windows.Forms.DialogResult.OK Then > Me.Close() > End If > Exit Sub > End Try > Hi,
INSERT SQL statement cannot overwrite data in a database. It always insert new record. Only UPDATE could do this. How do you check that it overwrites the data? Also, as William pointed, your SQL statements could be pretty dangerous due to the fact that you are constructing them using concatenation. It could lead to the SQL injection. Show quote news:1139869246.100141.78570@f14g2000cwb.googlegroups.com... >I am writing an application that populates a database from text files. > I have an insert command which works fine, however, it overwrites > anything that was in the database before. > > Looking for a little help: (the database starts off empty) > > Here is my code: > > Dim x As Integer = rtf4.Find("<sdr_string>") > Dim y As Integer = rtf4.Find("</sdr_string>") > > Try > > rtf4.Select(x + 4, ((y - 1) - (x + 4)) + 10) > rtf4.ScrollToCaret() > rtf4.Focus() > rtf4.Cut() > Dim j As String = Clipboard.GetText.Trim.ToString > Dim t As String = j.Substring(8, j.Length - 17).Trim > > Dim command As String > command = "INSERT into ParentChild(signal, FileName, Sheet) > values('" & t & "','" & My.Forms.Form1.Title.Replace(".edit", "") & > "','" & My.Forms.Form1.Title.Substring(My.Forms.Form1.Title.Length - 8, > 3) & "'" & ")" > > If cn.State = ConnectionState.Closed Then > cn.Open() > End If > > Dim cmd As New OleDb.OleDbCommand(command, cn) > cmd.CommandType = CommandType.Text > cmd.ExecuteNonQuery() > cn.Close() > Button2.PerformClick() > > Catch ex As Exception > If MessageBox.Show("Database has been prepped", "Database > Entries", MessageBoxButtons.OK, MessageBoxIcon.Information) = > Windows.Forms.DialogResult.OK Then > Me.Close() > End If > Exit Sub > End Try > hi guys, thank you all for your input.
I know that it is overwriting other records because after it dumps the data in the database (Which it does correctly), it overwrites previous information in the the table. For example, let say I run one .edit file through my app called 316720_01.edit. My app parses the file and dumps data in three columns. When I view the data, all is fine. Then when I run my next .edit file, the first one gets overwritten. Can you guys provide an example how else I can do this to avoid this or clear up my "dangerous" SQL statement. Thanks, John It could be because you are using Cut method that basically copies data into
the clipboard, but deletes data from the source. Show quote news:1139933420.604025.250180@g14g2000cwa.googlegroups.com... > hi guys, thank you all for your input. > I know that it is overwriting other records because after it dumps the > data in the database (Which it does correctly), it overwrites previous > information in the the table. > > For example, let say I run one .edit file through my app called > 316720_01.edit. > My app parses the file and dumps data in three columns. > > When I view the data, all is fine. Then when I run my next .edit file, > the first one gets overwritten. > Can you guys provide an example how else I can do this to avoid this or > clear up my "dangerous" SQL statement. > > Thanks, > John >  |
|||||||||||||||||||||||
Other interesting topics