vb.net - The ASP.NET function returns only zero for some cases - Stack Overflow

admin管理员组

文章数量:1123888

The ASP.NET function returns correct value back for only on case (TW_2888), but zero for all other cases. I know there are some values for the US and NL (When I operate similar code in MS Access VBA it gives value back exact the same for TW_2888 and some values for US and NL!!).

Why does it work for the third case, but doesn't find any value for the other?

The unusual query in my code is because there is a stored procedure in SQL Server and retrieving data is only in this form possible. The "MBD-X8", "MBD-X9" and so on are filtering to catch exact the required data.

Public Function GetSoldDataForRegions(startDate As Date, endDate As Date) As Integer
    Dim soldDataUS As Integer = 0
    Dim soldDataNL As Integer = 0
    Dim soldDataTW_2888 As Integer = 0
    Dim soldDataTW_3806 As Integer = 0

        Dim connectionStrings As Dictionary(Of String, String) = New Dictionary(Of String, 
        String) From {
            {"US", "Data Source=myConnection"},
            {"NL", "Data Source=myConnection"},
            {"TW_2888", "Data Source=myConnection"},
            {"TW_3806", "Data Source=myConnection"}
            }
        Dim queryBase As String = "SELECT TOP 1 SUM(Qty) AS SumOfQty FROM 
        GetSingleMBDSoldData 
        (',MBD-C7,MBD-C9,MBD-M1,MBD-P9', @StartDate, @EndDate)"

        For Each region As String In connectionStrings.Keys
            Dim connectionString As String = connectionStrings(region)

            Using conn As New SqlConnection(connectionString)
                Dim cmd As New SqlCommand(queryBase, conn)
                cmd.Parameters.AddWithValue("@StartDate", startDate.ToString("MM-dd-yyyy"))
                cmd.Parameters.AddWithValue("@EndDate", endDate.ToString("MM-dd-yyyy"))

                Try
                    Console.WriteLine($"Executing query for region: {region}")
                    conn.Open()

                    Dim result As Object = cmd.ExecuteScalar()
                    Console.WriteLine($"Result for {region}: {If(result, "NULL")}")

                    If result IsNot Nothing AndAlso Not IsDBNull(result) Then
                        Select Case region
                            Case "US"
                                soldDataUS = Convert.ToInt32(result)
                            Case "NL"
                                soldDataNL = Convert.ToInt32(result)
                            Case "TW_2888"
                                soldDataTW_2888 = Convert.ToInt32(result)
                            Case "TW_3806"
                                soldDataTW_3806 = Convert.ToInt32(result)
                        End Select
                    Else
                        Console.WriteLine($"No data found for region: {region}")
                    End If
                Catch ex As Exception
                    Console.WriteLine($"Error processing region {region}: {ex.Message}")
                End Try
            End Using
        Next

        Dim totalSoldData As Integer = soldDataUS + soldDataNL + soldDataTW_2888 + 
    soldDataTW_3806

        Console.WriteLine($"Sold Data US: {soldDataUS}")
        Console.WriteLine($"Sold Data NL: {soldDataNL}")
        Console.WriteLine($"Sold Data TW_2888: {soldDataTW_2888}")
        Console.WriteLine($"Sold Data TW_3806: {soldDataTW_3806}")
        Console.WriteLine($"Total Sold Data: {totalSoldData}")
    Return totalSoldData
End Function

The ASP.NET function returns correct value back for only on case (TW_2888), but zero for all other cases. I know there are some values for the US and NL (When I operate similar code in MS Access VBA it gives value back exact the same for TW_2888 and some values for US and NL!!).

Why does it work for the third case, but doesn't find any value for the other?

The unusual query in my code is because there is a stored procedure in SQL Server and retrieving data is only in this form possible. The "MBD-X8", "MBD-X9" and so on are filtering to catch exact the required data.

