Save field without displaying it on the VB form


  • I have a VB 2008 windows application that I'm working on; the data are stored in SQL. I have a lot of fields that user will need to fill out. Some fields that not displayed on the form are actual combination of few filled out fields. For example - user types in first name, middle name and last name in 3 separate boxes on the form and I'd like to combine those and assign it to the "FullName" field. I know it's duplicating the data but this FullName field would need to be merged into report as is in a different program. Another example - user will pick a data in combo box that is binded to a different table, but I need it to auto-populate 3 fields in the main form (those fields are not displayed). I know that I can actually add all those hidden fields on the form and assign calculated values based on user input and then everything is saved with built-in "Update" command. I was wondering if there is a way to specify those few calculated values as parameters and still use "Update" command without actually typing long Update command. I have over 400 fields - very lengthy command.. Hope it makes sense. Thanks!
    Monday, November 23, 2009 9:48 PM


  • Hey buddy I'm sorry about the delay I had a layover and just got in a few hours ago. Please click on the url below to be redirected to the sample app I  have compiled for you. I hope this helps which I think it will answer every bit of your questions and some. Thanks Charlie

    Download Here:
    Wednesday, November 25, 2009 8:24 AM

All replies

  • There are several way of going about this but I think I know what your looking for here.  Solution #1: Keep the 4 text boxes binded to the sql data-source (e.g. Dataset, TableAdapter, and etc) set the fourth textboxes binding to the full name data field. Once you have done this place the textbox out of the way and set the property Visible = False. Then add an event in the last of the (3) to insert a combination of all three values into the fourth textbox (e.g. txtFirstName.Text + " " + txtMiddle.Text + " " + txtLastName.Text). One last note on this method would be to add an event handler OnMouseLeave on the LastName textbox and this will be where the fourth textbox "FullName" gets it's full name value set.

    Solution #2: The proper way of doing this is to have your normal (3) textboxes working and when you need the full name pull each value from the database or keep the fullname field in the data table and use string combination to combine the (3) values into a single full name. If you go that route you will most likely have to implement custom save method using sqlclient namespace. Let me know if this helps you out. Thanks Charlie
    Tuesday, November 24, 2009 3:48 AM
  • I briefly read over this and i am not sure i follow 100%.  But, i thought i would mention something that i have used and if it is something that will help you then good, if not then disregard.

    I have a particular client application which has several hundred controls the user needs to input data into.  large sections have about 30 or so checkboxes.  0 to all of these may be checked depending.  this is each section so there is a lot to hanlde for the entire record.  so this would take a lengthy routine or sql statement to write.

    What i found was easiest for me was to loop throgh the checkboxes and combine a comma delimeted string consisting of the checked checkbox's names.  this way only the checked checkboxes are stored and the names allow me to split the string and check the controls by name in a loop.  so basically i can handle 30 checkboxes with a single field, 1 small loop to build the delimeted string, a single parameter for my insert or update command, and 1 small loop to read the names when the record is loaded.

    using this approach i was able to shorten my column from about 250 or so down to about 70.  i could probably combine more values into single fields but i try not to handle everything with strings.

    well, hope this helps.  might give you some ideas to slim some things down.
    FREE DEVELOPER TOOLS, CODE & PROJECTS at Database Code Generator and Tutorial
    Tuesday, November 24, 2009 4:18 AM
  • Use array to those field (textbox, checkbox, etc.). Then use looping to update command for every array ;)
    Tuesday, November 24, 2009 7:04 AM
  • Thank you for the suggestions. Solution 1 - is what I currently have. I wanted to see if there is a more efficient way to do it. As I mentioned - I have over 400 fields and trying not to make the form looking too busy (if I have a lot of hidden fields - hard to manage design). I have TabControl with 10 tabs already.. I was thinking about implementing solution 2 - to write the SQL command to save only calculated fields. Again - was trying to find an alternative.
    It's seems that there is got to be a way to setup the parameters with calculated fields, using existing parameters names. For example - built-in Update command already has @FullName parameter but I don't know how to assign this parameter calculated value prior to calling update command.
    Thanks again!
    Tuesday, November 24, 2009 1:21 PM
  • Thank you, Jeff. I actually have more fields on purpose. I'd need to merge those fields later into existing PDF and willl have to have all information stored in specific fields to setup the merge..
    Tuesday, November 24, 2009 1:23 PM
  • Me again. I've been trying to set the parameters prior to saving it and it still doesn't work. Please see the code below. What am I missing? I don't get any errors, but when I add a quick watch for the value of @FacTaxID - nothing showing up and no value inserted in the field. Please advise! FYI - I don't have FacTaxID field on the form, the field Me.txtFacTaxID is binded to related table.
    Private Sub TblAdmissDataEntryBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TblAdmissDataEntryBindingNavigatorSaveItem.Click
    Me.Cursor = Cursors.WaitCursor
           TblAdmissDataEntryTableAdapter.Adapter.UpdateCommand.Parameters("@FacTaxID").Value = Me.txtFacTaxID.Text
            TblAdmissDataEntryTableAdapter.Adapter.UpdateCommand.Parameters("@LimitedPartnershipName").Value = Me.txtFacLegalName.Text
    Me.Cursor = Cursors.Default
    end sub
    Tuesday, November 24, 2009 3:38 PM
  • Hey buddy I'm getting on a flight right now but I built you a sample app I can send you or post it for download and there is a method I just figured out today waiting at the airport that will blow your sox's off and it's only 2 lines of code. I will post around 9PM tonight sorry about the delays. Thanks Charlie
    Tuesday, November 24, 2009 9:55 PM
  • I appreciate it!
    Tuesday, November 24, 2009 9:57 PM
  • I appreciate it!

    did it solve your problem?
    If you propose as answered, so we can move on...
    Don't judge me, just Upgrade me. Thanks!
    Tuesday, November 24, 2009 10:06 PM
  • Hey buddy I'm sorry about the delay I had a layover and just got in a few hours ago. Please click on the url below to be redirected to the sample app I  have compiled for you. I hope this helps which I think it will answer every bit of your questions and some. Thanks Charlie

    Download Here:
    Wednesday, November 25, 2009 8:24 AM
  • Thank you, Charlie. I really appreciate you going into such length as designing sample application. I downloaded the project and reviewed the code and have a few questions. The Linq string pulls different full name than the one you pull from SQL - so Linq doesn't match the split version of the name. Was it intentional?
    About parameters - I wanted to use the built-in UpdateCommand that gets generated when you setup Data source connection in the TableAdapter and find a way to manipulate the existing parameters in that command. I see that in your project you didn't generate UpdateCommand and instead built your own. Is it possible to manipulate existing parameters of built in command? If not - then I'll still use built-in Update string for 400+ fields and setup separate SQL command to update Full name and other calculated fields.
    Thank you so much for your help!
    If it's not too much to ask - can you also take a look at another problem I'm having?
    Am I the only one experiencing it??
    Thanks again, Alla
    Wednesday, November 25, 2009 1:56 PM
  • Yes the LINQ needs to be tweaked a little I was just showing you a brief example to get you started. About the update command you can open the dataset view and view the code behind and the method I implemented will update the database. To use just click the little yellow + sign on the toolbar and when your ready to save the data click the button with a default image. It is the last button on the toolbar. It will save the new row including the fullname field behind the scenes in less than 2 lines of code. Thanks Charlie

    Wednesday, November 25, 2009 7:17 PM
  • Oh well. I guess I'll use a separate SQL update code for calculated fields. Unless someone will tell me why this line below doesn't work?!
    TblAdmissDataEntryTableAdapter.Adapter.UpdateCommand.Parameters("@FacTaxID").Value = Me.txtFacTaxID.Text
    Thanks again, Alla
    Wednesday, November 25, 2009 7:56 PM
  • In the project I sent you it shows you how to update the database and change the fullname on update and insert.  Are you using a strongly typed dataset like the one in the example I sent you? Thanks Charlie
    Wednesday, November 25, 2009 8:33 PM
  • Sorry, Charlie - I think there is some miscommunication here. I have setup connection to Data source - SQL database and using all automatically generated Select, Update, Insert, Delete commands - from table adapters. I understand that the method you suggested was to spell out each field and parameter in Insert/Update commands. I wanted to avoid it because of 400+ fields in those forms. It's much easier just to call it with 1 line of code: Me.TableAdapterManager.UpdateAll(Me.AdmPacketMainDataSet).
    I guess I'm trying to find an easier way. If I check the code behing UpdateCommand in Dataset designer - I can see that all parameters already there and wanted to find a way to modify those parameters prior to calling Update command (see example code from yesterday). Unfortunately that code doesn't give me any errors but doesn't update calculated fields either. If that method is not possible - I'll add the separate Update code (similar to the one in your sample) that I'll run right after built-in Insert for new records or UpdateCommand for existing. Hope I'm making sense. 
    About strongly typed dataset - I learned VB practically by myself (books + some on-line classes) and have enough basic knowledge to create applications, but don't know technical terms to answer your question, sorry..
    I really appreciate your assistance! Sincerely, Alla
    Wednesday, November 25, 2009 9:08 PM
  • I guess that the answer is no - not strongly typed dataset. I see that your dataset has an extention sdf, mine is dbo.

    Wednesday, November 25, 2009 9:13 PM
  • Well I understand your frustration and writing software with minimal knowledge can really be a pain but you can pat yourself on your back for the progress you have already made. So in your project did you use the datasource add-in in visual studio to create your dataset or did you create it manually? What exactly is wrong with the generated update method? Is it not updating the data correctly? Let me know exactly what you need help with and I can put you on the right track. Thanks Charlie

    Wednesday, November 25, 2009 9:55 PM
  • Thank you. I used a VS wizard to add a connection to SQL. The Update command works for all the fields displayed on the form and binded to the table fields. The problem is with the calculated fields that I don't display on the form or with the fields that are binded to different table. I was hoping that there was a way to manipulate the built in UpdateCommand parameters that are = calculated fields (see my code from yesterday - @FacTaxID is a parameter in the main table, but the value is stored in different table and displayed on the form). That code didn't update those 2 fields specified in the code. I can certainly write a separate SQL update code and use it. The other problem I have with date fields - see the reference link from earlier today.
    I am taking 2 days off - so I might not reply immediately. Have a great Thanksgiving! Thanks, Alla
    Wednesday, November 25, 2009 10:05 PM
  • That's great. That is what I have been trying to explain to you. In the sample application I sent you Open the dataset and double click on the accounts table adapter. Once the code view is showing look at the method I have provided. This will modify the data before it is inserted or updated and it is a very simple but professional way of completing something like this. Thanks Charlie

    Wednesday, November 25, 2009 10:22 PM
  • Im guessing you have resolved this issue? Thanks Charlie
    Saturday, November 28, 2009 6:12 AM