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

S

Shapper

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
 
S

Scott Allen

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/

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
Click to expand...
 
S

Shapper

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

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/

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
Click to expand...
Click to expand...
 
S

Scott Allen

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/

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

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/

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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

INSERT - SQL. Add Record. Problem. Need Urgent Help. Thank You. 3
Problem inserting record in Access Database. 2
Date/Time Strange Problem 2
adding data...got problem....pls help 2
Compilation error - Parameterized update 5
Increase value by 1. How to do this? 3
ListView ... Update and Insert 0
Authentication in Asp.Net 2.0 1

Members online

Total: 24 (members: 1, guests: 23)
Robots: 460

Forum statistics

Threads
473,769
Messages
2,569,580
Members
45,055
Latest member
SlimSparkKetoACVReview

Latest Threads

Top