PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197

PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197
Cant get the mistake in the update query-VBForums
Results 1 to 27 of 27

Thread: Cant get the mistake in the update query

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Mar 2017
    Location
    Netherlands
    Posts
    130

    Cant get the mistake in the update query

    Good day i have the next Update query and when i execute it i get the below error.

    Additional information: The parameterized query '(@Day nvarchar(4000),@CrewID nvarchar(4000),@1 nvarchar(4000),@2' expects the parameter '@Day', which was not supplied.

    But all colums in the datagridview have been filled and also the table in the sql database is updated

    Used the below code:

    Code:
    For Each row As DataGridViewRow In RestHours2DataGridView.Rows
    
                    Dim conn1 As New SqlConnection
    
                    conn1.ConnectionString = "Data Source=localhost\COASTWISE;Initial Catalog=RestHours;Integrated Security=True"
                    conn1.Open()
                    Dim cmd1 As New SqlCommand("UPDATE RestHours2 SET [Day] = @Day, CrewID = @CrewID, [1]=@1, [2] = @2, [3] = @3, [4] = @4, [5] = @5, [6] = @6, [7] = @7, [8] = @8, [9] = @9, [10] = @10, " _
                                           & "[11] = @11, [12] = @12, [13] = @13, [14] = @14, [15] = @15, [16] = @16, [17] = @17, [18 ]= @18, [19] = @19, [20] = @20, [21] = @21, [22] = @22, [23] = @23, [24] = @24, [25] = @25, [26] = @26, [27] = @27, [28] = @28, " _
                                            & "[29] = @29, [30] = @30, [31] = @31, [32] = @32, [33] = @33, [34] = @34, [35] = @35, [36] = @36, [37] = @37, [38] = @38, [39] = @39, [40] = @40, [41] = @41, [42] = @42, [43] = @43, [44] = @44, [45] = @45, " _
                & "[46] = @46, [47] = @47, [48] = @48, [Remarks] = @Remarks, [Alert24Hrs] = @Alert24Hrs, [Alert7D] = Alert7D, [HoursRest] = @HoursRest, [Month1] = @Month1, [Year1] = @Year1 WHERE ID = @ID ", conn1)
    
                    cmd1.Parameters.AddWithValue("@Day", row.Cells(0).Value)
                    cmd1.Parameters.AddWithValue("@CrewID", row.Cells(55).Value)
                    cmd1.Parameters.AddWithValue("@1", row.Cells(1).Value)
                    cmd1.Parameters.AddWithValue("@2", row.Cells(2).Value)
                    cmd1.Parameters.AddWithValue("@3", row.Cells(3).Value)
                    cmd1.Parameters.AddWithValue("@4", row.Cells(4).Value)
                    cmd1.Parameters.AddWithValue("@5", row.Cells(5).Value)
                    cmd1.Parameters.AddWithValue("@6", row.Cells(6).Value)
                    cmd1.Parameters.AddWithValue("@7", row.Cells(7).Value)
                    cmd1.Parameters.AddWithValue("@8", row.Cells(8).Value)
                    cmd1.Parameters.AddWithValue("@9", row.Cells(9).Value)
                    cmd1.Parameters.AddWithValue("@10", row.Cells(10).Value)
                    cmd1.Parameters.AddWithValue("@11", row.Cells(11).Value)
                    cmd1.Parameters.AddWithValue("@12", row.Cells(12).Value)
                    cmd1.Parameters.AddWithValue("@13", row.Cells(13).Value)
                    cmd1.Parameters.AddWithValue("@14", row.Cells(14).Value)
                    cmd1.Parameters.AddWithValue("@15", row.Cells(15).Value)
                    cmd1.Parameters.AddWithValue("@16", row.Cells(16).Value)
                    cmd1.Parameters.AddWithValue("@17", row.Cells(17).Value)
                    cmd1.Parameters.AddWithValue("@18", row.Cells(18).Value)
                    cmd1.Parameters.AddWithValue("@19", row.Cells(19).Value)
                    cmd1.Parameters.AddWithValue("@20", row.Cells(20).Value)
                    cmd1.Parameters.AddWithValue("@21", row.Cells(21).Value)
                    cmd1.Parameters.AddWithValue("@22", row.Cells(22).Value)
                    cmd1.Parameters.AddWithValue("@23", row.Cells(23).Value)
                    cmd1.Parameters.AddWithValue("@24", row.Cells(24).Value)
                    cmd1.Parameters.AddWithValue("@25", row.Cells(25).Value)
                    cmd1.Parameters.AddWithValue("@26", row.Cells(26).Value)
                    cmd1.Parameters.AddWithValue("@27", row.Cells(27).Value)
                    cmd1.Parameters.AddWithValue("@28", row.Cells(28).Value)
                    cmd1.Parameters.AddWithValue("@29", row.Cells(29).Value)
                    cmd1.Parameters.AddWithValue("@30", row.Cells(30).Value)
                    cmd1.Parameters.AddWithValue("@31", row.Cells(31).Value)
                    cmd1.Parameters.AddWithValue("@32", row.Cells(32).Value)
                    cmd1.Parameters.AddWithValue("@33", row.Cells(33).Value)
                    cmd1.Parameters.AddWithValue("@34", row.Cells(34).Value)
                    cmd1.Parameters.AddWithValue("@35", row.Cells(35).Value)
                    cmd1.Parameters.AddWithValue("@36", row.Cells(36).Value)
                    cmd1.Parameters.AddWithValue("@37", row.Cells(37).Value)
                    cmd1.Parameters.AddWithValue("@38", row.Cells(38).Value)
                    cmd1.Parameters.AddWithValue("@39", row.Cells(39).Value)
                    cmd1.Parameters.AddWithValue("@40", row.Cells(40).Value)
                    cmd1.Parameters.AddWithValue("@41", row.Cells(41).Value)
                    cmd1.Parameters.AddWithValue("@42", row.Cells(42).Value)
                    cmd1.Parameters.AddWithValue("@43", row.Cells(43).Value)
                    cmd1.Parameters.AddWithValue("@44", row.Cells(44).Value)
                    cmd1.Parameters.AddWithValue("@45", row.Cells(45).Value)
                    cmd1.Parameters.AddWithValue("@46", row.Cells(46).Value)
                    cmd1.Parameters.AddWithValue("@47", row.Cells(47).Value)
                    cmd1.Parameters.AddWithValue("@48", row.Cells(48).Value)
                    cmd1.Parameters.AddWithValue("@Remarks", row.Cells(52).Value)
                    cmd1.Parameters.AddWithValue("@Alert24Hrs", row.Cells(49).Value)
                    cmd1.Parameters.AddWithValue("@Alert7D", row.Cells(50).Value)
                    cmd1.Parameters.AddWithValue("@HoursRest", row.Cells(51).Value)
                    cmd1.Parameters.AddWithValue("@Month1", ComboBox1.Text)
                    cmd1.Parameters.AddWithValue("@Year1", ComboBox2.Text)
                    cmd1.Parameters.AddWithValue("@ID", row.Cells(56).Value)
    
                    cmd1.ExecuteNonQuery()
                    conn1.Close()
    
                Next
    
                MessageBox.Show("Records Saved.")

  2. #2
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    31,737

    Re: Cant get the mistake in the update query

    ACK! You should only open the connection ONCE... at the moment you're opening the connection for EVERY ROW! Also, create the command ONCE... add the parameters ONCE... what should be in the loop is the assigning of the values for the parameters and the executenonquery....
    That said, the SQL it's complaining about isn't the SQL you've posted. The error SQL looks like code for creating a table... not for the update you posted.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3

    Thread Starter
    Addicted Member
    Join Date
    Mar 2017
    Location
    Netherlands
    Posts
    130

    Re: Cant get the mistake in the update query

    Okay thanks for the pointers, took the loop to the cmd1.parameters and together with the execute nonquery.
    Now i get the error The variable name '@Day' has already been declared. Variable names must be unique within a query batch or stored procedure.

    The previous error came because the datgridview enable adding was unchecked i discovered

  4. #4
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    31,737

    Re: Cant get the mistake in the update query

    Ok... outside the loop you should be adding the parameters... INSIDE the loop you should be just setting the .Value of the parameters...
    something like this:
    Code:
            Dim conn1 As New SqlConnection
    
            conn1.ConnectionString = "Data Source=localhost\COASTWISE;Initial Catalog=RestHours;Integrated Security=True"
            conn1.Open()
            Dim cmd1 As New SqlCommand("UPDATE RestHours2 SET [Day] = @Day, CrewID = @CrewID, [1]=@1, [2] = @2, [3] = @3, [4] = @4, [5] = @5, [6] = @6, [7] = @7, [8] = @8, [9] = @9, [10] = @10, " _
                                           & "[11] = @11, [12] = @12, [13] = @13, [14] = @14, [15] = @15, [16] = @16, [17] = @17, [18 ]= @18, [19] = @19, [20] = @20, [21] = @21, [22] = @22, [23] = @23, [24] = @24, [25] = @25, [26] = @26, [27] = @27, [28] = @28, " _
                                            & "[29] = @29, [30] = @30, [31] = @31, [32] = @32, [33] = @33, [34] = @34, [35] = @35, [36] = @36, [37] = @37, [38] = @38, [39] = @39, [40] = @40, [41] = @41, [42] = @42, [43] = @43, [44] = @44, [45] = @45, " _
                & "[46] = @46, [47] = @47, [48] = @48, [Remarks] = @Remarks, [Alert24Hrs] = @Alert24Hrs, [Alert7D] = Alert7D, [HoursRest] = @HoursRest, [Month1] = @Month1, [Year1] = @Year1 WHERE ID = @ID ", conn1)
    
            cmd1.Parameters.Add("@Day", SqlDbType.Int)
            cmd1.Parameters.Add("@CrewID", SqlDbType.Int)
            cmd1.Parameters.Add("@1", SqlDbType.NVarChar, 50)
            cmd1.Parameters.Add("@2", SqlDbType.NVarChar, 100)
            cmd1.Parameters.Add("@3", SqlDbType.NVarChar, 150)
            cmd1.Parameters.Add("@4", SqlDbType.NVarChar, 1000)
    ...
    
    
    
    For Each row As DataGridViewRow In RestHours2DataGridView.Rows
     
                    cmd1.Parameters("@Day").VAlue =  row.Cells(0).Value
                    cmd1.Parameters("@CrewID").Value =  row.Cells(55).Value
                    cmd1.Parameters("@1").Value = row.Cells(1).Value
                    cmd1.Parameters("@2").Value = row.Cells(2).Value
                    cmd1.Parameters("@3").Value = row.Cells(3).Value
                    cmd1.Parameters("@4").Value = row.Cells(4).Value
    ...
    
                    cmd1.ExecuteNonQuery()
    
                Next
    
                conn1.Close()
                MessageBox.Show("Records Saved.")
    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  5. #5

    Thread Starter
    Addicted Member
    Join Date
    Mar 2017
    Location
    Netherlands
    Posts
    130

    Re: Cant get the mistake in the update query

    Have done it like your specs and i stll get the error: The variable name '@Day' has already been declared. Variable names must be unique within a query batch or stored procedure.
    When i remove the day in the query it goes to the next. so somehow it gets done 2 times i guess.
    Also
    cmd1.Parameters.Add("@1", SqlDbType.NVarChar, 50) @1 is in my datagridview a checkbox value (True/False)can i put it as a NVarChar as it will not take Boolean in the codeline?
    Last edited by zubenubie; Jan 22nd, 2018 at 04:32 PM.

  6. #6
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    31,737

    Re: Cant get the mistake in the update query

    post your code, because that makes no sense. If you're getting that error, it's because the .Parameters.Add is being called more than once.
    If something isn't taking a boolean, then something isn't right... I just used nvarchar in the example because I don't know what you're types really are. Presumably the types in the grid match what they should be in the database.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  7. #7

    Thread Starter
    Addicted Member
    Join Date
    Mar 2017
    Location
    Netherlands
    Posts
    130

    Re: Cant get the mistake in the update query

    Numbers 1 to 48 are checkboxes which give true or false in the database datatable.

    The code:
    Code:
    Dim conn1 As New SqlConnection
    
            conn1.ConnectionString = "Data Source=localhost\COASTWISE;Initial Catalog=RestHours;Integrated Security=True"
            conn1.Open()
            Dim cmd1 As New SqlCommand("UPDATE RestHours2 SET Day = @Day, CrewID = @CrewID, [1]=@1, [2] = @2, [3] = @3, [4] = @4, [5] = @5, [6] = @6, [7] = @7, [8] = @8, [9] = @9, [10] = @10, " _
                                           & "[11] = @11, [12] = @12, [13] = @13, [14] = @14, [15] = @15, [16] = @16, [17] = @17, [18 ]= @18, [19] = @19, [20] = @20, [21] = @21, [22] = @22, [23] = @23, [24] = @24, [25] = @25, [26] = @26, [27] = @27, [28] = @28, " _
                                            & "[29] = @29, [30] = @30, [31] = @31, [32] = @32, [33] = @33, [34] = @34, [35] = @35, [36] = @36, [37] = @37, [38] = @38, [39] = @39, [40] = @40, [41] = @41, [42] = @42, [43] = @43, [44] = @44, [45] = @45, " _
                & "[46] = @46, [47] = @47, [48] = @48, [Remarks] = @Remarks, [Alert24Hrs] = @Alert24Hrs, [Alert7D] = Alert7D, [HoursRest] = @HoursRest, [Month1] = @Month1, [Year1] = @Year1 WHERE ID = @ID ", conn1)
            cmd1.Parameters.Add("@Day", SqlDbType.Int)
            cmd1.Parameters.Add("@CrewID", SqlDbType.Int)
            cmd1.Parameters.Add("@1", SqlDbType.NVarChar, 50)
            cmd1.Parameters.Add("@2", SqlDbType.NVarChar, 50)
            cmd1.Parameters.Add("@3", SqlDbType.NVarChar, 50)
            cmd1.Parameters.Add("@4", SqlDbType.NVarChar, 50)
            cmd1.Parameters.Add("@5", SqlDbType.NVarChar, 50)
            cmd1.Parameters.Add("@6", SqlDbType.NVarChar, 50)
            cmd1.Parameters.Add("@7", SqlDbType.NVarChar, 50)
            cmd1.Parameters.Add("@8", SqlDbType.NVarChar, 50)
            cmd1.Parameters.Add("@9", SqlDbType.NVarChar, 50)
            cmd1.Parameters.Add("@10", SqlDbType.NVarChar, 50)
            cmd1.Parameters.Add("@11", SqlDbType.NVarChar, 50)
            cmd1.Parameters.Add("@12", SqlDbType.NVarChar, 50)
            cmd1.Parameters.Add("@13", SqlDbType.NVarChar, 50)
            cmd1.Parameters.Add("@14", SqlDbType.NVarChar, 50)
            cmd1.Parameters.Add("@15", SqlDbType.NVarChar, 50)
            cmd1.Parameters.Add("@16", SqlDbType.NVarChar, 50)
            cmd1.Parameters.Add("@17", SqlDbType.NVarChar, 50)
            cmd1.Parameters.Add("@18", SqlDbType.NVarChar, 50)
            cmd1.Parameters.Add("@19", SqlDbType.NVarChar, 50)
            cmd1.Parameters.Add("@20", SqlDbType.NVarChar, 50)
            cmd1.Parameters.Add("@21", SqlDbType.NVarChar, 50)
            cmd1.Parameters.Add("@22", SqlDbType.NVarChar, 50)
            cmd1.Parameters.Add("@23", SqlDbType.NVarChar, 50)
            cmd1.Parameters.Add("@24", SqlDbType.NVarChar, 50)
            cmd1.Parameters.Add("@25", SqlDbType.NVarChar, 50)
            cmd1.Parameters.Add("@26", SqlDbType.NVarChar, 50)
            cmd1.Parameters.Add("@27", SqlDbType.NVarChar, 50)
            cmd1.Parameters.Add("@28", SqlDbType.NVarChar, 50)
            cmd1.Parameters.Add("@29", SqlDbType.NVarChar, 50)
            cmd1.Parameters.Add("@30", SqlDbType.NVarChar, 50)
            cmd1.Parameters.Add("@31", SqlDbType.NVarChar, 50)
            cmd1.Parameters.Add("@32", SqlDbType.NVarChar, 50)
            cmd1.Parameters.Add("@33", SqlDbType.NVarChar, 50)
            cmd1.Parameters.Add("@34", SqlDbType.NVarChar, 50)
            cmd1.Parameters.Add("@35", SqlDbType.NVarChar, 50)
            cmd1.Parameters.Add("@36", SqlDbType.NVarChar, 50)
            cmd1.Parameters.Add("@37", SqlDbType.NVarChar, 50)
            cmd1.Parameters.Add("@38", SqlDbType.NVarChar, 50)
            cmd1.Parameters.Add("@39", SqlDbType.NVarChar, 50)
            cmd1.Parameters.Add("@40", SqlDbType.NVarChar, 50)
            cmd1.Parameters.Add("@41", SqlDbType.NVarChar, 50)
            cmd1.Parameters.Add("@42", SqlDbType.NVarChar, 50)
            cmd1.Parameters.Add("@43", SqlDbType.NVarChar, 50)
            cmd1.Parameters.Add("@44", SqlDbType.NVarChar, 50)
            cmd1.Parameters.Add("@45", SqlDbType.NVarChar, 50)
            cmd1.Parameters.Add("@46", SqlDbType.NVarChar, 50)
            cmd1.Parameters.Add("@47", SqlDbType.NVarChar, 50)
            cmd1.Parameters.Add("@48", SqlDbType.NVarChar, 50)
            cmd1.Parameters.Add("@Remarks", SqlDbType.NVarChar, 500)
            cmd1.Parameters.Add("@Alert24Hrs", SqlDbType.NVarChar, 50)
            cmd1.Parameters.Add("@Alert7D", SqlDbType.NVarChar, 50)
            cmd1.Parameters.Add("@HoursRest", SqlDbType.NVarChar, 50)
            cmd1.Parameters.Add("@Month1", SqlDbType.NVarChar, 50)
            cmd1.Parameters.Add("@Year1", SqlDbType.NVarChar, 50)
            cmd1.Parameters.Add("@ID", SqlDbType.Int)
    
    
            For Each row As DataGridViewRow In RestHours2DataGridView.Rows
                cmd1.Parameters.AddWithValue("@Day", row.Cells(0).Value)
                cmd1.Parameters.AddWithValue("@CrewID", row.Cells(55).Value)
                cmd1.Parameters.AddWithValue("@1", row.Cells(1).Value)
                cmd1.Parameters.AddWithValue("@2", row.Cells(2).Value)
                cmd1.Parameters.AddWithValue("@3", row.Cells(3).Value)
                cmd1.Parameters.AddWithValue("@4", row.Cells(4).Value)
                cmd1.Parameters.AddWithValue("@5", row.Cells(5).Value)
                cmd1.Parameters.AddWithValue("@6", row.Cells(6).Value)
                cmd1.Parameters.AddWithValue("@7", row.Cells(7).Value)
                cmd1.Parameters.AddWithValue("@8", row.Cells(8).Value)
                cmd1.Parameters.AddWithValue("@9", row.Cells(9).Value)
                cmd1.Parameters.AddWithValue("@10", row.Cells(10).Value)
                cmd1.Parameters.AddWithValue("@11", row.Cells(11).Value)
                cmd1.Parameters.AddWithValue("@12", row.Cells(12).Value)
                cmd1.Parameters.AddWithValue("@13", row.Cells(13).Value)
                cmd1.Parameters.AddWithValue("@14", row.Cells(14).Value)
                cmd1.Parameters.AddWithValue("@15", row.Cells(15).Value)
                cmd1.Parameters.AddWithValue("@16", row.Cells(16).Value)
                cmd1.Parameters.AddWithValue("@17", row.Cells(17).Value)
                cmd1.Parameters.AddWithValue("@18", row.Cells(18).Value)
                cmd1.Parameters.AddWithValue("@19", row.Cells(19).Value)
                cmd1.Parameters.AddWithValue("@20", row.Cells(20).Value)
                cmd1.Parameters.AddWithValue("@21", row.Cells(21).Value)
                cmd1.Parameters.AddWithValue("@22", row.Cells(22).Value)
                cmd1.Parameters.AddWithValue("@23", row.Cells(23).Value)
                cmd1.Parameters.AddWithValue("@24", row.Cells(24).Value)
                cmd1.Parameters.AddWithValue("@25", row.Cells(25).Value)
                cmd1.Parameters.AddWithValue("@26", row.Cells(26).Value)
                cmd1.Parameters.AddWithValue("@27", row.Cells(27).Value)
                cmd1.Parameters.AddWithValue("@28", row.Cells(28).Value)
                cmd1.Parameters.AddWithValue("@29", row.Cells(29).Value)
                cmd1.Parameters.AddWithValue("@30", row.Cells(30).Value)
                cmd1.Parameters.AddWithValue("@31", row.Cells(31).Value)
                cmd1.Parameters.AddWithValue("@32", row.Cells(32).Value)
                cmd1.Parameters.AddWithValue("@33", row.Cells(33).Value)
                cmd1.Parameters.AddWithValue("@34", row.Cells(34).Value)
                cmd1.Parameters.AddWithValue("@35", row.Cells(35).Value)
                cmd1.Parameters.AddWithValue("@36", row.Cells(36).Value)
                cmd1.Parameters.AddWithValue("@37", row.Cells(37).Value)
                cmd1.Parameters.AddWithValue("@38", row.Cells(38).Value)
                cmd1.Parameters.AddWithValue("@39", row.Cells(39).Value)
                cmd1.Parameters.AddWithValue("@40", row.Cells(40).Value)
                cmd1.Parameters.AddWithValue("@41", row.Cells(41).Value)
                cmd1.Parameters.AddWithValue("@42", row.Cells(42).Value)
                cmd1.Parameters.AddWithValue("@43", row.Cells(43).Value)
                cmd1.Parameters.AddWithValue("@44", row.Cells(44).Value)
                cmd1.Parameters.AddWithValue("@45", row.Cells(45).Value)
                cmd1.Parameters.AddWithValue("@46", row.Cells(46).Value)
                cmd1.Parameters.AddWithValue("@47", row.Cells(47).Value)
                cmd1.Parameters.AddWithValue("@48", row.Cells(48).Value)
                cmd1.Parameters.AddWithValue("@Remarks", row.Cells(52).Value)
                cmd1.Parameters.AddWithValue("@Alert24Hrs", row.Cells(49).Value)
                cmd1.Parameters.AddWithValue("@Alert7D", row.Cells(50).Value)
                cmd1.Parameters.AddWithValue("@HoursRest", row.Cells(51).Value)
                cmd1.Parameters.AddWithValue("@Month1", ComboBox1.Text)
                cmd1.Parameters.AddWithValue("@Year1", ComboBox2.Text)
                cmd1.Parameters.AddWithValue("@ID", row.Cells(56).Value)
    
                cmd1.ExecuteNonQuery()
            Next
            conn1.Close()
    Also now when i have added the lines with parameter.add. it is not saving nothing in the database table. before it did save the changes in the table and give the error
    Last edited by zubenubie; Jan 22nd, 2018 at 04:58 PM.

  8. #8
    Hyperactive Member
    Join Date
    Nov 2017
    Posts
    375

    Re: Cant get the mistake in the update query

    techgnome clearly pointed out that inside the loop you should be doing something like this:



    Code:
    For Each row As DataGridViewRow In RestHours2DataGridView.Rows
                    cmd1.Parameters("@Day").Value =  row.Cells(0).Value
                    cmd1.Parameters("@CrewID").Value =  row.Cells(55).Value
                    cmd1.Parameters("@1").Value = row.Cells(1).Value
                    cmd1.Parameters("@2").Value = row.Cells(2).Value
                    cmd1.Parameters("@3").Value = row.Cells(3).Value
                    cmd1.Parameters("@4").Value = row.Cells(4).Value
    You are still doing:

    Code:
    cmd1.Parameters.AddWithValue(...)
    inside your loop, which is the problem.

  9. #9

    Thread Starter
    Addicted Member
    Join Date
    Mar 2017
    Location
    Netherlands
    Posts
    130

    Re: Cant get the mistake in the update query

    Thanks both have read completely over those changes, must be tired
    when i put the line datagridview.datacource is nothing the query is running it gives records saved but does not saves it in the database table.
    when i remove that line the program freezes when i run the query

  10. #10
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    31,737

    Re: Cant get the mistake in the update query

    If they are booleans, then the parameters should be booleans... but SQLServer doesn't support booelan, so use the Bit datatype instead. Shouldn't be using nvarchar for storing something as small as a bit.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  11. #11
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    31,737

    Re: Cant get the mistake in the update query

    Quote Originally Posted by zubenubie View Post
    Thanks both have read completely over those changes, must be tired
    when i put the line datagridview.datacource is nothing the query is running it gives records saved but does not saves it in the database table.
    when i remove that line the program freezes when i run the query
    How are you testing that it is/isn't saving the records?
    In my signature block there is a link "I swear I saved my data, where'd it run off to?" ... click, read... read it anyways, it may not seem at first if it pertains, but it might, so be sure to read the whole thing... and make sure you're actually checking the database you think you're checking.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  12. #12
    Junior Member HeribertoLugo's Avatar
    Join Date
    Nov 2014
    Posts
    29

    Re: Cant get the mistake in the update query

    I didn't test this, but it should work. Assuming your gridview column names match the database table names. But really, you should be using SqlBulkCopy

    VB.NET Code:
    1. Private ReadOnly connectionString = "Data Source=localhost\COASTWISE;Initial Catalog=RestHours;Integrated Security=True"
    2.  
    3.     Private Sub SaveData()
    4.         Dim query As New StringBuilder("UPDATE [RestHours2] SET [Month1] = @Month1, [Year1] = @Year1, ")
    5.  
    6.         Using connection As New SqlConnection(connectionString)
    7.             Using command As New SqlCommand(query.ToString(), connection)
    8.                 For Each row As DataGridViewRow In Me.RestHours2DataGridView.Rows
    9.                     For Each column As DataGridViewColumn In Me.RestHours2DataGridView.Columns
    10.                         '' build the query if we havent yet
    11.                         If row.Index = 0 Then
    12.                             If column.Name.ToLower() <> "id" Then query.Append($"[{column.Name}] = @{column.Name}")
    13.  
    14.                             If column.Index <> Me.RestHours2DataGridView.ColumnCount - 1 Then
    15.                                 query.Append(", ")
    16.                             Else query.AppendLine("WHERE ID = @ID")
    17.                             End If
    18.                             '' do some formatting in case we need to grab the query for debug
    19.                             If column.DisplayIndex Mod 5 = 0 Then query.AppendLine()
    20.                         End If
    21.  
    22.                         command.Parameters.Add(New SqlParameter($"@{column.Name}", row.Cells(column.Name).Value))
    23.                     Next
    24.  
    25.                     command.Parameters.Add(New SqlParameter($"@Month1", ComboBox1.Text))
    26.                     command.Parameters.Add(New SqlParameter($"@Year1", ComboBox2.Text))
    27.  
    28.                     If row.Index = 0 Then command.CommandText = query.ToString()
    29.                     If connection.State <> ConnectionState.Open Then connection.Open()
    30.                     command.ExecuteNonQuery()
    31.  
    32.                     command.Parameters.Clear()
    33.                 Next
    34.             End Using
    35.         End Using
    36.  
    37.         MessageBox.Show("Records Saved.")
    38.     End Sub
    Last edited by HeribertoLugo; Jan 22nd, 2018 at 11:16 PM. Reason: forgot command text

  13. #13
    Junior Member HeribertoLugo's Avatar
    Join Date
    Nov 2014
    Posts
    29

    Re: Cant get the mistake in the update query

    could also just add the parameters once, then update the value in each row iteration

    VB.NET Code:
    1. Private ReadOnly connectionString = "Data Source=localhost\COASTWISE;Initial Catalog=RestHours;Integrated Security=True"
    2.  
    3.     Private Sub SaveData()
    4.         Dim query As New StringBuilder("UPDATE [RestHours2] SET [Month1] = @Month1, [Year1] = @Year1, ")
    5.  
    6.         Using connection As New SqlConnection(connectionString)
    7.             Using command As New SqlCommand(query.ToString(), connection)
    8.                 command.Parameters.AddWithValue($"@Month1", Me.ComboBox1.Text)
    9.                 command.Parameters.AddWithValue($"@Year1", Me.ComboBox2.Text)
    10.  
    11.                 For Each row As DataGridViewRow In Me.RestHours2DataGridView.Rows
    12.                     For Each column As DataGridViewColumn In Me.RestHours2DataGridView.Columns
    13.                         '' build the query if we havent yet
    14.                         If row.Index = 0 Then
    15.                             If column.Name.ToLower() <> "id" Then query.Append($"[{column.Name}] = @{column.Name}")
    16.  
    17.                             If column.Index <> Me.RestHours2DataGridView.ColumnCount - 1 Then
    18.                                 query.Append(", ")
    19.                             Else query.AppendLine("WHERE ID = @ID")
    20.                             End If
    21.                             '' do some formatting in case we need to grab the query for debug
    22.                             If column.DisplayIndex Mod 5 = 0 Then query.AppendLine()
    23.                         End If
    24.  
    25.                         If (command.Parameters.Contains($"@{column.Name}")) Then
    26.                             command.Parameters($"@{column.Name}").Value = row.Cells(column.Name).Value
    27.                         Else
    28.                             command.Parameters.AddWithValue($"@{column.Name}", row.Cells(column.Name).Value)
    29.                         End If
    30.                     Next
    31.  
    32.                     If row.Index = 0 Then command.CommandText = query.ToString()
    33.                     If connection.State <> ConnectionState.Open Then connection.Open()
    34.                     command.ExecuteNonQuery()
    35.                 Next
    36.             End Using
    37.         End Using
    38.  
    39.         MessageBox.Show("Records Saved.")
    40.     End Sub
    Last edited by HeribertoLugo; Jan 22nd, 2018 at 11:32 PM.

  14. #14
    Junior Member
    Join Date
    Jun 2017
    Posts
    25

    Re: Cant get the mistake in the update query

    You are missing the "@" sign here:

    [Alert7D] = Alert7D

  15. #15

    Thread Starter
    Addicted Member
    Join Date
    Mar 2017
    Location
    Netherlands
    Posts
    130

    Re: Cant get the mistake in the update query

    The query itself is okay and working.
    The problem i think is in the rows. In the datagridview are for the actual month so January 31 rows. When i put enable adding True then the query runs but does not save anything, because it puts then a row at line 32 and then it expects to put the values from that row.

    When i put enable adding false then then it does not add the row 32 but the program freezes.

    i have tried with the following isnot dbnull but is not working.

    Code:
    Dim conn1 As New SqlConnection
    
            conn1.ConnectionString = "Data Source=localhost\COASTWISE;Initial Catalog=RestHours;Integrated Security=True"
            conn1.Open()
            Dim cmd1 As New SqlCommand("UPDATE RestHours2 SET Day = @Day, CrewID = @CrewID, [1]=@1, [2] = @2, [3] = @3, [4] = @4, [5] = @5, [6] = @6, [7] = @7, [8] = @8, [9] = @9, [10] = @10, " _
                                           & "[11] = @11, [12] = @12, [13] = @13, [14] = @14, [15] = @15, [16] = @16, [17] = @17, [18 ]= @18, [19] = @19, [20] = @20, [21] = @21, [22] = @22, [23] = @23, [24] = @24, [25] = @25, [26] = @26, [27] = @27, [28] = @28, " _
                                            & "[29] = @29, [30] = @30, [31] = @31, [32] = @32, [33] = @33, [34] = @34, [35] = @35, [36] = @36, [37] = @37, [38] = @38, [39] = @39, [40] = @40, [41] = @41, [42] = @42, [43] = @43, [44] = @44, [45] = @45, " _
                & "[46] = @46, [47] = @47, [48] = @48, [Remarks] = @Remarks, [Alert24Hrs] = @Alert24Hrs, [Alert7D] = @Alert7D, [HoursRest] = @HoursRest, [Month1] = @Month1, [Year1] = @Year1 WHERE ID = @ID ", conn1)
            cmd1.Parameters.Add("@Day", SqlDbType.Int)
            cmd1.Parameters.Add("@CrewID", SqlDbType.Int)
            cmd1.Parameters.Add("@1", SqlDbType.Bit)
            cmd1.Parameters.Add("@2", SqlDbType.Bit)
            cmd1.Parameters.Add("@3", SqlDbType.Bit)
            cmd1.Parameters.Add("@4", SqlDbType.Bit)
            cmd1.Parameters.Add("@5", SqlDbType.Bit)
            cmd1.Parameters.Add("@6", SqlDbType.Bit)
            cmd1.Parameters.Add("@7", SqlDbType.Bit)
            cmd1.Parameters.Add("@8", SqlDbType.Bit)
            cmd1.Parameters.Add("@9", SqlDbType.Bit)
            cmd1.Parameters.Add("@10", SqlDbType.Bit)
            cmd1.Parameters.Add("@11", SqlDbType.Bit)
            cmd1.Parameters.Add("@12", SqlDbType.Bit)
            cmd1.Parameters.Add("@13", SqlDbType.Bit)
            cmd1.Parameters.Add("@14", SqlDbType.Bit)
            cmd1.Parameters.Add("@15", SqlDbType.Bit)
            cmd1.Parameters.Add("@16", SqlDbType.Bit)
            cmd1.Parameters.Add("@17", SqlDbType.Bit)
            cmd1.Parameters.Add("@18", SqlDbType.Bit)
            cmd1.Parameters.Add("@19", SqlDbType.Bit)
            cmd1.Parameters.Add("@20", SqlDbType.Bit)
            cmd1.Parameters.Add("@21", SqlDbType.Bit)
            cmd1.Parameters.Add("@22", SqlDbType.Bit)
            cmd1.Parameters.Add("@23", SqlDbType.Bit)
            cmd1.Parameters.Add("@24", SqlDbType.Bit)
            cmd1.Parameters.Add("@25", SqlDbType.Bit)
            cmd1.Parameters.Add("@26", SqlDbType.Bit)
            cmd1.Parameters.Add("@27", SqlDbType.Bit)
            cmd1.Parameters.Add("@28", SqlDbType.Bit)
            cmd1.Parameters.Add("@29", SqlDbType.Bit)
            cmd1.Parameters.Add("@30", SqlDbType.Bit)
            cmd1.Parameters.Add("@31", SqlDbType.Bit)
            cmd1.Parameters.Add("@32", SqlDbType.Bit)
            cmd1.Parameters.Add("@33", SqlDbType.Bit)
            cmd1.Parameters.Add("@34", SqlDbType.Bit)
            cmd1.Parameters.Add("@35", SqlDbType.Bit)
            cmd1.Parameters.Add("@36", SqlDbType.Bit)
            cmd1.Parameters.Add("@37", SqlDbType.Bit)
            cmd1.Parameters.Add("@38", SqlDbType.Bit)
            cmd1.Parameters.Add("@39", SqlDbType.Bit)
            cmd1.Parameters.Add("@40", SqlDbType.Bit)
            cmd1.Parameters.Add("@41", SqlDbType.Bit)
            cmd1.Parameters.Add("@42", SqlDbType.Bit)
            cmd1.Parameters.Add("@43", SqlDbType.Bit)
            cmd1.Parameters.Add("@44", SqlDbType.Bit)
            cmd1.Parameters.Add("@45", SqlDbType.Bit)
            cmd1.Parameters.Add("@46", SqlDbType.Bit)
            cmd1.Parameters.Add("@47", SqlDbType.Bit)
            cmd1.Parameters.Add("@48", SqlDbType.Bit)
            cmd1.Parameters.Add("@Remarks", SqlDbType.NVarChar, 500)
            cmd1.Parameters.Add("@Alert24Hrs", SqlDbType.Bit)
            cmd1.Parameters.Add("@Alert7D", SqlDbType.Bit)
            cmd1.Parameters.Add("@HoursRest", SqlDbType.Decimal)
            cmd1.Parameters.Add("@Month1", SqlDbType.NVarChar, 50)
            cmd1.Parameters.Add("@Year1", SqlDbType.NVarChar, 50)
            cmd1.Parameters.Add("@ID", SqlDbType.Int)
    
            RestHours2DataGridView.AllowUserToAddRows = False
    
            For Each row As DataGridViewRow In RestHours2DataGridView.Rows
                If row.Cells IsNot DBNull.Value Then
                    cmd1.Parameters("@Day").Value = row.Cells(0).Value
                    cmd1.Parameters("@CrewID").Value = row.Cells(55).Value
                    cmd1.Parameters("@1").Value = row.Cells(1).Value
                    cmd1.Parameters("@2").Value = row.Cells(2).Value
                    cmd1.Parameters("@3").Value = row.Cells(3).Value
                    cmd1.Parameters("@4").Value = row.Cells(4).Value
                    cmd1.Parameters("@5").Value = row.Cells(5).Value
                    cmd1.Parameters("@6").Value = row.Cells(6).Value
                    cmd1.Parameters("@7").Value = row.Cells(7).Value
                    cmd1.Parameters("@8").Value = row.Cells(8).Value
                    cmd1.Parameters("@9").Value = row.Cells(9).Value
                    cmd1.Parameters("@10").Value = row.Cells(10).Value
                    cmd1.Parameters("@11").Value = row.Cells(11).Value
                    cmd1.Parameters("@12").Value = row.Cells(12).Value
                    cmd1.Parameters("@13").Value = row.Cells(13).Value
                    cmd1.Parameters("@14").Value = row.Cells(14).Value
                    cmd1.Parameters("@15").Value = row.Cells(15).Value
                    cmd1.Parameters("@16").Value = row.Cells(16).Value
                    cmd1.Parameters("@17").Value = row.Cells(17).Value
                    cmd1.Parameters("@18").Value = row.Cells(18).Value
                    cmd1.Parameters("@19").Value = row.Cells(19).Value
                    cmd1.Parameters("@20").Value = row.Cells(20).Value
                    cmd1.Parameters("@21").Value = row.Cells(21).Value
                    cmd1.Parameters("@22").Value = row.Cells(22).Value
                    cmd1.Parameters("@23").Value = row.Cells(23).Value
                    cmd1.Parameters("@24").Value = row.Cells(24).Value
                    cmd1.Parameters("@25").Value = row.Cells(25).Value
                    cmd1.Parameters("@26").Value = row.Cells(26).Value
                    cmd1.Parameters("@27").Value = row.Cells(27).Value
                    cmd1.Parameters("@28").Value = row.Cells(28).Value
                    cmd1.Parameters("@29").Value = row.Cells(29).Value
                    cmd1.Parameters("@30").Value = row.Cells(30).Value
                    cmd1.Parameters("@31").Value = row.Cells(31).Value
                    cmd1.Parameters("@32").Value = row.Cells(32).Value
                    cmd1.Parameters("@33").Value = row.Cells(33).Value
                    cmd1.Parameters("@34").Value = row.Cells(34).Value
                    cmd1.Parameters("@35").Value = row.Cells(35).Value
                    cmd1.Parameters("@36").Value = row.Cells(36).Value
                    cmd1.Parameters("@37").Value = row.Cells(37).Value
                    cmd1.Parameters("@38").Value = row.Cells(38).Value
                    cmd1.Parameters("@39").Value = row.Cells(39).Value
                    cmd1.Parameters("@40").Value = row.Cells(40).Value
                    cmd1.Parameters("@41").Value = row.Cells(41).Value
                    cmd1.Parameters("@42").Value = row.Cells(42).Value
                    cmd1.Parameters("@43").Value = row.Cells(43).Value
                    cmd1.Parameters("@44").Value = row.Cells(44).Value
                    cmd1.Parameters("@45").Value = row.Cells(45).Value
                    cmd1.Parameters("@46").Value = row.Cells(46).Value
                    cmd1.Parameters("@47").Value = row.Cells(47).Value
                    cmd1.Parameters("@48").Value = row.Cells(48).Value
                    cmd1.Parameters("@Remarks").Value = row.Cells(52).Value
                    cmd1.Parameters("@Alert24Hrs").Value = row.Cells(49).Value
                    cmd1.Parameters("@Alert7D").Value = row.Cells(50).Value
                    cmd1.Parameters("@HoursRest").Value = row.Cells(51).Value
                    cmd1.Parameters("@Month1").Value = ComboBox1.Text
                    cmd1.Parameters("@Year1").Value = ComboBox2.Text
                    cmd1.Parameters("@ID").Value = row.Cells(56).Value
                End If
                cmd1.ExecuteNonQuery()
            Next
            conn1.Close()
            RestHours2DataGridView.AllowUserToAddRows = False
            MessageBox.Show("Records Saved.")

  16. #16
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    99,471

    Re: Cant get the mistake in the update query

    The data entry row at the bottom of a DataGridView has a value of True for its IsNewRow property, so you can easily exclude it that way. If you code is freezing then maybe you should debug it and find out exactly where and what data is in use at the time.

    Of course, you could just bind the grid to a DataTable and save the whole lot with a single call to Update on a data adapter. If you're not retrieving the records from the database in the first place then you just have to call SetModified on all the DataRows.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  17. #17

    Thread Starter
    Addicted Member
    Join Date
    Mar 2017
    Location
    Netherlands
    Posts
    130

    Re: Cant get the mistake in the update query

    You are right did some debugging and came to messagebox.show if i remove that line it is doing what i want.
    But it is quite strange that it freezes on that line of code

  18. #18
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    99,471

    Re: Cant get the mistake in the update query

    Code execution is supposed to stop an a call to MessageBox.Show. Are you saying, without actually saying, that that message box isn't displayed? If so, what happens if you put a similar call at the beginning of the code?
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  19. #19

    Thread Starter
    Addicted Member
    Join Date
    Mar 2017
    Location
    Netherlands
    Posts
    130

    Re: Cant get the mistake in the update query

    Indeed the messagebox.show is not showing also not in the beginning of the code and freezing the program
    Last edited by zubenubie; Jan 23rd, 2018 at 04:47 AM.

  20. #20
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    99,471

    Re: Cant get the mistake in the update query

    That is strange. Is there any multi-threading going on? That's a stab in the dark anyway.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  21. #21

    Thread Starter
    Addicted Member
    Join Date
    Mar 2017
    Location
    Netherlands
    Posts
    130

    Re: Cant get the mistake in the update query

    When i put datagridview.datasource = nothing then the messagebox shows up but it doesnt save the data. And no multi-threading so quite strange this one

  22. #22
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    99,471

    Re: Cant get the mistake in the update query

    So, what is assign to the DataSource of the grid? Is it a DataTable? If so then why didn't you just use a data adapter in the first place?
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  23. #23

    Thread Starter
    Addicted Member
    Join Date
    Mar 2017
    Location
    Netherlands
    Posts
    130

    Re: Cant get the mistake in the update query

    I have an autogenerated bindingsource with a tabeladapter.
    I was strugling with collumns getting autofilled with month and year. and then in mt database table all months created got the same month saved so that is why i do it now manually

  24. #24
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    99,471

    Re: Cant get the mistake in the update query

    Quote Originally Posted by zubenubie View Post
    I have an autogenerated bindingsource with a tabeladapter.
    OH MY GOD! Delete all the code you have shown us so far. Select it all and delete it. If you have a typed DataSet then you call Fill on a table adapter to populate a DataTable in an instance of that DataSet. To save data, you call Update on that same table adapter. That's all you do. If you had some issue when you were doing that then let's address that. What you are doing here is not the way to do that. Theer is simply no need to mix and match table adapters and data adapters like that. Make a choice: typed DataSet or untyped DataSet. Don't use both.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  25. #25

    Thread Starter
    Addicted Member
    Join Date
    Mar 2017
    Location
    Netherlands
    Posts
    130

    Re: Cant get the mistake in the update query

    Allright so i have to make the select and insert and update queries in the tableadapter or am i wrong?
    How do i call these queries then?

  26. #26
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    99,471

    Re: Cant get the mistake in the update query

    Standard ADO.NET is based on the System.Data namespace. It contains the DataSet, DataTable, DataColumn, DataRow and DataRelation classes for storing data locally, as well as the DbConnection, DbCommand and DbDataAdapter classes, which provide the base implementation for retrieving and saving data against a database. Microsoft and third parties can and have created providers for particular data sources, e.g. System.Data.SqlClient includes the SqlConnection, SqlCommand and SqlDataAdapter classes for SQL Server, System.Data.OleDb includes the OleDbConnection, OleDbCommand and OleDbDataAdapter classes for OLE DB data sources and MySql.Data.MySqlClient (from MySQL rather than Microsoft) includes the MySqlConnection, MySqlCommand and MySqlDataAdapter classes for MySQL.

    When writing your own standard ADO.NET code, you create a connection object to connect to the database and one or more command objects to move data between the database and the application over that connection. A data adapter groups up to four commands together to provide CRUD functionality for a single table. The Fill method executes the SelectCommand to retrieve data from the database into a DataTable, while the Update method executes the InsertCommand, UpdateCommand and DeleteCommand as required to save changes from a DataTable to the database. That DataTable may or may not be part of a DataSet.

    More to come...
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  27. #27
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    99,471

    Re: Cant get the mistake in the update query

    When you use the Data Source wizard, you are generating a typed DataSet. A typed DataSet extends the standard ADO.NET classes and makes them easier to use with the specific data you are working with in that case. The wizard will generate a single class that inherits DataSet as well as one class that inherits DataTable and one that inherits DataRow for each table in your database. It will also generate a table adapter class for each DataTable. A table adapter wraps a data adapter, so you use one instead of the other. The internal data adapter already contains commands and connections, so you don't need to do anything relating to them.

    The typed DataSet class that's generated by the wizard is easier to use than the standard DataSet class because it creates all the DataTables for you and exposes each one via a dedicated property. Each DataTable becomes a collection itself, rather than your having to go via the Rows property, and each DataRow has a property for each column, rather than your having to go via the Item property. Those extra properties mean that you get Intellisense help rather than having to use Strings for table and column names.

    When you generate a type DataSet, you get one DataTable and one table adapter for each database table by default. You can exclude tables if you want and you can also add DataTables and table adapters for stored procedures and arbitrary queries. Each table adapter generated has a Fill method that executes the SelectedCommand and an Update method that executes the InsertCommand, UpdateCommand and DeleteCommand. The SelectCommand contains the default query, which is basically a "SELECT *" for the table. You can add queries to a table adapter that allow you to filter the data in various ways. For instance, you can add a query with "WHERE Name = @Name" and that will add an extra method, which you would logically name FillByName. You can then call Fill on the table adapter to get all records or FillByName to get just the records that match a particular Name value.

    So, if you have a typed DataSet then you should use that and not write any standard ADO.NET code. You can add your DataSet and table adapter in the designer if you want, or in code. If you drag a table from the Data Sources window onto the form, they will be created for you. You call Fill on the table adapter to get the data into the DataTable. If that's bound, the data shows up in the UI automatically. Once the desired changes have been made, you call Update on the table adapter and that saves all the changes. That's pretty much all you need in most cases. If you have more specific needs then you'll need to make some changes. You might need some extra code and/or you might need to modify the DataSet in the designer. What you definitely don't need to do is write your own standard ADO.NET code. Generally, the only reason you'd need to do that if you already have a typed DataSet is if you need to generate SQL code at run time.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width