[C#] Failing to use SCOPY_IDENTITY()

Dear Reader,

Currently I am building an application for a theme park where I work as a trainee for school, one project for me is to rebuild all the hundreds of databases into a few sql driven application's. Now I got a problem whit the use of SCOPE_IDENTITY(). Because the data has to be correct before inserting it into the database I use the transact features of .NET and I create 1 SQL string wich I dump in that method. The problem is that I can't be able to use the value of SCOPE_IDENTITY() for some reason, maybe you guys see a mistake in the actual (dynamic) query:
Here is the query built up by my program to write the data (of a single form) into the database:

DECLARE @.OID int;
INSERT INTO Medisch (med_za_ID, med_WeekNr, med_Enen, med_Bijzonderheden, med_AfwijkendGedrag, med_SexGedrag, med_GemAdemhaling, med_GemHoesten, med_Temperatuur, med_Conditie, med_BloedGeprikt, med_Cupje, med_BasisVis, med_Eetlust, med_GemGewicht)VALUES(3,1123,,'','','',,,,'','False','False',45,'',);
SELECT @.OID = SCOPE_IDENTITY();
INSERT INTO Medisch_Medicijnen_Details (mmd_mmt_ID, mmd_med_ID, mmd_Hoeveelheid, mmd_Aantal) VALUES(@.OID, 2,23, 23 );

Everything else works unless the SCOPE_IDENTITY() things.
I hope someone can help me out fixing this mistake.
Tnx in advanced,

Grz.
StefanEXACTLY what is your problem? Does Medisch have an IDENTITY column? Why are you not passing any values for some rows (not sure this syntax would work). Why not just not include the column names in the column list?|||The problem is that SCOPE_IDENTITY() doesn't seem to return any values.
They don't have all valeus, because I did not fill in all the data in the form so it will insert a null value. The string is ok, only the part of returing the identity. (Medisch has an autoincrement column called med_ID).|||To make it clear, you are saying that @.OID is null?|||No, it does not seem to get any value :P. The following error ocurse:
Incorrect syntax near ',' wich occurs in the part

VALUES(@.OID, "+medI

so for some reason it does not want to get the value in the sqlvariabele @.OID.

For clearance, it will put that string in my first post inot the following method:

public bool SqlNieuweInvoer(string sqlDatabase, string sqlQuery)
{
SqlConnection conData = new SqlConnection("server="+strServerNaam+";" + "database="+sqlDatabase+";Trusted_Connection=yes");
SqlCommand comData = new SqlCommand(sqlQuery, conData);
conData.Open();
SqlTransaction TranData = conData.BeginTransaction();
comData.Transaction = TranData;
try
{
comData.ExecuteNonQuery();
TranData.Commit();
return true;
}
catch (Exception e)
{
TranData.Rollback();
MessageBox.Show(Convert.ToString(e));
return false;
}
finally
{
conData.Close();
}
}

|||Is this an error when you compile? If so, then it has NOTHING to do with the SQL Syntax, it is an error in how you are building the string in C# code.|||This error occurs when running the application.
The user is going to fill in the form and if he presses the "OK" button of that form the query will be generated (for example it has to indiviualy add the different kinds of medicine related to the medical week rapport (that's why I need the SCOPE_IDENTITY() function to give me the internal auto generated increment value of that medical report).|||This is the code that builts the query bye the way:

private string BouwQuery()
{
cstrInvoerQuery = cstrInvoerQuery + "DECLARE @.OID int;";
cstrInvoerQuery = cstrInvoerQuery + "INSERT INTO Medisch (med_za_ID, med_WeekNr, med_Enen, med_Bijzonderheden, med_AfwijkendGedrag, med_SexGedrag, med_GemAdemhaling, med_GemHoesten, med_Temperatuur, med_Conditie, med_BloedGeprikt, med_Cupje, med_BasisVis, med_Eetlust, med_GemGewicht)" +
"VALUES("+this.DierID+","+tbWeekNr.Text+","+tbEnen.Text+",'"+tbBijzonderheden.Text+"','"+tbAfwijkendGedrag.Text+"','"+tbSexGedrag.Text+"',"+tbAdemfrequentie.Text+","+tbHoesten.Text+","+tbTemperatuur.Text+",'"+gcbConditie.Text+"','"+cbBloedgeprikt.Checked+"','"+cbCupje.Checked+"',"+tbBasisVis.Text+",'"+tbEetlust.Text+"',"+tbGewicht.Text+");";
cstrInvoerQuery = cstrInvoerQuery + " SELECT @.OID = SCOPE_IDENTITY();";
string[] test = new string[] {};
int medID;
string strDelimiter = "\t[";
char[] delimiter = strDelimiter.ToCharArray();
foreach (string Item in lbMedicijnenSupplementen.Items)
{
if (!Item.StartsWith("Naam:"))
{
test = Item.Split(delimiter, 6);
medID = VerkrijgMedicijnSupplementID(test[0]);
if (medID != 0)
{
cstrInvoerQuery = cstrInvoerQuery + "INSERT INTO Medisch_Medicijnen_Details (mmd_mmt_ID, mmd_med_ID, mmd_Hoeveelheid, mmd_Aantal) VALUES(@.OID, "+medID+","+test[1]+", "+test[2]+");";
}
}
}
if(cbBloedgeprikt.Checked)
{
cstrInvoerQuery = cstrInvoerQuery + "UPDATE OmgevingsWaardes SET ow_LaatstBloedGeprikt='"+tbWeekNr.Text+"' WHERE ow_za_ID = "+this.DierID+";";
}
tbBijzonderheden.Text = cstrInvoerQuery;
return cstrInvoerQuery;
}

Maybe this helps you find me an answer :).|||Your problem is this. You are calling ExecuteNonQuery. This DOES NOT expect a result set to be returned. However, you ARE returning a result set (that is what SELECT @.OID... does).

Use ExecuteReader and get back a DataReader, or better use ExecuteScaler() and the return from that method can be cast to an integer.|||That might be helpfull, I thought the whole query was being excuted on the SQL server, but they actually being excecuted all @. once? That explaines allot, thanks, I will go and trie the other 2 things :).|||Whoohoo!!! I used the scalar 1 and it finally works, thank you very very much :).