[_HAWK_]

Hi I am brand new to SQL, and have read much, but still getting lost, any help is appreciated.
I have VB.Net Exp 2008 with SQL Exp 2008, on a windows form I have 6 databound textboxes and 6 unbound textboxes, in the bound textboxes the records show as the bound dataGridView rows are selected, in the unbound textboxes is where I will enter the new record to be sent to the database. Added an SQL database in the designer which gave me a dataset(MdDB1DataSet) and a tableAdapter(MdTBTableAdapter). My problem is with adding, updating and deleting records. The add records button addes it during debug to the dataGridView, but does not return when I debug again. So this means it is not added to the database. The update button updates the fields in the DGV, but no record was retained. Thanks for the help...

Add Record button:

			 Dim nr As DataRow = MdDB1DataSet.Tables(mdTb).NewRow
			nr(0) = Trim(txtAdd1.Text)
			nr(1) = Trim(txtAdd2.Text)
			nr(2) = Trim(txtAdd3.Text)
			nr(3) = Trim(txtAdd4.Text)
			nr(4) = Trim(txtAdd5.Text)
			nr(5) = Trim(txtAdd6.Text)
			MdDB1DataSet.Tables(mdTb).Rows.Add(nr)
			MdDB1DataSet.Tables(mdTb).AcceptChanges()
			MdTBTableAdapter.Update(MdDB1DataSet)

Update Record button:

			Dim index As Integer
			For index = 0 To dgvMM_Db.Rows.Count - 1
				If dgvMM_Db.Rows(index).Selected = True Then
					MsgBox(" Row # = " & index.ToString)
					MdDB1DataSet.Tables(mdTb).Rows(index).Item(0) = Trim(txt1.Text)
					MdDB1DataSet.Tables(mdTb).Rows(index).Item(1) = Trim(txt2.Text)
					MdDB1DataSet.Tables(mdTb).Rows(index).Item(2) = Trim(txt3.Text)
					MdDB1DataSet.Tables(mdTb).Rows(index).Item(3) = Trim(txt4.Text)
					MdDB1DataSet.Tables(mdTb).Rows(index).Item(4) = Trim(txt5.Text)
					MdDB1DataSet.Tables(mdTb).Rows(index).Item(5) = Trim(txt6.Text)
					MdDB1DataSet.Tables(mdTb).AcceptChanges()
					MdTBTableAdapter.Update(MdDB1DataSet.Tables(mdTb))
				 End If
			Next

[_HAWK_]

Victory is mine...

Ok, after playing around, in the Add Record button I deleted the line:

MdDB1DataSet.Tables(mdTb).AcceptChanges()

And in the Update button I changed this line:

MdDB1DataSet.Tables(mdTb).AcceptChanges()

To:

 MdDB1DataSet.Tables(mdTb).Rows(index).EndEdit()

It all seems to work, so unless you see a problem(and please let me know), I appear to be good/right as rain/happier than a clam/ok you get it...

[PsychoCoder]

It's great you were able to solve it yourself, congrats!

I am however moving this to the VB.NET forum so it's in the right place if someone else if having issues like this

[ZRonZ]

There is a common misconception with beginners (yours truly included) that when you are working in debug mode that the data gets saved to the database table. It doesn't. You really aren't working with the database table, you're working with a dataset which contains only a copy of the data retrieved from the database table when the bound form loads. Saving data in debug mode only goes to the dataset. Unless you specifically code an update to the database, the new data never gets there.

Once you publish and install the solution, without any added code, the data will save to the database table through a statement similar to:

	Private Sub CintasItemBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CintasItemBindingNavigatorSaveItem.Click
		Me.Validate()
		Me.CintasItemBindingSource.EndEdit()
		Me.TableAdapterManager.UpdateAll(Me.CintasSafetyDataSet)

	End Sub

This code fires when you click the disk icon in the navigator bar.

Debug is there to test applications, not run them like runtime. This comes in handy if you are doing a program revision where you need to use the live database. You can play all you want without messing up the live database table.

[Myplace311]

hawkvalley1, on 28 Jan, 2009 - 09:38 AM, said:

