PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197

PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197

PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197

PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197
Update SQL Statement-VBForums
Results 1 to 10 of 10

Thread: Update SQL Statement

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Mar 2010
    Location
    Ireland
    Posts
    85

    Update SQL Statement

    Should this work, I can't see a problem with it... but it's not working

    SQL Code:
    1. Private Sub myAddNew(ByVal CusRef AS INTEGER)
    2.         Dim ConnectionString AS String
    3.         Dim SQLString AS String
    4.         Dim whichButtonDialogResult AS DialogResult
    5.         Dim dbCommand AS System.Data.OleDb.OleDbCommand
    6.         Dim Connection AS System.Data.OleDb.OleDbConnection
    7.  
    8.         ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data "
    9.         ConnectionString += "Source=" & "Opticians.accdb "
    10.         Connection = NEW System.Data.OleDb.OleDbConnection(ConnectionString)
    11.         'SQLString = "INSERT INTO SpecSalesTable (DateReceived) "
    12.        SQLString = "Update SpecSalesTable "
    13.        SQLString += "SET DateReceived = #" & Date.Today & "#"
    14.        SQLString += "WHERE SpecSalesID = SpecIDTextBox.Text "
    15.        whichButtonDialogResult = MessageBox.Show("Are you sure these details are correct?", "Update!", MessageBoxButtons.YesNo, MessageBoxIcon.Question)
    16.        If whichButtonDialogResult = DialogResult.Yes Then
    17.            Connection.Open()
    18.            If CBool(ConnectionState.Open) Then
    19.                dbCommand = New System.Data.OleDb.OleDbCommand(SQLString, Connection)
    20.                Try
    21.                    dbCommand.ExecuteNonQuery()
    22.                    MessageBox.Show("Spectacles Returned, SpecSalesTable Updated! ")
    23.                Catch ex As Exception
    24.                    MessageBox.Show(" Error updating... ")    'IF ERROR DISPLAYS MESSAGE
    25.                 END Try
    26.  
    27.             END IF
    28.  
    29.         END IF
    30.  
    31.         Connection.Close()
    32.     END Sub

  2. #2
    Super Moderator Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,335

    Re: Update SQL Statement

    Moved To Database Development

    What is the error message?
    Please use [Code]your code goes in here[/Code] tags when posting code.
    When you have received an answer to your question, please mark it as resolved using the Thread Tools menu.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    I dont answer coding questions via PM or EMail. Please post a thread in the appropriate forum section.

    Creating A Wizard In VB.NET
    Paging A Recordset
    What is wrong with using On Error Resume Next
    Good Article: Language Enhancements In Visual Basic 2010
    Upgrading VB6 Code To VB.NET
    Microsoft MVP 2005/2006/2007/2008/2009/2010/2011/2012/Defrocked

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Mar 2010
    Location
    Ireland
    Posts
    85

    Re: Update SQL Statement

    Well from that code I'm only getting my own error message, it's connecting to the database, but goes to the catch...

  4. #4
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,385

    Re: Update SQL Statement

    change this
    MessageBox.Show(" Error updating... ") 'IF ERROR DISPLAYS MESSAGE
    to this
    MessageBox.Show(" Error updating... " & System.Enviornment.NewLine & ex.Message) 'IF ERROR DISPLAYS MESSAGE

    and post the real error
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  5. #5
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,674

    Re: Update SQL Statement

    This is your problem"
    SQLString += "WHERE SpecSalesID = SpecIDTextBox.Text "


    Your text box is inside your strng... so it is LITTERALY USING SpecIDTextBox.Text..... not the contents...

    1) In the future, try displaying your SQL in a messagebox before executring it so that you are sure you know what you are executing
    2) SQL doesn't know jack about your textboxes, so you can't use the name inside it like that
    3) Read the DB FAQ section on parameters, that's the best way to solve the problem.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  6. #6

    Thread Starter
    Lively Member
    Join Date
    Mar 2010
    Location
    Ireland
    Posts
    85

    Re: Update SQL Statement

    Data type mismatch in criteria expression...

    vb Code:
    1. SQLString = "Update SpecSalesTable "
    2.         SQLString += "SET DateReceived = #" & Date.Today & "#"
    3.         SQLString += "WHERE SpecSalesID = '" & SpecIDTextBox.Text & "' "

  7. #7

    Thread Starter
    Lively Member
    Join Date
    Mar 2010
    Location
    Ireland
    Posts
    85

    Re: Update SQL Statement

    "Update SpecSalesTable SET DateReceived = #25/03/2010#"

    There's the SQL String

  8. #8
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,385

    Re: Update SQL Statement

    Is SpecSalesId a Number or Text?

    If a number then

    vb.net Code:
    1. SQLString = "Update SpecSalesTable "        
    2. SQLString &= "SET DateReceived = #" & Date.Today & "#"        
    3. SQLString &= "WHERE SpecSalesID = " & SpecIDTextBox.Text

    if text then
    vb.net Code:
    1. SQLString = "Update SpecSalesTable "        
    2. SQLString &= "SET DateReceived = #" & Date.Today & "#"        
    3. SQLString &= "WHERE SpecSalesID = '" & SpecIDTextBox.Text & "'"
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  9. #9

    Thread Starter
    Lively Member
    Join Date
    Mar 2010
    Location
    Ireland
    Posts
    85

    Re: Update SQL Statement

    Once again Gary your my saviour

    number....

    Thanks to everyone!

  10. #10
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,324

    Re: Update SQL Statement

    Quote Originally Posted by Kielo View Post
    "Update SpecSalesTable SET DateReceived = #25/03/2010#"

    There's the SQL String
    SQLString += "SET DateReceived = #" & Date.Today & "#"
    You have got a bug there, and you are likely to notice it next week.

    DateReceived is only being set to the 25th of March because there aren't 25 months... on April 1st your SQL statement will include #01/04/2010# , which will mean that January 4th will be stored in the database.

    Either append the value to the SQL statement correctly (as explained in the FAQ article How do I use values (numbers, strings, dates) in SQL statements? ), or save yourself lots of hassle for that and lots of other things by following TG's advice.

    For an explanation of why you should be using parameters (and links to code examples), see the article Why should I use Parameters instead of putting values into my SQL string? from our Database Development FAQs/Tutorials (at the top of this forum).

Tags for this Thread

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width