Question Developing a bible verse search program

[chazrab]

The Listbox on the userform finally populated. But what's in it? It(Listbox1) should display the rows and
columns of the Access database in the other snip below. I feel like I'm finally getting somewhere with this.
Seems like something like a simple change somewhere, either in Access or in VS - like a setting, maybe - that will
finally allow the Listbox to display the rows and columns. BTW - this data hs 31,103 rows across 4 columns
KJV, NIV, NASB, RSV are the column headers( simply version translations) which I did not put in the Access DB. I guess I could add them later - as long as the VS Listbox will always reflect any changes made to the underlying
Access db.
THE FOLLOWING MAY BE A BORING COMMENT TO YOU BUT IT IS JUST A SELF ENCOURAGED HOPE:

This is the furtherest I've gotten in learning the ins and outs of VS and it's pretty exciting to be able to expand the creative abilities without the limitations I've had in a VBA IDE.

From what little I've learned so far, the learning curve should speed up much faster for me. Using C# to add objects to a userform and coding the objects to do what a user wants in VS seems pretty much identical to the coding objects in Excel/VBA. The Listbox is the rowsource for displaying Biblical text verses in custom designed textboxes. Different buttons perform different operations. No big deal.

Main challenge ( which is not possible with a VBA linked textbox ) is writing C# code to tell VS when to start and end red text in a userform textbox between "markers", be they quotes or something else.

A Rich Text Box in VBA does allow that but all the red text substrings have to be hard coded in the coding and I don't feel that the developers of VBA did much to improve the capabilities of a VBA RTB.

There should be an easier, more simple and elegant way to do this in VS and therein lies my one major goal for learning VS . I've reached an important development limitation in VBA - which is why I'm putting the effort in to learn VS. Thanks very much for your prompt and continued help. Sorry for being so wordy. cr, Kingwood, Tx

 

[Skydiver]

The VBA RichTextBox is just a wrapper around the Win32 RichEdit control. The WinForms RichTextBox is also just a wrapper around the Win32 RichEdit control. The RichEdit control needs you to set the formatting (e.g. fonts, colors, etc.) of runs of text. One of the easier ways to do that is to load RTF (Rich Text Format) into the control. An alternative way is to to use the RichEdit control's messages (aka API) to set the formatting for runs of text. Using Visual Studio doesn't magically give you extra powers to do formatted text in the RichTextBox.

The ListBox that you are using above is just a wrapper around the Win32 list box. By default the Win32 list box doesn't know how to format runs of text. It can only draw all the items with your chosen font and colors. To override that behavior, you'll need to use an owner drawn list box where the list box notifies you that it is ready to draw a list item, and you do the drawing. Since you get to do the drawing, you can change to whatever fonts and colors you want.

 

[chazrab]

I don't understand everything you've explained about wrappers and the process of selected red text coding - and that's OK. I'll just keep using YT vids to learn. one of the better ones I've been helped by is Professor Shad Sluiter. Thanks for all the help you' and your associates have given me. cr.

 

[chazrab]

Progressing along - one problem - my VS intellicode dropdown list does not have the same
items as in this tutorial - so I can't write and complete correct code without the same Intellicode list items.
Is there a setting to show all the Intellisense items - i.e., count, etc. ? See the snip of the Intellisense dropdown - it contains "count" along with other items mine does not have. Thx, cr

 

[cjard]

All in, the more sensible thing to do would be to use SQLite, or some small version of SQLServer; avoid Access unless you absolutely have to use it

 

[cjard]

it contains "count" along with other items mine does not have

You need to make sure the variable name to the left of the . is a List<T> or other type that has a Count property. If it's an array it doesn't have a Count it has a Length. If it's an IEnumerable and you've using'd System.Linq you'll have a Count() method but it's not necessarily the same

Pose a new question showing the entire code you complain of, rather than a screenshot that works correctly; we can't comment on code we can't see

 

[chazrab]

Thanks for all the help - I am not opposed to not using Access. It's just that I've created this completed
application in Excel/VBA and now want to use iit as a design model to create exactly the same application
in VS using C#. The underlying data in Excel is from a Rowsource property which links to an Excel Sheet.
Being very new to VS I thought the only and/or best way to use the Sheet data is to export it to an Access database and go from there. The Excel sheet with the data has 31,103 rows across 4 columns.

That said, I am feeling my way through the VS project creation to make this work. By that I mean, this Excel sheet has to be used from a VS Listbox. Each row of the Listbox is displayed in a linked Textbox on a userform. That's how the VBA userforms in the Excel app are built. I am going very slow here. VBA code does not have thes brackets and error popups, so all this is very new. But I really want to port this VBA app over to VS in C# because of severe limitations in VBA. Sorry for the long comment. cr

 

[chazrab]

An earlier comment was made about not attaching these snips - I don't like doing this either. Since I'm a newbie
I thought the Chinese proverb would apply. Giving up on moving this application to VS/C# is not me. Although
the VS code design window is new and challenging, to me its just a learning curve and I'm in a learning process with the goal of being where you are in knowing VS/C#. I won't attach any more
snips if it will cause any help for me to be ignored. Thanks to all for the help and comments. cr

 

[cjard]

C# can use the sheet directly, and it doesn't need Excel or any office program installed. Side note, the jet database driver can also read excel files (but I won't use that either)

Take a look at the attached project and let me know if you have any questions. Note, it was written in .net6 on vs2022 and is a very barebones implementation of a spec that runs "The Excel sheet with the data has 31,103 rows across 4 columns. This Excel sheet has to be used from a VS Listbox. Each row of the Listbox is displayed in a linked Textbox on a userform" - the supplied sheet has only 10,000 rows, but it gives an idea of how one might read an excel sheet of many rows and N columns, as many objects with N properties

 

