[RESOLVED] VB Crash course needed

[gep13]

Hey,

Why won't the application compile? Just because of the connection string?

If that is the case, just set the connection string to an empty string, and then run the application (hit the green play button) and your application will be deployed to the built in emulator. Once you have done that, browse the file system to find out where the application has been deployed to, and from there you should be able to modify the connection string.

This is why I suggested that you store the connection string in a configuration file, that way you don't have to re-compile the application just to change one setting.

Gary

[WythyRed]

\Program Files\smartdeviceproject2\sdf35

[gep13]

Ok, so that is where the Database is stored on the Emulator, so from there, you should be able to build up the correct connection string, which should take the following form:

http://connectionstrings.com/sql-server-2005-ce

Where in your case, Data Source, is going to be something like sdf35\database.sdf.

Here I am assuming sdf35 is a folder, and the sdf file lives in there, correct?

Gary

[WythyRed]

Right i've sorted the connection. My code now looks like this:

Dim cmd As New SqlCeCommand
        cmd.CommandText = "INSERT INTO LocationTable (LocationName) VALUES(@LocationName)"
        cmd.Parameters.AddWithValue("@LocationName", TextBox1)
        Dim sqlCeConn As New SqlCeConnection
        sqlCeConn.ConnectionString = "Data Source=\Program Files\smartdeviceproject2\sdf35.sdf"
        sqlCeConn.Open()
        Dim result As Integer = cmd.ExecuteNonQuery()

When i try and add a location and hit the 'add location' button i get the following error pointing to the line in bold:

No mapping exists from DbType System.Windows.Forms.TextBox to a known SqlCeType.

[WythyRed]

Ok, so that is where the Database is stored on the Emulator, so from there, you should be able to build up the correct connection string, which should take the following form:

http://connectionstrings.com/sql-server-2005-ce

Where in your case, Data Source, is going to be something like sdf35\database.sdf.

Here I am assuming sdf35 is a folder, and the sdf file lives in there, correct?

Gary

No, the file is called sdf35.sdf

[gep13]

That is because you are trying to pass the entire TextBox as a parameter, that is not what you want to do, you only want to pass the text within that TextBox as the parameter, i.e.

cmd.Parameters.AddWithValue("@LocationName", TextBox1.Text)

Gary

[gep13]

No, the file is called sdf35.sdf

Ok, so looks like you have that part sorted, now try what I suggested in my previous post.

Gary

[WythyRed]

That is because you are trying to pass the entire TextBox as a parameter, that is not what you want to do, you only want to pass the text within that TextBox as the parameter, i.e.

cmd.Parameters.AddWithValue("@LocationName", TextBox1.Text)

Gary

Yep that worked and got past that error and now i'm getting an error on the last line:

Dim result As Integer = cmd.ExecuteNonQuery()

The error is:

ExecuteNonQuery: Connection property has not been initialized.

If we can sort this one it should work.

[gep13]

Hey,

Visual Studio is trying to help you here, which quite a descriptive error message, i.e. the Connection Property has not been set.

If you look back at my post #101, you can see that the Command Class has a Connection Property, you can find out more about this here:

http://msdn.microsoft.com/en-us/libr...onnection.aspx

What you are missing is the following (notice the bold text):

Dim cmd As New SqlCeCommand
        cmd.CommandText = "INSERT INTO LocationTable (LocationName) VALUES(@LocationName)"
        cmd.Parameters.AddWithValue("@LocationName", TextBox1.Text)
        Dim sqlCeConn As New SqlCeConnection
        sqlCeConn.ConnectionString = "Data Source=\Program Files\smartdeviceproject2\sdf35.sdf"
        cmd.Connection = sqlCeConn
        sqlCeConn.Open()
        Dim result As Integer = cmd.ExecuteNonQuery()

Hope that works!!

Gary

[WythyRed]

It worked, i think. I now cant look at the db within VS because apparently "The path is not of a legal form.".

Eitherway, i'm going to bed as i'm shattered. Ta for all your help today. I'm sure i'll be back tomorrow...

[petevick]

You can't look at a database on a device/emulator with VS - you will need to copy it to your desktop to look at it, or use the Query application on the device/emulator.

I would also suggest opening your connection at the start of your program, and closing it a the end - opening for every insert will not help performance, and not closing it in the look will burn up memory.

[WythyRed]

How would i do that? Move the following code to the start of the code for the program?

 cmd.Connection = sqlCeConn
        sqlCeConn.Open()

Anyway, that aspect of the program is now working, and since then I have removed the image fields from both tables in my database (because they're a pain that's not necessary) and now, when I move from the 'add location' page in the program to the 'add object' page it crashes as it tries to load this page with the error message saying:

