Denaes

I'd have the program read all of the data from file into a dataset entirely (or in batches if its too much information) and then use the dataAdapter.update method to update more than one record at a time. I don't know if behind the scenes, its really calling 1,000,000 INSERT statements. That may be the case. Thats how I'd do it with my oleDB. I'm not sure about MSDB.

Public Sub CheckSave(ByVal textbox As TextBox) MsgBox("Initiating Save") If dstDatabase.HasChanges Then Dim dstChanges As New DataSet Try dstChanges = dstDatabase.GetChanges Catch ex As Exception textbox.Text = "Error: " & vbCrLf & ex.ToString End Try If dstChanges.HasErrors Then dstChanges.RejectChanges() Else Try dadAppUser.Update(dstDatabase, "AppUser") dadArea.Update(dstDatabase, "Area") dadCustomers.Update(dstDatabase, "Customers") dadCustomerStatus.Update(dstDatabase, "CustomerStatus") dadCustomerType.Update(dstDatabase, "CustomerType") dadInstructors.Update(dstDatabase, "Instructors") dadSchedule.Update(dstDatabase, "Schedule") dadScheduleToInstructors.Update(dstDatabase, "ScheduleToInstructors") dadtblState.Update(dstDatabase, "tblState") dadUserLevel.Update(dstDatabase, "UserLevel") Catch ex As Exception textbox.Text = "Error: " & vbCrLf & ex.ToString End Try End If dstChanges.Dispose() End If End Sub Its basically just checking for changes, if there are changes, moves them to another dataset, then processes the changes back to the database through the dataAdapters. I did the passing of the text box so the error would appear in a textbox if there was an error, to make it easier to troubleshoot. Really, all but two tables delete, update and insert perfectly. Its just this table and another table with a relationship that have this error.

