CODEWITHSUNDEEP
vb.net
user7261203
user7261203

Reputation:

How can I write a query in vb

I have to write a query in vb to update my Access db. I write this code:

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

    Dim rs As ADODB.Recordset = db.RecordSet
    db.connetti_DB(PathDB)
    db.get_rs("UPDATE Voci SET Descrizione = @v.Descrizione")

    db.close_DB()
End Sub

but it doesn't work. Visual Studio shows me this error: "additional information no value given for one or more required parameters"

I tried to do a SELECT query and it works fine:

Dim db As New cDB

    db.connect_DB(PathDB)
    db.get_rs("DELETE * FROM Voice")
    db.get_rs("SELECT Code_Chapter, Code_Paragraph, Description, Code_Voice, Price FROM Voice")

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.CursorLocation = ADODB.CursorLocationEnum.adUseClient
    'rs.LockType = ADODB.LockTypeEnum.adLockBatchOptimistic
    rs.LockType = ADODB.LockTypeEnum.adLockOptimistic

    rs.Open(query, db)

    Return rs

End Function

Where is the mistake?

Upvotes: 0

Views: 1043

Answers (1)

Steve
Steve

Reputation: 216243

The problem you experience is caused by the string "@v.Descrizione". For ADODB this is a parameter placeholder (not the value of the class field cVoce.Descrizione) and thus it requires you to provide the 'additional' information about an expected parameter that you don't provide.

You can simply concatenate the string value on the spot, but this is the worst of possible solutions, leading to errors in parsing if the string contains a single quote or worse to some kind of Sql Injection (albeit Access is not an easy target).

So you need to use parameters when you run this kind of queries.

I have found some old code in my source code repository. This code was used in an old ASP classic web site (VBScript no less) converted to a more modern ASP.NET site. It is no more in use by me and by my firm. So I can share it with you.

First you need to create this method (You can even make it part of your cDB class and probably use the connection available there)

Function GetCommand(commandText as String, ADODB.Connection conn) as ADODB.Command
    Dim cmd = CreateObject("ADODB.Command")
    cmd.Activeconnection = conn
    cmd.CommandText = commandText
    cmd.CommandType = adCmdText
    return cmd
End Function

Now you call the method passing the command string with a parameter placeholder for the Description field 

Dim cmd = GetCommand("UPDATE Voci SET [Description] = ?", db.GetConnection)

You add the parameters to the cmd object 

    cmd.Parameters.Append cmd.CreateParameter("@0", adChar,,, v.Descrizione)

Finally you execute the command without getting back a recordset (it is an UPDATE query after all and there is no return data from it)

    Dim recordsChanged as Integer
    cmd.Execute(recordsChanged,,adExecuteNoRecords)
    if recordsChanged > 0 Then
        Console.WriteLine("Update succesfull")

I have put the field name Description between square brackets. It is unclear what is the right name. Description or Descrizione? In the first case you need square brackets because Description is a reserved word for MS-Access.

Again, if the budget and the time allows it, I strongly suggest to move your code to ADO.NET library and discard the convoluted ADODB COM interface. You can even investigate the pros and cons of an ORM to distance your code from the database interface. There are some ORM very useful like Entity Framework for its completeness or Dapper for its speed and simplicity.

Upvotes: 3

Related Questions