Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Visual Basic > VB 6 Visual Basic 6 > VB Databases Basics
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
VB Databases Basics Beginning-level VB coding questions specific to using VB with databases. Issues not specific to database use will be redirected to other forums.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the VB Databases Basics section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old June 11th, 2008, 12:06 PM
Registered User
 
Join Date: May 2008
Location: Dallas, TX, USA.
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default Updating SQL Database using Windows Forms in VB

I'm having a problem getting my records to update in my SQL database. I can write the code to update fine in SQL Management Studio but when I put the information in Textboxes in my Visual Basic win form it runs fine but does not update. The code I am using is as follows:

    Private Sub Save_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Save.Click
        Dim Objconn As New SqlClient.SqlConnection("Data Source=PEEKSLAPTOP\SQLEXPRESS;Initial Catalog=gmcontainer;Integrated Security=True;Pooling=False")
        Objconn.Open()
        Dim strSQL As String

        Dim UpdateCmd As New SqlCommand(strSQL, Objconn)

        UpdateCmd = New SqlCommand("UPDATE ContainerInfo SET PriorLocation = @PriorLocation WHERE (ContainerName = @Original_ContainerName); SELECT ContainerName, CurrentLocation, VendorCode, DatePlaced, PriorLocation, PriorVendorCode, PriorDatePlaced FROM ContainerInfo WHERE (ContainerName = @ContainerName))", Objconn)

        UpdateCmd.Parameters.Add("@ContainerName", Data.SqlDbType.NVarChar).Value = comContainerNumber.Text
        UpdateCmd.Parameters.Add("@CurrentLocation", Data.SqlDbType.NVarChar).Value = comCurrentLocation.Text
        UpdateCmd.Parameters.Add("@VendorCode", Data.SqlDbType.NVarChar).Value = comVendorCode.Text
        UpdateCmd.Parameters.Add("@DatePlaced", Data.SqlDbType.NVarChar).Value = dateDatePlaced.Text
        UpdateCmd.Parameters.Add("@PriorLocation", Data.SqlDbType.NVarChar).Value = comPriorLocation.Text
        UpdateCmd.Parameters.Add("@PriorVendorCode", Data.SqlDbType.NVarChar).Value = comPriorVendorCode.Text
        UpdateCmd.Parameters.Add("@PriorDatePlaced", Data.SqlDbType.DateTime).Value = datePriorDatePlaced.Text

        Dim strContainerName As String = comContainerNumber.Text
        If strContainerName = "" Then
            MsgBox("Container Number cannot be empty")
            Exit Sub
        End If

        Objconn.Close()
        Me.comContainerNumber.Text = ""
        Me.comVendorCode.Text = ""
        Me.comCurrentLocation.Text = ""
        Me.dateDatePlaced.Text = ""
        Me.comPriorLocation.Text = ""
        Me.comPriorVendorCode.Text = ""
        Me.datePriorDatePlaced.Text = ""
        MsgBox("Container Number updated successfully")
    End Sub



Let me know if you guys can help. Thanks

Reply With Quote
  #2 (permalink)  
Old June 11th, 2008, 12:27 PM
Registered User
 
Join Date: May 2008
Location: Dallas, TX, USA.
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I think I might need to have a stored procedure for this, I already made one for an update query, but still no avail.

Reply With Quote
  #3 (permalink)  
Old June 11th, 2008, 01:02 PM
Friend of Wrox
Points: 7,395, Level: 36
Points: 7,395, Level: 36 Points: 7,395, Level: 36 Points: 7,395, Level: 36
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Capital Federal, , Argentina.
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

Hi there..

Two weirds things I see on your code. You are actually creating an update command, but you are never executing it.. Also, you are sending a lot of parameters to an update query, but the query never use them...

And also, you are testing the value of container number, before you add it to the parameters collection, shouldn't that be done before that?

And yes, you can use an SP to do this.

HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from dparsons signature and he Took that from planoie's profile
================================================== =========
My programs achieved a new certification (can you say the same?):
WORKS ON MY MACHINE
http://www.codinghorror.com/blog/archives/000818.html
================================================== =========
I know that CVS was evil, and now i got the proof:
http://worsethanfailure.com/Articles...-Hate-You.aspx
================================================== =========
Reply With Quote
  #4 (permalink)  
Old June 11th, 2008, 03:02 PM
Registered User
 
Join Date: May 2008
Location: Dallas, TX, USA.
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Oh thanks Gonzalo. I sent all those parameters but didnt declare them yet b/c I was testing.

Everytime I put

UpdateCmd.ExecuteNonQuery()

After my parameters I always crash the program... why I dont know.

And for testing the value of container number before I add it to the parameters, you are suggesting I just switch the code around?

Thanks

PK

Reply With Quote
  #5 (permalink)  
Old June 11th, 2008, 03:49 PM
Friend of Wrox
Points: 7,395, Level: 36
Points: 7,395, Level: 36 Points: 7,395, Level: 36 Points: 7,395, Level: 36
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Capital Federal, , Argentina.
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

Well, it will crash if you send all those parameters that doesn't exists...

Anyway, can you debug your app. and test what is failing?

you are receiving an error somewhere...

HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from dparsons signature and he Took that from planoie's profile
================================================== =========
My programs achieved a new certification (can you say the same?):
WORKS ON MY MACHINE
http://www.codinghorror.com/blog/archives/000818.html
================================================== =========
I know that CVS was evil, and now i got the proof:
http://worsethanfailure.com/Articles...-Hate-You.aspx
================================================== =========
Reply With Quote
  #6 (permalink)  
Old June 12th, 2008, 11:33 AM
Registered User
 
Join Date: May 2008
Location: Dallas, TX, USA.
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for the suggestions Gonzalo. For some reason the power went out to my office last night and this morning I ran my program and it magically worked...

LOL...

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
How Do I SHOW and HIDE Windows Forms in VB.NET? kegeorge0819 VB How-To 1 March 10th, 2005 03:49 AM
Updating to a SQL Database Arsi C# 3 August 23rd, 2004 12:49 PM
Updating to SQL Database Arsi Classic ASP Databases 4 August 23rd, 2004 12:48 PM
Updating Database Through Forms clueless_may Access 1 April 16th, 2004 07:37 AM



All times are GMT -4. The time now is 02:08 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.