Private Sub dadScheduleSetup() 'Delete SQL Statment Const STR_SQL_Schedule_DELETE_ID As String = "DELETE FROM Schedule WHERE ScheduleID=?" 'Select SQL Statement Const STR_SQL_Schedule_SELECT_ALL As String = "SELECT * FROM Schedule" 'Insert SQL Statement Const STR_SQL_Schedule_INSERT As String = "INSERT INTO Schedule " & _ "(ScheduleID, AreaID, CustomerID, AppUserCreate, AppUserUpdate, [Date], StartTime, " & _ "EndTime, ActualStartTime, ActualEndTime, Price, AmountPaid, Notes) " & _ "VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)" 'Update SQL Statement Const STR_SQL_Schedule_UPDATE As String = "UPDATE Schedule SET " & _ "AreaID = ?, CustomerID = ?, AppUserCreate = ?, AppUserUpdate = ?, " & _ "[Date] = ?, StartTime = ?, EndTime = ?, ActualStartTime = ?, ActualEndTime = ?, " & _ "Price = ?, AmountPaid = ?, Notes = ? WHERE (ScheduleID = ?)" 'Schedule DataAdapter Commands Dim cmmScheduleSelect As New OleDbCommand(STR_SQL_Schedule_SELECT_ALL, cnnData) Dim cmmScheduleDelete As New OleDbCommand(STR_SQL_Schedule_DELETE_ID, cnnData) Dim cmmScheduleInsert As New OleDbCommand(STR_SQL_Schedule_INSERT, cnnData) Dim cmmScheduleUpdate As New OleDbCommand(STR_SQL_Schedule_UPDATE, cnnData) Dim prmSQLDeleteSchedule, prmSQLInsertSchedule, prmSQLUpdateSchedule As OleDbParameter 'Initialize and instantiate Data Adapter dadSchedule = New OleDbDataAdapter(STR_SQL_Schedule_SELECT_ALL, cnnData) 'Set data adapter command properties With dadSchedule .SelectCommand = cmmScheduleSelect .DeleteCommand = cmmScheduleDelete .UpdateCommand = cmmScheduleUpdate .InsertCommand = cmmScheduleInsert End With 'Add Delete command paremeters prmSQLDeleteSchedule = dadSchedule.DeleteCommand.Parameters.Add( _ "@ScheduleID", OleDbType.Integer, Nothing, "ScheduleID") prmSQLDeleteSchedule.Direction = ParameterDirection.Input prmSQLDeleteSchedule.SourceVersion = DataRowVersion.Original 'Add Update command paremeters With cmmScheduleUpdate.Parameters .Add("@AreaID", OleDbType.Integer, Nothing, "AreaID") .Add("@CustomerID", OleDbType.Integer, Nothing, "CustomerID") .Add("@AppUserCreate", OleDbType.VarChar, 25, "AppUserCreate") .Add("@AppUserUpdate", OleDbType.VarChar, 25, "AppUserUpdate") .Add("@Date", OleDbType.VarChar, 10, "Date") .Add("@StartTime", OleDbType.VarChar, 4, "StartTime") .Add("@EndTime", OleDbType.VarChar, 4, "EndTime") .Add("@ActualStartTime", OleDbType.VarChar, 4, "ActualStartTime") .Add("@ActualEndTime", OleDbType.VarChar, 4, "ActualEndTime") .Add("@Price", OleDbType.VarChar, 10, "Price") .Add("@AmountPaid", OleDbType.VarChar, 10, "AmountPaid") .Add("@Notes", OleDbType.VarChar, 250, "Notes") End With prmSQLUpdateSchedule = dadSchedule.UpdateCommand.Parameters.Add( _ "@ScheduleID", OleDbType.Integer, Nothing, "ScheduleID") prmSQLUpdateSchedule.Direction = ParameterDirection.Input prmSQLUpdateSchedule.SourceVersion = DataRowVersion.Original 'Add Insert command paremeters With cmmScheduleInsert.Parameters .Add("@ScheduleID", OleDbType.Integer, Nothing, "ScheduleID") .Add("@AreaID", OleDbType.Integer, Nothing, "AreaID") .Add("@CustomerID", OleDbType.Integer, Nothing, "CustomerID") .Add("@AppUserCreate", OleDbType.VarChar, 25, "AppUserCreate") .Add("@AppUserUpdate", OleDbType.VarChar, 25, "AppUserUpdate") .Add("@Date", OleDbType.VarChar, 10, "Date") .Add("@StartTime", OleDbType.VarChar, 4, "StartTime") .Add("@EndTime", OleDbType.VarChar, 4, "EndTime") .Add("@ActualStartTime", OleDbType.VarChar, 4, "ActualStartTime") .Add("@ActualEndTime", OleDbType.VarChar, 4, "ActualEndTime") .Add("@Price", OleDbType.VarChar, 10, "Price") .Add("@AmountPaid", OleDbType.VarChar, 10, "AmountPaid") .Add("@Notes", OleDbType.VarChar, 250, "Notes") End With End Sub This is the procedure I use to setup the data adapter. dadSchedule.Update is what I use to call the update/insert/delete The problem I have is when I try to update/insert a record on the table from a datagrid. Delete works fine. I basically have one of these set up for each data adaptor in the database (about 9 right now). Each table has its own dataAdaptor, which (for me) confuses the relationship issue a little. I'd really appreciate any help.

Can I just replace the SQL Server namespace (SQLDB is it?) with oleDB and most everything would work fine? I got a hunking huge book by APress, which covers both SQLServer and oleDB, but its first priority is always SQLServer. Often times I'm not sure if data adapters work the same with their .update and 4 built in commands with their parameters or whatnot. If its all compatable, just in a different wrapper to optimize for SQLServer, I really don't mind. If its fairly different, then thats a pretty big problem for trying to sort through what sample code will work with oleDB and what isn't. The people I work for insist on my database being an actual database and just ignore me when I mention XML. In truth, it wouldn't be too hard to switch, would it? Load the oleDB database into a dataset, then save it as XML? Personally I think an Access database is wasted on what I'm doing. I have like 15 tables, 6 relationships and only like 20 records (mostly testing my application entering data) and the database is over a megabyte already. When I get a little app to generate a few thousand records, I'll see exactly if most of it is start-up bloat or if its proportional. Do you know if XML is any faster/slower than .mdb?

