How to retrieve the GUID value of a SQL NewID() identity column after an insert ?

Hello,

In my table, i've a GUID column type. I insert a new record with NewID() function in Sql request.

Is it possible to retreive the GUID column of this new record (without requerying the table) ?

I'm using EVC, Sql Mobile 3.0 and OLE DB interface.

Thanks in advance.

no, you have to turn around and requery the database. another option if you are using CF2 is to create the GUID in your mobile app code and use it in your INSERT statement instead of NewID(). Then you know what it is without the extra database roundtrip.

-Darren

How to retrieve the GUID value of a SQL NewID() identity column after an insert ?

Hello,

In my table, i've a GUID column type. I insert a new record with NewID() function in Sql request.

Is it possible to retreive the GUID column of this new record (without requerying the table) ?

I'm using EVC, Sql Mobile 3.0 and OLE DB interface.

Thanks in advance.

no, you have to turn around and requery the database. another option if you are using CF2 is to create the GUID in your mobile app code and use it in your INSERT statement instead of NewID(). Then you know what it is without the extra database roundtrip.

-Darren

how to retrieve a col definition NULL or NOT NULL

Hello,
I usually retreive information on columns using:
SELECT * FROM syscolumns order by name asc
SELECT * FROM systypes
systypes.names defines the type for instance.
But I am trying to find what defines NULL or NOT NULL for each columns.
Would someone know what defines it?
thanks for your helpDid you check the isnullable column in syscolumns?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Wilfrid" <grille11@.yahoo.com> wrote in message news:479723a7$0$7041$426a74cc@.news.free.fr...
> Hello,
> I usually retreive information on columns using:
> SELECT * FROM syscolumns order by name asc
> SELECT * FROM systypes
> systypes.names defines the type for instance.
> But I am trying to find what defines NULL or NOT NULL for each columns.
> Would someone know what defines it?
> thanks for your help
>
>
>|||Next time I will open my eyes, sorry to bother.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eVhTlfbXIHA.1188@.TK2MSFTNGP04.phx.gbl...
> Did you check the isnullable column in syscolumns?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Wilfrid" <grille11@.yahoo.com> wrote in message
> news:479723a7$0$7041$426a74cc@.news.free.fr...
>> Hello,
>> I usually retreive information on columns using:
>> SELECT * FROM syscolumns order by name asc
>> SELECT * FROM systypes
>> systypes.names defines the type for instance.
>> But I am trying to find what defines NULL or NOT NULL for each columns.
>> Would someone know what defines it?
>> thanks for your help
>>
>>
>>
>|||No worries. Sometimes the obvious isn't so ... obvious. :-)
Also, if you are on 2005, consider using the catalog views instead, for instance sys.columns.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Wilfrid" <grille11@.yahoo.com> wrote in message news:479736f1$0$10823$426a74cc@.news.free.fr...
> Next time I will open my eyes, sorry to bother.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:eVhTlfbXIHA.1188@.TK2MSFTNGP04.phx.gbl...
>> Did you check the isnullable column in syscolumns?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Wilfrid" <grille11@.yahoo.com> wrote in message news:479723a7$0$7041$426a74cc@.news.free.fr...
>> Hello,
>> I usually retreive information on columns using:
>> SELECT * FROM syscolumns order by name asc
>> SELECT * FROM systypes
>> systypes.names defines the type for instance.
>> But I am trying to find what defines NULL or NOT NULL for each columns.
>> Would someone know what defines it?
>> thanks for your help
>>
>>
>>
>>
>

How to retreive set of rows from stored procedure

hi,

i am new to SQL. i have created a stored procedure which gets a input parameter "Category" and it selects datas which falls under this category. when i run this procedure it returns only the last row. it doesnt retreive the entire set of rows. which method should i follow to solve my problem.. i want all the rows which comes under the category to be returned ....

MY PROCEDURE

CREATE PROCEDURE [dbo].[Items_Category_sorted]

