VB Express & SQL Server Express

[JaedenRuiner]

Okay,

I'm trying to do something very simple here, which is create a small database to be used by my application. I installed SQl Server Express and VB Express, and proceeded to create a database. First I added a DataSource, (and created the sdf file through that process) and then with the Database Explorer I created several tables, and the relationships between them, which I then drag/dropped onto a the dataset designer. (.xsd file).

Now, I want to use them, and I haven't a clue how, so I don't expect anyone to answer everything, but perhaps a point in the right direction for a good tutorial.

There are BindingSources, TableAdapters, the Dataset (typed or untyped) and a whole slew more of stuff that seems way more complicated than necessary.
My First desire:
A list box with all the table names, and a DataGridView(). When i click on the table name I want the DataGridView to list the contents of that table. So far, it doesn't work at all. When I set the datagridview's data source to my dataset and set the datamember to a table it seems to work, in design mode, but in run mode I can't change the DataMember type to tell the datagrid to refresh with a new datasource table.

letting the system do it for me, auto-created a TableAdapter for the specific Table beind used as the DataMember and a BindingSource specific to that table as well. But I can't change the DataMember of the Binding Source either so again that didn't work.

Do I need to have 45 different objects for all the different tables in my DataSet just to get the DataGridView to read them, or can I set the input at run-time?

Secondly, (on the same form) i have a text box and I want to be able to write and run SQL on the dataset, involving all the tables within, but I can't even begin to find how to Execute a SQL statement on the data set so I'll need a point in that direction.

Basically, I know Databases quite well, and I know VB.Net decently, but How to connect the two together?

Thanks
Jaeden "Sifo Dyas" al'Raec Ruiner

 

[JohnH]

"sdf" - that's the hint you're using VS2008 and Sql Compact, right? Your profile says VS2005, and "SQL Server Express" uses mdf files.

The Designer can't help you create one strongly typed object that works universally, so you have to write some code.

Changing DataMember should work, but have you filled that table in Dataset from database yet? What's behind "fill" in relation to a TableAdapter is basically its SelectCommand, which is a SqlCeCommand. Short sample:

Dim con As New SqlCeConnection("Data Source=|DataDirectory|\test2.sdf")
Dim ada As New SqlCeDataAdapter("SELECT * FROM [table1]", con)
Dim ds As New DataSet
ada.Fill(ds, "table1")
ada.SelectCommand.CommandText = "SELECT * FROM [table2]"
ada.Fill(ds, "table2")
Me.DataGridView1.DataSource = ds
Me.DataGridView1.DataMember = "table1"

Now setting .DataMember = "table2" in another event will display that table in DGV promptly. You can of course also fill one table and display it, then later clear and fill another for display.

To run a sql command use the SqlCeCommand class.

You might be able to list db table names with SMO, but I'm not sure if it is supported for SQL Compact.

*edit* - not supported until SQL/Management 2008. But with an instance of the strongly typed dataset you can read the table names from Tables collection even if they have no data filled.​

Testing out common data access scenarios by configuring designer, then looking though the generated code may for some help understanding how everything holds together, but it sure generates lots of code. For a beginner reading documentation or books about this topic may seem large at first, but patience and learning is needed.

 

[JaedenRuiner]

"sdf" - that's the hint you're using VS2008 and Sql Compact, right? Your profile says VS2005, and "SQL Server Express" uses mdf files.

Well, at Home i use VS2005 Pro, but as we know the Express versions of VB and VC++ are 2008, so sorry 'bout the little confusion. As for the SQL Compact...i guess I am. I mean, I downloaded the VB2008, and in the install it asked about installing sql server express (or something like that). But realizing I did not want to make everyone on our network who will be using the application have to install Access, I figured I'd use SQl Server Express to set up a simple data base access point. I mean, this is not going to be big at all, nor is it going to be search intensive. It is simply a data point for manipulating and analyzing some data and creating some output in a generated email.

