Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: VB.NET
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?
 
<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>
 
<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 />
 

 
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
musiw628
Edited 13-Nov-12 18:39pm
v3

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

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.
  Permalink  
Comments
musiw at 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 at 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 at 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 at 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 at 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 at 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 at 16-Nov-12 2:00am
   
i already tried..still no success..can you help me..
 
thanks in advance.
Dave Kreskowiak at 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.

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

  Print Answers RSS
0 OriginalGriff 430
1 DamithSL 288
2 Sergey Alexandrovich Kryukov 220
3 Peter Leow 215
4 Maciej Los 195


Advertise | Privacy | Mobile
Web03 | 2.8.140709.1 | Last Updated 14 Nov 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid