Solved

Visual Studio Visual Basic Form load from sql database

Posted on 2007-03-17
16
231 Views
Last Modified: 2013-12-25
I have the following code

Private Sub SettlementStatment_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
 
        Me.SettlementTableAdapter.Fill(Me.NightAuditDataSet.Settlement)

 '---------------------------------------------------------------------------------------------------------------------------'
  Dim row As DataRow = NightAuditDataSet.Tables(0).Rows(NightAuditDataSet.Tables(0).Rows.Count - 1)
MessageBox.Show(row(0).ToString())
Me.Sitetxt.Text = 2

 End Sub

The fields are set up in the databindings properties at form[Desing]

but when you load the form the fields do not populate.

Even though the      MessageBox.Show(row(0).ToString())
Shows the last record number.

First time doing this --can you tell me the code for the sub
if I give you this
Server=FESSQL;Database=NightAudit;Trusted_Connection=yes;"
Table Name ----Settlement
Tried to make work -but had no luck

I'm I missing something relating to this:

myConnection = New SqlConnection(ConnStr)
            sql = "select * from tblSubCategories"
            da = New SqlDataAdapter(sql, myConnection)
            da.Fill(ds, "tblSubCategories")
            dg.DataSource = ds.Tables(0)


JK
0
Comment
Question by:fsuedu
  • 8
  • 7
16 Comments
 
LVL 27

Expert Comment

by:planocz
ID: 18742017
I have a sample program that you can play with but, it is pretty large and I have a Access db as a test sample.
If you want I can email it to you.
0
 
LVL 34

Expert Comment

by:Sancler
ID: 18742036
Your dataset/datatable is obviously being filled with something or you wouldn't get the last record number being reported.  So there seems to be something wrong with the bindings.  

You say "The fields are set up in the databindings properties at form[Desing]".  How was that done?  Did you drag stuff from the DataSource window onto the form?  Or did you drag controls from the ToolBox onto the form and then set the databindings by clicking the little buttons in the top right corner?

And can you please clarify the last bit of your post?  That seems to be dealing with a different table - "tblSubCategories" - rather than a table called "Settlement" which is (a) what your description "Table Name ----Settlement" refers to and (b) successfully brought over by the tableadapter.  Is the dg you refer to in that one of the controls for which "The fields are set up in the databindings properties at form[Desing]"?

Roger  
0
 

Author Comment

by:fsuedu
ID: 18743321
Hello Sancler

                          I  dragged controls from the ToolBox onto the form and then set the databindings by right clicking -go to properties-Data-(DataBindings)-Text-Then Select from the datasource the Appropriate sql field that matches the txt field?

No the last part was just an exampe -of code that I thought I might be missing

Thanks for your help
0
 
LVL 34

Expert Comment

by:Sancler
ID: 18743643
What does it say opposite the Text heading, under the DataBindings Heading, in the TextBox's Properties window?  If, as is quite likely, it refers to <someName>BindingSource what does it say under the headings DataSource and DataMember in the Properties window for that?  I would expect it to be NightAuditDataSet for the former and Settlement for the latter.

Roger
0
 

Author Comment

by:fsuedu
ID: 18743924
For exampe it say SettlementBindingSource - PreviouAdvDepositstxt  for the properties for txtbox PreviouAdvDepositstxtText  

Lost on where to go for the last part what does it say under the headings DataSource and DataMember in the Properties window for that?

Under the form[Design]

I have NightAuditDataSet ,SettlementBindingSource,SettlementTableAdapter

if I look at SettlementBindingSource properties in DataSource it has NightAuditDataSet

if that help?

Jk
0
 
LVL 34

Expert Comment

by:Sancler
ID: 18744258
SettlementBindingSource should also have a property DataMember.  It's pretty certain, given that the BindingSource is called SETTLEMENTBindingSource that that property will be Settlement.  If so, the databindings look like they should work.  

But if you can confirm that, it will close down one avenue of enquiry.

Can you try please changing the code that gets the number for your MsgBox to this

Dim row As DataRow = NightAuditDataSet.Tables("Settlement").Rows(NightAuditDataSet.Tables("Settlement").Rows.Count - 1)
MessageBox.Show(row(0).ToString())

and see if it's still giving the result you expect?

Roger
0
 

Author Comment

by:fsuedu
ID: 18747654
Good Morning

get this error message: Cannot bind to the property or column DateStamptxt on the DataSource.
Parameter name: dataMember
0
 
LVL 34

Expert Comment

by:Sancler
ID: 18747725
It's very difficult debugging things like this at a distance.  There's so many bits and pieces now scattered - by VB.NET 2005 - through so many files.  What I suggest you do is zip up the whole project and upload the .zip file here

http://www.ee-stuff.com/

then post the URL that you are given back in this thread.  That way, I can look at it as a whole rather than having to ask detailed questions.  If you want to remove any sensitive stuff - e.g. passwords in Connection Strings - before you do so, that will be OK.  Just replace them with *****, so I can recognise what's for real.

Roger
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:fsuedu
ID: 18748030
0
 
LVL 34

Expert Comment

by:Sancler
ID: 18748791
The good news is that I can find nothing wrong.  The bad news is that that gets you no nearer to solving your problem.

Although I have done a pretty through review of the code, I cannot actually run it to test without data.  I did dummy up a form and some data just to test out the settings on the DateStamptxt control but, as I expected from the code, the binding worked OK on that.

Originally, as I understood things, the form was loading OK and your message box was showing a value that confirmed that the data was loading, but all the controls were left blank.  With the error reported in your latest post, I would not even expect the form to load.  That error would occur on this line

        Me.DateStamptxt.DataBindings.Add(New System.Windows.Forms.Binding("Text", Me.SettlementBindingSource, "DateStamptxt", True))

in the InitializeComponent Sub.  Am I right about that?  If so, what changes did you make between the original position and that which you are now reporting?

Roger
0
 

Author Comment

by:fsuedu
ID: 18748973
You  might want to kill me I just relized it is pulling the data
from the SQL Table--but it is the first row instead of the last?

It is going off this
 Dim row As DataRow = NightAuditDataSet.Tables(0).Rows(NightAuditDataSet.Tables(0).Rows.Count - 1)
        MessageBox.Show(row(0).ToString())

Any Ideas

0
 

Author Comment

by:fsuedu
ID: 18749009
Its pulling off Primary Key 91 which is the first record now.
Put should be pulling off record primary key 98
but the message box says 98

Does that make sense?
0
 
LVL 34

Expert Comment

by:Sancler
ID: 18749168
When your form loads, the controls will display the data from the first record.  Your message box is - specifically - pulling its data from the last.  To move between the various records you will need some navigation mechanism, and I don't see any.  Just to check, stick a button on your form with this code

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        SettlementBindingSource.Position += 1
    End Sub

and click it.  The controls should then update to reflect different records.

Roger
0
 

Author Comment

by:fsuedu
ID: 18749452
Yeap it worked the recordes changed as I hit the button until the last recored.

Is thier a way to make it the last record when it loads?
0
 
LVL 34

Accepted Solution

by:
Sancler earned 500 total points
ID: 18749538
       SettlementBindingSource.Position = SettlementBindingSource.Count - 1

Roger
0
 

Author Comment

by:fsuedu
ID: 18750209
Thanks for all your help
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now