Writing zeros to DB

base836ball

Active member
Joined
May 4, 2005
Messages
31
Programming Experience
1-3
I have a text box that when the form loads the text box displays:
$0.00

The user can then put in a value or leave it the way it is. If they leave it the way it is then it doesnt write 0 to the database, it just leaves it blank.
In order for my Crystal Report to work I need it to be 0 if the user does not change the value. I tried saying:

If txtTextBox.text = "$0.00" Then
txtTextBox.text = 0
end if

How can I write zeros to the db if the format $0.00 stays that way, and is not changed by the user.
 
It was currency, but I figured it was easier to code the $ on the VB end rather than dealing with writing currency values to the database and getting confused so I switched it to text. I will try the quotes.
 
Ok I have mistaken, The DB is set to numeric not currency or text. Here is some code

If txtTextBox.text = "$0.00" Then
txtTextBox
.text = 0
End If

When I read a number like 0 from the database I convert the value to the $0.00 option. Then when i write it I just want to write the 0 I can get it to write the zero by the above code, but if I puyt in $65.00 it makes it 0 in the database and not 65. Hope this helps.

Then i just update the database by bound controls.

If I change the DB to text and do it with quotes in my code it errors out on me.
 
Last edited:
:D

First off, without quotes you cannot add zerro to the string (you can but it's not the right way). Get the quotes back.

Second off, you should use either Currency or Text data type in your database to store currency value within. I don't recommend using of Number/Decimal dtype for fields that store currency.

Third off, you should use decimal data type in vb.net while you deal with $ ... as "currency" is vb6 overdated datatype.

That's why i asked for some code ... and if you don't mind i still want to see some your code :) We could improve that ... Cheers ;)
 
Here is all my code just so youy dont miss anything
You can make fun of it and critique it cuz im still quite new at this sooo I dont care. the more help to code the better

VB.NET:
[size=2]Dim bmbAdd As BindingManagerBase

Dim S As String = "$0.00"

Dim dTotal As Double = 0.0

Dim dHaul As Double = 0.0

Dim dStor As Double = 0.0

Dim dAdd As Double = 0.0

Dim dTAmount As Double = 0.0

Dim dDAmount As Double = 0.0

Dim Count As Integer = 1

Dim dsDup As DataSet

Private Sub frmAdd_Load(ByVal sender As System.Object, ByVal e As 

System.EventArgs) Handles MyBase.Load

daAdd.Fill(DsAdd2, "Primary Table")

daTower.Fill(DsTower2, "Tower")

bmbAdd = Me.BindingContext(DsAdd2, "Primary Table")

RecordCount = bmbAdd.Count

InitialLoad()

dtpReport.Value = Date.Today

dtpTow.Value = Date.Today

End Sub

Private Function SetButtons(ByVal State As Boolean)

txtStock.ReadOnly = False

txtCheck.ReadOnly = False

txtHaul.ReadOnly = False

txtStor.ReadOnly = False

txtAdd.ReadOnly = False

txtTAmount.ReadOnly = False

cboTower.Enabled = True

txtDAmount.ReadOnly = False

dtpReport.Enabled = True

dtpTow.Enabled = True

chbMain.Enabled = True

txtComment.ReadOnly = False

End Function

#Region "3.1"

Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As 

System.EventArgs) Handles btnNext.Click

If bmbAdd.Position = RecordCount - 1 Then

MessageBox.Show("There are no morerecords", "Last Record", 

MessageBoxButtons.OK, MessageBoxIcon.Information)

Else

bmbAdd.Position += 1

InitialLoad()

End If

End Sub

'3.2 The PREVIOUS button

Private Sub btnPrev_Click(ByVal sender As System.Object, ByVal e As 

System.EventArgs) Handles btnPrev.Click

If bmbAdd.Position = 0 Then

MessageBox.Show("There are no more records", "First Record", 

MessageBoxButtons.OK, MessageBoxIcon.Information)

Else

bmbAdd.Position -= 1

InitialLoad()

End If

End Sub

'3.3 The FIRST button

