how to store multiple values from textbox & dropdown to database

Question

0.00/5 (No votes)

See more:

hi all,

please help me on this.i have a form which let user to add new row (multiple textbox and dropdown in it) if they want to add data.

right now, i dont know how to store multiple values from textbox & dropdown to database? is it store into 1 row or multiple rows? how about if i want to retrieve values back in future?which one is best?

C#

<SCRIPT language="javascript">
       function addRow(data) {

           var table = document.getElementById(data);

           var rowCount = table.rows.length;
           var row = table.insertRow(rowCount);

           var colCount = table.rows[0].cells.length;

           for (var i = 0; i < colCount; i++) {

               var newcell = row.insertCell(i);

               newcell.innerHTML = table.rows[0].cells[i].innerHTML;
               //alert(newcell.childNodes);
               switch (newcell.childNodes[0].type) {
                   case "text":
                       newcell.childNodes[0].value = "";
                       break;
                   case "checkbox":
                       newcell.childNodes[0].checked = false;
                       break;
                   case "select-one":
                       newcell.childNodes[0].selectedIndex = 0;
                       break;
               }
           }
       }


    </SCRIPT>

XML

<table style="width: 100%;"><tr><td><h3 style="text-decoration: underline">Sebab Hantar</h3></td></tr></table>
        <table style="width: 100%;" border="1" >
            <tr>
                <td style="width: 100px">Stokereta</td>
                <td style="width: 80px">Jenis Roda</td>
                <td style="width: 100px">No Siri</td>
                <td style="width: 80px">Saiz Roda (mm)</td>
                <td style="width: 50px">Flange (mm)</td>
                <td style="width: 50px">Hollow (mm)</td>
                <td style="width: 100px">Tindakan</td>
                <td style="width: 100px">Skidded</td>
                <td style="width: 350px">Catatan</td>

            </tr>
            </table>
            <table style="width: 100%;" id="data" border="1"  >
            <tr>
                <td >
                    <asp:TextBox ID="stokereta" runat="server" Width="100" BorderStyle="None" ></asp:TextBox>
                </td>
                <td>
                    <asp:DropDownList ID="ddl_jenisroda" runat="server" Width="80" >
                    </asp:DropDownList>

                </td>
                <td>
                    <asp:TextBox ID="nosiri" runat="server" Width="100" BorderStyle="None" ></asp:TextBox>

                </td>
                <td>
                    <asp:TextBox ID="saizroda" runat="server" Width="80" BorderStyle="None" ></asp:TextBox>
                </td>
                <td>
                    <asp:TextBox ID="flange" runat="server" Width="50" BorderStyle="None" ></asp:TextBox>
                </td>

                <td>
                    <asp:TextBox ID="hollow" runat="server" Width="50" BorderStyle="None" ></asp:TextBox>
                </td>
                <td>
                    <asp:DropDownList ID="ddl_tindakan" runat="server" Width="100" >
                    <asp:ListItem>Pilih..</asp:ListItem>
                    <asp:ListItem>Reprofiling</asp:ListItem>
                    <asp:ListItem>Rediscing</asp:ListItem>
                    </asp:DropDownList>


                </td>
                <td>
                    <asp:DropDownList ID="ddl_skid" runat="server" Width="100" >
                    <asp:ListItem>Pilih..</asp:ListItem>
                    <asp:ListItem>Ya</asp:ListItem>
                    <asp:ListItem>Tidak</asp:ListItem>
                    </asp:DropDownList>
                </td>
<td>
            <asp:TextBox ID="catatan" runat="server" Columns="20" Rows="5" Width="350px" Height="40" TextMode="MultiLine" BorderStyle="None" ></asp:TextBox></td>
            </tr>



        </table>
        <br />
        <input id="Button2" type="button" value="Tambah" onclick="addRow('data')" style="border: thin ridge #000000"  />

        <asp:Label ID="Label3" runat="server" Text="Proses" Visible="False"></asp:Label>
        <br />
        </asp:Panel>
        <br />
    <asp:Button ID="send" runat="server" Text="Hantar" BorderColor="Black" Width="60px" OnClick="Send_Click" />
        <asp:Button ID="clear" runat="server" Text="Semula" BorderColor="Black" Width="60px" OnClick="Semula_Click"/><br />

