none
correct syntax for db update RRS feed

  • Question

  • hi there,

    i wish to write some detail to my db


    i have this code (not completed)

    Code Block

       public void UpdateAction(Action TheAction)
            {
                // TODO: Update action in database table
                string sql = "update Actions set ......... where ID={0}";

                sql = string.Format(sql, TheAction.ID);

                SqlCommand cmd = new SqlCommand(sql, _con);

                 _con.Open();

                try
                {
                    cmd.ExecuteNonQuery();
                }
                catch
                {
                    _con.Close();
                    throw new SystemException("Failed to update the requested action");
                }

                _con.Close();
           
            }

     

     


    the object TheAction contains

    ID (int)
    Type (string)
    Fulfilled (bool)
    MemberID (int)
    OrganisationID (int)
    TargetDate (datetime)
    AmberDate (datetime) called GoAmberDate in db table 
    RedDate (datetime) called GoRedDate in db table 
    Owner (string)

    I don't wish to use a dataset but use direct execution of the sql commands but am unfamiliar with the correct syntax and haven't been able
    to find any examples after looking around today and would greatly appreciate any help,

    thanks,

    Tuesday, January 8, 2008 7:30 PM

Answers

  • String sql = "INSERT INTO Actions (Fullfiled, MemberID, OrganisationID, TargetDate, GoAmberDate, GoRedDate, Owner) VALUES (@Fullfiled, @MemberID, @OrganisationID, @TargetDate, @GoAmberDate, @GoRedDate, @Owner)";

    You can set the parameters values same as before.

    Regards
    Wednesday, January 9, 2008 12:30 PM

All replies

  • string sql = "update Actions set Type=@Type, Fullfiled=@Fullfilled, MemberID=@MemberID, OrganisationID=@OrganisationID, TargetDate=@TargetDate, GoAmberDate=@GoAmberDate, GoRedDate=@GoRedDate, Owner=@Owner where ID=@ID";
    ...
    cmd.Parameters.Add("@Type", SqlDBType.VarChar) = yourTypeValue;
    cmd.Parameters.Add("@Fulfilled", SqlDBType.Bit) = yourFulfilledValue;
    cmd.Parameters.Add("@MemberID", SqlDBType.Int) = yourMemberIDValue;
    cmd.Parameters.Add("@OrganisationID", SqlDBType.Int) = yourOrganisationIDValue;
    cmd.Parameters.Add("@TargetDate", SqlDBType.DateTime) = yourTargetDateValue;
    cmd.Parameters.Add("@GoAmberDate", SqlDBType.DateTime) = yourAmberDateValue;
    cmd.Parameters.Add("@GoRedDate", SqlDBType.DateTime) = yourRedDateDateValue;
    cmd.Parameters.Add("@Owner", SqlDBType.VarChar) = yourOwnerValue;
    cmd.Parameters.Add("@ID", SqlDBType.Int) = yourIDValue;
    cmd.ExecuteNonQuery();

    Regards







    Tuesday, January 8, 2008 11:44 PM
  • Hi there, thanks,

     

    where would the line below come into it? i have to pass the connection string somewhere to i guess.

     

     

    Code Block
    SqlCommand cmd = new SqlCommand(sql, _con);

     

     

    i'll have a play about with what you've suggested and see how i get on tho,

     

    cheers,

     

    Wednesday, January 9, 2008 9:08 AM
  •  string sql = "update Actions set Type=@Type, Fullfiled=@Fullfilled, MemberID=@MemberID, OrganisationID=@OrganisationID, TargetDate=@TargetDate, GoAmberDate=@GoAmberDate, GoRedDate=@GoRedDate, Owner=@Owner where ID=@ID";
    SqlCommand cmd = new SqlCommand(sql, _con);
    cmd.Parameters.Add("@Type", SqlDBType.VarChar) = yourTypeValue;
    cmd.Parameters.Add("@Fulfilled", SqlDBType.Bit) = yourFulfilledValue;
    cmd.Parameters.Add("@MemberID", SqlDBType.Int) = yourMemberIDValue;
    cmd.Parameters.Add("@OrganisationID", SqlDBType.Int) = yourOrganisationIDValue;
    cmd.Parameters.Add("@TargetDate", SqlDBType.DateTime) = yourTargetDateValue;
    cmd.Parameters.Add("@GoAmberDate", SqlDBType.DateTime) = yourAmberDateValue;
    cmd.Parameters.Add("@GoRedDate", SqlDBType.DateTime) = yourRedDateDateValue;
    cmd.Parameters.Add("@Owner", SqlDBType.VarChar) = yourOwnerValue;
    cmd.Parameters.Add("@ID", SqlDBType.Int) = yourIDValue;
    cmd.ExecuteNonQuery();

     

    Regards
     
     

    Wednesday, January 9, 2008 9:11 AM
  • when i build i'm gettin the error message

     

    Error 1 The name 'SqlDBType' does not exist in the current context C:\Users\Mat\Documents\DSBSystems\TheHub.NET\DLL\DData.cs 745 46 DLL

    i've have this included already

     

    using System.Data.SqlClient

     

    Wednesday, January 9, 2008 9:17 AM
  • this is one of the lines

     

    Code Block

    cmd.Parameters.Add("@MemberID", SqlDBType.Int) = TheAction.MemberID;

     

     

    i'm accessing TheAction members directly from the past object, should i create a new instance of this object and assign TheAction to it then use that (if that makes sense Smile

    Wednesday, January 9, 2008 9:26 AM
  • Question1: It is SqlDbType.
    Question2: It is ok as you did it, no need to create a new instance.

    Regards
    Wednesday, January 9, 2008 9:37 AM
  • any ideas why i'm getting that error message then?

     

    Wednesday, January 9, 2008 9:42 AM
  • Have you replaced SqlDBType with SqlDbType? Notice that I replaced DB -> Db.

    Regards
    Wednesday, January 9, 2008 9:45 AM
  • Ah yes, great.

     

    I am now getting a couple more errors for each line

     

    Error 1 The left-hand side of an assignment must be a variable, property or indexer C:\Users\Mat\Documents\DSBSystems\TheHub.NET\DLL\DData.cs 745 13 DLL
    Error 2 Cannot implicitly convert type 'bool' to 'System.Data.SqlClient.SqlParameter' C:\Users\Mat\Documents\DSBSystems\TheHub.NET\DLL\DData.cs 744 63 DLL
    Error 3 The left-hand side of an assignment must be a variable, property or indexer C:\Users\Mat\Documents\DSBSystems\TheHub.NET\DLL\DData.cs 745 13 DLL
    Error 4 Cannot implicitly convert type 'int' to 'System.Data.SqlClient.SqlParameter' C:\Users\Mat\Documents\DSBSystems\TheHub.NET\DLL\DData.cs 745 62 DLL
    .... and so on

     

    here's the code as is -

     

    Code Block

    public void UpdateAction(Action TheAction)

    {

    // TODO: Update action in database table

    string sql = "update Actions set Fullfiled=@Fullfilled, MemberID=@MemberID, " +

    "OrganisationID=@OrganisationID, TargetDate=@TargetDate, GoAmberDate=@GoAmberDate, " +

    "GoRedDate=@GoRedDate, Owner=@Owner where ID=@ID";

    SqlCommand cmd = new SqlCommand(sql, _con);

    cmd.Parameters.Add("@Fulfilled", SqlDbType.Bit) = true;

    cmd.Parameters.Add("@MemberID", SqlDbType.Int) = TheAction.MemberID;

    cmd.Parameters.Add("@OrganisationID", SqlDbType.Int) = TheAction.OrganisationID;

    cmd.Parameters.Add("@TargetDate", SqlDbType.DateTime) = TheAction.TargetDate;

    cmd.Parameters.Add("@GoAmberDate", SqlDbType.DateTime) = TheAction.AmberDate;

    cmd.Parameters.Add("@GoRedDate", SqlDbType.DateTime) = TheAction.RedDate;

    cmd.Parameters.Add("@Owner", SqlDbType.VarChar) = TheAction.Owner;

    cmd.Parameters.Add("@ID", SqlDbType.Int) = TheAction.ID;

    cmd.ExecuteNonQuery();

    _con.Open();

    try

    {

    cmd.ExecuteNonQuery();

    }

    catch

    {

    _con.Close();

    throw new SystemException("Failed to update the requested action");

    }

    _con.Close();

    }

     

     

     

     

     

    Wednesday, January 9, 2008 9:53 AM
  • Yes, sorry about that.
    It should be:

    cmd.Parameters.Add("@Fulfilled", SqlDbType.Bit).Value = true;

    cmd.Parameters.Add("@MemberID", SqlDbType.Int).Value = TheAction.MemberID;

    cmd.Parameters.Add("@OrganisationID", SqlDbType.Int).Value = TheAction.OrganisationID;

    cmd.Parameters.Add("@TargetDate", SqlDbType.DateTime).Value = TheAction.TargetDate;

    cmd.Parameters.Add("@GoAmberDate", SqlDbType.DateTime).Value = TheAction.AmberDate;

    cmd.Parameters.Add("@GoRedDate", SqlDbType.DateTime).Value = TheAction.RedDate;

    cmd.Parameters.Add("@Owner", SqlDbType.VarChar).Value = TheAction.Owner;

    cmd.Parameters.Add("@ID", SqlDbType.Int).Value = TheAction.ID;


    (Notice the .Value).


    Regards
    Wednesday, January 9, 2008 9:56 AM
  • that's brilliant Marcel, thanks.

     

    Also, if i now wish to Add a new Action, should i just replace the syntax 'update' with 'insert' and not include the TheAction.ID, as this is the primary key on the table and, i assume is automatically generated.

    Wednesday, January 9, 2008 11:08 AM
  • Yes, that's OK.
    Wednesday, January 9, 2008 11:10 AM
  • hi again,

     

    could you just show me what the correct syntax for the above would be for adding a new action as i think i'm getting my 'insert' syntax wrong (i'm really gonna have to spend some time learning sql!!)

     

     

     

    Wednesday, January 9, 2008 11:45 AM
  • String sql = "INSERT INTO Actions (Fullfiled, MemberID, OrganisationID, TargetDate, GoAmberDate, GoRedDate, Owner) VALUES (@Fullfiled, @MemberID, @OrganisationID, @TargetDate, @GoAmberDate, @GoRedDate, @Owner)";

    You can set the parameters values same as before.

    Regards
    Wednesday, January 9, 2008 12:30 PM