UPDATE EXCEL Query

[Piya27]

Hi All,

My update query refuses to update the data in the Excel sheet via OLEDB connection.
I am able to insert and retireve the records but could not update.
Though my syntax is correct and when I execute the query using ExecuteNonQuery, it shows no error no exceptions but also not updating the data in excel sheet

[sknake]

That is unfortunate. Why don't you post the query and related code to the update so we can see where the problem may exist?

[Piya27]

Dim DbConnString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                              "Data Source=" & fpath & ";" & _
                              "Extended Properties=""Excel 8.0;HDR=YES"""
        Dim DbConn As New OleDb.OleDbConnection 'DbCon Connection
        DbConn.ConnectionString = DbConnString

        Dim con As New SqlConnection(connection_string)
        con.Open()

Dim ast_liab_query As String
 ast_liab_query = "SELECT * From Company_Profile Where Company_Profile.serial_no = '" & TextBox1.Text & "'"

 Dim ast_liab_adapter As New SqlDataAdapter(ast_liab_query, con)
        Dim ast_liab_dset As New DataSet()
        Dim ast_liab_cmd As New SqlCommand(ast_liab_query, con)
   ast_liab_adapter.Fill(ast_liab_dset)
        ast_liab_adapter.Dispose()
        Dim ast_liab_dr As SqlDataReader
        ast_liab_dr = ast_liab_cmd.ExecuteReader
Dim ast_liab_colCount As Integer = ast_liab_dset.Tables(0).Columns.Count

While ast_liab_dr.Read
            ''string for insert and delete command
            For i As Integer = 0 To ast_liab_colCount - 1
         If i = ast_liab_colCount-1 Then
                    ast_liab_updatecmd1 = ast_liab_updatecmd1 & "'" & ast_liab_dr.GetName(i) & "' = '" & ast_liab_dr.GetString(i) & "' "
                Else
                    ast_liab_updatecmd1 = ast_liab_updatecmd1 & "'" & ast_liab_dr.GetName(i) & "' = '" & ast_liab_dr.GetString(i) & "', "
                End If
Next

End While
ast_liab_dr.Close()

 Dim ast_liab_excelcmd As New OleDbCommand()
        ast_liab_excelcmd.Connection = DbConn

Dim ast_liab_duplicate As Integer = countDuplicacy(fpath, "AssetsLiabilities")

            DbConn.Open()
            If (ast_liab_duplicate = 1 Or ast_liab_duplicate > 1) Then
                Try
                    ast_liab_excelcmd.CommandText = "UPDATE AssetsLiabilities SET " & ast_liab_updatecmd1 & " WHERE 'Serial No' = '" & TextBox1.Text & "'"
                    ast_liab_excelcmd.ExecuteNonQuery()
                Catch
                End Try

[Piya27]

AssetsLiabilities is the Sheet name.

[samir_ibrahim]

I begin recently large excel automation & manipulation project. and I have vs2008 and office 2007

First, I note that when I use HDR=YES in the connection string, it ask for ISAM driver so I omit it and my connection string became like this.

Dim _xls_loc = "c:\test.xls"
Dim _xls_cnn_str As String = "Provider=Microsoft.Jet.OleDb.4.0;data source=" & _xls_loc & ";Extended Properties=Excel 8.0;"

Second, you have to decide how you want to deal with the sheet name as a table, it is kinda tricky. I know 3 ways

select * from [sheet1$]
Select * From [sheet1$A1:B10]

or this one which i am currently using, create a Name for the excel range you want to use. in excel 2007 you have to go Formulas >> Define Name after you select the range you want to use and give it a name, then you can use this name in the excel as select * from mynamedexcel Don't forge to use [] when dealing with field name contains spaces such as [Serial No] not 'Serial No'

try this

ast_liab_excelcmd.CommandText = "UPDATE [AssetsLiabilities$] SET " & ast_liab_updatecmd1 & " WHERE [Serial No] = '" & TextBox1.Text & "'"

this is a good site for Excel ADO & automation
http://www.homeandlearn.co.uk/excel2007/excel2007s7p6.html

hth

[Piya27]

ast_liab_excelcmd.CommandText = "UPDATE [AssetsLiabilities$] SET " & ast_liab_updatecmd1 & " WHERE [Serial No] = '" & TextBox1.Text & "'"

I have saved the column name as 'Serial No' with quotes also in the name,only beacause of the space issue. I have tried using sheet name as [AssetsLiabilities$] but that still cant update.

I am also using Excel2007 nd VS2008 and my excell sheet data has about 500 columns, but when i update all 500 columns atonce , I recieve an error: "Too many feilds defined", so I differentiated my data into sheets like I have shown here is Assets n Liabilities Sheet. and that has 200columns nd now I dunt get any error it also executes the query but my sheet doesnt seem to be updated. May b I am missing some point. :(

[samir_ibrahim]

I have saved the column name as 'Serial No' with quotes also in the name,only beacause of the space issue

Very nice from you.

Are you aware that putting the field name between quotes is INVALID ?

[Piya27]

Ohhh!!!
Actually I just did that because of space between the column names. May be I should try using []. Actually I am saving my column names as 'Serial No', 'Compnay Name', 'Branch' etc.
And my data gets exported to excel with these header names also with ' included in the names.

[Piya27]

Thanks for the help...
That colon issue was causing the problem. using [] has solved the problem. Now I am able to update.

Thanks alot.
:)

[samir_ibrahim]

Glad I helped.

Have a nice day.