Critical problem ??[Resolved by Lunatic3]

[Pirate]

I'm almost done with my big database class . anyways , it's reuseable except one function that saves to db . I used this code below but it it doesn't save all the data to the same record . It follow this sequence :
1
_ 2
__ 3
___ 4
____ 5 etc
Thanks in advance !
[Highlight=VB]
see the template

[WuzZeH]

I have to say I'm no expert at ADO.NET (still learning for my 70-306 exam) but I have noticed the below line of code:

mydataset.Tables(TableStr).Rows.Add(MyDataRow)

In the way you have coded it, it will add a new row to the table per field that is passed in. Possible solutions:

* The function checks for the existence of a record via a unique identifier, and then inserts/updates accordingly.

* Separate functions for inserting and updating records.

* All the fields could be inserted/updated at once but the code could be tricky if you want to make your class truly scalable.

Also, another suggestion for your function is to make it more scalable in the way you pass in controls. With your code, you are assuming that every control you pass in has a .text property, this might not always be the case, what about Radio Buttons and Checkboxes?

Hope that helps.

[Negative0]

The way I would approach it would be something like this:

Add a function to your SaveDB class that just adds an attribute to the current row.

Once you have added all of the attributes to the current row, then run a final routine called SaveDB which writes your data to the DB.

Here is a fake example of what I mean:

Public Class clsSaveDB
    Private arrData As ArrayList
    Private mstrTable As String
    Structure structData
        Public txtData As String
        Public txtColumn As String
    End Structure
    Public Sub New(ByVal strTable As String)
        arrData = New ArrayList()
        mstrTable = strTable
    End Sub
 
    Public Function AddItem(ByVal txtData As String, ByVal txtColumn As String)
        Dim tmp As structData
        tmp.txtData = txtData
        tmp.txtColumn = txtColumn
        arrData.Add(tmp)
    End Function
 
    Public Function SaveDB()
        Dim strSQL As String
        Dim x As Long
        strSQL = "INSERT INTO " & mstrTable & " ("
        For x = 0 To arrData.Count - 1
            strSQL = strSQL & arrData(x).txtColumn & ","
        Next
        'remove the last comma
        strSQL = Left(strSQL, strSQL.Length - 1) & ") VALUES ("
        For x = 0 To arrData.Count - 1
            strSQL = strSQL & "'" & arrData(x).txtData & "',"
        Next
        strSQL = Left(strSQL, strSQL.Length - 1) & ")"
 
        yourdb.execute(strSQL)
 
    End Function
End Class
 
'Calling code
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim x As New clsSaveDB("table1")
        x.AddItem("x", "7y")
        x.AddItem("x", "7yz")
        x.AddItem("x", "7ya")
        x.AddItem("x", "7yt")
 
        x.SaveDB()
 
    End Sub

[Pirate]

Thanks a lot both of you guys . I will try either ways and feeb back here .

[Pirate]

Negative0 , your trick is cool but don't you thing this would make all this process slow ?? Since you've used Loop and arrays ?
Thank you

[Negative0]

I dont think it would be too slow even if you had a lot of columns. If you notice, the only time you are going to the database is after the information is already gathered.

In your example, you were hitting the database on every single add statement. If anything, it would speed up the processing.

[Pirate]

That's right . but ...

Hmmm , how would change this line to save values to datarow

MyDataRow(ColumStr) = ctrl.Text

How to save these values to db ??
Thank you dude

[Negative0]

If you want to keep doing it the way you had it, then you need to create a new row outside of your "SaveDB" function. You should only write things to the db or the datarow when you have the entire row stored in memory.

The reason you are getting your

1
_ 2
__ 3
___ 4
____ 5 etc

effect is because you are creating a new row everytime you add a column. You need to create a new row once and then keep adding things to that row. Once you are done with the row, then update the DB.

[Pirate]

umm , I'm ganna follow your code . Let me figure how to add the array to datarow obj .
I really appreciate that Negative0 .

[Pirate]

It looks hard to use DataRow in this case I'm not sure though. any hints please ?

[Pirate]

see the template ....

[Lunatic3]

You are defining a new dataset called fill_dataset. This dataset does not contain any datatable (you have not added any) so you cant make a refrence to t_table.thats why the first exception is thrown.

[Pirate]

It's declared and being filled in "Additem_datarow" function . I guess all this crap won't work . Do you have any other code to share Lunatic3 ?

[Lunatic3]