However, you are probably right about the Sql Compact, because after I downloaded the Sql Server Express with the Advanced Services (tools, etc) i Found that even though I created a database with it, I couldn't find the file. I mean, i need this to be in a file on a share drive, which I may not be able to do, because both express and compact complained that they couldn't "create" a database on a share drive. however, I figured I might be able to create it and then link/connect to it later once created. Who knows, I will continue playing in that department.

Back to the question at hand though.

Basically, when I first created the dataset (the xsd file in vb express), it gave it a name and everything seemed fine. I then wanted to set the dataset as the source for the DGV, but upon doing so, it created a "MyDataSet1 as MyDataSet" component in the form. Basically, i'm figuring my design of the dataset (xsd) was merely a type definition. I noticed as well there is a MyDataSet.xsd file as well as a MyDataSet.vb file (not sure what the partial class is inside that data set, but I'm sure i'll learn.)

in my playing around this is the code I found that worked, stealing some of it from the designer.vb file for my form.

   Private Sub Admin_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
      Me.taTbl_Material.Fill(Me.dsRexam.tbl_Material)
      Me.taTbl_Location.Fill(Me.dsRexam.tbl_Location)
      Me.taTbl_BoL.Fill(Me.dsRexam.tbl_BoL)
      Me.taTbl_Email.Fill(Me.dsRexam.tbl_Email)
   End Sub

   Private Sub ListBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged
      Dim st As String = "tbl_Material"

      BindSource.SuspendBinding()
      CType(Me.dgvResults, System.ComponentModel.ISupportInitialize).BeginInit()
      CType(Me.BindSource, System.ComponentModel.ISupportInitialize).BeginInit()

      Select Case (ListBox1.SelectedIndex)
         Case 0 : st = "tbl_Material"
         Case 1 : st = "tbl_Location"
         Case 2 : st = "tbl_BoL"
         Case 3 : st = "tbl_Email"
      End Select
      BindSource.DataSource = dsRexam
      BindSource.DataMember = st
      dgvResults.DataSource = BindSource
      CType(Me.dgvResults, System.ComponentModel.ISupportInitialize).EndInit()
      CType(Me.BindSource, System.ComponentModel.ISupportInitialize).EndInit()
      BindSource.ResumeBinding()
   End Sub

As you see I was trying to use one bindingsource variable to handle all the bindings as necessary instead of having 4 tableadapters and 4 bindingsources for each one. I have been looking onto the ADO.Net forum here and I'm gleaning a better understanding of the DataSet, DataTable, BindingSource, etc classes. I'm just not necessarily sure how they all interconnect. As it is, the BindSource variable is NEVER related to the TableAdapter that fills the individual tables of the DataSet, but maybe I'm interpreting the usage of the TableAdapter wrong.

Life was so much simpler when all i had to do was run a DAO.ExecuteSql() in access vba, *cheezygrin*....that and sql*plus with oracle i didn't need to do any of the connectivity i could just type the sql and it ran. This programmatic stuff is a bit more confusing, as we have 20 different types to do what I would expect was 3 jobs, but eh, noobies need to learn somewhere.

So, at this point, I am looking at figuring the difference between the Sql Compact and the Sql Express, which is better/worse for my purposes, as well how to use the Sql Express Management Studio thingy to create a database that my Vb.Net app can connect to. (previously i tried that and couldn't find the file or whatever I needed to connect and currently all these things are retained locally, haven't even begun to challenge the concept of network communication.)

SMO Don't recognize the acronym ???

But with an instance of the strongly typed dataset you can read the table names from Tables collection even if they have no data filled.

So since my dataset contains all the tables, the tables collection within that class retains the 'names' of the tables so I could just add them one at a time at run-time. Good to know.

For a beginner reading documentation or books about this topic may seem large at first, but patience and learning is needed.

Don't i know it. Problem is, I don't know which are the good ones and which are the bad ones. Once I begin to dissect how the classes interact and how they are built, the rest is simple. All code is readable, and as with every other language i've learned, it just takes patience and dedication to figure out what they are trying to do...with the occasional point in the right direction from others.

