smoothdogg00

My SQL statements are perfectly fine when I test them in the Query Builder, but I can not, for the life of me, figure out why they won't work in my application.

Here is what I have as my SQL statement within my UpdateQuery function:

UPDATE members
SET id = @id, first_name = @first_name, last_name = @last_name, phone_number = @phone_number, date_joined = @date_joined
WHERE (first_name LIKE @first_name and last_name LIKE @last_name)

Here is how I am calling the function:

Me.DsAllMembersBindingSource.EndEdit()
Me.DsAllMembersTableAdapter.UpdateQuery(Me.IdTextBox.Text.Trim(),
Me.First_nameTextBox.Text.Trim(), Me.Last_nameTextBox.Text.Trim(),
Me.Phone_numberTextBox.Text.Trim(), Me.Date_joinedTextBox.Text.Trim())
Me.Close()

Can someone please help


Re: Visual Basic Language My SQL Update function isn't working in VB, can someone help?

ahmedilyas

one of the typical problems is that if you include the database in your project (solution explorer) that you should make sure that the "Copy To Output" property is set to "copy if newer" or "never copy". if you set it to never copy then initially you need to manually copy over the file to the bin\debug (or bin\release) folder in your project folder. This will then prevent it from overwriting the file everytime you build and run your project.

what happens if you do this:

Dim result as Integer = Me.DsAllMembersTableAdapter.UpdateQuery(Me.IdTextBox.Text.Trim(),
    Me.First_nameTextBox.Text.Trim(), Me.Last_nameTextBox.Text.Trim(),                             
    Me.Phone_numberTextBox.Text.Trim(), Me.Date_joinedTextBox.Text.Trim())

MessageBox.Show(result.ToString())

 

what does it return






Re: Visual Basic Language My SQL Update function isn't working in VB, can someone help?

smoothdogg00

The MessageBox returns the value 1.

Sorry, I am new to Visual Basic and I had a lot of trouble trying this project in C# before, which is why I switched over. I just can't get a decent connection and update situation with the SQL database.


EDIT:  Also, the Solution Explorer was set to "Do Not Copy".




Re: Visual Basic Language My SQL Update function isn't working in VB, can someone help?

ahmedilyas

converting from one language to another wont help the problem - itll still remain, you need to get to the root of the problem and fix it. Does the condition evaluate to true or have you tried the whole copy to output thing I suggested earlier




Re: Visual Basic Language My SQL Update function isn't working in VB, can someone help?

smoothdogg00

The table is just not getting updated with the values. I have a form, with all of the values listed above. When I change just first_name, it should change the row in the table with the matching ID, but it doesn't happen and I'm not sure why. I just tried as "Copy If Newer" with the same result as before. The MessageBox does return 1 (true).




Re: Visual Basic Language My SQL Update function isn't working in VB, can someone help?

smoothdogg00

Nobody has an answer This seems like very beginner level stuff...




Re: Visual Basic Language My SQL Update function isn't working in VB, can someone help?

ahmedilyas

someone will get back when they know :-) It is also the end of the weekend! :-)

IF the messagebox returns a value > 0 then it means it is updating the database but something somewhere isnt showing it.

Can you....after doing that update, DO NOT close the application. Open the database, can you see the update






Re: Visual Basic Language My SQL Update function isn't working in VB, can someone help?

smoothdogg00

Yeah, when I return to Visual Studio and preview the data in the table, it isn't updated, which is why I am so lost! Anyway I do appreciate your help. I'm surprised you're the only one who has responded to me in the past 3 evenings. You must be busy!




Re: Visual Basic Language My SQL Update function isn't working in VB, can someone help?

ahmedilyas

very, love helping out :-)

hmm. something odd going on. Funny thing, someone else had a similar issue, even after checking everything over and over, found out that the table adapter/dataset were not correctly created as the user tampered with it causing this to happen.

tried creating a new project and doing the very basic stuff, like inserting 1 field of data






Re: Visual Basic Language My SQL Update function isn't working in VB, can someone help?

smoothdogg00

Well, I created the database in SQL Server Manager Express. Then, I connected to that database through Visual Studio 2005 Express. From VS, I was able to view the database in the database view sidebar and I was also able to run a Query through VS. I ran the exact query I have been attempting, without variables, and it modified the row correctly. [Edited - False Information]

What I am going to do right now is create a new database with a few items IN Visual Studio. I am going to create a new DataAdapter and BindingSource, etc. I will run the same code I currently have and let you know my results.




Re: Visual Basic Language My SQL Update function isn't working in VB, can someone help?

smoothdogg00

I don't know what I did, but I have it working for the moment! I deleted all of my binding sources and datatables and somehow came up with a good end result. Thanks for all of your help, you may see me back here soon! :-)