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
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