cannot insert into sdf database using mobile application

[joe1987]

Private Sub InsertDatabase()
Dim rowaffected As Integer
Dim SqlInsert As String
Dim fname As String
Dim mname As String
Dim lname As String

Dim ConnPPC As New System.Data.SqlServerCe.SqlCeConnection("Data Source =" + (System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase) + "\test.sdf;"))
ConnPPC.Open()

fname = Me.txtfname.Text
mname = Me.txtmname.Text
lname = Me.txtlname.Text
Dim cmd As SqlCeCommand = ConnPPC.CreateCommand

Try
SqlInsert = "INSERT INTO test1(firstname,middlename,lastname) VALUES('" & fname & "','" & mname & "','" & lname & "')"

cmd.CommandText = SqlInsert
cmd.Connection = ConnPPC
rowaffected = cmd.ExecuteNonQuery()

If rowaffected = 1 Then
MsgBox("Client added succefully")
Else
MsgBox("Cannot add new client")
End If

ConnPPC.Close()
Catch ex As Exception
MsgBox("Error :" & ex.ToString)
End Try
End Sub

[joe1987]

can anyone help me with this code ? :S

[kvprajapati]

Open a database connection before ExecuteNonQuery()

[joe1987]

i have already opened a daabase connection , and so i move it just before the ExecuteNonQuery() but it did not work

[Ramesh S]

Does your code throw any error?

[joe1987]

none error

[Ramesh S]

I have doubt about the path passed to the SqlCeConnection object.

Display the value of the connection details in a message box. Check whether the file name with path displayed in the messagebox is correct.

You can dislay that information from the following code

MessageBox.Show("Data Source =" + (System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase) + "\test.sdf;")

[joe1987]

how can i fix the problem ? it is not the same

[joe1987]

C:\Documents and Settings\joe.elhajj\My Documents\Visual Studio 2005\Projects\final test\final test\test.sdf

this is the full path of the database so how can i put it correctly in the connection ?

[joe1987]

i have tried to put the full path but an error occurs:
Format of the initialization string does not conform to specification starting at index 0.

the connection was:
ConnPPC As New System.Data.SqlServerCe.SqlCeConnection("C:\Documents and Settings\joe.elhajj\My Documents\Visual Studio 2005\Projects\final test\final test\test.sdf")

[Ramesh S]

This file must be in your PDA or emulator. So copy the .sdf , for example, to the My Documents folder in the PDA or emulator.

And then try this

Dim ConnPPC As New System.Data.SqlServerCe.SqlCeConnection("Data Source =" + @"\My Documents\test.sdf;")

[joe1987]

Dim ConnPPC As New System.Data.SqlServerCe.SqlCeConnection("Data Source =C:\Documents and Settings\joe.elhajj\My Documents\Visual Studio 2005\Projects\final test\final test\test.sdf;")

why the connection would not open?

[joe1987]

i did not understand what did u mean,i did not install any emulator it is already existing in the .net so can you please explain to me more?

[Ramesh S]

I thought you are working smart device project. Just leave it.

Dim ConnPPC As New System.Data.SqlServerCe.SqlCeConnection("Data Source =C:\Practice\test.sdf")

Here 'Practice' is name of the folder where i keep the SDF file. It is working for me.

Did you get any error?

[joe1987]

an error occurrs saying the path is not valid,check the directory for the database

Dim ConnPPC As New System.Data.SqlServerCe.SqlCeConnection("Data Source =C:\final test\test.sdf")

[Ramesh S]

Where are you running your application. In local or inside a mobile device or emulator?

Inside mobile device or emulator, You can mention data source like
"Data Source =\My Documents\test.sdf". You cannot specify drive name like 'C:\' or 'D:\'.

If you are running your application in local( your PC) and your project is not smart device project, then check whether the test.sdf exists in the folder 'C:\final test'.

[joe1987]

i am runing my program on local and the full path of the file is

C:\Documents and Settings\joe.elhajj\My Documents\Visual Studio 2005\Projects\final test\final test

i tried to put it in the connection data source but the same error occurs

[Ramesh S]

i am runing my program on local and the full path of the file is

C:\Documents and Settings\joe.elhajj\My Documents\Visual Studio 2005\Projects\final test\final test

i tried to put it in the connection data source but the same error occurs

Just for testing purpose,

Place your sdf file in C:\test.sdf and then change your connection details as

Dim ConnPPC As New System.Data.SqlServerCe.SqlCeConnection("Data Source=C:\test.sdf")

Now check whether it is working.

[joe1987]

i placed the file in C: and i tested it
the error is :The path is not valid.check the directory for database [path = "C:\test.sdf"]

[joe1987]

please can anyone help me?i don't really know what is happening

[kvprajapati]

Generally, device applications are deployed at \program files\projectname_folder (you may change this settings - click on project name & set property Output File Folder in property windows)
So your database (deployed) path should be,

Dim cn As New SqlCeConnection("Data Source=\Program Files\SmartDeviceProject2\AppDatabase1.sdf")
'SmartDeviceProject2 is a name of project

[joe1987]

the output file in my project says %CSIDL_PROGRAM_FILES%\final_test
so how my connection shoud be written and how to get access to this folder?

[kvprajapati]

Emulator deploy (setup) an application in this folder along with database (.sdf).