Add new record on form load; Show correct row ID on form
Add new record on form load; Show correct row ID on form
Add new record on form load; Show correct row ID on form
Add new record on form load; Show correct row ID on form
Add new record on form load; Show correct row ID on form
Add new record on form load; Show correct row ID on form Add new record on form load; Show correct row ID on form Add new record on form load; Show correct row ID on form Add new record on form load; Show correct row ID on form Add new record on form load; Show correct row ID on form Add new record on form load; Show correct row ID on form Add new record on form load; Show correct row ID on form Add new record on form load; Show correct row ID on form
Add new record on form load; Show correct row ID on form Add new record on form load; Show correct row ID on form
Add new record on form load; Show correct row ID on form
Go Back  Xtreme Visual Basic Talk > > > Add new record on form load; Show correct row ID on form


Reply
 
Thread Tools Display Modes
  #1  
Old 05-05-2008, 12:16 PM
147 147 is offline
Newcomer
 
Join Date: May 2008
Location: Eastern Shore
Posts: 20
Default Add new record on form load; Show correct row ID on form


Hi,

I'm new to this board, and VB in general, so I apologize in advance if this is a dumb question. I briefly searched through the archives but I couldn't find a a specific answer to my problem (which is why I assume its a dumb one )

In an attempt to learn VB I'm trying to build a database project for a friend's business. This db will be used to track customer information, predominantly for targeted marketing purposes. So far, the development has been slow (I'm still trying to adapt from VBA to VB), but its all starting to make sense now.

I'm using two different forms for adding new customers and reviewing the information of existing customers, mainly because the database will grow by about 2,500 customers per year and we wanted to be able to search the records without returning every record when the forms load.

Most of this works so far, but I'm having trouble with two issues. When I open the new customer entry form, I want the form to automatically begin entering data without clicking the "Add New" button.

The second problem involves the row ID from the database. It keep showing up as "-1", "-2" etc. The row ID records properly in the table, so no big deal. I have read that this problem is a bug in VB and that its nothing to worry about. However, down the road I will need to access the correct row ID from this same form in order to print out a report for the customer (as needed).

For what its worth here is the code I have so far:

Code:
    'Update current record when the form closes
     Private Sub frmNewCustomer_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
        Me.TblCustomersBindingSource.EndEdit()
        Me.TblCustomersTableAdapter.Update(Me.dsNewCustomer.tblCustomers)
    End Sub

    'Set default values for datetimepicker
    Private Sub BindingNavigatorAddNewItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BindingNavigatorAddNewItem.Click
        dsNewCustomer.Tables("tblCustomers").Columns("CustomerSince").DefaultValue = Date.Today
        dsNewCustomer.Tables("tblCustomers").Columns("WaiverDate").DefaultValue = Date.Today
    End Sub

    'Begin recording new record when form opens
    Private Sub frmNewCustomer_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Call BindingNavigatorAddNewItem(sender, e) 'This doesn't work- still trying to figure out why    
     End Sub
Any help would be appreciated with either of these two concerns!
Reply With Quote
  #2  
Old 05-05-2008, 12:27 PM
MKoslof's Avatar
MKoslofAdd new record on form load; Show correct row ID on form MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

Quote:
Most of this works so far, but I'm having trouble with two issues. When I open the new customer entry form, I want the form to automatically begin entering data without clicking the "Add New" button.
Do you have text boxes or DateTime pickers at the UI level? If so, why not just set these values at the control level? Set the .Text or .Value properties to be what you want on load. Setting the default value on the DataSet table isn't going to render a new record with default values for particular fields, just set this at the UI leve and when the Add button is clicked pass the values down into the Update

Regarding the Row Id, I'm not sure I follow what the issue is. Are you returning the ID from the database? If so, can't you store that value in a hidden column and reference it when needed? What I mean is, say you have an Identity column on your tables, just return that value in your stored procedure or the Data Fetch of the BindingSource, then if you don't want it visibly displayed hide the column in the grid or just make the column read only and display it etc
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #3  
Old 05-05-2008, 12:57 PM
147 147 is offline
Newcomer
 
Join Date: May 2008
Location: Eastern Shore
Posts: 20
Default

Quote:
Originally Posted by MKoslof View Post
Do you have text boxes or DateTime pickers at the UI level? If so, why not just set these values at the control level? Set the .Text or .Value properties to be what you want on load. Setting the default value on the DataSet table isn't going to render a new record with default values for particular fields, just set this at the UI leve and when the Add button is clicked pass the values down into the Update
This is kindof another problem and I didn't want to overload this post with too much info, but I'll explain why this is here. I am using the datetimepicker for these fields (although I am considering just going to masked text boxes since those are faster for data entry anyway). When I set the "value" property and try to update the row, I get an error that the database doesn't allow Null values for these dates (which is true). I never could figure out why the DTP was not sending the "value" property back to the database...or dataset...or table adapter...or whatever... Like I mentioned, I'm probably going to go with a masked input box for these instead, which will hopefully resolve that problem.

Quote:
Originally Posted by MKoslof View Post
Regarding the Row Id, I'm not sure I follow what the issue is. Are you returning the ID from the database? If so, can't you store that value in a hidden column and reference it when needed? What I mean is, say you have an Identity column on your tables, just return that value in your stored procedure or the Data Fetch of the BindingSource, then if you don't want it visibly displayed hide the column in the grid or just make the column read only and display it etc
Sorry, I wasn't clear on this. When I begin entered a new record, the field txtCustID (bound to the identity column in the table) does not show what the current record number should be for the new row (is "identity" the right word?). Instead, it returns "-1" for the first record I enter, "-2" for the next and so on. If I close the form and reopen to begin entering new records, the count resets to "-1". Despite what is shown in this field, though, the column on each row of the table is properly numbered, sequentially incrementing by "1" for each new row. So the table data is accurate, but I am unable to use the correct row identity from the table directly from this form.
Reply With Quote
  #4  
Old 05-05-2008, 03:52 PM
MKoslof's Avatar
MKoslofAdd new record on form load; Show correct row ID on form MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

Ok, we need to take a few steps back here. I think we need to first understand a few things.

1) Does your table have identity columns? Are there primary keys? What is your underlying data structure/tables in play

