problem with sql update statement

• • Reply

  12 years ago

  by chandrubngit

  CHANDRASHEKAR B

  Bangalore, India

  Joined 12 years ago
  I am working with a project using MS Access 2003 as backend database and vb.net 2003 as front end windows application. I need to update few records of table1 using the values given by the user through textboxes. At the same time I need to insert a new record in table2 using the same values of textboxes given by user. I tried with single adapter to update command for 1st table and insert command for 2nd table. Also tried with two adapters one for updating and one for inserting. But it is not working.bcoz in the where clause of update statement for 1st table one field value may contain null value or a value from textbox. Dim query as string Query=update table1 set status=?, details=? Where (userid=’” & TextBox1.Text.Trim & “’ And Priority=’” & TextBox2.Text.Trim & “’) Here Textbox2 may be blank…if it is blank then query should update the corresponding row in database whose priority field value is blank & userid is textbox1 value. Bcoz another same record may exist in database with priority value not null and userid (Textbox1 value). I can tackle this by using the if condition…. If textbox2 value is blank, then I will use update statement like this Query=update table1 set status=?, details=? Where (userid=’” & TextBox1.Text.Trim & “’ And Priority IS NULL) If textbox2 contains a value then I will use Query=update table1 set status=?, details=? Where (userid=’” & TextBox1.Text.Trim & “’ And Priority=’” & TextBox2.Text.Trim & “’) But I want to know is there any better method….than this to do…? Thanks in Advance…
  Report abuse
• • Reply

  12 years ago

  by konikula

  Matej Kasper

  Trutnov, Czech Republic

  Joined 15 years ago
  If I parsed your question well, you want to see, if there is some more native solution to Textbox -> Null / Value @ SQL I suppose not ... but you can use sql compartment "IIF(" & TextBox1.Text.Trim & " = '', NULL, " & TextBox1.Text.Trim & ")" what is, I suppose, most native solution to "translate" text to these two options in sql check this thread I met recently on MSDN, and preferly my & Xingwei's calculations there [VB won't save TextBox to database](http://social.msdn.microsoft.com/Forums/en-US/vblanguage/thread/2f3e8c44-27df-440c-98d2-539a95322023/)
  Report abuse