Comments by Member 11856456

Member 11856456 23-Feb-23 21:02pm View

Thanks, I cant tell any speed difference, however, this did make it more efficient on memory consumption. So, still a win in my book.

Member 11856456 29-Nov-22 7:05am View

Thank you for the help

Member 11856456 25-Nov-22 6:29am View

thanks this really helps out.

Member 11856456 25-Nov-22 6:15am View

Richard, It is not finding the module finance. Dim moduleType As Type = Type.GetType(finance) I am using finance as a string variable. is this supposed to be something else? Maybe a specific object, or should a string variable work?

Member 11856456 22-Nov-22 10:25am View

I am still at a loss, I even have looked into linq to help solve the issue. But I cant figure out how to code the linq query correctly.

the query has to be set up so that SSN or Individual_ID is sorted first, the list of(EMployee, Spouse, Partner, Child) is second, then last_name, and finally first_name.

Dim anyHasValue = Demographic_data_view.Rows.
OfType(Of DataGridViewRow).
Where(Function(r) Not r.IsNewRow AndAlso r.Cells.
OfType(Of DataGridViewTextBoxCell).
Any(Function(c) c.OwningColumn.Index = col AndAlso
If(c.Value, String.Empty).ToString().Trim().Length > 0)).
Any()

'this line of code allows you to sort a range of columns that contain the data
If anyHasValue = True Then
sort_str = "Employee_SSN ASC, "
'sort_str = "Employee_SSN"
Else
'Sort by individual ID column
sort_str = "Individual_ID ASC, "
'sort_str = "Individual_ID"
End If

'.SortFields.Add Key:=Range("H10:H" & vRowc), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:="Employee,Spouse,Child,"

'the excel sortfilds will be converted over to vb.net as text values incorportated into the sort_str variable
dt.DefaultView.Sort = sort_str + " Last_name ASC, First_name ASC, Benefit_type ASC"
dt = dt.DefaultView.ToTable()

'Dim order As List(Of String) = New List(Of String)() From {
' "Employee",
' "Spouse",
' "Partner",
' "Child"
'}
'Dim query = From item In order Join row In individual.dt.AsEnumerable() On item Equals row.Field(Of String)("Relationship") Select row
'Dim result = query.CopyToDataTable()
'Demographic_data_view.DataSource = result

Member 11856456 19-Nov-22 13:14pm View

These are the same types of articles I have already read. These do not help with custom sorts with mulitple values in 1 column.

Member 11856456 5-Aug-21 6:19am View

Richard, I noticed that during the async memory usage goes up pretty rapidly, how can I slow the progression or keep it more steady? When I was using parallel.for I was able to keep the memory consumption to about 45 MB, using async for instance of 1000 records jumps up to about 520 MB.

Member 11856456 31-Jul-21 12:04pm View

For anyone wanting to know reading this post, I used the first example in solution 2. it actually ran pretty quickly, and it allowed me to do parts at a time so I didn't have any memory issues. Thank you Richard!!!

Member 11856456 23-May-21 3:53am View

Griff, its not 100% its bringing in records that seem to have no match please see the example:

Last name First name Middle name Verify or Proof code Death month Death day Death year
LEE PEARL 09 00 1975
MARTIN MICHAEL 10 00 1974
SWEET HELEN 07 00 1979
WARD GEORGE 10 00 1982
SMITH JOHN M V 06 15 1967
PEARSON ARTHUR 07 00 1987
PEARSON ARTHUR 07 00 1987

As you can see the last 2 matched but the ones above did not have a match.

code:

SELECT a.* FROM ssdm a
JOIN (SELECT [First name]
,[Last name]
,[Death month]
,[Death day]
,[Death year]
FROM ssdm
GROUP BY [First name]
,[Last name]
,[Death month]
,[Death day]
,[Death year]
HAVING COUNT(*) > 1) b
ON a.[First name] = b.[First name]
AND a.[Last name] = b.[Last name]
AND a.[Death month] = b.[Death month]
and a.[Death day] = b.[Death day]
and a.[Death year] = b.[Death year]

Member 11856456 30-Aug-20 12:10pm View

thank you, great information!

Member 11856456 30-Aug-20 11:48am View

I see, it creates 2,000,000 because the same data in the database plus the datatable I have filled. I also should have explained that the datatable that is filled is only in memory and not being shown. its just to be compared against in the background when looking for matching data when someone imports an excel file. I will combine your information with what I received from Dave. Thank you both!

Member 11856456 30-Aug-20 0:04am View

I appreciate it. thank you!

Member 11856456 29-Aug-20 23:52pm View

is there a good article where I can see how this is done? I googled single query to multiple queries in vb.net, or sql query into batches in vb.net. not much documentation on this.

Member 11856456 29-Aug-20 23:46pm View

comparisons, it made sense to me to have everything available to make comparisons when a match is found I could update if new information was available.

Member 11856456 29-Aug-20 23:38pm View

How do I "You would have to split your query into multiple queries, each returning it's own set of records" I have only done one query at a time. This could be a good learning experience for me. So instead of the 1,000,000 in memory how do I do your method?

Member 11856456 29-Aug-20 23:29pm View

is there a place to get an example on how to do this. I do not mind to change the design and code on what I am doing now. I figured the way I was doing it was not the best way but it was the only way I could figure out how to compare against the data I have.

Member 11856456 16-Jun-20 15:57pm View

I have updated the information including the sections asked about. Hopefully, this will give some better clarification.

Member 11856456 15-Jun-20 19:47pm View

I appreciate the post, could you describe what would be needed to help give clarification? I will do my best to update if I knew what was needed.

Member 11856456 9-Jun-20 19:16pm View

Griff, been awhile. Thank you for the response, this is a very interesting way to achieve what i am looking for. I really appreciate the help as i was stuck.

Member 11856456 9-Jun-20 19:15pm View

if there is something better than table adapters I would like to know about it that way I can expand my knowledge. Also, I am loading them all so I can find an update the row found in my match statement. I am doing what I can to increase the speed of my match statement code. I appreciate any help.

Member 11856456 18-Mar-20 19:40pm View

