Query update from an Excel File

Question

I have an Excel file with a list of datas that I have to import into an Access db created by me. Inside the db I have a column named 'Description'. It can happens, when the description is more long than a cell, that I have to update my field, but I don't know how to write correctly the query.

In my Excel file the description starts from the C column, so when I want to extract the string I write a line of code like this:

cap.Description = grid(r, 3).Text

I tried to write an Update query, but it doesn't works because VS shows me this message: "additional information no value given for one or more required parameters".

Public Sub updateDB(ByVal PathDB As String)
Dim db As New cDB
Dim v As New cVoice

Dim rs As ADODB.Recordset = db.RecordSet
db.connect_DB(PathDB)
db.get_rs("UPDATE Voice SET Description = @v.Description")

db.close_DB()
End Sub

Public Function get_rs(ByVal query As String) As ADODB.Recordset

    If db Is Nothing Then rs = Nothing : Return rs

    rs = New ADODB.Recordset
    rs.CursorType = ADODB.CursorTypeEnum.adOpenStatic
    rs.LockType = ADODB.LockTypeEnum.adLockOptimistic

    rs.Open(query, db)

    Return rs

End Function

 Public Function connect_DB() As ADODB.Connection

    If Not My.Computer.FileSystem.FileExists(pPathDB) Then db = Nothing : Return db

    db = New ADODB.Connection
    db.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & pPathDB & ";Persist Security Info=False"
    db.Open()

    If db.State = ConnectionState.Closed Then db = Nothing

    Return db

End Function

Then I tried to switch, stupidly, Description = @v.Description with Description = grid(r, 3).Text, but obviously this way doesn't work. So, how can I express that I have to update my db column with C cell in Excel?

Need to assign the parameter a value: learn.microsoft.com/en-us/sql/ado/reference/ado-api/… — Jacob H
– Jacob H, Commented Oct 5, 2017 at 13:06
This doesn't seem to be valid VBA code. For example, you cannot assign something in a declaration as you do in Dim rs As ADODB.Recordset = db.RecordSet. Also, a lot of set commands missing — FunThomas
– FunThomas, Commented Oct 5, 2017 at 13:07
@FunThomas I write other simple querys and the program runs correctly — Matteo
– Matteo, Commented Oct 5, 2017 at 13:13

Ahmad Shaarawi · Accepted Answer · 2017-10-05 13:33:13Z

1

Try to make the query as:

 db.get_rs("UPDATE Voice SET Description  = '" + v.Description +"'")

As this error means that there is wrong spelling for the value or missed value as mentioned here: no value given for one or more required parameters

edited Oct 5, 2017 at 13:33

answered Oct 5, 2017 at 13:17

Ahmad Shaarawi

1918 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Query update from an Excel File

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related