"The column name is not valid. [ Node name (if any) = ,Column name = Picture ]"

Pointing at this line of code:

Dim returnValue As Integer = Me.Adapter.Fill(dataTable)

Obviously, for some reason, it's looking for the picture field I deleted. Do I have to update the dataset or something?

Finally, I need to polish up the insert query designed above so that, once the button has been clicked and it adds the location to the DB it deletes the text from the text box and I also need it to throw up an error message if the user enters a location name that's already in use (it's a unique field in the DB) rather than cut back to VS as it does currently.

[petevick]

Yes - move your connection open to the beginning of the code, and remember to close it at the end

Have you re-copied your database to the device after deleting your field?

Do a select before the insert, to ensure the record already exists, and throw up a message beforehand.

Alternately, you could catch the error, and put your message there.

[WythyRed]

Should I move

 cmd.Connection = sqlCeConn
        sqlCeConn.Open()

in with the imports? IE:

Imports System
Imports System.Reflection
Imports System.Runtime.InteropServices
Imports System.Data.SqlServerCe
cmd.Connection = sqlCeConn
        sqlCeConn.Open()

If that's correct, then where should I stick

sqlCeConn.Close()

?

[gep13]

Hey,

If you try and do that you will get a compilation error as this is invalid. You can't put code operations at that level, it needs to be done within some event handler.

declare a private class member of Type SqlCeConnection at the top of your class, and then in the Main event of your program, create the new instance of it, and then open the connection.

Then, when you application is exiting, there should be an event handler for this, close the connection.

Gary

[WythyRed]

Anyway, that aspect of the program is now working, and since then I have removed the image fields from both tables in my database (because they're a pain that's not necessary) and now, when I move from the 'add location' page in the program to the 'add object' page it crashes as it tries to load this page with the error message saying:

"The column name is not valid. [ Node name (if any) = ,Column name = Picture ]"

Pointing at this line of code:

Dim returnValue As Integer = Me.Adapter.Fill(dataTable)

Obviously, for some reason, it's looking for the picture field I deleted. Do I have to update the dataset or something?

If I can just come back to this, I need to solve one problem at a time as I'm easily overloaded.

In response to the above reply 'have I re-copied my database to the device after deleting your field?' - Yes, I did and I've redone it just to be sure, however I'm still getting the same error when I try and navigate to the 'Add New Object' form.

[WythyRed]

I'm not sure what I've done, but when i go to compile the project now, and i've compiled it with no problems earlier this morning, i now get an error message that says:

Unable to start program '%CSIDL_PROGRAM_FILES%\SmartDeviceProject2\Smartdevice.exe'.

The data needed to complete this operation is not yet available.

Anyone any ideas what's happened there? I haven't changed anything I don't think.

Edit: Although the project runs when I start it from within the emulator.

[WythyRed]

The compile reports this:

warning MSB3247: Found conflicts between different versions of the same dependent assembly.
SmartDeviceProject2 -> C:\Documents and Settings\Administrator\My Documents\Visual Studio 2008\Projects\SmartDeviceProject2\SmartDeviceProject2\bin\Debug\SmartDeviceProject2.exe
Done building project "SmartDeviceProject2.vbproj".

It seems i have an .exe for my project in the same directory on the emulator as the .sdf. I don't know if I put it there by mistake when making sure that the latest version of the BD was on the emulator. Should i delete it?

[gep13]

Hey,

I am really not sure that I follow the problem that you are currently having.

Can you perhaps post again with exactly what you have done, and what the outcome is.

Perhaps with the aid of screenshots. I think a screen shot of your solution explorer might help to diagnose what is going on.

Gary

[petevick]

it sounds as though the project was possibly still running when you tried to compile it

This thread just seems to get longer and more confusing with many different questions.

When one is solved, wouldn't it be easier to mark it resolved and ask a whole new question?

[WythyRed]

Hey,

I am really not sure that I follow the problem that you are currently having.

Can you perhaps post again with exactly what you have done, and what the outcome is.

Perhaps with the aid of screenshots. I think a screen shot of your solution explorer might help to diagnose what is going on.

Gary

When I now try and run the project in VS2008 i get the error message:

The data necessary to complete this operation is not yet available.

I don't understand why it's doing that, because I don't remember changing anything and it used to deploy without problem.

[WythyRed]

it sounds as though the project was possibly still running when you tried to compile it

This thread just seems to get longer and more confusing with many different questions.

When one is solved, wouldn't it be easier to mark it resolved and ask a whole new question?

I don't think it was.

