[origen2g]

Hello out there.

I have been on a database project for a while now. I wanted to be able to make changes to my database using the update command of a Datagridview control.However, I have been faced with a series of unsuccessful outcomes.I want to do this how do I go about it?




Public Sub InsertInformation()

Dim connex As MySqlConnection
Dim theNumber, tryNumber As Integer
Dim MyCommandText, coll As String
Dim NameOne, RankOne, StaffNosOne, DepartmentOne, BankOne, AccountNosOne, BranchOne, AdditionCommentOne, DeductionCommentOne As String
Dim GradeOne, StepOne As Integer
Dim DriverAllowanceOne, PensionOne, VehicleOne, NonAccidentOne, AdditionOne, CTCSOne, DeductionsOne As Double


MyCommandText = " insert into " & MyFileTableText & " values(" & NameOne & "," & GradeOne & "," & StepOne & "," & RankOne & "," & _
StaffNosOne & "," & DepartmentOne & "," & BankOne & "," & AccountNosOne & "," & BranchOne & ");"


Try
connex = New MySqlConnection
connex.ConnectionString = "server=127.0.0.1;" & "uid=root;" & "pwd = instructed;"
connex.Open()
myCommand.Connection = connex
myAdapter.UpdateCommand = myCommand


Dim cmdbuilder As New MySqlCommandBuilder(myAdapter)
Dim i As Integer

Try
i = myAdapter.Update(myDataSet, "myData")
MsgBox("Records Updated= " & i)
Catch ex As Exception
MsgBox(ex.Message)
End Try

Catch myerror As MySqlException 'Exception

MessageBox.Show("Error UpdatingDatabase: " & myerror.Message)
connex.Close()
End Try

connex.Close()
connex.Dispose()
End Sub

This post has been edited by origen2g: 25 May 2010 - 06:56 AM

[modi123_1]

Define "a series of unsuccessful outcomes". Does that mean you are getting error messages? If so, where, and what are they? Is it not responding how you would like it? If so what is it supposed to be doing vs what it *is* doing.

[PDUNZ]

And please use the Code tags when posting code.

[origen2g]

PDUNZ, on 26 May 2010 - 07:29 PM, said:

And please use the Code tags when posting code.

I am so sorry for taking this long to reply.This is because I am having problems actually connecting to my MySQL database. I had left off handling the VB.NET project for a while and had uninstalled the MySQL server.However, now that I have re-installed it, I am being confronted with inability to connect or create a database.Please bear with me for now:I am assiduously working on ordering things out.Thanks

[origen2g]

modi123_1, on 25 May 2010 - 09:03 PM, said:

Define "a series of unsuccessful outcomes". Does that mean you are getting error messages? If so, where, and what are they? Is it not responding how you would like it? If so what is it supposed to be doing vs what it *is* doing.

Actually, thank you for your patience.I have been able to run the program on my laptop.That is, a duplicate file of the .NET codes.When I try to save the datagridview data using the update command of the datagridview,I obtain a message like this CONCURRENCY VIOLATION.THE UPDATE COMMAND AFFECTED ZERO OF THE EXPECTED 1 RECORDS.So, please what should I do?

[modi123_1]

It sounds like you are attempting to edit an auto generated field.. what's your table structure (columns, types, keys) and your update statement look like?

[origen2g]

Actually, I was just attempting to supply fresh data into the database through The Datagridview control.When I click on the update menu of my application, it throws the exception CONCURRENCY VIOLATION.THE UPDATE COMMAND AFFECTED ZERO OF THE EXPECTED 1 RECORDS.Though at times it updates, but it updates one row less than the new rows of data just supplied.For instance If I input 5 rows, it updates only 4 rows to the database.Also If I input 10 rows, it updates only 9 rows out of the 10, etc.I am not editing previous data in the database, i am just supplying fresh data in view of updating the database.Also, there is no Key field and none of the fields are auto generated: I manually type in all the data.Maybe I should suggest a way out:you just give a lines of code that allows you to successfully update a database via the datagridview and I adapt the codes to my own project.However, The sub routine for updating the database is seen below.

