Tech-Today
Platform: Windows Mobile, SqlCE2.0
Problem:
You want to get the id of the last query you insert.
Solution:
Using the @@identity you have to execute your queries like this:
Note:
1.) @@IDENTITY returns SqlDecimal so you need to convert it to integer
- Insert An Md5 Password In Mssql
To md5 a string in mssql we execute this query: select HASHBYTES('MD5', 'password') //But this has a problem because it returns a VarBinary data type which is a garbled text. So it should be converted first to NVarchar: SELECT CONVERT(NVARCHAR(32),HashBytes('MD5',...
- How To Connect Msaccess Database From Java Using Jdbc Driver
The method is almost similar to my previous code, and like that it's better to explain it by code: private static Connection getConnection() { String driver = "sun.jdbc.odbc.JdbcOdbcDriver"; String url = "jdbc:odbc:anime"; //anime is the database...
- How To Connect Mysql Or Postgresql Server From Java Using Jdbc Driver
How to connect MySql or PostgreSql Server from Java using JDBC Driver I think the best answer to this is through coding: private static Connection getConnection() { Connection conn = null; try { Class.forName("com.mysql.jdbc.Driver"); //conn = DriverManager.getConnection("jdbc:postgresql://127.0.0.1:5432/anime",...
- Select Top, Limit Workaround On Sqlce 2.0 Or Limiting The Number Of Rows/getting A Range From A Dataset
Problem: -In SQLCE 2.0 it is documented that 2 of the common used keywords are not supported in gettings a range of records from a dataset. They are LIMIT and TOP which are use this way: LIMIT: SELECT * FROM tableA LIMIT 5, 10; This query will select...
- Create Auto Increment Primary Key On Sql Server Compact Database
Just examine this script: CREATE TABLE user ( user_id INT IDENTITY(1, 1) NOT NULL PRIMARY KEY, user_name NVARCHAR(50) ) Regarding IDENTITY(1, 1) and SQLCE Data Type, refer to the following article: http://www.codeproject.com/KB/mobile/DBApplicationsForPocketPC.aspx...
Tech-Today
SQLCE get last insert id, convert SqlDecimal to Int32
Platform: Windows Mobile, SqlCE2.0
Problem:
You want to get the id of the last query you insert.
Solution:
Using the @@identity you have to execute your queries like this:
SqlCeConnection conn = null;
try {
conn = new SqlCeConnection(GetConnection());
conn.Open();
//execute the insert statement
SqlCeCommand cmd = conn.CreateCommand();
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
//get the last insert id
cmd.CommandText = "SELECT @@IDENTITY"; //using identity
//take note ExecuteScalar return an object, and in sqlce @@identity returns SqlDecimal
SqlDecimal x = (SqlDecimal)cmd.ExecuteScalar();
//so my way of converting it to int is catch the value as SqlDecimal
//convert that to string and then convert the string to int
//this way works for me
id = Convert.ToInt16(x.ToString());
}
Note:
1.) @@IDENTITY returns SqlDecimal so you need to convert it to integer
- Insert An Md5 Password In Mssql
To md5 a string in mssql we execute this query: select HASHBYTES('MD5', 'password') //But this has a problem because it returns a VarBinary data type which is a garbled text. So it should be converted first to NVarchar: SELECT CONVERT(NVARCHAR(32),HashBytes('MD5',...
- How To Connect Msaccess Database From Java Using Jdbc Driver
The method is almost similar to my previous code, and like that it's better to explain it by code: private static Connection getConnection() { String driver = "sun.jdbc.odbc.JdbcOdbcDriver"; String url = "jdbc:odbc:anime"; //anime is the database...
- How To Connect Mysql Or Postgresql Server From Java Using Jdbc Driver
How to connect MySql or PostgreSql Server from Java using JDBC Driver I think the best answer to this is through coding: private static Connection getConnection() { Connection conn = null; try { Class.forName("com.mysql.jdbc.Driver"); //conn = DriverManager.getConnection("jdbc:postgresql://127.0.0.1:5432/anime",...
- Select Top, Limit Workaround On Sqlce 2.0 Or Limiting The Number Of Rows/getting A Range From A Dataset
Problem: -In SQLCE 2.0 it is documented that 2 of the common used keywords are not supported in gettings a range of records from a dataset. They are LIMIT and TOP which are use this way: LIMIT: SELECT * FROM tableA LIMIT 5, 10; This query will select...
- Create Auto Increment Primary Key On Sql Server Compact Database
Just examine this script: CREATE TABLE user ( user_id INT IDENTITY(1, 1) NOT NULL PRIMARY KEY, user_name NVARCHAR(50) ) Regarding IDENTITY(1, 1) and SQLCE Data Type, refer to the following article: http://www.codeproject.com/KB/mobile/DBApplicationsForPocketPC.aspx...