sql - Error converting Nvarchar data type to datetime from VB.NET -

-

i have problem regarding dates program stored procedure in sql.

my program takes date excel spreadsheet , parses such:

            tempdate = date.fromoadate(exws.cells(exrow, mymatchedcolumns(2)).value)             dim format() = {"dd/mm/yyyy", "dd-mm-yyyy", "yyyy-mm-dd"}             duedate = date.parseexact(tempdate, format,             system.globalization.datetimeformatinfo.invariantinfo,             globalization.datetimestyles.none)

duedate 'date' variable i'm assuming @ point 'duedate' universal date object. think best way parse both english regional date , polish dates, since used on polish machine.

however, when sending values stored procedure:

        mysqlstring = "exec bsp.partprice_sp " & _             "'" & duedate & "', " & _             "'" & mypartid & "', " & _             "'" & currency & "'"

english sends exec bsp.partprice_sp '01/09/2015', 'l555', 'usd' returns price.

polish sends exec bsp.partprice_sp '2015-09-01', 'l555', 'usd' incurs error:

the conversion of nvarchar data type datetime data type resulted in out-of-range value.

i understand error, next execution of stored procedure, polish machine sent 2015-09-10 returned price fine. i'm guessing since 2015-10-09 still within range interestingly returned correct price 10th september 2015.

nevertheless i'm struggling find universal way of executing stored procedure in both english , polish. can give parse dates correctly makes sql happy awesome.

thanks.

program written in vb .net.

p.s if change @duedate in stored procedure nvarchar datetime, an:

error converting data type varchar datetime.

error, i'm guessing english format. many thanks.

date formats in inline non-parameterised queries 1 of things can drive spare in experience.

so -

  1. if @ possible issue sqlcommand collection of sqlparameters, add actual date-typed .net variables. safer.
  2. if have keep inline sql reason, never pass in regionalised date formats. reliably break odd reasons. ideally send server yyyy-mm-dd hh:mm:ss.ms, safe run through centralised function - partly it's easier making sure you're calling right .tostring() format in places correctly, partly i've in past had occasional servers complain @ format.

Comments

Post a Comment

Popular posts from this blog

Hatching array of circles in AutoCAD using c# -

-
i beginner in autocad plugins .i have created array of circle using following code. now, have hatch circles in array .how that??how hatch circles @ once? moreover, how can return hatch method , use hatch objects.sorry language.thanks in advance. [assembly: commandclass(typeof(beamsection.class1))] namespace beamsection { public class class1 { //class1 obj = new class1(); [commandmethod("beamatsupport")] public void addlightweightpolyline() { // current document , database document acdoc = application.documentmanager.mdiactivedocument; database accurdb = acdoc.database; promptpointresult ppr = acdoc.editor.getpoint("\nselect starting point "); var ucs = acdoc.editor.currentusercoordinatesystem; point3d startingpt = ppr.value.transformby(ucs); list<double> radius = new list<double>(); radius.add(0.5); ...
Read more

ios - UITEXTFIELD InputView Uipicker not working in swift -

-
hello trying pop uipickerview view programmatically when user clicks on textfield. have tried doesn't doing anything. isn't working. nothing happening when click textfield class userprofiletableviewcontroller: uitableviewcontroller,uitextfielddelegate,uipickerviewdelegate,uipickerviewdatasource { var itempicker: uipickerview! = uipickerview() @iboutlet weak var gendertxtfield: uitextfield! var gender = ["male","female"] override func viewdidload() { super.viewdidload() gendertxtfield.delegate = self itempicker!.delegate = self itempicker!.datasource = self itempicker!.backgroundcolor = uicolor.blackcolor() self.gendertxtfield.inputview = itempicker } func numberofcomponentsinpickerview(pickerview: uipickerview) -> int{ return 1 } // returns # of rows in each component.. func pickerview(pickerview: uipickerview, numberofrowsincomponent component:...
Read more