2) How are you setting up your TableAdapters and Data controls? What queries are you binding, how did you set everything up in general. The small piece of code you have provided isn't giving me enough to go off of

3) Read some tutorials on database design AND ADO.net. I highly suggest not using the wizard controls, not until you at least understand how to invoke SQLCLient and OLEDB connection, command and Reader objects.

I think we need to walk before we can run.
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #5  
Old 05-05-2008, 06:49 PM
sgm sgm is offline
Junior Contributor
 
Join Date: Aug 2003
Location: AlBayda, Libya
Posts: 305
Default

from what you've written so far I'll try to answer you with those assumptions:
1- you are using VB 2008.
2- you've used the wizards to build your dataset and bound your controls by dragging items from the datasource window to your form, also you have used an autoIncrement field for your CustID and have set it as your primary key.

assuming that both assumptions are right here is my contribution to this post:-
For the custID field the default behavior for your dataset and tableAdapter for any AutoIncrement field is to set negative values so they won't conflict with other users entry from another instance of your application, this behavior is a default for .NET because ADO .NET is built on disconnected model that is there is no actual permanent connection between the actual data "Access db or SQL Server db" and the data present within the application developed using ADO .NET 2.0. So you will get negative values until you update that's when you'll get the actual value from the database, in this case you can either disable the control so the user won't alter it by accident or remove it all together since it does not represent the actual number of the record i.e an ID of 32 does not mean that you have 32 records.
As for your datepicker doesn't pass the value of the date, I remember this happening to me a while ago in my case the datepicker won't pass the value if it was selected using code and only passes it when it was selected by mouse click, So I've changed the control and used textbox instead. strange enough the project that i'm using now the datepicker works fine?!
I hope this would help you
good luck
Reply With Quote
  #6  
Old 05-05-2008, 08:28 PM
147 147 is offline
Newcomer
 
Join Date: May 2008
Location: Eastern Shore
Posts: 20
Default

Quote:
Originally Posted by MKoslof View Post
Ok, we need to take a few steps back here. I think we need to first understand a few things.

1) Does your table have identity columns...
2) How are you setting up...
3) Read some tutorials...
1: Yes- CustID is the identity column / primary key. I'm using SQL Server 2005 CE. I'll admit that this is my first experience with SQL Server of any flavor.

2: As assumed (correctly) by sgm, I used the wizard to set up the TableAdapters and data controls.

The dataset returns just about everything in my "tblCustomers" table, as well as everything from a related table, "tblCustomerVisits". The purpose being is that I'm trying to add a new customer with this form (BTW, I still don't know how to do that ), which will include all of their personal information. The second table adapter will -hopefully- allow me to record this customer's first visit to the business from this form as well.

3: Please bear with me, but I'm confused here. The MSDN documentation is a little ...ack... dry, not to mention difficult to follow. Whenever I saw references to SQLClient and OLEDB I've assumed they were associated with SQL Server, not CE. I'll study them more closely now.

Also, I wasn't aware that ADO.NET was the basis of the database material. I have access to a MS Press book on ADO.NET, but it was published in 2003 or 2004. Would this be any use to me here, or should I look for a more current resource?

Quote:
Originally Posted by MKoslof View Post
I think we need to walk before we can run.
I agree- my nose is starting to hurt...