(

@.Category varchar(10),

@.ProductID Char(10) OUTPUT,

@.Name Char(50) OUTPUT,

@.UnitPrice Numeric(9) OUTPUT,

@.Stock Numeric(9) OUTPUT

)

AS

BEGIN

SET NOCOUNT ON;

SELECT @.ProductID=ProductID,

@.Name=Name,

@.UnitPrice=UnitPrice,

@.Stock =Stock

From

ProductDetails

Where

Category=@.Category

END

As you're using output parameters, you can only have one value per parameter and this will be set to values in the last row returned by your query.

To return a recordset of multiple values, try:

CREATE PROCEDURE [dbo].[Items_Category_sorted]

(

@.Category varchar(10)

)

AS

BEGIN

SET NOCOUNT ON;

SELECT ProductID,

Name,

UnitPrice,

Stock

From

ProductDetails

Where

Category=@.Category

END

Hope this helps!

How to retreive data from Excel to SQL SERVER Database table

Hi Friends,

I am Using SQL SERVER 2005

I need to retreive data from Excel file to SQLSERVER table..

Thanks in advance

Regards

Rajkumar.M

There are many ways to do this. You may use SQL Server integration services, or you may create a persistent connection to any data source supporting OLE DB (including MS Excel) via linked servers, or you may use OPENDATASOURCE clause in your query.

http://support.microsoft.com/kb/321686 will help you start.

How to retreive data from Excel into SQLSERVER database table

Hi friends,

I need to retreive data from Excel into SQLSERVER database table

give me reply asap.

Thanks in advance...

you can do it, using SqlBulkCopy using ADO.NET

I have developed a small tool in C# that can work for you,

I have tested it using sql server 2000/2005 and works ok
but you require Framework 2.0

so if you wanna test it, just mail me
vish4forum at yahoo dot com. I will send you that exe

|||

Hi Vish,

I found ur reply useful..

please send me sample code that will be better

|||

Hi Vish,

I tried using SQLBulk copy..But i got error 'could not find installable ISAM'

Here is my code

string excelConnectionString = @."Provider=Microsoft.Jet.OLEDB.4.0;Data Source=192.168.0.25;Extended
Properties=""Excel 11.0;HDR=YES;IMEX=1;""";

OleDbConnection con = new OleDbConnection(excelConnectionString);


con.Open();
OleDbCommand cmd = new OleDbCommand("select * from [test$]", con);
dr = cmd.ExecuteReader();

SqlConnection con1 = new SqlConnection();
con1.ConnectionString = System.Configuration.ConfigurationManager.AppSettings["strcon"];
con1.Open();
SqlBulkCopy bulk1 = new SqlBulkCopy(con1);
bulk1.DestinationTableName = "tblexcel";
bulk1.WriteToServer(dr);
Response.Write("Loaded");

|||

Checkout this two articles

1)http://davidhayden.com/blog/dave/archive/2006/05/31/2976.aspx

2)http://support.microsoft.com/kb/321686

hope this helps !!!

how to retreive data depends on another data ...

my application will add and delete and update records in db

my problem is when to update and delete ..

I have two dropdownboxes one to retreive servers and one to retreive dbs in the server ...

SqlDataAdapter daa =

new SqlDataAdapter("SELECT * FROM DB_tbl"+","+"Servers_tbl where Servers_tbl.Server_Name == '"+ DropDownList4.SelectedValue +"' ", sqlConnection1 );

this is the structure of each table >>

servers_tbl : SRV_ID,Server_name

DB_teb : DB_ID,SRV_ID,DB_name

the dropdown box retreive just the names ... so what is the base of where part of the command

hint : the DB_tbl does not have the server name but has the sever id

Hi Adorer,

Based on my understanding, you need to know when to issue a data bind on the dropdownlist which displays db names.

I think you can handle the SelectedIndexChanged event of the Server DropDownList to fill the db names.

You can use 2 SqlDataSources and use a ControlParameter to achieve this. Also, make sure you have set AutoPostback property to true.

HTH. If this does not answer you question, please feel free to mark it as Not Answered and post your reply. Thanks!