Solved

newbie VB and SQL problem

Posted on 2004-08-18
4
147 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
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…
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

688 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