Public Sub InsertInformation()

Dim connex As MySqlConnection
Dim theNumber, tryNumber As Integer
Dim MyCommandText, coll As String
Dim NameOne, RankOne, StaffNosOne, DepartmentOne, BankOne, AccountNosOne, BranchOne, AdditionCommentOne, DeductionCommentOne As String
Dim GradeOne, StepOne As Integer
Dim DriverAllowanceOne, PensionOne, VehicleOne, NonAccidentOne, AdditionOne, CTCSOne, DeductionsOne As Double


MyCommandText = " insert into " & MyFileTableText & " values(" & NameOne & "," & GradeOne & "," & StepOne & "," & RankOne & "," & _
StaffNosOne & "," & DepartmentOne & "," & BankOne & "," & AccountNosOne & "," & BranchOne & ");"


Try
connex = New MySqlConnection
connex.ConnectionString = "server=127.0.0.1;" & "uid=root;" & "pwd = instructed;"
connex.Open()
myCommand.Connection = connex
myAdapter.UpdateCommand = myCommand


Dim cmdbuilder As New MySqlCommandBuilder(myAdapter)
Dim i As Integer

Try
i = myAdapter.Update(myDataSet, "myData")
MsgBox("Records Updated= " & i)
Catch ex As Exception
MsgBox(ex.Message)
End Try

Catch myerror As MySqlException 'Exception

MessageBox.Show("Error UpdatingDatabase: " & myerror.Message)
connex.Close()
End Try

connex.Close()
connex.Dispose()
End Sub 

[modi123_1]

origen2g, on 01 June 2010 - 02:00 AM, said:

Actually, I was just attempting to supply fresh data into the database through The Datagridview control.When I click on the update menu of my application, it throws the exception CONCURRENCY VIOLATION.THE UPDATE COMMAND AFFECTED ZERO OF THE EXPECTED 1 RECORDS.Though at times it updates, but it updates one row less than the new rows of data just supplied.For instance If I input 5 rows, it updates only 4 rows to the database.Also If I input 10 rows, it updates only 9 rows out of the 10, etc.I am not editing previous data in the database, i am just supplying fresh data in view of updating the database.Also, there is no Key field and none of the fields are auto generated: I manually type in all the data.Maybe I should suggest a way out:you just give a lines of code that allows you to successfully update a database via the datagridview and I adapt the codes to my own project.However, The sub routine for updating the database is seen below.

Public Sub InsertInformation()

Dim connex As MySqlConnection
Dim theNumber, tryNumber As Integer
Dim MyCommandText, coll As String
Dim NameOne, RankOne, StaffNosOne, DepartmentOne, BankOne, AccountNosOne, BranchOne, AdditionCommentOne, DeductionCommentOne As String
Dim GradeOne, StepOne As Integer
Dim DriverAllowanceOne, PensionOne, VehicleOne, NonAccidentOne, AdditionOne, CTCSOne, DeductionsOne As Double


MyCommandText = " insert into " & MyFileTableText & " values(" & NameOne & "," & GradeOne & "," & StepOne & "," & RankOne & "," & _
StaffNosOne & "," & DepartmentOne & "," & BankOne & "," & AccountNosOne & "," & BranchOne & ");"


Try
connex = New MySqlConnection
connex.ConnectionString = "server=127.0.0.1;" & "uid=root;" & "pwd = instructed;"
connex.Open()
myCommand.Connection = connex
myAdapter.UpdateCommand = myCommand


Dim cmdbuilder As New MySqlCommandBuilder(myAdapter)
Dim i As Integer

Try
i = myAdapter.Update(myDataSet, "myData")
MsgBox("Records Updated= " & i)
Catch ex As Exception
MsgBox(ex.Message)
End Try

Catch myerror As MySqlException 'Exception

MessageBox.Show("Error UpdatingDatabase: " & myerror.Message)
connex.Close()
End Try

connex.Close()
connex.Dispose()
End Sub 

