Refresh sdf file and insert from txt

Question

1.00/5 (1 vote)

See more:

My app installed in two system and access the data from txt file from a remote location/system.sql server compact(.sdf) file as database. After installation it is ok fine. But updation not happening. How to clear or refresh the sdf file to null.if tge sdf file to be null it will read from txt. Dataset.Reset command shows error.both app to be updated after reopen. Not preferring the delete command because the ID numbering will change. How it possible? Please give me a suggestion/solution. Thanks

What I have tried:

VB.NET

  1  Imports System.Data.SqlServerCe
  2  Imports System.Data
  3  Imports System.IO
  4  
  5  Public Class Form1
  6      Dim CONN As New SqlCeConnection("Data Source=|DataDirectory|\Contactdb.sdf")
  7      Dim CMD As SqlCeCommand
  8      Dim DA As SqlCeDataAdapter
  9      Dim TABLE As DataTable
 10      Dim SRDR As StreamReader
 11      Dim SWR As StreamWriter
 12  
 13      Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
 14          If CONN.State = ConnectionState.Closed Then
 15              CONN.Open()
 16          End If
 17          If Not My.Computer.FileSystem.DirectoryExists(DIRECTORY) Then My.Computer.FileSystem.CreateDirectory(DIRECTORY)
 18          End If
 19          If Not System.IO.File.Exists(fname) Then
 20              System.IO.File.CreateText(fname)
 21          End If
 22  
 23          dgv1.Rows.Clear()
 24  
 25  SRDR = New IO.StreamReader(fname)
 26          While SRDR.Peek <> -1
 27              txt1 = SRDR.ReadLine()
 28              TXT = txt1.Split(vbTab)
 29              STR = "INSERT INTO Contactdb(Name,[Mobile],Office) VALUES ('" & TXT(0) & "','" & TXT(1) &"','" & TXT(2) & "')"
 30              CMD = New SqlCeCommand(STR)
 31              CMD.Connection = CONN
 32              CMD.ExecuteNonQuery()
 33          End While
 34          SRDR.Close() 
 35      End Sub
 36  
 37      Private Sub btnsave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsave.Click
 38          STR = "INSERT INTO Contactdb(Name,[Mobile],Office)VALUES ('" & txtname.Text & "','" & txtmob.Text & "',,'" & txtoff.Text & "')"
 39          CMD = New SqlCeCommand(STR, CONN)
 40          CMD.ExecuteNonQuery() 
 41          MsgBox("Contact of " & txtname.Text & " Saved", vbExclamation, "SAVE")
 42  
 43  Dim wr As New StreamWriter(fname)
 44          Dim RC As Integer = dgv1.Rows.Count
 45          Dim CC As Integer = dgv1.Columns.Count
 46          For i As Integer = 0 To RC - 2 Step +1
 47              For j As Integer = 0 To CC - 1 Step +1
 48                  wr.Write(dgv1.Rows(i).Cells(j).Value & vbTab) 
 49              Next
 50              wr.Write(wr.NewLine)
 51          Next
 52          wr.Close()
 53      End Sub

Posted 29-Nov-21 22:03pm

Gireesh B

Updated 29-Nov-21 23:04pm

Richard Deeming

v2

Add a Solution

Comments

Richard MacCutchan 30-Nov-21 4:08am

"Dataset.Reset command shows error."
What error? Where does it occur? No one can help you if you do not provide full details of your problem.

Richard Deeming 30-Nov-21 4:20am

Your code is vulnerable to SQL Injection[^]. NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]

Gireesh B 30-Nov-21 4:54am

Hi, i didn't get about this injection. Can you explain simply as taking my code as an example.

Richard Deeming 30-Nov-21 4:59am

Type the following into your txtoff box, and press the "save" button:

Robert');DELETE FROM Contactdb;--

But make sure you have a backup of your database first.

Then read the links I posted to understand what the problem is, and how to fix it.

Using cmd As New SqlCeCommand("INSERT INTO Contactdb (Name, [Mobile], Office) VALUES (@Name, @Mobile, @Office)", CONN)
    cmd.Parameters.AddWithValue("@Name", TXT(0))
    cmd.Parameters.AddWithValue("@Mobile", TXT(1))
    cmd.Parameters.AddWithValue("@Office", TXT(2))
    cmd.ExecuteNonQuery()
End Using

Using cmd As New SqlCeCommand("INSERT INTO Contactdb (Name, [Mobile], Office) VALUES (@Name, @Mobile, @Office)", CONN)
    cmd.Parameters.AddWithValue("@Name", txtname.Text)
    cmd.Parameters.AddWithValue("@Mobile", txtmob.Text)
    cmd.Parameters.AddWithValue("@Office", txtoff.Text)
    cmd.ExecuteNonQuery()
End Using

CHill60 30-Nov-21 4:44am

Also "Not preferring the delete command because the ID numbering will change" - ID numbering will not change if you use an IDENTITY column. There may be gaps in the number sequence as the result of a delete. If the ID numbering is changing for you then you must be using ROW_NUMBER() as an ID - swap that out to use an IDENTITY column (or other persisted number on each row)

Gireesh B 30-Nov-21 4:52am

Ok... Thank you.. I want to try this and reply.

Gireesh B 30-Nov-21 22:59pm

Earlier mentioned changes and suggestions implemented. Thank you.
and one more doubt to be cleared which is the Dataset. Tables. Clear() and Datatable. Rows. Clear()
Not working. Data appending every time.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Accepted Answer · 2021-11-29T23:04:00

In addition to what Richard has (correctly) said about SQL injection ...

You are trying to "share" a single user file between two systems: that's a very poor idea - and when you open a connection to it and never close that connection you cause problems for the other system, since an exclusive lock is established to ensure data integrity when a connection is established for updates.

So the second system tries to connect, find the file is unavailable, and crashes.
If you want to use a DB and share it, use a "proper" server based system such as SQL Server or MySQL - SQLCE and Access are a bad idea, particularly if you don't work extremely carefully at all times!