Posted on

Splitting string into individual variables

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

Posted on

Split a string into individual words

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

Posted on

Error when running program

Hi group,

It's been some time since I last posted. It's good to be involved in programming again. It's been much too long.

It's been a while since I've written code that would write to a SQL database. It seems like the connection string is correct. However I'm getting a 'InvalidArithmetic operation resulted in an overflow'. I'm unsure why I'm getting this. Hopefully one of you will see it in my code and make a suggestion on how to fix. Here's my code:

    Dim con As New SqlConnection
    Dim cmd As New SqlCommand
    Dim query As String = "INSERT INTO CustRec (CustAccntNo, CustName, CustAddr1, CustAddr2, CustCity, CustState, CustZipCode, CustPhoneNo, CustAltPhone, CustType, CustSendStatement, CustCreateDate) &
             VALUES (@AcctNo, @CustName, @Addr1, @Addr2, @City, @State, @ZipCode, @Phone, @AltPhone, @CustType, @SendStatement, @CreateDate) "
    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.Add("@AcctNo", SqlDbType.Int).Value = CInt(tbxAccntNo.Text)
        cmd.Parameters.Add("@CustName", SqlDbType.VarChar).Value = tbxCustomerName.Text
        cmd.Parameters.Add("@Addr1", SqlDbType.VarChar).Value = tbxAddr1.Text
        cmd.Parameters.Add("@Addr2", SqlDbType.VarChar).Value = tbxAddr2.Text
        cmd.Parameters.Add("@City", SqlDbType.VarChar).Value = tbxCity.Text
        cmd.Parameters.Add("@State", SqlDbType.VarChar).Value = tbxState.Text
        cmd.Parameters.Add("@ZipCode", SqlDbType.VarChar).Value = tbxZipCode.Text
        cmd.Parameters.Add("@Phone", SqlDbType.VarChar).Value = tbxCustPhoneNo.Text
        cmd.Parameters.Add("@AltPhone", SqlDbType.VarChar).Value = tbxCustAltPhone.Text
        cmd.Parameters.Add("@CustType", SqlDbType.VarChar).Value = tbxCustType.Text
        cmd.Parameters.Add("@SendStatement", SqlDbType.VarChar).Value = tbxSendStatement.Text
        cmd.Parameters.Add("@CreateDate", SqlDbType.Date).Value = Date.Now
        cmd.ExecuteNonQuery()

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

Do you see where it may be? The only thing 'arithmetic' is that I'm converting a number in a textbox to Cint because it is an integer (a 7 digit integer ie. 9999999999). Any thoughts?

Thanks for your help

Posted on

Write information to Database

Hi group,

I'm trying to store the information from a various textboxes into my database. However it is not writing the data (I'm getting my planned error message, "New Record FAILED. Please contact your systems administrator." From the code below, do you see where my error may be? What corrections do I need to make to ensure a successful insert?

