-
Jan 22nd, 2018, 01:38 PM
#1
Thread Starter
Addicted Member
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.")
-
Jan 22nd, 2018, 02:39 PM
#2
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
-
Jan 22nd, 2018, 03:10 PM
#3
Thread Starter
Addicted Member
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
-
Jan 22nd, 2018, 03:47 PM
#4
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
-
Jan 22nd, 2018, 04:21 PM
#5
Thread Starter
Addicted Member
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.
-
Jan 22nd, 2018, 04:38 PM
#6
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
-
Jan 22nd, 2018, 04:41 PM
#7
Thread Starter
Addicted Member
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.
-
Jan 22nd, 2018, 05:04 PM
#8
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.
-
Jan 22nd, 2018, 06:23 PM
#9
Thread Starter
Addicted Member
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
-
Jan 22nd, 2018, 07:01 PM
#10
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
-
Jan 22nd, 2018, 07:03 PM
#11
Re: Cant get the mistake in the update query
Originally Posted by zubenubie
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
-
Jan 22nd, 2018, 11:04 PM
#12
Junior Member
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:
Private ReadOnly connectionString = "Data Source=localhost\COASTWISE;Initial Catalog=RestHours;Integrated Security=True" Private Sub SaveData() Dim query As New StringBuilder("UPDATE [RestHours2] SET [Month1] = @Month1, [Year1] = @Year1, ") Using connection As New SqlConnection(connectionString) Using command As New SqlCommand(query.ToString(), connection) For Each row As DataGridViewRow In Me.RestHours2DataGridView.Rows For Each column As DataGridViewColumn In Me.RestHours2DataGridView.Columns '' build the query if we havent yet If row.Index = 0 Then If column.Name.ToLower() <> "id" Then query.Append($"[{column.Name}] = @{column.Name}") If column.Index <> Me.RestHours2DataGridView.ColumnCount - 1 Then query.Append(", ") Else query.AppendLine("WHERE ID = @ID") End If '' do some formatting in case we need to grab the query for debug If column.DisplayIndex Mod 5 = 0 Then query.AppendLine() End If command.Parameters.Add(New SqlParameter($"@{column.Name}", row.Cells(column.Name).Value)) Next command.Parameters.Add(New SqlParameter($"@Month1", ComboBox1.Text)) command.Parameters.Add(New SqlParameter($"@Year1", ComboBox2.Text)) If row.Index = 0 Then command.CommandText = query.ToString() If connection.State <> ConnectionState.Open Then connection.Open() command.ExecuteNonQuery() command.Parameters.Clear() Next End Using End Using MessageBox.Show("Records Saved.") End Sub
Last edited by HeribertoLugo; Jan 22nd, 2018 at 11:16 PM.
Reason: forgot command text
-
Jan 22nd, 2018, 11:21 PM
#13
Junior Member
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:
Private ReadOnly connectionString = "Data Source=localhost\COASTWISE;Initial Catalog=RestHours;Integrated Security=True" Private Sub SaveData() Dim query As New StringBuilder("UPDATE [RestHours2] SET [Month1] = @Month1, [Year1] = @Year1, ") Using connection As New SqlConnection(connectionString) Using command As New SqlCommand(query.ToString(), connection) command.Parameters.AddWithValue($"@Month1", Me.ComboBox1.Text) command.Parameters.AddWithValue($"@Year1", Me.ComboBox2.Text) For Each row As DataGridViewRow In Me.RestHours2DataGridView.Rows For Each column As DataGridViewColumn In Me.RestHours2DataGridView.Columns '' build the query if we havent yet If row.Index = 0 Then If column.Name.ToLower() <> "id" Then query.Append($"[{column.Name}] = @{column.Name}") If column.Index <> Me.RestHours2DataGridView.ColumnCount - 1 Then query.Append(", ") Else query.AppendLine("WHERE ID = @ID") End If '' do some formatting in case we need to grab the query for debug If column.DisplayIndex Mod 5 = 0 Then query.AppendLine() End If If (command.Parameters.Contains($"@{column.Name}")) Then command.Parameters($"@{column.Name}").Value = row.Cells(column.Name).Value Else command.Parameters.AddWithValue($"@{column.Name}", row.Cells(column.Name).Value) End If Next If row.Index = 0 Then command.CommandText = query.ToString() If connection.State <> ConnectionState.Open Then connection.Open() command.ExecuteNonQuery() Next End Using End Using MessageBox.Show("Records Saved.") End Sub
Last edited by HeribertoLugo; Jan 22nd, 2018 at 11:32 PM.
-
Jan 23rd, 2018, 02:37 AM
#14
Lively Member
Re: Cant get the mistake in the update query
You are missing the "@" sign here:
[Alert7D] = Alert7D
-
Jan 23rd, 2018, 03:59 AM
#15
Thread Starter
Addicted Member
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.")
-
Jan 23rd, 2018, 04:07 AM
#16
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.
-
Jan 23rd, 2018, 04:28 AM
#17
Thread Starter
Addicted Member
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
-
Jan 23rd, 2018, 04:33 AM
#18
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?
-
Jan 23rd, 2018, 04:39 AM
#19
Thread Starter
Addicted Member
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.
-
Jan 23rd, 2018, 05:07 AM
#20
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.
-
Jan 23rd, 2018, 05:15 AM
#21
Thread Starter
Addicted Member
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
-
Jan 23rd, 2018, 06:50 AM
#22
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?
-
Jan 23rd, 2018, 07:23 AM
#23
Thread Starter
Addicted Member
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
-
Jan 23rd, 2018, 07:41 AM
#24
Re: Cant get the mistake in the update query
Originally Posted by zubenubie
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.
-
Jan 23rd, 2018, 09:03 AM
#25
Thread Starter
Addicted Member
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?
-
Jan 23rd, 2018, 07:10 PM
#26
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...
-
Jan 23rd, 2018, 08:37 PM
#27
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|