Saving to a spreadsheet

You are using OleDBConnection to connect to the Excel and you must be using OleDbDataAdapter too. This dataadapter has an update method for which you will have to generate an Update Command first. Take a look at the MSDN for the Update Method and how to use it.

PS: Any special reason for creating this new user id?

I now ave the code to perform the update but am not sure how to attach it to a button. The code is:

da.Update(myDataset)

But how can I get the button to perform that? The code I have already is:

Private Sub frmViewer_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
lblForm.Text = ClsGrp
Debug.Print(ClsGrp)
Dim excelCon2 As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\TutGrpPtsManager\points.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1""")
excelCon2.Open()
Dim da2 As New OleDb.OleDbDataAdapter("Select * from [Points$] where ClsGrp Like '" & ClsGrp & "'", excelCon2)
'Dim da2 As New OleDb.OleDbDataAdapter("Select * from [Points$] where ClsGrp = '7rjt'", excelCon2)

Dim ds2 As New DataSet

da2.Fill(ds2)
DataGridView1.DataSource = ds2.Tables(0)
Debug.Print(ClsGrp)

End Sub

Private Sub cmdExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdExit.Click
End
End Sub

Thanks for any help

Cheers for that. I will get onto it straight away

any error on the following statement?

da2.Update(ds2)

as Shuja Ali has already pointed out you should suppy an update command for da2.UpdateCommand property. or might as well, use Command builder.

hope that helps!

you'd simply create a command builder passing the data adapter as its parameter...

Public Class frmViewer
Dim excelCon2 As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\TutGrpPtsManager\points.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1""")
Dim da2 As New OleDb.OleDbDataAdapter("Select * from [Points$] where ClsGrp Like '" & ClsGrp & "'", excelCon2)
Dim _cb as new oledb.oledbcommandbuilder(da2)
Dim ds2 As New DataSet
.
.
.

Please someone help me - I am close to going insane with this problem!

Hi Thread1 here is my code:

Public Class frmViewer
Dim excelCon2 As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\TutGrpPtsManager\points.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1""")
Dim da2 As New OleDb.OleDbDataAdapter("Select * from [Points$] where ClsGrp Like '" & ClsGrp & "'", excelCon2)
Dim _cb As New OleDb.OleDbCommandBuilder(da2)
Dim ds2 As New DataSet

Public Sub frmViewer_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
lblForm.Text = ClsGrp
Debug.Print(ClsGrp)
excelCon2.Open()

da2.Fill(ds2)
DataGridView1.DataSource = ds2.Tables(0)
Debug.Print(ClsGrp)

End Sub

Private Sub cmdExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdExit.Click
LoginForm1.Close()
Me.Close()
End Sub

Private Sub cmdupdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdupdate.Click

'_cb.GetUpdateCommand("update [Points$] where ClsGrp Like '" & ClsGrp & "'", excelCon2)
'da2.UpdateCommand = _cb
da2.Update(ds2, "[" & Points & "]")

End Sub

End Class

I have now added a field to the spreadsheet that has a unique number in it - the studentid. Could this act as a Primar Key?

Thanks again so far

uh oh! it seems like you're going to create the update command manually. it will take your time if you have number of columns in excel :D. well.. you can remove the commandbuilder. and by the way, you must set the IMEX to 0 in the connection string.

Public Class frmViewer
Dim excelCon2 As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\TutGrpPtsManager\points.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=0""")
Dim da2 As New OleDb.OleDbDataAdapter("Select * from [Points$] where ClsGrp Like '" & ClsGrp & "'", excelCon2)
Dim ds2 As New DataSet

Public Sub frmViewer_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
lblForm.Text = ClsGrp
Debug.Print(ClsGrp)
excelCon2.Open()

da2.Fill(ds2)
DataGridView1.DataSource = ds2.Tables(0)
Debug.Print(ClsGrp)



' COL1 here must be the column that holds unique identification for rows (primary key)
Dim update As New OleDb.OleDbCommand("UPDATE [Points$] SET COL2 = ?, COL3 = ? WHERE COL1 = ?", excelCon2)
update.Parameters.Add("@COL2", OleDb.OleDbType.VarWChar, 255, "COL2")
update.Parameters.Add("@COL3", OleDb.OleDbType.VarWChar, 255, "COL3")
update.Parameters.Add("@COL1", OleDb.OleDbType.VarWChar, 255, "COL1")

da2.UpdateCommand = update

End Sub

Private Sub cmdExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdExit.Click
LoginForm1.Close()
Me.Close()
End Sub

Private Sub cmdupdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdupdate.Click

da2.Update(ds2.tables(0))

End Sub

End Class

you're almost there. anyway, here is a simple code that generates the right column names for the update SQL statement.


Dim update As New OleDb.OleDbCommand
Dim sb As New System.Text.StringBuilder("UPDATE [Sheet1$] SET ", 1024)

' assume StudID as the primary key
For Each c As DataColumn In ds2.Tables(0).Columns
If String.Compare(c.ColumnName, "StudID", True) <> 0 Then
sb.AppendFormat("[{0}] = ?,", c.ColumnName)
update.Parameters.Add(String.Format("@{0}", c.ColumnName), OleDb.OleDbType.VarWChar, 255, String.Format("{0}", c.ColumnName))
End If
Next
If sb.Chars(sb.Length - 1) = ","c Then sb.Remove(sb.Length - 1, 1)

' put the primary key in WHERE clause
sb.Append(" WHERE StudID = ?")
update.Parameters.Add("@StudID", OleDb.OleDbType.VarWChar, 255, "StudID")

update.CommandText = sb.ToString()
update.Connection = excelCon2

da2.UpdateCommand = update