Dim con As New SqlClient.SqlConnection
        con.ConnectionString = ("Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\Don\Documents\Visual Studio 2019\SalesForm\DWHRPT.mdf;Integrated Security=True")
        con.Open()
        Dim com As New SqlClient.SqlCommand("", con)
        com.CommandText = "INSERT INTO CUSTREC (CUST_ACCT_NO, CUST_COMPANY_NAME, CUST_LOCATION, CUST_TYPE, CUST_FIRST_NAME, CUST_MIDDLE_INITIAL, 
                                                  CUST_LAST_NAME, CUST_MAIL_ADDRESS1, CUST_MAIL_ADDRESS2, CUST_MAIL_CITY, CUST_MAIL_STATE, 
                                                  CUST_MAIL_ZIP_CODE, CUST_MAIL_ZIP_PLUS4, CUST_CREDIT_LIMIT, CUST_PRIMARY_PHONE, CUST_ALT_PHONE, 
                                                  CUST_TAX_EXEMPT_STATUS, CUST_TAX_EXEMPT_ID, CUST_SHIP_TO_ADDRESS1, CUST_SHIP_TO_ADDRESS2, 
                                                  CUST_SHIP_TO_CITY, CUST_SHIP_TO_STATE, CUST_SHIP_TO_ZIP_CODE, CUST_SHIP_TO_ZIP_PLUS4, CUST_USE_SELL_PRICE,
                                                  CUST_STD_DISC_PCNT, CUST_SHIP_TO_CONTACT_NAME, CUST_SHIP_TO_PHONE_NO, CUST_START_DATE) 
                            VALUES (@AcctNo, @CompName, @Loc, @Type, @FirstName, @MidInt, @LastName, @MailAddr1, @MailAddr2, @MailCity, @MailState, 
                                    @MailZip, @MailZipPlus4, @CreditLimit, @PrimaryPhone, @AltPhone, @TaxStatus, @TaxID, @ShipAddr1, @ShipAddr2,
                                    @ShipCity, @ShipState, @ShipZipCode, @ShipZipPlus4, @SellPrice, @Disc, @ShipContactName, @ShipToPhone, @StartDate)"

        Try
            com.Parameters.Add("@AcctNo", SqlDbType.BigInt).Value = tbxAccountNo.Text
            com.Parameters.Add("@CompName", SqlDbType.VarChar).Value = tbxCompanyName.Text
            com.Parameters.Add("@Loc", SqlDbType.Int).Value = tbxLoc.Text
            com.Parameters.Add("@Type", SqlDbType.VarChar).Value = tbxCustType.Text
            com.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = tbxFirstName.Text
            com.Parameters.Add("@MidInt", SqlDbType.VarChar).Value = tbxMiddleInt.Text
            com.Parameters.Add("@LastName", SqlDbType.VarChar).Value = tbxLastName.Text
            com.Parameters.Add("@MailAddr1", SqlDbType.VarChar).Value = tbxAddress1.Text
            com.Parameters.Add("@MailAddr2", SqlDbType.VarChar).Value = tbxAddress2.Text
            com.Parameters.Add("@MailCity", SqlDbType.VarChar).Value = tbxCity.Text
            com.Parameters.Add("@MailState", SqlDbType.VarChar).Value = tbxState.Text
            com.Parameters.Add("@MailZip", SqlDbType.VarChar).Value = zipCode
            com.Parameters.Add("@MailZipPlus4", SqlDbType.VarChar).Value = zipPlus4
            com.Parameters.Add("@CreditLimit", SqlDbType.Int).Value = tbxCreditLimit.Text
            com.Parameters.Add("@PrimaryPhone", SqlDbType.BigInt).Value = primephone
            com.Parameters.Add("@AltPhone", SqlDbType.BigInt).Value = altphone
            com.Parameters.Add("@TaxStatus", SqlDbType.VarChar).Value = tbxTaxExempt.Text
            com.Parameters.Add("@TaxID", SqlDbType.VarChar).Value = tbxTaxExemptID.Text
            com.Parameters.Add("@ShipAddr1", SqlDbType.VarChar).Value = tbxShipAddress1.Text
            com.Parameters.Add("@ShipAddr2", SqlDbType.VarChar).Value = tbxShipAddress2.Text
            com.Parameters.Add("@ShipCity", SqlDbType.VarChar).Value = tbxShipCity.Text
            com.Parameters.Add("@ShipState", SqlDbType.VarChar).Value = tbxShipState.Text
            com.Parameters.Add("@ShipZipCode", SqlDbType.VarChar).Value = ShipZipCode
            com.Parameters.Add("@ShipZipPlus4", SqlDbType.VarChar).Value = ShipZipPlus4
            com.Parameters.Add("@SellPrice", SqlDbType.Int).Value = Convert.ToInt32(tbxUseSellPriceNo.Text)
            com.Parameters.Add("@Disc", SqlDbType.Int).Value = Convert.ToInt32(tbxStdDiscPcnt.Text)
            com.Parameters.Add("@ShipContactName", SqlDbType.VarChar).Value = tbxShipToContactName.Text
            com.Parameters.Add("@ShipToPhone", SqlDbType.BigInt).Value = ShipPhone
            com.Parameters.Add("@StartDate", SqlDbType.VarChar).Value = tbxAcctStartDate.Text
            com.ExecuteNonQuery()
            MessageBox.Show("New Record Created", "", MessageBoxButtons.OK)
        Catch ex As System.Data.SqlClient.SqlException
            MessageBox.Show("New Record FAILED.  Please contact your systems administrator.", "", MessageBoxButtons.OK)
        End Try
        con.Close()

In advance, thanks for your help.

Don

Posted on

Is it realistic to pursue a career in programming?