Well there's your problem:


You declare all these variables...

Dim connex As MySqlConnection
Dim theNumber, tryNumber As Integer
Dim MyCommandText, coll As String
Dim NameOne, RankOne, StaffNosOne, DepartmentOne, BankOne, AccountNosOne, BranchOne, AdditionCommentOne, DeductionCommentOne As String
Dim GradeOne, StepOne As Integer
Dim DriverAllowanceOne, PensionOne, VehicleOne, NonAccidentOne, AdditionOne, CTCSOne, DeductionsOne As Double

... and never actually set them to values before you attach them to an insert statement here..

MyCommandText = " insert into " & MyFileTableText & " values(" & NameOne & "," & GradeOne & "," & StepOne & "," & RankOne & "," & _
StaffNosOne & "," & DepartmentOne & "," & BankOne & "," & AccountNosOne & "," & BranchOne & ");"

At no point in that chunk of code do you reference a dataview where you might have 'entered' this data.

Additionally "myAdapter" is never declared.

Finally, and probably most important - your use of the "myAdapter" (MySqlDataAdapter) is for an 'insert' statement, right? That's what your SQL string says, right? Why the hell are you using the myAdapter.UpdateCommand method?


Follow this if you are inserting data from your grid to the database.
http://dev.mysql.com...r-insertcommand

Over all your logic is a bit confusing in what you want to do. Just to be clear you want to take values you typed into a grid and insert them into a database?

If that's the case you don't need to do an update on the dataset after wards since the data is what you inserted, right?

origen2g, on 01 June 2010 - 02:00 AM, said:

Maybe I should suggest a way out:you just give a lines of code that allows you to successfully update a database via the datagridview and I adapt the codes to my own project.

Oh, and for the record, you don't need to suggest a "way out". This is not *my* problem - this is yours. My way out is not returning to this thread and that does *not* include just handing code over to someone at random.

kthnxbi!

This post has been edited by modi123_1: 01 June 2010 - 08:07 AM

[origen2g]

modi123_1, on 01 June 2010 - 07:06 AM, said:

origen2g, on 01 June 2010 - 02:00 AM, said:

Actually, I was just attempting to supply fresh data into the database through The Datagridview control.When I click on the update menu of my application, it throws the exception CONCURRENCY VIOLATION.THE UPDATE COMMAND AFFECTED ZERO OF THE EXPECTED 1 RECORDS.Though at times it updates, but it updates one row less than the new rows of data just supplied.For instance If I input 5 rows, it updates only 4 rows to the database.Also If I input 10 rows, it updates only 9 rows out of the 10, etc.I am not editing previous data in the database, i am just supplying fresh data in view of updating the database.Also, there is no Key field and none of the fields are auto generated: I manually type in all the data.Maybe I should suggest a way out:you just give a lines of code that allows you to successfully update a database via the datagridview and I adapt the codes to my own project.However, The sub routine for updating the database is seen below.

Public Sub InsertInformation()

Dim connex As MySqlConnection
Dim theNumber, tryNumber As Integer
Dim MyCommandText, coll As String
Dim NameOne, RankOne, StaffNosOne, DepartmentOne, BankOne, AccountNosOne, BranchOne, AdditionCommentOne, DeductionCommentOne As String
Dim GradeOne, StepOne As Integer
Dim DriverAllowanceOne, PensionOne, VehicleOne, NonAccidentOne, AdditionOne, CTCSOne, DeductionsOne As Double


MyCommandText = " insert into " & MyFileTableText & " values(" & NameOne & "," & GradeOne & "," & StepOne & "," & RankOne & "," & _
StaffNosOne & "," & DepartmentOne & "," & BankOne & "," & AccountNosOne & "," & BranchOne & ");"


Try
connex = New MySqlConnection
connex.ConnectionString = "server=127.0.0.1;" & "uid=root;" & "pwd = instructed;"
connex.Open()
myCommand.Connection = connex
myAdapter.UpdateCommand = myCommand


Dim cmdbuilder As New MySqlCommandBuilder(myAdapter)
Dim i As Integer