Hi I am brand new to SQL, and have read much, but still getting lost, any help is appreciated.
I have VB.Net Exp 2008 with SQL Exp 2008, on a windows form I have 6 databound textboxes and 6 unbound textboxes, in the bound textboxes the records show as the bound dataGridView rows are selected, in the unbound textboxes is where I will enter the new record to be sent to the database. Added an SQL database in the designer which gave me a dataset(MdDB1DataSet) and a tableAdapter(MdTBTableAdapter). My problem is with adding, updating and deleting records. The add records button addes it during debug to the dataGridView, but does not return when I debug again. So this means it is not added to the database. The update button updates the fields in the DGV, but no record was retained. Thanks for the help...

Add Record button:

			 Dim nr As DataRow = MdDB1DataSet.Tables(mdTb).NewRow
			nr(0) = Trim(txtAdd1.Text)
			nr(1) = Trim(txtAdd2.Text)
			nr(2) = Trim(txtAdd3.Text)
			nr(3) = Trim(txtAdd4.Text)
			nr(4) = Trim(txtAdd5.Text)
			nr(5) = Trim(txtAdd6.Text)
			MdDB1DataSet.Tables(mdTb).Rows.Add(nr)
			MdDB1DataSet.Tables(mdTb).AcceptChanges()
			MdTBTableAdapter.Update(MdDB1DataSet)

Update Record button:

			Dim index As Integer
			For index = 0 To dgvMM_Db.Rows.Count - 1
				If dgvMM_Db.Rows(index).Selected = True Then
					MsgBox(" Row # = " & index.ToString)
					MdDB1DataSet.Tables(mdTb).Rows(index).Item(0) = Trim(txt1.Text)
					MdDB1DataSet.Tables(mdTb).Rows(index).Item(1) = Trim(txt2.Text)
					MdDB1DataSet.Tables(mdTb).Rows(index).Item(2) = Trim(txt3.Text)
					MdDB1DataSet.Tables(mdTb).Rows(index).Item(3) = Trim(txt4.Text)
					MdDB1DataSet.Tables(mdTb).Rows(index).Item(4) = Trim(txt5.Text)
					MdDB1DataSet.Tables(mdTb).Rows(index).Item(5) = Trim(txt6.Text)
					MdDB1DataSet.Tables(mdTb).AcceptChanges()
					MdTBTableAdapter.Update(MdDB1DataSet.Tables(mdTb))
				 End If
			Next

I would like to tell you about 2 alternative methods.
I think you might like them.
1) For each aRow as DataGridViewRow in dgv_YourGV.Rows. . . If aRow.Selected . . . NEXT
2) For each aRow as DataGridViewRow in dgv_yourGV.SelectedRows

Also There is a type sqlComBuilder.

I do this:

Try
dim strSql as string = "SELECT * FROM"
dim sqlDA as new sqlClient.sqlDataAdaptor(strSql,"ConString")
dim sqlComBuilder as new sqlClient.sqlCommandBuilder(sqlDA)
sqlDA.InsertCommand = sqlComBuilder.GetInsertCommand ''Do this for each command you need insert, delete
' ,uptade
dim tblRetreivedData as new DataTable
sqlDa.Fill(tblRetreivedData)

'At this point i would use the same princalbes you did for the .newRwo.
'The only differancve is that i would not use the line Accept changes. this way the row is dirty and need to update.
'then when passed through the .Update it will be identifyed as needing updates.

'Also i like to do the following for debugging purposes.

dim intRowsAffected as integer = myDataAdtaptor.Uptated(myDataTable)
debug.print(intRowsAffected.ToString)''Now i know how manny rows were updated, inserted and deleted all at once

Catch Ex as Exception
MsgBox(Ex.ToString)
end try

I am happy to teach. so please let me know if ya have any more questions.


PS I dont know what this other post is about, if you fire an sql command at and SQL database it will change the Database. so please backup the database as often as possibel if it is ecential.

Also i know i cant spell, dont need to know how to spell to programm.

This post has been edited by Jayman: 29 January 2009 - 08:56 PM

[_HAWK_]

