Visual Basic

Re: How to insert an ID number to another table column

30-Aug-12 20:56

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.

Wayne Gaylard30-Aug-12 22:24

Wayne Gaylard

30-Aug-12 22:24

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

SQL

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);
    	//add other parameters here from text boxes
        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.

Re: How to insert an ID number to another table column

garyu8730-Aug-12 23:09

Re: How to insert an ID number to another table column

30-Aug-12 23:09

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.

Wayne Gaylard30-Aug-12 23:19

Wayne Gaylard

30-Aug-12 23:19

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

Re: How to insert an ID number to another table column

Simon_Whale30-Aug-12 23:20

Simon_Whale

30-Aug-12 23:20

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

Re: How to insert an ID number to another table column

garyu8731-Aug-12 0:12

Re: How to insert an ID number to another table column

31-Aug-12 0:12

Thank you for the example and explaination its working. Smile | :)

modified 31-Aug-12 6:19am.

Wayne Gaylard31-Aug-12 0:19

Wayne Gaylard

31-Aug-12 0:19

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

Re: How to insert an ID number to another table column

garyu871-Sep-12 20:22

Remove controls from a form using For Each

1-Sep-12 20:22

Thanks for the pointer already notice that. Smile | :)

Steven St. John30-Aug-12 5:30

30-Aug-12 5:30

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?

ChandraRam30-Aug-12 8:03

ChandraRam

30-Aug-12 8:03

When deleting from a collection, it is best to start at the end and work backwards... count downto 0 or some such thing.

Steven St. John30-Aug-12 12:23

30-Aug-12 12:23

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.

kureigu maikeru31-Aug-12 2:33

kureigu maikeru

31-Aug-12 2:33

you cannot delete the picture boxes if the picture boxes is inside the controls of controls Smile | :)

Steven St. John31-Aug-12 4:15

31-Aug-12 4:15

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?

Simon_Whale31-Aug-12 4:43

Simon_Whale

31-Aug-12 4:43

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
  'do stuff
 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

ChandraRam31-Aug-12 8:18

ChandraRam

31-Aug-12 8:18

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

Simon_Whale31-Aug-12 9:04

Simon_Whale

31-Aug-12 9:04

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

Steven St. John1-Sep-12 3:52