Posts by Papa_Don

Here's what I've done. Keep in mind that, in my search I was looking to see if an existing account number exists. But you can do the same thing with names, addresses, etc. Just change the data field you're searching.

Dim con As New SqlConnection
        Dim cmd As New SqlCommand
        Dim query As String = "Select Count (*) As CustAccntNo FROM CustRec WHERE CustAccntNo = @AcctNo"
        Try
            con.ConnectionString = "Data Source=(LocalDB)\MSSQLLocalDB;Initial Catalog=C:\USERS\DON\DOCUMENTS\SALES.MDF;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False"
            con.Open()
            cmd.Connection = con
            cmd = New SqlCommand((query), con)
            cmd.Parameters.AddWithValue("@AcctNo", SqlDbType.BigInt).Value = Convert.ToInt64(tbxAccntNo.Text)
            execScarlar = Convert.ToInt64(cmd.ExecuteScalar())

        Catch ex As Exception
            MessageBox.Show("Invalid" & ex.Message)
        Finally
            con.Close()

        End Try
        If execScarlar <> 0 Then
                MessageBox.Show("Account No. " & tbxAccntNo.Text & " has already been used. Please use another account number.", "Customer Account Number")
                tbxAccntNo.Text = ""
                tbxAccntNo.Select()
                Exit Sub
        End If

Hi Group,

I'm using the split function for the very first time. I understand what it's doing. However I don't know how to output each individual word into a specific variable.

As an example, the string is "Dickerson Tile Company". I have defined 7 variables to accept up to 7 word splits.

Dim Search1, Search2, Search3 ...... Search7

Ultimately I want to output the split into

Search1 = "Dickerson"
Search2 = "Tile"
Search3 = "Company"

My code is currently

Dim s As String = tbxCustomerName.Text
Dim words As String() = s.Split(New Char() {" "c}) 'words are delimited with a space " "

This is where I'm struggling: How do I get my output into the individual buckets? My assumption is I would use an array or For statement. However I can't seem to find the correct syntax to create the name of the variable (Search1, Search2, etc.) and then to get the individual word into each variable. Can you help?

Thanks for your assistance.

Papa Don

I was able to figure this out. Here's what I did:

 Private Sub tbxAccountNo_LostFocus(sender As Object, e As EventArgs) Handles tbxAccountNo.LostFocus
    If GlobalVariables.custpnl1 = 2 And tbxAccountNo.Text.Length = 10 And IsNumeric(tbxAccountNo.Text) = True Then
        Dim dt As New DataTable()
        Dim rowIndex As Integer = 0
        Dim searchID As Int64 = Convert.ToInt64(tbxAccountNo.Text)
        Dim strQ As String = String.Empty
        Dim conStr As String

        strQ = "SELECT CUST_ACCT_NO
                FROM CUSTREC 
                WHERE CUST_ACCT_NO = " & searchID
        conStr = "Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\Don\Documents\Visual Studio 2019\SalesForm\DWHRPT.mdf;Integrated Security=True"

        Dim dataAdapter As New SqlDataAdapter(strQ, conStr)
        dataAdapter.Fill(dt)
        dataAdapter.Dispose()
        acctNoExists = False
        For i As Integer = 0 To (dt.Rows.Count - 1)
            rowIndex = i
            If searchID = Convert.ToInt64(CStr(dt.Rows(rowIndex)("CUST_ACCT_NO"))) Then
                acctNoExists = True
                MessageBox.Show("Account #" & searchID & " exists.  Please enter a unique 10 digit account number.", "", MessageBoxButtons.OK)
                tbxAccountNo.Clear()
                tbxAccountNo.Select()
                Exit Sub
            End If
        Next
    End If
End Sub

I hope someone else can use this method.

Don

Close this post.

@rproffitt,

Yes, the same one. I'm no longer with them as the company was purchased by Marriott and the end of 2016. My position ended in April of 2017. I'm now trying to get back to some similar position with another company but I haven't written the first line of code (until now) since that time.

@ Minimalist, I've run your test and found that it didn't find or kill anything on the network. It only killed those Excel applications running on my computer (which is what I anticipated and wanted it to do). Ironically though, I had to run the "Kill" commands twice to get it to clear everything out.

Thanks to everyone who helped out. This was truly great learning - which I greatly appreciate!

Don

Group,

I've figured it out: Since Outlook is using HTML, I needed to wrap my information within the HTML syntax. Here's what it now looks like:

`.HTMLBody = "<p>Text above Excel cells" & "<br><br>" & _
                RangetoHTML(rng) & "<br><br>" & _
                "Text below Excel cells.</p>"`

Don

As it turns out, I had a table listed twice which may have been causing the issue. I took it out, change to a different table and it worked correctly. Here's the final code:

case
          WHEN EXTRACT(YEAR from a19.FULL_DATE) IN ('2015') and a15.COUNTRY_NAME in ('CANADA')
          then sum((A11.RM_NT_QTY * A11.RM_RATE_USD_AMT) * (SELECT  d_cur_exchng.cur_exchng_from_usd_rate
                                                            FROM    d_cur_exchng,
                                                                    crmmart.d_date_period
                                                            WHERE   d_cur_exchng.date_key = a11.STAY_DATE_KEY  and
                                                                    crmmart.d_date_period.full_date = a19.full_date and
                                                                    cur_key = 113 and 
                                                                    rcrd_sts_cd = 'A'))

Got it! Thanks group!

Don

For what it's worth, I've discovered that the formatting of what I'm searching with has to be in the same format as what I'm searching for. In other words if the search criteria is '04-OCT' then the date on sheet2 needs to be in the same format (which I thought was odd). I was truly under the impression that a date, regardless of how it was formatted on the spreadsheet, was being read as an integer representing the number of days beyond 1/1/1900. However I found that to be untrue.

Stuugie, thanks for the help. Although I didn't use your code, I do like the way you wrote it.

tinstaafl,

Never mind. I found a fix. It turned out pretty simple. For the benefit of others, here's what I did:

Private Sub cmbxPrinter_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles cmbxPrinter.SelectedIndexChanged
        Dim sindex As Integer
        Dim sitem As Object
        Dim strsitem As String
        Dim spltitem As String
        If cmbxPrinter.SelectedIndex > -1 Then
            sindex = cmbxPrinter.SelectedIndex
            sitem = cmbxPrinter.SelectedItem
            txbxCustomerNo.Focus()
        End If
        strsitem = Convert.ToString(sitem)
        spltitem = strsitem.Split("|").Last
        printID = spltitem

And, by the way, printID wasn't holding the correct ID as I thought. It was holding the last ID number from the Data Table that was accessed earlier in the module. It just happened that I chose that printer as my test. However with the new code I've written, the correct ID is captured.

Thanks for everyones help!!

Don

Group,

I found the correct code syntax:

SET TermsPcnt = @tPcnt, TermsDescription = @tDescription, TermsDays = @tDays, TermsProx = @tProx, TermsBillType = @tBillType
WHERE TermsCodeID = @svID
            cmd.Parameters.AddWithValue("@tID", trmID)
            cmd.Parameters.AddWithValue("@tPcnt", termsPcnt)
            cmd.Parameters.AddWithValue("@tDescription", termsDesc)
            cmd.Parameters.AddWithValue("@tDays", tDays)
            cmd.Parameters.AddWithValue("@tProx", sProx)
            cmd.Parameters.AddWithValue("@tBillType", billType)

This is written in Visual Basic.

Hopefully others will find benefit from this.

Thanks, group!

Don