.NET GENES

To get last inserted unique ID in MySQL, use LAST_INSERT_ID(). C# Code: 1 2 3 4 string insert_product_query = ( "INSERT INTO PRODUCTS (Stock) VALUES (5); SELECT LAST_INSERT_ID()" ); MySqlCommand cmd_query = new MySqlCommand(insert_product_query, objConn); int idResult = Convert.ToInt32(cmd_query.ExecuteScalar()); txtID.Text = idResult.ToString(); MySQL Docs: Getting Last Unique ID in MySQL

Based from Visual Basic Forums, I learned a tip on using Crosstab/Pivot queries. This is presented with SQL Server Execution Plan. It's better to use the concept of the second one compared with the first one. SELECT SUM ( CASE WHEN DATEDIFF( DAY , upload_package_received_date, GETDATE()) = 0 THEN 1 ELSE 0 END ) AS Today, SUM ( CASE WHEN DATEDIFF( DAY , upload_package_received_date, GETDATE()) <= 7 THEN 1 ELSE 0 END ) AS Last_Week, SUM ( CASE WHEN DATEDIFF( DAY , upload_package_received_date, GETDATE()) <= 30 THEN 1 ELSE 0 END ) AS [30 Days Ago] FROM temp_uploadpackage Second Example SELECT ( SELECT COUNT (1) FROM temp_uploadpackage WHERE DATEDIFF( DAY , upload_package_received_date, GETDATE()) = 0) AS Today, ( SELECT COUNT (1) FROM temp_uploadpackage WHERE DATEDIFF( DAY , upload_package_received_date, GETDATE()) <= 7) AS Last_Week, ( SELECT COUNT (1) FROM temp_uploadpackage WHERE DATEDIFF...

Hi, In reference to the previous post on DataGridView paging using C# Datagridview paging using BindingSource in C# , I developed a Visual Basic.NET version for VB.NET Developers. Main Form Class: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 Option Infer On Imports System.Configuration Imports System.ComponentModel Imports MySql Imports MySql.Data Imports MySql.Data.MySqlClient Public Class FBinding Public Property TotalRecords() As Integer Public Const PageSize = 10 Private sourceData As New List( Of String ) Private dtSource As New DataTable Dim page As New PageOffsetList() Public Sub New() ' This call is required by the designer. InitializeComponent() ' Add any initiali...

Hello, VB.NET Version Here: DataGridview Paging Using BindingSource And BindingNavigator In VB.NET Here's a simple way to integrate paging in Datagridview using BindingNavigator and BindingSource. The original source can be found here: How can we do paging in datagridview in winform . I made some modifications to simulate loading of thousands of records from a database. Main Form Class: public partial class FBinding : Form { public static int totalRecords { get ; set ; } public const int pageSize = 10; private List< string > sourceData = new List< string >(); private DataTable dtSource = new DataTable(); public FBinding() { InitializeComponent(); bindingNav.BindingSource = bindingWebsite; bindingWebsite.CurrentChanged += new EventHandler(bindingWebsite_CurrentChanged); SetSource(); bindingWebsite.DataSource = new PageOffsetList(); } void bindingWebsite_CurrentChanged( object sender, EventAr...

Good evening! The code above has a database field called desc which is a reserved word in SQL as an order by criteria. dbQuery = String.Format( @"INSERT INTO accom_temp_hotelagencies(hotel_id, agency_id, hotel_webid, desc)VALUES(?HotelId, ?AgencyId, ?HotelWebId, ?Description)" ); The solution is to enclose the field name desc with an acute or back quote. as shown in the code below: dbQuery = String.Format( @"INSERT INTO accom_temp_hotelagencies(hotel_id, agency_id, hotel_webid, `desc`)VALUES(?HotelId, ?AgencyId, ?HotelWebId, ?Description)" ); Cheers!

There's a weird scenario when retrieving table names from a specific database, upon querying, using connection.Open() in C#, an error such as "Unknown database 'database_name'" shows in the exception object. I was simply checking the database name using MySQL Browser and found out that the database name starts with a space. So, after thinking about it, I decided to modify the app.config and changed the value of the connection string by enclosing the name with single quotes to include the space character of the database name. Not working app.config setting: <add key= "try" value= "server=127.0.0.1;database=database_v1;Uid=james;pwd=pass123;" /> Working app.config setting with database name enclosed in single quotes: <add key= "try" value= "server=127.0.0.1;database=' database_v1';Uid=james;pwd=pass123;" /> Note: This fix is applied towards database names that starts/ends with space. Cheers! Th...

Here's a query to count number of repeating records based from union all query. Union all query does not remove duplicate records. Here's a sample script: select resultsduplicate.url as job_item, count (resultsduplicate.url) as repeat_occurence from ( SELECT url FROM mainTable where id = 47 and url like '%testsite.com%' and _active = 1 UNION ALL SELECT url FROM detailTable where id = 47 and url like '%testsite.com%' and _active = 1 )resultsduplicate group by resultsduplicate.url having ( count (resultsduplicate.url) >1); Original Source From (Stackoverflow.com)