Thank you Richard, I retrieved code to remove hidden special characters and to remove line breaks, and guess what? It worked!!!! Thank you for the idea, I thought something else might be going on.

Member 11856456 16-Mar-20 16:25pm View

Hey Griff, if I individually look for the social in each table I can find it. Is it possible that having similar but not the same table structure may be influencing these results.

allcalssns:
Last_name First_name Middle_name Month_of_death Day_of_death Year_of_death County_of_death sex ssn Month_of_birth Day_of_birth Year_of_birth Place_of_birth Race Death_certificate_number Cause_of_death Suffix Place_of_death State_of_death

SSdm:
SSN Last name Suffix First name Middle name Verify or Proof code Death month Death day Death year Birth month Birth day Birth year State or Country code of Residence Zip code of last residence Zip code lump sum payment State_of_death State_of_birth County_of_death Sex

Also when i cut and paste into excel it would seem that some of my column values are thrown to the second line. Not sure if that means anything or if excel just does that.

Member 11856456 28-Dec-19 22:25pm View

What would you recommend for someone who wants to implement their code across multiple cores to decrease the time of their processing?

Member 11856456 23-Dec-19 8:30am View

I changed you code up a little bit because I have multiple tables already built.

Dim d As New DataSet
Profileadaptor.Fill(profiledt)
profiledt.TableName = "Profile"
d.Tables.Add(profiledt)

works the way I need it too. thanyou Maciej Los for the recomendation of using datasets to incorporate using a table/tables by string name.

Member 11856456 17-Aug-19 12:44pm View

Dave I have rearranged some of the code outside the parallel.for. the stuff above the for statement is now above the parallel.for and the stuff after the I2 iteration is under the parallel for. after trying this out the code looked like it was running correctly. However, because it is out of sequence, 1,2,3,4,5 the match statement I have does not work. I was hoping to keep that specific order and just send to individual threads and then write to the table. is there a way to accomplish this? Please keep in mind this is my first attempt at doing anything parallel.

Member 11856456 17-Aug-19 0:13am View

I have updated the question, all I care about is how to skip over this section:
SyncLock lock
If updatevalue = "yes" Then
UpdateModule.update_all_tables()
End If
End SyncLock

SyncLock lock
If insertvalue = "Yes" Then
Insertmodule.insert_all()
End If
End SyncLock
' end_diagnostic("match")

' MessageBox.Show("table 1: " & countI & " table 2: " & counti2)
SyncLock lock
Manualimport.Show()
End SyncLock

until the last parallel for iteration

Member 11856456 11-Aug-19 15:09pm View

Hey Dave, I tried to use this the way you have suggested and I tried to place this in other portions of my code and I still could not get this to work. I think I am missing something. But I wanted to try a couple things before I posted back.

Member 11856456 11-Aug-19 11:47am View

Dave, I tried this but its saying "value cannot be null" there is a green line under the lock object, what should be tied to this object?

Member 11856456 11-Aug-19 11:29am View

I have been reading about synchronization, however, I can't find any good examples to she how to do this with datatables. From what I am gathering the reading and the comparing is fine. What is the best approach in these types of situations?

Member 11856456 9-Aug-19 16:30pm View

Richard, I am going to give this a whirl. Can you please explain what you mean by number of items to return. Can you give an example. and I think you are right I should switch to a parallel.for statement instead, is the set up of the example you have given the same?

Member 11856456 4-Aug-19 18:57pm View

yes,
Private Sub process(i As Long)
end sub

I changed the private to public and got a new message:
System.MissingMemberException: 'Overload resolution failed because no accessible 'process' accepts this number of arguments.'

Member 11856456 20-Apr-19 10:48am View

I originally had an Identity(1,1) with autoincrement. However, I removed this and started over. I did not realize that during this process I forgot to make certain tables with PK. So, for these tables I was looking to insert the primary key dynamically.

Member 11856456 20-Jan-19 22:33pm View

Interesting way to tackle the problem, only issue is this only assumes the row index of table 1 but not the other tables. Is there a way to call forth which row was used for each table.

For instance

birth table record 1 would be row 1, but the information for the other tables are different. Death index for this individual may be on row 5 and the cemetery may be on row 10. it is all dependent on what records were found first and then inserted into the database.

Member 11856456 3-Oct-18 23:56pm View

Richard, this was a great educational tool and gave me a better understanding of why the full name should be included in one single column. I will be changing up a few things including this. However, I would still like to be able to filter by first and last name if possible from a single column. I think its possible to do this by the space in between each portion of the name. How would I go about this process from a single column?

Member 11856456 30-Sep-18 0:17am View

It is interesting. Thanks for the advice, it gives me a place to start.

Member 11856456 30-Sep-18 0:10am View

CONCENTATE is a Function in specific microsoft products (excel, access, and sql), However, I do not see this word anywhere else.

Member 11856456 29-Sep-18 23:48pm View

so you are saying create a first, middle, and last columns with a 4th column being a fullname which would be the concentated values of the first 3 columns?

Member 11856456 29-Sep-18 22:31pm View

I want to say thanks, I adjusted my initial table to structure from the separated values to one single column, which is the birth_date, I will be doing the same thing for death. One of the other issues I have was just searching by year if need be, but I found a way to modify the original code to make it do exactly what I wanted. Again, thanks for the help!

Member 11856456 29-Sep-18 4:58am View

What happens when you have an unknown date or if the date is missing values. For example, 10/--/2017, here we know the month and the year of the event, but not the day. Same can be said of the remaining fields. Maybe someone died from a long time ago and all you can read on the headstone is a portion of the date. would I still be able to use a date column for these cases?

Member 11856456 22-Sep-18 14:19pm View

Sorry for the confusion. I have separated the deaths because everyone in my database not only people who have died, the database also includes living relatives. If the information only included dead individuals I would have designed one table to include that data also. I originally was using the inner join. However, if the information has only the date of death I get 0 results and it leaves me with a blank datagrid. Where you have personID I only want that name to show up 1 time and the rest will be handled on another form.

Member 11856456 19-Sep-18 12:37pm View

I revised my question and included more plus I added my query.