[chazrab]

That's it! That's exactly what I want to accomplish!. A Listbox populated with search results from a "sheet" or
database that displays the text of a verse and/or an added note. The journey is uphill at this point for me because I've got to learn how to do this in VS/C#. Once I can get to that point, to me, it's just a matter of creating and displaying multiple form views of search results for users - all tied to a Listbox Datasource[VS], Rowsource[VBA]. Here I go again with two snips - you can see how the VBA Listbox code shows multiple column translations of the same verse in 4 columns. If I can get to where your example shows, I can move forward a whole lot faster. Sorry again for the snips.

 

[cjard]

Mmm.. this is heading into database territory I think. You'd probably want a Verses table that has the bible version, book, chapter and verse as an ID, and other tables that capture edit histories and comments on that verse. I'd load the Verses table using the CSV then get the DB to do the searching via a full text index. In some senses taking C# and then using it to recreate exactly a VBA app in the same VBA ways is a bit like buying a ferrari to replace a shopping cart in order to carry groceries home, including pushing the ferrari to and from the shops yourself..

..though I do acknowledge that there is a certain degree of background knowledge that feeds a "how would I do this?" that you may not necessarily have, so perhaps the following opinion would help:

I'd..

* install some flavour of sqlserver on the local machine
* install the ef core poer tools visual studio extension
* install sql server management studio (ssms)
* use ssms to connect to the local server and create a database
* create a table in the DB for the verses, having columns like Bible, Book, Chapter, Verse, Text
* create a .net 6 project (VS 2022)
* use EF Core Power Tools to reverse engineer the db to create c# code that will interact with it
* use something like the code above attached, to load the CSV into an array of objects (assuming each column is a bible version and each row is aligned on boook/chapter/verse so matthew/24/11 is all on the same row in the csv
* parse the book/chapter/verse, derive the bible version from which column youre doing at the time. code for that might look like:

var text = myCsvRowObject.Kjv; //example "Matthew 24:2 And Jesus..."
var bits = text.Split(' ',':');
var book = bits[0];
var chapter = int.Parse(bits[1]);
var verse = int.Parse(bits[2]);

* for each object generated from a csv row, make 4 database entites (one for each bible version) for that verse, with the accompanying text. Code for that might look like:

context.Verses.Add(new (){
  Bible = "KJV",
  Book = book,
  Chapter = chapter,
  Verse = verse,
  Text = myCsvRowObject.Kjv
});
context.Verses.Add(new (){
  Bible = "NASB",
  Book = book,
  Chapter = chapter,
  Verse = verse,
  Text = myCsvRowObject.NASB
});
context.Verses.Add(new (){
  Bible = "NIV",
  Book = book,
  Chapter = chapter,
  Verse = verse,
  Text = myCsvRowObject.Niv
});
context.Verses.Add(new (){
  Bible = "RSV",
  Book = book,
  Chapter = chapter,
  Verse = verse,
  Text = myCsvRowObject.Rsv
});
context.SaveChanges();

* look up enabling full text searching - it wouldn't be needed immediately and the search command below isn't how you'd issue a fulltext query, so put this one down as future upgrades
* implement a search that asks the DB for some keywords; you'll get a list of objects back that you can divide into 4 lists based on the bible version, and assign those lists to a listbox datasource (to show up in the listbox the easiest thing to do would be to make an override of ToString() for the DB entity that returns the verse text). Code for that might look like:

var verses = context.Verses.Where(v => v.Contains(searchTextBox.Text)).OrderBy(v => v.Chapter).ThenBy(v => v.Verse).ToArray();
var grouped = verses.GroupBy(v => v.Bible).ToDictionary(g => g.Key, g => g.ToList());

kjvListbox.DataSource = grouped["KJV"];
nivListbox.DataSource = grouped["NIV"];
...

//this would be neeeded somewhere to extend the Verse class such that it will show the text in a listbox
public partial class Verse{
    public override string ToString() => Text;
}

That should, i think, be all you need to get a barebones DB driven search prog up and running.. More work would be needed to make the listboxes multiline, and more DB table to add edit histories and comments. Might be easier to use a DataGridView

 

[chazrab]

Thanks for the reply - it seems you spent a lot of time developing this answer with all these code examples - and I can't tell you how much I appreciate that - admittedly, I don't understand everything in these lines of code and the guide you're suggesting - you understand what I want to accomplish way, way better than I do at this point in my learning curve - probably because of the various images and my explanations.

The other thing I'm not sure of is - do I have the same version of VS everybody else has - some of the controls in the dropdown properties in these VS tutorial versions do not appear in mine. I have O365 and all the latest updates are installed, - If not, then I need to download or upgrade my VS so I can be on the same page as you and all the tutorial pros.

I need to spend the weekend studying line by line of the suggested method of development of this Bible app in VS/C#. I will say, that because I know VBA very well, I "assumed" learning to develop this application in VS/C# would be an easier task than starting from scratch at a beginner's state - wrong! Thanks again for making such an effort to help me with this. cr

 

[cjard]

these VS tutorial versions

Which tutorial versions? (Did I miss you linking to a tutorial?)

 

[chazrab]

I might have used the wrong terminology - I just meant some controls like .Count does not appear in the Intellisense dropdowns

 

[cjard]

What are you trying to call .Count on? The word to the left of the period - what is it? (Expecting an answer like "a variable of type List<string>")


For example, this should give you .Count:

var x = new List<string>();
var c = x.Count;

This should not, because an array does not have a Count property:

var x = new string[1];
var c = x.Count;

But this would, if LINQ is active:

var x = new string[1];
var c = x.Count(); //extension method made available by `using System.Linq`. Note, a method and a property are different things