so you need to ship SQL Server 2000 with your program? Its $2,000-$6,000 per liscence from what I see. True, that may be worth it for true server situations like a corporation serving 100's of computers. But then it seems that oleDB is going to be the standard for normal programs which you distribute to clients (ie, just datastorage). Are there any books that deal with ADO.Net and oleDB then? It seems that every book just on ADO.Net focuses mostly on SQL Server and only oleDB as a sidenote.

Can you use SQL server without buying it? Can I make a program using SQLServer rather than oleDB and distribute it as is? I guess that would mean that I'd need to create the database with code then...

Most of the books and tutorials seem to highlight it. I know its a VERY expensive program. Is SQL Server required to use it in a VB.Net program? So far, from what I can tell, oleDB/Access seems to be better, but maybe its just what I'm used to.

Can you use that like a full select statement? "itemID<5 OR itemID>100" That would cover really simple selects, but how about more complex ones, like when you want to populate another datatable with a combined relation of two tables? Say I wanted to join MemberName and MemberBenefits at the MemberBenefitID column to have rows containing the member and their benefits? I'm trying to cut back on querying the database for information I'm using when possible.

I havn't seen how to do it, but based on what I've read about datasets, it IS a replication of a database (full or partial) and this should be possible. Anyone know?

Error: System.Data.OleDb.OleDbException: You cannot add or change a record because a related record is required in table 'Schedule'. at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping) at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable) at SchedulerWorking.modProcedures.CheckSave(TextBox textbox) in C:\Documents and Settings\MiniSlink\Desktop\Schedule Program\SchedulerWorking\modProcedures.vb:line 72 Thats the error I'm getting by trapping the dataAdaptor.Update method with a Try...Catch statement. Now, I understand why its doing it. I have a relationship in the database between the two tables. The related column is the "ScheduleID". Now, I have a schedule with an ID of 0. I also set the ScheduleID on this table to 0 - the relation. I get the error about it being in a relationship, even though I made a valid choice. When I use Access, I can just type in all the fields and the number 0 for the relationship and it works just dandy. I'm wondering what I should do to get around this? Should I not have relationships IN the database itself, but create the relationships as I use them by using JOINs in my SQL Select statements? Or is there another easier way to do this?

From what I've seen they come with a 32 or 64mb card. Seeing as some are advertised for taking pictures, movie clips and playing MP3's, I don't see how even the much vaunted (and very expensive upgrade) 256mb flash card could handle that, let alone the standard 64mb card. Then again, I thought the concept of 3/4ths of the MP3 players were retarded. Yeah, pay 150 bucks to be able to hold a CD or two worth of music. 64mb pretty poor. When I got my mp3 player, I was all over the place with shopkeepers looking at me like I was stupid "What do you mean you want to hold more than an albums worth of music?" For 30 bucks more I got an Archos MP3 player/recorder with 10gb of hard drive space - which saved one windows installation when the OS AND CDRom got messed up and aided in filesharing between my PC and my wifes Mac. Just like then, now I'm sure I can wait for the version of a Palm/Pocket PC with hard drive space on it. The only reason why it wouldn't be out is that the companies have aggreements with the flash card makers not to include storage space. I saw the Sony one, the Clicke or whatever, had an option for a 550 dollar flash stick which was 1gig. Its only a matter of time until someone realizes the need for a small PC - not a super duper Cell Phone/Camera/MP3 player with quasi PC inclinations

But they don't have any sort of hard drive. I wonder if my USB Flash Stick thingie would work. Well, despite how powerful they may be, spending 500 bucks on something without a hard drive then having to go out and bust out on 100+ dollar flash cards doesn't seem like a great deal. They must have some sort of harddrive. I assume the OS is on a firmware. I know a tablet is just a laptop that doesn't flip open and has a touch sensitive monitor rather than a keyboard. A little lighter and pretty cool. I wouldn't get a tablet that wasn't a combo that either detaches from its base or does some sort of transformer flip deal. I'm going to check out those Palm PC sites, but I think a Palm PC isn't what I thought it was or need. I think a detachable tablet would fare much better as I always have a messenger bag with me anyways.