Maybe it would, however I've nearly completed what it is I need to do (touch wood, fingers crossed, etc), so I doubt there's much point now as the whole thing can hopefully be marked as resolved soon.

[petevick]

Is there any other message along with "The data necessary to complete this operation is not yet available"??

[WythyRed]

Unable to start program '%CSIDL_PROGRAM_FILES%\SmartDeviceProject2\Smartdevice.exe'.

The data needed to complete this operation is not yet available.

===============

The bizarre thing is that the project does actually run in the emulator despite the error message.

I think I have accidentally exported the project to the emulator, and when i try and deploy it from with VS it causes a conflict. I have tried to delete the project from the emulator, but it tells me it can not be done because there is a sharing violation.

[gep13]

Hey,

Are you saving the state of the emulator when you close it down? I seem to remember something about this in an earlier part of this thread, but can't remember when.

It might be worth reverting the emulator back to the default and trying again.

Gary

[WythyRed]

If I open the emulator and close it down, without saving the state, should it return to default? Or to the previous saved state?

[gep13]

Hey,

If I remember rightly, there is actually an option to revert the saved state of the emulator. Once you have the emulator open, hit the file menu, or whatever the top left menu is, and I think the menu option is in there.

Gary

[WythyRed]

Ta, a hard reset did the trick, although i had to reinstall the cab files.

[gep13]

Ah, that was it. You had already installed SQL Server onto the emulator, that is why you saved the state. You are going to want to create a setup and deployment project at some point that takes care of that installation for you.

Gary

[WythyRed]

I'll look at that when everything else is done.

What I need to do now is add a small line of code (TextBox1.Clear() or something similar) to the end of the following code so that after the button is pressed the text in the box is removed. I also need to add an event handler so that the same location name can't be entered twice, and if it is, to ensure it doesnt crash the system.

private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim cmd As New SqlCeCommand
        cmd.CommandText = "INSERT INTO LocationTable (LocationName) VALUES(@LocationName)"
        cmd.Parameters.AddWithValue("@LocationName", TextBox1.Text)
        Dim sqlCeConn As New SqlCeConnection
        sqlCeConn.ConnectionString = "Data Source=\Program Files\smartdeviceproject2\sdf35.sdf; Password=111084;"
        cmd.Connection = sqlCeConn
        sqlCeConn.Open()
        Dim result As Integer = cmd.ExecuteNonQuery()

    End Sub

[gep13]

Hey,

Have we not already discussed opening the connection as early as possible?!?! Why are you still opening it in the click event handler?!!?

As for your question, you have two options.

1) Enforce this rule at the database level. Set a constraint on the location column to ensure that it is unique. Then, when you try and add a row with the same location, it will cause an exception. In your code, you will need to handle this exception, and deal with it gracefully.

2) Before doing the insert query, do a select, based on the location name. If there are rows returned, don't do the insert, if not, then do the insert.

Also, TextBox.Clear() should have the desired result.

Gary

[WythyRed]

Hey,

Have we not already discussed opening the connection as early as possible?!?! Why are you still opening it in the click event handler?!!?

As for your question, you have two options.

1) Enforce this rule at the database level. Set a constraint on the location column to ensure that it is unique. Then, when you try and add a row with the same location, it will cause an exception. In your code, you will need to handle this exception, and deal with it gracefully.

2) Before doing the insert query, do a select, based on the location name. If there are rows returned, don't do the insert, if not, then do the insert.

Also, TextBox.Clear() should have the desired result.

Gary

I've not changed it because at the moment the system works, and that's all I'm bothered about. I have a rapidly approaching deadline and I'm willing to sacrifice performance aslong as the system works.

[WythyRed]

Also, TextBox.Clear() should have the desired result.

''Clear' is not a member of 'System.Windows.Forms.TextBox'.

I've had a look down the list of possible code, but stands out as being the alternative to Clear().

[WythyRed]

After a bit of googling it looks like the answer is going to be something along the lines of:

TextBox1 = String.Empty

[WythyRed]

Shabba.

TextBox1.Text = ""

[gep13]

I don't have the IDE up in front of me, but if .Clear() isn't there, then you might just have to do:

TextBox1.Text = string.Empty;

Not all methods that exist in the full framework exist in the compact one.

Gary

[gep13]

Ah, you beat me to it

I got distracted before posting.

[petevick]

I've not changed it because at the moment the system works, and that's all I'm bothered about. I have a rapidly approaching deadline and I'm willing to sacrifice performance aslong as the system works.

Shudders

[gep13]

Yeah, I had thought the same, but wasn't going to mention it

[WythyRed]

Anyway, i've finished the system now and it works to an acceptable level.

Thanks for all your help.