question

gie23-0301 avatar image
0 Votes"
gie23-0301 asked TvanStiphout commented

Inserting Records in MS Access Using VB.net

I am trying to Insert Data In my Ms Access Data Base from Vb.net

The Code Below I'm Using can Actually Save Data
Im using Parameterized Query
The Problem is when I save the Computed Values from a certain lines of Codes the Values of the sum will Change.

The Save Code Below:

Try
Dim sqlconn As New OleDb.OleDbConnection
Dim sqlquery As New OleDb.OleDbCommand
Dim connstring As String
connstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\PlanX.mdb"
sqlconn.ConnectionString = connstring
sqlquery.Connection = sqlconn
sqlconn.Open()
sqlquery.CommandText = "INSERT INTO SchoolProfile([Grade1Female],[Grade1Male],[Grade1Total])VALUES(@Grade1Female,@Grade1Male,@Grade1Total )"

         sqlquery.Parameters.AddWithValue("@Grade1Female", Grade1FemaleTextBox.Text)
         sqlquery.Parameters.AddWithValue("@Grade1Male", Grade1MaleTextBox.Text)
         sqlquery.Parameters.AddWithValue("@Grade1Total ", Grade1TotalTextBox.Text)
          
         sqlquery.ExecuteNonQuery()
         sqlconn.Close()

       SchoolProfileTableAdapter.Fill(PlanXDataSet.SchoolProfile)
         SchoolProfileDataGridView.Update()
         MessageBox.Show("School Added")
         Update()
     Catch ex As Exception
         MessageBox.Show(ex.Message)
     End Try

For the Compute Code: ( inserted at the Grade1Male TextBox)

    Dim A As Double
     Dim B As Double
     Dim Result as Double

      If Not Double.TryParse(Grade1FemaleTextBox.Text, A) Then
         MessageBox.Show("INVALID")
     Else
         If Not Double.TryParse(Grade1MaleTextBox.Text, B) Then
             MessageBox.Show("INVALID")
         Else
             result1 = A + B
            Grade1TotalTextBox.Text = result1.ToString("")
         End If
     End If


Problem: After I Press the Save Button,
"INVALID" pops up from the message box
I can't seem to pinpoint the problem.
I also used toggle Breakpoint, but it wont show what is the problem.

office-vba-dev
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Calculated values like A+B don't belong in a relational database, but should be returned by a query.

0 Votes 0 ·

1 Answer

Viorel-1 avatar image
0 Votes"
Viorel-1 answered gie23-0301 commented

It is not clear why your second code is called after you press the Save button, but you can rewrite the line from the first part in this manner:

 . . .
 Dim A As Double
 Dim B As Double
 Double.TryParse(Grade1FemaleTextBox.Text, A)
 Double.TryParse(Grade1MaleTextBox.Text, B)
 sqlquery.Parameters.AddWithValue("@Grade1Total", A + B)
 . . .

· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

nice, it's actually easier that way, but what i want in the code i wrote is that it will check if the the input is an integer or not.

0 Votes 0 ·

If you want to check if the input is an integer or not, then check if the input is an integer or not:

 . . .
 Dim A As Integer
 Dim B As Integer
 If Not Integer.TryParse(Grade1FemaleTextBox.Text, A) Then
     MsgBox("Invalid Grade1 Female")
     Return
 End If
 If Not Integer.TryParse(Grade1MaleTextBox.Text, B) Then
     MsgBox("Invalid Grade1 Male")
     Return
 End If
 sqlquery.Parameters.AddWithValue("@Grade1Total", A + B) 
 . . .

Instead of Return you can use some Else If.

The validation can be moved to a separate function, which is called before writing to database, and which returns a Boolean.


1 Vote 1 ·

I'm still confused if im gonna combine the compute function and the save function into a single button?

or should I insert the Compute Function in the grade 1 male.

0 Votes 0 ·

Thank you very much, you are helping me a lot

0 Votes 0 ·