Solved

newbie VB and SQL problem

Posted on 2004-08-18
4
144 Views
Last Modified: 2010-04-23
Hi,

I have a form with a few textboxes for user input.  The form is to remove quantity from a pallet.  I cannot seem to figure out how to query my database and update the quantity based on this information.

I want to select palletid, quantity from tblPallets where palletid = txtpalletid.text and then subtract the value of val(txtQuantity.text) from that pallet.

But which object so i use?  Do i do it via code or those drop in objects from the toolbar?  How to I get a textbox value into one of those toolbar sql objects?

Frustrated,
Dan
:-)
0
Comment
Question by:Dan Flood
  • 2
4 Comments
 
LVL 7

Expert Comment

by:natloz
ID: 11836305
Well... a SQL statement would look like:

Dim strSQL as string

'Make sure it is number and not blank...
if IsNumeric(txtQuantity.text) and IsNumeric(txtPalletID.text) then
  if txtQuantity.text <> "" and txtPalletID <> "" then
     strSQL = "UPDATE tblPallets SET quantity = quantity - " & CInt(txtQuantity.text) & " where palletID = " & _
                    Cint(txtPalletID.text)
  endif
endif

then do a DataReader with EXECUTENONQuery to do the update...
0
 
LVL 18

Accepted Solution

by:
DotNetLover_Baan earned 400 total points
ID: 11836734
Hi there..
if you want to do it drag and drop way...(I don't recommend though), follow these steps..
1) Drag n drop an SqlConnection object from the "Data" tab of the ToolBox.
2) Select it and go to its properties in the Property box. Click on the connection string, click New Connection and create the connection. Say the name of this control is  SqlConnection1
here is the code...

Sub ChangeData()
   Dim SqlStr As String="UPDATE tblPallets SET quantity = quantity - " & CInt(txtQuantity.text) & " where palletID = " & Cint(txtPalletID.text)
   Dim SqlCmd As New SqlCommand(SqlStr, SqlConnection1)
   Try
      SqlConnection1.Open()
      SqlCmd.ExecuteNonQuery()
      SqlConnection1.Close()
   Catch Ex As Exception
      MsgBox(Ex.ToString)
   End Try
End Sub


Now use this sub whenever you need it.
-Baan
0
 

Author Comment

by:Dan Flood
ID: 11841107
Thank you - both answers helped - is there a way to split points?
0
 
LVL 7

Expert Comment

by:natloz
ID: 11844305
You can split points.

Yes. Scroll down to the bottom of the question, just above the text box, and click the "Split Points" link. Select the radio button of the comment who you want to Accept as the Accepted Answer. Only one button can be selected. Set the point value (a text box above the comment) of how much you want this person to receive of the points. Then set the point values for each of the experts comments to whom you want to allocate points and these will be considered Assisted Answers in helping you resolve the issue. Double check your information and then click the Submit button at the bottom of the page. One note: the total points of the splits must equal the amount you asked the question for itself, and no person can receive fewer than 20 points.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question