Add multiple room based on user selected

Question

0.00/5 (No votes)

See more:

Currently i doing the add room function. I m stuck at the add multiple room.

example: user select the number "10" from dropdownlist, and clicks the add. I want it to insert 10 rows into the database table. This to avoid me to add 10 times.

I have autogenerate the roomID

VB

Dim mySqlConn As MySqlConnection = New MySqlConnection("server=localhost;userid=root;password=1234;database=ace")
    Dim cmd As MySqlCommand

VB

Private Sub autoGenerate()
        Dim curValue As Integer
        Dim result As String
        Dim cmd = New MySqlCommand("Select MAX(RID) FROM ROOMS", mySqlConn)
        result = cmd.ExecuteScalar().ToString()
        If String.IsNullOrEmpty(result) Then
            result = "RI0000"
        End If

        result = result.Substring(2)
        Int32.TryParse(result, curValue)
        curValue = curValue + 1
        result = "RI" + curValue.ToString("D4")
        TextBox2.Text = result

    End Sub

My Add button

VB

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        'User select room number
        Dim i as Integer = ComboBox2.Text
        If Button1.Text = "Button1" Then
            Try
                mySqlConn.Open()
                Dim query As String
                query = "Insert into ace.rooms (RID,ROOMID,STATUS) values (@RID, @ROOMID, @STATUS) "
                cmd = New MySqlCommand(query, mySqlConn)
                cmd.Parameters.AddWithValue("@RID", TextBox2.Text)
                cmd.Parameters.AddWithValue("@ROOMID", ComboBox1.SelectedValue)
                cmd.Parameters.AddWithValue("@STATUS", "No")
                cmd.ExecuteNonQuery()

                MessageBox.Show("Success")
                mySqlConn.Close()
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            Finally
                mySqlConn.Dispose()
            End Try
        ElseIf Button1.Text = "Update" Then


        End If

What I have tried:

Currently i no idea how to loop it. Can anyone help me to code this? Thanks and appreciate.

Posted 20-Feb-16 17:15pm

Member 11800686

Updated 20-Feb-16 18:31pm

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Garth J Lancaster · Accepted Answer · 2016-02-20T18:31:00

I would create a sub-procedure (or whatever its called in VB.Net) and put this code

VB

cmd.Parameters.AddWithValue("@RID", TextBox2.Text)
cmd.Parameters.AddWithValue("@ROOMID", ComboBox1.SelectedValue)
cmd.Parameters.AddWithValue("@STATUS", "No")
cmd.ExecuteNonQuery()

in it, with the exception that into the sub-procedure, you have to pass your parameters, so you have something roughly like

VB

Private Sub  multipleRoomAdd(loopValue As Integer, cmd as MySqlCommand, tb2_Value As String, cb1_Value  As String, Status as String)
                cmd.Parameters.AddWithValue("@RID", tb2_Value)
                cmd.Parameters.AddWithValue("@ROOMID", cb1_Value)
                cmd.Parameters.AddWithValue("@STATUS", Status)
                cmd.ExecuteNonQuery()
End Sub

so that you can then do

VB

cmd = ... // As Per your code 

For addLoopIndex As Integer = 1 to i
    multipleRoomAdd(addLoopIndex, cmd, TextBox2.Text, ComboBox1.SelectedValue, "No")
End For

but, you're not out of the woods yet ! you need to think about :-

1. using LoopValue which will go from 1 to 10 (for example) to alter/form the values for @RID/tb2_Value and/or @ROOMID/cb1_Value - if you dont create new values, possibly based on the loop #, you'll end up with rows all the same, I doubt thats what you want (btw why are these two similar ?) - I'd likely use a Sub Procedure here to mutate/get the values required on each iteration

2. possibly reset the cmd parameters

so you possibly end up with

VB

Private Sub  multipleRoomAdd(loopValue As Integer, cmd As MySqlCommand, tb2_Value As String, cb1_Value As String, Status As String)

                // Use loopValue 1..n and tb2_Value from TextBox2 to get new RID value 
                newRIDValue = getNewRID(loopValue, tb2_Value) 
                cmd.Parameters.AddWithValue("@RID", newRIDValue)

                // Use loopValue 1..n and cb1_Value from ComboBox1 to get new ROOMID value 
                newROOMIDValue = getNewROOMID(loopValue, cb1_Value)
                cmd.Parameters.AddWithValue("@ROOMID", newROOMIDValue)
              
                cmd.Parameters.AddWithValue("@STATUS", Status)
                cmd.ExecuteNonQuery()

                // ? Reset cmd Parameter List
                cmd.Parameters.Reset() ??
End Sub

//TODO 
// 1 Define a sub Procedure for getNewRID that given loopValue (eg 1..10) and The TextBox2.Text Value, returns a new (unique ?) RID 
// 2 Define a sub Procedure for getNewROOMID that given a loopValue (eg 1..10) and the ComboBox1.SelectedText Value, returns a new ROOMID

I dont write VB - so just use this as an idea of what you could do, dont use it verbatim, I doubt any of its legal VB anyway - good luck