Member Avatar for samuel_1991

With regards to another thread, http://www.daniweb.com/forums/thread251069.html on how to compare the current date to retrieve relevant data (Details there), now I have issue on storing this query result on a VB.net variable.

It works in this way. It is an academic work to do a ecommerce website, and I need to compute some tax imposed on the goods purchased. Relevant subtotal etc has been implemented except on computing the tax. It is supposed to be a function and I have coded it in this way:

Public Function computeTaxCharge(ByVal dblSubTotal As Double) As Double
        Dim dblTaxRate As Double
        Dim dblTaxCharge As Double

        Dim strConn As String
        strConn = ConfigurationManager.ConnectionStrings("AppDb").ToString
        Dim conn As New SqlConnection(strConn)
            Dim strSql As String
            strSql = "SELECT Top 1 TaxRate" & _
            "FROM GST" & _
            "WHERE EffectiveDate <= @dt" & _
            "ORDER BY EffectiveDate DESC"
            ' Define an Command object to execute the SQL statement
            Dim cmd1 As New SqlCommand(strSql, conn)
            cmd1.Parameters.AddWithValue("@Dt", System.DateTime.Now())
            ' Open database connection
            conn.Open()
            ' Execute the Insert SQL
            cmd1.ExecuteNonQuery()
          
            ' Define a data adapter to fetch data
            Dim da As New SqlDataAdapter(cmd1)
            'Create a dataset
            Dim ds As New DataSet
            ' Fetch data to a table
            da.Fill(ds, "dblTaxRate")
            ' Define a data table to hold the data fetched
            Dim dt As New DataTable
            ' Close database connection
            conn.Close()
            dblTaxCharge = dblSubTotal * dblTaxRate / 100
            Return dblTaxCharge
    End Function

But it crashes on

da.Fill(ds,"dblTaxRate")

.

How should I do? Thank you in advance.

  • 1 Contributor
  • 1 Reply
  • 1K Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by samuel_1991

Dani AI

Generated

Likely causes of the crash in 's function: the SQL string concatenation removed necessary spaces (producing an invalid statement), and a SELECT was executed with ExecuteNonQuery (which is for INSERT/UPDATE/DELETE). When a single scalar value is needed, ExecuteScalar is the simplest, fastest option. Also make sure to handle DBNull and explicitly parse the returned value.

Example pattern (safe disposal, parameter type, DBNull check):

Public Function ComputeTaxCharge(ByVal subTotal As Double) As Double
    Dim taxRate As Double = 0.0
    Dim cs As String = ConfigurationManager.ConnectionStrings("AppDb").ConnectionString
    Dim sql As String = "SELECT TOP 1 TaxRate FROM GST WHERE EffectiveDate <= @dt ORDER BY EffectiveDate DESC"

    Using cn As New SqlConnection(cs)
        Using cmd As New SqlCommand(sql, cn)
            cmd.Parameters.Add("@dt", SqlDbType.DateTime).Value = DateTime.Now
            cn.Open()
            Dim obj = cmd.ExecuteScalar()
            If obj IsNot Nothing AndAlso obj IsNot DBNull.Value Then
                Double.TryParse(obj.ToString(), taxRate)
            End If
        End Using
    End Using

    Return subTotal * taxRate / 100
End Function

Troubleshooting tips: confirm the DB column format (is TaxRate stored as 5 for 5% or 0.05 as a fraction?), inspect the exact SqlException message if it still fails (it will point to malformed SQL when spaces are missing), avoid AddWithValue when precision matters and prefer explicit SqlDbType, and cache the tax lookup if performance is a concern (fetch once per request/session rather than per item).

Member Avatar for samuel_1991

Ok, I will make it clear.


I need to use a query to fetch data from database.
The query result must assign to a variable, dblTaxrate that will do the computing in computeTaxCharge using

dblTaxCharge = dblSubtotal * dblTaxrate / 100

where dblTaxCharge is something to show the tax charged on a label during initalization. (This has implemented)

Thus, in short, that is I wish to save my query result on to dblTaxRate which is currently not part of any dataTable, label, textbox etc.

Edited by samuel_1991 because: n/a
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.