Click here to Skip to main content
15,867,141 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi Everyone, this is 3rd forum I am posting this with no help whatsoever.....
What I am trying to do is very simple but somehow it ends up being an impossible task...
I am working on APP that has a prebuild formatted datagrid. When App runs DataGrid gets populated with Values.
What I need is when App is Closed or crashed those values to be saved in DataBase SQL.
When App is Open I need those values to be loaded in DataGrid.
No matter what I tried I can't save data correctly it either duplicates Same value in full column or doesn't work at all. I don't use "WHERE" statement. So I don't really have a reference my only reference is Row Count. But when I try to use Loop I get an error because I can't keep calling on parameter... I understand I need to fill data adapter but this produces 0 results.

What I have tried:

SqlBUlk Copy- doesn't work for me it simply dumbs one database to another.
DataSet binding - Doesn't work for me because I need to be able to control the rows from the app and binding disables it.
Stored Procedure, SQL Commands produced 0 results - I can only update a defined index cell/row meaning by one at the time. I have reviwed:
Most MSN pages/forums and etc and still no luck....

The Way I load data:
Dim connectionString As String = "Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True"
       Dim sql As String = "Select * FROM Table1"
       Dim connection As New SqlConnection(connectionString)
       Dim dataadapter As New SqlDataAdapter(sql, connection)
       Dim ds As New DataSet()
       connection.Open()
       dataadapter.Fill(ds, "Data") ' Data tags data

       'Count Rows In data Table
       Dim commandRowCount As New SqlCommand("Select COUNT(*) FROM " + "dbo.Table1", connection)
       Dim countStart As Long = System.Convert.ToInt32(commandRowCount.ExecuteScalar())
       TextBox1.Text = countStart


       For x = 0 To countStart - 1
           If x = 0 Then
               DataG.Rows.Add(1)
           ElseIf x < countStart - 1 Then
               DataG.Rows.Add(x)
           End If
           DataG.Rows(x).Cells("Column1").Value = ds.Tables("Data").Rows(x).Item("Column1")
           DataG.Rows(x).Cells("Column2").Value = ds.Tables("Data").Rows(x).Item("Column2")
           DataG.Rows(x).Cells("Column3").Value = ds.Tables("Data").Rows(x).Item("Column3")
       Next

       connection.Close()
   End Sub


Here is what I tried to save data. This obviously works only for one cell and I can't loop. Tst_1 SQL what I wrote as. Update Table1 set Column1=@Column1 and etc':
Private Sub Save_1()

        Dim connetionString As String = "Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True"
        Dim con As New SqlClient.SqlConnection(connetionString)
        con.Open()
        Dim cmd As New SqlClient.SqlCommand("Tst_1", con)

        cmd.CommandType = CommandType.StoredProcedure

        Dim countR As Integer
        countR = DataG.RowCount
        TextBox1.Text = countR
        Dim ds As New DataSet



        cmd.Parameters.Add(New SqlClient.SqlParameter("@Column1", DataG.Item("Column1", rowIndex:=0).Value))
        
        cmd.ExecuteNonQuery()
        con.Close()


Here is another solution that supposed to work.... but produces zero results I assume this is a correct way since it loads data adapter but it does not save anything:
Private Sub named()
        Dim DA As SqlDataAdapter = New SqlDataAdapter
        Dim Parm As New SqlParameter

        Dim connectionString As String = ("Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True")

        Dim con As New SqlClient.SqlConnection(connectionString)

        con.Open()
        Dim ds As New DataSet()

        DA.InsertCommand = New SqlCommand("INSERT Into Table1(Column1, Column2, Column3) Values(@Column1, @Column1, @Column3)", con)
        Parm = DA.InsertCommand.Parameters.Add(New SqlParameter("@Column1", SqlDbType.NVarChar, 50, "Column1"))
        Parm = DA.InsertCommand.Parameters.Add(New SqlParameter("@Column2", SqlDbType.NVarChar, 50, "Column2"))
        Parm = DA.InsertCommand.Parameters.Add(New SqlParameter("@Column3", SqlDbType.NVarChar, 50, "Column3"))
        DA.TableMappings.Add("Table1", "DataG")

        DA.Update(ds, "Table1")
    End Sub
Posted
Comments
[no name] 25-Jun-19 19:30pm    
People can't help because no one gets the point of your code or why you have to "control the rows" (whatever that means).
Member 14072220 26-Jun-19 16:29pm    
Gerry Schmitz: Question is not WHY but HOW. If you are unable to understand I can't help you.
Sinisa Hajnal 26-Jun-19 9:39am    
Create unique identifier in your database (autoincrement ID is fine) - include it in your query and hide it in the datarows. That way you can call insert, update and delete with primary key. And rowindex will not be a problem anymore.
Member 14072220 26-Jun-19 11:56am    
Sinisa Hajnal: I was thinking about this since the only way to update Database is to have a primary key correct?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900