[RESOLVED] VB Crash course needed

[WythyRed]

I just want to populate the DB i've created in SQL CE and i tried to do it using a query, but i dont know how to 'run' the query so it actually puts the data into the DB.

At the moment i just want to put some made up data (Locations: bedroom, bathroom, hall, kitchen, living room, garden. Objects: mum, dad, son, daughter, cousin. grandfather, grandmother.) into the DB so i can test it before filling it with the proper data.

[WythyRed]

This is the query i did:

INSERT INTO LocationTable (LocationName)
VALUES (Bathroom, Kitchen, Bedroom, Hall)

[petevick]

You would presumably have got an error message on the insert which should have been some guidance.

You are trying above to put 4 values into 1 column

you need
insert into locationtable (locationname) values ('Bathroom')
insert into locationtable (locationname) values ('Kitchen')
insert into locationtable (locationname) values ('Bedroom')
insert into locationtable (locationname) values ('Hall')

These are not really mobile device development issues, but standard SQL usage

[gep13]

Hey,

Since you are editing the table in Management Studio, why not just type the information directly into the columns. I forget the exact menu item now, but if you right click on the table, one of the options should be something like "View Data" or "Show Data". Once you have that open, just write the data in as you would within Excel. Obviously, this won't help when it comes to adding entries through code, you will need to get the query working for that, but it will let you get some data in there to start with.

As Pete indicated, if you are wanting to insert multiple rows into a table you can't do that in one SQL statement, you would need one SQL statement for each row that you are trying to add. You can add values into multiple columns in the same statement.

Gary

[petevick]

Afraid this doesn't exist for CE databases in SQL Management Studio - but I forgot, it does work from Visual Studio

In Server Explorer in VS, connect to your CE database, right-click on your table, Select Show Table Data and it will show all the records and let you add

[WythyRed]

I was about to say that i dont think you can in the management studio, but you were right about VS2008. Good man.

[WythyRed]

Now when i try and add the column object name as a datasource for the combo box i get an error telling me that:

'This operation requires a reference to SQL Server Compact 3.5. The project has a reference to a different version. Update the reference and try again.'

[gep13]

Afraid this doesn't exist for CE databases in SQL Management Studio - but I forgot, it does work from Visual Studio

In Server Explorer in VS, connect to your CE database, right-click on your table, Select Show Table Data and it will show all the records and let you add

Ah, good to know!! I was working from memory there, and now that I think about it, it was in Visual Studio that I was doing this. I sort of assumed you could do the same in Management Studio.

Gary

[WythyRed]

Can anyone recommend a book on building device apps within vs2008?

[gep13]

Hey,

I don't know of any books off the top of my head, but there are lots of good web resources:

http://msdn.microsoft.com/en-us/netf.../bb495180.aspx
http://www.dotnetfordevices.com/ specifically the RoadAssistance WebCast Series which you are find here:
http://www.dotnetfordevices.com/snippets/12.html
http://channel9.msdn.com/posts/Rory/...-Introduction/

Hope some of those help!!

Gary

[WythyRed]

Now when i try and add the column object name as a datasource for the combo box i get an error telling me that:

'This operation requires a reference to SQL Server Compact 3.5. The project has a reference to a different version. Update the reference and try again.'

I've goggled that, and apparently the answer is:

This issue occurs because the version of the System.Data.SqlServerCe.dll that is referenced in the project differs from the System.Data.SqlServerCe.dll file that exists on the computer. To resolve this issue, follow these steps:

1. On the View menu, click Solution Explorer.
2. Click References.
3. Click to select the System.Data.SqlServerCe check box, and then set the Specific Version property to False in the Properties dialog box.

But i can not see 'References' after selecting the View menu. Anyone got any ideas?

[gep13]

Within the Solution Explorer for your project you should be able to expand out the project, and one of the nodes should be the References one.

As here:



Gary

[WythyRed]

Ta, i've made the correction outlined above.

Do you know how i can find out where the project is going to look for the DB when i put it on the emulator, so that i can put it in the correct directory?

[gep13]

Hey,

That really depends on how you have set this up.

Normally I store the database in a folder called data, and I add a connection string, normally in the configuration file, that stores the connection to the database, and I use this connection in the application. Then if I need to move the database, I just need to edit the config file.

Gary

[WythyRed]

I have the DB, named project.sdf, in a folder in my documents.

C:\Documents and Settings\Administrator\My Documents\Visual Studio 2008\Projects\SmartDeviceProject1\SmartDeviceProject1

To be exact. This is the same folder that my device app is saved in too.

How would i go about making a configuration file? This seems to be getting more and more difficult at every turn!

[gep13]

Oh man, it's all coming back to me now.

The .Net Compact Framework doesn't have ConfigurationManager class, so you have to do that yourself. There are various references on the web about running your own version, here is one of them:

http://blogs.freshlogicstudios.com/P...c-597627e92247