This is frustrating now - I am getting the error:
IndexOutOfRangeException was unhandled
Cannot find table o

On the following line:

For Each c As DataColumn In ds2.Tables(0).Columns

Any ideas as to why?

Thanks

Doh - my fault. Pasted the code BEFORE I filled the DS!!

I am now getting the error:

OleDbException was unhandled
Cannot update '(expression)'; field not updateable.

For the line:

da2.Update(ds2.Tables(0))

Is this to do with the primary key? The Column StudID definately doesn't contain any duplicates in it.

Here is the full ocde for the form incase needed:

Public Class frmViewer
Dim excelCon2 As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\TutGrpPtsManager\points.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=0""")
Dim da2 As New OleDb.OleDbDataAdapter("Select * from [Points$] where ClsGrp Like '" & ClsGrp & "'", excelCon2)
Dim ds2 As New DataSet

Public Sub frmViewer_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
lblForm.Text = ClsGrp
Debug.Print(ClsGrp)
excelCon2.Open()

da2.Fill(ds2)
DataGridView1.DataSource = ds2.Tables(0)
Debug.Print(ClsGrp)

Dim update As New OleDb.OleDbCommand
Dim sb As New System.Text.StringBuilder("UPDATE [points$] SET ", 1024)

' assume StudID as the primary key

For Each c As DataColumn In ds2.Tables(0).Columns
If String.Compare(c.ColumnName, "StudID", True) <> 0 Then
sb.AppendFormat("[{0}] = ?,", c.ColumnName)
update.Parameters.Add(String.Format("@{0}", c.ColumnName), OleDb.OleDbType.VarWChar, 255, String.Format("{0}", c.ColumnName))
End If
Next
If sb.Chars(sb.Length - 1) = ","c Then sb.Remove(sb.Length - 1, 1)

' put the primary key in WHERE clause
sb.Append(" WHERE StudID = ?")
update.Parameters.Add("@StudID", OleDb.OleDbType.VarWChar, 255, "StudID")

update.CommandText = sb.ToString()
update.Connection = excelCon2

da2.UpdateCommand = update

End Sub

Private Sub cmdExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdExit.Click
LoginForm1.Close()
Me.Close()
End Sub

Private Sub cmdupdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdupdate.Click

da2.Update(ds2.Tables(0))

End Sub

End Class

Thanks in advance

Hi Thread1, will check the XLS cell protectin thing. Meanwhile, I attach a copy of the spreadsheet. I have deleted the names out of the Name column for confidentiality purposes. I have had to convert it to a TAB delimited file as it wouldn't allow an .xls file to be uploaded!

Thanks so far ;)

thats wierd! I wonder why it doesn't work here then?

Are you using the same code as I posted in my last post? This one:

Public Class frmViewer
Dim excelCon2 As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\TutGrpPtsManager\points.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=0""")
Dim da2 As New OleDb.OleDbDataAdapter("Select * from [Points$] where ClsGrp Like '" & ClsGrp & "'", excelCon2)
Dim ds2 As New DataSet

Public Sub frmViewer_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
lblForm.Text = ClsGrp
Debug.Print(ClsGrp)
excelCon2.Open()

da2.Fill(ds2)
DataGridView1.DataSource = ds2.Tables(0)
Debug.Print(ClsGrp)

Dim update As New OleDb.OleDbCommand
Dim sb As New System.Text.StringBuilder("UPDATE [points$] SET ", 1024)

' assume StudID as the primary key

For Each c As DataColumn In ds2.Tables(0).Columns
If String.Compare(c.ColumnName, "StudID", True) <> 0 Then
sb.AppendFormat("[{0}] = ?,", c.ColumnName)
update.Parameters.Add(String.Format("@{0}", c.ColumnName), OleDb.OleDbType.VarWChar, 255, String.Format("{0}", c.ColumnName))
End If
Next
If sb.Chars(sb.Length - 1) = ","c Then sb.Remove(sb.Length - 1, 1)

' put the primary key in WHERE clause
sb.Append(" WHERE StudID = ?")
update.Parameters.Add("@StudID", OleDb.OleDbType.VarWChar, 255, "StudID")

update.CommandText = sb.ToString()
update.Connection = excelCon2

da2.UpdateCommand = update

End Sub

Private Sub cmdExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdExit.Click
LoginForm1.Close()
Me.Close()
End Sub

Private Sub cmdupdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdupdate.Click

da2.Update(ds2.Tables(0))
End Sub

I will try to convert the file I sent you and then use that one.

I think I know what it was - 2 coloumns in my spreadsheet - the Target and Total columns use formulas.

They are only basic ones e.g. the Total formula is =SUM(D2+F2+G2+H2-E2) and the Target one is =SUM(100-I2)

Is there anyway to get it to save with the formulas in place e.g if they data in columns Total and Target haven't changed, then don't update those columns?

I thought of that but the thing is, I need them to be able to see those 2 columns as they contain important totals. There must be a way of just updating the columns that have changed.

Hi again Thread1

Could you please give me an example of how to do this? I really need to get this finished now. I feel I am on the verge of finishing. This is the last question I promise!

Thanks a lot :)

I have tried what you suggested above but it still displays the Cannot update '(expression)'; field not updateable error

I have sorted it out - all I had to do was make the columns readonly!

e.g.:

DataGridView1.Columns("Pastoral").ReadOnly = True
DataGridView1.Columns("Assessment").ReadOnly = True
DataGridView1.Columns("House").ReadOnly = True
DataGridView1.Columns("Total").ReadOnly = True
DataGridView1.Columns("Target").ReadOnly = True
DataGridView1.Columns("OnTrip?").ReadOnly = True

DOH I am sooooooo thick sometimes ;)

Thanks for all your help everyone