By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,262 Members | 2,664 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,262 IT Pros & Developers. It's quick & easy.

Insert, Update and Delete. Can somebody please help me?

P: n/a
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

Nov 19 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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


Nov 19 '05 #2

P: n/a
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


Nov 19 '05 #3

P: n/a
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
>
>


Nov 19 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.