C# .NET - UPDATE statement

Asked By ladey pink on 07-Feb-10 09:14 PM
Hi.
I would like to know if this UPDATE statement of mine is correct. I need to edit the information. 
The coding is 
        if (varFC != ddlFC.SelectedValue.ToString())
                {
                    if (ddlFC.SelectedValue.ToString() == "-")
                    {
                       cmd =
                       new OleDbCommand("UPDATE tblStudentFC SET tblStudentFC.FCID = null "
                       + "WHERE (((tblStudentFC.StudentID)=" + varStudentID + "));", conn);
                        cmd.ExecuteNonQuery(); //
                     }

                    else
                    {
                         cmd =
                         new OleDbCommand("UPDATE tblStudentFC.FCID SET (tblIndexFC.FCID + ' - ' +                                  tblIndexFC.Description) = '" + ddlFC.SelectedValue.ToString() + "' "
                         + "WHERE (((tblStudentFC.StudentID)= " + varStudentID + "));", conn);
                         cmd.ExecuteNonQuery(); 

Thanks. :)
F Cali replied to ladey pink on 07-Feb-10 09:49 PM

Try this code:

cmd = new OleDbCommand("UPDATE tblStudentFC SET tblStudentFC.FCID = " + ddlFC.SelectedValue + " WHERE (((tblStudentFC.StudentID)=" + varStudentID + "));", conn);
                        cmd.ExecuteNonQuery();

Regards,
http://www.sql-server-helper.com/functions/comma-delimited-to-table.aspx

Venkat K replied to ladey pink on 07-Feb-10 11:29 PM
cmd =
                         new OleDbCommand("UPDATE tblStudentFC.FCID SET (tblIndexFC.FCID + ' - ' +                                  tblIndexFC.Description) = '" + ddlFC.SelectedValue.ToString() + "' "
                         + "WHERE (((tblStudentFC.StudentID)= " + varStudentID + "));", conn);
                         cmd.ExecuteNonQuery(); 
 
I found discrepandy in the portion which is highlighted in red. The column is treated as
tblIndexFC.FCID-tblIndexFC.Description remove that portion and verify your statement.
 
NOTE: Debug your code and store the query you are getting into cmd.
Run the text against SQL Server Query Analyzer to verify the syntax.
Hope this helps!
Thanks,
Sandra Jain replied to ladey pink on 08-Feb-10 01:31 AM
FYI. You cannot insert null values into the database. It will be stored as an empty string with the allotted size. The best possible workaround is to leave the field empty during insert, which will automatically insert null value, provided the field accepts null values to be inserted.

Try to tweak your coding and come up with some other logic. Because update null will not work.


replied
ladey pink replied to Venkat K on 08-Feb-10 01:44 AM
I have to get these coding
if (varFC != "")
{ cmd =
new OleDbCommand("DELETE FROM tblStudentFC WHERE tblStudentFC.StudentID = " + varStudentID + ";", conn);
cmd.ExecuteNonQuery();  }

cmd = new OleDbCommand("SELECT tblIndexFC.Description, tblIndexFC.ID FROM tblIndexFC "
+ "WHERE (((tblIndexFC.FCID + ' - ' + tblIndexFC.Description)='" + ddlFC.SelectedValue.ToString() + "'));", conn);
reader = cmd.ExecuteReader();

if (reader.Read())
{ cmd = new OleDbCommand("INSERT INTO tblStudentFC(StudentID, FCID) VALUES(?,?)", conn);
 cmd.Parameters.Add("@StudentID", OleDbType.Numeric, 20).Value = varStudentID;
 cmd.Parameters.Add("@FCID", OleDbType.Numeric, 50).Value = reader["ID"].ToString();
cmd.ExecuteNonQuery(); }

to change it into an UPDATE statement. 
Meaning when I edit the information, I need to use an UPDATE statement to save it. I'm stuck.
Could you please help?

Thank you.
Venkat K replied to ladey pink on 08-Feb-10 02:52 AM

Update Query:

cmd = new OleDbCommand("Update tblStudentFC set FCID=SELECT tblIndexFC.ID FROM tblIndexFC WHERE tblIndex (((tblIndexFC.FCID + ' - ' + tblIndexFC.Description)='" + ddlFC.SelectedValue.ToString() + "')) Where StudentID= varstudentid;", conn)

The plain update query would be like this:

Update tblStudentFC set FCID=(SELECT tblIndexFC.ID FROM tblIndexFC WHERE fcid-desc=param1) Where StudentID= varstudentid

Check out the syntax error, hope this helps!

Thanks,

ladey pink replied to Venkat K on 08-Feb-10 03:22 AM
The coding you just gave me is not helping at all.
I still can't capture the new edited info to my database.
It will always go back to its previous selected item.

Any other better ideas?

Help.
Thank you.
Jonathan VH replied to ladey pink on 08-Feb-10 07:36 AM

From the WHERE clause in your select statement for that table, it appears that you actually need to update two columns from one string in your C# program.  Use a regex expression to split your ddlFC.SelectedValue value into the two components (I named them intFCID and strDescription in the below example) and then use those as parameter values in an UPDATE command, e.g.:

{ cmd = new OleDbCommand("UPDATE tblStudentFC SET FCID = ?, Description = ?;", conn);
 cmd.Parameters.Add("@FCID", OleDbType.Integer, 20).Value = intFCID;
 cmd.Parameters.Add("@Description", OleDbType.VarChar, 50).Value = strDescription;
 cmd.ExecuteNonQuery(); }

I don't know the actual data types of your columns, so those may need to be changed.

You should also paramterize your DELETE and SELECT statements as that will help prevent SQL injection attacks.