10 Replies - 4235 Views - Last Post: 24 November 2014 - 05:43 PM Rate Topic: -----

#1 Orosolido34   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 22-November 14

Update an acces database using sql statement with parameters

Posted 22 November 2014 - 01:53 PM

HI, I am new to database programing and I am trying to update
an access data base using the code below. the problem is that
whenever I try to run it I get an exception error indicating
that one or more parameters are missing. can any one help to
identify why of the error.
     query = "UPDATE TRUNK_INVENTORY SET Part_Number= ?, Description= ?, Analyzer= ?, Trunk_Location= ?, Qty_On_Hand= ?," & _
                    "Inventory_Count= ?, Last_Entry_Date=? WHERE Part_Number= ?"
            Dim cmd As OleDbCommand = New OleDbCommand(query, MiConexion)

                cmd.CommandType = CommandType.Text

                
                cmd.Parameters.AddWithValue("Part_Number", txtPartNumber)
                cmd.Parameters.AddWithValue("Description", txtDescription.Text)
                cmd.Parameters.AddWithValue("Analyzer", txtAnalyzer.Text)
                cmd.Parameters.AddWithValue("Trunk_Location", txtTrunLocation.Text)
                cmd.Parameters.AddWithValue("Qty_On_Hand", txtQtyOnHand.Text)
                cmd.Parameters.AddWithValue("Inventory_Count", txtInventoryCount.Text)
                cmd.Parameters.AddWithValue("Last_Entry_Date", txtLastUpdate.Text)

                cmd.ExecuteNonQuery()
                MsgBox("Part Number was Updated")
            Catch ex As Exception
                MessageBox.Show(ex.Message & " - " & ex.Source)
            End Try


Is This A Good Question/Topic? 0
  • +

Replies To: Update an acces database using sql statement with parameters

#2 andrewsw   User is offline

  • awks lol ffs
  • member icon

Reputation: 6697
  • View blog
  • Posts: 27,502
  • Joined: 12-December 12

Re: Update an acces database using sql statement with parameters

Posted 22 November 2014 - 02:05 PM

Topic moved to VB.NET forum.

When using ? placeholders you have to supply the Part_Number twice.

Note that OleDb does not support named parameters so that you have to ensure that you supply each parameter in the same order as they appear in the SQL-statement. So repeat the Part_Number as the last parameter.

However, setting the part number is possibly redundant; that is, if you are setting it to the same value.



When using named-parameters you should include the @-sign. However, it shouldn't matter for OleDb.. because it doesn't support them anyway ;)

This post has been edited by andrewsw: 22 November 2014 - 02:15 PM

Was This Post Helpful? 0
  • +
  • -

#3 Orosolido34   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 22-November 14

Re: Update an acces database using sql statement with parameters

Posted 22 November 2014 - 02:30 PM

Thank you for responding so quick,
can you please show me an example?.
I Don't understand when you said
put part_Number Twice.
Was This Post Helpful? 0
  • +
  • -

#4 andrewsw   User is offline

  • awks lol ffs
  • member icon

Reputation: 6697
  • View blog
  • Posts: 27,502
  • Joined: 12-December 12

Re: Update an acces database using sql statement with parameters

Posted 22 November 2014 - 02:34 PM

After this line
cmd.Parameters.AddWithValue("Last_Entry_Date", txtLastUpdate.Text)

add this line
cmd.Parameters.AddWithValue("Part_Number", txtPartNumber)

(assuming that it is the same part-number that you are both looking up and setting).

Quote

I Don't understand when you said
put part_Number Twice.

You have 8 question marks, you need to supply 8 values.

This post has been edited by andrewsw: 22 November 2014 - 02:35 PM

Was This Post Helpful? 0
  • +
  • -

#5 Orosolido34   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 22-November 14

Re: Update an acces database using sql statement with parameters

Posted 22 November 2014 - 02:45 PM

I try to insert the code as you indicated but
now I don't get the error but the database is
not getting updated.
Was This Post Helpful? 0
  • +
  • -

#6 andrewsw   User is offline

  • awks lol ffs
  • member icon