Sure, let me review the posts carefully and see what i could do about it, however it will be great if you clearly tell me what you have (connections, datasets...) and what you need.

Thanks

[Pirate]

Thank you my friend Lunatic3 .

I'm ganna create a little simulation for this situation and send it here so that it becomes more easier to try on the same proj !

[Pirate]

Ok , here is a template proj that we can both play with it . Thanks for your time.

[Pirate]

just to be notified , that I've used XP access . It should be compatiable with Access 2000 .

[Lunatic3]

I get no error running your project and the data are added just fine! do you face errors??

[Pirate]

Lunatic3 , do you know what I'm talking about ? This is only sample of my code .I know it's running well .

Please read the post carefully , if you didn't understand what I need then , let me know and I will explain that in details .
Thanks

[Lunatic3]

I thought your problem was that it added the fileds in this way:
1
-- 2
----3
......

so please describe more, sorry for being dumb

[Pirate]

hmm , I'll do my best to describe it , if you still unable to get it , notify me

You see this piece of code :

MyDataRow("C_URL") = ctrls(0).Text
MyDataRow("C_username") = ctrls(1).Text
MyDataRow("C_password") = ctrls(2).Text
MyDataRow("C_comment") = ctrls(3).Text

It gets data FROM four textboxes and saves them TO four Columns in the database . It's working , but I need it to be more scalable . Meaning , I can add data in the future without rewriting soure code . Got it ?

[Lunatic3]

Ok, see if it is what you want:

by any means - that you are convinient with- gather an array of objects and instead of ctrls() pass that array -that I call it desiredvalues()- to your function. This array lenght should be the same as number of the fileds in that table and the order you add data to it, the same as you like it to be added to your table.
Then in your class just write this line of code instead of that you mentioned above:

MyDataRow.ItemArray = desiredvalues

[Pirate]

I'm sorry I don't follow !! I can understand code rather than talks .
Thanks

[Lunatic3]

ok

This code goes into your form where you want to save to database:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim desiredvalues() As Object = {TextBox1.Text, TextBox2.Text, TextBox3.Text, TextBox4.Text}
' Alternativley you can loop through your controls and add their values to this array
'
'
            SaveDB(desiredvalues, "MyTab")
'
 
End Sub

and in your class:

'
'
Public Shared Function SaveDB(ByVal desiredvalues As Object, ByVal TableStr As String)
 
'
'
'
Dim MyDataRow As DataRow = mydataset.Tables(TableStr).NewRow
 
MyDataRow.ItemArray = desiredvalues 'here you add them all
 
mydataset.Tables(TableStr).Rows.Add(MyDataRow)
MyAdapter.Update(mydataset, TableStr)
MyConnection.Close()
'
'

[Pirate]

aah thanx , I will give it a shot !

[Pirate]

Perfect !

My friend Lunatic3 I owe , Thank you so much . You just did it . I was afraid to use arrays and fail . Thank you all over !

[Pirate]

Originally posted by WuzZeH
I have to say I'm no expert at ADO.NET (still learning for my 70-306 exam) but I have noticed the below line of code:



In the way you have coded it, it will add a new row to the table per field that is passed in. Possible solutions:

* The function checks for the existence of a record via a unique identifier, and then inserts/updates accordingly.

* Separate functions for inserting and updating records.

* All the fields could be inserted/updated at once but the code could be tricky if you want to make your class truly scalable.

Also, another suggestion for your function is to make it more scalable in the way you pass in controls. With your code, you are assuming that every control you pass in has a .text property, this might not always be the case, what about Radio Buttons and Checkboxes?
Hope that helps.

Can you guess what does this guy mean by "Radio Buttons and Checkboxes" ?

I really need ideas to have more scalable database . I don't want to build my code again . I overloeaded a lot of functions for search , delete , addition , working on editing now ,
Do you think I am missing something important ??
Thanks

[Lunatic3]

First:

You are most welcome

Second:

Let me tell you what he means:
In your code you passed the 'Control' to your function, and there used its .Text property, but there are cases that you dont want to pass text, you may need to pass a True/False (based on a radio button or check box) or DateTIme based on a DateTimePicker to your database. In such cases that code was useless. But with the current code you pass your array of objects to the save function and you shoulnt face any problem I guess.

[Pirate]

Now , this makes sense at least ! but my question now How would I include these features ? I need ideas or a link would be great though !

[Lunatic3]

I guess you dont need any extra coding. All you have to do is adding what you need to your array.

[Pirate]

lol , That's enough for today !
enjoy your coding !