SGM: Thanks very much for your input, especially for your experience with datepicker. Your problem was the same as mine- the default value wouldn't pass; only a value selected by mouse click was considered valid. It's a great visual control, but ultimately not too useful to me in that format. Like you, I chose to convert the field to a regular text field and let the database sort out the filtering.
Reply With Quote
  #7  
Old 05-06-2008, 06:22 AM
147 147 is offline
Newcomer
 
Join Date: May 2008
Location: Eastern Shore
Posts: 20
Default

I began working on this problem again early this morning and I learned something new that I was hoping someone could explain. When I add rows to the table through the program, they do actually appear in the table the next time I run the program. However, if I try to view the data directly from the datatbase by selecting a table and clicking "View Data", all of the new rows are gone. Also, if I try to refresh the connection to the DB, the same thing happens when I open the program the next time- the new data is gone.

The only way the data remains consisatent is if I enter information directly into the table from the "view data" option.

Is this just how it is, or have I set something up incorrectly?
Reply With Quote
  #8  
Old 05-06-2008, 11:41 AM
MKoslof's Avatar
MKoslofAdd new record on form load; Show correct row ID on form MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

Are you positive you are inserting data into and viewing the same database connection? Do you have SQL Server Express or a full version of SQL Server? Have you tried looking at your tables outside of the .Net IDE?
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #9  
Old 05-06-2008, 01:02 PM
147 147 is offline
Newcomer
 
Join Date: May 2008
Location: Eastern Shore
Posts: 20
Default