And here is a method that I use to check whether the Database actually exists on the file system:

        /// <summary>
        /// Tests to see if the SQL Server Database Exists
        /// </summary>
        /// <returns></returns>
        public static bool DatabaseExists()
        {
            if (File.Exists(GlobalCache.Instance.AppPath + "Data\\" + ConfigurationManager.AppSettings["SQLDatabase"]))
            {
                return true;
            }
            else
            {
                return false;
            }
        }

Where SQLDatabase in the config file is:

    <add key="SQLDatabase" value="test.sdf"/>

Hope that helps!!

Gary

[petevick]

Hi,
the emulator doesn't know anything about C:\Documents and Settings\Administrator\My Documents\Visual Studio 2008\Projects\SmartDeviceProject1\SmartDeviceProject1

drag and drop the SDF file into your project (or add the sdf to your project), and set it to 'copy if newer', and the sdf will be copied to your emulator when you deploy.

Alternately, 'dock' your emulator using the device emulator manager in visual studio, explore the device and copy the sdf that way.

The best book (in my opinion) on CF Development is Microsoft Mobile Development Handbook which is for 2005 - there are no books out yet for 2008 development on mobile devices

HTH

[WythyRed]

Adding the sdf file to the project sounds like the simpliest way to go.

The sdf and the project are already in the same folder on my hard drive - is that what you mean by drag and drop it?

[petevick]

In Visual Studio, drag the sdf from Windows Explorer and drop it in the project.
Alternately do Project>Add Existing Item and add it that way.

Remember to set the properties to 'Copy if newer'

[WythyRed]

Hello all - I'm back. Stop crying at the back.

I'm going relatively strong at the moment and have ironed out all my cab issues.

I'm now ready to start populating my database using a form in the project and, as you could probably have guessed, I have no idea how to do this, although I imagine it'll involve an INSERT function.

This is my (very basic) Location Table that i want to populate:



And this is the form I'm going to use to populate it:



What should happen is that when the 'Add Location' button is selected the Location Name entered into the box will be entered into the Db. Each record obviously needs a unique ID which will need to be done by the system, and currently when you add a new record manually the system provides the unique ID number.

What code do i need to put behind the button to make this happen? If I can figure out how to add this one field, then I should be able to adapt the code to add the multiple fields that need doing when a user adds a new object.

[gep13]

Hey,

Here is a quick example:

                SqlCeCommand  cmd = new SqlCeCommand();
                cmd.CommandText = "INSERT INTO Event_Items(EventID, EventType, ItemID, ItemType, Discipline) VALUES(@EventID, @EventType, @ItemID, @ItemType, @Discipline)";
                cmd.Parameters.AddWithValue("@EventID", eventID);
                cmd.Parameters.AddWithValue("@EventType", eventType);
                cmd.Parameters.AddWithValue("@ItemID", item.ItemID);
                cmd.Parameters.AddWithValue("@ItemType", item.ItemType);
                cmd.Parameters.AddWithValue("@Discipline", item.Discipline);

                cmd.Connection = GlobalCache.Instance.Connection;

                int result = cmd.ExecuteNonQuery();

You should be able to see that I am using a parameterized query, adding the values of the parameters in that query to the command object, and then executing that query.

You will need to replace GlobalCache.Instance.Connection with the connection to your database.

Hope that helps!!

Gary

[WythyRed]

So how would that code look with my fields?

SqlCeCommand  cmd = new SqlCeCommand();
                cmd.CommandText = "INSERT INTO LocationTable (LocationID, LocationName) VALUES(@LocationID, @LocationName";
                cmd.Parameters.AddWithValue("@LocationID", LocationID);
                cmd.Parameters.AddWithValue("@LocationName", LocationName);
                
                cmd.Connection = Data Source=C:\Documents and Settings\Administrator\My Documents\Visual Studio 2008\Projects\SmartDeviceProject2\SmartDeviceProject2\sdf35.sdf;Persist Security Info=True;

                int result = cmd.ExecuteNonQuery();

Does that look correct?

I dont want the user to add a LocationID, I want that to be done (and it is when locations are entered manually) automatically.

[petevick]

Make locationID an identity field, and you don't need it in your list or values.
your connection string looks totally wrong - your device knows nothing about the c:\ drive - I thought we had already been over this ground.

We seem to have switched into c# too.

Something like...

SqlCeCommand  cmd = new SqlCeCommand()
                cmd.CommandText = "INSERT INTO LocationTable (LocationName) VALUES(@LocationName")
               cmd.Parameters.AddWithValue("@LocationName", LocationName)
                
                result = cmd.ExecuteNonQuery()

[WythyRed]

LocationID is an identity field, so good, i can get rid of that.

Re the Db - I have now brought it within the project, as you suggested above.

[WythyRed]

When i copy that code in it tells me the following are not declared:

SqlCeCommand
cmd
LocationName
result

[gep13]

We seem to have switched into c# too.

My bad!! C# is my default, and I forgot this was a VB question

You will need an Imports Statement for System.Data.SqlServerCe. As for result, you will need to declare that as an integer. LocationName is your variable, so where does that come from?

Gary

[WythyRed]