Member 11856456 19-Sep-18 11:57am View

Yeah, I had to change the statement to a left join because not everything was connected at the time and when you use an inner join it looks for exact matches. However, that did help me understand where I went wrong in writing my join statements.

Member 11856456 19-Sep-18 11:57am View

Deleted

Yeah, I had to change the statement to a left join because not everything was connected at the time and when you use an inner join it looks for exact matches. However, that did help me understand where I went wrong in writing my join statements.

Member 11856456 14-Sep-18 14:10pm View

actually watching a video on a 3 table inner join brought me to an access video. I followed the example and it worked, but could not get this to run with a 4 table. However, the example above helped and I see where my mistake was.

Member 11856456 13-Sep-18 23:40pm View

this is for a 3 table, but what about for a 4 table join?

Member 11856456 10-Sep-18 17:19pm View

You're welcome, Thanks again.

Member 11856456 10-Sep-18 16:43pm View

Hey Alek, yeah originally when I ran the code the list of items would be on their individual lines with no blank lines. I should have said that in the orignal. I appreciate your approach to the problem.

Member 11856456 10-Sep-18 15:30pm View

Alek your code enhancements to my code were great, the only issue is that the output into the richtextbox has blank lines. there should be no blank lines in the richtextbox. I tried maneuvering some of the code around, but I am still getting the blank lines.

Member 11856456 10-Sep-18 10:29am View

it compares what I have in my datatable to the lines of the document. What I would like to do is have a way to use the database information and have it fill in the .replace method.

I just dont know how to make the code look like this:
regstr.Replace("text", "text").Replace("text", "text").Replace("text", "text"). 'etc

where it automatically will add the .Replace("text", "text") for every datatable entry that matche.

Member 11856456 6-Sep-18 14:35pm View

Is there a way I can post my database diagram? or possibly send it to you? that way you can see what I am trying to accomplish.

Member 11856456 6-Sep-18 12:44pm View

The cemetery_reference is the name of the cemetery and the location. The other is the individuals buried within the cemetery and their locations. Also, the reference_cemetery table is set up where no duplicates of that cemetery can be entered.

Member 11856456 5-Sep-18 11:18am View

This is a vb.net 2017 application.

Member 11856456 31-Aug-18 12:01pm View

I have changed my code, I have now included a parameterized statement. Please review the code above. However, I do not understand the cached query plan. Can you give me an example?

Member 11856456 31-Aug-18 11:47am View

I have no foreign keys set up on any table, mainly because people have to work around them. Would people still be able to use an SQL injection since the code requires selection of rows and nothing to be typed in?

Member 11856456 28-Aug-18 10:16am View

The only issue with this is it requires that the source already has an Identity. What I am trying to do as I import the information into the tables, they will use the identity column of my main table. It would be a 1 to 1 insert. So, if the new row in the main table column is 4000 then the individual_ID corresponding to the other tables should be 4000. Then the next import 4001 and so on.

As it stands only my target tables have the ID and individual_ID columns. my table called profile is the main table which all tables will be referenced from. how can I place the ID value into each table that it needs to be inserted into?

Member 11856456 24-Aug-18 13:30pm View

content moved to the question in [EDIT After solution 1]

Member 11856456 15-Aug-18 16:39pm View

On my main table I want to use the autoincremented ID column to be the reference to the other tables.

1. ID, First_name, Last_name, DOB
2. ID, Job_title, pay, Idividual_id
3. ID, Time_of_employeement, Individual_ID

So, the Invidiual_ID column of table 2 if there is information related to that person should contain the number of the ID column of the main table, table 1. same with the 3rd table. I am importing data into these tables, but I want to make sure that they reference the right person.

Member 11856456 15-Aug-18 16:12pm View

I have 5 tables for now. each table has an individual_ID column that is used for referring to the ID column of my main table. When I click on the name in datagridview I use the ID to get all records related to this ID from other tables.

Tables:
1. ID, First_name, Last_name, DOB
2. ID, Job_title, pay, Idividual_id
3. ID, Time_of_employeement, Individual_ID
just to give you an idea

Member 11856456 15-Aug-18 13:09pm View

I already Have an autoincrement ID column, but what I am doing is splitting a data table that was made by an excel sheet. So What I am hoping to accomplish is having that row number transfer to another table while I am using the insert into select statement. The hope is to have a way to make sure that the other tables are able to be referenced using the ID that from the main table. Any suggestions on an easier way to accomplish this?

Member 11856456 13-Aug-18 17:58pm View

I completely understand I am trying to come up with a decent method that will not use a ton of memory. As it stands I can use a bulk copy statement to pull into one table, is it possible to split that data up to other tables?

Member 11856456 13-Aug-18 17:56pm View

I just use a simple SQL query to connect to the database. But The reason I was asking this question is I thought that if I had all the table names I could run a for each statement and send data that was relevant to each table to split the information.

Member 11856456 4-Aug-18 9:43am View

Sorry, I realized after your question that I duplicated the columns. I have updated the question.

Member 11856456 4-Aug-18 9:11am View

I was giving an example of what I am trying to accomplish. I am trying to make a match statement without using the match and merge. I am trying to achieve this because match and merge will not insert in a third table which is what I need. As for the columns I want to find equivalencies between tables A and B and the information that is not filled in I want to update that information from table B.

Member 11856456 11-Jul-18 13:31pm View

I see that the code is using the source information for the insert, but where is it being sent to for the output? Can I specify a table for the out put and can I do this on a when matched statement?

Member 11856456 27-Jun-18 21:42pm View

Just posted the code.

Member 11856456 18-May-18 9:20am View

Are you running it in a 64x environment? Because that is where I am having my issue. It will not let me drag and drop this control or any control that I have created in listview if my build is 64x. I am not understanding why that would be a problem, because the initial listview that already comes in the toolbox can be used in a 64x program.

Member 11856456 17-May-18 18:12pm View

I have additional code that will be added to it. I am just wanting to have my own version of the listview control. It will have its own functionality, which is mouseclick that embeds a textbox into the listview subitem and then once I am done editing the cell I can hit enter and got to the next subitem and do the same thing. The problem is when I go to make my own listview, it never shows up and I get that error mentioned above. So, I figured that I was missing something in the initial code that would make it appear, or contain it.