Thanks for the help
Jaeden "Sifo Dyas" al'Raec Ruiner

 

[JohnH]

SQLce is meant for single user, local machine/device and is just a file structure. Other Sqls works as services, meaning in a network environment one machine is attached to the files and serve other connected parties, you knowing databases well knows what database service is. Even Sql Express works like this, but is blocked for network and IP access by default, configure Remote Connections with Surface Area Configuration tool (+ possibly enable TCP/IP protocol for networks with SQL Config Manager). VB Express 2008 includes SqlCe by default, with option to add Sql Express also.

SMO is Sql Management Objects, nice libraries, but not what you need here.

As it is, the BindSource variable is NEVER related to the TableAdapter that fills the individual tables of the DataSet, but maybe I'm interpreting the usage of the TableAdapter wrong.

It never is, BS work between controls and data source (dataset), TA (which is connection + data adapter + other query commands) works between database and local dataset.

 

[JaedenRuiner]

SQLce is meant for single user, local machine/device and is just a file structure. Other Sqls works as services, meaning in a network environment one machine is attached to the files and serve other connected parties, you knowing databases well knows what database service is. Even Sql Express works like this, but is blocked for network and IP access by default, configure Remote Connections with Surface Area Configuration tool. VB Express 2008 includes SqlCe by default, with option to add Sql Express also.

I get it. So basically the SQLCE works on a file, but the SQl Express is a service built into a specific system, and when I want to access that database i'd log into that system with a sql client and then be able to access the database, thus in order to create a database on a separate computer i'd have to install sql express and configure it to allow ip access on that system. Sucks, but at least I know what I'll need to do.

It never is, BS work between controls and data source (dataset), TA (which is connection + data adapter + other query commands) works between database and local dataset.

Okay, this begins to make a lot of sense. So the TableAdaber is a connection and a data adapter (with some more specific info) that is used to connect to the database and load the data into a local dataset, where the BindingSource acts as a liason between local controls and the local dataset.

One question, with the "DataSource" and "DataMember" properties of many controls is the BindingSource really necessary? What does it provide beyond the intrinsic data binding that already exists with many .net controls?

Thanks
Jaeden "Sifo Dyas" al'Raec Ruiner

 

[JaedenRuiner]

Okay,

This might make everything simple.
Why does this code NOT work:

   Private Sub Admin_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
      Dim i As Integer
      Me.taManager.tbl_BoLTableAdapter = New RexamClean.rexamshipDataSetTableAdapters.tbl_BoLTableAdapter()
      Me.taManager.tbl_EmailTableAdapter = New RexamClean.rexamshipDataSetTableAdapters.tbl_EmailTableAdapter()
      Me.taManager.tbl_LocationTableAdapter = New RexamClean.rexamshipDataSetTableAdapters.tbl_LocationTableAdapter()
      Me.taManager.tbl_MaterialTableAdapter = New RexamClean.rexamshipDataSetTableAdapters.tbl_MaterialTableAdapter()
      For i = 0 To dsRexam.Tables.Count - 1
         TableList.Items.Add(Strings.Split(dsRexam.Tables(i).TableName, "_")(1))
      Next
   End Sub

   Private Sub TableList_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles TableList.SelectedIndexChanged
      Dim lb As ListBox = sender
      Dim st As String = "tbl_Material"

      BindSource.DataSource = Nothing
      Select Case (TableList.SelectedItem)
         Case "Material"
            st = "tbl_Material"
            taManager.tbl_MaterialTableAdapter.Fill(dsRexam.tbl_Material)
         Case "Location"
            st = "tbl_Location"
            taManager.tbl_LocationTableAdapter.Fill(dsRexam.tbl_Location)
         Case "BoL"
            st = "tbl_BoL"
            taManager.tbl_BoLTableAdapter.Fill(dsRexam.tbl_BoL)
         Case "Email"
            st = "tbl_Email"
            taManager.tbl_EmailTableAdapter.Fill(dsRexam.tbl_Email)
      End Select
      BindSource.DataSource = dsRexam
      dgvResult1.DataSource = BindSource
      BindSource.DataMember = st
   End Sub

   Private Sub BindSource_BindingComplete(ByVal sender As Object, ByVal e As System.Windows.Forms.BindingCompleteEventArgs) Handles BindSource.BindingComplete
      SQLText.AppendText("BS: Bind Complete" & vbCrLf)
   End Sub

   Private Sub BindSource_DataMemberChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles BindSource.DataMemberChanged
      SQLText.AppendText("BS: Data Member Changed: " & BindSource.DataMember & vbCrLf)
      BindSource.ResetBindings(True)
   End Sub