Where do I add Imports System.Data.SqlServerCe to? Is it within the Form code ie:

Public Class Form9

Imports System.Data.SqlServerCe

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        SqlCeCommand(cmd = New SqlCeCommand())
            cmd.CommandText = "INSERT INTO LocationTable (LocationName) VALUES(@LocationName")
        cmd.Parameters.AddWithValue("@LocationName", LocationName)

        cmd.Connection =

        result = cmd.ExecuteNonQuery()
    End Sub
End Class

Same with declaring result?

LocationName is input by the user on the form. I need to take it from the form and add it to the Db, if I have understood your question correctly.

[gep13]

Hey,

Yip, add the Imports statement at the top of the code file, beside all the System Imports.

result can be declared in the scope of the button click event.

If LoationName is coming from the textbox on the form, all you willl need to do is TextBoxName.Text, where TextBoxName is the Name you have given to the TextBox.

Gary

[WythyRed]

This is a very stupid question, even by my standards, but how can i view the code where all the import statement are? The only code seem able to find is when i right click on a form and select show code, which only shows the code for buttons and general widgets on that form.

[WythyRed]

Ah, found it.

I've now got the following imports:

Imports System
Imports System.Reflection
Imports System.Runtime.InteropServices
Imports System.Data.SqlServerCe

But i'm still getting the following compile error:

name 'sqlcecommand' is not declared

&

name 'sqlcecommand' is not defined

[petevick]

Open your sql connection and then something like...

Dim cmd as new SQLCeCommand
cmd.CommandText = "INSERT INTO LocationTable (LocationName) VALUES(@LocationName")
cmd.Parameters.AddWithValue("@LocationName", LocationName)
cmd.connection = whatever you set up as your connection
 result = cmd.ExecuteNonQuery()

[WythyRed]

Open your sql connection and then something like...

Dim cmd as new SQLCeCommand
cmd.CommandText = "INSERT INTO LocationTable (LocationName) VALUES(@LocationName")
cmd.Parameters.AddWithValue("@LocationName", LocationName)
cmd.connection = whatever you set up as your connection
 result = cmd.ExecuteNonQuery()

Thanks very much. That cut the number of errors down from 11 to 4.

The errors i received are:

End of statement expected. - after... VALUES(@LocationName")

Name 'LocationName' is not declared - ("@LocationName", LocationName)

name 'result' not declared.

Also an error about the connection. Do you know where i can find out what i set up as the connection? Is it listed somewhere?

Ta again.

[petevick]

Quotes in wrong place

cmd.CommandText = "INSERT INTO LocationTable (LocationName) VALUES(@LocationName)"

Name 'LocationName' is not declared - ("@LocationName", LocationName)

That is the name of your text box containing location name

Try

Dim result as integer = cmd.ExecuteNonQuery()

Only you can tell us what you set up as the connection

Presumably something like

Dim sqlCeConn as new sqlceconnection
sqlceconn.connectionstring = "Data Source=\My Documents\MyBase.sdf"
sqlceConn.open()

[WythyRed]

Top man, that sorted all the errors apart from the connection string.

I thought by bringing the .sdf into the project that it would know where it is? It currently resides on my hard drive in the following folder:

C:\Documents and Settings\Administrator\My Documents\Visual Studio 2008\Projects

But as you said, the device knows nothing about my c:\ drive - so do i have to put the .sdf onto the emulator manually?

[petevick]

add it to your project, and set it to 'copy if newer', and it will then copy to your deployment folder.

your connection string will then be similar to
\Program Files\MyProgram\mybase.sdf

It will be in the same folder as your executable

[WythyRed]

add it to your project, and set it to 'copy if newer', and it will then copy to your deployment folder.

Yep, done that.

your connection string will then be similar to
\Program Files\MyProgram\mybase.sdf

It will be in the same folder as your executable

Documents and Settings\Administrator\My Documents\Visual Studio 2008\Projects\SmartDeviceProject2\SmartDeviceProject2\sdf53.sdf

[petevick]

Nope - it will be on your device or emulator - or should be.

Use file explorer on the device/emulator and look in \program file\the name of your program

You seem to be confused by the difference between the device/emulator and your desktop PC.

[WythyRed]

I've looked on the emulator and the only thing in \program files are:

\connections\empty
\Windows media player
\Microsoft Visual Studio 2008\smart devices\sdk\sql server\mobile\v2.0\wce400\armv4 - contains 2 cab files
\microsoft sql server compact edition\v3.5\devices\wce500\armv4i - contains 3 cab files.

[petevick]

Have you deployed your program to the emulator?

I have never seen the paths below on an emulator.

\Microsoft Visual Studio 2008\smart devices\sdk\sql server\mobile\v2.0\wce400\armv4 - contains 2 cab files
\microsoft sql server compact edition\v3.5\devices\wce500\armv4i - contains 3 cab files.


You sure you are running the file explorer from within the emulator?

[WythyRed]

By deploy do you mean run it? I cant at the moment because it wont compile.

I put those cab files on the emulator myself, and i used the file structure as is on my hard drive.