Member 11856456 14-May-18 19:51pm View

Christian, This portion of code will be in a keypress event that is initiated by pressing enter. As of right now, it will go through the items but gets the error mentioned up top once it reaches past the max index number.

Member 11856456 14-May-18 8:16am View

No, and yes. My program is built in a 64-bit environment. However, when making a class to use as a control, the control only works in a 32-bit environment. If I try to place it on a form in my program it keeps disappearing and keeps getting removed from the toolbox. I thought I removed all 32-bit components of the code, but it still only works with a 32-bit environment.

Member 11856456 11-May-18 9:59am View

it's not embedding the control like the WM_PAINT was doing. This should have worked as an equivalent.

Member 11856456 11-May-18 0:41am View

I had seen this post originally and that is what gave me the idea to use a paint event instead. However, I am still having issues with how to approach what should be in the case portion.

Protected Overrides Sub OnPaint(ByVal e As PaintEventArgs)
Dim blackPen As New Pen(Color.Black, 3)

Select Case e.Graphics
Case
Dim rc As Rectangle
For Each ec As EmbeddedControl In _embeddedControls
rc = Me.GetSubItemBounds(ec.Item, ec.Column)
ec.Control.Bounds = rc
Next
e.Graphics.DrawRectangle(blackPen, rc.X, rc.Y, rc.Width, rc.Height)
End Select

' Draw rectangle to screen.

MyBase.OnPaint(e)
End Sub

This should hoepfully replace the old method for the 32 bit system. any suggestions?

Member 11856456 10-May-18 22:13pm View

I have eliminated all but 1 one portion that relates back to 32bit. here is the small portion I need a workaround for.

Protected Overloads Overrides Sub WndProc(ByRef m As Message)
Select Case m.Msg
Case WM_PAINT
Dim rc As Rectangle
For Each ec As EmbeddedControl In _embeddedControls
rc = Me.GetSubItemBounds(ec.Item, ec.Column)
ec.Control.Bounds = rc
Next
Exit Select
End Select
MyBase.WndProc(m)
End Sub

I need to remove the paint portion and the m.msg. However, I am unsure how to make a similar protected overload that will be geared to the rest of the code.

Member 11856456 10-May-18 15:10pm View

Found out that changing my class code to a 64x would fix the problem. However, for some odd reason, the code will not work in a 64x environment.

Member 11856456 10-May-18 15:07pm View

I figured our why this is not working, portion of code is based upon an 86x app, the program I am creating is a 64x. I confirmed this by making different builds with the code in a new project. I do not know where I can change the class code to make it usuable in a 64x program.

Member 11856456 10-May-18 11:53am View

Maybe I can use a regular listview. Is there a way to embed and remove embedded controls without creating a custom listview?

Member 11856456 10-May-18 11:44am View

Richard, I dont disagree. However, the goal is to be able to embed and remove controls by clicking on a listview item. However, Since the listview is not appearing I have no idea if the embedding portion of code will run correctly and even if it does I still need a way to remove the embedded control once I am done.

Member 11856456 10-May-18 9:35am View

I posted the additional code that appears once the control is added to the form. I have to apologize because I thought that it was the control code that was interfering with me seeing the items appear during runtime. It is extremely weird because after the program has run the items appear in the control and you can see them when you look at the form before debugging. However, once the debugging process starts the listview is completely blank on the form.

Member 11856456 9-May-18 20:59pm View

I had to revise the code in the question. Found out it's not adding to form because there is something missing in the class code portion. Hopefully, I can get help in understanding why it is not working.

Member 11856456 29-Apr-18 0:12am View

So the way I understand it. have a folder in my application called individuals. Within this folder store other directories for each individual. For instance, d:\myapp\data\Persons\1
d:\myapp\data\Persons\2
d:\myapp\data\Persons\3 ....etc

then create another directory within 1,2,3 called an infofolder where I would place documents into and store their pathways into the database.

Could I just make a d:\myapp\data\Persons\1...etc and store related documents in it and then store the paths into a database?

Member 11856456 27-Apr-18 11:46am View

Thank you very much. I haven't made it that far into programming. However, since I have been on this site I have learned a whole lot. Again thanks for the help in understanding the code provided.

Member 11856456 27-Apr-18 11:31am View

Richard, I could not get the double buffer to work, however, I did find a code that stops flickering. Since you have been in this for awhile, and it's a shortcode, could you explain why this stops flickering?

Member 11856456 26-Apr-18 15:02pm View

I just tried this in 3 different locations of my code and none of them responded to the flickering. maybe I am looking at this wrong?

Member 11856456 26-Apr-18 14:42pm View

This seems like it would solve my problem, the only issue is that it shows how to do this in C#, not vb.net. How would I go about setting this up for a vb.net application?

Member 11856456 21-Apr-18 9:14am View

I had to change one thing in the coding for it to work
changed: Private Sub txb_Leave(sender As Object, e As EventArgs) Handles TextBox1.Leave
to: Private Sub txb_Leave(sender As Object, e As EventArgs) Handles Txb.Leave

The code works great, I really appreciate the help. There was not much difference in the code so it is pretty easy to understand.

Thanks again for the help

Member 11856456 21-Apr-18 2:16am View

Could you give an example. Also, sorry about the code, its not a double click, its only a single click. The first portion says double because thats what I started with, but have changed it since then.

Member 11856456 12-Apr-18 11:09am View

I will try this when I get in, I will just have to convert from C# to vb.net. will vb.net have "out" as part of their choices? You have if (!DateTime.TryParse(start.Text, out startddate)) which has out in it.

Member 11856456 10-Apr-18 15:44pm View

The first one didn't work. however, the second solution worked perfectly. I am a little confused on how it works. Can you please explain to me how this solution works so I can possibly manipulate the solution if I need to. Originally I tried to use the and statement to say in between this and that exclude these, but that didn't work. So I am so interested on why this OR statement works the way I need it to?

Member 11856456 10-Apr-18 15:01pm View