I mean, the Text box on the screen updates the text i'm adding to it, when I step through and follow the watches of the variables, (including the dsRexam dataset) all the values are there (including the row data from the database in the dsRexam DataSet) and yet the DataGridView NEVER shows anything.

No table is displayed, no grid, columns, rows, etc. Nothing. The dgvResult1.DataSource is set to the BindSource. The BindSource.DataSource is set to the dsRexam dataset. And when I click in the list box, it sets the appropriate DataMember, but DOESN'T refresh my control. Why Not!!! It is so frustrating to look at code like this that in all logic "Should" work, but doesn't. however, before and after the DataMember change if I put these lines in:

      'CType(Me.dgvResults, System.ComponentModel.ISupportInitialize).BeginInit()
      'CType(Me.BindSource, System.ComponentModel.ISupportInitialize).BeginInit()
      'CType(Me.dgvResults, System.ComponentModel.ISupportInitialize).EndInit()
      'CType(Me.BindSource, System.ComponentModel.ISupportInitialize).EndInit()

It seems to work. Pardon me, but, wth man. I have to execute a type cast call to the ISupportInitialize Interface to get table data to refresh from my dataset into my DataGridView. That's a bit...redundant and frankly counter intuitive. Basically, I'm just frustrated that everything I'm doing is so fundamentally basic when it comes to programmatic accessing of databases, and yet, the standard programming (and OOP) logic doesn't apply at all. ResetBindings() as a method of BindingSource and Control tells me it will "Reset the bindings" but it doesn't. Refresh() only invalidates and repaints the control, I need to order the control to "reload its data from its datasource at RUN TIME" but it appears there is no such command. If I can resolve this simple issue, everything else will pretty much fall into place, its just that ifcChanging the DataMember and trying to order the control to Reset its Bindings, Refresh, or generally do something other than sit there doesn't work, than nothing else I try will work either. *shrug*

Maybe there is something underneath that I'm not catching, but it seems:

• 1 DataSet (4 Tables)
• 1 BindingSource
• 1 DataGridView

Should be all i need to view the 4 tables.

Peace,

Jaeden "Sifo Dyas" al'Raec Ruiner

 

[JaedenRuiner]

DataGridView.AutoGenerateColumns...
Would have been nice to see that in the Property Sheet for the designer, but apparently it is not a published property, even though it was defaultly set to False in the the designer.vb file. I changed that and suddenly everything works now...
Thanks
Jaeden "Sifo Dyas" al'Raec Ruiner

 

[JohnH]

is the BindingSource really necessary?

As you see in sample I posted it may not be necessary. Here are some snippets from "about BS" help page:

The BindingSource component serves many purposes.

• First, it simplifies binding controls on a form to data by providing currency management, change notification, and other services between Windows Forms controls and data sources.
• Navigation and updating...
• Operations such as sorting and filtering ...
• In addition, the BindingSource component can act as a strongly typed data source.
• BindingSource provides members for accessing the underlying data.
• Data sources that are bound to a BindingSource component can also be navigated and managed with the BindingNavigator class

No table is displayed, no grid, columns, rows, etc. Nothing.

