Conditional statements with sql query

Question

I posted a question earlier but it is so complicated that I think that's the reason why nobody tries to answer it. So I decided to put this in the simplest way I can. So here is my code:

    Private Sub frmcrc_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    conn = GetConnect()
    Dim com As New SqlClient.SqlCommand
    Dim dr As SqlClient.SqlDataReader

    conn.Open()
    com.CommandText = "Select * from tblCRClearance where crc_no = '" & frmComm.txtCRCNum1.Text & "'"
    com.Connection = conn
    dr = com.ExecuteReader
    dr.Read()

    txtCRCNo.Text = dr!crc_no
    txtCLTAcctNo.Text = dr!crc_clientno
    lblrootpayee.Text = dr!crc_rootpayee
    txtPayeeName.Text = dr!crc_payeename
    txtClientName.Text = dr!crc_clientname
    txtProjName.Text = dr!crc_projectname
    txtCommType.Text = dr!crc_commtype
    txtGrossAmt.Text = dr!crc_grossamount
    txtWTax.Text = dr!crc_withheld
    txtCALType1.Text = dr!crc_caliqtype1
    txtCALDate1.Text = dr!crc_caliqdate1
    txtCALNo1.Text = dr!crc_caliqnum1
    txtCALAmt1.Text = dr!crc_caliqamt1
    txtCALDesc1.Text = dr!crc_caliqdesc1
    txtCALType2.Text = dr!crc_caliqtype2
    txtCALNo2.Text = dr!crc_caliqnum2
    txtCALAmt2.Text = dr!crc_caliqnum2
    txtCALDate2.Text = dr!crc_caliqdate2
    txtCALDesc2.Text = dr!crc_caliqdesc2
    txtCALType3.Text = dr!crc_caliqtype3
    txtCALDate3.Text = dr!crc_caliqdate3
    txtCALNo3.Text = dr!crc_caliqnum3
    txtCALAmt3.Text = dr!crc_caliqamt3
    txtCALDesc3.Text = dr!crc_caliqdesc3
    txtCALIQ.Text = dr!crc_caliqtotal
    txtNetAmt.Text = dr!crc_netamount
    txtVoucherNum.Text = dr!crc_voucherno
    txtCRLNum.Text = dr!crc_crlno
    txtDate.Text = dr!crc_date
    txtCPFNo.Text = dr!crc_cpfno
    txtPreparedBy.Text = dr!crc_preparedby
    txtCheckedBy.Text = dr!crc_checkedby
    txtApprovedBy.Text = dr!crc_approvedby
    txtGrossPrice.Text = dr!crc_grossprice
    txtLess1.Text = dr!crc_lessprice1
    txtDesc1.Text = dr!crc_lessdesc1
    txtLess2.Text = dr!crc_lessprice2
    txtDesc2.Text = dr!crc_lessdesc2
    txtLess3.Text = dr!crc_lessprice3
    txtDesc3.Text = dr!crc_lessdesc3
    txtNetSellingP.Text = dr!crc_netsellingprice
    txtRate.Text = dr!crc_rate
    txtGrossRel.Text = dr!crc_grossrelease
    txtDed1.Text = dr!crc_ded1
    txtDDesc1.Text = dr!crc_deddesc1
    txtDed2.Text = dr!crc_ded2
    txtDDesc2.Text = dr!crc_deddesc2
    txtDed3.Text = dr!crc_ded3
    txtDDesc3.Text = dr!crc_deddesc3
    txtDed4.Text = dr!crc_ded4
    txtDDesc4.Text = dr!crc_deddesc4
    txtDed5.Text = dr!crc_ded5
    txtNetRel.Text = dr!crc_netrelease
    txtCollectibles.Text = dr!crc_collectibles


    conn.Close()


    txtCLTAcctNo.Text = frmComm.cmbAcctNo1.Text
    txtCRLNum.Text = frmComm.txtCRLNo.Text
    txtProjName.Text = frmComm.txtUnitCode1.Text
    txtClientName.Text = frmComm.cmbClientName1.Text + " " + frmComm.Label13.Text

    txtCommType.Text = frmComm.cmbParticulars1.Text
    txtPayeeName.Text = frmComm.cmbPayee01.Text
    Me.lblrootpayee.Text = frmComm.lblrootpayee.Text

End Sub

It works like this: the sql query gets the data with the crc number the same as in txtCRCNum1. It works just as how I want it to, but I have some issues about this. I have 30 textboxes just like txtCRCNum1, so there are going to be 30 different values of crc number, depending on which textbox will I get its value. I want to have a conditional statement wherein it checks if the textbox that should contain the crc number is empty. If it is empty, then the fields will display just do this:

    txtCLTAcctNo.Text = frmComm.cmbAcctNo1.Text
    txtCRLNum.Text = frmComm.txtCRLNo.Text
    txtProjName.Text = frmComm.txtUnitCode1.Text
    txtClientName.Text = frmComm.cmbClientName1.Text + " " + frmComm.Label13.Text

    txtCommType.Text = frmComm.cmbParticulars1.Text
    txtPayeeName.Text = frmComm.cmbPayee01.Text
    Me.lblrootpayee.Text = frmComm.lblrootpayee.Text

If not empty, then it will execute the sql query part.

How should I put up the conditional statement so that it will work as I have described?