Reputation: 6697
  • View blog
  • Posts: 27,502
  • Joined: 12-December 12

Re: Update an acces database using sql statement with parameters

Posted 22 November 2014 - 02:53 PM

You are trying to UPDATE record(s) WHERE Part_Number= ?, but this is also the value that you are setting Part_Number to; is this correct? For example, you are updating record(s) where the Part_Number is 22, and setting this Part_Number to.. 22.

Or are you trying to create a new record? In which case, the Part_Number won't already exist in the table and your query won't update anything. To create a new record you would use INSERT INTO, not UPDATE.

INSERT INTO Statement (Microsoft Access SQL)



It is also a possibility that you are working with a new copy of the database on each run:

How to: Manage Local Data Files in Your Project

This post has been edited by andrewsw: 22 November 2014 - 03:06 PM

Was This Post Helpful? 0
  • +
  • -

#7 Orosolido34   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 22-November 14

Re: Update an acces database using sql statement with parameters

Posted 22 November 2014 - 03:05 PM

my intention is, to be able to update each and every one
of the textbox when needed, including the Part number.
for example, if I want to update the part number in case
a mistake was made at the entry. then I want to be able
to do that. When I change the code and discard the first
part Number in the update line, I manage to get it to work but I cant update
the part number if I have to.
Was This Post Helpful? 0
  • +
  • -

#8 andrewsw   User is offline

  • awks lol ffs
  • member icon

Reputation: 6697
  • View blog
  • Posts: 27,502
  • Joined: 12-December 12

Re: Update an acces database using sql statement with parameters

Posted 22 November 2014 - 03:18 PM

Please don't add extra line-breaks to your posts, it makes it difficult to read.



I am still unclear what you are trying to achieve, particularly when you talk about updating textboxes: it is the database table that you are trying to update, with values currently in the textboxes.

Your code is attempting to update record(s) with the part-number currently displayed in the part-number textbox. If someone changes this part-number from, say, 22 to 30, then your SQL-statement won't work because it is looking for a record with part-number 30, which doesn't (yet) exist. You have already lost track of the previous part-number (22) and its record.

If the part-number is the primary key then it shouldn't be changed at all. If you need to change it then you should use some other field, perhaps an auto-number, as primary key. Then your UPDATE would be based on this primary key, not on the moving-target of Part_Number.

This post has been edited by andrewsw: 22 November 2014 - 03:24 PM

Was This Post Helpful? 0
  • +
  • -

#9 Orosolido34   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 22-November 14

Re: Update an acces database using sql statement with parameters

Posted 22 November 2014 - 03:38 PM

sorry for the confusion. my primary language is Spanish and
some time is hart to express what we want in other languages.
what Aim trying to do is to update may database using textboxes.
my textboxes already have the information downloaded to then from the database. but
I want to be able to edit this data if necessary. But you answer my question and solve
the problem in your last statement in where you mention the primary key, which in my case
is the part number. Now I know that I cant edit it and it makes perfect sense.
Thank you so much for your time and effort.
Was This Post Helpful? 0
  • +
  • -

#10 andrewsw   User is offline

  • awks lol ffs
  • member icon

Reputation: 6697
  • View blog
  • Posts: 27,502
  • Joined: 12-December 12

Re: Update an acces database using sql statement with parameters

Posted 22 November 2014 - 03:50 PM

You are welcome.

Yes, it is essential that the primary key should not be changed, partly (mainly) for the reason, and problems, that you have just encountered.

The great primary-key debate

This post has been edited by andrewsw: 22 November 2014 - 03:55 PM

Was This Post Helpful? 0
  • +
  • -

#11 Orosolido34   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 22-November 14

Re: Update an acces database using sql statement with parameters

Posted 24 November 2014 - 05:43 PM

Hi,
I am new to database programing even thus I have created a few
programs for self use by copying a pasting some codes.
I have look in the internet for information about using
SQL select, insert, update, statements. But most of the information
is for working with SQL Server. Does any one knows where I can
find a good tutorial on using vb.net and SQL statements for MS Access
database. specialy on using paramatize query.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1