VB

Dim conn As New SqlConnection(connectionString)
        conn.Open()
        Dim sqlcomm As New SqlCommand("Insert into butiran_hantar ([depoh],[siriSA],[kuantiti],[date],[stokereta],[jenisroda], " +
                              "[nosiri],[saizroda],[flange],[hollow],[tindakan],[skid],[catatan],[status]) " +
                              "values (@depoh,@siriSA,@kuantiti,@date,@stokereta,@jenisroda, " +
                              "@nosiri,@saizroda,@flange,@hollow,@tindakan,@skid,@catatan,@status)", conn)

        sqlcomm.Parameters.AddWithValue("@depoh", depohlist.SelectedValue)
        sqlcomm.Parameters.AddWithValue("@siriSA", lblsiri.Text)
        sqlcomm.Parameters.AddWithValue("@kuantiti", kuantiti.Text)
        sqlcomm.Parameters.AddWithValue("@date", tarikh.Text)
        sqlcomm.Parameters.AddWithValue("@stokereta", stokereta.Text)
        sqlcomm.Parameters.AddWithValue("@jenisroda", ddl_jenisroda.SelectedValue)
        sqlcomm.Parameters.AddWithValue("@nosiri", nosiri.Text)
        sqlcomm.Parameters.AddWithValue("@saizroda", saizroda.Text)
        sqlcomm.Parameters.AddWithValue("@flange", flange.Text)
        sqlcomm.Parameters.AddWithValue("@hollow", hollow.Text)
        sqlcomm.Parameters.AddWithValue("@tindakan", ddl_tindakan.SelectedValue)
        sqlcomm.Parameters.AddWithValue("@skid", ddl_skid.SelectedValue)
        sqlcomm.Parameters.AddWithValue("@catatan", catatan.Text)
        sqlcomm.Parameters.AddWithValue("@status", "Proses")
        'sqlcomm.Parameters.AddWithValue("@ulasandm", ulasandm.Text)

        Try
            sqlcomm.ExecuteNonQuery()

            Label2.Text = "Saved."
            
        Catch ex As Exception
            Label2.Text = ex.Message.ToString()
        End Try

thanks in advance
musiw.

Posted 13-Nov-12 16:37pm

musiw

Updated 13-Nov-12 18:39pm

Orcun Iyigun

v3

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)

Dave Kreskowiak · Answer 1 · 2012-11-13T18:40:00

Solution 1

There is no "best". It depends entirely on your data requirements. You can do either, or you can store each line in a seperate table key'd to the record that it belongs to.

Posted 13-Nov-12 18:40pm

Dave Kreskowiak

Comments

musiw 14-Nov-12 4:13am

how to store multiple values to multiple row..??currently my code seems to store in a single row in sql

Dave Kreskowiak 14-Nov-12 7:47am

You'll have to iterate over your collection (for/next) of values and execute an SQL Insert for each of those values.

musiw 15-Nov-12 5:42am

i already did using for loop..everytime i enter the data..lets say 2 rows of data..it kept get the two rows data..then it cannot store in database..i dont kno how..if u can show me with code it will be helpful..

thanks

Dave Kreskowiak 15-Nov-12 7:58am

Well, without seeing the code that is getting this data and sending it to the database, it's pretty much impossible to tell you what's wrong with it.

musiw 15-Nov-12 19:51pm

Dim cnn As New SqlConnection(connectionString)
cnn.Open()
Dim cmd As New SqlCommand

Dim tran As SqlTransaction = cnn.BeginTransaction()
cmd.Connection = cnn
cmd.Transaction = tran
cmd.CommandText = "Insert into butiran_hantar ([depoh],[siriSA],[kuantiti],[date],[stokereta],[jenisroda], " +
"[nosiri],[saizroda],[flange],[hollow],[tindakan],[skid],[catatan],[status]) " +
"values (@depoh,@siriSA,@kuantiti,@date,@stokereta,@jenisroda, " +
"@nosiri,@saizroda,@flange,@hollow,@tindakan,@skid,@catatan,@status)"
cmd.CommandType = CommandType.Text
cmd.Connection = cnn

