I am a PHP/Delphi MySQL programmer for some years, now I am going to create some code in MS SQL and VB. So I downloaded and registered MS SQL 2005 Server Express Edition and some tools like SQL Server Studio Management Express, and VB 2005 Express.
As I have some knowledge of MySQL design and programming, I have installed MS software, rather without problems. Then, using Studio Management I created new database, new table in it with some four fields (int, varchars and smallint). Then from the same tool I opened table and inserted some data, and closed this application.
Next I opened Visual Basic and in Database Explorer I set up connection to my SQL database indicating the file it is stored in (path\*.mdf). I ordered not to copy data to new file but to use original one.
Then in Data Sources I created new data source based on previously set connection. In the end I dragged and dropped my table from Data Sources into my new Form of my new Windows Application, nice grid with panel appeared. So I ran the app, I tried to put some data and then after trying to save (using floppy disk icon on panel) i got error:
Update requires a valid UpdateCommand when passed DataRow collection with modified rows.
which appears in event handler for saving in line i've indicated.
Me.LudzieTableAdapter.Update(Me.TestDataSet.ludzie) ' <-- here highlights
I tried then to add to ID field identity specification, nothing changed. Then in query editor of management console I put some code:
As some sources mentioned. I restarted Service. Nothing Changed. So this is my problem. What to change.
I use Win XP Pro SP2.
And next one question, even more important for me is the way I connect to database. I found that in Visual Basic Creating new connection in Database Explorer I indicate the file on my local drive!!! And for example if I try to open my table alongside in Management Studio Express and in my App in VB and the error raises
Cannot open user default database. Login failed.
Login failed for user 'FS7120M\m227'.
which is not a surprise for me as in VB i am using file which cannot be shared.
In MySQL I simply put address, port and so on and I could use my database in Delphi, PHP, and another management tools with no hassle. Is it possible in VB? Or it only can be used to exclusive using database?
Please inform me if such questions shall be directed to VB forum, I am not sure, and thank you for _any_ answer.
MS goes right way giving people great possibility to use and even sell small apps using their Express Editions of SQL and VB, but it is still too confusing for people who encouraged start using their apps and have such basic problems.
In SQL Server you do not connect to the file, you connect to the service. In SQL Server 2005 Express I think that you have to use the form 'computer-name\database-name'. I'm not sure, it's a long while since I installed 2005 Express, but I think it wants you to use its default name.
The problem is, that in VB 2005 EE, when I try to Add Connection from Database Explorer (using Connect to Database button), in data source I have only two choices:
MS Access Database File and MS SQL Server Database File. So both require file name to input, I presume, that I should have any other data source to choose from, am I right (a kind of network source, even if it is on my local host)?
I tried to reconfigure some services, but shall I run i.e. SQL Server Browser (which is disabled and I cannot enable it)... What step to head now? There are still to many choices to mess up with.
I've never tried it that way. I create a connection string which becomes a parameter to the connection object. A connection string looks something like this:
ConnectionStr = "Data Source=Computer-Name\\SQLEXPRESS;Initial Catalog=Database-Name;Trusted_Connection=yes;Integrated Security=SSPI;Connection Timeout=10;";
Well, I thought that in such RAD IDE as VB 2005 is i will not have to write that code, as there are plenty visual designers to connect to DB, but it looks, nowadays you still have to rely on yourself with such tasks. But what is the purpose of Database Components I mentioned before if they do not fulfill their duties?
I found that in Add Connection Panel (where Data Source and Database File Name fields are) in Advanced tab i can produce such string by selecting properties form a properties list, but still have no possibility to run connection, as I have to fill the Database File Name field to process further. My sample string though looks like:
Data Source=.\SQLEXPRESS;Initial Catalog=test;Integrated Security=True;Connect Timeout=30;User Instance=True
You can drag components to your form and VS writes code for you but, most of the time, I find it easier to write it my self and control it all in code. But maybe that's because of my write-it-all-yourself background.
I still don't understand why it's asking you for a file name; are you definitely making a SQL Server connection?