I am getting this error: "The expression contains unsupported operator 'Between'.'"

Member 11856456 5-Apr-18 2:42am View

Winforms

Member 11856456 5-Apr-18 2:35am View

Deleted

win forms

Member 11856456 4-Apr-18 22:41pm View

They are identifiable by text, visual studio will not allow me to use white spaces in the design name. I was hoping to do something close to a for each statement, that would compare all the texts.

Member 11856456 27-Mar-18 11:41am View

Thanks, Richard, I will keep a mental note of this. Thanks again for the help.

Member 11856456 27-Mar-18 10:31am View

Thanks, I could not figure out why I could not just dispose of the information and then remove the image file afterward. So, Never use the Image.fromfile() code, focus on using a stream instead?

Member 11856456 21-Mar-18 3:13am View

I have the newest community version that I am using. As for additional details, I have a databound gridview that I am trying to add to dynamically, that way I see all newly updated data. It gives me the error I stated above.

Member 11856456 19-Feb-18 15:23pm View

Thank you for the advice, I will thoroughly examine your code and see how it works and go from there.

Member 11856456 19-Feb-18 14:31pm View

I think I found out why all specific portions are being distributed to other columns of that row. The separator is : however, some of the values on the other side of the : are blank. This seems to be where the misunderstanding for the translation process is. So, the follow-up question would be how can I make the code understand that a blank value is just a null or empty string for that column? If this question can be answered then it would solve this issue.

Member 11856456 19-Feb-18 12:06pm View

yes, I am trying to get these values into a database. Here are the information and the format that I am using at this time.

Page: a-01-1 & a-01-2
Number: 34
Deceased_name: Wm Aaron
Race: W
Sex: M
Age: 34
Marital_status:
Place_of_birth: DuBois
Occupation:
Date_of_death: Feb 28, 1895
Place_of_death: DuBois
Cause_of_death: Erysiplas
Duration_of_illness: 2 wks
Cemetery: Karthaus cemetery
Date_of_burial: Mar 4, 1895
Fathers_name:
Mothers_name:
Record_date: June 4, 1895

Page: a-05-1 & a-05-2
Number: 10
Deceased_name: Mary Abdenhof
Race: W
Sex: F
Age: 1 mo
Marital_status:
Place_of_birth: Irvona Bor
Occupation:
Date_of_death: 14-Apr-03
Place_of_death: Borough
Cause_of_death: Pneumonia
Duration_of_illness: 1 wk
Cemetery: Hagertys X Road
Date_of_burial: 16-Apr-03
Fathers_name: Edward H Abdenhof
Mothers_name: Catharine Abdenhof
Record_date: 13-May-03

what do you mean by key?

Member 11856456 17-Feb-18 1:49am View

Good news is I was able to get the first one to work that was not StringBuilder. However, I tried the StringBuilder and attempted multiple ways to get it to work and it just would not. None the less my question has been answered. I appreciate your help. Thanks again!

Member 11856456 17-Feb-18 0:58am View

still the same, is there a better approach to this situation?

Member 11856456 17-Feb-18 0:33am View

That didnt work, it kept giving me red lines where the split function or the string.join are.

Member 11856456 17-Feb-18 0:13am View

Just updated it for you.

Member 11856456 16-Feb-18 23:47pm View

When I import multiple text files it only adds one blank line, which is at the top of the richtextbox. I was looking for something that would add a blank line as a separator per text file being imported. If there is a better method to do so I would like to know it. I have not used this portion or vb.net before. So, dealing with lines and text files are a new realm.

Member 11856456 15-Feb-18 13:31pm View

Awesome, I appreciate it. This is exactly what I needed.

Member 11856456 14-Feb-18 23:02pm View

I appreciate the feedback, still trying to get the Richtextbox to hold the text information from multiple text files.

Member 11856456 14-Dec-17 9:16am View

im using the SQL bulkcopy method. I will check this out to see if I can locate something useful in it to help this dilemma.

Member 11856456 14-Dec-17 9:09am View

The where portion works, but why would I need to do any of this if the information being imported already is supposedly empty for certain cells? If I can understand the why maybe I can fix it before importing the data.

Member 11856456 14-Dec-17 8:59am View

So basically I have to go column by column fixing this data instead of doing it all at one time?

Member 11856456 14-Dec-17 8:57am View

I wish I didn't have to do this at all. this problem seems to stem from me importing an excel sheet into my datatable. This information looks like it has empty string values in certain cells, but when I look up the column = '' nothing comes up in the search. However, after I use the statement LTRIM(RTRIM(ISNULL([{0}],''))) = '' then it fixes that problem and it shows the cells as empty strings.

Member 11856456 20-Nov-17 18:53pm View

I figured that knowing how to string the datatable would eventually lead me to it. A t least that is what I am hoping for. Also, I figured it would be easier for people to help me with stringing the datatable columns and rows.

Member 11856456 20-Nov-17 18:40pm View

overall that is the main objective, I have made it further, but its not placing the information into the cells they belong in. How would I go about making it understand that the information needs to be separated into rows and columns?

Member 11856456 20-Nov-17 18:24pm View

I have improved the code in the question, but it still isn't quite where I need it.

Member 11856456 10-Oct-17 13:03pm View

So either way that I tried that you have listed here only seems to give the first result of the sql select statement. I was hoping to fill the combobox with all the results.

I tried to iterate through and force populate, but the problem now is that it repeats the same result.

here is what I am trying to use, maybe you could lead me in the right direction.

For i As Integer = 0 To newdts2.Rows.Count - 1

Dim buslicense As String = dr("business license number").ToString
businesslicensecombobox.Items.Add(buslicense)

Next

Member 11856456 4-Oct-17 1:49am View

What about combining to the bindingsource. Lets say I make multiple statements to make distinct columns, can I take those 4-5 sql statements and put them into 1 bindingsource?

Member 11856456 5-Sep-17 12:45pm View

I am not sure how to query 2 databases at the same time, I am only used to doing one. is there an example somewhere or maybe a quick example you can give that could get me started in the right direction? Did not figure trying to move table information from one database to another would be so hard.

Member 11856456 5-Sep-17 12:30pm View

