Filter Crystal Report using combobox in VB.net with source code

 

Imports System.Data.SqlClient

Public Class Form1

    Dim conn As New SqlConnection("Data Source=.;Initial Catalog=sports;Integrated Security=true")

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        combo1()

        report1()

    End Sub

    Private Sub combo1()

        conn.Open()

        Dim strsql As New SqlCommand("Select Distinct sport From Table1", conn)

        Dim myreader As SqlDataReader = strsql.ExecuteReader

        ComboBox1.Items.Clear()

        While myreader.Read()

            ComboBox1.Items.Add(myreader("sport"))

        End While

        conn.Close()

    End Sub

    Private Sub report1()

        Dim cmd As New SqlCommand("Select * From Table1 Where sport Like '%" + ComboBox1.Text + "%'", conn)

        Dim da As New SqlDataAdapter(cmd)

        Dim dt As New DataTable

        da.Fill(dt)

        Dim cr_report As New CrystalReport1

        cr_report.SetDataSource(dt)

        CrystalReportViewer1.ReportSource = cr_report

        CrystalReportViewer1.Zoom(90%)

        CrystalReportViewer1.Refresh()

    End Sub

    Private Sub ComboBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox1.SelectedIndexChanged

        report1()

    End Sub

End Class

c# tutorial for beginners - hotel management system project

 using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Windows.Forms;

using System.Data.SqlClient;

namespace Book__room

{

    public partial class Form3 : Form

    {

        public Form3()

        {

            InitializeComponent();

        }

        SqlConnection conn = new SqlConnection("Data Source=.;Initial catalog=hotel;Integrated Security=true");

        string strsql;

        SqlCommand cmd;

        private void button1_Click(object sender, EventArgs e)

        {

            strsql = "insert into table1(room,startdate,enddate)Values(@room1,@start1,@end1)";

            cmd = new SqlCommand(strsql, conn);

            cmd.Parameters.AddWithValue("room1", comboBox1.Text);

            cmd.Parameters.AddWithValue("start1", dateTimePicker1.Value.ToString());

            cmd.Parameters.AddWithValue("end1", dateTimePicker2.Value.ToString());

            conn.Open();

            cmd.ExecuteNonQuery();

            conn.Close();

            MessageBox.Show("The room reserved successfully");

        }

        private void button2_Click(object sender, EventArgs e)

        {

            conn.Open();

            strsql = "Select * from table1 Where room=@room1 " +

                "And ((@start1 >= startdate And @end1 <= enddate)" +

                "Or (@start1 <= startdate And @end1 >= enddate)" +

                "or (@start1 > startdate And @end1 > enddate And @start1<enddate)" +

                "or (@start1 < startdate And @end1 < enddate And @end1>startdate))";

            cmd = new SqlCommand(strsql, conn);

            cmd.Parameters.AddWithValue("room1", comboBox1.Text);

            cmd.Parameters.AddWithValue("start1", dateTimePicker1.Value.ToString());

            cmd.Parameters.AddWithValue("end1", dateTimePicker2.Value.ToString());

            SqlDataReader myreader = cmd.ExecuteReader();

            if (myreader.Read())

            {

                MessageBox.Show("The room reserved From: " + myreader.GetValue(2).ToString() + " To: " + myreader.GetValue(3).ToString());

            }

            else

            {

                MessageBox.Show("The room not reserved before");

            }

            conn.Close();

        }

    }

}

VB.net: count and Insert Checked rows from DataGridView to sql Database with source code

 

Imports System.Data.SqlClient

