|
Hi
I have written a stored procedure as follows:
ALTER PROCEDURE dbo.VehiclesInsert
(
@TypeId int = 4,
@Reg varchar = 15,
@Mileage decimal = 9,
@Price money = 8,
@Fuel int = 4,
@Colour varchar = 80,
@BodyShape int = 4,
@Doors int = 4,
@Warranty int = 4,
@MoreInfo text,
@RegLetter int=4,
@CreatedBy int=4
)
AS
INSERT INTO Vehicles
(TypeId,Reg,Mileage,Price,Fuel,Colour,BodyShape,Doors,Warranty,MoreInfo,RegLetter,CreatedBy)
VALUES
(@TypeId,@Reg,@Mileage,@Price,@Fuel,@Colour,@BodyShape,@Doors,@Warranty,@MoreInfo,@RegLetter,@CreatedBy)
RETURN
Although I have defined the Reg variable as Varchar 15, it only writes the first letter into the Reg field of my DB. I am using ASP.NET and c# to call the sproc - I even tried hard-coding the parameter value e.g:
objCmd.Parameters.Add("@Reg", "Test Reg");
However it still only adds "T" into the Reg field. I can manually add Test Reg into the field in the DB table so the field lengths all seem to be ok.
I'm very new to SProcs so please excuse me if I'm missing something obvious!
Thanks
Lorna
|
|
|
|
|
try make this changes:
.<br />
.<br />
.<br />
@Reg varchar (15),<br />
.<br />
.<br />
.<br />
@Colour varchar (80),<br />
.<br />
.<br />
.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
Thanks - that was the problem (I knew it would be something obvious  )
I actually had int = 4 in other SProcs so I've changed those values to just int now in case anyone else is interested and they all work fine.
|
|
|
|
|
Mmm. Blue boy is right, your equals to syntax is just assigning default values to the parameters. Your varchar is being declared with the default length of 1.
|
|
|
|
|
try this ok?
sql_cmd.Parameters.Add("@country", SqlDbType.varchar).Value = textbox_GrandPrixCountry.text;
what do you get from this???
Member 3402886 wrote: objCmd.Parameters.Add("@Reg", "Test Reg");
nelsonpaixao@yahoo.com.br
trying to help & get help
|
|
|
|
|
Hi Everyone,
I have created one dtsx package in Sql Server Integration Services. Is there any way to assign revision number to my package? How can I update that revision number. 
CodeManiac
xxxxxxxxxx
xxxxxxxxxx
|
|
|
|
|
If you've created the package using Business Intelligence Studio, hit F4 on the package and you'll find version number, major and minor.
|
|
|
|
|
Hi i want to update the "name" element in XML using the OPENXML in SQLServer. below is what i'm trying to do without success. Im not even sure if its possible. The value "xxxxx" is normally the name of a table, but when dealing with xml i'm not sure what should be there. Im passing in the xml as a parameter to the stored procedure and doing suff to it. but the last thing in the stored procedure is to update the "name" element. not sure how to do this need some help please. Thanks ONeil
update xxxxxxx
set OPENXML (@hDoc, '/Employees/Employee')
WITH (tName varchar(20) './Name') = 'UpdatedName'
|
|
|
|
|
Have a look at XML DML and especially updating a value: replace value of (XML DML)[^]
Hope this helps,
Mika
The need to optimize rises from a bad design
|
|
|
|
|
Thanks that helped alot. I manage to get cross that hurdle but now im faced with another problem. im getting error --- "The argument 1 of the xml data type method "modify" must be a string literal" --- for the below statement. I want to take the parameter @code and make it part of the insert statement. if i hardcode a value in the insert statement it work but once i reference the "@code" im getting "The argument 1 of the xml data type method "modify" must be a string literal". Any help would be appreciated
########## XML passing IN ###############
<Employees>
<Employee ID= '111'><Code>1</Code><Name>Name1</Name></Employee>
</Employees>
############# Stored Procedure ###########
ALTER PROC [dbo].[sp_Testing]
@empdata xml,
@code varchar(255)
AS
DECLARE @hDoc int
exec sp_xml_preparedocument @hDoc OUTPUT,@empdata
Select @empdata
set @empdata.modify('insert <Employee ID= ''101''>
'+@code+'</Code><Name>''Name101''</Name></Employee>into (/Employees)[1]')<br />
<br />
--return XML<br />
Select @empdata
|
|
|
|
|
Would this piece code perform the operation correctly? If I understood correctly, you want to insert an element to the xml data so you don't need to dynamically parse or concatenate the string.
declare @empdata xml
set @empdata = '<Employees><Employee ID= ''111''><Code>1</Code><Name>Name1</Name></Employee></Employees>'
Select @empdata
set @empdata.modify('insert <Employee ID= ''101''></Employee> into (/Employees)[1]')
Select @empdata
The result:
<Employees>
<Employee ID="111">
<Code>1</Code>
<Name>Name1</Name>
</Employee>
<Employee ID="101" />
</Employees>
Mika
The need to optimize rises from a bad design
|
|
|
|
|
It works fine if i hardcode the values in (as i said in my previous post and as you have done in your previous post). the problem is when i try passing in the "@code varchar(255)" as a parameter and use it to build the insert statement. that when im getting the error
|
|
|
|
|
Sorry about the misunderstanding, that's what you said in the first place (I'll have to go back to the english lessons and learn how to read  ).
Let's try something else. Note that the variable for the element to add is not varchar but xml (this is required).
declare @empdata xml
declare @somethingToAdd xml
set @empdata = '<Employees><Employee ID= ''111''><Code>1</Code><Name>Name1</Name></Employee></Employees>'
set @somethingToAdd = '<Employee ID= ''101''></Employee>'
Select @empdata
set @empdata.modify('insert sql:variable("@somethingToAdd") into (/Employees)[1]')
Select @empdata
Would this help?
The need to optimize rises from a bad design
|
|
|
|
|
Im feeling very stupid today. for some reason im always getting errors. copy, paste and try the below code. It parses ok but when Execute im getting this error ------- "The data types varchar and xml are incompatible in the add operator."
########################################
ALTER PROC [dbo].[sp_Testing2]
@empdata xml,
@code varchar
AS
DECLARE @hDoc int
declare @somethingToAdd xml
exec sp_xml_preparedocument @hDoc OUTPUT,@empdata
set @somethingToAdd = '<Employee ID= ''101''><Code>'+@code+'</Code><Name>''Name101''</Name></Employee>'
Select @empdata
set @empdata.modify('insert sql:variable('+ @somethingToAdd+') into (/Employees)[1]')
-- return the XMl
Select @empdata
####################################################
|
|
|
|
|
One problem is that you modified the insert literal (don't concatenate!).
If you don't need hDoc in somewhere else, the procedure would be in minimum something like this (note that @code is xml):
ALTER PROC [dbo].[sp_Testing2]
@empdata xml,
@code xml
AS
Select @empdata
set @empdata.modify('insert sql:variable("@code") into (/Employees)[1]')
-- return the XMl
Select @empdata
The need to optimize rises from a bad design
|
|
|
|
|
Thanks you have helped alot
|
|
|
|
|
You're welcome
The need to optimize rises from a bad design
|
|
|
|
|
hi,
i am not very much sure that sql server database can be installed on windows xp os or not. whenever i am trying to install sql server database on windows xp os then information is showing that only client can be installed but database portion is disabled........so if anyone know that how to install sql server database on windows XP OS then plzz let me know with in detail info.
Thanks
tbhattacharjee
|
|
|
|
|
Yes it can be installed on XP, but SP level must be 2.
Depending on the version, you must first install client components and after that server portion. Also you must have sufficient privileges.
The need to optimize rises from a bad design
|
|
|
|
|
Please help me,
I want to connect to an mdf database (built by SQL Server 2005 Express) in VC++ using OLE DB. I can't do it. please help. please provide a code for me to connect to "c:\Database#1.mdf" using code.
Please help. Urgent.
|
|
|
|
|
|
yeah, no one cares if it urgent for you. Connection strings are one of the basic parts of programming. At least TRY to do it and if you get an error come back and we can look at your code. We're not going to do your work for you.
Blog link to be reinstated at a later date.
|
|
|
|
|
This is my code:
<br />
_ConnectionPtr m_pConnection;<br />
<br />
CoInitialize(NULL);<br />
<br />
m_csDataSource = <br />
"Provider=SQLNCLI;Server=.\\SQLExpress; \<br />
AttachDbFilename=C:\\DATABASE#1.MDF; \<br />
Trusted_Connection=Yes;"<br />
<br />
<br />
<br />
HRESULT hRes = m_pConnection.CreateInstance(__uuidof(Connection));<br />
if (SUCCEEDED(hRes))<br />
{<br />
hRes = m_pConnection->Open(_bstr_t((LPCTSTR)m_csDataSource),<br />
_bstr_t(L""),_bstr_t(L""),adModeUnknown);<br />
if (SUCCEEDED(hRes))<br />
{<br />
m_bIsConnectionOpen = TRUE;<br />
}<br />
}<br />
I get this error:
An attempt to attach an auto-named database for file C:\\Database#1.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located in on UNC share.
|
|
|
|
|
Why do I bother.
Blog link to be reinstated at a later date.
|
|
|
|
|
My Paranoid Hubby wrote: Why do I bother.
For the entertainment of others 
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
"Not only do you continue to babble nonsense, you can't even correctly remember the nonsense you babbled just minutes ago." - Rob Graham
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