here is the actual code that I am running based on your suggestion:

Dim tabname As String
tabname = InputBox("Name of your new table", "new table", "", 1, 1)

Dim sqlconn1 As New SqlConnection(connect1)
Dim sqlconn2 As New SqlConnection(connect2)

sqlconn1.Open()
sqlconn2.Open()

Dim cb2 As New StringBuilder("Select * into " + ComboBox2.Text + ".dbo." + "[" + tabname.ToString + "] [IN externaldb] from " + ComboBox1.Text + ".dbo." + "[" + ListBox1.SelectedItem.ToString + "]")

Dim sqladp = New SqlDataAdapter

sqladp.SelectCommand = New SqlCommand(cb2.ToString, sqlconn1)

sqladp.SelectCommand.ExecuteNonQuery()

sqlconn1.Close()
sqlconn2.Close()

End Sub

figured this may help

Member 11856456 5-Sep-17 12:22pm View

could you please give an example as I have not dealt with using 2 databases before. I appreciate the help.

Member 11856456 5-Sep-17 10:41am View

I dont mind to create a new table at this point. How would you use the select into if you are using 2 databases? I dont know how to use 2 connection strings and make the work together in vb.net

Member 11856456 4-Sep-17 21:01pm View

I will switch out the statement. I will try it this way, I am running a dual statement because I am getting information from one table into another database and bringing it over to be used in a secondary database

Member 11856456 31-Aug-17 13:30pm View

I will look more into doing separate connections thanks for the info.

Member 11856456 22-Aug-17 14:32pm View

It still didn't work, so I just decided to put an additional SQL statement in that references back to the table after the new add.

Member 11856456 22-Aug-17 14:02pm View

worked well for my update statement, but not the insert. it first show which ever records I selected and acts like an update statement. When I go and restart the program it shows the new information

Member 11856456 18-Aug-17 14:46pm View

I was just a little off. I used messagebox.show to help me put the string together. Thanks Richard!!

Member 11856456 18-Aug-17 8:58am View

I have yet to venture into creating classes, where do I put the code? is it in form 1?

Member 11856456 2-Aug-17 13:58pm View

I am trying to convert my sql statement into a parameters so that I will not have any chance of sql injections. I want to incorporate the addwithvalue using the input box(ipb).

Member 11856456 27-Jul-17 13:22pm View

So it would be best to use an adaptor over a regular statement? i didn't know that the connections would open and close by themselves. What about previous data accessed, is it disposed of before the new data is accessed?

Member 11856456 24-Jul-17 16:19pm View

Dave, I appreciate the response. Since I am new to coding looking up your "separation of concerns" was something I had to do. Since I do not know a lot about coding do you have any examples of creating what you are talking about or possible examples from other people. All my code is doing is creating a temporary form with a picturebox in it so people can enlarge their image. once they are done they can use the escape key and then it will close that temporary form. If there is a better, cleaner way to do this I would love to learn how.

Member 11856456 24-Jul-17 6:42am View

yeah, I couldnt get an appropriate answer to it. your answer helped on this, but its not 100% what I am looking for. it does allow me to add that picform as withevents which is a step in the right direction. I am still researching this, I have been doing this one problem for about a week. Its extremely frustrating lol, but its a good learning expereince.

Member 11856456 24-Jul-17 5:42am View

Im getting a "Cannot access a disposed object". this is close to what I need problem is when I go to create another form which is picform thats when i get that error.

Member 11856456 24-Jul-17 5:05am View

so I have created this form,

Dim picform As New Form
picform.Controls.Add(control)
picform.Height = 800
picform.Width = 600
' picform.FormBorderStyle = FormBorderStyle.None
picform.Show()

but this is in another event, so I can not get a handle from it. So I am trying to figure out a way to close the newly created form from an escape key press.

Member 11856456 24-Jul-17 4:22am View

The only problem is where it says mybase.keydown, it suppose to be picform.keydown. I cant find this event in the withevents section, but I can see the form1. I need to call forth picform to perform this function. any suggestions?

Member 11856456 19-Jul-17 5:53am View

I see what you are saying, but I have no idea on how to approach it. I am still learning vb.net. every so often I try to advance what I have to something more efficent, or advanced. So if you could help out and show an example of what you are thinking much would be appreciated.

Member 11856456 19-Jul-17 5:04am View

There is no counter, images are converted per folder that they are in as an array. those pictures are placed individually into picture boxes and then displayed in panel1.control. I can go 1 image at a time, but I want to be able to scroll through each image with the right arrow key.

1. there are pictureboxes in the panel
2. I am not sure where the keydown event should be placed and how its properly used.
3. "pic" which is the array of pictureboxes is the behavior picturebox is suppose to do. I am just wanting to individually go to the next one and have it displayed. I figure it would be easier to make a new form and display them on it going to the next picture box in line. It would look like a pictureviewer in a way.

Member 11856456 18-Jul-17 17:52pm View

I have improved the question, I thought it was going to be a simple answer that I just couldn't figure out. The reason I assume the panel holds the info I want is because the original pictureboxes, dubbed "pic" are located in the panel.

Member 11856456 18-Jul-17 3:54am View

Pic is the pictureboxes I have created from an array. Pic is added to panel1.controls. I am trying to use the right and left arrow keys to go back and forth between the images to that they can be displayed at a pace someone would like to go.

Member 11856456 10-Jul-17 17:34pm View

I did the recommended solution you have posted, it gets me past my initial problem and now when I run the program it says "System.NullReferenceException: 'Object reference not set to an instance of an object.' this is at the " bookList.Application.ScreenUpdating = False" portion.

also, do I need to add any additional information to the "Dim ThisWorkbook As Microsoft.Office.Interop.Excel.Workbook = exapp.Workbooks.Add()" inside the parenthesis?

Member 11856456 10-Jul-17 17:02pm View

Deleted

I did the recommended solution you have posted, it gets me past my initial problem and now when I run the program it says "System.NullReferenceException: 'Object reference not set to an instance of an object.' this is at the " bookList.Application.ScreenUpdating = False" portion.