Private Sub btnFirst_Click(ByVal sender As System.Object, ByVal e As 

System.EventArgs) Handles btnFirst.Click

bmbAdd.Position = 0

InitialLoad()

End Sub

'3.4 The LAST button

Private Sub btnLast_Click(ByVal sender As System.Object, ByVal e As 

System.EventArgs) Handles btnLast.Click

bmbAdd.Position = RecordCount

InitialLoad()

End Sub

#End Region

Private Sub FillNulls()

If txtStock.Text = "" Then

MessageBox.Show("Please enter a valid Stock number", "Stock 

Entry Error", MessageBoxButtons.OK)

txtStock.Focus()

End If

If txtCheck.Text = Nothing Then

txtCheck.Text = ""

End If

If txtHaul.Text = "$0.00" Then

txtHaul.Text = "0"

End If

If txtStor.Text = "$0.00" Then

txtStor.Text = "0"

End If

If txtAdd.Text = "$0.00" Then

txtAdd.Text = "0"

End If

If txtTAmount.Text = "$0.00" Then

txtTAmount.Text.ToString()

End If

If cboTower.Text = Nothing Then

cboTower.Text = "System"

End If

'If txtDAmount.Text = 0 Then

' txtDAmount.Text = 0

'End If

If txtReport.Text = Nothing Then

txtReport.Text = Date.Today.ToString

End If

If txtTow.Text = Nothing Then

txtTow.Text = Date.Today.ToString

End If

If chbMain.Text = Nothing Then

chbMain.Text = ""

End If

If txtComment.Text = Nothing Then

txtComment.Text = ""

End If

InitialLoad()

End Sub

Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As 

System.EventArgs) Handles btnAdd.Click

SetButtons(False)

If Count <> 1 Then

dHaul = 0

dStor = 0

dAdd = 0

dTAmount = 0

dDAmount = 0

dTotal = 0

FillNulls()

Dim dv As New DataView(DsAdd2.Primary_Table())

dv.RowFilter = "Stock = '" & txtStock.Text & "'"

If dv.Count > 0 Then

MessageBox.Show(" ")

txtStock.Text = ""

Exit Sub

End If

bmbAdd.EndCurrentEdit()

daAdd.Update(DsAdd2, "Primary Table")

Else

Count = 2

End If

bmbAdd.AddNew()

InitialLoad()

txtStock.Focus()

txtReport.Text = Me.dtpReport.Value.ToString

txtTow.Text = Me.dtpTow.Value.ToString

End Sub

#Region "5.1"

'Setting up the Return Key for each textbox

Private Sub StockFocus(ByVal sender As System.Object, ByVal e As 

System.Windows.Forms.KeyEventArgs) Handles txtStock.KeyDown

If e.KeyCode = Keys.Return = True Then

If txtStock.Focus = True Then

txtCheck.Focus()

End If

End If

End Sub

Private Sub CheckFocus(ByVal sender As System.Object, ByVal e As 

System.Windows.Forms.KeyEventArgs) Handles txtCheck.KeyDown

If e.KeyCode = Keys.Return = True Then

If txtCheck.Focus = True Then

txtHaul.Focus()

End If

End If

End Sub

Private Sub HaulFocus(ByVal sender As System.Object, ByVal e As 

System.Windows.Forms.KeyEventArgs) Handles txtHaul.KeyDown

If e.KeyCode = Keys.Return = True Then

If txtHaul.Focus = True Then

txtStor.Focus()

dHaul = Val(txtHaul.Text)

txtHaul.Text = Format(dHaul, "Currency")

dTotal = dTotal + dHaul

Totals()

End If

End If

End Sub

Private Sub StorFocus(ByVal sender As System.Object, ByVal e As 

System.Windows.Forms.KeyEventArgs) Handles txtStor.KeyDown

If e.KeyCode = Keys.Return = True Then

If txtStor.Focus = True Then

txtAdd.Focus()

dStor = Val(txtStor.Text)

txtStor.Text = Format(dStor, "Currency")

dTotal = dTotal + dStor

Totals()

End If

End If

End Sub

Private Sub AddFocus(ByVal sender As System.Object, ByVal e As 

System.Windows.Forms.KeyEventArgs) Handles txtAdd.KeyDown

