About Stephen Jackson Expertise I can help with questions regarding VB.NET syntax and object references, with data interfaces and with the design and creation of robust, data aware object classes. I can also be very helpful with creating distributable applications and provide tricks and tips on .msi creation. I also have extensive experience in designing SQL Server Databases and interfacing them with VB.NET. I try to avoid web specific questions, as that is an area of expertise all its own.
Experience
Experience in the Area: I have been a programmer in Visual Basic since version 1.0 and have worked with VB.NET (which is infinitely more powerful than previous versions) since its initial release and SQL Server, both as a corporate IT professional and professional consultant. I first wrote Basic in 1976 on a TRS 80 and have worked in Visual Basic 1.0 and every subsequent release of Microsoft Visual Basic. I worked for over 7 years as a Senior Level Consultant in the area and currently hold a Project Manager position in IT.
Education and Credentials: MBA in Econometrics, 1983, University of Memphis.
BBA in Financial Management, 1982, Fogelman College of Business and Economics, University of Memphis. Microsoft Certified Professional
Areas of Special Expertise:
My specialty is the design of Object Oriented Solutions with robust, data aware object classes. I generally avoid the classic ‘Three Tier’ model as I find it redundant and cumbersome to maintain. I also specialize in the creation of ‘User Friendly’ User Interfaces which help lessen the need for user training and help prevent user error. I work best with Windows Forms based applications, and while I do work in C# as well, I prefer to limit my questions here to Windows Forms based applications created in Visual Basic.NET and SQL Server. I wil also address questions relating to the distribution and installation of Windows Forms based applications created in VB.NET.
Expert: Stephen Jackson Date: 5/8/2008 Subject: Problems updating and deleting in VB.Net 2003
Question QUESTION: Ok I have a very simple database of two tables. So when I made my form in VB I used a view that connected the two tables to create my data adapter and dataset. However, it told me that it was not able to create an update or a delete SQL statement. So now that I'm coding it gives me this error, "Additional information: Update requires a valid InsertCommand when passed DataRow collection with new rows." Here is the code pertaining to the connection of the database and for the add and delete buttons:
'conWork
'
Me.conWork.ConnectionString = "Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Engine Type=5;Jet OLEDB:System databa" & _
"se=;Jet OLEDB:Global Bulk Transactions=1;Data Source=""H:\School\Capstone\Wedding" & _
"db.mdb"";User ID=Admin;Provider=""Microsoft.Jet.OLEDB.4.0"";Jet OLEDB:Don't Copy Lo" & _
"cale on Compact=False;Jet OLEDB:SFP=False;Jet OLEDB:Encrypt Database=False;Jet O" & _
"LEDB:Registry Path=;persist security info=False;Jet OLEDB:Compact Without Replic" & _
"a Repair=False;Jet OLEDB:Global Partial Bulk Ops=2;Mode=Share Deny None;Jet OLED" & _
"B:Create System Database=False"
'
'daWork
'
Me.daWork.SelectCommand = Me.OleDbSelectCommand1
Me.daWork.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "tblWork", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("fldVendor$TaxID", "fldVendor$TaxID"), New System.Data.Common.DataColumnMapping("TaxID", "TaxID"), New System.Data.Common.DataColumnMapping("fldID", "fldID"), New System.Data.Common.DataColumnMapping("fldStartDate", "fldStartDate"), New System.Data.Common.DataColumnMapping("fldEndDate", "fldEndDate"), New System.Data.Common.DataColumnMapping("fldFinishDate", "fldFinishDate"), New System.Data.Common.DataColumnMapping("fldDesc", "fldDesc"), New System.Data.Common.DataColumnMapping("fldCost", "fldCost"), New System.Data.Common.DataColumnMapping("fldCheckNumber", "fldCheckNumber"), New System.Data.Common.DataColumnMapping("fldDatePaid", "fldDatePaid"), New System.Data.Common.DataColumnMapping("fldCategory", "fldCategory"), New System.Data.Common.DataColumnMapping("Address1", "Address1"), New System.Data.Common.DataColumnMapping("Address2", "Address2"), New System.Data.Common.DataColumnMapping("City", "City"), New System.Data.Common.DataColumnMapping("State", "State"), New System.Data.Common.DataColumnMapping("Zipcode", "Zipcode"), New System.Data.Common.DataColumnMapping("VComments", "VComments"), New System.Data.Common.DataColumnMapping("Rating", "Rating"), New System.Data.Common.DataColumnMapping("Phone1", "Phone1"), New System.Data.Common.DataColumnMapping("Phone2", "Phone2"), New System.Data.Common.DataColumnMapping("FirstName", "FirstName"), New System.Data.Common.DataColumnMapping("LastName", "LastName")})})
'
'OleDbSelectCommand1
'
Me.OleDbSelectCommand1.CommandText = "SELECT tblWork.[fldVendor$TaxID], tblVendor.TaxID, tblWork.fldID, tblWork.fldStar" & _
"tDate, tblWork.fldEndDate, tblWork.fldFinishDate, tblWork.fldDesc, tblWork.fldCo" & _
"st, tblWork.fldCheckNumber, tblWork.fldDatePaid, tblWork.fldCategory, tblVendor." & _
"Address1, tblVendor.Address2, tblVendor.City, tblVendor.State, tblVendor.Zipcode" & _
", tblVendor.VComments, tblVendor.Rating, tblVendor.Phone1, tblVendor.Phone2, tbl" & _
"Vendor.FirstName, tblVendor.LastName FROM (tblWork INNER JOIN tblVendor ON tblWo" & _
"rk.[fldVendor$TaxID] = tblVendor.TaxID)"
Me.OleDbSelectCommand1.Connection = Me.conWork
Private Sub mnuFileNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuFileNew.Click
Dim addNewWorkForm As New frmAdd
If addNewWorkForm.ShowDialog = DialogResult.OK Then
addNewWork()
End If
End Sub
Private Sub addNewWork()
Dim newDataRow As System.Data.DataRow
newDataRow = DsWork1.tblWork.NewRow
newDataRow.Item("fldStartDate") = newStartDate
newDataRow.Item("fldEndDate") = newEndDate
newDataRow.Item("fldCategory") = newCategory
newDataRow.Item("fldDesc") = newDesc
newDataRow.Item("fldCost") = newCost
newDataRow.Item("fldVendor$TaxID") = newTaxID
DsWork1.tblWork.Rows.Add(newDataRow)
daWork.Update(DsWork1)
End Sub
Private Sub mnuFileDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuFileDelete.Click
If MessageBox.Show("Are you sure you want to delete this Work Form?", "Confirm", MessageBoxButtons.YesNo) = DialogResult.Yes Then
currManager.RemoveAt(currManager.Position)
daWork.Update(DsWork1)
currManager.Refresh()
End If
End Sub
'Just in case this has anything to do with it my .Fill It works perfectly though.
Private Sub WorkForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
daWork.Fill(DsWork1)
currManager = Me.BindingContext(DsWork1, "tblWork")
End Sub
ANSWER: Jenny,
Hmmmmm a good question and I believe the issue here is not with the VB.NET part but rather in the SQL statement which creates the query. For VB.NET to properly create the update and delete objects, the underlying query must be editable and allow these functions in the database. My guess is that the query simply is not editable.
A good way to check this would be to simply create a view in SQL with this SQL statement and manually check to see if it allows updates and deletes. My guess is that it will not. This might simply be a keying issue (the foreign key in the second table must be a primary key in the first and the second table must also have a primary key, for example). If you can send me the SQL underlying it, I might be better able to help.
Thanks and I hope this is helpful!
Stephen Jackson
---------- FOLLOW-UP ----------
QUESTION: I really appreciate your time helping me.
fldID is primary key of the first table, and fldVendor$TaxID is the foreign key made to match up to TaxID, the primary key of the second table. I know its weird but I named all my fields of the first table with fld, and the fields from the second table are just the names without fld proceeding it. This is the problem the data adapter wizard gives me:
The original query has a select list that has columns from multiple tables. The statement cannot be generated automatically for this type of query.
It says that for the insert, update, and delete command.
Answer Jenny,
I believe I see what may be the issue here (though I am not in a position to construct a model and test it, regrettably). I believe the engine which creates the required data objects may require the primary key of TaxID to not only have identical data types and sizes, but to have exactly the same name. I have run across this before, and it is one of those assumed 'practices' that some of the processes rely on. I belive that if you name the primary and foreign key identically, this may work. I should also mention that I have often had trouble with this process of having a form automatically create an updatable query data object and handle it all correctly. Generally, I avoid this by calling the tables separately, but the extra development time is probably not worthwhile here.
Try renaming the foreign key as tblWork.TaxID and see if this helps. If this is not possible then I am honestly not sure how to proceed. Please let me know how this works. We achieve in this business often by sheer persistence! :c)