I'm unclear..... Are you asking us where to put an IF in your code when you've already seemingly defined it and just need to write the IF? Also - just as a FYI - but do you know the SQL Injection danger with the way you just take the value form a text box and puts it into the SQL Query? A malignant user could delete your database with that field? — Allan S. Hansen
– Allan S. Hansen, Commented May 5, 2014 at 7:00
I'm sorry. I mean, I tried writing an IF statement for the conditions I have mentioned but it doesn't work. And about your FYI (thanks, I never knew that), can you tell me how I can do it without putting my database in danger? Like what I did above, how should I do it? — airhalynn101
– airhalynn101, Commented May 5, 2014 at 8:07
the value can be passed to the new form as a property, an argument to a method or in the form constructor so that it wont matter which textbox it came from. e.g. "Where crc_no = " & myCrcVal.ToString. This form would use variable value and not need to use an IF except maybe if the var is String.Empty. Even that could be minimized. And do use parameters for queries. — Ňɏssa Pøngjǣrdenlarp
– Ňɏssa Pøngjǣrdenlarp, Commented May 5, 2014 at 13:01
@Arman This is how I tried to do it: If txtCRCNum1.Text <> "" Then 'sql query else 'display some values — airhalynn101
– airhalynn101, Commented May 6, 2014 at 1:39

Ňɏssa Pøngjǣrdenlarp · Accepted Answer · 2014-05-06 13:02:13Z

1

You can add procedures and methods to forms just like any other class. In this case, since the CRCForm in all cases depends on a crc value, we will pass it in the constructor.

Public Class frmcrc
    ' a crc value property
    Private _crcVal As String = ""            ' I am guessing it is string

    ' the constructor
    public Sub New(crcval As String)
         ' This call is required by the designer.
         InitializeComponent()

         _crcVal = crcval
    End Sub

    ' etc
End Class

When the user clicks one of the 30 (?!) textbox/button pairs. This requires a form instance rather than the VB default instance (ie frmcrc.Show) which is bad anyway.

Dim f As New frmCrc(txtCRCNum1.Text)
f.Show

The form now has the value it needs to work with whether it is from TB1, Tb2 or TB22. The form need not know or care where it came from. The query would use _crcVal in the SQL.

The existing form load code then just needs to deal with cases where _crcVal is String.Empty. If it is not String.Empty then just post the frmComm parts (which probably ought to be a class) Else perform the query and display results.

You could also add a CrcValue property to the form to execute the query and display the results without creating a new form instance, just use the new crcvalue.

EDIT

A class for the "other" stuff - I cant tell what the data represents, I'll call it BaseAcct:

Public|Friend Class BaseAcct
   Public Property AccountNumber As String

   Public Property UnitCode As String
   ' etc

End Class

Newer versions support auto implemented properties where that is all you need to define a property. Older versions, you will need to code a Get and Set. You could also add procedures like FetchData and DisplayData to have the class get the data from one form and post it to another, specifying which form as an parameter. The ELSE you have to copy data from one Form to the other could then be:

  Dim BAcct As New BaseAcct
  ' ... etc

  If _crcVal <> String.Empty Then 
     ' ...

  Else
      BAcct.Display(Me)    ' let the class post the data to this form
  End If

The crcVal can be one of the Properties in the class, but even if it is, I would require that it be passed to frmcrc in the constructor since that form seems to be meaningless without it. That is, your code should not be able to create a form instance without that value.

edited May 6, 2014 at 13:02

answered May 5, 2014 at 13:41

Ňɏssa Pøngjǣrdenlarp

39k12 gold badges65 silver badges193 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

airhalynn101 Over a year ago

Should I put it on frmcrc or on the form where txtcrcnum gets its value?

Ňɏssa Pøngjǣrdenlarp Over a year ago

Should I put it on... what is "it" pray tell? Thats the framework for your crc form class; specifically, how to pass it a TB value when you go to show it.

airhalynn101 Over a year ago

I know, I already got it. Sorry. One more: can I use this to pass more than one values (not just the txtcrcnum) to frmcrc?

Ňɏssa Pøngjǣrdenlarp Over a year ago

If you are thinking of passing all those other form values, you are better off creating a small class, stash the values there and use them from the class. That class could also be used to display the values.

airhalynn101 Over a year ago

I have read this thread and tried it for the crc number and somehow I succeed. Public Property CRCNum As ``String Get Return txtCRCNo.Text End Get Set(ByVal value As String) ``End Set End Property But does this only store one value or can I store as much values as I want here? If possible, how? Should I name them all with return (return txtcrcnum, return netamt, ...)?

King of kings · Accepted Answer · 2014-05-05 12:20:23Z

0

First thing, you should be aware of all the 30 textboxes ids'. I dont know where would you place the if statement. but i would suggest you to have a button_click event where you will check all the textboxes.

Another thing for all the different 30 textboxes, Do you have the same textboxes like txtCRCNo,txtCLTAcctNo,lblrootpayee,etc., If it so , then you can allow only one textbox at a time.

So make textbox_TextChanged event for all the textboxes,while typing into a textbox all other textboxes should be empty.

Note: put all the textboxes in a panel.

like,

  Sub txtCRCNum1_TextChanged()
       txtCRCNum2.Text=""
       txtCRCNum2.Enabled=False
       ' and so on
  End Sub

Now it is easy to make your solution on button_click

  Sub button_click()
    Dim ctrl As New Control
    For Each ctrl In Panel1.Controls
        If TypeOf ctrl Is TextBox Then
            If ctrl.Text <> "" Then
                'sql query with that textbox
            End If
        End If
    Next

answered May 5, 2014 at 12:20

King of kings

6952 gold badges6 silver badges21 bronze badges

1 Comment

airhalynn101 Over a year ago

I have already done this, and it helped me organize all those textboxes. But when I'm trying to do the IF part (as you have suggested), the sql query doesn't work (or the whole thing doesn't even reach to that because of some faults I can't identify). :(

Collectives™ on Stack Overflow

Conditional statements with sql query

2 Answers 2

5 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related