Dataset overkill for my application?

[modfather]

I'm looking for opinions/suggestions. Please help! I'm a newbie, so please be gentle.

I have a simple windows application that has ten text boxes. They are: txtCompetition, txtCompetitor, and eight scores: txtScore1...txtScore8, etc. There's also a save button. The first two text boxes are just for data validation and to narrow down a competition and a competitor in a competition. And based on those two bits of information, I want to find the ONE record (or none) for this competitor. I then want to either create the record based on the 10 text boxes or update the eight scores. The question I have: are Datasets designed well for such a small amount of data I'm bringing back? Or should I just do ExecuteNonQueries to do my add/changes? I'm struggling a bit with Dataset updates, and I'm NOT using a datagrid, so it seems that databinding is a bit weird for me as well...

Any questions or suggestions would be sooo helpful!
Thanks!
Steve

 

[BG12424]

Is your win form text boxes for navigating between rows in your dataset or is it for entering new information into your database?

Do you have specific code that you can share with us?



regards,
Brian

http://www.gainesconsulting.com

The problem with doing something right the first time is that nobody appreciates how difficult it was.

 

[modfather]

Thanks for your reply! Both fair questions - To explain better:

I'm dealing with 10 text boxes. However, this is NOT a datagrid, just a traditional application form. I don't have any need for navigation between records because the first two textboxes are for a competition number and a competitor number FOR that competition. Once those two fields are filled out, the remaining eight text boxes are integers where I enter scores for that competitor for that competition. Once the first score text box gets focus, I want to open my dataadapter, dataset and fill it. The point is: This competitor may already have scores, in which case I want to fill the text boxes with the dataset values, or they may have no scores, in which case I want to add a new "Results" record, and take the values in the eight textboxes and store them in the database. It's also possible that I may want to delete the "Results" record as well. For this specific problem, though, I want to get the appropriate "Results" record (if there is one) based on the competition number and the competitor number. The reason I asked about overkill ofis because I will always have 0 or 1 record that I am going to have. It is imperitive that this application run very fast, as I do a massive amount of data entry, primarily using just the keyboard and 10-key. Anyway, I'm rambling. Here's my code. A bit of a warning: I'm lost! I left in some of my comments because I'm unsure of the direction to go with this. I assume I need to either have a QueryBuilder or build my own Update script (which I'd rather do, if it's faster), but am still lost.

Private Sub txtScore1_GotFocus(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtScore1.GotFocus
'Dim myvar As String = txtCompetitor.Text
' & txtCompetitor.Text & " and compnum = " & "'txtCompNum.Text'"
'Dim da As OleDbDataAdapter
'Dim ds As New DataSet
da = New OleDbDataAdapter("SELECT ScoreA1, ScoreA2, ScoreA3, ScoreB1, ScoreB2, ScoreB3, ScoreC1, ScoreC2, ScoreC3, Notes FROM results where cardnum = 239 and compnum = '114'", strConn)
ds = New DataSet
Try
da.Fill(ds)
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
Dim dt As DataTable = New DataTable("Results&quot


'Dim pbndTemp As Binding
'pbndTemp = New Binding("Text", ds, "results.ScoreA1&quot
'Me.DataBindings.Add(pbndTemp)
txtScore1.BackColor = Color.FromKnownColor(KnownColor.Aquamarine)
'MessageBox.Show(ds.Tables(0).Rows(0).Item(0))
txtScore1.Text = ds.Tables(0).Rows(0).Item(0)
txtScore1.DataBindings.Add(New Binding("Text", ds.Tables(0), "ScoreA1&quot)
End Sub

Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
ds.Tables("Results&quot.Rows(0).Item("ScoreA1&quot = 77
ds.AcceptChanges()
'ds.results(0).ScoreA1 = 77

'Dim cmdBuilder As New OleDbCommandBuilder(da)
'da.InsertCommand = cmdBuilder.GetInsertCommand
'MessageBox.Show(da.Update(ds).ToString)
cn.Close()
'ds.Tables(0).Rows(0).Item(0) = txtScore1.Text
'MessageBox.Show(txtScore1.Text)
'da.UpdateCommand = cmdbuilder.getupdatecommand
'da.Update(ds)
'cn.Close()
End Sub

 

[BG12424]

Ok, well, have you thought about omitting the dataset and just using a datatable? The DataSet is a container to all the tables, but you can just bind to a DataTable, thus avoiding that extra overhead. I think that this would be an advisable solution for you.

regards,
Brian

http://www.gainesconsulting.com

The problem with doing something right the first time is that nobody appreciates how difficult it was.

 

[modfather]

Sorry for my ignorance, but I can't find anywhere in my books or elsewhere how to do that. Any pointers on where I could look?

 

[BG12424]

Something like this...

Dim dt as DataTable = New DataTable
myAdapter.Fill(dt)
DataGrid1.DataSource = dt
DataGrid1.DataBind

regards,
Brian

http://www.gainesconsulting.com

The problem with doing something right the first time is that nobody appreciates how difficult it was.

 

[modfather]

Brian,

Thanks a bunch! Unfortunately, I'm not using a DataGrid, but I got this working yesterday:

Private Sub txtCompetitor_Leave(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtCompetitor.Leave
txtName.Text = dr.GetString(0) & ", " & dr.GetString(1)
Dim tempTable As New Data.DataTable
Dim kntr As Integer
da = New OleDbDataAdapter("SELECT ScoreA1, ScoreA2, ScoreA3, ScoreB1, ScoreB2, ScoreB3, ScoreC1, ScoreC2, ScoreC3, Notes FROM results where cardnum = " & txtCompetitor.Text & " and compnum = '" & txtCompNum.Text & "'", strConn)
da.Fill(tempTable)
If da.Fill(tempTable) > 0 Then
txtScore1.Text = tempTable.Rows(0).Item(0)
End If
...
txtScore2.Text = tempTable.Rows(0).Item(1)
txtScore3.Text = tempTable.Rows(0).Item(2)
txtScore4.Text = tempTable.Rows(0).Item(3)
txtScore5.Text = tempTable.Rows(0).Item(4)
txtScore6.Text = tempTable.Rows(0).Item(5)
txtScore7.Text = tempTable.Rows(0).Item(6)
txtScore8.Text = tempTable.Rows(0).Item(7)
txtScore9.Text = tempTable.Rows(0).Item(8)
End Sub

So this seems to allow me access to my DataTable, and I'm able to save data back to the DB with an ExecuteNonQuery, but is that the proper way to do that? And I have a little problem with doing all of the assignments to my text boxes like above. I can't get to the textboxes in my Controls, because these "Score" controls are INSIDE a GroupBox. Any suggestions?

Thanks again! You've been great!
Steve