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