problem with sql update statement

ado.net , vb.net , msaccess 2003 Bangalore, India
  • 8 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…
  • 8 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/)

Post a reply

Enter your message below

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Weeks of coding can save you hours of planning.”