Insert, Update and Delete Problem. Need Help. Thanks.
-
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("@
-
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("@
I believe oledb parameters are 'placement' only parameters (that is, the name of the parameter is irrelevant, only its order in the statement matters) In this case you have one more parameter (placement wise) in the statement than you are adding to the parameter collection (you can't get away with 'sharing' the news_id parameter just by name matching, you must add another parameter using the same value, but a different name - the names must be unique to satisfy the collection rules, but serve no role in terms of matching parameters in the statement). I suspect you dont really want to CHANGE the value of news_id anyway, so just remove it from your filed list, and move the parameter to last in the colection. Absolute faith corrupts as absolutely as absolute power Eric Hoffer The opposite of the religious fanatic is not the fanatical atheist but the gentle cynic who cares not whether there is a god or not. Eric Hoffer
-
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("@
Instead of the lines: ..." .Add(New OleDbParameter("@title_ptPT", "Test INSERT - title PT"))" try to use: ".Add("@title_ptPT", "Test INSERT - title PT") As far as I know there is no need to declare a new OleDbParameter in this statement. Hope it helps. JJRDK
-
I believe oledb parameters are 'placement' only parameters (that is, the name of the parameter is irrelevant, only its order in the statement matters) In this case you have one more parameter (placement wise) in the statement than you are adding to the parameter collection (you can't get away with 'sharing' the news_id parameter just by name matching, you must add another parameter using the same value, but a different name - the names must be unique to satisfy the collection rules, but serve no role in terms of matching parameters in the statement). I suspect you dont really want to CHANGE the value of news_id anyway, so just remove it from your filed list, and move the parameter to last in the colection. Absolute faith corrupts as absolutely as absolute power Eric Hoffer The opposite of the religious fanatic is not the fanatical atheist but the gentle cynic who cares not whether there is a god or not. Eric Hoffer
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, 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
You missed my most important point. The parameters are PLACEMENT (i.e. ORDER) sensitive. You must add the parameters to the collection in the same order that you used them in the statement. You get no update because no news_id value in the table matches the publication date (politly converted to a long integer). news_id MUST be the last parameter added to the collection. To be sure that the ADD method overload is not doing some polite conversion behind your back, you should specify the data type and direction for the parameters, as well, rather than rely on the parameter constructor to determine them for you. Absolute faith corrupts as absolutely as absolute power Eric Hoffer The opposite of the religious fanatic is not the fanatical atheist but the gentle cynic who cares not whether there is a god or not. Eric Hoffer
-
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("@
As Rob answered, the parameters are order-sensitive for
OleDb* classes. So add the@news-idparameter again at the end, since you reference it at the end. I just answered this more throughly on the ASP.NET forum, so please don't cross-post in different forums, it's considered bad manners here. -- LuisR
Luis Alonso Ramos Intelectix - Chihuahua, Mexico Not much here: My CP Blog!