DataGridView has a AutoGenerateColumns property that defaults False, but must be True when switching between sources of different schemas. Set this True in form Load, you don't need to reset datasources for neither BS or DGV, just change the BS.DataMember.
...somehow I didn't see your previous post when I entered thread, or perhaps just time fled...

 

[JaedenRuiner]

Cool.

Yea, i began to figure it out once I was able to find that autogeneratecolumn, which is very frustrating that it was hidden in such a manner.

However, Now I got one for you:

   Private sqlcmd As New SqlServerCe.SqlCeCommand("", _
                         New SqlServerCe.SqlCeConnection(My.MySettings.Default.rexamshipConnectionString))

..snip...

      Dim trans As SqlServerCe.SqlCeTransaction
      sqlcmd.Connection.Open()
      trans = sqlcmd.Connection.BeginTransaction()
      sqlcmd.CommandType = CommandType.Text
      sqlcmd.CommandText = "drop table tbl_Temp;"
      SQLText.AppendText(sqlcmd.Connection.Database & vbCrLf)
      'SQLText.AppendText(Str(sqlcmd.ExecuteNonQuery()) & vbCrLf)
      sqlcmd.CommandText = "CREATE TABLE [tbl_Temp] ( " & _
                           "xref nvarchar(6)," & _
                           "name nvarchar(150)," & _
                           "bolref nvarchar(10)," & _
                           "matref nvarchar(6)," & _
                           "shipdate datetime," & _
                           "qty numeric(10)" & _
                           ");"

      SQLText.AppendText(Str(sqlcmd.ExecuteNonQuery()) & vbCrLf)
      trans.Commit(SqlServerCe.CommitMode.Immediate)
      sqlcmd.Connection.Close()

Just as a trial run, because I am so familiar and used to working solely in sql i wanted to see what happened when I tried this. Some of the command executions I'll be performing are designed to allow the "admin" (not really an admin but you get the idea) to manage some of the facets of this back end db through cmd line sql. However, when I execute this code, it the executenonequery() returns a -1 when the help files say it *should* return 0. I'm wondering what part of the command interaction I might be missing. I've tried it without the transaction code (the begin/commit stuff) but either way, after executing the command and closing out the application the tbl_Temp does not exist in the database file that I created. Once, it actually came up with an error that the table already existed which is why I added the "drop table" execution, but on average that line when executed generates an error saying the table does NOT exist. Either way, I cannot get the Database Explorer in the VB Express IDE to refresh and show me the created table.

Thanks
Jaeden "Sifo Dyas" al'Raec Ruiner

 

[JaedenRuiner]

As a quickie...I think I got the database to "add" a new table via the sqlcecommand and the create table sql statement. however, shortly after I did it, and was able to do some select queries on the table into a default dataset type (not "strongly" typed) i found that the database explorer began having issues. I was getting the error:
The Path is not valid, or something like that. I forget the specifics and now I can replicate the error.

either way, while my program is running, the create table works, and I can access the table. But once I close out the table is gone. I actually had it at one point being able to remain over several runs of the application, but when i closed out vbexpress and reloaded it the table was gone. In no way did the table ever appear in the database explorer.

Suggestions?
Thanks
Jaeden "Sifo Dyas" al'Raec Ruiner

 

[JohnH]

executenonequery() returns a -1 when the help files say it *should* return 0

I disagree, help says it should return -1:

For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. For all other types of statements, the return value is -1.

either way, while my program is running, the create table works, and I can access the table. But once I close out the table is gone. I actually had it at one point being able to remain over several runs of the application, but when i closed out vbexpress and reloaded it the table was gone. In no way did the table ever appear in the database explorer.

You are probably experiencing the classic cjard DNU phenomenon, ie you have a project template db used in Designer and you have a runtime db in deployment/debug output path. Runtime changes does not change the project db, and runtime db is sometimes overwritten by the project db during build (determined by changes made in Designer and the file property "Copy to output").

The Path is not valid

I also got this yesterday with the SqlCe data connection, must be a bug, I restarted VS and problem was gone.

 

[JaedenRuiner]