I probably have the express version of SQL Server- whichever one comes with the VS 2008 Pro trial edition you download from MS (I'm trying to see if my status as a student will let me buy the educational version before shelling out $800+).

I have tried publishing the program and running it outside of the IDE and in that scenario all updates to the tables remain constant. Does this happen because the IDE environment is not meant to manipulate the data?

Also, I figured out how to add a row to the table using an INSERT query on the dataset and then calling / populating that query from the form. I'm not sure if this is the best management approach to handling this problem, and it generates an extra few lines of code, but its working for me (so far).

As for your first question, I'm not sure I understand although I would like to. I started reading through the ADO.NET book I have on hand and I understand table adapters and data sets more clearly. I'm still reading about connections, though, so I'm not sure how I could have multiples.

I connected to the database using the wizard, of course, and there is only one database showing in the server explorer. However, I do have three datasets at this time- do they each create their own connections or do they use the original connection?

Thanks for your help with this!
Reply With Quote
  #10  
Old 05-06-2008, 01:08 PM
MKoslof's Avatar
MKoslofAdd new record on form load; Show correct row ID on form MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

Quote:
As for your first question, I'm not sure I understand although I would like to. I started reading through the ADO.NET book I have on hand and I understand table adapters and data sets more clearly. I'm still reading about connections, though, so I'm not sure how I could have multiples.
Good, the more you learn the easier this will become. I don't mean if your code has multiple connections, I was saying to verify your single connection instance (where-ever that might be) is the same one you are VIEWING. So you are opening Server A, Tables A and inserting into Server A, Tables A. It sounds like when you run this outside of the IDE you see the data in the tables. The Server Explorer in VS.net is simply a view of the SQL Server tables and schemas, it should match up, even if you view it IDE "Explorer view". Maybe you need to refresh the view in the IDE, I don't use this much, but I do know, there should be no differences per say.

Quote:
I connected to the database using the wizard, of course, and there is only one database showing in the server explorer. However, I do have three datasets at this time- do they each create their own connections or do they use the original connection?
Every data set invokes the connection. However, the Wizards use vanilla implementation of ADO.net, or basically adhere to the connection pooling aspects of ADO.net for performance and optimiziation. These wizards also handle the opening and closing of the connection internally. However, I strongly recommend ditching the wizards and building your own data controls, you'll get there
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #11  
Old 05-06-2008, 04:53 PM
sgm sgm is offline
Junior Contributor
 
Join Date: Aug 2003
Location: AlBayda, Libya
Posts: 305
Default

Hi
a great book to start is Programming ADO .NET 2.0 Core Reference by David Sceppa, it will guide you through the ADO .NET maze and give a very deep insight.
As for your entered data doesn't show in your database I just have one question did you create your database as file inside the VS IDE? do you have a database file in the solution explorer window??

If yes, you will find that your actual file is located in your project and it will copy itself into the bin\debug file everytime you run your program so what ever you did the last run will be gone, to remedy this you will have to select your database file from your solution explorer and change the property: copy to the output directory to copy if newer instead of always copy.

check this site for a great series of videos to get you started, they are about VB 2005.
http://msdn.microsoft.com/en-us/vbasic/bb466226.aspx


If not then you'll need to check your connection string, just as MKoslof said.

one last advice listen to MKoslof you'll save your self a lot of time.

good luck
Reply With Quote
  #12  
Old 05-06-2008, 06:21 PM
147 147 is offline
Newcomer
 
Join Date: May 2008
Location: Eastern Shore
Posts: 20
Default

Quote:
Originally Posted by sgm View Post
Hi
a great book to start is Programming ADO .NET 2.0 Core Reference by David Sceppa, it will guide you through the ADO .NET maze and give a very deep insight.
Thanks, I'll look it up on Amazon tonight.

Quote:
Originally Posted by sgm View Post
As for your entered data doesn't show in your database I just have one question did you create your database as file inside the VS IDE? do you have a database file in the solution explorer window??

Yes to both. Also, I watched that video literally an hour ago! It was helpful, but it did not solve my particular problem.
Quote:
Originally Posted by sgm View Post
If not then you'll need to check your connection string, just as MKoslof said.
one last advice listen to MKoslof you'll save your self a lot of time.
good luck
I'm beginning to think this is actually the problem, but I'm not sure where to look to find the answer to this question.

Here's what I've learned, though:
FIRST: I can add records to the database from the add record form (a huge leap forward for me at this point). If I do this outside of the IDE environment, the data saves to the table and I can retrieve it from the new customer form repeatedly. If I do this inside the ID environment, I can retrieve it as long as I don't refresh the database, or shut down VS and restart.

SECOND: I have a second form, existing customers, that uses a different dataset. In this form I can search the database for customers based on one of several parameters. I am able to retrieve all matching records that I entered directly into the table (server explorer>tables>r-click table>show table data), but none of those that I entered in with the first form.

It almost seems like the data I'm entering into the first form is not making it all the way back to the database tables, but that doesn't make sense to me because I can retrieve the information after restarting the program. Could this be a sign of a connection related issue? How do I code my own connection (which file / window, not the code itself, please)?

Since using different datasets results in different connections, is there any reason to use multiple datasets at all, or should I just create one 'MEGASET' and bypass this problem altogether? I've chosen to break them down mainly because I was concerned about performance and ease of understanding the layout.

I'm attaching my code from both forms in case it helps.
Attached Files
File Type: txt CustomerCode.txt (5.6 KB, 9 views)

Last edited by 147; 05-06-2008 at 06:49 PM.
Reply With Quote
  #13  
Old 05-06-2008, 07:19 PM
sgm sgm is offline
Junior Contributor
 
Join Date: Aug 2003
Location: AlBayda, Libya
Posts: 305
Default

I'm confused here, if you are using bound controls in your form then you don't need to write any code, just click the plus sign on the navigator type in your data and then click save that's it.
if you want to validate your input watch the other movie titled adding validation.
if you want to write your own code for adding new records for a strongly typed dataset check the snippets that come with VB just right click on your code select insert snippet navigate to data - Designer features and ADO .NET you'll find a code snippet on how to add a row to your dataTable you can go from there.
you should use a dataset for each form and it should contain only tables that are related to that form, also you can skip the form load fill statement unless you need your data to show for the user, one last thing only load what you need when you need it.
You might want to restart your project from scratch using what you've learned so far I'm sure that there is so many errors in your current project that debugging every single one of them would be a nightmare. Just start over as long as your are still in the beginning, practice makes perfect.

good luck
Reply With Quote
  #14  
Old 05-06-2008, 07:44 PM
147 147 is offline
Newcomer
 
Join Date: May 2008
Location: Eastern Shore
Posts: 20
Default

Quote:
Originally Posted by sgm View Post
You might want to restart your project from scratch using what you've learned so far ...


Actually, I took your advice and I was able to reproduce the basis of my program in just a few minutes, and everything worked like a charm. I'm thinking that all of the adding, removing, renaming, and modifying the datasets has left behind some ghosts that I don't know how to hunt down, and that these were somehow causing the issues I was having.

Like you said, though, practice makes perfect. I've learned a lot from you two in the last 48 hours- thanks very much for all your time on this.
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Add new record on form load; Show correct row ID on form
Add new record on form load; Show correct row ID on form
Add new record on form load; Show correct row ID on form Add new record on form load; Show correct row ID on form
Add new record on form load; Show correct row ID on form
Add new record on form load; Show correct row ID on form
Add new record on form load; Show correct row ID on form Add new record on form load; Show correct row ID on form Add new record on form load; Show correct row ID on form Add new record on form load; Show correct row ID on form Add new record on form load; Show correct row ID on form Add new record on form load; Show correct row ID on form Add new record on form load; Show correct row ID on form
Add new record on form load; Show correct row ID on form
Add new record on form load; Show correct row ID on form
 
Add new record on form load; Show correct row ID on form
Add new record on form load; Show correct row ID on form
 
-->