Solved

Visual Studio Visual Basic Form load from sql database

Posted on 2007-03-17
16
277 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Stressed Out?

Watch some penguins on the livecam!

 
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
 

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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
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…

717 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