Wednesday, 16 January 2013

Calling Stored Procedure with params in VB.NET

Calling Stored Procedure with params in 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)
         Dim conn As SqlConnection =  New SqlConnection("Server = .\sqlexpress2012

        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)


        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))