|
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
|
|
|
|
|
Yes, that's essentially the solution I'm using now.
Thank you everyone.
|
|
|
|
|
Hello!
Got a little problem and i'm sure that you guys could help me easily.
I have to List-Controls 1. CONTACT-LIST 2. VIP-LIST
You could add contacts to the vip-list per drag&drop.
Next step is, that i would like to delete vip-list-entries when i just drag them out of the vip-list.
How could i do that? Because the entry should be deleted when left mouse button goes up and the mouse is not over the vip-list. Because the event "PreviewMouseLeftButtonUp" could be fired everywhere (outside of the applicationwindow, somewhere else on my applicationwindow) i don't know how to handle it.
I hope you understand my problem?!
Thanks for your help!
|
|
|
|
|
We got a WPF-forum; I haven't used the tech, but..
If it works like WinForms, there will not be any event that tells you it's dropped "outside" of your application. We worked around the limitation once by implementing the dragdrop to the shell, but that was a dirty and expensive hack that simply didn't work.
Simplest way is to "not use" drag and drop, and simply have a button that deletes the selected item. You will not get notifications from other apps if the item is dropped there.
Bastard Programmer from Hell 
if you can't read my code, try converting it here[^]
|
|
|
|
|
Try using the DragLeave event. It won't cancel the drag-drop, but it will allow you to remove the entry from the list once the mouse leaves the control.
|
|
|
|
|
I want to build a module to combine multiple text file in a .csv file by using vb.net.
My text file data like this:
(T1.txt)
UnitCode|Qty
001|56
002|45
003|100
004|78
005|67
(T2.txt)
UnitCode|Qty
001|78
002|166
003|10
005|12
I want the output like this:
(Output.csv)
UnitCode,T1,T2
001,56,78
002,45,166
003,100,10
004,78,
005,67,12
Anyone expert can help me?
|
|
|
|
|
You need to think this through logically. Use a StreamReader to open both files, use String.Split on each line of each text file and add it to a StringBuilder on each iteration, using a comma to separate each item. When you have finished traversing the text files, you can save the csv file using a StreamWriter.
When I was a coder, we worked on algorithms. Today, we memorize APIs for countless libraries — those libraries have the algorithms - Eric Allman
|
|
|
|
|
|
Just a simple sample
Sub Main()
Dim dict = New Dictionary(Of String, List(Of Integer))
Dim unitCode = String.Empty
Dim qty = 0
For Each file In GetFiles()
For Each row In file
unitCode = GetUnitCode(row)
qty = GetQuantity(row)
If dict.ContainsKey(unitCode) Then
dict(unitCode).Add(qty)
Else
Dim qtyList = New List(Of Integer)
qtyList.Add(qty)
dict.Add(unitCode, qtyList)
End If
Next
Next
End Sub
Hope that helps!
|
|
|
|
|
I was thinking of basically the same solution.
stevelk,
At then end of the process you need to write the contents of the dictionary out and you will have the desired combined results.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
