Captain Baz

I am using VB 2005 Express Edition!

Also - this is my first attempty at accessing an SQL database, so I acknowledge that I have probably done something very silly but at this stage I am stumped and need some help.

As a reference on how to do this, I used KB Article 301216.

I have basically copied exactly what was shown in the article changing names to suit my requirements and have come up with the following code :-

Dim connection As New SqlConnection(ConnectionString)

connection.Open()

Dim adapter As New SqlDataAdapter

ReadString = "SELECT * FROM configuration"

adapter.SelectCommand = New SqlCommand(ReadString, connection)

Dim DSetCONFIGURATION As New DataSet("CONFIGURATION")

adapter.FillSchema(DSetCONFIGURATION, SchemaType.Source, "configuration")

adapter.Fill(DSetCONFIGURATION, "configuration")

Dim DTblCONFIGURATION As DataTable

DTblCONFIGURATION = DSetCONFIGURATION.Tables("configuration")

Dim DRowCONFIGURATION As DataRow

For Each DRowCONFIGURATION In DTblCONFIGURATION.Rows

CONUSER = DRowCONFIGURATION("CONUSER")

CONSERIAL = DRowCONFIGURATION("CONSERIAL")

CONDIR = DRowCONFIGURATION("CONDIR")

CONLEDGERS = DRowCONFIGURATION("CONLEDGERS")

CONDFLTCOY = DRowCONFIGURATION("CONDFLTCOY")

CONYEAR = DRowCONFIGURATION("CONYEAR")

CON1STMTH = DRowCONFIGURATION("CON1STMTH")

Next

ConnectionString has been defined elsewhere, but for the record it is :-

Public Const ConnectionString As String = _

"Data Source=.\SQLEXPRESS;AttachDbfilename=C:\TCB\Data\TCB.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"

The problem :-

I am accessing a Database containing just one Row(record) with 7 Columns(fields) so no indexing is required. When the program is run, the variables CONUSER, CONSERIAL etc, are not populated with any data. Stepping through the program reveals that the program skips through (jumps over) the following 2 lines :-

Dim DTblCONFIGURATION As DataTable and Dim DRowCONFIGURATION As DataRow