Public Class Form2

    Dim conn As New SqlConnection("Data Source=.;Initial Catalog=students;Integrated Security=true")

    Dim countcheck As Integer

    Private Sub DataGridView1_CellValueChanged(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellValueChanged

        If e.RowIndex < 0 Then Return

        Dim ischecked As Boolean = CBool(DataGridView1.Rows(e.RowIndex).Cells(0).Value)

        If ischecked Then

            countcheck += 1

        Else

            countcheck -= 1

        End If

        Label1.Text = countcheck

    End Sub

    Private Sub DataGridView1_CurrentCellDirtyStateChanged(sender As Object, e As EventArgs) Handles DataGridView1.CurrentCellDirtyStateChanged

        If DataGridView1.IsCurrentCellDirty Then

            DataGridView1.CommitEdit(DataGridViewDataErrorContexts.Commit)

        End If

    End Sub

    Private Sub Form2_Load(sender As Object, e As EventArgs) Handles MyBase.Load

               Dim cmd As New SqlCommand("Select * from table1", conn)

        Dim da As New SqlDataAdapter(cmd)

        Dim dt As New DataTable

        da.Fill(dt)

        DataGridView1.DataSource = dt

               Dim checkboxcol As New DataGridViewCheckBoxColumn

        checkboxcol.Width = 90

        checkboxcol.Name = "checkboxcol"

        checkboxcol.HeaderText = "Select student"

        DataGridView1.Columns.Insert(0, checkboxcol)

        DataGridView1.AllowUserToAddRows = False

    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

        If countcheck > 0 Then

            For Each row As DataGridViewRow In DataGridView1.Rows

                Dim select1 As Boolean = Convert.ToBoolean(row.Cells("checkboxcol").Value)

                If select1 Then

                    Dim cmd2 As New SqlCommand("Insert Into table7(firstname,lastname,marks)Values(@firstname,@lastname,@marks)", conn)

                    cmd2.Parameters.AddWithValue("firstname", row.Cells("firstname").Value)

                    cmd2.Parameters.AddWithValue("lastname", row.Cells("lastname").Value)

                    cmd2.Parameters.AddWithValue("marks", row.Cells("marks").Value)

                    conn.Open()

                    cmd2.ExecuteNonQuery()

                    conn.Close()

                End If

            Next

            MessageBox.Show("Data Inserted successfully")

        Else

            MessageBox.Show("Please select students")

        End If

    End Sub

End Class

Retrieve data from sql server database in asp.net using VB.net

Imports System.Data.SqlClient

Partial Class _Default

    Inherits System.Web.UI.Page

    Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

        Dim constring As String = ConfigurationManager.ConnectionStrings("sportsconn").ConnectionString

        Dim conn As New SqlConnection(constring)

        conn.Open()

        Dim cmd As New SqlCommand("Select name1,age,sport,points,Format(date_reg, 'dd/MM/yyyy') As date_reg From table1 Where id=@id", conn)

        cmd.Parameters.AddWithValue("id", TextBox1.Text)

        Dim myreader As SqlDataReader

        myreader = cmd.ExecuteReader()

        If myreader.Read() Then

            Label7.Visible = False

            TextBox2.Text = myreader("name1").ToString()

            TextBox3.Text = myreader("age").ToString()

            TextBox4.Text = myreader("sport").ToString()

            TextBox5.Text = myreader("points").ToString()

            If Not (myreader.IsDBNull(myreader.GetOrdinal("date_reg"))) Then

                TextBox6.Text = myreader("date_reg")

            Else

                TextBox6.Text = ""

            End If

        Else

            Label7.Visible = True

            TextBox2.Text = ""

            TextBox3.Text = ""

            TextBox4.Text = ""

            TextBox5.Text = ""

            TextBox6.Text = ""

        End If

conn.close()

    End Sub

End Class 

Code in web config:

 <connectionStrings>

    <add name="sportsconn" connectionString="Data Source=.;Initial Catalog=sports;Integrated Security=true" providerName="System.Data.Sqlclient" />

  </connectionStrings>

Visual Basic.net: insert date and check a date between two dates in access database(system book room in a hotel)

 Imports System.Data.OleDb

Public Class Form6

    Dim conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\dd.mdb")

    Private Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim strsql1 As String

        strsql1 = "insert into table1(room1,startdate1,enddate1)Values(@room,@start1,@end1)"

        Dim cmd1 As New OleDbCommand(strsql1, conn)

        cmd1.Parameters.AddWithValue("@room", OleDbType.VarChar).Value = ComboBox1.Text

        cmd1.Parameters.AddWithValue("@start1", OleDbType.Date).Value = DateTimePicker1.Value.ToString

        cmd1.Parameters.AddWithValue("@end1", OleDbType.Date).Value = DateTimePicker2.Value.ToString

        conn.Open()

        cmd1.ExecuteNonQuery()

        conn.Close()

        MessageBox.Show("The Room reserved successfully")

    End Sub

  

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

        conn.Open()

        Dim strsql2 As String

        strsql2 = " Select * from table1 where room1=@room " & _

            " And ((@start1>=startdate1 and @end1<= enddate1)" & _

            " or (@start1<=startdate1 and @end1>= enddate1)" & _

            " or (@start1>startdate1 and @end1> enddate1 and @start1 <enddate1 )" & _

            " or (@start1<startdate1 and @end1< enddate1 and @end1 >startdate1 ))"

        Dim cmd2 As New OleDbCommand(strsql2, conn)

        cmd2.Parameters.AddWithValue("@room", OleDbType.VarChar).Value = ComboBox1.Text

        cmd2.Parameters.AddWithValue("@start1", OleDbType.Date).Value = DateTimePicker1.Value.ToString

        cmd2.Parameters.AddWithValue("@end1", OleDbType.Date).Value = DateTimePicker2.Value.ToString

        Dim myreader As OleDbDataReader = cmd2.ExecuteReader

        If (myreader.Read()) Then

            MessageBox.Show(" The room reserved from: " & (myreader.GetValue("2").ToString & " to: " & (myreader.GetValue("3").ToString)))

        Else

            MessageBox.Show(" The Room not reserved before")

        End If

        conn.Close()

    End Sub

End Class

Programming in C# Insert data into sql Database and fix an error Violation of PRIMARY KEY

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Windows.Forms;

using System.Data.SqlClient;

namespace Insert_button2

{

    public partial class Form1 : Form

    {

        public Form1()

        {

            InitializeComponent();

        }

        private void button1_Click(object sender, EventArgs e)

        {

            SqlConnection conn = new SqlConnection("Data source=.;initial catalog=names3;integrated security=true");

            conn.Open();

            SqlCommand cmd2 = new SqlCommand("Select username from table2 where username=@username", conn);

            cmd2.Parameters.AddWithValue("username", textBox2.Text);

            SqlDataReader myreader = cmd2.ExecuteReader();

            if (myreader.Read())

            {

                conn.Close();

                MessageBox.Show("Duplicate username");

            }

            else

            {

                conn.Close();

                SqlCommand cmd = new SqlCommand("Insert into table2(name1,username)Values(@name1,@username)", conn);

                cmd.Parameters.AddWithValue("name1", textBox1.Text);

                cmd.Parameters.AddWithValue("username", textBox2.Text);

                conn.Open();

                cmd.ExecuteNonQuery();

                conn.Close();

                MessageBox.Show("Data inserted successfully");

            

            }

           

        }

    }

}

 

Create a Application to connect access database with C# - Complete Course

 

Contents:

Add controls to your form 00:00 Delete last blank row in datagridview 09:26 Browse button 09:42 Create table database 14:21 Add Dataset and relate it with controls 15:37 change columns header text datagridview 20:52 New button 23:16 Format DateTime column in a DataGridView 25:04 Save or update button 26:17 Search button 29:34 Reset button 32:26 Remove button 33:24 Previous button 34:16 Next button 34:48 First button 35:06 Last button 35:25 Close button 35:41