Wednesday, 16 January 2013

Calling Stored Procedure with params in ado.net VB.NET

Calling Stored Procedure with params in ado.net VB.NET


This example explains if SQL-Server "Language" is different from Current UI culture. In this case datetime params needs to convert to SQL-Server Culture.


Private Sub Calling_Stored_Procedures_using_adonet(begingdate As DateTime, enddate As DateTime)
       
        Try
         Dim conn As SqlConnection =  New SqlConnection("Server = .\sqlexpress2012
                                               database=northwindtrusted_connection=yes")

        String beginDate = begingdate.ToString(
                            New System.Globalization.CultureInfo("en-US"))
        String endDate = enddate.ToString(
                          New System.Globalization.CultureInfo("en-US"))
        String stroredProc = "Exec [dbo].[Sales by Year] @Beginning_Date='"
                             + beginDate + "',@Ending_Date='" + endDate+"'"

        Dim sel As SqlCommand =  New SqlCommand(stroredProc)
        sel.Connection = conn
        Dim ad As SqlDataAdapter =  New SqlDataAdapter(sel)

        conn.Open()
        ad.Fill(dt)

        End Try
End Sub
Calling at Client Side

System.Threading.Thread.CurrentThread.CurrentCulture = New System.Globalization.CultureInfo("te-IN")
System.Threading.Thread.CurrentThread.CurrentUICulture = New System.Globalization.CultureInfo("te-IN")
Calling_Stored_Procedures_using_adonet(New DateTime(1996, 1, 1), New DateTime(1998, 12, 31))