|
Error: 245
"Conversion failed when converting the varchar value 'AS' to data type int."
The problem is that I have no field or variable named "AS". So I am really stumped. The complete code of the stored procedure is below: (I have marked the location of the error with a comment.)
Do you have any idea what it's talking about? Thank you very much for any pointers in the right direction!
ALTER PROCEDURE [dbo].[spVendorUpdateVendor]
@VENDORID INT,
@ACTIVE BIT,
@COUNTRYID INT,
@VENDORCODE NVARCHAR(10),
@NAME NVARCHAR(50),
@WEBSITE1 NVARCHAR(50),
@WEBSITE2 NVARCHAR(50),
@VIACODE NVARCHAR(10),
@TERMS SMALLINT,
@USECOMPCODE BIT,
@DISCOUNTPERCENT SMALLINT,
@DELETEDBYID INT,
@DELETEDON DATETIME,
@NONUS BIT,
@NOTES NVARCHAR(MAX)
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
IF EXISTS(SELECT ID FROM VENDOR_MASTER WHERE ID = @VENDORID)
BEGIN
IF (@DELETEDBYID > 0) AND (NOT EXISTS(SELECT USERID FROM USERS WHERE USERID = @DELETEDBYID))
BEGIN
RETURN 2;
END
IF (NOT EXISTS(SELECT * FROM COUNTRY_CODES CC WHERE CC.COUNTRYCODE = @COUNTRYID))
BEGIN
RETURN 3;
END
IF (NOT EXISTS(SELECT SHIPVIACODE FROM ORDER_HANDLE_MASTER_SHIPVIA_CODES WHERE SHIPVIACODE = @VIACODE))
BEGIN
RETURN 4;
END
UPDATE VENDOR_MASTER SET ACTIVE = @ACTIVE,
COUNTRYID = @COUNTRYID,
VENDORCODE = @VENDORCODE,
NAME = @NAME,
WEBSITE1 = @WEBSITE1,
WEBSITE2 = @WEBSITE2,
VIACODE = @VIACODE,
TERMS = @TERMS,
USECOMPCODE = @USECOMPCODE,
DISCOUNTPERCENT = @DISCOUNTPERCENT,
DELETEDBYID = @DELETEDBYID,
DELETEDON = @DELETEDON,
NONUS = @NONUS,
NOTES = @NOTES
WHERE ID = @VENDORID;
END
ELSE
BEGIN
RETURN 1;
END
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() [ErrorNumber],
ERROR_PROCEDURE() [ErrorProcedure],
ERROR_LINE() [ErrorLine],
ERROR_MESSAGE() [ErrorMessage]
RETURN ERROR_NUMBER();
END CATCH
RETURN 0;
END
The difficult we do right away...
...the impossible takes slightly longer.
modified 8-Jul-16 16:46pm.
|
|
|
|
|
Well, I have a guess.
First, the question of the value 'AS'. You're having a status return parameter in several places. What's the name of it?
Also, in the catch block you make a
SELECT ERROR_NUMBER() [ErrorNumber],
ERROR_PROCEDURE() [ErrorProcedure],
ERROR_LINE() [ErrorLine],
ERROR_MESSAGE() [ErrorMessage] before RETURN ERROR_NUMBER(); Is that select returned? And what type would that select be?
|
|
|
|
|
Thanks for helping.
Yes, the SELECT dataset is returned containing the error information. Not sure what you mean by what type is the select. I don't know if selects have types the way scalar values do.
In several places I return a status value, but I'm not using a named value, I'm using a numeric constant. Is that what you meant?
EDIT:
OK I think I might know what you mean. The variable that returns the return value is called "@ReturnValue".
The difficult we do right away...
...the impossible takes slightly longer.
modified 8-Jul-16 19:02pm.
|
|
|
|
|
Maybe a silly point but are there any triggers spit associated with the tables?
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
No. There are no triggers.
Could it be a bug in SQL Server? How likely is that?
Do you think maybe if I rearranged the code the error might go away?
The difficult we do right away...
...the impossible takes slightly longer.
|
|
|
|
|
Quote: IF (NOT EXISTS(SELECT * FROM COUNTRY_CODES CC WHERE CC.COUNTRYCODE = @COUNTRYID))
Check the data type for CountryCode!
Richard Andrew x64 wrote: Could it be a bug in SQL Server? How likely is that? Nah something in your code is smelly, just can't spot it!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
DING DING! I think we have a winner!
Thank you so much for spotting that. I had been looking at it for so long that I couldn't see it.
Thanks.
The difficult we do right away...
...the impossible takes slightly longer.
|
|
|
|
|
Our naming conventions are similar, code and id are very different types
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I am not an expert in SQL, but isn't that message complaining about a value, not a variable name? That is to say, some variable contains the string "AS" and you are trying to convert it to an integer.
|
|
|
|
|
Yes you are absolutely correct. I'm not an expert either, so that's why I couldn't spot that.
Thanks for your useful input!
The difficult we do right away...
...the impossible takes slightly longer.
|
|
|
|
|
I wrote this linq statement, it worked fine until I added another join. Guess I got it wrong.
So first this i s list of departments. The first join works fine for the avatars, it's the 2nd join which is a list of categories that belong to the department.
So there are only 6 departments, but I get 11. One record, #5 gets repeated 5 times.
I can't see any errors in it unless I used the wrong join type, in which I'm not sure how to write the correct one.
pResults =
(
from d in context.PRODUCT_DEPARTMENT
join da1 in context.PRODUCT_DEPARTMENT_AVATARS on d.Avatar_Primary equals da1.AvatarID into avatars
from da1 in avatars.DefaultIfEmpty()
join pc in context.PRODUCT_CATEGORY on d.DepartmentID equals pc.DepartmentID into categories
from pc in categories.DefaultIfEmpty()
where d.Deleted == false
orderby d.Name
select new model_departments_index
{
DepartmentID = d.DepartmentID,
Enabled = d.Enabled,
Deleted = d.Deleted,
Name = d.Name,
Description = d.Description,
Featured = d.Featured,
Rollback = d.Rollback,
Avatar_Primary_ID = d.Avatar_Primary,
Avatar_Primary_Image = new model_type_avatar
{
Name = da1.Name,
Alt = da1.Alt,
Data = da1.Data,
Type = da1.Type,
Url = da1.Url
},
Categories =
(
from c in categories
orderby c.Name
select new json_product_categories
{
text = c.Name,
value = c.CategoryID
}
).ToList<json_product_categories>()
}
).ToList();
pValue = pResults.Count();
|
|
|
|
|
I forgot about joining a table that has multiple records that match, which creates multiple primary records, so you have to group.
Anyways, I did this for the time being, don't know why I didn't think of it yesterday. I keep thinking that I didn't know the value of d.departmentID in order to make a match.
pResults =
(
from d in context.PRODUCT_DEPARTMENT
join da1 in context.PRODUCT_DEPARTMENT_AVATARS on d.Avatar_Primary equals da1.AvatarID into avatars
from da1 in avatars.DefaultIfEmpty()
where d.Deleted == false
orderby d.Name
select new model_departments_index
{
DepartmentID = d.DepartmentID,
Enabled = d.Enabled,
Deleted = d.Deleted,
Name = d.Name,
Description = d.Description,
Featured = d.Featured,
Rollback = d.Rollback,
Avatar_Primary_ID = d.Avatar_Primary,
Avatar_Primary_Image = new model_type_avatar
{
Name = da1.Name,
Alt = da1.Alt,
Data = da1.Data,
Type = da1.Type,
Url = da1.Url
},
Categories =
(
from c in context.PRODUCT_CATEGORY
where c.DepartmentID == d.DepartmentID
select new json_product_categories
{
text = c.Name,
value = c.CategoryID
}
).ToList()
}
).ToList();
pValue = pResults.Count();
|
|
|
|
|
Hello All,
We have a table which has merged data from two different flat files. The total number of rows in table is about 2 million. We would like to now compare the data in the table, since it now has data from two different file sources. We need to compare the data on 6-7 fields within the same table.
What would be the an accurate option to go about this? We also have a query which self joins itself and compares the fields in concern.
Thank you for your time!
|
|
|
|
|
In which way do you need to compare the the rows?
Are you looking for duplicates? Partial or fully.
Are there any text fields that needs fuzzy matching?
|
|
|
|
|
add and identity column to the table
concatenate the 7 fields into a key field (can use a view)
group by the key field having a count > 1
You now have a list of the duplicated key values.
select identity field using row_number() and partition over the key field
delete anything with a row_number > 1
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
What RDBMS are you using?
You could do something like
SELECT DISTINCT (your 7 columns) FROM table
to get the unique values if that's what you're looking for.
|
|
|
|
|
Hey guys,
while i was doing some performance tests on my software i came across a fascinating result.
A lot of my DB query execution code uses transactions since if there is an error i have to undo the canges on the database. So now i found that the transaction creation and commit takes roughly 2 the cost of the actual executeQuery(). I also have a isDBAvailable check before every DB query gets fired to cope with connection problems. Now i saw i used to do the available check also with a transaction, which is in fact quite an overload for just checking a SELECT 0 executing as scalar.
So now im not sure if i should stick to the transaction there, any pros or cons? Should i just execute the scalar without using a transaction, what would you guys do? I mean it actually costs ~20ms so far using a transaction and currently im not sure if i need it or not. Little bit stuck right here cause i don'T know what i was thinking back then
Discuss?
[ed. Moved to Database discussion forum]
Edit: Thanks for moving, i see that the term technical discussion was a bit overshot in this!
Rules for the FOSW ![ ^]
if(this.signature != "")
{
MessageBox.Show("This is my signature: " + Environment.NewLine + signature);
}
else
{
MessageBox.Show("404-Signature not found");
}
modified 5-Jul-16 7:57am.
|
|
|
|
|
HobbyProggy wrote: A lot of my DB query execution code uses transactions
Maybe I have a big gaping hole in my understanding of DB's, but why do you need to create a transaction if you're "just" doing a query? Are you writing to some temp tables to perform the query?
Marc
|
|
|
|
|
Not really, but let's say i would insert 20 datasets that are dependant of something else and dataset 19 fails i need to reroll the whole data. That's actually why i use a transaction, so it get's done en block and can be reverted in case of failure.
Rules for the FOSW ![ ^]
if(this.signature != "")
{
MessageBox.Show("This is my signature: " + Environment.NewLine + signature);
}
else
{
MessageBox.Show("404-Signature not found");
}
|
|
|
|
|
Again, why would you put any SELECT in a transaction? (Hint, hint)
|
|
|
|
|
THANKS! I now reread it and got it. Maybe that was the knot in my head!
Rules for the FOSW ![ ^]
if(this.signature != "")
{
MessageBox.Show("This is my signature: " + Environment.NewLine + signature);
}
else
{
MessageBox.Show("404-Signature not found");
}
|
|
|
|
|
Hi,
In my table having, from time and to time, I have to calculate the difference between the two times to calculate hour's of time worked. So can anyone provide me a solution
|
|
|
|
|
Do some research into DATEDIFF[^]
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Yes, I checked using datediff and my query is
select datediff(hh,'05:30:00','10:00:00') from tablename
but it gives me 5 as answer which is not correct
|
|
|
|
|
It is perfectly correct, you looked for the difference in hours. Use your brain, try minutes or even seconds, if you wait for answers to be spoon fed to you it is going to be a long slow day.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|