after i have inserted a row into the DB i am trying to get the last identity like this :
Dim myCommand2 As New SqlCommand("SELECT @@IDENTITY AS 'Identity'", )
Dim myReader2 As SqlDataReader
myReader2 = myCommand2.ExecuteReader()
where myConnection2 is the connection object
after i did this i dont underdsand how do i get the 'Identity' value?
i tried myReader2 ('Identity') but no luck
any 1 can help
thnaks in advance
pelegWell i dont understand what thos @@ do in sql query, any way if this Identity is 'int' you can use "Select Max(identity)" then you can use ExecuteScalar() as its one value then assign it to Integer variable
Dim Identity as Integer = myCommand2.ExecuteScalar()
you dont need to use DataReader as long you retrieve one valuewel @@IDENTITY return youthe last inser id!
ifi use Max(identity) and in the same time some 1 else will insert a row i wll recive incorrect id!well, did it work any way what i told you to do?!On the same connection that you used to insert, without closing the connection you would need to do the query on the next command.
So here is the basic idea, please excuse any errors in the code, it as meant as a guide and not to be compiled exactelly as is.
Hope this helps. Let me know if you have more questions.
string strInsert = "insert into table (col1, col2) values (val1, val2)";
string strIdentQuery = "select @@IDENTITY";
int Ident = 0;
using (SqlCommand objCommand = new SqlCommand(strInsert, MySqlConnection))
{
' Open the Sql Connection
MySqlConnection.Open();
' Run the Insert Command
objCommand.ExecuteNonQuery();
'Set the command query to the Identity Query
objCommand.CommandText = strIdentQuery;
' Get the returned Identity
Ident = Int32.Parse(ObjCommand.ExecureScalar());
'Close the connection
MySqlConnection.Close();
}thanks alot sark it worked!Glad to hear it worked for you. Happy coding.1 more question - how do i make a select and accessing each one of the field ?
assuming i have only 1 row in the recorset?
which function to use on asp.net?
thnaks in advance
peleg
(i dont want to put the result in a dataset but to use the values to my own needs)Your easiest way is to use a datareader.
string strSqlQuery = "select stringfield1, integerfield2 from tablename";
string strField1 = "";
string iField2 = "";
SqlDataReader objDataReader = null;
using (SqlCommand objCommand = new SqlCommand(strSqlQuery , MySqlConnection))
{
' Open the Sql Connection
MySqlConnection.Open();
' Run the query, attaching it to the reader
objDataReader = objCommand.ExecuteReader();
' Make sure you actually have a reader returned
if(!(objDataReader == null))
{
' Read from the dataobject, moving it to the first row
' If we were returning more than 1 row, you should use a "while" here instead of an "if" and you will loop through each of the records.
if (objDataReader.Read())
{
' Use the GetString method on the reader if you are returning a string from your query
strField1 = objDataReader.GetString(0);
' Use the GetInt32 method on the reader if you are returning an integer
iField2 = objDataReader.GetInt32(1);
' OR you can access the fields by name, and use the type parsers
strField1 = objDataReader["stringfield1"].ToString();
iField2 = Int32.Parse(objDataReader["integerField2"].ToString());
}
}
'Close the connection
MySqlConnection.Close();
}
Dim myCommand2 As New SqlCommand("SELECT @@IDENTITY AS 'Identity'", )
Dim myReader2 As SqlDataReader
myReader2 = myCommand2.ExecuteReader()
where myConnection2 is the connection object
after i did this i dont underdsand how do i get the 'Identity' value?
i tried myReader2 ('Identity') but no luck
any 1 can help
thnaks in advance
pelegWell i dont understand what thos @@ do in sql query, any way if this Identity is 'int' you can use "Select Max(identity)" then you can use ExecuteScalar() as its one value then assign it to Integer variable
Dim Identity as Integer = myCommand2.ExecuteScalar()
you dont need to use DataReader as long you retrieve one valuewel @@IDENTITY return youthe last inser id!
ifi use Max(identity) and in the same time some 1 else will insert a row i wll recive incorrect id!well, did it work any way what i told you to do?!On the same connection that you used to insert, without closing the connection you would need to do the query on the next command.
So here is the basic idea, please excuse any errors in the code, it as meant as a guide and not to be compiled exactelly as is.
Hope this helps. Let me know if you have more questions.
string strInsert = "insert into table (col1, col2) values (val1, val2)";
string strIdentQuery = "select @@IDENTITY";
int Ident = 0;
using (SqlCommand objCommand = new SqlCommand(strInsert, MySqlConnection))
{
' Open the Sql Connection
MySqlConnection.Open();
' Run the Insert Command
objCommand.ExecuteNonQuery();
'Set the command query to the Identity Query
objCommand.CommandText = strIdentQuery;
' Get the returned Identity
Ident = Int32.Parse(ObjCommand.ExecureScalar());
'Close the connection
MySqlConnection.Close();
}thanks alot sark it worked!Glad to hear it worked for you. Happy coding.1 more question - how do i make a select and accessing each one of the field ?
assuming i have only 1 row in the recorset?
which function to use on asp.net?
thnaks in advance
peleg
(i dont want to put the result in a dataset but to use the values to my own needs)Your easiest way is to use a datareader.
string strSqlQuery = "select stringfield1, integerfield2 from tablename";
string strField1 = "";
string iField2 = "";
SqlDataReader objDataReader = null;
using (SqlCommand objCommand = new SqlCommand(strSqlQuery , MySqlConnection))
{
' Open the Sql Connection
MySqlConnection.Open();
' Run the query, attaching it to the reader
objDataReader = objCommand.ExecuteReader();
' Make sure you actually have a reader returned
if(!(objDataReader == null))
{
' Read from the dataobject, moving it to the first row
' If we were returning more than 1 row, you should use a "while" here instead of an "if" and you will loop through each of the records.
if (objDataReader.Read())
{
' Use the GetString method on the reader if you are returning a string from your query
strField1 = objDataReader.GetString(0);
' Use the GetInt32 method on the reader if you are returning an integer
iField2 = objDataReader.GetInt32(1);
' OR you can access the fields by name, and use the type parsers
strField1 = objDataReader["stringfield1"].ToString();
iField2 = Int32.Parse(objDataReader["integerField2"].ToString());
}
}
'Close the connection
MySqlConnection.Close();
}