If e.KeyCode = Keys.Return = True Then

If txtAdd.Focus = True Then

txtTAmount.Focus()

dAdd = Val(txtAdd.Text)

txtAdd.Text = Format(dAdd, "Currency")

dTotal = dTotal + dAdd

Totals()

End If

End If

End Sub

Private Sub TAmountFocus(ByVal sender As System.Object, ByVal e As 

System.Windows.Forms.KeyEventArgs) Handles txtTAmount.KeyDown

If e.KeyCode = Keys.Return = True Then

If txtTAmount.Focus = True Then

cboTower.Focus()

dTAmount = Val(txtTAmount.Text)

txtTAmount.Text = Format(dTAmount, "Currency")

dTotal = dTotal + dTAmount

Totals()

End If

End If

End Sub

Private Sub TowerFocus(ByVal sender As System.Object, ByVal e As 

System.Windows.Forms.KeyEventArgs) Handles cboTower.KeyDown

If e.KeyCode = Keys.Return = True Then

If cboTower.Focus = True Then

txtDAmount.Focus()

End If

End If

End Sub

Private Sub DAmountFocus(ByVal sender As System.Object, ByVal e As 

System.Windows.Forms.KeyEventArgs) Handles txtDAmount.KeyDown

If e.KeyCode = Keys.Return = True Then

If txtDAmount.Focus = True Then

dtpReport.Focus()

dDAmount = Val(txtDAmount.Text)

txtDAmount.Text = Format(dDAmount, "Currency")

End If

End If

End Sub

Private Sub ReportFocus(ByVal sender As System.Object, ByVal e As 

System.Windows.Forms.KeyEventArgs) Handles dtpReport.KeyDown

If e.KeyCode = Keys.Return = True Then

If dtpReport.Focus = True Then

dtpTow.Focus()

End If

End If

End Sub

Private Sub TowFocus(ByVal sender As System.Object, ByVal e As 

System.Windows.Forms.KeyEventArgs) Handles dtpTow.KeyDown

If e.KeyCode = Keys.Return = True Then

If dtpTow.Focus = True Then

btnAdd.Focus()

End If

End If

End Sub

 

#End Region

Private Sub Totals()

dTotal = dHaul + dStor + dAdd + dTAmount

lblTotal.Text = Format(dTotal, "Currency")

End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As 

System.EventArgs) Handles Button1.Click

Me.Close()

End Sub

Private Sub InitialLoad()

dHaul = Val(txtHaul.Text)

txtHaul.Text = Format(dHaul)

dTotal = dTotal + dHaul

dStor = Val(txtStor.Text)

txtStor.Text = Format(dStor, "Currency")

dTotal = dTotal + dStor

dAdd = Val(txtAdd.Text)

txtAdd.Text = Format(dAdd, "Currency")

dTotal = dTotal + dAdd

dTAmount = Val(txtTAmount.Text)

txtTAmount.Text = Format(dTAmount, "Currency")

dTotal = dTotal + dTAmount

Totals()

dDAmount = Val(txtDAmount.Text)

txtDAmount.Text = Format(dDAmount, "Currency")

End Sub

#Region "Finding Stock"

Private Sub btnFind_Click(ByVal sender As System.Object, ByVal e As 

System.EventArgs) Handles btnFind.Click

If txtFind.Text = "" Then