also, do I need to add any additional information to the "Dim ThisWorkbook As Microsoft.Office.Interop.Excel.Workbook = exapp.Workbooks.Add()" inside the parenthesis?

Member 11856456 10-Jul-17 16:30pm View

Deleted

I did the recommended solution you have posted, it gets me past my initial problem and now when I run the program it says "System.NullReferenceException: 'Object reference not set to an instance of an object.' this is at the " bookList.Application.ScreenUpdating = False" portion.

also, do I need to add any additional information to the "Dim ThisWorkbook As Microsoft.Office.Interop.Excel.Workbook = exapp.Workbooks.Add()" inside the parenthesis?

Member 11856456 10-Jul-17 14:03pm View

just added that to my question, thanks for bringing that to my attention.

Member 11856456 10-Jul-17 14:01pm View

Sorry, I forgot to put that in my vb.net code its giving me a null object reference error at Dim ThisWorkbook As Excel.Workbook = exapp.Workbooks.Add(). I cant seem to get past this point.

Member 11856456 6-Jul-17 4:10am View

the I+=1 but that's not exactly what I wanted it to do. I just used the same format for ID, so I could test the code. but I could not come up with a creative way to express UserID which should stay the same as long as its the same user of course.

Member 11856456 6-Jul-17 4:07am View

I setup foreign keys, I thought by doing so it would automatically pass the values over to the access table. My assumption was wrong. Can you give me an example of your parameterized access table?

Member 11856456 1-Jul-17 1:07am View

I appreciate the advice, I will work on how to parameterise my statements.

Member 11856456 30-Jun-17 1:43am View

I fixed the code where I can get images and place them in SQL into a single table like you told me too. I even made an ImageID and regular ID column. I am just trying to figure this last part out and that will get this one portion of my program completed. Anyways, I have never used a executerscalar or executereader so far. When you mean concentate you mean the "'"?

Member 11856456 4-Jun-17 14:55pm View

so I will try to dray a diagram using brackets as column displays.

|Table Name|Row count|date of employment|Products sold|

using the sql reader function in vb.net to display the information in listview. Just an easy way to view data once I click on a database. I have everything set up except for an appropriate SQL statement. Is there a better way to get the data?

Member 11856456 4-Jun-17 8:14am View

Just tried that now, it's not giving me any specific column information and just repeats "acceptable_cursor_options" in each row.

Member 11856456 1-Jun-17 2:08am View

Thank you, I will take your advice. I will have to go through and redo quite a few, but I think in the end it will be worth it. thanks for the explanations as well.

Member 11856456 31-May-17 12:59pm View

This brings up a good question then. How do you go about making a search or filter function that would not cause an SQL injection?

Member 11856456 24-May-17 16:26pm View

I can kind of see what you are doing, this configuration falls under sql normalization. The originalgriff and a few others have suggested looking into it, which I have. I do not understand it as much, I know there are 4 levels to it. Just didnt know that my question would follow the same guidelines. One of my other questions dealt with images and it was suggested to do the same thing, just make a table of the images and that all images should be n one column. Anyways, I will look more into sql normaization. Thanks both of you for the replies.

Member 11856456 7-May-17 20:24pm View

Thank you, I will look up sql normalisation.

Member 11856456 7-May-17 19:22pm View

Griff,

you are right about your observation on what I am trying to do. Let me explain a little further on what I am trying to do. I am making a database that involves quite a few people. I have paper and documents in jpeg form that I would like to include with their infomation. How can I accomplish this by your advice that you have given before. Can you give examples or give me a place I can see examples?

again, thank you for the sound advice.

Member 11856456 6-May-17 13:19pm View

Thank you, works like a charm!

Member 11856456 1-May-17 2:07am View

2 Last things, is there a quicker way to judge points on a print document? Also, how can I set up a print document for each type of printing I would like to do? I am making multiple different things that need to be printed, but they are each about a page.

Member 11856456 30-Apr-17 15:24pm View

Griff,

To be honest I am an amateur at coding. I know how I want things to look, just don't know all the specifics to make it more appealing or better in terms of programming. Anyways I haven't had a chance to use any print document type coding. Can you draw lines and have data stringed in to fill a form to print. All I am trying to do is make a paper form (once printed out) that will have specific information from the custom on the sheet. Will I be able to do this through print document?

Member 11856456 20-Apr-17 11:23am View

I see there were only minor changes to make this code work appropriately. Thank you for your help.

Member 11856456 4-Apr-17 16:02pm View

How it is set up is the entire column is blank, as for the excel sheet there is no "ID" column. So, I assume when it is pulling in the information from the excel sheet its placing null or blank values. What I am trying to do is as I import an excel sheet it will automatically produce an integer = 1 per record. So that if there are multiple records it will give a number for that record. Let's say there are 5 records I am importing into the vb.net database, they will have a corresponding number up to 5. 1,2,3,4,5. initially I achieved this when I typed out each excel object in this format:

dtExcelData.Columns.Add(New DataColumn("Last_name", GetType(String)))

Member 11856456 30-Mar-17 12:56pm View

I am not sure what the output is. When I run the code I get this error:

Additional information: An attempt to attach an auto-named database for file c:\users\jj\documents\visual studio 2015\Projects\WindowsApplication3\WindowsApplication3\Database1.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

Member 11856456 23-Mar-17 0:39am View

Deleted

This code works well, the only problem is that it only allows the adding of 1 row. This code is extremely close to what I am looking for. What I am trying to do is add pictures selected to one row, then if I have more pictures but it's to something else I can select them and add them to a new row. I am cataloging a book and thought it would be an easy way to keep the chapters together.

Member 11856456 19-Feb-17 22:33pm View

I am trying to get a min and max value from all tables in a database. so when I go and pick a database it will display the tables, row count, min age, max age and original database that the information is in. I want to be able to do a print out of that information.

Member 11856456 19-Feb-17 10:12am View

NightWizzard,

I just changed and improved my question not to long ago in hopes to provide more clarity.

Member 11856456 19-Feb-17 9:56am View

NightWizzard,

The only problem with the solution that you have given is that it does not produce values for all tables, only one table. I have to have it generate age min and max along with the table name its associated with.

