|
you can used Reserved words @@identity
INSERT Table name DEFAULT VALUES
SELECT @@IDENTITY -- used after any action on db(insert ,update,delete)
or
SELECT IDENT_CURRENT('Table Name')-- used without action this the best
|
|
|
|
|
Hi,
I write a function using Transat-Sql(in sqlserver 2005) which returns a integer. My problem i dont know how to catch the result of my function via C#(Visual 2005). I connect to my database, but the object SqlDatareader does not give me the result of my function.
Thank you for your help
|
|
|
|
|
Use ExecuteScalar for this something like...
wrap the function into a stored proc then call the stored proc:
<br />
SqlCommand command = new SqlCommand("MyStoredProcedure", connection);<br />
command.CommandType = CommandType.StoredProcedure;<br />
object objData = command.ExecuteScalar();<br />
return objData;<br />
|
|
|
|
|
cmd=new sqlcommand("storedname",connection)
cmd.CommandType = CommandType.StoredProcedure
p1 = New SqlParameter("@RETURN_VALUE", SqlDbType.Int, 4)
p1.Direction = ParameterDirection.ReturnValue
cmd.Parameters.Add(p1)
cmd.ExecuteNonQuery()
returnvalue= cmProducts.Parameters("@RETURN_VALUE").Value
Rami Abd Alhalim
|
|
|
|
|
Hello,
Would you please be kind to answer my question !
I have developed an ASP.NET Web application using SQL Server Express 2005 in my local PC where I have used VarBinary(MAX) for an image data storage. It is working fine in my computer. Now, I have configured my SQL Server 2005 in my production server using MyLittleAdmin, I found that, I can not set the value MAX for VarBinary, so I am forced to enter the value of varBinary = 8000. Now, When I insert image to this field more than 8KB, I get the following message,
"String or binary data would be truncated."
So, what does it mean, Is it not really possible to store image or file data in VarBinary which is more than 8KB ? if yes, then how can I configure my table for that using MyLittleAdmin ?
Regards
|
|
|
|
|
I don't know about this software you used "MyLittleAdmin" but I know that when I have a stored procedure that takes a parameter varbinary(MAX), I declare the parameter in the code with a length of -1 as follows:
procedure_Insert.Parameters.Add(
new System.Data.SqlClient.SqlParameter("@picture",
System.Data.SqlDbType.VarBinary, -1,
System.Data.ParameterDirection.Input,
false, 0, 0, "picture", System.Data.DataRowVersion.Current, null)
);
To my knowledge the maximum size of varbinary(MAX) is 2GB but I could be wrong on that one.
Hope this helps 
Talal
-- If this is a post that has been helpful to you, please vote for it. Thank you!
"Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning."
--Rich Cook
|
|
|
|
|
Hi Talal,
Thanks a lot for your reply. Actually I am new and I have never used Stored_Procedures. In my web application, I have used SQLDataSouruce to insert the image. I have insert statement:
INSERT INTO [Images] ([title], [description], [imagedata], [mimetype]) VALUES (@title, @description, @imagedata, @mimetype)
Now, this works in my computer using SQLExpress. But the problem is, I will have to specify a value for the database column of [imagedata] in my remote hosting server's SQL, but I did not need to specify the value of [imagedata] (varBinary-MAX) in SQLExpress. How can I overcome this size issue while configuring the database column ?
Regards.
|
|
|
|
|
Could it be possible that the remote SQL server is not version 2005? As I recall, SQL 2000 doesn't have the MAX feature.
-- If this is a post that has been helpful to you, please vote for it. Thank you!
"Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning."
--Rich Cook
|
|
|
|
|
Or also, that your MyLittleAdmin software doesn't support the MAX size. If you have SQL 2005 on your production server, MAX is supported.
|
|
|
|
|
hello frnds,
i want a query which display a record in following manner
suppose i give
select * from TestTable where testName like 'V%'
the result shows the all names start from characters "V"
but is there any way to accept a numberonly after "V"
hope u can help...
thankx in advance
-koolprasad2003
If the message is useful for U then please Rate This message...
Be a good listener...Because Opprtunity knoughts softly...N-Joy
|
|
|
|
|
If you are using SQL-Server then:
... where testName like 'V[0-9]%'
If you are not using SQL-Server then:
... where left(testName,1,2) between 'V0' and 'V9'
Regards
Andy
If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".
|
|
|
|
|
greetings people,
simply wondering whether there is another of doing this is standard ANSI Transact SQL.
So is here is the query that im running in SQL Server 2005:
Select flavour, isnull([Period 1],0) as [Period 1] , isnull([Period 2], 0) as [Period 2] from (
select sum(crates) as crates, flavour, dbo.getperiod(saledate,'08 Jul 2007','08 Aug 2007') as Period
from ReportSummary where
dbo.getperiod(saledate,'08 Jul 2007','08 Aug 2007') <> 'unknown' and producttypeid=10
group by flavour,dbo.getperiod(saledate,'08 Jul 2007','08 Aug 2007')
) as p
pivot
(
sum(crates) for period in ([Period 1],[Period 2])
) as pvt
and the result set is as follows:
FLAVOUR Period1 Period2
Bitter Lemon 894.41 0.00
Blackcurrant 892.53 0.00
Citrus 481.25 2.00
Coca-Cola 1346.00 0.00
Coke Light 131.75 0.00
Ginger Ale 404.88 0.00
Orange 2667.92 0.00
Soda Water 327.80 0.00
Sprite 1886.49 0.00
Stoney Ginger Beer 559.71 0.00
Tonic Water 250.00 0.00
my problem is that i need to run the same query in a Sybase databse.HOwever, Sybase does not have the pivot operator. Any other of doing the same thing??
Kind regards,
Pete.
|
|
|
|
|
Hi Pete
You should be able to adapt the following technique:
select flavour,
sum(case when period = 1 then crates else 0 end) as Period1_Total,
sum(case when period = 2 then crates else 0 end) as Period2_Total
from ReportSummary
group by flavour
order by flavourThe case clause counts just the crates for the stated period.
Regards
Andy
If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".
|
|
|
|
|
hello friends..I got this error while conecting to sqlsever 2005 through Visual studio 2005
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
can anyone help me..to resolve it
|
|
|
|
|
|
|
Thank you,
I tried all these suggestions but still connection is not established..Please Help me
|
|
|
|
|
huh...could it be that your server URL is wrong by any remote chance?
-- If this is a post that has been helpful to you, please vote for it. Thank you!
"Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning."
--Rich Cook
|
|
|
|
|
I write connection string as
string str="server=pcname/SQLEXPRESS; Initial Catalog=databasename;user=username;password=password ";
Is it right. I am using sql server 2005 express edition
|
|
|
|
|
Could you try:
"data source=PCName\ServerName;initial catalog=DBName;User ID=Username; Password=Password;"
and see what happens?
-- If this is a post that has been helpful to you, please vote for it. Thank you!
"Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning."
--Rich Cook
|
|
|
|
|
I tried it ..But the result is the same...I also allow remote connections through TCP/IP in sql server configuration manager..
|
|
|
|
|
you are using wrong connection string
Use this..
Data Source=REMOTE_SQL_SERVER;Network Library=DBMSSOCN;Initial Catalog=MY_DATABASE;User ID=MY_DB_USER;Password=MY_USER_PASSWORD;
|
|
|
|
|
Well the connection string I gave works perfectly for me. However, it would be interesting to try the SSPI thing:
"data source=PCName\DBServerName;initial catalog=DBName;Integrated Security=SSPI"
-- If this is a post that has been helpful to you, please vote for it. Thank you!
"Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning."
--Rich Cook
|
|
|
|
|
I have done bulk insert. When I do the bulk insert next time; new item has to be inserted and if the item exists already it must be updated. Can this be possible? If its possible plz explain it with coding
Regards,
LEE
|
|
|
|
|
Hi John
In this case, I normally do a bulk insert into a temporary table then run the following to update existing records:
update MMT set
Value1 = T1.Value1,
Value2 = T1.Value2,
LastUpdateDate = GetDate()
from MyMainTable MMT
inner join #MyBulkInsertTable T1
on T1.Id = MyMainTable.Id
where T1.Value1 <> MMT.Value1 or T1.Value2 <> MMT.Value2The where-clause ensures that you only update records that have changed. If the Value1 and Value2 columns can be null then I normally use the IsNull function to force a value in the comparison.
And the following to insert new records:
insert into MyMainTable
select T1.Id, T1.Value1, T1.Value2, GetDate() as InsertDate,
GetDate() as LastUpdateDate
from #MyBulkInsertTable T1
left outer join MyMainTable MMT
on MMT.Id = T1.Id
where MMT.Id is nullThis uses an outer-join to identify rows that have not yet been loaded. The "InsertDate" and "LastUpdateDate" columns provide a primative audit trail that can be useful when things go wrong.
A similar technique can be used for deleting records from MyMainTable.
Hope that helps.
Regards
Andy
If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
