|
papy-boom wrote: tried to get select @@IDENTITY AS returnedvalue to get the name of column but it doesn't work
No, because it only returns the value.
papy-boom wrote: i have a datareader to read the value returned value and the argument to pass to datareader is the name of the column so if my procedure return me a value without the name of column it's not simple to get the value
Now I don't follow the logic. You cannot ask the last added value for a single column using @@IDENTITY. It doesn't work that way.
If you need to know what's the last added identity value to a specific table, you can select MAX from the identity column. But I don't see the point why you should do this since the value of an identity column is normally irrelevant. Refer to: http://en.wikipedia.org/wiki/Surrogate_key[^]
So the question is, why do you wan't to know the value at all?
|
|
|
|
|
when i excute my procedure In SQL i have the value but in the name of column i have (no column name)
i write a script in C# to get the value with data reader but the method used in datareder is GetValueParam(Command,columName) the procedure dosen't return a name for this column i tried to give a name to returned column so i modify it with
COMMIT TRANSACTION INSERT_Artist
RETURN SELECT max(Artist#) AS ArtistId from Artist
LBL_ERROR:
ROLLBACK TRANSACTION INSERT_Artist
but i have the same problem (no column name) for returned value i need the column name to read it with a datareader without using another transaction
excuse my ignorance but i'm really dump in SQL Server
|
|
|
|
|
papy-boom wrote: the method used in datareder is GetValueParam(Command,columName) the procedure dosen't return a name for this column
Okay, now I understand your problem. You can give the column a name or rename a column in a result set using AS, like:
SELECT @@IDENTITY AS SomeColumnName
|
|
|
|
|
i've tried that but still not working
|
|
|
|
|
Try
Select @@Identity ID
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
In what respect is it not working?
Bob
Ashfield Consultants Ltd
|
|
|
|
|
it works it was a problem in my Program
thank you all !!
|
|
|
|
|
Hi,
I am trying to pass an UPDATE / INSERT statement into an MS-SQL DB via a BAT script. Basically, I want to amend / add entries in the DB via a BAT script.
1. Can this be done?
2. If it can be done, how would I connect to the DB? What would the syntax be?
Any assistance on this will be much appreciated.
Thanks,
M
|
|
|
|
|
|
Thanks for your response Colin, much appreciated.
However, it looks like SQLCMD is only available in MS-SQL 2005 onwards. I am using MS-SQL 2000 though, and I think the OSQL command is the alternative.
Any experience with this?
Thanks!
|
|
|
|
|
mil_an wrote: I think the OSQL command is the alternative
Correct. The server & connection details can go on the command line and you can read a file of commands to execute. A quick google should reveal plenty of examples.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Thanks.
This is what I have so far and it seems to work when run on the server where the DB is located:
OSQL -s <<SERVER_NAME>>\<<DB_NAME>> -U <<USERNAME>> -P <<PASSWORD>> -i <<PATH_TO_SQL_COMMAND_FILE>>
The above makes the connection to the DB and runs the SQL statements held in a text file (PATH_TO_SQL_COMMAND_FILE), which looks a little like this:
UPDATE <<TABLE_NAME>>
SET <<Column>> = '<<VALUE>>'
WHERE <<PREDICTAES>>
GO
Now, I am facing the following issue. I am unable to run this command from a different server that does not host the DB. I get the below error:
[Shared Memory]SQL Server does not exist or access denied.<br />
[Shared Memory]ConnectionOpen (Connect()).
I am using the host name of the DB server including DNS for server_name.
Any ideas?
modified on Wednesday, December 10, 2008 9:15 AM
|
|
|
|
|
I am working with a project using MS Access 2003 as backend database and vb.net 2003 as front end windows application. I need to update few records of table1 using the values given by the user through textboxes. At the same time I need to insert a new record in table2 using the same values of textboxes given by user. I tried with single adapter to update command for 1st table and insert command for 2nd table. Also tried with two adapters one for updating and one for inserting. But it is not working.bcoz in the where clause of update statement for 1st table one field value may contain null value or a value from textbox.
Dim query as string
Query=update table1 set status=?, details=? Where (userid=’” & TextBox1.Text.Trim & “’ And Priority=’” & TextBox2.Text.Trim & “’)
Here Textbox2 may be blank…if it is blank then query should update the corresponding row in database whose priority field value is blank & userid is textbox1 value.
Bcoz another same record may exist in database with priority value not null and userid (Textbox1 value).
I can tackle this by using the if condition….
If textbox2 value is blank, then I will use update statement like this
Query=update table1 set status=?, details=? Where (userid=’” & TextBox1.Text.Trim & “’ And Priority IS NULL)
If textbox2 contains a value then I will use
Query=update table1 set status=?, details=? Where (userid=’” & TextBox1.Text.Trim & “’ And Priority=’” & TextBox2.Text.Trim & “’)
But I want to know is there any better method….than this to do…?
Thanks in Advance…
|
|
|
|
|
chandrubngit wrote: I want to know is there any better method….than this to do
I don't see what's the problem in your code (except that you're not using parameters for values in conditions). If you're trying to get rid of the if logic, you can convert possible NULL in Priority to an empty string if you want. Something like:
... AND IIF(IsNull(Priority), '', Priority) = ’" & TextBox2.Text.Trim & "’)
or preferrably
... AND IIF(IsNull(Priority), '', Priority) = ?)
but unless you have a very small amount of rows in your table, this will have a (possibly untolerable) negative impact on performance.
|
|
|
|
|
I'm working on an ASP.Net applica tion accessing couple of database tables from AS400 mainframe server database . In the code there's an SQL Query, which seems very complicated to me, the person who did the actual coding is no more with our firm.
Right now the query is producing some kinda result which is wrong. So can anyone please help me out in finding what's wrong with the SQL query? The SQL query is attached below:
Me.OdbcSelectCommand2.CommandText =
"SELECT
SAMFILE.STAYP.SCLINT,
SAMFILE.STAYP.SADMHH,
SAMFILE.STAYP.SADMYY,
SAMFILE.STAYP.SADMMM,
SAMFILE.STAYP.SBLDG,
SAMFILE.STAYP.SADMDD,
SAMFILE.STAYP.STRMCD,
SAMFILE.STAYP.STRMHH,
SAMFILE.STAYP.STRMYY,
SAMFILE.STAYP.STRMMM,
SAMFILE.STAYP.STRMDD,
SAMFILE.CLIENTP.CNAME,
SAMFILE.CLIENTP.CRACE1,
SAMFILE.CLIENTP.CRACE2,
SAMFILE.CLIENTP.CRELIG,
SAMFILE.CLIENTP.CSEX,
SAMFILE.CLIENTP.CBTHHH,
SAMFILE.CLIENTP.CBTHYY,
SAMFILE.CLIENTP.CBTHMM,
SAMFILE.CLIENTP.CBTHDD,
SAMFILE.REFSRP.REFSDS,
SAMFILE.STAYP.SMDPM,
SAMFILE.STAYP.SREFRL,
SAMFILE.STAYP.SADRG1,
SAMFILE.STAYP.SADRG2,
SAMFILE.RACETP.RACEDS,
SAMFILE.RELIGP.RELGDS,
SAMFILE.STAYP.SADMAG
FROM
SAMFILE.REFSRP,
SAMFILE.RELIGP,
SAMFILE.RACETP,
{
oj SAMFILE.STAYP
LEFT OUTER "JOIN SAMFILE.CLIENTP
ON SAMFILE.STAYP.SCLINT = SAMFILE.CLIENTP.""CLNT#""
}
WHERE
SAMFILE.REFSRP.REFSCD = SAMFILE.STAYP.SREFRL
AND
SAMFILE.RELIGP.RELGCD = SAMFILE.CLIENTP.CRELIG
AND
SAMFILE.RACETP.RACECD = SAMFILE.CLIENTP.CRACE1"
// After some loop conditions, the OdbcSelectCommand2 is used in 4 concatenations like this.
OdbcSelectCommand2.CommandText = OdbcSelectCommand2.CommandText & " AND (({ fn CONCAT({ fn CONCAT({ fn CONCAT({ fn CONCAT({ fn CONCAT(digits(SADMHH), digits(SADMYY)) }, '-') }, digits(SADMMM)) }, '-') }, digits(SADMDD)) } <= { d '" & End_date_conv & "' })"
OdbcSelectCommand2.CommandText = OdbcSelectCommand2.CommandText & " AND ({ fn CONCAT({ fn CONCAT({ fn CONCAT({ fn CONCAT({ fn CONCAT(digits(STRMHH), digits(STRMYY)) }, '-') }, digits(STRMMM)) }, '-') }, digits(STRMDD)) } >= { d '" & Start_date_conv & "' })"
OdbcSelectCommand2.CommandText = OdbcSelectCommand2.CommandText & " OR ({ fn CONCAT({ fn CONCAT({ fn CONCAT({ fn CONCAT({ fn CONCAT(digits(SADMHH), digits(SADMYY)) }, '-') }, digits(SADMMM)) }, '-') }, digits(SADMDD)) } <= { d '" & End_date_conv & "' })"
OdbcSelectCommand2.CommandText = OdbcSelectCommand2.CommandText & " AND (SAMFILE.STAYP.STRMCD = 0))"
// To get the desired result, one more table is suppossed to be used, i.e MOVESP table, but its a tremendously huge table containing records of more than 30+ years and each id is having multiple records and to get the expected result it should be used in the code as something like this. But everytime I try this, it throws an exception "System.outofmemoryexception". I tried increasing the physical and virtual RAM and even timeout durations too but all in vain.
OdbcSelectCommand2.CommandText = OdbcSelectCommand2.CommandText & " AND (SAMFILE.MOVESP.MCODE = 'A'))"
Note:
This query works but it fetches wrong information. In the below query I want to replace the last condition (SAMFILE.STAYP.STRMCD = 0 ) with this new condition ( SAMFILE.MOVESP.MCODE = ""A"" ) and I believe this will make it to fetch the right values to display on the crystal report. But whenever I try to include this new code alongwith other necessary changes it shoots up an erorr message. And the other changes which I make are like :
1. Adding a new element by the name "MCODE " in "as400ds2.xsd "
2. Adding [New System.Data.Common.DataColumnMapping("MCODE", "MCODE") ] into the OdbcDataAdapter.TableMappings.AddRange section .
Thanking you all in anticipation
|
|
|
|
|
current column is of type "Text" in sql server
I am converting table pl/sql. What would be an equivalent datatype in pl/sql.
|
|
|
|
|
Have a look at CLOB or NCLOB types. I think they are the closest match. If you don't need more than 4000 bytes of text then you can also use VARCHAR2 .
|
|
|
|
|
thanks for ur response. what is the difference between CLOB and NCLOB? I am going to be using this column to store html text
|
|
|
|
|
Civic06 wrote: what is the difference between CLOB and NCLOB
CLOB is used for single- and multi-byte characters wheras NCLOB is used for unicode text.
|
|
|
|
|
Thanks. One more question: either type will only take up the amount of space depending on how much is stored in the column? In other words, its not fixed length right?
|
|
|
|
|
Civic06 wrote: its not fixed length right
No it's not. Single CLOB can store gigabytes of data, so if it would be fixed length, it would eat out the disk space quite fast.
The minimum storage size is size of one block but depending on the CHUNK parameter, it can be multiple blocks.
|
|
|
|
|
Apologies if this isn't the correct forum.
OK, the situation is that our web app has a feature to send autometed emails. The emails may potentially contain the Euro symbol ('€'). The DB server holds the currency symbol, and it can be SELECTed correctly. However, when the symbol is used in an auto-email, it's replaced with a '?'
The sending is performed using CDO, encaspulated into an SP. The sp itself sets up the various requisites through sp_OASetProperty, and references the CDO schema...eg:
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
I'm able to correctly send a mail with a Euro symbol in it though Outlook, using the same exchange server.
Anyone have any idea what may be causing this / seen it before? Most of the links I've found seem to hint at setting Message.BodyPart.Codepage / CharSet, but I'm unable to find out how to do the same thing in T-SQL....
C# has already designed away most of the tedium of C++.
|
|
|
|
|
You could try setting the body to HTML if it's plain text and that may make a difference.
Keep It Simple Stupid! (KISS)
|
|
|
|
|
Hi and thanks for the quick reply.
Unfortunately, it's already an HTML email. I've just tried swapping to a text body just in case, and same result.
Cheers for the suggestion though!
C# has already designed away most of the tedium of C++.
|
|
|
|
|