|
You're welcome.
|
|
|
|
|
Hello everybody,
I'm trying to add records to the 'Customer' and the 'Item' classes of my DataContext. The Sub is part of the (partial) DataContext class. Adding new records seems to be a simple thing when I believe all the sources I read, but I'm obviously still wrong with something: 'SubmitChanges' doesn't update the underlying database... This despite the fact that all the DB records (old & new) are printed to the screen before, i.e. seem to be part of the 'Inserts' list. BTW, there are no exceptions thrown.
Can someone please tell me what's wrong with that code?
Public Sub InsertTest()
Dim cust As New Customer ' add new customer
With cust
.First_Name = "Paul"
.Last_Name = "Miller"
End With
Customer.InsertOnSubmit(cust)
Dim item1 As New Item ' add new product item
With item1
.Item_Name = "DVD"
.Item_Price = 19.75
End With
Item.InsertOnSubmit(item1)
Dim item2 As New Item ' add new product item
With item2
.Item_Name = "BlueRay"
.Item_Price = 29.75
End With
Item.InsertOnSubmit(item2)
' ONLY FOR DEBUGGING
' print changed objects
Dim mySet As System.Data.Linq.ChangeSet = Me.GetChangeSet()
Dim liste As Object = mySet.Inserts
For Each entry As Object In liste '<----- prints existing & added entries
Select Case True
Case TypeOf (entry) Is Item
Console.WriteLine(entry.Item_Name)
Case TypeOf (entry) Is Customer
Console.WriteLine(entry.last_name)
Case Else
Console.WriteLine("Objekt vom Typ '{0}'", entry.GetType.Name)
End Select
Next
SubmitChanges() '<---- isn't writing changes to database
Console.ReadLine()
End Sub
modified 3-Sep-12 17:46pm.
|
|
|
|
|
Finally I found out that the underlying database actually did get updated...
It's just not the same database that I can see in the ServerExplorer – all the changes have been written into a local copy of the database, which I found in the "Debug" directory.
|
|
|
|
|
Hi, am not sure if this should be a vb.net or visual studio question, if I am posting at the wrong place please pardon me.
I am currently updating my VB code to VB.NET, it’s a learning process for me and I am trying to conform all my code to 100% VB.NET . I have set up ‘Option Strict’ and that helps with a lot of bad habit. I was wondering if I can do the something for flagging VB6 functions. Visual Studio automatically references the VB6 library which is good if you don’t have time when migrating, however I have time and would like to abstain from VB6 or toggle the library ON/OFF when needed.
Thank you in advance,
-Jay
|
|
|
|
|
Your question doesn't make any sense at all.
What are you talking about when you say "VB6 library"??
No, there is no way to toggle "Option Strict" on and off at the method level. It's the entire file/project or nothing.
|
|
|
|
|
One can enable ‘Option Strict’ per *.vb file by adding “Option Strict On” on the first line of the code.
What I was trying to do is to be able to do same with Microsoft.VisualBasic.dll library. When I start Visual Studio 2008 the Microsoft.VisualBasic.dll library is automatically referenced. I was hoping to be able to turn it off.
|
|
|
|
|
juno101 wrote: One can enable ‘Option Strict’ per *.vb file by adding “Option Strict On” on the
first line of the code.
Yeah, that's why I said "file/project"...
juno101 wrote: What I was trying to do is to be able to do same with Microsoft.VisualBasic.dll
library
Since "Option Strict" has nothing to do with the references in a project, this doesn't make any sense.
juno101 wrote: When I start Visual Studio 2008 the Microsoft.VisualBasic.dll library is
automatically referenced. I was hoping to be able to turn it off.
To what end?? All this would do is rip out VB-specific functions and classes and pretty much gut the ability to use any legacy code since it will all depend on these functions for backward compatibility. For instance, Left(), Right, Instr(), Len(), ...
|
|
|
|
|
Hi Dave, thank you very much for helping me . . . I think we are on the same page now.
Quote: To what end?? All this would do is rip out VB-specific functions and classes
That is the whole point. I am not sure how long Microsoft intends to keep support for the "VB6" functions. And instead of using Object Events that are provided in vb.net I habitually tend to use VB6 functions. If I can turn that 'off' in all the new code that I write then the new code will be independent from VB6. Additionally, I can retrain myself to only use object events. So instead of Len() I would use String.Length . . ..
As for the legacy code I can turn the VB6 library 'ON'.
|
|
|
|
|
Now that's just silly. Just don't use the legacy functions. It's that simple. Besides, that namespace is NOT used for just backwards compatibility. For instance, the VB.NET CodeDom Provider is in therem which provides access to the VB.NET compiler (can also be used to generate source code from IL!) It also provides the ApplicationServices namespace and as well as support for the My namespace.
BTW, it's not "object events". It's object oriented programming.
|
|
|
|
|
Sub Query_Insert_Test()
Try
AddQuery = "Begin Transaction "
AddQuery &= "INSERT INTO tblFoodAndBeverage(Breakfast,Lunch,Dinner,Supper,Snack,Beverage,Grocerries,Sub_Total) VALUES('" & txtBreakFeast.Text & "','" & txtLunch.Text & "','" & txtDinner.Text & "','" & txtSupper.Text & "','" & txtSnack.Text & "','" & txtBeverage.Text & "','" & txtGroceries.Text & "','" & txtSubFnB.Text & "')"
AddQuery &= "INSERT INTO tblExpenses(Date, <big>Food_And_Beverage</big>, Allowance, Total,Wallet) VALUES('" & DateTimePicker1.Value & "',(S<big>ELECT Food_And_BeverageID FROM [tblFoodAndBeverage],[tblExpenses] WHERE tblFoodAndBeverage.Food_And_BeverageID = tblExpenses.ExpensesID and tblExpenses.Date = '" & DateTimePicker1.Value &</big> "'),'" & txtAllowance.Text & "','" & txtTotalUsage.Text & "','" & txtWalletCash.Text & "')"
AddQuery &= "Commit Transaction "
cmd = New SqlCommand(AddQuery, connection)
connection.Open()
Dim Check As Integer = cmd.ExecuteNonQuery()
If Check > 0 Then
MsgBox("Record Inserted.")
connection.Close()
Else
MsgBox("Error. Record not inserted.")
End If
Catch ex As Exception
MessageBox.Show(ex.Message & " #################### " & ex.ToString)
End Try
End Sub
I need to insert the Food_And_BeverageID number to tblExpenses's Food_And_Beverage.
Is there any better query to select only the correct value and also a single row data.
Adding one record is okay but when I need to add another one it doesn't work and display error subquery doesn't allow to return multiple value.
Info the ID's is in Auto Number.
|
|
|
|
|
I see several things here that you are doing wrong. First off, you should not be concatenating strings to create your sql query, rather use Parameterised queries. Second you should use the ADO.Net Transaction to update more than one table at a time. Third, you should use the
SELECT @@IDENTITY to return the id of the last inserted row. Here is a quick example of how I would do this :-
using(SqlConnection con = new SqlConnection("yourConnectionString"))
{
con.Open();
SqlTransaction trans = con.BeginTransaction();
try
{
SqlCommand cmdFandB = new SqlCommand("INSERT INTO F&B (breakfast, lunch, dinner) VALUES (@breakfast, @lunch, @dinner);SELECT @@IDENTITY;", con);
cmdFandB.Parameters.AddWithValue("@breakfast", txtBreakfast.Text);
cmdFandB.Parameters.AddWithValue("@lunch", txtLunch.Text);
cmdFandB.Parameters.AddWithValue("@dinner", txtDinner.Text);
cmdFandB.Transaction = trans;
int FandBID = (int)cmdFandB.ExecuteScalar();
SqlCommand cmdExpenses = new SqlCommand("expenses insert query here", con);
cmdExpenses.Parameters.AddWithValue("@foodandbeverageid", FandBID);
cmdExpenses.Transaction = trans;
cmdExpenses.ExecuteNonQuery();
trans.Commit();
MessageBox.Show("Success");
}
catch (SqlException sex)
{
trans.Rollback();
MessageBox.Show("Failure" + sex.Message);
}
}
Hope this helps
[Edit]Sorry, example is in C#, but you should get the drift[/Edit]
When I was a coder, we worked on algorithms. Today, we memorize APIs for countless libraries — those libraries have the algorithms - Eric Allman
modified 31-Aug-12 4:44am.
|
|
|
|
|
Wayne Gaylard thanks but can you care to explain what is this:
Quote: int FandBID = (int)cmdFandB.ExecuteScalar();
and also is it possible to explain and show example for this to my code:
Quote: SELECT @@IDENTITY
I know identity is for auto number but i'm very new to it.
|
|
|
|
|
int FandBID = (int)cmdFandB.ExecuteScalar();
In VB this would be
Dim FandBID As Integer = CInt(cmdFandB.ExecuteScalar()) and that basically just declares an integer, and uses the return value from the ExecuteScaler[^] method to initiate this integer.
<a href="http://msdn.microsoft.com/en-us/library/ms187342.aspx">SELECT @@IDENTITY</a>[<a href="http://msdn.microsoft.com/en-us/library/ms187342.aspx" target="_blank" title="New Window">^</a>] is a standard Sql query that returns the identity column of the last inserted row that was inserted with a specific connection, so in this case the identity column of the FoodAndBeverage table.
I have linked to the MSDN pages for each relevant section.
Hope this helps
When I was a coder, we worked on algorithms. Today, we memorize APIs for countless libraries — those libraries have the algorithms - Eric Allman
|
|
|
|
|
SELECT @@IDENTITY [^]
as this MSDN article explains it contains the last identity value that is generated by the statement
Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch
|
|
|
|
|
Thank you for the example and explaination its working.
modified 31-Aug-12 6:19am.
|
|
|
|
|
Take a look at this:
garyu87 wrote: cmd1.Parameters.AddWithValue("@FNBID", txtExpensesID.Text )
Shouldn't that be
cmd1.Parameters.AddWithValue("@FNBID", Food_And_BeverageID) instead ?
When I was a coder, we worked on algorithms. Today, we memorize APIs for countless libraries — those libraries have the algorithms - Eric Allman
|
|
|
|
|
Thanks for the pointer already notice that.
|
|
|
|
|
I suspect I can solve my problem on my own, but I'm wanting to find the most efficient solution.
I have a form with several picture boxes on it as well as a handful of other controls. I would like to cycle through the controls and, if the control is a PictureBox, remove it. I tried this:
Private Sub CreateAndDisplayTableau(ByRef frm As Form)
'Destroy picture boxes on form
Dim ctrl As Control
For Each ctrl In frm.Controls
If TypeOf ctrl Is PictureBox Then
frm.Controls.Remove(ctrl)
End If
Next
End Sub
I see now that what this does is only removes half of the PictureBox. If the first PictureBox is the 4th control encountered out of 20 controls, after removal, the 5th control becomes the 4th control and, since the 4th control was already evaluated, it gets skipped.
I see that I could set the Tag property to "Mark for deletion" or something and then cycle back through and delete them that way. Or, I could use a For Next Loop and decrement the counter after removal. So I can solve this problem, I feel sure. But what is the most efficient solution?
|
|
|
|
|
When deleting from a collection, it is best to start at the end and work backwards... count downto 0 or some such thing.
|
|
|
|
|
I thank you for your response. I am biased to using For Each when I can, but maybe that's just silly prejudice. I will iterate backwards though the collection - this works.
|
|
|
|
|
you cannot delete the picture boxes if the picture boxes is inside the controls of controls
|
|
|
|
|
I'm not exactly sure what you mean though I have since realized I must call:
ctrl.Dispose
in order to destroy the picture box. Presumably I could do this whether "detaching" it from the form's control collection or not?
|
|
|
|
|
depends on how you attempt to remove the control.
if you use
For Each ctrl as Control in Me.Controls
Next
it complains at runtime that the collection has changed. but as someone else pointed out using a pointer variable and moving from the end of the list to the start i.e.
dim NumberOfControl as Integer = Me.Controls.Count
for Counter as integer = 0 to NumberOfControls step -1
if typeof ctrl is picturebox then
end if
Next
it doesn't give you any hassle like the for each approach
Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch
|
|
|
|
|
Simon_Whale wrote: for Counter as integer = 0 to NumberOfControls step -1
Shouldn't it be
for Counter as integer = NumberOfControls to 0 step -1
|
|
|
|
|
yes it should sorry I typing in between meetings on my windows phone
Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch
|
|
|
|