selecting maximum entry from sql server compact column
-
please help me, i have been having sleepless nights on this code Select max(convert(int,SubString(Employee_ID,PATINDEX('%[0-9]%',Employee_ID),Len(Employee_ID)))) from Employee this code returns a value of 20, when i used sql server 2012 from a column(varchar()) that contains the following; emp001 emp005 emp020 emp018 but now, in sql server compact 4.0 column(nvarchar()), the following errors are thrown; Error Code: 80040E14 Message : Data conversion failed. [ OLE DB status value (if known) = 2 ] Minor Err.: 26306 Source : SQL Server Compact ADO.NET Data Provider Num. Par. : 2 i am using vb in vs 2012 please what am i not understanding? thanks a lot
-
please help me, i have been having sleepless nights on this code Select max(convert(int,SubString(Employee_ID,PATINDEX('%[0-9]%',Employee_ID),Len(Employee_ID)))) from Employee this code returns a value of 20, when i used sql server 2012 from a column(varchar()) that contains the following; emp001 emp005 emp020 emp018 but now, in sql server compact 4.0 column(nvarchar()), the following errors are thrown; Error Code: 80040E14 Message : Data conversion failed. [ OLE DB status value (if known) = 2 ] Minor Err.: 26306 Source : SQL Server Compact ADO.NET Data Provider Num. Par. : 2 i am using vb in vs 2012 please what am i not understanding? thanks a lot
Reduce the select statement to its base components, something like 'select max(employee_id) from Employee). Then add in each clause one at a time until it breaks. Understandable, 'select max(employee_id) from Employee' is not what you want, but it may help find the issue. Tim
-
please help me, i have been having sleepless nights on this code Select max(convert(int,SubString(Employee_ID,PATINDEX('%[0-9]%',Employee_ID),Len(Employee_ID)))) from Employee this code returns a value of 20, when i used sql server 2012 from a column(varchar()) that contains the following; emp001 emp005 emp020 emp018 but now, in sql server compact 4.0 column(nvarchar()), the following errors are thrown; Error Code: 80040E14 Message : Data conversion failed. [ OLE DB status value (if known) = 2 ] Minor Err.: 26306 Source : SQL Server Compact ADO.NET Data Provider Num. Par. : 2 i am using vb in vs 2012 please what am i not understanding? thanks a lot
I have found my way :-D out
-
please help me, i have been having sleepless nights on this code Select max(convert(int,SubString(Employee_ID,PATINDEX('%[0-9]%',Employee_ID),Len(Employee_ID)))) from Employee this code returns a value of 20, when i used sql server 2012 from a column(varchar()) that contains the following; emp001 emp005 emp020 emp018 but now, in sql server compact 4.0 column(nvarchar()), the following errors are thrown; Error Code: 80040E14 Message : Data conversion failed. [ OLE DB status value (if known) = 2 ] Minor Err.: 26306 Source : SQL Server Compact ADO.NET Data Provider Num. Par. : 2 i am using vb in vs 2012 please what am i not understanding? thanks a lot
Member 10218952 wrote:
please what am i not understanding?
Atomicity. It looks a lot like a formatted field, with a string, and a number that gets padded (for displaying purposes). Each field should hold an atomic value; the example consists of two facts, and should have been split over two fields. That's assuming that "emp" is not a constant.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
-
Reduce the select statement to its base components, something like 'select max(employee_id) from Employee). Then add in each clause one at a time until it breaks. Understandable, 'select max(employee_id) from Employee' is not what you want, but it may help find the issue. Tim
i found my way out by creating a new column, ID as a int data type, i then used
Select Max(ID) from Employee
to get the maximum value. finally i just send a string value of Emp"&ID to Employee_ID Column Thanks for your Reply
-
Member 10218952 wrote:
please what am i not understanding?
Atomicity. It looks a lot like a formatted field, with a string, and a number that gets padded (for displaying purposes). Each field should hold an atomic value; the example consists of two facts, and should have been split over two fields. That's assuming that "emp" is not a constant.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
i found my way out by creating a new column, ID as an int data type, i then used Select Max(ID) from Employee to get the maximum value. finally i just send a string value of Emp"&ID to Employee_ID Column Thanks For your Reply
-
i found my way out by creating a new column, ID as an int data type, i then used Select Max(ID) from Employee to get the maximum value. finally i just send a string value of Emp"&ID to Employee_ID Column Thanks For your Reply
Which is half of what Eddy suggested, the ID is correct but storing the "Emp" & ID is wrong, this information has no value and is only required in the UI/Reports. It should be created in the output, not stored. This argument goes out the window if you need to maintain backwards compatibility with a crappy design.
Never underestimate the power of human stupidity RAH
-
Member 10218952 wrote:
please what am i not understanding?
Atomicity. It looks a lot like a formatted field, with a string, and a number that gets padded (for displaying purposes). Each field should hold an atomic value; the example consists of two facts, and should have been split over two fields. That's assuming that "emp" is not a constant.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
thanks to you all. guess i will learn more about atomicity. life became very much easier when i used
Select Max(Employee_ID) from Employee
then using substring to trim the output to taste. thanks to you all, i really appreciate it :-D :-D :thumbsup::thumbsup:
-
thanks to you all. guess i will learn more about atomicity. life became very much easier when i used
Select Max(Employee_ID) from Employee
then using substring to trim the output to taste. thanks to you all, i really appreciate it :-D :-D :thumbsup::thumbsup:
pssst - see the little green up arrow on the right of the message, give Eddy's rep a tickle and up vote the response (the best way to say thanks)
Never underestimate the power of human stupidity RAH
-
Which is half of what Eddy suggested, the ID is correct but storing the "Emp" & ID is wrong, this information has no value and is only required in the UI/Reports. It should be created in the output, not stored. This argument goes out the window if you need to maintain backwards compatibility with a crappy design.
Never underestimate the power of human stupidity RAH
Mycroft Holmes wrote:
This argument goes out the window if you need to maintain backwards compatibility with a crappy design.
No it doesn't :) You can always keep such stuff in a view, sp or a query. I'm accepting one argument only for keeping a crappy design, and that is: If it's not broken, don't fix it. Ooh, it still feels like Monday to me. :zzz:
Be excellent to each other. And... PARTY ON, DUDES! Abraham Lincoln