In this scenario, we have to convert a timezone from Central USA to Hongkong time. I took note of DST here from (-06:00) to (-05:00). Here's a sample query. SELECT CONVERT_TZ( '2012-09-12 04:35:00' , '-05:00' , '+08:00' ) as convert_to_ph; Cheers!

Here is a query to count records gathered in every hour of a given day. The date_visited is a 24 hour time format which is of timestamp data type. The idea came from a fellow developer.. select hour(time(date_visited)) as hour_of_day_24_hour_format, count (hour(time(date_visited))) as count_per_hour from tblpropsale_v2 where date(date_visited) = '2012-05-07' and web_id = 642 and is_visited = 1 group by hour(time(date_visited)) order by hour(time(date_visited)) asc ;

Here's a query that has a subquery in which the result is aliased as inner table and then the outer query compares the result from the subquery against the field in the outer query. SELECT id,w_id, start , f_count, count_percent FROM tblLogs, ( select max (id) as max_id from tblLogs where w_id = 92 and categ = 'bike' and date( start ) = '2012-01-24' ) as inner_table WHERE DATE( start ) = '2012-01-24' AND w_id =92 AND categ = 'bike' and err = 'none' and remark = '' and inner_table.max_id = tblLogs.id;

Here's an example of equi join select distinct site_scripts._id, crw_site.site, crw_subcategory.subcategory from site_scripts, crw_site , crw_subcategory where site_scripts.subcategory_id = 8 and crw_site._id = site_scripts._id and crw_subcategory.subcategory_id = site_scripts.subcategory_id and crw_scripts.country_id = 1 and crw_site._active = 1 order by crw_site.site; Note: The concept can be used as an alternate to inner join...

Source: MySQL bug Forum Steps 1. Open MySQL Browser 2. Click Tools menu 3. Click Options 4. Choose Browser 5. Check Show Advanced Toolbars 6. Click Apply 7. Click close 8. Transactions Toolbar appears on MySQL Browser 9. Click the Transaction button on the transaction toolbar everytime you execute the query Sample query: Set @ names = 'james' ; select @ names ;

Software Used: Mysql 5.0 connector/Visual Studio 2010 We encounter a tricky problem when showing mysql database column value with collation of typelatin1_swedish_ci. The value retrieved from the column and shown on a c# string variable is not the same with what is in the database. The ascii characters has been stripped off in the string variable. Here is a string variable with special character apostrophe stored in a column with latin1swedish collation. Ex. Take on bank robbers, beat the bad guys or shoot it out with the police in the quest for freedom – all in the name of fun! Doesn’t sound like your average day out, but today we’re making this happen! string sql = "select convert(description using utf8) as description from your_table where id='554be79124998120b8c3505d47c88c2d'" ; Sample C# Implementation private void TestDeals() { DataTable dt = new DataTable(); string conn_s = ConfigurationManager.AppSettings[ "LocalDB" ]; stri...

Here's a simple demonstration of sql in MySQL where you can retrieve a record from a previous date. select sum (data_gathered) as data_gatherer, name from tblctivity where name like '%prgGatherer1.xml%' and (date(rec_date) = (CURDATE() - 1));

Assuming QuantityPerUnit is the field to be tested. -- return products with no null values select * from products where QuantityPerUnit is not null ; -- return products with null values select * from products where isnull (QuantityPerUnit);

To reset the seed of a MySQL table, perform alter table statement and set the AUTO_INCREMENT value to 1. ALTER TABLE tblProduct AUTO_INCREMENT = 1; Cheers!

Hello, To insert date object using C#.net in MySQL db,use the code below: string query = String.Format( "Insert into tbllistings(DateRegistered)" + "values(DATE_FORMAT({0}, '%Y-%m-%d %h:%m:%s'))" ,objJobs.DateRegistered); The key to the solution is using the MySQL Date format function.

Here's another version using logical operators in MySQL. 1: select date_format(date_purchased, '%m/%d/%Y' ) as dates, 2: url from dbcommerce.tblproducts 3: where date_format(date_purchased, '%m/%d/%Y' ) >= '04/30/2011' 4: and date_format(date_purchased, '%m/%d/%Y' ) <= '05/30/2011' 5: order by date_purchased asc ;

Here is an example to query MySQL Date Range given the date field is of type TimeStamp. The date_format() will remove the time part in comparing. select date_format(date_purchased, '%m/%d/%Y' ) as dates, url from dbCommerce.tblproducts where date_format(date_purchased, '%m/%d/%Y' ) between '04/30/2011' and '05/30/2011' order by date_purchased asc ;

Solution: Add Allow Zero to your Web.Config or App.Config <add key= "connectionString" value= "Database=your_db ;Data Source=localhost;User Id=root; Password=password;Allow Zero Datetime=True;" /> Cheers!