Hello,
I have created 3 functions to insert, update and delete an Access
database record. The Insert and the Delete code are working fine.
The update is not. I checked and my database has all the necessary
records in it when testing it. I get the error "No value given for one
or more required parameters." when I try to update the database.
Can you tell me what am I doing wrong?
Thanks,
Miguel
These are my 3 codes:
UPDATE
' Set Connection
Dim connectionString As String = AppSettings("connectionString")
Dim dbConnection As IDbConnection = New
OleDbConnection(connectionString)
' Set Query and Command
Dim queryString As String = "UPDATE [t_news] SET [news_id]=@news_id,
[title_pt-PT]=@title_pt-PT, [title_en-GB]=@title_en-GB,
[text_pt-PT]=@text_pt-PT, [text_en-GB]=@text_en-GB,
[publication_date]=@publication_date WHERE ([t_news].[news_id] =
@news_id)"
Dim dbCommand As IDbCommand = New OleDbCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection
' Add Parameters
With dbCommand.Parameters
.Add(New OleDbParameter("@news_id", 1))
.Add(New OleDbParameter("@title_pt-PT", "Test UPDATE - title PT"))
.Add(New OleDbParameter("@title_en-GB", "Test UPDATE - title EN"))
.Add(New OleDbParameter("@text_pt-PT", "Test UPDATE - text PT"))
.Add(New OleDbParameter("@text_en-GB", "Test UPDATE - text EN"))
.Add(New OleDbParameter("@publication_date",
DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss")))
End With
' Update Record
dbConnection.Open()
Try
dbCommand.ExecuteNonQuery()
Finally
dbConnection.Close()
End Try
INSERT
' Set Connection
Dim connectionString As String = AppSettings("connectionString")
Dim dbConnection As IDbConnection = New
OleDbConnection(connectionString)
' Set Query and Command
Dim queryString As String = "INSERT INTO [t_news] ([title_pt-PT],
[title_en-GB], [text_pt-PT], [text_en-GB], [publication_date]) VALUES
(@title_ptPT, @title_enGB, @text_ptPT, @text_enGB, @publication_date)"
Dim dbCommand As IDbCommand = New OleDbCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection
' Add Parameters
With dbCommand.Parameters
.Add(New OleDbParameter("@title_ptPT", "Test INSERT - title PT"))
.Add(New OleDbParameter("@title_enGB", "Test INSERT - title EN"))
.Add(New OleDbParameter("@text_ptPT", "Test INSERT - text PT"))
.Add(New OleDbParameter("@text_enGB", "Test INSERT - text EN"))
.Add(New OleDbParameter("@publication_date",
DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss")))
End With
' Insert New Record
dbConnection.Open()
Try
dbCommand.ExecuteNonQuery()
Finally
dbConnection.Close()
End Try
DELETE
' Set Connection
Dim connectionString As String = AppSettings("connectionString")
Dim dbConnection As IDbConnection = New
OleDbConnection(connectionString)
' Set Query and Command
Dim queryString As String = "DELETE FROM [t_news] WHERE
([t_news].[news_id] = @news_id)"
Dim dbCommand As System.Data.IDbCommand = New
System.Data.OleDb.OleDbCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection
' Add Parameters
dbCommand.Parameters.Add(New OleDbParameter("@news_id", 2))
' Delete Record
dbConnection.Open()
Try
dbCommand.ExecuteNonQuery()
Finally
dbConnection.Close()
End Try
3  3302 
Hi Miguel:
This is a Friday afternoon guess:
I believe with the OLE-DB provider parameters are based on position,
not name. Try adding an additional OleDbParameter for the @news_id in
the WHERE and see what happens.
--
Scott http://www.OdeToCode.com/blogs/scott/
On Fri, 8 Jul 2005 17:30:30 +0000, "Shapper"
<mdmoura*NOSPAM*@gmail.*DELETE2SEND*com> wrote: Hello,
I have created 3 functions to insert, update and delete an Access
database record. The Insert and the Delete code are working fine.
The update is not. I checked and my database has all the necessary
records in it when testing it. I get the error "No value given for one
or more required parameters." when I try to update the database.
Can you tell me what am I doing wrong?
Thanks,
Miguel
These are my 3 codes:
UPDATE
' Set Connection
Dim connectionString As String = AppSettings("connectionString")
Dim dbConnection As IDbConnection = New
OleDbConnection(connectionString)
' Set Query and Command
Dim queryString As String = "UPDATE [t_news] SET [news_id]=@news_id,
[title_pt-PT]=@title_pt-PT, [title_en-GB]=@title_en-GB,
[text_pt-PT]=@text_pt-PT, [text_en-GB]=@text_en-GB,
[publication_date]=@publication_date WHERE ([t_news].[news_id] =
@news_id)"
Dim dbCommand As IDbCommand = New OleDbCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection
' Add Parameters
With dbCommand.Parameters
.Add(New OleDbParameter("@news_id", 1))
.Add(New OleDbParameter("@title_pt-PT", "Test UPDATE - title PT"))
.Add(New OleDbParameter("@title_en-GB", "Test UPDATE - title EN"))
.Add(New OleDbParameter("@text_pt-PT", "Test UPDATE - text PT"))
.Add(New OleDbParameter("@text_en-GB", "Test UPDATE - text EN"))
.Add(New OleDbParameter("@publication_date",
DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss")))
End With
' Update Record
dbConnection.Open()
Try
dbCommand.ExecuteNonQuery()
Finally
dbConnection.Close()
End Try
INSERT
' Set Connection
Dim connectionString As String = AppSettings("connectionString")
Dim dbConnection As IDbConnection = New
OleDbConnection(connectionString)
' Set Query and Command
Dim queryString As String = "INSERT INTO [t_news] ([title_pt-PT],
[title_en-GB], [text_pt-PT], [text_en-GB], [publication_date]) VALUES
(@title_ptPT, @title_enGB, @text_ptPT, @text_enGB, @publication_date)"
Dim dbCommand As IDbCommand = New OleDbCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection
' Add Parameters
With dbCommand.Parameters
.Add(New OleDbParameter("@title_ptPT", "Test INSERT - title PT"))
.Add(New OleDbParameter("@title_enGB", "Test INSERT - title EN"))
.Add(New OleDbParameter("@text_ptPT", "Test INSERT - text PT"))
.Add(New OleDbParameter("@text_enGB", "Test INSERT - text EN"))
.Add(New OleDbParameter("@publication_date",
DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss")))
End With
' Insert New Record
dbConnection.Open()
Try
dbCommand.ExecuteNonQuery()
Finally
dbConnection.Close()
End Try
DELETE
' Set Connection
Dim connectionString As String = AppSettings("connectionString")
Dim dbConnection As IDbConnection = New
OleDbConnection(connectionString)
' Set Query and Command
Dim queryString As String = "DELETE FROM [t_news] WHERE
([t_news].[news_id] = @news_id)"
Dim dbCommand As System.Data.IDbCommand = New
System.Data.OleDb.OleDbCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection
' Add Parameters
dbCommand.Parameters.Add(New OleDbParameter("@news_id", 2))
' Delete Record
dbConnection.Open()
Try
dbCommand.ExecuteNonQuery()
Finally
dbConnection.Close()
End Try
Hi,
I did that:
' Set Query and Command
Dim queryString As String = "UPDATE [t_news] SET
[news_id]=@news_id_update, [title_pt-PT]=@title_ptPT,
[title_en-GB]=@title_enGB, [text_pt-PT]=@text_ptPT,
[text_en-GB]=@text_enGB, [publication_date]=@publication_date WHERE
([t_news].[news_id] = @news_id)"
...
' Add Parameters
With dbCommand.Parameters
.Add(New OleDbParameter("@news_id_update", 5))
.Add(New OleDbParameter("@news_id", 5))
.Add(New OleDbParameter("@title_ptPT", "Test UPDATE - title PT"))
.Add(New OleDbParameter("@title_enGB", "Test UPDATE - title EN"))
.Add(New OleDbParameter("@text_ptPT", "Test UPDATE - text PT"))
.Add(New OleDbParameter("@text_enGB", "Test UPDATE - text EN"))
.Add(New OleDbParameter("@publication_date",
DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss")))
End With
I get the error: "Cannot update 'news_id'; field not updateable."
Remember that news_id is the primary key on my Access database and set
as "AutoNumber"
Then I tried the following query as i don't need to change the news_id
field:
Dim queryString As String = "UPDATE [t_news] SET
[title_pt-PT]=@title_ptPT, [title_en-GB]=@title_enGB,
[text_pt-PT]=@text_ptPT, [text_en-GB]=@text_enGB,
[publication_date]=@publication_date WHERE ([t_news].[news_id] =
@news_id)"
I don't get any error but there is no change in data!!!! And record 5 is
there!
I even placed a Response.Write("Done") in the code:
' Update Record
dbConnection.Open()
Try
dbCommand.ExecuteNonQuery()
Finally
dbConnection.Close()
Response.Write("Done")
End Try
And it is displayed "DONE"!
My INSERT and DELETE codes are working fine.
What is going on with this UPDATE code?
Thanks,
Miguel
"Scott Allen" <sc***@nospam.odetocode.com> wrote in message
news:sc***@nospam.odetocode.com: Hi Miguel:
This is a Friday afternoon guess:
I believe with the OLE-DB provider parameters are based on position,
not name. Try adding an additional OleDbParameter for the @news_id in
the WHERE and see what happens.
--
Scott
http://www.OdeToCode.com/blogs/scott/
On Fri, 8 Jul 2005 17:30:30 +0000, "Shapper"
<mdmoura*NOSPAM*@gmail.*DELETE2SEND*com> wrote:
Hello,
I have created 3 functions to insert, update and delete an Access
database record. The Insert and the Delete code are working fine.
The update is not. I checked and my database has all the necessary
records in it when testing it. I get the error "No value given for one
or more required parameters." when I try to update the database.
Can you tell me what am I doing wrong?
Thanks,
Miguel
These are my 3 codes:
UPDATE
' Set Connection
Dim connectionString As String = AppSettings("connectionString")
Dim dbConnection As IDbConnection = New
OleDbConnection(connectionString)
' Set Query and Command
Dim queryString As String = "UPDATE [t_news] SET [news_id]=@news_id,
[title_pt-PT]=@title_pt-PT, [title_en-GB]=@title_en-GB,
[text_pt-PT]=@text_pt-PT, [text_en-GB]=@text_en-GB,
[publication_date]=@publication_date WHERE ([t_news].[news_id] =
@news_id)"
Dim dbCommand As IDbCommand = New OleDbCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection
' Add Parameters
With dbCommand.Parameters
.Add(New OleDbParameter("@news_id", 1))
.Add(New OleDbParameter("@title_pt-PT", "Test UPDATE - title PT"))
.Add(New OleDbParameter("@title_en-GB", "Test UPDATE - title EN"))
.Add(New OleDbParameter("@text_pt-PT", "Test UPDATE - text PT"))
.Add(New OleDbParameter("@text_en-GB", "Test UPDATE - text EN"))
.Add(New OleDbParameter("@publication_date",
DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss")))
End With
' Update Record
dbConnection.Open()
Try
dbCommand.ExecuteNonQuery()
Finally
dbConnection.Close()
End Try
INSERT
' Set Connection
Dim connectionString As String = AppSettings("connectionString")
Dim dbConnection As IDbConnection = New
OleDbConnection(connectionString)
' Set Query and Command
Dim queryString As String = "INSERT INTO [t_news] ([title_pt-PT],
[title_en-GB], [text_pt-PT], [text_en-GB], [publication_date]) VALUES
(@title_ptPT, @title_enGB, @text_ptPT, @text_enGB, @publication_date)"
Dim dbCommand As IDbCommand = New OleDbCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection
' Add Parameters
With dbCommand.Parameters
.Add(New OleDbParameter("@title_ptPT", "Test INSERT - title PT"))
.Add(New OleDbParameter("@title_enGB", "Test INSERT - title EN"))
.Add(New OleDbParameter("@text_ptPT", "Test INSERT - text PT"))
.Add(New OleDbParameter("@text_enGB", "Test INSERT - text EN"))
.Add(New OleDbParameter("@publication_date",
DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss")))
End With
' Insert New Record
dbConnection.Open()
Try
dbCommand.ExecuteNonQuery()
Finally
dbConnection.Close()
End Try
DELETE
' Set Connection
Dim connectionString As String = AppSettings("connectionString")
Dim dbConnection As IDbConnection = New
OleDbConnection(connectionString)
' Set Query and Command
Dim queryString As String = "DELETE FROM [t_news] WHERE
([t_news].[news_id] = @news_id)"
Dim dbCommand As System.Data.IDbCommand = New
System.Data.OleDb.OleDbCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection
' Add Parameters
dbCommand.Parameters.Add(New OleDbParameter("@news_id", 2))
' Delete Record
dbConnection.Open()
Try
dbCommand.ExecuteNonQuery()
Finally
dbConnection.Close()
End Try
The @news_id parameter is getting populated with the publication date
- so the UPDATE statement never finds the record. As I mentioned in
the first reply, parameters are resolved by position for the OleDb
provider.
In other words:
OleDbParameters are not resolved by name.
OleDbParameters are resolved in the order they are added to the
OleDbCommand.
If your UPDATE looks like this:
UPDATE [t_news]
SET
[title_pt-PT]=@title_ptPT,
[title_en-GB]=@title_enGB,
[text_pt-PT]=@text_ptPT,
[text_en-GB]=@text_enGB,
[publication_date]=@publication_date
WHERE ([t_news].[news_id] = @news_id
Then you need to add to dbCommand.Parameters in the same order the
params appear in the query:
..Add(New OleDbParameter("@title_ptPT", "Test UPDATE - title PT"))
..Add(New OleDbParameter("@title_enGB", "Test UPDATE - title EN"))
'
' other params
'
..Add(New OleDbParameter("@news_id", 5))
HTH,
--
Scott http://www.OdeToCode.com/blogs/scott/
On Fri, 8 Jul 2005 22:20:26 +0000, "Shapper"
<mdmoura*NOSPAM*@gmail.*DELETE2SEND*com> wrote: Hi,
I did that:
' Set Query and Command
Dim queryString As String = "UPDATE [t_news] SET
[news_id]=@news_id_update, [title_pt-PT]=@title_ptPT,
[title_en-GB]=@title_enGB, [text_pt-PT]=@text_ptPT,
[text_en-GB]=@text_enGB, [publication_date]=@publication_date WHERE
([t_news].[news_id] = @news_id)"
...
' Add Parameters
With dbCommand.Parameters
.Add(New OleDbParameter("@news_id_update", 5))
.Add(New OleDbParameter("@news_id", 5))
.Add(New OleDbParameter("@title_ptPT", "Test UPDATE - title PT"))
.Add(New OleDbParameter("@title_enGB", "Test UPDATE - title EN"))
.Add(New OleDbParameter("@text_ptPT", "Test UPDATE - text PT"))
.Add(New OleDbParameter("@text_enGB", "Test UPDATE - text EN"))
.Add(New OleDbParameter("@publication_date",
DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss")))
End With
I get the error: "Cannot update 'news_id'; field not updateable."
Remember that news_id is the primary key on my Access database and set
as "AutoNumber"
Then I tried the following query as i don't need to change the news_id
field:
Dim queryString As String = "UPDATE [t_news] SET
[title_pt-PT]=@title_ptPT, [title_en-GB]=@title_enGB,
[text_pt-PT]=@text_ptPT, [text_en-GB]=@text_enGB,
[publication_date]=@publication_date WHERE ([t_news].[news_id] =
@news_id)"
I don't get any error but there is no change in data!!!! And record 5 is
there!
I even placed a Response.Write("Done") in the code:
' Update Record
dbConnection.Open()
Try
dbCommand.ExecuteNonQuery()
Finally
dbConnection.Close()
Response.Write("Done")
End Try
And it is displayed "DONE"!
My INSERT and DELETE codes are working fine.
What is going on with this UPDATE code?
Thanks,
Miguel
"Scott Allen" <sc***@nospam.odetocode.com> wrote in message
news:sc***@nospam.odetocode.com:
Hi Miguel:
This is a Friday afternoon guess:
I believe with the OLE-DB provider parameters are based on position,
not name. Try adding an additional OleDbParameter for the @news_id in
the WHERE and see what happens.
--
Scott
http://www.OdeToCode.com/blogs/scott/
On Fri, 8 Jul 2005 17:30:30 +0000, "Shapper"
<mdmoura*NOSPAM*@gmail.*DELETE2SEND*com> wrote:
>Hello,
>
>I have created 3 functions to insert, update and delete an Access
>database record. The Insert and the Delete code are working fine.
>
>The update is not. I checked and my database has all the necessary
>records in it when testing it. I get the error "No value given for one
>or more required parameters." when I try to update the database.
>
>Can you tell me what am I doing wrong?
>
>Thanks,
>Miguel
>
>These are my 3 codes:
>
>UPDATE
>
> ' Set Connection
> Dim connectionString As String = AppSettings("connectionString")
> Dim dbConnection As IDbConnection = New
>OleDbConnection(connectionString)
>
> ' Set Query and Command
> Dim queryString As String = "UPDATE [t_news] SET [news_id]=@news_id,
>[title_pt-PT]=@title_pt-PT, [title_en-GB]=@title_en-GB,
>[text_pt-PT]=@text_pt-PT, [text_en-GB]=@text_en-GB,
>[publication_date]=@publication_date WHERE ([t_news].[news_id] =
>@news_id)"
> Dim dbCommand As IDbCommand = New OleDbCommand
> dbCommand.CommandText = queryString
> dbCommand.Connection = dbConnection
>
> ' Add Parameters
> With dbCommand.Parameters
> .Add(New OleDbParameter("@news_id", 1))
> .Add(New OleDbParameter("@title_pt-PT", "Test UPDATE - title PT"))
> .Add(New OleDbParameter("@title_en-GB", "Test UPDATE - title EN"))
> .Add(New OleDbParameter("@text_pt-PT", "Test UPDATE - text PT"))
> .Add(New OleDbParameter("@text_en-GB", "Test UPDATE - text EN"))
> .Add(New OleDbParameter("@publication_date",
>DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss")))
> End With
>
> ' Update Record
> dbConnection.Open()
> Try
> dbCommand.ExecuteNonQuery()
> Finally
> dbConnection.Close()
> End Try
>
>
>INSERT
>
> ' Set Connection
> Dim connectionString As String = AppSettings("connectionString")
> Dim dbConnection As IDbConnection = New
>OleDbConnection(connectionString)
>
> ' Set Query and Command
> Dim queryString As String = "INSERT INTO [t_news] ([title_pt-PT],
>[title_en-GB], [text_pt-PT], [text_en-GB], [publication_date]) VALUES
>(@title_ptPT, @title_enGB, @text_ptPT, @text_enGB, @publication_date)"
> Dim dbCommand As IDbCommand = New OleDbCommand
> dbCommand.CommandText = queryString
> dbCommand.Connection = dbConnection
>
> ' Add Parameters
> With dbCommand.Parameters
> .Add(New OleDbParameter("@title_ptPT", "Test INSERT - title PT"))
> .Add(New OleDbParameter("@title_enGB", "Test INSERT - title EN"))
> .Add(New OleDbParameter("@text_ptPT", "Test INSERT - text PT"))
> .Add(New OleDbParameter("@text_enGB", "Test INSERT - text EN"))
> .Add(New OleDbParameter("@publication_date",
>DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss")))
> End With
>
> ' Insert New Record
> dbConnection.Open()
> Try
> dbCommand.ExecuteNonQuery()
> Finally
> dbConnection.Close()
> End Try
>
>DELETE
>
> ' Set Connection
> Dim connectionString As String = AppSettings("connectionString")
> Dim dbConnection As IDbConnection = New
>OleDbConnection(connectionString)
>
> ' Set Query and Command
> Dim queryString As String = "DELETE FROM [t_news] WHERE
>([t_news].[news_id] = @news_id)"
> Dim dbCommand As System.Data.IDbCommand = New
>System.Data.OleDb.OleDbCommand
> dbCommand.CommandText = queryString
> dbCommand.Connection = dbConnection
>
> ' Add Parameters
> dbCommand.Parameters.Add(New OleDbParameter("@news_id", 2))
>
> ' Delete Record
> dbConnection.Open()
> Try
> dbCommand.ExecuteNonQuery()
> Finally
> dbConnection.Close()
> End Try
>
> This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Philip Boonzaaier |
last post by:
I want to be able to generate SQL statements that will go through a list of
data, effectively row by row, enquire on the database if this exists in...
|
by: robert |
last post by:
been ruminating on the question (mostly in a 390/v7 context) of
whether, and if so when, a row update becomes an insert/delete.
i assume that...
|
by: Diana Estrada |
last post by:
Hi, I have a store procedure to insert , and I use tableadapter to
execute this store procedure, when I run the application and execute the
sp ,...
|
by: Ivan |
last post by:
Hi to all !!!
I have one stored procedure that update and delete rows in differents
tables, but when I try of delete of the main table this show...
|
by: abhi81 |
last post by:
Hello All,
I have a table on which I have created a insert,Update and a Delete trigger. All these triggers write a entry to another audit table with...
|
by: =?Utf-8?B?UmljaA==?= |
last post by:
On a form - I have a datagridview which is docked to the entire form. The
datagridview allows users to Delete and/or Add Rows. On the Form_Load...
|
by: magnolia |
last post by:
i created a trigger that will record the changes made to a table .everything works fine except the insert query.whenerever i try to insert a record...
|
by: veasnamuch |
last post by:
I have a problem while I create a trigger to my table. My objective is getting any change made to my table and record it in to another table . My ...
|
by: Michael |
last post by:
I know I am missing something simple, but I am stuck. When I submit
the following for the employee info gets stored in scale1 and scale1
shows...
|
by: concettolabs |
last post by:
In today's business world, businesses are increasingly turning to PowerApps to develop custom business applications. PowerApps is a powerful tool...
|
by: better678 |
last post by:
Question:
Discuss your understanding of the Java platform. Is the statement "Java is interpreted" correct?
Answer:
Java is an object-oriented...
|
by: Kemmylinns12 |
last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and...
|
by: CD Tom |
last post by:
This happens in runtime 2013 and 2016. When a report is run and then closed a toolbar shows up and the only way to get it to go away is to right...
|
by: jalbright99669 |
last post by:
Am having a bit of a time with URL Rewrite. I need to incorporate http to https redirect with a reverse proxy. I have the URL Rewrite rules made...
|
by: antdb |
last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine
In the overall architecture, a new "hyper-convergence" concept was...
|
by: Matthew3360 |
last post by:
Hi, I have a python app that i want to be able to get variables from a php page on my webserver. My python app is on my computer. How would I make it...
|
by: AndyPSV |
last post by:
HOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and on my computerHOW CAN I CREATE AN AI with an .executable...
|
by: WisdomUfot |
last post by:
It's an interesting question you've got about how Gmail hides the HTTP referrer when a link in an email is clicked. While I don't have the specific...
| | |
