ClickOnce deployment woe resolved...this time

In my case, which is probably not yours but it could be, unused references were the culprit. I removed them and all is golden now. At least until the next problem.

Here's the situation...

Two projects. One deploys fine with ClickOnce, one does not. They are identical as far as the publishing settings so I can't find what's wrong. I glean from the long error message log something about not being able to read the manifest and parse errors.

I'm on my own with this project so I search the internet for help. I'm not alone as it turns out a lot of people have ClickOnce woes and it appears there are no simple answers. All kinds of solutions and workarounds are suggested, some of which I try and some I know aren't the issue and won't help. There are many suggestions to sign the certificate, but unsigned certificates are allowed, and the "good" project is unsigned and deployed just fine. So I continue to search for answers.

I find a blog that suggests duplicate .dll files are at fault. I go to the "bad" project's property page and look at the resources but don't see any duplicates. I select the "Unused References" button and am surprised to see three are listed. Judging from their names they don't appear to be unused. I check the "good" project and there are no unused references there. Hmmm. Interesting. So I back up my project, remove the supposedly unused references and publish again. I hold my breath, waiting for the same error message to appear. And it doesn't. It runs! Hooray!

I choose nothing...or maybe it should be empty.

I've encountered some times when exceptions are thrown because of a null value in the database. For my current project, it doesn't matter if a column contains a null value. In fact, there are many null values because much of the data that can be entered is optional. Whenever I encountered one of these peskly null exceptions, I tried to handle it in code, but then I stumbled upon something much easier. Much easier.

The colunns of datatables are set to throw an exception for null values when the column is read - exactly under what conditions I'm not quite sure but I have run into them. But you don't have to live with that. You can choose to return a value "Empty"  or "Nothing". I chose "Nothing" and my (coding) life was suddenly made easier. Except...when I tried to read values from the datatable and apply them as a datasource for a combobox, my combobox wasn't being populated. It was very strange because I was populating four comboboxes and one of them was working but the comboboxes for the other three were blank. But when I chose "Empty" instead of "Nothing", my comboboxes worked. So I guess there is a difference. 

I stumbled on this by looking at the properties of the datatable column. Go to the Edit DataSet with Designer and then go to a column in one of your tables and select it. In the Properties window you can see there is a selection for NullValue.

Sometimes it's a very simple solution

When you work with a datagridview you deal with rows and rowindex and cells and if it's a bound datagridview you have to remember to get the name of the column correct or it won't work...blah, blah, blah.

And sometimes all you want is to not have the datagridview be displayed with the first row selected (default is to highlight it in blue), which is really quite simple but for some reason I keep forgetting it:

datagridview.clearselection()

That's it.

Integer vs. string

I think, but I may be wrong, that when you set up your database and define the datatypes that it's best to choose string (varchar) instead of integer unless the value truly needs to be treated as an integer. I'm just guessing though. My reason for this thought is that I set one of my fields as an integer because a user would enter numbers into it. But in reality the numbers were just a label and I probably should have made that field a varchar. I say this because those values are used as the choices for a combobox and I can't include a blank value with the number...because blank is not an integer. I've added "zero" to the list but I don't like it. I want blank, so I'll have to figure something else out.

Null datetime

I'm working on a project right now that is a user interface to a SQL database. Pretty standard stuff, I think. In creating my user forms, I've run into some issues where I want the user to be able to do something that I I've been able to do on other forms and that they're likely going to want to do, but I can't figure out how to do it! But with patience and a lot of searching, I made it work. This first post, and actually the reason for this blog, is all because of null dates.

