dcsimg
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 3 of 3

Thread: problem with sql update statement

  1. #1
    Join Date
    Dec 2008
    Posts
    1

    Question problem with sql update statement

    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.

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

    Code:
    Query=update table1 set status=?, details=? Where (userid=’” & TextBox1.Text.Trim & “’ And Priority IS NULL)
    If textbox2 contains a value then I will use
    Code:
    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…

  2. #2
    Join Date
    Jan 2003
    Location
    7,107 Islands
    Posts
    2,487

    Re: problem with sql update statement

    do you have primary keys on your tables? i would suggest to add primary keys to your tables. a primary key is a column or group of columns that uniquely identifies a record in a table. it will make your life easier in the where clause.
    Busy

  3. #3
    Join Date
    Apr 2002
    Location
    Egypt
    Posts
    2,210

    Re: problem with sql update statement

    chandrubngit,
    Using string concatenation to create a SQL statement is discouraged. This exposes your application to SQL injection attacks and other functional errors. You need to use parameterized queries instead.
    About your style of doing the query, it's ok to check for values entered by user and call run appropriate query according to it.
    Hesham A. Amin
    My blog , Articles


    <a rel=https://twitter.com/HeshamAmin" border="0" /> @HeshamAmin

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Windows Mobile Development Center


Click Here to Expand Forum to Full Width




On-Demand Webinars (sponsored)