Try
i = myAdapter.Update(myDataSet, "myData")
MsgBox("Records Updated= " & i)
Catch ex As Exception
MsgBox(ex.Message)
End Try

Catch myerror As MySqlException 'Exception

MessageBox.Show("Error UpdatingDatabase: " & myerror.Message)
connex.Close()
End Try

connex.Close()
connex.Dispose()
End Sub 

Well there's your problem:


You declare all these variables...

Dim connex As MySqlConnection
Dim theNumber, tryNumber As Integer
Dim MyCommandText, coll As String
Dim NameOne, RankOne, StaffNosOne, DepartmentOne, BankOne, AccountNosOne, BranchOne, AdditionCommentOne, DeductionCommentOne As String
Dim GradeOne, StepOne As Integer
Dim DriverAllowanceOne, PensionOne, VehicleOne, NonAccidentOne, AdditionOne, CTCSOne, DeductionsOne As Double

... and never actually set them to values before you attach them to an insert statement here..

MyCommandText = " insert into " & MyFileTableText & " values(" & NameOne & "," & GradeOne & "," & StepOne & "," & RankOne & "," & _
StaffNosOne & "," & DepartmentOne & "," & BankOne & "," & AccountNosOne & "," & BranchOne & ");"

At no point in that chunk of code do you reference a dataview where you might have 'entered' this data.

Additionally "myAdapter" is never declared.

Finally, and probably most important - your use of the "myAdapter" (MySqlDataAdapter) is for an 'insert' statement, right? That's what your SQL string says, right? Why the hell are you using the myAdapter.UpdateCommand method?


Follow this if you are inserting data from your grid to the database.
http://dev.mysql.com...r-insertcommand

Over all your logic is a bit confusing in what you want to do. Just to be clear you want to take values you typed into a grid and insert them into a database?

If that's the case you don't need to do an update on the dataset after wards since the data is what you inserted, right?

origen2g, on 01 June 2010 - 02:00 AM, said:

Maybe I should suggest a way out:you just give a lines of code that allows you to successfully update a database via the datagridview and I adapt the codes to my own project.

Oh, and for the record, you don't need to suggest a "way out". This is not *my* problem - this is yours. My way out is not returning to this thread and that does *not* include just handing code over to someone at random.

kthnxbi!

Actually first, I'd like to say I feel you're rather pissed off at my attempt at coding, please don't for I am new at this.Anyway, Thank you for the response you offered thus far and also your observations.The fact is that I never really intended to insert data into the database but rather on the contrary I intended to UPDATE the database.The insert statement codes lines you see should have been commented out.It was an act of error.Actually for the myAdapter variable you claim I did not declare, it was declared as a public variable at the top section of the file (Is that an errorneous action?).I would want you to analyze the UPDATE statement instead.

[modi123_1]

origen2g, on 03 June 2010 - 01:02 AM, said:

<snip>

Actually first, I'd like to say I feel you're rather pissed off at my attempt at coding, please don't for I am new at this.Anyway, Thank you for the response you offered thus far and also your observations.The fact is that I never really intended to insert data into the database but rather on the contrary I intended to UPDATE the database.The insert statement codes lines you see should have been commented out.It was an act of error.Actually for the myAdapter variable you claim I did not declare, it was declared as a public variable at the top section of the file (Is that an errorneous action?).I would want you to analyze the UPDATE statement instead.

Pissed off at your coding? Nope, just pointing out what's wrong.

Okay.. so you are not inserting.. then where's the update statement? You are updating a record that exists, right? I haven't seen any particular verification that that is occurring. This presents a good opportunity to highlight why stored procedures are better than your average hard coded statements. It would be easy to shove the data to a stored procedure on the database, have it verify if the record exists or not and then either insert or update based on that outcome.

RE: "myAdapter ... was declared as a public variable at the top section of the file" It would be well advised to keep that declare inside your routine with the rest of the SQL objects. It would keep track of it with like objects as well as clears it out on each call so you don't muck up with old info in it.