Wait a minute. I just checked out Dell and saw that they had 64mb memory cards. ? Is this portable media for it, like a floppy or zip disk is for a desktop? If thats the entire storage capacity (no hard drive/internal flash memory) If windows CE or Windows for mini 2003 (whatever its called) isn't really windows or windows compatable, and it isn't a mini PC, then whats its angle? Why is it going for so much more money than a palm or Sony Clique? I'm picturing 500 bucks and getting a mini PC, not just another version of a palm pilot. Can it even read PDFs? Not like it would matter all that much if it doesn't have any storage capacity on its own. You'd have to spend another hundred bucks upgrading to a flash memory card that could hold a PDF (most are 30-100mb) I'm starting to think that a Palm PC is less than ideal unless I'm missing something. The only upbeat I can see is if you spend another 200-300 bucks, it can be wireless and most likely network with your PC. I don't need a datebook, I need something to increase productivity (typing notes or code, reading e-books, etc) while I'm away from my machine. It doesn't sound like what I'd been lead to believe. Oh well, maybe in a few years or something.

Maybe there is one of those code editors with scripts for VB.Net that would run on a PPC. Can a PPC run applications, or is it not windows depsite having the windows name?

I know you can make .Net apps to run them, but how about the Design Environment? I don't think you're going to get a fully fledged version of VB.Net running on a 400mhz handheld device, but anything like a lite version of .net? Even if it was just a text editor with intelisense I'd be happy. Maybe even a text editor which knew the keywords to change colors or whatnot... I have a laptop, but I can't really bring it to my job - as its at a front desk. I often have to leave the desk and can't lock down my laptop or put it away. Well I could, and I'd probobly be told not to bring it in if it interferes with my work. But I'm getting an advance for a program I'm writing and wondering if a handheld would make a sound investment - with one of those flip/fold out keyboards to type with. As it is, I just play with my Game Boy Advance SP at work for lack of anything better to do.

wow. I'm surprised. I guess I'm going into uncharted territory here :p I'll be sure to make a simple tutorial for the rare event of relational databases (:D) in .Net for those who want to follow in my brave footsteps :eek: I'm just playing. But apparently if nobody has a clue, I'll post a link if I find one or I'll make a tutorial once I piece it together.

It seems like it should be easy. If you have two tables (orders and items) you should be able to make a data relation like this: dim drOrderItems as new datarelation("OrderItems", dsData.Order.ItemID, dsData.Items.ItemID) Something like this. In the book I got, it gets kinda confusing because its both oleDB (Access) and sql server, which are both different. It looks like the example in book is creating 2 seperate arrays of datacolumns to be placed into the datarelation. This book has like half a page of preamble in the Relationship chapter, then goes into a 4 page code example, which isn't very well documented. Often times instead of taking things one step at a time, I'd run through an example and after wondering why it was so long for something so simple, I find out he declared each variable 5 different ways and showed 4 different ways to use it. This code example on relations jumps right in on multiple ways to declare it, some looking really over complicated (it doesn't tell you why you're doing it differently or in what circumstances you should do so) and then continues on to to a few other things, like updating the relation, using it, etc. I tend to pick up concepts quickly, but I'd rather see 8 code examples of 8 different methods - each explained - than 1 code example of 8 different methods. Are there any no frills examples or tutorials out there? Just basically how to create a datarelation. Then a seperate step on how to retrieve data from it. Then a seperate step on how to put data into it. I'd be greatly appreciative for some help

By open and close early you mean open it, get all the data like into a dataset, then close it, right? You can't just open and close it early if you're constantly manipulating the database directly, you'd have to open it up every time you need to read/change/add to it