Dim n As Integer = Convert.ToInt32(kuantiti.Text)
For i As Integer = 0 To n - 1
'Dim p1 As New SqlParameter("@param1", SqlDbType.Int)
'Dim p2 As New SqlParameter("@param2", SqlDbType.Int)
If i = 0 Then
'p1.Value = Convert.ToInt32(textBox1.Text.Trim())
' row 1 in database
' row 1 in database
'p2.Value = Convert.ToInt32(textBox2.Text.Trim())
cmd.Parameters.AddWithValue("@depoh", depohlist.SelectedValue)
cmd.Parameters.AddWithValue("@siriSA", Convert.ToInt32(lblsiri.Text))
cmd.Parameters.AddWithValue("@kuantiti", Convert.ToInt32(kuantiti.Text))
cmd.Parameters.AddWithValue("@date", tarikh.Text)
cmd.Parameters.AddWithValue("@stokereta", stokereta.Text)
cmd.Parameters.AddWithValue("@jenisroda", ddl_jenisroda.SelectedValue)
cmd.Parameters.AddWithValue("@nosiri", Convert.ToInt32(nosiri.Text))
cmd.Parameters.AddWithValue("@saizroda", Convert.ToInt32(saizroda.Text))
cmd.Parameters.AddWithValue("@flange", Convert.ToInt32(flange.Text))
cmd.Parameters.AddWithValue("@hollow", Convert.ToInt32(hollow.Text))
cmd.Parameters.AddWithValue("@tindakan", ddl_tindakan.SelectedValue)
cmd.Parameters.AddWithValue("@skid", ddl_skid.SelectedValue)
cmd.Parameters.AddWithValue("@catatan", catatan.Text)
cmd.Parameters.AddWithValue("@status", "Proses")
Else
cmd.Parameters.AddWithValue("@depoh", depohlist.SelectedValue)
cmd.Parameters.AddWithValue("@siriSA", Convert.ToInt32(lblsiri.Text))
cmd.Parameters.AddWithValue("@kuantiti", Convert.ToInt32(kuantiti.Text))
cmd.Parameters.AddWithValue("@date", tarikh.Text)
cmd.Parameters.AddWithValue("@stokereta", stokereta.Text)
cmd.Parameters.AddWithValue("@jenisroda", ddl_jenisroda.SelectedValue)
cmd.Parameters.AddWithValue("@nosiri", Convert.ToInt32(nosiri.Text))
cmd.Parameters.AddWithValue("@saizroda", Convert.ToInt32(saizroda.Text))
cmd.Parameters.AddWithValue("@flange", Convert.ToInt32(flange.Text))
cmd.Parameters.AddWithValue("@hollow", Convert.ToInt32(hollow.Text))
cmd.Parameters.AddWithValue("@tindakan", ddl_tindakan.SelectedValue)
cmd.Parameters.AddWithValue("@skid", ddl_skid.SelectedValue)
cmd.Parameters.AddWithValue("@catatan", catatan.Text)
cmd.Parameters.AddWithValue("@status", "Proses")
End If
'cmd.Parameters.Add(p1)
'cmd.Parameters.Add(p2)

' Execute the query
cmd.ExecuteNonQuery()
Next

my aspx code is already pasted above.i have a form where user can add row if they want.in 1 row i have multiple textboxes and dropdown.

Dave Kreskowiak 15-Nov-12 20:22pm

Sigh... Look at your code again. Inside the For loop you're creating and adding all of your parameter objects EVERY TIME YOU GO THROUGH THE LOOP! This means on eht first time through, your cmd object has 14 parameter object. On the second time through the loop, you create 14 MORE parameter objects, for a total of 28, and on the third time through yor cmd object now has 42 parameters objects, ... and so on.

You need to remove those from the loop and put the parameter object creation (DO NOT use .AddWithValue!) up where you defined the INSERT query. Then you can set the value of the parameter objects inside the For loop.

musiw 16-Nov-12 2:00am

i already tried..still no success..can you help me..

thanks in advance.

Dave Kreskowiak 16-Nov-12 8:13am

You mean write your code for you?? Nope.
I've got my own code I'm getting paid to write, and I've got a ton of it to get out the door today.