Inserting computed data into ms acccess using VB.NET

Question

0.00/5 (No votes)

See more:

Problem:

The sum of the 2 textboxes are computed and stored in the result textbox.
And After Pressing the Save Button To save it in the Database a msgbox pops up saying "INVALID" like 3 times.

What I have tried:

For the Compute Code :

dim a as double
dim b as double
dim result as double

 If Not Double.TryParse(KMTextBox.Text, A) Then
            MessageBox.Show("INVALID")
        Else
            If Not Double.TryParse(KFTextBox.Text, B) Then
                MessageBox.Show("INVALID")
            Else
                result = A + B
                KTTextBox.Text = result.ToString("")
            End If
        End If

For the Save Button

Try
            Dim sqlconn As New OleDb.OleDbConnection
            Dim sqlquery As New OleDb.OleDbCommand

            connstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\Planz.mdb"
            sqlconn.ConnectionString = connstring
            sqlquery.Connection = sqlconn
            sqlconn.Open()
            sqlquery.CommandText = "INSERT INTO Table1 ([KM],[KF],[KT]) Values (@KM,@KF,@KT)"
            sqlquery.Parameters.AddWithValue("@KM", KMTextBox.Text)
            sqlquery.Parameters.AddWithValue("@KF", KFTextBox.Text)
            sqlquery.Parameters.AddWithValue("@KT", KTTextBox.Text)

           sqlquery.ExecuteNonQuery()
            sqlconn.Close()

           Table1TableAdapter.Fill(PlanzDataSet.Table1)
           Table1DataGridView.Update()
            MessageBox.Show("Record Added")
            Update()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

Posted 5-Aug-21 20:20pm

Beginner213456

Updated 5-Aug-21 20:50pm

Add a Solution

Comments

Richard MacCutchan 6-Aug-21 3:20am

You should never use TextBox values direct like that to save in your database. You have no idea what they may contain. Also, you should store numbers as numbers, not as strings.

Create three type double variables at class level, and save the two input numbers and the calculated total in them in the calculation part. Then in your save code you just save the numeric variables, and ignore the TextBoxes.

Beginner213456 6-Aug-21 4:56am

I removed the .Text from the Insert Code

can you please some examples how to create a three type double variable at class level?

Richard MacCutchan 6-Aug-21 6:28am

The same as you have in the above code (but with proper meaningful names) just after the line that begins: Public Class ....

It would be a good idea to spend some time studying the full language, rather than trying to learn from questions posted here.

Beginner213456 8-Aug-21 20:49pm

I am not saving saving decimal numbers, so is it okay to convert my code into int32.tryparse or integer.tryparse instead of double.tryparse?

Beginner213456 8-Aug-21 20:59pm

dim a as integer
dim b as integer
dim result1 as integer
If Not Integer.TryParse(KMTextBox.Text, A) Then
MessageBox.Show("INVALID")
Else
If Not Integer.TryParse(KFTextBox.Text, B) Then
MessageBox.Show("INVALID")
Else
result1 = A + B
KTTextBox.Text = result1
End If
End If

i used this code and the INVALID error was gone, but when i press the Save, it says "data type mismatch in criteria expression"

Beginner213456 9-Aug-21 1:46am

My problem regarding in the Compute and Save Have been Resolved Thank you.
I saved them as integer and used integer.tryparse to check if the values in the textboxes are integers, if not it will show "INVALID"

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2021-08-05T20:50:00

Solution 1

Start by not assuming that "text" is the same as "number".
Instead of passing string values, do the TryParse operation in the Save method to generate numeric values and pass those to your DB instead of passing the text you assume is correct.
That way, Access doesn't have to assume anything about the string version when it tries to convert it to a number for storage. Remember, the user can change textbox values independently of your other code!

Then find out where the error is coming from: use the debugger to step through the code and find out if it's the ExecuteNonQuery or the Update call that generates the error message.

Sorry, but we can't do that for you - we have no access to your data!

And it's also a very bad idea to hardcode connection strings: store them in config files so that they only need to be changed in one place when the path to a DB changes, and that place does not require a recompilation.

Posted 5-Aug-21 20:50pm

OriginalGriff

Comments

Beginner213456 6-Aug-21 3:43am

Can you tell me more,what should i do about the tryparse operation, should i copy and paste it in the save button?
or should i modify it?

Beginner213456 6-Aug-21 3:47am

is there a code to store data in the config files?
are the app.config some kind of storage? are they linked with the database?

OriginalGriff 6-Aug-21 5:08am

How would a config file holding the connection string be part of the DB that it's storing the connection string for?

Google "config file VB" and your environment: Web, WinForms, WPf, ... the solution will be different for each.

Beginner213456 9-Aug-21 2:34am

I already have an app.config file, should i edit the codes?
According to the questions answered and articles i have read, the codes from the articles are already in the app.config file,
my question now is how to store and retrieve the constring?

OriginalGriff 6-Aug-21 5:06am

You've already used TryParse in your own code! So you know how to use it, or you are just copy'n'pasting chunks from others work without even trying to understand them ... that's dangerous!

Beginner213456 9-Aug-21 20:36pm

I realized that I'm not Inserting Decimal Numbers So I Decided to Modify my Code
Instead of double I used Integer.TryParse
Dim a as integer
dim b as integer
If Not Integer.TryParse(KMTextBox.Text, A) Then
MessageBox.Show("INVALID")
Else
If Not Integer.TryParse(KFTextBox.Text, B) Then
MessageBox.Show("INVALID")
Else
result1 = A + B
KTTextBox.Text = result1
End If
End If

and in the Save Button, I replaced the
sqlquery.Parameters.AddWithValue("@KT", KTTextBox)
with
sqlquery.Parameters.AddWithValue("@KT", A + B)

please correct me if im wrong, thank you