@Dani,
I just tried to use the comment button inside your post. However I can't see this either. But I'm not deleting them I gather you saw my comment in your email. Like you I don't see it here either.
For the record, I don't know how to delete anything here. I've not found an edit/delete button.
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@rprofitt,
I'm beginning to understand what you're pushing me to do. I've rewritten the code to read as:
Dim words As String() = s.Split(New Char() {" "c})
Dim Search1 As String
Dim Search2 As String
Dim Search3 As String
Dim Search4 As String
Dim Search5 As String
Dim Search6 As String
Dim Search7 As String
Dim i As Integer = 1
Dim searchName As Object
For Each word As String In words
searchName = word
If i = 1 Then
Search1 = searchName.ToString
End If
If i = 2 Then
Search2 = searchName.ToString
End If
If i = 3 Then
Search3 = searchName.ToString
End If
If i = 4 Then
Search4 = searchName.ToString
End If
If i = 5 Then
Search5 = searchName.ToString
End If
If i = 6 Then
Search6 = searchName.ToString
End If
If i = 7 Then
Search7 = searchName.ToString
End If
i += 1I'm not sure that's the most efficient way to write the code. I'll continue to research the better way. But this does work. It seems I didn't understand :
For Each word As String In words
Console.WriteLine("WORD: {0}", word)
NextI discovered that "WORD: {0}" wasn't required. The sytax, "For Each word As String In words" is the actual code/loop that began to separate the words. Then it was just putting the words into the appropriate variable.
Thanks for your help and for the push. You made me think!
I just figured out how to post code.
Here's what I've attempted, but it's failing:
Dim i As Integer = 1
Dim searchName As String
Dim searchNo As String
Dim search1 As String
Dim search2 As String
Dim search3 As String
Dim search4 As String
Dim search5 As String
Dim search6 As String
Dim search7 As String
For Each word As String In words
searchNo = Convert.ToString(i)
searchName = "Search" & searchNo
If searchName = "search1" Then
search1(": {0}", word)
i += 1
End If
If searchName = "search2" Then
search2(": {0}", word)
i += 1
End If
etc., etc., etc.
NextIt is giving me the error, "search1 as string - Expression is not a method."
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
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
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 TryDo 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
I'm not sure why but I'm not getting an error message. But I've looked and what I've attempted to write isn't there. I'll go back and step through it to see if I can find something and report back if needed.
My appologies for using the community forum. However the site has changed since the last time I used it (a couple of years ago) and I'm not sure how to direct inquiries to my fellow members here.
Interesting thought! I may have to consider something like that. That actually makes a lot of sense.
Don
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 …@rproffitt,
I think I've learned my answer. For me, at 60, it isn't realistic to consider a career in programming. I wish I had pursued it 10 or 20 years ago. I do love the creativity and problem solving required to build computer applications. I do know that at this juncture I don't have the knowledge and experience to do it. Meaningful courses to do it would take more years than I have to give. I need to consider a "Plan B".
Thanks for everyone's thoughts. It is greatly appreciated.
Don
You've answered my question with your comments. Ultimately I wanted to know if it was realistic for someone my age to attempt a new career in programming. If my age is going to stand in the way, I'm not interested in investing $10,000 and 6 months of time to begin to gain the skills needed.
It's frustrating to know this. I feel like us "old guys" come with lots of real-world experience, maturity and work ethic that the millennials don't seem to possess.
Dani,
I am not opposed in any way to remain as a data analyst. However it seems that in my area of the country (Atlanta, Georgia), my age is standing in the way. It's just that I have a natural interest in programming. Thus the reason why I'm interested in exploring ways to get in - even as an entry-level programmer.
Thanks again for your questions. You're the best!
Don
Dani,
The vast majority of my IT experience is with databases. Of the many hats that I wore in my career field (tile and stone distribution), one of those was querying the database to write and create reports to futher analyse the customers, the inventory and sales. I began doing it full time when I moved to the Hospitality industry, albeit that was comparatively short-lived (only 3 years). Given that I'm being forced to find new employement, I'd prefer to do something that is challenging, makes me think, has some degree of creativity and is geared for business. With that said, I've found that programming meets that criteria given the limited amount of professional experience that I've had. That experience includes several apps developed in VB.net that automated some repetitive tasks done by humans. There also were multiple Excel projects that required a fair amount of VBA behind them that eliminated a lot of manual/repetitive tasks done by the user. I say this to illustrate how I learned I wanted to do more with programming.
Given what you've said, it seems that I need some kind of employement counselor that could look at my specific abilities and experience that could direct me in what direction to go with training and education. Is there such a group of professionals that offer that kind of service? If so, how would I find those?
Thanks you greatly for your information. It was quite helpful. If you have other questions for me, don't …
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
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 SubI hope someone else can use this method.
Don
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 SubThe "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 - …
Close this post.
The problem turned out to be that the database wasn't setup correctly. I've since fixed that and have had no problem connecting.
@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.
FYI..... In attempting to fix this, I commented out a few lines to make it more visual. I failed to remove the comment marks before posting this.
I hope this clears any questions you might have.
Don
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 SubIn advance, thanks for your assistance.
Don
@rproffitt,
That was the fix. It worked as it should have.
Again, thanks for the second pair of eyes.
Don
@rproffitt,
Duh..... I see the error - "SEARCH". It should have been "SELECT". I'm not sure why I wrote it that way. I'll change it and test it. I'll let you know what happens.
Thanks for you good pair of eyes!
Don
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, …
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)?
Hello Group!
I've developed an app that takes the data in a text file and moves it to specific columns in an Excel file. My computer has the Excel 2010 installed. So the application works fine with no issues. However we've begun to upgrade computers and software in the office which means we are moving to Office 2016. I'm finding that my application doesn't behave well with Excel 2016. It stops part of the way through and simply won't finish.
The original application was built in Visual Studio 2010. It is using .NET Framework 4.5 and references Microsoft Excel 14.0 Object Library. I have rebuilt the app in Visual Studio 2015 but with the same parameters as listed above.
My question is: Are you aware of any compatibility issues with VB.net and the various versions of Excel and, if so, what should I being doing to address this?
I hope I'm explain and asking questions correctly. Therefore, please don't hesitate to ask any question that I (hopefully) can clear up.
In advance, thanks for your input.
Don
@ 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
@Minimalist, when you say "if others are also using excel , their processes will also be killed.", are you meaning that if the user has other instances of Excel open, those will be killed too? Or do you mean other users on our network will have their instances of Excel killed?
The latter doesn't seem right, but I can see where any instance of excel could be killed if others are open.
I did read through the link earlier today but didn't attempt anything. If it's only going to kill all the Excel processes on my computer, I'm good with that.
Don
@rproffitt, To clarify, because as the app ran I noticed the computer speed degrading. So I opened the task manager and saw many many instances of Excel open (via the "Processes" tab. That's when I knew Excel wasn't completely closing the process. This tool was built in Visual Studio 2015.
If you have ideas, don't hesitate to offer it. I'll try anything to get these processes to shut down.
Thanks for your interest.
Don
@ Reverend Jim,
I've gone back to working on this with no success. I've done a lot of reading and all offer various ideas. I've also tried your method of closing the instances with no success. Here is my latest updates:
Dim xlWorkBook As Excel.Workbook
Dim xlWorkSheet As Excel.Worksheet
Dim xlRange As Excel.Range
' Start Excel and get Application object.
Dim xlApp As New Excel.Application
xlApp.Visible = False
' Add a new workbook.
xlWorkBook = xlApp.Workbooks.Add
xlWorkSheet = CType(xlWorkBook.ActiveSheet, Worksheet)
' Run the rest of the routine
'Closing all the instances of Excel:
xlApp.DisplayAlerts = False
xlWorkBook.SaveAs(newExlName, FileFormat:=51, CreateBackup:=False)
xlWorkBook.SaveAs(stdExlName, FileFormat:=51, CreateBackup:=False)
xlApp.DisplayAlerts = True
xlWorkBook.Close(newExlName)
xlWorkBook.Close(stdExlName)
xlApp.Quit()
releaseObject(xlWorkSheet)
releaseObject(xlWorkBook)
releaseObject(xlApp)
xlRange = Nothing
xlWorkSheet = Nothing
xlWorkBook = Nothing
xlApp = Nothing
End Sub
Private Sub releaseObject(ByVal obj As Object)
'Release an automation object
Try
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
obj = Nothing
Catch ex As Exception
obj = Nothing
Finally
GC.Collect()
End Try
End SubI cannot get these instances of Excel to close after numerous changes and attempts. Do you or any others have some good ideas? Are there some commands to kill any open Excel process that is open?
Needless to say, with all of these Excel instances open, it really slows my computer down. Any help will be appreciated.
Don
I can't remember if this was an imported project or not. It may have been. But I like Rev. Jim's idea. I've done this before. So I think I'll do it here. It won't take too long. Thanksfully it's not a billion lines of code with 100 forms.
Thanks group. I'll chalk this one up to experience.
Don
@rproffitt,
When you say to change the name via the command line, where is this found?
For the record, I can see the Restran Solutions.pdb file listed in the \bin\Debug and \bin\Release folders in the Solutions Explorer. It's also found in the \obj\x86\Debug\TempPE folder.
I'm really scratching my head. This is the first time I've run into this issue.
Don
@yvrej17,
I found how to "Clean" the solution. Unfortunately it did not work. I'm still getting the error message : "Could not find file 'bin\Release\Restran Solutions.pdb'."
(Restran Solutions C:\Program Files (x86)\MSBuild\14.0\bin\Microsoft.Common.CurrentVersion.targets 3506 5)
Any ideas on how to fix this?
Thanks for your help.
Don
yvrej17, I'm not sure what you mean by "clean" the build. The reason I'm getting the error message as I've attempted to build the project. However it isn't building because it's not finding the debug file. Can you explain further?
Thanks,
Don
Hi group,
I'm making a handful of changes to an existing program I wrote a year or so ago. For some reason I can't get it to build as I'm getting an error that says:
1> Copying file from "obj\x86\Debug\Restran Solutions.exe" to "bin\Debug\app.publish\Restran Solutions.exe".
1>C:\Program Files (x86)\MSBuild\14.0\bin\Microsoft.Common.CurrentVersion.targets(3506,5): error MSB3113: Could not find file 'bin\Release\Restran Solutions.pdb'.
1>
1>Build FAILED.
I did look to see if that file path exists and it does not. Is this something that needs to be created? How to I go about fixing this?
In advance, thanks for your help.
Don
Oracle has referred me to Microsoft. They say it's a Visual Studio issue.
:(
Don
@jwshepard,
My apologies for the delay in getting back to you. I do appreciate the code snippet. Unfortunately I can't tell if it's working or not. I am getting a couple of errors in my output box. These are:
1) Exception thrown: 'System.ArgumentException' in Oracle.DataAccess.dll
2) Exception thrown: 'System.NullReferenceException' in OracleConnections.exe
I'm researching these now in an effort to fix this. If you know something about setting up the Oracle Client Database, feel free to chime in. Currently I'm lost (LOL... sort of).
Again, thanks for your assistance.
Hi group,
I'm discovering that my Excel spreadsheets that are being created by a VB.net app I've written isn't completely closing them as they should be. I'm struggling to understand why and how to fix this. Here is the code for the portion of the app that creates the workbook, inserts the data and closes the workbook (or it should be). Do you see some commands that I may be missing (they are at the bottom of the code)?
xlApp = New Excel.Application
xlApp.Visible = False
' Add a new workbook.
xlWorkBook = xlApp.Workbooks.Add
xlWorkSheet = CType(xlWorkBook.ActiveSheet, Worksheet)
If System.IO.File.Exists(DASRPTFileName) = True Then
System.IO.File.Delete(DASRPTFileName)
End If
rowXl = 1
Dim DASDay As String = ""
Dim DASMonth As String = ""
Dim DASYear As String = ""
Dim DASDateTest As String = ""
Dim DASDateSerial As Double = 0
Dim DASDate As Date
Dim propID As String
Dim headers() As String = {"Prop No/Date", "Non-Guaranteed", "Guaranteed", "Stayovers", "Departures", "Adult", "Youth", "Children", "Sold", "OOO", "Off", "Not Sold", "Occ %", "Proj Revenue", "Avg Rate"}
For col As Integer = 0 To UBound(headers)
xlWorkSheet.Cells(1, col + 1) = headers(col)
Next
xlRange = xlWorkSheet.Range("A1", "O1")
xlRange.WrapText = True
xlRange.Interior.Color = Color.LightGray
xlRange.HorizontalAlignment = -4108
rowXl = rowXl + 1
Dim objReader3 As New System.IO.StreamReader(DASRPT14Day)
Do While objReader3.Peek() <> -1
txtLine = objReader3.ReadLine()
' first convert date into Month, Day and Year (ex June 1, 2016)
propID = Trim(Microsoft.VisualBasic.Left(txtLine, 5))
DASDay = Microsoft.VisualBasic.Mid(txtLine, 7, 2)
DASMonth = Microsoft.VisualBasic.Mid(txtLine, 10, 3)
DASYear = Microsoft.VisualBasic.Mid(txtLine, 14, …Group,
I've written what I believe is fairly simple code to populate a combobox via data from an Oracle Database. Unfortunately it isn't working and the error in the output box says: "Exception thrown: 'System.BadImageFormatException' in OracleConnections.exe" (the name I've assigned to the program is "OracleConnections"). This is not an error I've seen before. But this is also the first time I've connected to a server.
In reading I see that this could be an issue of a 32bit version vs. a 64bit version. I'm using Visual Studio Express 2015 to build this in. I don't know if there are different "bit" versions of VS 2015. But none of my 32 bit versions of Excel with SQL work in my 64 bit version of Office.
Here's the output code I'm getting. Hopefully this will help you explain to me what's happening"
'OracleConnections.vshost.exe' (CLR v4.0.30319: OracleConnections.vshost.exe): Loaded 'C:\Windows\Microsoft.Net\assembly\GAC_32\mscorlib\v4.0_4.0.0.0__b77a5c561934e089\mscorlib.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'OracleConnections.vshost.exe' (CLR v4.0.30319: OracleConnections.vshost.exe): Loaded 'C:\Windows\assembly\GAC_MSIL\Microsoft.VisualStudio.HostingProcess.Utilities\14.0.0.0__b03f5f7f11d50a3a\Microsoft.VisualStudio.HostingProcess.Utilities.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'OracleConnections.vshost.exe' (CLR v4.0.30319: OracleConnections.vshost.exe): Loaded 'C:\Windows\Microsoft.Net\assembly\GAC_MSIL\System.Windows.Forms\v4.0_4.0.0.0__b77a5c561934e089\System.Windows.Forms.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'OracleConnections.vshost.exe' (CLR v4.0.30319: OracleConnections.vshost.exe): Loaded 'C:\Windows\Microsoft.Net\assembly\GAC_MSIL\System\v4.0_4.0.0.0__b77a5c561934e089\System.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'OracleConnections.vshost.exe' (CLR v4.0.30319: OracleConnections.vshost.exe): Loaded 'C:\Windows\Microsoft.Net\assembly\GAC_MSIL\System.Drawing\v4.0_4.0.0.0__b03f5f7f11d50a3a\System.Drawing.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'OracleConnections.vshost.exe' (CLR v4.0.30319: OracleConnections.vshost.exe): …I did find the solution on my own. I kept trying different things. It all came down to choosing "<other>" and loading the Oracle OLE DB.
Hi group,
I've looked all over the internet to find my solution without luck. Hopefully you can help.
I need to add an Oracle server to my data sources in Visual Studio (if it matters, I'm using VB.net). Per the best instructions I've found so far, I've selected "Data Sources" and have chosen to "Add New Data Source". I've chosen "Database" as to where the application will get data from. "Dataset" is the database model chosen. When trying to add the new connection, my choices are "Microsoft Access Database File", "Microsoft SQL Server", "Microsoft SQL Server Database File" and "<other>". I do not see a choice for an Oracle Database. I believe that is what I need to add. I do have a drop down box for the "Data provider". In that it give options for .Net framework providers in SQL and OLE DB. Again, nothing for Oracle. Clearly I must be doing something wrong. But what? Can you help??
In advance, thanks for all you do.
Don
ddanbe,
I'm begining to think that part of the issue is my connection string. While I did get it from our database team, I've either entered it wrong or I simply don't know how to connect to it correctly. If you're familiar with these things, here's what I've written:
Try
Dim conn As New Oracle.DataAccess.Client.OracleConnection("DWHPRD_ADHOC=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=comp.corp.star)(PORT=1521))(CONNECT_DATA=(SERVER=dedicated)(SERVICE_NAME=ADHOC))) ; User ID=xxx;Password=xxx;")
conn.Open()
Dim sql As String = "select dw01.RP_ID" _
& "from donacwil.RATE_PLANS_SSB dw01" _
& "where dw01.PROP_ID in ('" & propID & "')" _
& "And dw01.DW_RCRD_STS_CD in ('A')" _
& "Order by dw01.RP_ID"
Dim cmd As New Oracle.DataAccess.Client.OracleCommand
cmd.Connection = conn
cmd.CommandText = sql
cmd.ExecuteNonQuery()
Dim dr As Oracle.DataAccess.Client.OracleDataReader = cmd.ExecuteReader()
dr.Read()
Debug.Print(dr.Read)
While (dr.Read())
Debug.Print(dr.GetString(0))
cmbxRatePlans.Items.Add(dr.GetString(0))
End While
conn.Close()
conn = Nothing
Catch ex As Exception
Throw ex
End TryThe first thing I'd really like to do is to ensure I'm connecting correctly and the query is being run. I've attempted a "Debug.Print" to see if I'm getting any data, but I've got nothing to return to the output box. Of course it may be that I've got a wrong connection string.
I've been up and down Google to find an answer. I had found several prior to creating this thread but nothing has worked. The above is my latest iteration. Thoughts?
As always, thanks for your help.
Don
Hi Group,
I'm attempting to pull info from our database to populate a combo-box in VB.net (Visual Studio Express 2015). Here's my first attempt at the connection:
Dim conn As New Oracle.DataAccess.Client.OracleConnection()
conn.ConnectionString = oraDBFrom here I'm not sure how to pull the data into the combobox. I do have my code written to find the information:
Dim sql As String = "select dw01.RP_ID" _
& "from donacwil.RATE_PLANS_SSB dw01" _
& "where dw01.PROP_ID in ('" & propID & "')" _
& "And dw01.DW_RCRD_STS_CD in ('A')" _
& "Order by dw01.RP_ID"Does anyone know what to do from here?
Thanks for your help.
Don
Hi Group,
I've imported an existing form into a new project that I'm creating (using Visual Studio 2015 Express/VB.net). When displaying the "Design" version (the user interface), it looks like a plain form and doesn't resemble the actual form in the original program.
As per the directions, I added the existing item through the Solutions Explorer (Add > "Existing Item"). I added the
programName.Designer.vb
programName.resx
programName.vb
Where did I go wrong? What am I missing?
In advance, thanks for your help.
Don
Group,
I get the error when I try to build the project. The Errors List says, "CA2213 (the warning code) Main contains filed Main.objwriter2 that is of IDisposable type StreamWriter. Change the Dispose method on Main to call Dispose or Close on this field."
When you click the error message, the following code is highlighted in yellow:Protected Overrides Sub Dispose(ByVal disposing as Boolean)
This is found on the Main.Designer.vb tab. The remaining code is as follows:
Try
If disposing AndAlso components IsNot Nothing Then
components.Dispose()
End If
Finally
MyBase.Dispose(disposing)
End Try
End Sub
I'm confused as to what it's trying to get me to do. I have found that the text file (called "logFile") that "objwriter2" is trying to write to is "being used by another application", which should be false. This file is on my computer and is not open or being used by anything else.
For the time being, I've removed objwriter3 from the program which allows it to run fine. But I do want to put it back in. Any thoughts as to what I need to do?
Don
Good Morning group!
I've made some minor changes to my coding (to open a log file, write to it and then close it) and now I'm getting an error that I've not seen before. The error code is CA2213. I've read what it says and I've tried to look it up online, but I don't seem to grasp what it means. Hopefully you can help.
When I double click the error, the yellowed out line is:Protected Overrides Sub Dispose(ByVal disposing As Boolean)
The remaining code says:
Try
If disposing AndAlso components IsNot Nothing Then
components.Dispose()
End If
Finally
MyBase.Dispose(disposing)
End Try
End Sub
The error message say to "Change Dispose Method to call Close or Dispose on this field". Where is this change supposed to be?
In advance, thanks for your help.
Don
Rev Jim,
Brilliant. I see also I wasn't reading the text file to append it to the other file.
Thanks for the help. This is awesome!
Don
Rev. Jim, do you think it would be a problem to do this? The program runs Monday thru Friday. During the week there is only one file to update. Monday morning is the only day (generally) that there are multiple file to merge. FYI... I'm all for simple!
Thanks again for your input.
Don
@rproffitt and @ddanbe, you prompt me to ask a couple of questions:
Line 20 is saying (I hope) that If i = 0. "i" should be the first file in the folder. I want that first file saved as "restranSave" (a path). Line 27 is the Elseto line 20. In other words, "If i > 0" (meaning the 2nd, 3rd, etc. file in the folder) then I want to append this the the "restranSave" file path. Is that NOT what I'm doing? Should I be coding this different?
I will eliminate the "Dispose" and just stick with "Close" at 23 and 29.
As always, thanks for your help.
Don