Member 11856456 19-Feb-17 9:53am View

OrigianlGriff,

I have supplied additional information along with some data and design. I hope this helps with a clearer understanding.

Member 11856456 8-Feb-17 8:16am View

I had to change the code just a little to run the string I already created. but the code works beautifully, thank you for your help.

While Not Reader.EndOfData
lines = Reader.ReadFields()
CheckedListBox1.Items.AddRange(lines)
End While

Member 11856456 30-Jan-17 10:25am View

As of now I have approximately 25,000,000 names associated with all databases and tables

Member 11856456 30-Jan-17 10:16am View

I actually have a function that allows the user to add tables to a specific database. However, I am not sure what you mean by not a good design? I found that it's faster to have multiple tables than to have one massive table. as of right now the system tables and rows work perfectly, its the other information in SQL I am having a problem with.

Member 11856456 30-Jan-17 9:59am View

you are correct, I think I have about 10 databases, approximately 600 tables. I am just trying to retrieve information on each and have them displayed.

Member 11856456 30-Jan-17 9:13am View

Year1 is going to be the minimum birthyear year2 is going to be the maximum birth year. so when displayed it will show the minimum value in a specific column and in the other, which is year2 it will show the maximum value in a specific column. so literally I am showing per table the year of the oldest and youngest person in that database, which will give them the range.

Member 11856456 30-Jan-17 9:00am View

Thank you, I did not realize that I didn't reply directly to the question he presented. I have added additional information, so hopefully that will help clarify what I am looking for.

Member 11856456 30-Jan-17 8:59am View

I have added additional information to the question and the format on how it should look.

Member 11856456 29-Jan-17 5:15am View

Deleted

I just updated the question with more particular information. Hopefully I can get some help with this issue.

Member 11856456 27-Jan-17 10:53am View

Thank you for the explanation.

Member 11856456 27-Jan-17 9:58am View

Richard, you are a genius thank you. I am still learning a lot about vb.net, would you mind possibly explaining the [{0}] in the code?

Member 11856456 27-Jan-17 8:52am View

how can I go about that using the for each statement?

Member 11856456 25-Jan-17 13:29pm View

how can I rearrange my code to where I am saying that these checked items are used to create the columns of my table. That's mainly all I am trying to do is use the text to create columns based upon what the user has selected?

Member 11856456 20-Jan-17 16:44pm View

I also have tried this method:

ElseIf String.IsNullOrEmpty("last Name") Then
lastName = Nothing

still getting the parameter error

Member 11856456 20-Jan-17 14:01pm View

I tried using an else statement.

If String.Equals(label, "last Name", StringComparison.OrdinalIgnoreCase) Then
lastName = match.Groups("value").Value
else
lastName = string.empty

the error is still saying it needs a parameter @lastname.

Member 11856456 20-Jan-17 3:48am View

I still haven't figured it out, but I am reading about match.success, I am trying to figure out how to implement it into the current code that if a specific section does not match then skip over that.

Member 11856456 19-Jan-17 14:01pm View

thanks, this gives me a great place to start.

Member 11856456 19-Jan-17 13:43pm View

Richard, It seems like this code works. I am needing to add an auto-increment to the mix for the ID column to make for sure. Quick question, will this go to the next row after a break such as a space between the information?
for example-

First Name: steve
Last name: Woods
Race: W
Sex: M

First Name: john (this would start a new row)
Last name: Stockton
Race: B
Sex: M

Member 11856456 19-Jan-17 13:29pm View

Deleted

I am getting 5 errors with the code provided above:

regex is not defined, RegexOptions is not declared, Matchcollection is not defined, match as Match is not defined, matches expression is of type 'matchcollection' which is not a collection type.

Member 11856456 17-Jan-17 15:13pm View

I have made it further, problem is once its place in the appropriate index it copies the same text making a duplicate, but removes the other text. I am reposting the new code.

Member 11856456 17-Jan-17 1:54am View

yeah, it was suggested that my code is wrong because now it's allowing me to drag and drop, but nothing happens. I want to rearrange the list box so that I have certain items in a new position/index

Member 11856456 9-Jan-17 14:44pm View

So once I get the database into the treeview, I have a county column and state column. Example, I used Kentucky as the state and in here I have 3 different counties.

Kentucky-
Floyd
Floyd
Floyd
Floyd
Jefferson
Jefferson
Jefferson
Pike
Pike

what I want to do is remove the duplicated text and keep just 1 of each

Kentucky-
Floyd
Jefferson
Pike

thats just an example, I have other columns I would like to do the same way at the same time.

Member 11856456 9-Jan-17 14:29pm View

This is a very interesting code, but when I run it based upon the text, it removes all text that is "B". What I am looking to do based on your example, is if the database has 3 B's, I only want 1 B.

Member 11856456 7-Jan-17 5:45am View

your solution was close, after tinkering with it this was what I came up with

For Each dgvr As DataGridViewRow In DataGridView1.SelectedRows
the SQL statement worked fine once this portion was set up, thank you and Mr. Richard for your help.

Member 11856456 7-Jan-17 4:40am View

I just tried it this way and I got a new error: Public member 'Item' on type 'DataGridViewRow' not found

Member 11856456 7-Jan-17 4:30am View

I have tried to switch the code to this:
For Each dgvr As DataRowCollection In DataGridView1.SelectedCells.Item(0).Value

but in my SQL string code I have to change it to dgvr.tostring, but I am still getting the same error as before.

Member 11856456 7-Jan-17 4:06am View

I am trying to remove duplicate treenodes that show up in tree view from a database I have. when I populate treeview a lot of the same names pop up, so I am trying to remove those to make my treeview more clean and neat.

Member 11856456 3-Jan-17 15:50pm View

I have figured it out, I had to set it to selected cell to retrieve the value.

Member 11856456 3-Jan-17 13:14pm View

If I do not change it into a string I get an error saying "converting from string "delete from [table2] where Id = " to type 'Double' is not valid"

my parameter is supposed to be equal to the number in the ID column.

not sure what you mean by create 2 parameters?

Comments by Member 11856456 (Top 189 by date)