You may have saw my other post on wether to open/close a connection in a single sub or on program wide basis. This one is more of a question about which methods of datacontrol are going to be the better route to use. Currently I'm using the direct Connection method. I open a connection and use SQL to make it jump through hoops. I have a book called "Database Programming with VB.Net" by A Press/Carsten Thomsen. Its a good book, very thurough. But I'm also in the middle of a project, I can't really read the whole book. My boss would be pissed, though it really would benifit the whole project I'm sure. So I'm doing amateur hacks like this: Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click Dim MyConnection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & Application.StartupPath & "\Customers.mdb") MyConnection.Open() Dim MyCommand As New OleDbCommand("DELETE FROM " & strTable & " WHERE " & strItem & " = '" & lstSelect.GetItemText(lstSelect.SelectedItem) & "'", MyConnection) MsgBox("There was " & MyCommand.ExecuteNonQuery() & " records affected") MyConnection.Close() MyCommand.Dispose() itemChanged() End Sub I saw in the book Data Adapters had some neat methods of placing SQL statements into the Adapter and allowing you to just call them like this: daConnection.Delete, saying you put the proper delete SQL statement in the data adaptor. I know and love DataSets. I use them with XML often. But I'm just not sure about this. This is a relational database for a fairly large project. I think what I'm half afraid of someone doing is making some changes (which would go into the dataset) and then crash their computer before they get to a point were the data should be saved to the database. It would seem redundant to use a dataset and save to the database after every update. I think if I can figure out how to use the dataset in a relational way, like creating a relational table Customer_Orders, which is a combination of Customer and Orders. I think what I'm asking for mostly is advice. This isn't going to be an enterprise level program, but it is fairly big for a one person task. Also, I do things in code. Its pretty and nice to do things with the visual aspect, but the second you can't do something visually, you're stuck and have to go and do it in code anyways. I'd rather know whats going on in the back room and how to tweak it to do what I need :)

When should you open and close connections to a database? Should you open a connection and keep it open until you close the program (on a program that is going to constantly keep accesing the database)? on one form, I have a code to edit, delete, update and make a new record. Each procedure starts with opening the connection and then closes it after its over. This also seems to cause some lag when click on the combobox to change tables. Is it ok to keep a connection open? This is the sort of thing I'm doing: Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click Dim MyConnection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & Application.StartupPath & "\Customers.mdb") MyConnection.Open() Dim MyCommand As New OleDbCommand("DELETE FROM " & strTable & " WHERE " & strItem & " = '" & lstSelect.GetItemText(lstSelect.SelectedItem) & "'", MyConnection) MsgBox("There was " & MyCommand.ExecuteNonQuery() & " records affected") MyConnection.Close() MyCommand.Dispose() itemChanged() End Sub Obviously I'm taking a direct rout with this. Just feeding SQL directly through the connection. The variables, in this case, are received from a combo box (the table to delete from) and the selected item of a list box (in this case, the one you want to delete). As always, thank you for your input :)

I didn't change the structure of the dataset, I said I added the 7 tables from the dataset to the schema and everything dissapeared. The schema works with 1 table, but not when you add all 8 tables to it. I'm following these steps out of the book microsoft put out to teach you how to do this. Actually since then (it was a while ago) I had to move on with the project. So I just used SQL statements and rigged my own in VB.Net code relations, which was a huge pain, having to grab one field and search another table for that field to grab the other tables... I just got a book by A! (apress?) which looks a lot more complete and thurough than that microsoft book did.

Thank you very much. The problem was that I wasn't using the quotes. I guess I lost track of the ' when I was using the value of the textbox. Now it works perfectly :)

I have a table called CustomerStatus in a database (.mdb), which has two Field Names: CustomerStatusID and CustomerStatus. I was looking into doing an INSERT command, but I keep getting errors. Here is my code: Dim MyConnection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & Application.StartupPath & "\Customers.mdb") MyConnection.Open() Dim MyCommand As New OleDbCommand("INSERT INTO CustomerStatus (CustomerStatus) VALUES (" & txtAdd.Text & ")", MyConnection) MsgBox("There was " & MyCommand.ExecuteNonQuery() & " records affected") MyConnection.Close() MyCommand.Dispose() I'm only INSERTING to the CustomerStatus field because CustomerStatusID is an Autonumber. Is this why I'm getting an error? Should I drop the Autonumber and make the CustomerStatusID field a number instead and add an incrimented number instead?

This looks like some pretty good stuff. I'm going to have to take a good look at this for my current project :D

I just got a better database book also ("Database Prorgramming with VB.Net" by A!). I had ADO.Net by Microsoft, which was the worst computer book I have on my shelf now. I'll look into the fillschema method. I'm thinking its just less complex to just make the tables in access and do the relationships in .Net.