Group,

I've dabbled in coding (both front end and back end) since the mid 1990s. However I've never done it full time (except for a short stint writing database query's in 2015 and 2016). Because of the economic downturn in 2008 and the sale of Starwood Hotels to Marriott, I find myself with accepting what amounts to temp jobs to (somewhat) support myself. Thus I've pondered the idea of a coding school or a certificated program through one of the local college to gain useful skills in the field. But here's the rub: I'm almost 60. So here are a few questions that I have for the group. I'll appreciate your candid answers.

1) Is it realistic to even consider this as a career for the next 10 years for someone my age? Assuming it is,
2) Are there some coding schools that you can comfortably recommend that are accepted by the IT industry?
3) Are there other practical ways to gain knowledge and experience that would be useful to a future employer?
4) What languages are the most desired today?
5) Is most of the programming done today as a web-based application (this is primarily what I see the coding schools training one for)?

In advance, I appreciate your thoughts and ideas. I look forward to hearing from you.

Don

Posted on

VB.net Check datatable for an existing account number

Hi group,

I'm attempting to write code to check to see if an existing account number exists. The user is to input a 10 digit account number. The code then is to query the database to see if that number exists. If it does, the message box displays the message that the number exists. However the code I've written isn't working as I wish. Can you offer some suggestions as to how to do this correctly?

Here's what I've attempted:

Private Sub tbxAccountNo_TextChanged(sender As Object, e As EventArgs) Handles tbxAccountNo.TextChanged
    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
        Dim strQ As String = String.Empty
        Dim conStr As String

        Dim msgAcctNo 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()

        For i As Integer = 0 To (dt.Rows.Count - 1)
            rowIndex = i
            If IsDBNull(dt.Rows(rowIndex)("CUST_ACCT_NO")) Then
                acctNoExists = False
            Else
                msgAcctNo = CStr(dt.Rows(rowIndex)("CUST_ACCT_NO"))
                acctNoExists = True
                MessageBox.Show("This account number exists.  Please enter a unique 10 digit account number.", "", MessageBoxButtons.OK)
                Exit Sub
            End If
        Next
    End If
End Sub

The "Text_Changed" event is used to fire this off - when there are 10 numeric characters in the textbox. That part seems to be working correctly as I've toggled it to stop at "For i As Integer = 0 To (dt.Rows.Count - 1)". But at this point, that's the only thing that seems to be working.

If you can teach me what I'm doing wrong, please feel free to do so.

As always, thanks for your help.

Don

Posted on

Why is the ListView box showing same headings twice?

Group,

I've written some code to populate a listview using 3 fields from a database. When creating the listview, I named the individual columns for appearance purposes, (Account No, Company Name and Name). Those columns are being populated appropriately as expected. However data column names are being shown in column 4 5 and 6, but with no data. What in my code is causing this? Any suggestions on how to fix this?

My code is as follows:

Private Sub tbxCompanyName_Leave(sender As Object, e As EventArgs) Handles tbxCompanyName.Leave
    lvwSearchCustomer.Visible = True
    Me.lvwSearchCustomer.View = View.Details
    Me.lvwSearchCustomer.GridLines = True
    Dim strQ As String = String.Empty
    Dim datasource As String = "Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\Don\Documents\Visual Studio 2019\SalesForm\DWHRPT.mdf;Integrated Security=True"
    conn = New SqlConnection(datasource)
    Dim searchID As String = tbxCompanyName.Text

    strQ = "SELECT CUST_ACCT_NO, 
            CUST_COMPANY_NAME,
            CONCAT(CUST_FIRST_NAME,' ',CUST_MIDDLE_INITIAL,' ',CUST_LAST_NAME) as MailingName    
            FROM CUSTREC 
            WHERE CUST_COMPANY_NAME LIKE '" & searchID & "%' OR CUST_LAST_NAME LIKE '" & searchID & "%'"

    cmd = New SqlCommand(strQ, conn)
    da = New SqlDataAdapter(cmd)
    ds = New DataSet
    da.Fill(ds, "Tables")
    Dim i As Integer = 0
    Dim j As Integer = 0
    ' adding the columns in ListView
    For i = 0 To ds.Tables(0).Columns.Count - 1
        Me.lvwSearchCustomer.Columns.Add(ds.Tables(0).Columns(i).ColumnName.ToString())
    Next
    'Now adding the Items in Listview
    For i = 0 To ds.Tables(0).Rows.Count - 1
        For j = 0 To ds.Tables(0).Columns.Count - 1
            itemcoll(j) = ds.Tables(0).Rows(i)(j).ToString()
        Next
        Dim lvi As New ListViewItem(itemcoll)
        Me.lvwSearchCustomer.Items.Add(lvi)
    Next