(They simply aren't executed)

This has the obvious result that the variables DRowCONFIGURATION and DTblCONFIGURATION have a value of 'empty' and that the .Rows property in the FOR ....... Statement is 'nothing' resulting in the program falling through the FOR . NEXT loop without exercising it.

I can't see what I have done wrong and why this bit of code should behave this way.

Can anyone put me out of my misery



Re: Visual Basic Express Edition Populating a Dataset from an SQL Database

Trucker

Does your SQL database actually contain a table named "configuration"  

james

aka:Trucker

 





Re: Visual Basic Express Edition Populating a Dataset from an SQL Database

Captain Baz

Yes!

The DataTable "configuration" has 1 row with 7 columns(fields).

The names of the columns are :-

CONUSER

CONSERIAL

CONDIR

CONLEDGERS

CONDFLTCOY

CONYEAR

CON1STMTH

All fields are of 'string' type.

I am attempting to load them into a dataset with the same 7 names. It just seems more logical to me that - for example - a variable called CONUSER in the SQL datatable keeps that name all the way through the program. (For better or worse, I have decided to use the convention that all data stored in my SQL database has all capital letters in it's name as distinct from variables in general use through my program which will be mainly lower-case). (I can't help it, it's the COBAL/BASIC programmer in me!)





Re: Visual Basic Express Edition Populating a Dataset from an SQL Database

Trucker

It looks as though the problem is because your table contains no data. That is why the datarow loop is being skipped. If your variable names (capitolized) are assigned to textboxes  and no data exists in the table, then no data will be displayed when you loop thru the row on the table. If you do have data in the table, you need to tell the loop which row to grab ( index number).  It all depends on how you are displaying the data.  

 

james

aka:Trucker

 

 





Re: Visual Basic Express Edition Populating a Dataset from an SQL Database

Captain Baz

Thankyou James for your interest in my problem!

Firstly, I am a little confused because I have 2 responses from you (one an email, the other a message like this). Anyhow, I will respond to them both!

Your email suggests that "my new Row is being skipped because it already exists in the Table. Just load the Table into the DataSet!"

Doesn't the following line in my code do just that

adapter.Fill(DSetCONFIGURATION, "configuration")

The problem seems to occur with the next line :-

Dim DTblCONFIGURATION As DataTable

It is simply skipped! Mind you, I can make this line execute by changing it to the following :-

Dim DTblCONFIGURATION As New DataTable

There seems to be nothing I can do to make the Dim DRowCONFIGURATION ............ line execute though!

Regarding your other response - The records I am trying to load the data I have read from the SQL DataSet into, are simple string variables (as they are in the SQL DataTable). I have them defined in a Module as follows :-

Public CONUSER As String

Public CONSERIAL As String

Public CONDIR As String

Public CONLEDGERS As String

Public CONDFLTCOY As String

Public CONYEAR As String

Public CON1STMTH As String

I'm not trying to do anything fancy here! Simply load the contents of a one dimensional non-indexed DataSet from an SQL DataTable into a matching list of variables in my program. All variables(fields) in the SQL DataSet are simple string variables as are the variables in my program. (In fact they all have the same names which doesn't seem to matter). Can anything be more basic (No pun intended).

If it's not too much trouble, could you send me a sample of the changes you would make to my code

Regards

Captain Baz





Re: Visual Basic Express Edition Populating a Dataset from an SQL Database

ShEi

Try not using a datatable and  use this option instead.

Dim connection As New SqlConnection(ConnectionString)

connection.Open()

Dim ReadString As String

ReadString = "SELECT * FROM configuration"

Dim myCommand As New SqlCommand(ReadString, connection)

myCommand.ExecuteNonQuery()

Dim adapter As New SqlDataAdapter(myCommand)

Dim DSetCONFIGURATION As New DataSet()

connection.Close()

adapter.Fill(DSetCONFIGURATION)

'assuming that you have declared the variables and that you assigned the values in the same order as the order of your fields in your database

CONUSER = DSetCONFIGURATION.Tables(0).Rows(0).Items(0)

CONSERIAL =  DSetCONFIGURATION.Tables(0).Rows(0).Items(1)

CONDIR =  DSetCONFIGURATION.Tables(0).Rows(0).Items(2)

CONLEDGERS =  DSetCONFIGURATION.Tables(0).Rows(0).Items(3)

CONDFLTCOY =  DSetCONFIGURATION.Tables(0).Rows(0).Items(4)

CONYEAR =  DSetCONFIGURATION.Tables(0).Rows(0).Items(5)

CON1STMTH =  DSetCONFIGURATION.Tables(0).Rows(0).Items(6)






Re: Visual Basic Express Edition Populating a Dataset from an SQL Database

Trucker

Sorry, I edited my response a couple of times to get to what was posted here. The email you received is the notification that you received a response here to your post. The system may have sent you a notification on the first response I posted before I edited it to what is showing now. ShEi's example is good and will get you going. I don't use SQL as much as some folks do. I am still supporting older, Access Database systems. So, my response to your post was based on that and my limited SQL experience. ( I do know how to use SQL, just do not use it all the time)

james

aka:Trucker





Re: Visual Basic Express Edition Populating a Dataset from an SQL Database

Captain Baz

Thanks for the info Trucker, I would love to be able to say that it worked. But it didn't!

When stepping through the program at the line that says :-

CONUSER = DSetCONFIGURATION.Tables(0).Rows(0).Item(0)

I get the error message 'There is no row at position 0'

On the basis of the old adage "If it looks like a duck and it sounds like a duck .................. etc,etc", maybe we should be accepting that if the program says there aren't any rows there then there really aren't any rows there. Maybe there is something wrong with my ConnectionString and/or ReadString resulting in no data being retrieved from the SQL DataBase.

It is a little strange that no errors are generated, but believable!

The problems I am having are entirely consistant with no data actually being there!

Given I know practically nothing about SQL, I nonetheless ask the question - Is the ReadString I am using, correct I have only 1 row of data in my "configuration" datatable, and no index. This, while very simplistic, may be causing a problem.

It's only a thought, what do you think

Once again, thankyou for your help, it's much appreciated.

Captain Baz





Re: Visual Basic Express Edition Populating a Dataset from an SQL Database

Trucker

The readstring is correct as it selects everything in the table and makes it available to your code. Here's a quick test, right before the line

CONUSER = DSetCONFIGURATION.Tables(0).Rows(0).Item(0)

Do something like this:

Dim rowCount as Integer

rowCount = DSetCONFIGURATION.Tables(configuration).Rows.Count

Then put a breakpoint on the CONUSER line and run your program. Then when you hold the cursor over rowCount it should give you the number of rows in your table.

I do have a question, how did you create the SQL table in the first place Is it from another portion of code you have written or from another application It could be, if you created the table in another portion of your code, that you did not add any data to the table. If the table was created from another application, does that application see the data in the table

Go to this link (if you haven't already done so) http://msdn.microsoft.com/vstudio/express/sql/

And download SQL Server Express Edition and use the tools there to check out your SQL database. That way you can check out problems, build tables to test and it will help you find some of the problems you are having if it is related to the way the table is built.

james

aka:Trucker





Re: Visual Basic Express Edition Populating a Dataset from an SQL Database

Captain Baz

Tried that and it came back with the result of 0 Rows!

I think that I had better have a close look at my SQL Database.

I will do that this weekend. Thanks for the help!

I will let you know how I get on.

Regards,

Captain Baz





Re: Visual Basic Express Edition Populating a Dataset from an SQL Database

Squire James

There are several steps you are doing that the Fill method will do for you, like opening the connection and filling in table schema. My guess is that the DataTable is being fumbled somewhere on the hand-off. I suggest not trying to pull out the DataTable, and refer to it from the DataSet directly. This would make it come out to something like this:

Dim connection As New SqlConnection(ConnectionString)

Dim adapter As New SqlDataAdapter("SELECT * FROM configuration", connection)

Dim DSetCONFIGURATION As New DataSet

DSetCONFIGURATION.Tables.Add("Configuration")

adapter.Fill(DSetCONFIGURATION, "Configuration")

For Each DRowCONFIGURATION As DataRow In DSetCONFIGURATION.Tables("Configuration").Rows

CONUSER = DRowCONFIGURATION("CONUSER")

CONSERIAL = DRowCONFIGURATION("CONSERIAL")

CONDIR = DRowCONFIGURATION("CONDIR")

CONLEDGERS = DRowCONFIGURATION("CONLEDGERS")

CONDFLTCOY = DRowCONFIGURATION("CONDFLTCOY")

CONYEAR = DRowCONFIGURATION("CONYEAR")

CON1STMTH = DRowCONFIGURATION("CON1STMTH")

Next

I've done a lot of these, and as far as I know if the database has the goods the above code should work!





Re: Visual Basic Express Edition Populating a Dataset from an SQL Database

Captain Baz

Thanks for the help 'Squire James'!

Tried your suggestion.

Same result as all my other attempts.

I now feel sure that my problem lies with my SQL Database.

I will let you know how I get on.

Regards,

Captain Baz





Re: Visual Basic Express Edition Populating a Dataset from an SQL Database

Captain Baz

Well guys. We did it!!!

The problem was with my SQL datatable.

When I checked, I found that I actually had 'null' values in all my columns with the result that VB said there must be no rows.

Once I made sure there were string values in each column(field), it all worked!!

Do I feel stupid You bet! My only excuse is that I am a novice and this was my first attempt.

Hopefully we have all learnt something.

Best regards and once again thankyou!!

Captain Baz





Re: Visual Basic Express Edition Populating a Dataset from an SQL Database

Trucker

Glad to hear you figured it out! Don't feel stupid. Sometimes the easiest answers don't seem possible. And besides that, you certainly aren't the first one to have something like that happen !!!

james

aka:Trucker