Solved

newbie VB and SQL problem

Posted on 2004-08-18
4
140 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:jakyll
  • 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:jakyll
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

707 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now