End Sub

In advance, thanks for your assistance.

Don

Posted on

SQL error in VB.net

Hi group,

I'm attempting to populate a listview with data from my test database created via Visual Studio 2019. I'm getting a syntax error that says, "Incorrect syntax near the keyword 'FROM'." It's not clear to me what the error is. The code I've written is as follows:

Dim strQ As String = String.Empty
Dim datasource As String = "Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\Don\Documents\Visual Studio 2019\SalesForm\DWHRPT.mdf;Integrated Security=True"
conn = New SqlConnection(datasource)
Dim searchID As String = tbxCompanyName.Text
 strQ = "SEARCH CUST_ACCT_NO, CUST_COMPANY_NAME, CUST_FIRST_NAME, CUST_MIDDLE_INITIAL, CUST_LAST_NAME 
            FROM CUSTREC 
            WHERE CUST_COMPANY_NAME LIKE '" & searchID & "%'"
cmd = New SqlCommand(strQ, conn)
da = New SqlDataAdapter(cmd)
ds = New DataSet
da.Fill(ds, "CUSTREC")

The error is displayed via the last line above (I'm not sure why, but that's a question for later). I've tried to read through the details of the error message, but most of this doesn't mean anything to me yet.

If you will, could you look through my code and the details of the error message to see where my syntax error truly is. Any "schooling" you can offer will be appreciated.

System.Data.SqlClient.SqlException
HResult=0x80131904
Message=Incorrect syntax near the keyword 'FROM'.
Source=.Net SqlClient Data Provider
StackTrace:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable)
at SalesForm.CustomerInfo.tbxCompanyName_Leave(Object sender, EventArgs e) in C:\Users\Don\Documents\Visual Studio 2019\SalesForm\CustomerInfo.vb:line 189
at System.Windows.Forms.Control.OnLeave(EventArgs e)
at System.Windows.Forms.Control.NotifyLeave()
at System.Windows.Forms.ContainerControl.UpdateFocusedControl()
at System.Windows.Forms.ContainerControl.AssignActiveControlInternal(Control value)
at System.Windows.Forms.ContainerControl.ActivateControlInternal(Control control, Boolean originator)
at System.Windows.Forms.ContainerControl.SetActiveControlInternal(Control value)
at System.Windows.Forms.ContainerControl.set_ActiveControl(Control value)
at System.Windows.Forms.Control.Select(Boolean directed, Boolean forward)
at System.Windows.Forms.Control.SelectNextControl(Control ctl, Boolean forward, Boolean tabStopOnly, Boolean nested, Boolean wrap)
at System.Windows.Forms.Form.ProcessTabKey(Boolean forward)
at System.Windows.Forms.ContainerControl.ProcessDialogKey(Keys keyData)
at System.Windows.Forms.Form.ProcessDialogKey(Keys keyData)
at System.Windows.Forms.TextBoxBase.ProcessDialogKey(Keys keyData)
at System.Windows.Forms.Control.PreProcessMessage(Message& msg)
at System.Windows.Forms.Control.PreProcessControlMessageInternal(Control target, Message& msg)
at System.Windows.Forms.Application.ThreadContext.PreTranslateMessage(MSG& msg)
at System.Windows.Forms.Application.ThreadContext.System.Windows.Forms.UnsafeNativeMethods.IMsoComponent.FPreTranslateMessage(MSG& msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
at SalesForm.My.MyApplication.Main(String[] Args) in :line 81

Posted on

Visual Studio 2019 with a database

Hi group,

I've just installed Visual Studio 2019 and have started my first app using this. Within the program I created a database using the tools provided in Visual Studio. I now want to begin writing to the tables I've created. But I'm stumped on how to do this. Hopefully you can help.

This database is installed on my laptop and is called DATAWHOUSE. Will I need some kind of connection string to write to the data table (and to read from it and/or make changes to it in the future)?