Create Tables And Insert Data In Sql Server Mobile On Dekstop
Hello (sorry my bad english, im brazilian)
I was using Visual Studio 2003 and SQL Server CE 2.0 for C# mobile applications. The .sdf database were created in the emulator or in the mobile device itself using Query Analizer.
The application developed need some initial data to run, and this data is obtained executing one service that reads a postgree database, and insert the data in the SQL CE database of the mobile device. But, given the size of the database (maybe 10.000 rows), it tooks too much time (sometimes 6 hours).
Now we are migrating to Visual Studio 2005 and SQL Server 2005 Mobile Edition.
I want to know if its possible to create the .sdf database and load the data into this database on the desktop. Maybe through the execution of a .sql script, or through a service executed on the desktop.
After this, its just upload de .sdf file to the mobile device.
I use the following 3 sets of sql code in SQL Server Management Studio Express (SSMSE) to import the csv data/files to 3 dbo.Tables via CREATE TABLE & BUKL INSERT operations:
-- ImportCSVprojects.sql --
CREATE TABLE Projects
BULK INSERT dbo.Projects
FIELDTERMINATOR = ',',
ROWTERMINATOR = ''
GO ======================================= -- ImportCSVsamples.sql --
CREATE TABLE Samples
BULK INSERT dbo.Samples
FIELDTERMINATOR = ',',
ROWTERMINATOR = ''
GO ========================================= -- ImportCSVtestResult.sql --
CREATE TABLE TestResults
BULK INSERT dbo.TestResults
FIELDTERMINATOR = ',',
ROWTERMINATOR = ''
======================================== The 3 csv files were successfully imported into the ChemDatabase of my SSMSE.
2 questions to ask: (1) How can I designate the Primary and Foreign Keys to these 3 dbo Tables? Should I do this "designate" thing after the 3 dbo Tables are done or during the "Importing" period? (2) How can I set up the relationships among these 3 dbo Tables?
Am trying to find a way to insert/update/delete data in a SQL mobile database on a Windows CE 5.0 device FROM a desktop PC.
This situation is completely stand alone, no network (apart form device/desktop), no GPRS etc etc etc.
I've looked at RDA but i dont believe it fits my app. (pulling data from a 2005 server that doesnt exist doesnt really help me much, push can't be used without a pull which kills the idea.)
The goal is a UI on the desktop that can manipulate data in the SQL mobile Database.
I've tried all i can find/think off in relation to this but to no avail.
My latest attempt has been using the simplest method possible (using a VS wizard datasource to the devices DB and tryign to whack that on a form) but this just creates a "Path not found. Check the directory for the database [Path = Mobile Device/ce_swipe/TestDB.sdf".
After a little bit of search, I succeeded! Here are the guidelines for using an SQL server mobile dataset residing in your desktop computer on your desktop (populating a SQL Server mobile database on your desktop). At the end of the guidelines, I give a sample program to populate an sqlce database from a text file.
I hope you will find these guidelines and sample program usefull.
You may contact me at email@example.com (remove nospam for actual address)
(You should have VS2005 and Sql Server 2005 installed on your desktop computer)
1. create a Windowsapplication project File->New Project Choose Windows in Project Types and Windows Apllication from Templates
2. Insert a reference to SQL Server Mobile Project->Add Reference->Browse c:Program FilesMicrosoft Visual Studio 8 Common7IDESystem.Data.SqlServerCE.DLL
3. Insert imports system.data.sqlserverce at the top of your form file
4. Write your application just as you would as if your project was a Smartdeviceapplication.
i.e. use sqlconnection and other constructs.
A sample application is as follows: (dbtableexist is a subroutine which shows how to process the SCHEMA. In this subroutine, change TABLES to INDEXES and TABLE_NAME to INDEX_NAME to search for an index)
Imports system.data.SqlServerCe Imports System.IO Public Sub createaccessfile(ByVal a$) 'curdbpath is the path of the database e.g. d:my sql databases Dim connStr As String = "Data Source = " & curdbpath & a$ & ".sdf" Dim conn As SqlCeConnection = Nothing Dim command As SqlCeCommand Try conn = New SqlCeConnection(connStr) conn.Open() command = conn.CreateCommand() If dbtableexist(conn, "ACCESSFILE") Then command.CommandText = "DROP TABLE ACCESSFILE" command.ExecuteNonQuery() End If
command.CommandText = "CREATE TABLE ACCESSFILE ( code nchar(20),field nchar(3),totalrecs nvarchar(255),indexbits ntext)" command.ExecuteNonQuery() command.CommandText = "CREATE INDEX codeindex on ACCESSFILE (field,code)" command.ExecuteNonQuery() Catch ex As SqlCeException Dim m1 = ex.Message Dim m2 = ex.Source Dim m3 = ex.NativeError MsgBox(m1 & m2 & m3, , "open " & a$ & " for accessfile creation") Exit Sub End Try
' Create and prepare a SQL statement. command.CommandText = " insert into accessfile values (?,?,?,?);"
' Read and insert the lines from the file until the end ' of the file is reached. Dim accfname As String = curdbpath & a$ & "_accessfile.inp" Dim fs As FileStream = New FileStream(accfname, FileMode.Open) Dim sr As New StreamReader(fs) Dim line As String, codestr As String, fieldstr As String, totalrecsstr As String, indexbitsstr As String Dim line1 As String, flagstr As String, field As Integer
line = sr.ReadLine : line1 = line Dim currec As Integer = 0 ' Note: Even though named parameterized queries are not supported, we still need ' to provide unique parameter names so that we can manipulate parameter collection; command.Parameters.Add("@code", "a") command.Parameters.Add("@field", 1) command.Parameters.Add("@totrecs", "c") command.Parameters.Add("@indexbits", "d") Do currec = currec + 1 If currec Mod 10 = 0 Then dispcaption(Str(currec)) flagstr = before(line, Chr(9)) : line = after(line, Chr(9)) codestr = before(line, Chr(9)) : line = after(line, Chr(9)) field = Val(before(line, Chr(9))) : line = after(line, Chr(9)) totalrecsstr = before(line, Chr(9)) : line = after(line, Chr(9)) indexbitsstr = line Try command.Parameters(0).Value = codestr command.Parameters(1).Value = Format(field, "000") command.Parameters(2).Value = totalrecsstr command.Parameters(3).Value = indexbitsstr ' Calling Prepare after having set the Commandtext and parameters. command.Prepare() command.ExecuteNonQuery() Catch ex As SqlCeException Dim m1 = ex.Message Dim m2 = ex.Source Dim m3 = ex.NativeError MsgBox(m1 & m2 & m3, , "Add record to Acessfile") conn.Close() sr.Close() Exit Sub End Try line = sr.ReadLine : line1 = line Loop Until line Is Nothing sr.Close() conn.Close() End Sub Private Function dbtableexist(ByVal dbconnection As SqlCeConnection, ByVal tablename As String) As Boolean Dim cmd As SqlCeCommand, rdr As SqlCeDataReader Dim iname As String, tablename1 As String, i As Integer tablename1 = UCase(tablename) cmd = dbconnection.CreateCommand cmd.CommandType = CommandType.Text cmd.CommandText = "select * from INFORMATION_SCHEMA.INDEXES" Try rdr = cmd.ExecuteReader Catch ex As SqlCeException Dim m1 = ex.Message Dim m2 = ex.Source Dim m3 = ex.NativeError MsgBox(m1 & m2 & m3) Stop End Try Do While rdr.Read iname = rdr("TABLE_NAME") If UCase(iname) = tablename1 Then dbtableexist = True : Exit Function Loop dbtableexist = False End Function
In SQL Server Management Studio, in Object Explorer, expand the (local) computer node.
Right-click the Replication folder, and then choose Configure Web Synchronization
There is no option when Right-clicking the Replication folder under my local server, however there IS an option for Configuring Web Synchronization on the Replication folder under SQL Server Mobile. (Unfortunately the Configure the publication for Web Synchronization is listed BEFORE the SQLServer Mobile Task, so it is pretty confusing) So, assuming this is what the author meant, I successfully configured the Web Server and the SQLMobile shows under my default web site.
When I attempt to create a Subscription under SQLServer Mobile, I get the following error:
Am I missing something? Should the web configuration be done on my local SQL Server, not the SQLServer Mobile replication directory? If so, how come I do not have that option as suggested in the directions?
I have checked and rechecked permissions. As I mentioned, the snapshot is successfully created (verified by Right-clicking the publication and viewing the snapshot agent status)
I am trying to take an entire MS SQL database and put it in an sql file. I have succesfully copied the tables into an sql file by highlighting the tables in enterprise manager and choosing 'generate sql script'.
That gives me the structure, but now I would like the data (in insert statements). I have looked in enterprise manager's export wizard and sql analyzer to no avail. There seem to be a lot of options for exporting data except this one! Please point me in the right direction.
At the end of the day, I would like to be able to put everything in a text file. Then, should I have problems, I can just copy my text into query analyzer and have a brand new database.
My apologies in advance for maybe appearing a little novice-like in my questions. That's because I am.
I have a large table containing some 8500 records, migrated from Access to MS SQL. I can access it through VS.NET without any trouble. I've partially built an app for my WM5 device using VS.NET, with nice forms, etc., and want to be able to view the records present in the large table via the forms.
So I need to know the easiest way to acces the data via my WM5 device, using forms designed in VS.NET. I have SQL Server and SQL Server Mobile - can I move the data from the SQL Server tables into a new SQL Server Mobile database?
Any help you can give would be greatly appreciated.
In my C# code I have a Class property that takes the value DateTime.Min upon initialisation, but when I try to insert this into the database column (yes, it is DateTime data type :)) I get an 'Unexpected Error' from SQL Server Mobile.
Hello I have a problem with setting relations properly when inserting data using adonet. Already have searched for a solutions, still not finding a mistake... Here's the sql management studio diagram :
and that causes (at line 67):"The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Question_SurveyTemplate". The conflict occurred in database "ankietyzacja", table "dbo.SurveyTemplate", column 'id'. The statement has been terminated. at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo batchCommands, Int32 commandCount) at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo batchCommands, Int32 commandCount) at System.Data.Common.DbDataAdapter.Update(DataRow dataRows, DataTableMapping tableMapping) at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping) at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable) at AnkietyzacjaWebService.Service1.createSurveyTemplate(Object o) in J:\PL\PAI\AnkietyzacjaWebService\AnkietyzacjaWebServicece\Service1.asmx.cs:line 397"
Could You please tell me what am I missing here ? Thanks a lot.
I have code that creates a SQL Mobile database on a server. It has worked in many environments, but I have one customer experiencing an issue. It will not create the database on their server. I would appreciate any thoughts.
The exact error message I get is "the operating system does not support encryption"
I am getting it on the line: SQLCeEngine.CreateDatabase()
Here is the code: SQLCeEngine = New SqlServerCe.SqlCeEngine(SQLConnectionString) ReturnValue += ": Declare new" SQLCeEngine.CreateDatabase() ReturnValue += ":Create" SQLCeEngine.Dispose() ReturnValue += ": Dispose"
Here is the error message I get from our application- the part after Err: is the actual error message from the system: Data Source = D:EVADATAWebServiceDownloads est;Password=test;Persist Security Info=False;: Declare new:Error!:Err:The operating system does not support encryption.
When I go into SQL Express and create a SQL Mobile database using it, it can be created. So, I have to believe it can be done on the server, but I don't know why the code can not do it. (e.g. FileConnect Object ExplorerServer Type: SQL Server compact Edition and create a database). The database is successfully created if I do it manually this way. It even creates fine if I select the 'Encrypt' button.
The machine is a Windows 2000 Server sp4 version 5.00.2195 SQL 2000 is installed SQL 2005 Express is installed I ran the SQLServerCE31-EN.msi file and it is installed. (I have also re-run and repaired it) I have also copied over the newest version of our code from scratch.
I'm trying to get my first replication going, and I have set up a database successfully, along with merge replication, however when I attempt to create the first subscriber, I get a permissions issue, regardless of what I do. In the SQLCESA30.LOG file, I get the following entries:
I posted the initial thread under the "Replication" forum, but am including this 'pointer' post here due to a lack of replies there, and the relevance of this thread to this forum. Please see http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=193693&SiteID=1 for more details.
Currently, I'm using the following steps to migrate millions of records from Foxpro tables to SQL Server tables:
1. Transfer Foxpro records to .dat files and then bcp to SQL Server tables in a dummy database. All the SQL tables have the same columns as the Foxpro tables. 2. Manipulate the data in the SQL tables of the dummy database and save the manipulated data into the SQL tables of the real database where the tables may have different structure from the corresponding Foxpro tables.
I only know the following ways to import Foxpro data into SQL Server:
#1. Transfer Foxpro records to .dat files and then bcp to SQL Server tables #2. Transfer Foxpro records to .dat files and then Bulk Insert to SQL Server tables #3. DTS Foxpro records directly to SQL Server tables
I'm thinking whether the following choices will be better than the current way:
1st choice: Change step 1 to use #2 instead of #1 2nd choice: Change step 1 to use #3 instead of #1 3rd choice: Use #3 plus manipulating in DTS to replace step 1 and step 2
Need some help. In my db I created 3 tables. 1- tblClient 2- tblCategories 3- tblClient_Cat ( colums: 1) client_id 2) cat_id).What I'm trying to do is create a sp to insert data from a web form into the tblClient and into the tblCat using the same sp. this is all I got so far.CREATE PROCEDURE [usp_insetClient](@ClientName varchar(25),@Contact varchar(100),@Tel char(13),@Fax char(13),@WebAdd varchar (250),@email varchar(250),@CatID int) ASBegininsert tblClient(Client_name,client_contact,Lient_tel,client_fax,client_webadd,client_email)values(@ClientName,@Contact,@Tel,@Fax,@WebAdd,@email)select @@identityinsert tblclient_cat(cat_id,client_id)values(@Catid,@@identity)(I tried to use this sp but didnot work)as you can see I need help inserting the client_id just created by the db into the tblclient_cat since the cat_id is passed from the web formThanksErnesto
Hi I was watching this beginner webcast Lesson 8: Obtaining Data from a SQL Server 2005 Express Edition Database http://msdn.microsoft.com/vstudio/express/visualcsharp/learning/#beginners I tried to do the same exact thing. But when I tried to insert data in the newly created table I got the follwoing error window: Invalid value for cell (row1, column 2)Then changed value in this cell was not recognized as valid..Net Framework Data Type: ByteError Message; You cannot use the Result pane to set this Field data to values other than NULL. Type a value appropriate for the data type or press ESC to cancel change.
hi, iam creating an asp.net application the database is created on the SQLSERVER, but i have a problem, i pretend to insert data in to 2 tables and i have no ideia how to do this. Can you help me to solve this problem? Please.....
hi, i have got a problem, iam creating an asp.net application, but i pretend to insert data in two different tables, but i have no ideia how to do this.Can you help to solve this problem? please..........
hi all , I need to get data from textboxes and insert data into 2 tables. I tried as following code but it didn't work.. TextBox6 is primary key for Book and it's auto number field at access database ( So, if i run "cmd = New OleDbCommand(str, cn)" then data will added to other colume and the primary key will auto create .) . But I need to insert the data into Inventory too using same button . B_ID is foreign key in Inventory to connect with Primary Key (which is Book_ID) at Book. How can I insert data into those 2 table using vb.net ? Thanks in advanced and will appreciate if you could provite the code sample too .
--- Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click Dim cmd2 As OleDbCommand Try cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C: estdb.mdb;") cn.Open() str = "insert into Book(Title,Arthur,Publisher,Year)" + " values ('" & TextBox1.Text & "','" & TextBox2.Text & "','" & TextBox3.Text & "','" & TextBox4.Text & "')" Dim str2 As String = "insert into Inventory(B_ID,Price,Quantity)" + " values (" & TextBox6.Text & ",'" & TextBox7.Text & "','" & TextBox8.Text & "')" cmd = New OleDbCommand(str, cn) cmd = New OleDbCommand(str2, cn) cmd.ExecuteNonQuery() cmd2.ExecuteNonQuery()
Catch ex As Exception MessageBox.Show("Error: " & ex.Message) 'catch error End Try cn.Close() Call EnableReadOnly()
I have timecard records need to be inserted into the EMP_TC table automatically. This job should run every day to create EMP_TC records for the full current month for any ASSIGNMENT_ID€™s where ASGNMT_END_DATE is either null or greater than the run date of this job and PRD_START_DATE is in the current run month. PRD_START_DATE is always Sunday, PRD_END_DATE is always Saturday. For example, if an employee starts a new project on 4/8/2008, the first timecard record in EMP_TIMECARD for that EMP_ASGNMT_ID should be 4/6/2008 €“ 4/12/2008. If PRD_END_DATE is null then the job would populate the following 4 records for the EMP_ASGNMT_ID: i have currently stored procedure doing this but i had to specify the dates manually .how could i automate this process. i can avoid stored procedures needed but i am really confused on what logic to apply to it. We need to pick the assigment id and dates automatically from employment assignment table. Assignment id is nothinb but a identity coloum please advise please advise
I'm developing an application that need to create everyday information to be stored in a SQL Mobile Database.
The SQL Mobile database will have:
Last schema from tables (in SQL Server database); Last information in the tables;
I don't want to copy all the tables from SQL Server database than this mecanism should give me the way to choose the tables.
I have been reading about the SqlCeReplication but I don't undestand what is InternetUrl property. The SQL Mobile database is created in the PC and after is copy to the the Mobile Device. How can I avoid the InternetURL property?
tkx in advance Paulo Aboim Pinto Odivelas - Portugal
hi. can anyone help me, please. i am using vwd2005 express edition and sql server 2005 express. i want to insert data in multiple tables at once. the tales are linked to each other through primary key and foreign keys. for example i have one table with a primary key. when i add data to it, i have to retrieve the id of the newly inserted record and then introduce this id in another table as a foreign key. i have 10 tables linked in this way, and only one form to add data to the database. can you help me, please? i'd be very greatful. thanks.
Hello frdz, I have two tables in sqlserver 2005. I have created the stored procedure for insert,update data.I m creating my application in asp.net with C# Table-1 CUSTOMERFields:customerid int identity,cardid int,customername varchar(20) not null,address varchar(20) not null,city varchar(20) not null,emailid varchar(20) Table-2 CARDFields:cardid int identity,cardtype varchar(20) not null,carddetails varchar(20) not null INSERT INTO CUSTOMER (customername ,address,city,emailid) VALUES (@customername,@address,@city,@emailid) SELECT @customerid = SCOPE_IDENTITY()/* HELP HERE NOT ABLE TO GET DATA OF CARD */ SELECT @cardid = cardid from CARD where customerid =@cardid Pls tell me how to insert the data ...There is only one cardid for only one customerid both should be unique no duplication....One customer can have only one and one card...
Hi there!!! Trying to figure something out, I have searched this forum but no answer to my dilemma.
I have three tables on a database that I have to insert new data and update the old one. The structure of the tables is like this:
Table1 custid int primary key,identity fname varchar(30) lname varchar(30)
Table2 fileid int primary key, identity ssn varchar(11) custid int foreign key
Table3 statusid int primary key,identity title varchar(18) fileid int foreign key
This is very general but that's the idea. Now I receive a text file with the necessary info, I've already parse and break down the file into the correct fields.
Now for my question, how I insert the relevant data onto the tables from this one parsed file? and also how I go about inserting the primary keys from the different tables onto the foreign keys of each tables?
I tried relationships and key indexes, but it just spew a bunch of errors, that I'm investigating.
I don't understand why I am having this error. It does create the database on AddSubscription() method but it is failing opening the database on Synchronise(). I have also tried uninstalling and then reinstalling all the SQL Mobile components in the following order.
I am developping a non-managed C++ application for PocketPC using a SQL Server mobile database.
The application is compiled for PocketPC 2003 and uses SQL Server Mobile v2. I use Visual Studio 2005. But I need to compile the application for Windows Mobile 5.0 devices. So I installed the WM5 SDK and had the WM5 into my project configuration.
The "ssceoledb.h" which I include incluses the "transact.h" file. But my problem is that this file is only provided with the PocketPC 2003 SDK and not in the WM5 SDK. So I cannot use the WM5 configuration project with SQL Server. I also tried with the last SQL Server Mobile 2005 (ie v3.0) and the "ssceoledb30.h" also includes "transact.h".
Did I miss something to install ? Do you know how I can resolve the problem ?
Hi all, Thanks in advance, I have to transfer data from sql express 2005 to sql mobile(.sdf).
Actually i have a sql server express 2005 (sqlee) in laptop... and the .sdf (sqlce) handheld device (ppc) that has been connected by activesync.
Can you please make it clear for my following questions? 1. Is it possible to write a query like " insert into ....sdf select from...sqlexp...." between 2 servers...? 2. Is there any copy command like a BULKCOPY .. to insert all records to .sdf (sqlce) from sql server express?
(or) 3. Is it correct to create a smart device application (CF 2.0) to transfer data between sql express and .sdf by using active sync...?
i have a small problem with this code, it cann't save changes on Datatable to the database SQL Server Mobile ..
the execution work succesfully but without changes in the database !
here is the code, please try to help :
#Region " << Declarations >> "
Dim objCon As New SqlCeConnection("Data source=Storage CardFull_Database.sdf") Dim objDA As SqlCeDataAdapter Dim objCmdBldr As SqlCeCommandBuilder Dim objTB As New DataTable("MyTB") Dim objBS As New BindingSource
Private Sub frmDatabase_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
objDA = New SqlCeDataAdapter("Select * From MyTB", objCon) objDA.MissingSchemaAction = MissingSchemaAction.AddWithKey
objDA.Fill(objTB) objBS.DataSource = objTB
' Add New Record for example Private Sub mnuAddNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuAddNew.Click
If Not CType(objBS.Current, DataRowView).IsNew Then objBS.AddNew()
Dim dRowView As DataRowView = objBS.Current dRowView.BeginEdit()
dRowView("id") = 10 dRowView("name") = "Someone"
dRowView.EndEdit() End If
' Calling Save Methode mnuSave_Click(sender, e)
' Save Values Private Sub mnuSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuSave.Click
objBS.EndEdit() objCmdBldr = New SqlCeCommandBuilder(objDA) objDA.Update(objTB)
Ok, I have a page on my website where we can add products to our database. We are a music store, and most products have different versions or colors. I've created 2 tables, Products and Subproducts. The products table may hold info like Fender Stratocaster, and the subproducts would hold colors (Blue, Sunburst, etc). The subproducts table has an integer field called MainProductID, which is linked to the mainproducts table field RecordID. So far the page uses a wizard where if first creates the main product using an sql datasource. After the data has been added to the main products table, my page gives you the opportunity to add different sub products. The problem I am having is actually feeding in the RecordID from the main products table to my insert parameter on the sub products data source. This is what I have tried so far: There is a formview on the page that is bound to the main products table, after the entry is created I can physically see the info on my screen, so I know the data is there at my disposal SubProductsDataSource.InsertParameters.Add("@MainProductID", Formview1.datakey.item("RecordID"))SubProductsDataSource.Insert()Using this adds the data to the table, but the MainProductID is nullalso is there a cheap little way to refresh a page, because when I upload the product images I have it go to the next step where you are supposed to be able to see the images you uploaded, I don't see them which makes me think that the page is loading faster than the images are uploading. Thanks