MessageBox.Show("Please enter a valid Stock number", "Stock 

Entry Error", MessageBoxButtons.OK)

Else

strSQL = "SELECT Id, Check#, Hauling, Storage, Additional, 

Towing, First, [Driver Amount], [Report Date], [Tow Date], [Mainstay Tow], 

Comments, ID FROM [Primary Table] WHERE Stock ='" & txtFind.Text & "'"

If SqlConnection1.State = False Then

SqlConnection1.Open()

End If

cmd = New SqlClient.SqlCommand(strSQL, SqlConnection1)

objRead = cmd.ExecuteReader

While objRead.Read

txtStock.Text = txtFind.Text

If IsDBNull(objRead("Check#")) Then

txtCheck.Text = ""

Else

txtCheck.Text = objRead("Check#")

End If

If IsDBNull(objRead("Hauling")) Then

txtHaul.Text = 0

Else

txtHaul.Text = objRead("Hauling")

End If

If IsDBNull(objRead("Storage")) Then

txtStor.Text = 0

Else

txtStor.Text = objRead("Storage")

End If

If IsDBNull(objRead("Additional")) Then

txtAdd.Text = 0

Else

txtAdd.Text = objRead("Additional")

End If

If IsDBNull(objRead("Towing")) Then

txtTAmount.Text = 0

Else

txtTAmount.Text = objRead("Towing")

End If

If IsDBNull(objRead("First")) Then

cboTower.Text = "System"

Else

cboTower.Text = objRead("First")

End If

If IsDBNull(objRead("Driver Amount")) Then

txtDAmount.Text = 0

Else

txtDAmount.Text = objRead("Driver Amount")

End If

If IsDBNull(objRead("Report Date")) Then

dtpReport.Value = Date.Today

Else

dtpReport.Value = objRead("Report Date")

End If

If IsDBNull(objRead("Tow Date")) Then

dtpTow.Value = Date.Today

Else

dtpTow.Value = objRead("Tow Date")

End If

If IsDBNull(objRead("Mainstay Tow")) Then

chbMain.Text = ""

Else

chbMain.Text = objRead("Mainstay Tow")

End If

If IsDBNull(objRead("Comments")) Then

txtComment.Text = ""

Else

txtComment.Text = objRead("Comments")

End If

InitialLoad()

End While

objRead.Close()

End If

txtFind.Text = ""

End Sub

#End Region

Private Sub btnAdd2_Click(ByVal sender As System.Object, ByVal e As 

System.EventArgs) Handles btnAdd2.Click

Dim confirm As Integer

confirm = MessageBox.Show("Are you sure you want to remove stock: " 

& txtStock.Text & " ?", "Delete Confirmation", MessageBoxButtons.YesNo)

If confirm = 6 Then

bmbAdd.RemoveAt(bmbAdd.Position)

bmbAdd.EndCurrentEdit()

'daAdd.DeleteCommand.

End If

End Sub

Private Sub dtpReport_ValueChanged(ByVal sender As System.Object, ByVal 

e As System.EventArgs) Handles dtpReport.ValueChanged

Me.txtReport.Text = Me.dtpReport.Value

End Sub

 

Private Sub btnModify_Click(ByVal sender As System.Object, ByVal e As 

System.EventArgs) Handles btnModify.Click[/size]

[size=2]SetButtons(False)

End Sub

End Class[/size]
 
If you are dealing with currency then your db type should be currency. When you retrieve from or save to the db you should use the VB type Decimal. They are just numbers, so they are no different to using integers or doubles but they are less prone to round-off error. To display a decimal number as a currency value using the local machine settings for currency format use:
VB.NET:
Dim currencyString As String = FormatCurrency(currencyValue)
and to convert the currency string to a decimal use:
VB.NET:
Dim currencyValue As Decimal = Decimal.Parse(currencyString, Globalization.NumberStyles.Currency)
Then your values can go to and from your database like any other numbers.
 
'Hi base836ball,

I hadn't time to improvise and make a similar project that matches all your needs but i made a simple demo (very short i must say, plus without real purpose; anyway it will demonstrate couple tips & tricks) :p

Also, i will try to give couple advices too :)


  • Use vb.net String.Empty equivalent instead of using vb6 quotes ""
  • Use DBNull.Value instead of using IsDBNull() function to check for dbnull as it saves 20% of speed (minimum)
  • For $ always use decimal
  • To format string as currency use: text1.Text = String.Format("{0:n2}", decimalArg) 'you can see this within attached project
there are couple more but i'm a bit lazy today :D ... take a look of attachment


Cheers ;)
 

Attachments

  • base836ball.zip
    55.4 KB · Views: 44
Thanks for all the help guys. I got it working although I ran into a new problem, but I will post a new thread for that one.

Thanks a lot,

Drew
 
Back
Top