I disagree, help says it should return -1:

For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. For all other DML statements, the return value is -1.

For DDL statements, such as CREATE TABLE or ALTER TABLE, the return value is 0.

Perhaps i'm misunderstanding the DDL, but it says "Such as Create Table" so I deduced that meant my Create Table statement. *shrug*. -1 or 0, it does work though.

You are probably experiencing the classic cjard DNU phenomenon, ie you have a project template db used in Designer and you have a runtime db in deployment/debug output path. Runtime changes does not change the project db, and runtime db is sometimes overwritten by the project db during build (determined by changes made in Designer and the file property "Copy to output").

I shouldn't be getting this problem unless VB2008 is more nefarious than I thought. My project is in "C:\Source\Visual Studio 2008\Project\My Project" folder, where my db is in C:\Db\mydb.sdf. When I added the DataSource it kept asking to move the DB and I always said no!. The db I'm using is basically a template for once I get the application running, I'll most likely be wiping all the tables to be ready for *real* data, not test data. If VS is copying the db or doing something with it that I don't know about I want it to stop. I just want to be able to "browse" it with my Data Connection Server Explorer, and then link to it at run time, no matter where the "file" pysically is. You know, kinda of like the old ADODB and DAO connections of Access VBA, you just link and it works without all the fancy graphical IDE stuff. The IDE additions are nice, but the program is trying to "think" for me and I don't appreciate it. *chuckle*

I also got this yesterday with the SqlCe data connection, must be a bug, I restarted VS and problem was gone.

Yea, i noticed that too.

Thanks
Jaeden "Sifo Dyas" al'Raec Ruiner

 

[JohnH]

SQLce documentation was missing in my library so I just checked the SqlCommand class, you're right this was added in documentation for SqlCeCommand.

So you haven't added db to project, I don't think VS has done it anyway you should in that case see the db file as an item of project in Solution Explorer. You can also doublecheck connection string in project settings and DB Explorer window to see the path used. After runtime modification, have you tried Refresh the data connection in DB Explorer?

 

[cjard]

You know, kinda of like the old ADODB and DAO connections of Access VBA, you just link and it works without all the fancy graphical IDE stuff. The IDE additions are nice, but the program is trying to "think" for me and I don't appreciate it. *chuckle*

Nothing has actually changed. In the olden days that you kinda like, you would:

Write code to conenct the db
Write code to download the data
Write code to push the data into the text box
Write code to implement a save button
Write code to take the data out of the box and psuh it back into the db

The modern way:

The IDE writes code to represent a data access layer in your project, and all database access code is sensibly encapsulated in this one place
The IDE writes code to pul lthe data down from the db
The IDE writes code to put the data into a local storage
The IDE writes code to link a textbox to the data
The IDE writes code to take the data out and push it back


There is no difference, except who writes the code. The IDE does a better job in seconds than you can in weeks, trust me. If you want to see the code it wrote, go look at the DataSet1.Designer.vb file

What you have to do now is read up about MVC architecture and understand why it is good, and how the DataSet/TextBox implement it

 

[cjard]

PS you asked for a good tutorial on this stuff.. see DW2 in my sig, section Creating a Simple Data App

The other thing i noted from reading your posts; you dont seem 100% on with OO concepts and VB here is NOT going to help because it makes the very stupid confusion between an Instance and a Type. The problem is that when you drop a dataset on a form, you end up with an object of type MyDataSet and the instance is also called MyDataSet

You'll do yourself a lot of favours if you change that instance to be called _myDataset..

Dim _myDataSet as MyDataSet = New MyDataSet()

This is an instance:

_myDataSet.SomeTable 'it's a property accessor of an instance of a SomeTableDataTable object

These are types:

MyDataSet.SomeTableDataTable
MyDataSet.SomeTableRow


-

Allowing this confusion between types and instances (or read another way, allowing the coder to Dim variables that have names the same as types, and keywords) is one of the most retarded things modern vb does and it should have been dumped years ago.