How many forms do you encounter that have a date field but you want or need the option to leave it blank? My database table has a datetime column and my form has a datetimepicker on it. Well, you can't just "blank out" a datetimepicker. I didn't want to confuse the user with minimum dates, fake dates, or even the grayed out date in the datetimepicker when the checkbox version is used. I didn't want to use a plain textbox because I like the datetimepicker calendar. What I wanted was the datetimepicker box to be blank when the user first opens the form and if he/she doesn't enter anything then it should stay blank. And if the user puts a date in and then wants it gone, there should be a way to get rid of it. I didn't realize this would be so difficult, but I made it work. Basically, I make the datetimepicker custom format "blank" when I need it to be blank and I updated the database with a null value for the datetime field when the user "clears out" the datetime field, using a menu option. Here are the specifics:

1. Make the datetimepicker box blank.

If it's a new form that doesn't show any data, I initialize the datetimepicker with a blank custom format:

MyDateTimePicker.CustomFormat = " "      
MyDateTimePicker.Format = DateTimePickerFormat.Custom

If the form is displaying data from the database, then I find out if the value is null (nothing) and if it is, I give it the blank custom format:

If TypeOf (Me.MyTableBindingSource.Current("DateTime")) Is DBNull Then
MyDateTimePicker.CustomFormat = " "      
MyDateTimePicker.Format = DateTimePickerFormat.Custom
End If

2. Use the ValueChanged event for the datetimepicker box to change the format when a user enters something in the box.

Private Sub MyDateTimePicker_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyDateTimePicker.ValueChanged

MyDateTimePicker.CustomFormat = "yyyy/MM/dd HH:mm:ss"

End Sub

3. Allow the user to enter a value and then clear it. For this I used a context menu strip. If the user right clicks on the box, a "Clear Date" menu item appears and they use this to clear the date. But first I needed to create a SQL query for the dataset where I only update the datetime. The SheetID is the primary key in the table so I know I'm choosing the right one to update.

UPDATE (MyTable) SET (DateTime) = null FROM MyTable WHERE (SheetID = @SheetID)

4. The event for the Clear Date context menu strip item updates the table with a null value for the date, calls another query to fill the sheet and then applies the same blank custom format to the datetimepicker box so that it appears blank.

Private Sub ClearDateToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ClearDateToolStripMenuItem.Click

Me.MyTableTableAdapter.UpdateNullDateTime(SheetID)

MyDateTimePicker.CustomFormat = " "
MyDateTimePicker.Format = DateTimePickerFormat.Custom

End Sub

5. One other feature I implemented was to allow the user to generate new sheets from the currently displayed sheet. For example, say you have a sheet that contains information for a product ID and you want the more sheets created for different product IDs but with the same information. I accomplished this by inserting new rows into the database for each of the new sheets, using a SQL insert query for the tableadapter with variables for all of the information I wanted to capture. But I found that when the current sheet had a null datetime field the value was entered into the new sheets as 01/01/1900, not null. The way I got around this was to read the value for the datetime directly from the datatable for the currently displayed form and if it was null, I used "nothing" instead of the datetime variable in the insert query. By the way, an error occurred when I tried to just make the variable "copydate" equal to "nothing" so that's why I have to call the update query in an "if" statement.

In this example code the variables copyinfo, copyinfo2, and copydate are set to information on the form that I want to insert into the new record:

If TypeOf (Me.MyTableBindingSource.Current("DateTime")) Is DBNull Then

me.MyTableTableAdapter.insertCopyofSheet(newID, copyinfo, nothing, copyinfo2)

else

me.MyTableTableAdapter.insertCopyofSheet(newID, copyinfo, copydate, copyinfo2)

End If

The first post

Hello!

It was hard to come up with a title for this blog. I already have a blog, called Monkeyroom, but it's about sewing and knitting and gardening and travel and other non-technical stuff. I've been learning how to program in vb.net and wanted a place to share the code and the "tricks" I've learned. I know...why not C# or maybe even some other language? There are reasons. Anyway, I figured I better start a separate blog so as not to alienate my Monkeyroom followers.

I have a topic for the next post but I'm not ready to post it just yet. I'll give you a hint though - it's about nothing. ;-)