Hi Myplace311,
I appreciate your response, I am self taught, and I read as much as I can, your post has some things I have not read about. I will make those changes, it is hard to get all the right stuff in all the google searches, but I get things here and there. When I first set up the program I could see when just the dataset was holding the info, or at least I think so, when the DataGridView would change and then I would stop the debug and no data was in the database when I would look in the 'Show Table Data' in the Database Explorer. So, if by my code(last post) it changes here 'Show Table Data' - is that if fact updating the database or not? Cause it does show up there right now. As for speeling I am not so good at that eitgher... Thank God for intellisense...
Thanks again...

Don

FYI: close only counts in horseshoes, hand granades and nuclear missiles...

[_HAWK_]

Also ZronZ thank you for your response as well, there is a lot for me to digest here, I feel I'm starting to get it though...

Not sure why my last post was Quoting the whole thread, musta hit somethin waaassmmaacckk

[Myplace311]

When testing any of the programs i write, the data changes if it is working.
''if you see the changes on the show data then i would have to say it is making the changes

The main reason for this is lets say i make one form
with one gridview
and 3 buttons
i can set button one to set the Gridviews data source to tdatatable one
i can set button one to set the Gridviews data source to tdatatable two
i can set button one to set the Gridviews data source to tdatatable three


The other advantage is you are able to eaisley change the connection string during code pritty eaisily.


So when using the datadound contrle it is possible that it is not updated during debug, i would like to here more about this myself

This post has been edited by Myplace311: 30 January 2009 - 05:24 AM

[Myplace311]

Mostly self tought aswell.
the For Each loop i showed you befor is doing this-
Something can be any objecs(all classes are derived from object so ANYTHING) Also you can create a new instance in the line, you do not need to dim it befor hand

For eash SOMETHING IN somthingElse

next

for each aTextBox as TextBox in frmMyForm.controles
''this is your name for the current controle in the loop
Debug.print( aTextBox.Name)
 aTextBox.text = "New Text"
next

This post has been edited by Myplace311: 30 January 2009 - 07:36 AM

[_HAWK_]

Yeah I accually knew that, it was a long day, I also noticed when howering over the variable it will tell what it picked ' Dim Index as Integer' so if I don't like what it picks I would have wrote ' For Each Item as ListViewItem in List1... when I am tired I write all crasy like that... But back to topic so as long as I update the table adpter it seem to update the database, as in my Add Button I used MyTableAdapter.Update(nr) 'nr being the .NewRow on the dataset that was holding the table info.

In my delete row button I just wrote the SQL statement: "DELETE FROM MdTB WHERE @Md1 = MdColumn1Name"

set the command.parameter.add("@Md1)
then command.parameter("@Md1").value = txtadd.text
and this deletes the row from the Show Table Data page
then I also had to delete it from the dataset to refresh the DGV

this could have also been the case for the INSERT and UPDATE buttons right?

"INSERT INTO MdTB(cl1, cl2, cl3, cl4, cl5)
VALUES (@Md1, @Md2, @Md3, @Md4, @Md5)

then add the WHERE clause to the above for the UPDATE statement to find the row

[Myplace311]

Ya that should work out for ya, let me know how you make out,your method is a little differeant thatn mine

This post has been edited by Myplace311: 30 January 2009 - 02:35 PM

[_HAWK_]

Mplace311, my insert statement is working fine, but I am wanting to not add any duplicate in the 1st column I have tried several ideas with a WHERE clause and I get a syntax error near 'WHERE'

INSERT INTO MdTB(cl1, cl2, cl3)
VALUES (@md1, @md2, @md3)
WHERE NOT EXIST (SELECT * FROM MdTB.cl1)
or I tried
WHERE @md1 <> cl1

So am I going about this all wrong?
This is the primary key column, FYI.

[_HAWK_]

Ok, got it working in the database but it still goes to the dataset and into the DGV, more work.
Ok, got rid of the NewRow code then refill the dataset via the tableAdapter... now the DGV refreshes when new items are added, yeah... does this sound correct?

IF NOT EXISTS (SELECT * FROM MdTB WHERE cl1 = @Md1)
...insert statement

This post has been edited by hawkvalley1: 31 January 2009 - 01:08 PM