Insert, Update and Delete Problem. Need help.

microsoft.public.sqlserver.programming

8 Jul 2005 5:31 PM

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

8 Jul 2005 5:48 PM

Steve Kass

Your update query refers to @title_pt, @title_en, @text_pt, etc., and
you have
not specified any parameters with those names. In other words, the hyphens
are creating a problem. You don't have this problem in the other
queries because
none of your parameter names have hyphens in them.

Steve Kass
Drew University

Shapper wrote:

Show quote

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

8 Jul 2005 10:22 PM

Shapper

Hi,

I made some corrections after reading your comments.
However it's still not working...and I don't get any error to.

Really strange. This is what I did:

    ' 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

Show quote

"Steve Kass" <sk***@drew.edu> wrote in message news:skass@drew.edu:

> Your update query refers to @title_pt, @title_en, @text_pt, etc., and
> you have
> not specified any parameters with those names. In other words, the hyphens
> are creating a problem. You don't have this problem in the other
> queries because
> none of your parameter names have hyphens in them.
>
> Steve Kass
> Drew University
>
> Shapper 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
> >
> >
> >

8 Jul 2005 10:33 PM

Steve Kass

The first step towards answering "why doesn't my query
work" is to look at your query.

Print the querystring value instead of executing it (or grab
its value before executing by setting a breakpoint, copy the
query to Query Analyzer, connect to the same server and
database as your connection string, and run the query and
see what happens there.

SK

Shapper wrote:

Show quote

> Hi,
>
> I made some corrections after reading your comments.
> However it's still not working...and I don't get any error to.
>
> Really strange. This is what I did:
>
>    ' 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
>
> "Steve Kass" <sk***@drew.edu> wrote in message news:skass@drew.edu:
>
>> Your update query refers to @title_pt, @title_en, @text_pt, etc., and
>> you have
>> not specified any parameters with those names. In other words, the
>> hyphens
>> are creating a problem. You don't have this problem in the other
>> queries because
>> none of your parameter names have hyphens in them.
>>
>> Steve Kass
>> Drew University
>>
>> Shapper 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
>> >
>> >
>> >
>
>