|
It's only a few lines of code:
- read next record
- convert data types
- write new record
|
|
|
|
|
I got most of it completed with DB Browser I posted my procedure It did not show as an Answer Thanks
|
|
|
|
|
Your "Steps for Process" isn't clear on every single step, and frankly, seems like you don't understand exactly what is going on at each step, especially the last one.
I'm sorry to say it, but your entire script is screaming "data loss!"
Also, there is no reason to start your column names with some abbreviation of their type, called "Hungarian Notation."
|
|
|
|
|
Make Changes in the DB manually with DB Browser This was perhaps the simplest solution for me
I did not want to learn all the code to put these commands behind a button click procedure
DB Browse has a tab labeled Execute SQL so my first task was to create a new Column in the DB Table
ALTER TABLE TxData ADD NxData INTEGER
Where TxData is the TABLE name and NxData is a new column with INTEGER type data. I hope
Next I needed to write the values in the old column to the new colum
UPDATE TxData SET NxData = txSearchMonth
OK Now we still have a column txSearchMonth that is referenced in the SQLite searches in the application
Because this column was created as a column with TEXT it needs to be deleted
It was causing errors in my SQLite Searches
ALTER TABLE TxData DROP COLUMN txSearchMonth
One last step I have not implemented that will save me from rewriting code in the searches is to RE-Name
the NxData column to txSearchMonth
Any one who wants to share a little code to use these commands with a button click feel free I am all EYE's
|
|
|
|
|
That is one of the most dangerous processes I have ever seen. Modifying your live data base in place and even dropping columns is something I would never dream of.
Also in the following statement:
UPDATE TxData SET NxData = txSearchMonth
You need to check that the NxData column has actually been converted from TEXT to INTEGER , because SQLite is quite happy storing text in integer fields: see Datatypes In SQLite[^].
|
|
|
|
|
Richard I agree Modifying live data was way too scary. So I wrote a TEST app with data that did not matter
Checking that NxData column is TEXT or INTEGER is on the agenda for today
I am not sure how to do that. I guess select the value in the column and see if it is equal to a know INTEGER
As for why I went down this route I was told because my TEXT value in the txSearchMonth was text the search I had written was failing. It was suggested I would need to rewrite the app. This was a DB design issue on my part that I am sure will not happen again! Your feedback is valued as I know you have a number of years as a professional
developer It is too cold for my other hobby woodworking so back to playing programmer and shoveling my Arizona snow
|
|
|
|
|
I did actuall test that earlier, and it seems to be OK. So if you enter "23" for a field that is declared as INTEGER type, SQLite will convert it to an integer. The only issue arises if the text is not a pure integer. The link I gave you above does explain how SQLite treats different data types, and is worth reading.
Choroid wrote: shoveling my Arizona snow I thought it was hot in the southern USA. I live 17 degrees further north and it's only cool here.
|
|
|
|
|
It is the 7000 ft elevation that does the trick
|
|
|
|
|
I used to travel to Colorado regularly and recall seeing snow at the top of Mount Evans in June.
|
|
|
|
|
WOW I would spend my summer in Idaho Springs, Colorado my friend owed the Clear Creek Pharmacy
also would fly out from Ohio in the winter to ski
YES snow in June I had a company car so started driving up Mt Evans the Chevy Impala not so good in 4 in of snow
They renamed Mt. Evans to Mount Blue Sky whole other story
Not so sure I like trying to changing History because it was offensive
Learn from the offenses and don't repeat them guess I am not Woke ha ha
|
|
|
|
|
I didn't get to Idaho Springs, but my two favourite towns were Old Colorado in Colorado Springs, and Estes Park. I worked in the UK, but corporate HQ was in Louisville, CO, so it was great to be sent out there at the company's cost; sometimes more than once in a year.
|
|
|
|
|
Richard MacCutchan wrote: dangerous processes I have ever seen.
I was working at a company with about 300 employees.
DBA seemed like a nice guy.
One day I asked him where he was checking his SQL (stored procs) into source control.
He had no idea what source control was.
I then asked where he was doing his work day to day.
On the production database...
|
|
|
|
|
and that's where I walk my ass out the door.
|
|
|
|
|
1) Add a new column to your existing data base: Call it "Year"; make it an int.
2) Copy your "string" year to your int year.
3) Test with your new int year.
4) Delete the old column.
"Before entering on an understanding, I have meditated for a long time, and have foreseen what might happen. It is not genius which reveals to me suddenly, secretly, what I have to say or to do in a circumstance unexpected by other people; it is reflection, it is meditation." - Napoleon I
|
|
|
|
|
Are you aware that SQLite does not statically type it's columns, so even if you declare a column as type int , you can still insert any value into the column and SQLite will not validate for you?
e.g.
sqlite> create table test(i int, d date);
sqlite> insert into test(i,d) values("seven", "Hello");
sqlite> insert into test(i, d) values(7, "2023-01-01");
sqlite> select * from test;
seven|Hello
7|2023-01-01
sqlite>
See Datatypes In SQLite
Since that's the case, you might wish to write insert/update triggers so that invalid input is flagged before database insert/updates. That might cause significant performance degradation, though, so maybe strict data validation in the application and the data converter would be a better approach.
Keep Calm and Carry On
|
|
|
|
|
Are you aware that SQLite does not statically type it's columns
I was that is why I designed the DB with all the columns as TEXT
The issue was the search variable txSearchMonth is not entered it is being read from the DB as TEXT
Only the two variables gvFromMonth & gvToMonth are selected from a drop down combo box
the txSearchMonth only contains 1 to 12
So because they were TEXT a search for 1 to 3 would bring 10 data along
When I changed the txSearchMonth to INTEGER with these lines of code in DB Browser the search function as intended
ALTER TABLE TxData ADD NxData INTEGER
UPDATE TxData SET NxData = txSearchMonth
ALTER TABLE TxData DROP COLUMN txSearchMonth
ALTER TABLE TxData RENAME COLUMN NxData to txSearchMonth
cmd.CommandText = "SELECT * FROM TxData WHERE txSearchMonth >= $gvFromMonth AND txSearchMonth <= $gvToMonth AND txYear = $gvYear "
Thanks for the reply and advice
|
|
|
|
|
I have a SQLite DB for a Check Book app that has a DB with the field txSearchMonth
which contains a Integer corresponding to the Month of the Year.
I have written a search that lets the user select a month from two combo boxes and enter the year in a text box.
The issue I am experiencing if I select Jan and Feb and enter the year my results includes the current month
NOT just January and February
I have tried various search statements to no avail.
I have used a DataModule to define these variables.
Public gvYear As String
Public gvFromMonth As Integer
Public gvToMonth As Integer
Data is displayed in a DataGridView that is NOT bound to the database.
I will post the complete code but the routine that is failing is gvSearchType = "MoRangeYr" Then
Private Sub ViewSearches()
Dim intID As Integer
Dim strDate As String
Dim strTxType As String
Dim strAmt As Decimal
Dim strCKNum As String
Dim strDesc As String
Dim strBal As Decimal
Dim rowCount As Integer
Dim maxRowCount As Integer
Dim emptyStr As String = " "
Using conn As New SQLiteConnection($"Data Source = '{gv_dbName}';Version=3;")
conn.Open()
Using cmd As New SQLiteCommand("", conn)
If gvSearchType = "All" Then
cmd.CommandText = "SELECT * FROM TxData"
ElseIf gvSearchType = "MoYr" Then
cmd.CommandText = "SELECT * FROM TxData WHERE txSearchMonth = $gvFromMonth AND txYear = $gvYear "
cmd.Parameters.AddWithValue("$gvFromMonth", gvFromMonth)
cmd.Parameters.AddWithValue("$gvYear", gvYear)
ElseIf gvSearchType = "TxMoYr" Then
cmd.CommandText = "SELECT * FROM TxData WHERE txType = $gvTxType AND txSearchMonth = $gvFromMonth AND txYear = $gvYear "
cmd.Parameters.AddWithValue("$gvTxType", gvTxType)
cmd.Parameters.AddWithValue("$gvFromMonth", gvFromMonth)
cmd.Parameters.AddWithValue("$gvYear", gvYear)
ElseIf gvSearchType = "MoRangeYr" Then
cmd.CommandText = "SELECT * FROM TxData WHERE txSearchMonth >= $gvFromMonth AND txSearchMonth <= $gvToMonth AND txYear = $gvYear "
cmd.Parameters.AddWithValue("$gvFromMonth", gvFromMonth)
cmd.Parameters.AddWithValue("$gvToMonth", gvToMonth)
cmd.Parameters.AddWithValue("$gvYear", gvYear)
ElseIf gvSearchType = "Year" Then
cmd.CommandText = "SELECT * FROM TxData WHERE txYear = $gvYear"
cmd.Parameters.AddWithValue("$gvYear", gvYear)
End If
Using rdr As SQLite.SQLiteDataReader = cmd.ExecuteReader
While rdr.Read()
intID = CInt((rdr("TID")))
strDate = rdr("txSortDate").ToString
strTxType = rdr("txType").ToString
strAmt = CDec(rdr("txAmount"))
strCKNum = rdr("txCKNum").ToString
strDesc = rdr("txDesc").ToString
strBal = CDec(rdr("txBalance"))
dgvTX.Columns(3).DefaultCellStyle.Format = "N"
dgvTX.Columns(6).DefaultCellStyle.Format = "N"
dgvTX.Rows.Add(intID, strDate, strTxType, strAmt, strCKNum, strDesc, strBal, emptyStr)
rowCount = rowCount + 1
End While
dgvTX.Columns(3).DefaultCellStyle.Format = "N"
dgvTX.Columns(6).DefaultCellStyle.Format = "N"
dgvTX.Sort(dgvTX.Columns(0), ListSortDirection.Descending)
End Using
I decided to use just the corresponding integer for the month to simplify the search.
Perhaps too simplified ! Suggestions appreciated Code Welcomed I have tried various search statements.
|
|
|
|
|
You show some code and we're to assume the search arguments and data are valid; of which there is no evidence.
You used an int for month, and a string for year. Which raises "questions".
Not much for others to go on.
"Before entering on an understanding, I have meditated for a long time, and have foreseen what might happen. It is not genius which reveals to me suddenly, secretly, what I have to say or to do in a circumstance unexpected by other people; it is reflection, it is meditation." - Napoleon I
|
|
|
|
|
I agree on the use of Integer and Strings in a search not the best design.
Other searches where I use just one month and the year work fine.
FWIW if I search for Jan to Feb by not using Feb and use Mar instead I get the proper results
ie Jan & Feb data only. It is as if the rdr does not know when to stop reading ? ? ?
When the app loads this test runs.It is to find the 4 Tue of the month. Could it be interfering ?
Function FourthTueOfNextMonth(dt As Date) As Date
Dim currDate As Date = (New Date(dt.Year, dt.Month, 1)).AddMonths(1)
While currDate.DayOfWeek <> DayOfWeek.Tuesday
currDate = currDate.AddDays(1)
End While
Return currDate.AddDays(21)
End Function
I have looked at the DB with DB Browser all data looks fine
When I create the table I used this syntax
txSearchMonth TEXT)" same format for Year
Not sure changing to Numeric would solve the issue.
Thanks for the reply
|
|
|
|
|
I posted an Answer to my question. Tested on a few cases looks like it is a good solution.
Per your comment I will look at keeping all the variables as integers and not mixing in strings.
|
|
|
|
|
your observation made me wonder why I used string for year?
As I was writing a small test app it happened with strict ON I had a issue converting a INTEGER to a String
So I gave up and just made gvYear a string So you know I learned from my mistake
gvYear = Convert.ToInt32(tbYear.Text)
If you like you can look at my final solution here
Link to CP Question[^]
Thanks for the comment
|
|
|
|
|
After refreshing my brain with a long search through my SQL book I found this ANSWER
I have not used the SQL Syntax "IN" before but the key word and the addition of Parenthesis I have a solution.
Life is good
This is the faulty line of code
cmd.CommandText = "SELECT * FROM TxData WHERE txSearchMonth >= $gvFromMonth AND txSearchMonth <= $gvToMonth AND txYear = $gvYear "
This is the correct syntax below
cmd.CommandText = "SELECT * FROM TxData WHERE txSearchMonth IN ($gvFromMonth, $gvToMonth) AND txYear = $gvYear "
|
|
|
|
|
That changes the meaning of your query from "records between these two months (inclusive)" to "records in these two specific months".
But that doesn't address your question:
Quote: if I select Jan and Feb and enter the year my results includes the current month
NOT just January and February
There are no months between January and February, so the two queries are identical.
Unless there's something else odd about your data that you haven't told us?
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
BACK to the drawing board DAM
Just did a search Jan to Mar and NO Feb data
You mean I need to know how to test ha ha
Might try adding my original >= IN <= parameters
If that fails Google search time
Thanks for the heads up advice
|
|
|
|
|
I'm facing a problem with VB.NET 2022 that I recently installed on my PC. I'm new to programming, so I'd appreciate some guidance on this issue.
The problem I'm encountering is related to the "Windows Form App" template in VB.NET 2022. Here's what's happening:
Initial Launch: When I launch VB.NET 2022 for the first time and create a new project using the "Windows Form App" template, everything works as expected. The form is displayed correctly.
Subsequent Launches: However, if I close VB.NET and reopen it, the "Windows Form App" template seems to be missing. It's not available in the list of project templates. This happens every time I close and reopen VB.NET.
Opening Previous Projects: Additionally, if I try to open a project that I created using the "Windows Form App" template, I encounter an error message.
I'm really eager to learn and get started with programming, so this issue is quite frustrating for me. As a beginner, I would greatly appreciate any assistance or guidance you can provide to help me resolve this problem.
Thank you so much for your time and help!
|
|
|
|