Public Function GetSoldDataForRegions(startDate As Date, endDate As Date) As Integer
    Dim soldDataUS As Integer = 0
    Dim soldDataNL As Integer = 0
    Dim soldDataTW_2888 As Integer = 0
    Dim soldDataTW_3806 As Integer = 0

        Dim connectionStrings As Dictionary(Of String, String) = New Dictionary(Of String, 
        String) From {
            {"US", "Data Source=myConnection"},
            {"NL", "Data Source=myConnection"},
            {"TW_2888", "Data Source=myConnection"},
            {"TW_3806", "Data Source=myConnection"}
            }
        Dim queryBase As String = "SELECT TOP 1 SUM(Qty) AS SumOfQty FROM 
        GetSingleMBDSoldData 
        (',MBD-C7,MBD-C9,MBD-M1,MBD-P9', @StartDate, @EndDate)"

        For Each region As String In connectionStrings.Keys
            Dim connectionString As String = connectionStrings(region)

            Using conn As New SqlConnection(connectionString)
                Dim cmd As New SqlCommand(queryBase, conn)
                cmd.Parameters.AddWithValue("@StartDate", startDate.ToString("MM-dd-yyyy"))
                cmd.Parameters.AddWithValue("@EndDate", endDate.ToString("MM-dd-yyyy"))

                Try
                    Console.WriteLine($"Executing query for region: {region}")
                    conn.Open()

                    Dim result As Object = cmd.ExecuteScalar()
                    Console.WriteLine($"Result for {region}: {If(result, "NULL")}")

                    If result IsNot Nothing AndAlso Not IsDBNull(result) Then
                        Select Case region
                            Case "US"
                                soldDataUS = Convert.ToInt32(result)
                            Case "NL"
                                soldDataNL = Convert.ToInt32(result)
                            Case "TW_2888"
                                soldDataTW_2888 = Convert.ToInt32(result)
                            Case "TW_3806"
                                soldDataTW_3806 = Convert.ToInt32(result)
                        End Select
                    Else
                        Console.WriteLine($"No data found for region: {region}")
                    End If
                Catch ex As Exception
                    Console.WriteLine($"Error processing region {region}: {ex.Message}")
                End Try
            End Using
        Next

        Dim totalSoldData As Integer = soldDataUS + soldDataNL + soldDataTW_2888 + 
    soldDataTW_3806

        Console.WriteLine($"Sold Data US: {soldDataUS}")
        Console.WriteLine($"Sold Data NL: {soldDataNL}")
        Console.WriteLine($"Sold Data TW_2888: {soldDataTW_2888}")
        Console.WriteLine($"Sold Data TW_3806: {soldDataTW_3806}")
        Console.WriteLine($"Total Sold Data: {totalSoldData}")
    Return totalSoldData
End Function

• asp.net
• vb.net
• ms-access

Share Improve this question Follow edited yesterday marc_s 754k183183 gold badges1.4k1.4k silver badges1.5k1.5k bronze badges asked yesterday aarjaangaarjaang 1133 bronze badges 3

• This looks somewhat strange with a starting "," in the string: ',MBD-C7,MBD-C9,MBD-M1,MBD-P9' ------------- and are you sure that each value not supposed to be ,'MBD', MBD-C9','MBD-M1','MBD-P9' etc.? (in other words, each token is a string???) What I STRONG suggest is you fire up SQL manager, and paste in that query - see what the results are. Since you ARE seeing some data, then this suggests the date restricting or some such is not what you expect. I ALSO VERY much suggest you use .Add, and STRONG type the parameter type. – Albert D. Kallal Commented yesterday
• Thank you, Albert. Please ignore the starting "," in the string. Since the string was much too long, I just cut a part of that, and I forgot to cut the starting comma. I will try your suggestion with 'MBD-M1','MBD-P9', but because it gives correct value for one case, it should be correct in the current format. – aarjaang Commented yesterday
• I just tried it with 'MBD-M1','MBD-P9' etc. It returns only zero for all 4 cases. But with the former format it returns number only for TW_2888!! – aarjaang Commented yesterday

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

As noted, I strong suggest trying that query in SQL Studio.

And also, I have repeated stated to NOT use .addWith, but to use .Add with a STRONG typed SqlDbType parameter (dateTime) setting.

And, worse here you are converting a datetime to a string, and trying to feed to that SQL server as a date - (a VERY bad idea).

So, fire up SQL studio, and try say this in a new query:

DECLARE @StartDate datetime
DECLARE @EndDate datetime

set @StartDate = '2025-01-09'
set @EndDate = '2025-01-12'

select * from GetSingleMBDSoldData('MBD-C7,MBD-C9,MBD-M1,MBD-P9', @StartDate, @EndDate)

So, test/try the query - make sure it works from SQL studio.

And as I stated, do NOT convert your datetime to a string, and feed that to SQL server.

Hence this:

cmd.Parameters.Add("@StartDate", SqlDbType.DateTime).Value = startDate
cmd.Parameters.Add("@EndDate",SqlDbType.DateTime).Value =  endDate

So, test this in SQL studio, and verify that your select query works.

本文标签： vbnetThe ASPNET function returns only zero for some casesStack Overflow