Posts by Hardz

Hi Zelrick,

I just thought that the data that you had provided is in a standard form that consists of 5 digits numerical number. Yes of course an error would occur if there's a blank or the cell length is less than the specified length, because of its calculation to determine the location of the character. But by using a conditional statement, we can do it this way:

Dim cell1, cell3, col1, col2, col3, col4 As String
                    For Each row As DataRow In dts.Tables("[Sheet1$]").Rows
                        cell1 = row(0).ToString()
                        cell3 = row(2).ToString()
                        col1 = ""
                        col2 = ""
                        col3 = ""
                        col4 = ""
                        If cell1.Length = 5 Then
                            col1 = cell1.Substring(0, cell1.Length - 3)
                            col2 = cell1.Substring(2, cell1.Length - 2)
                        End If
                        If cell3.Length = 5 Then
                            col3 = cell3.Substring(0, cell3.Length - 4)
                            col4 = cell3.Substring(1, cell3.Length - 2)
                        End If
                        DataGridView1.Rows.Add(col1, col2, col3, col4)
                    Next

The only problem with this code if the cell length is less than or more than 5. But I think it is another problem and out of scope for this discussion that needs for creation to a new thread, so that others can help and contribute.

Thanks,

Hardz

@zelrick: Thanks for the code that you have provided.

@teze: Hi, How is it going so far? Do you still having trouble loading dgv value to a dgv to other form? Anyway, I have here another sample on how to load dgv to dgv to another form.

@ Form1:

Dim dt As DataTable
    'Handle form load event to initialize dgv
    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        dt = New DataTable()
        'using 3 columns in a table
        dt.Columns.Add("ColA")
        dt.Columns.Add("ColB")
        dt.Columns.Add("ColC")

        DataGridView1.DataSource = dt

        'loading sample data to a dgv which is consists of 5 rows
        For i As Integer = 1 To 5
            dt.Rows.Add("A" & i, "B" & i, "C" & i)
        Next
    End Sub
    Dim f2 As Form2
    'There have been many options on how to load the dgv to another dgv:
    '1. Load directly the datatable value of dgv1 to dgv2 through its datasource. By handling button1 click event:
    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        f2 = New Form2()
        'calling the function to load datatable directly to a dgv datasource of form2.
        f2.loadDgv1ToDgv2Method1(dt)
        f2.ShowDialog()
    End Sub
    '2. Or this method that uses loop to load the dgv1 datarows to dgv2, which is I think a decent one. So by handling another button click event:
    Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
        f2 = New Form2()
        'initializing dgv at form2
        f2.initializeDgvAtFrm2()
        'loading dgv datarows to a datatable of dgv2.
        For Each row As DataRow In dt.Rows
            f2.loadDgv1ToDgv2Method2(row("ColA").ToString(), row("ColB").ToString(), …

Hi,

What have you done so far? Is your dgv a databound or not? Anyway, please see this link on passing value from dgv to another.

Hardz

Hi,

Since you don't have a column that references the date field and analysis header field, you have to manually create a reference for these two fields. For this sample I created an autogenerated number labeled as Category that would references the two, then combined using union clause.

;with cte As (
Select Date, Pot, Si, Fe, row_number() OVER (ORDER BY (SELECT 0)) As Category From YourTable Where Pot != '' And Pot Is Not Null
Union All
Select Date, Pot, Si, Fe, row_number() OVER (ORDER BY (SELECT 0)) As Category From YourTable Where Pot = '' Or Pot Is Null)

Select Max(Date) As Date, Max(Pot) As Pot, Min(Si) As Si, Min(Fe) As Fe From cte Group By Category

Hi,

The above sample code is not safe and prone to sql injection. By using a parameterized query:

Dim idate As String = "07/03/2015"
        Dim query As String = "SELECT DISTINCT * FROM workStartTable WHERE StartBy Between @ And @ + ' 23:59:59' "

'your code initialization for connection string, Oledbcommand, etc.
'
'
'assuming that cmd = oledbcommand so:
cmd.Parameters.AddWithValue("StartBy", idate)

Hi,

I think the better way to query a date from MS access database is to use a range or between operator.

Dim idate As String = "07/03/2015"
        'using range
        Dim query As String = "SELECT DISTINCT * FROM workStartTable WHERE StartBy Between #" & idate & "# And #" & idate & " 23:59:59" & "# "

Or you can also use this query:

Dim query As String = "SELECT DISTINCT * FROM workStartTable WHERE StartBy >= #" & idate & "# And StartBy <= #" & idate & " 23:59:59" & "# "

Hardz

Hi,

Just apply the new code to your button click event and remove/comment the old one and see what happened. Because just as I told a while ago, that the old sample indicates a separate path for text file and excel file (but don't worry it is working).

Hi,

I don't know if this code would help, but process.start won't work against delimeted text. Unless, if you use this method: Workbooks.OpenText.

Dim ExcelApp As New Excel.Application
        ExcelApp.Visible = True
        ExcelApp.Workbooks.OpenText(Filename:=lblPath.Text,
            ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=False _
            , Space:=True, Other:=False)
        ExcelApp = Nothing
        GC.Collect()

As Reverend Jim said, it is better to Dispose orphaned excel.exe to avoid memory leak.

yes, as jwenting said 2K records are very small, compare to approximately 20M records that our(company) table holds (the audit trail/history table).I have no problem querying this table because it is properly indexed and properly referenced to another table using only an express version of Sql server 2012.

Hi,

The sample code above indicates a separate path for text file and excel file. Where in the text file was located based on your sample at C:\Users\abcde\Desktop\anothertest.txt, while I created a .xlsx file and put its value to lblPath or Label2. Maybe you can try this procedure to see if it is working. And I will try your requirements later on.

Hi,

You have to properly dipose the connection string and the Sql command to avoid memory leak, please see this link: Disposing Sql Connection. And be sure to use a parameterized query to avoid Sql Injection

        Dim con As New SqlClient.SqlConnection("Server=.\SQLEXPRESS;Initial Catalog=RCS;Integrated Security=True")
        Dim cmd As New SqlCommand()
        Try
            con.Open()
            Dim da As New SqlDataAdapter
            cmd.CommandText = "select r.Surname, r.First_name, r.Sex, c.type from dbo.cand_results AS r, dbo.centres AS c WHERE r.Centre_code = c.code AND r.Exam_number = @examno "
            cmd.CommandType = CommandType.Text
            cmd.Connection = con
            cmd.Parameters.AddWithValue("@examno", TxtLatest.Text)
            Dim ds As New DataSet("cand_results")
            da.SelectCommand = cmd
            da.FillSchema(ds, SchemaType.Source, "cand_results")
            da.Fill(ds, "cand_results")
            Dim tblcand_results As DataTable
            tblcand_results = ds.Tables("cand_results")
            If tblcand_results.Rows.Count = 0 Then
                MsgBox("Candidate not found!", vbExclamation, "Candidate not found")
                TxtLatestSurname.Text = ""
                TxtLatestOther.Text = ""
                TxtLatestSex.Text = ""
                Exit Sub
            End If
            Dim drcurrent As DataRow
            For Each drcurrent In tblcand_results.Rows
                TxtLatestSurname.Text = ds.Tables("cand_results").Rows(inc).Item(0)
                TxtLatestOther.Text = ds.Tables("cand_results").Rows(inc).Item(1)
                TxtLatestSex.Text = ds.Tables("cand_results").Rows(inc).Item(2)
                TxtCentre_type1.Text = ds.Tables("cand_results").Rows(inc).Item(3)
            Next
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            con.Dispose()
            cmd.Dispose()
        End Try

try remove the $ sign, and create a new excel file, use "sheet1" as default sheet name.

A little tweak using your code, which resulted on the following:

Dim APP As New Excel.Application
    Dim worksheet As Excel.Worksheet
    Dim workbook As Excel.Workbook
    Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
        workbook = APP.Workbooks.Open(lblPath.Text)
        worksheet = workbook.Worksheets("sheet1")

        Dim i As Integer = 1
        Using MyReader As New Microsoft.VisualBasic.FileIO.TextFieldParser("C:\Users\abcde\Desktop\anothertest.txt")
            MyReader.TextFieldType = FileIO.FieldType.Delimited
            MyReader.SetDelimiters(" ")
            Dim row As String()
            While Not MyReader.EndOfData
                Try
                    row = MyReader.ReadFields()
                    Dim field As String
                    For Each field In row
                        'MsgBox(field)
                        worksheet.Cells(1, i).Value = field
                        i = i + 1
                    Next
                Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException
                    MsgBox("line " & ex.Message & "is not valid and will be skipped.")
                End Try
            End While
        End Using

        MessageBox.Show("successful...")
        workbook.Save()
        workbook.Close()
        APP.Quit()
        Process.Start("Excel", lblPath.Text)
    End Sub

Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
        workbook = APP.Workbooks.Open(lblPath.Text) 'path of excel file
        worksheet = workbook.Worksheets("sheet1")

        Dim i As Integer = 1
        ' Split string based on spaces.
        ' using textbox1 for your text input for example, just put this character to textbox1: 12345 12345 abcde abcde 12345
        Dim words As String() = TextBox1.Text.Split(New Char() {" "})
        ' Use For Each loop over words and display them.
        Dim word As String
        For Each word In words
            worksheet.Cells(1, i).Value = word
            i = i + 1
        Next
        MessageBox.Show("successful...")
        workbook.Save()
        workbook.Close()
        APP.Quit()
        Process.Start("Excel", lblPath.Text)
    End Sub

Private Sub BtnPrint_Click(sender As System.Object, e As System.EventArgs) Handles BtnPrint.Click
        If String.IsNullOrEmpty(Page2Combobox.Text) Then
            PrintDocument1.Print()
            'MessageBox.Show("Printed document1")
            PrintDtTextBox.Text = DateTimePicker1.Text
            Me.PrintStatusTextBox.Text = Label1.Text
        Else
            'if not empty then print document1 and print document2
            PrintDocument1.Print()
            'MessageBox.Show("Printed document1")
            PrintDocument2.Print()
            'MessageBox.Show("Printed document2")
            PrintDtTextBox.Text = DateTimePicker1.Text
            Me.PrintStatusTextBox.Text = Label1.Text
        End If

        Me.Validate()
        Me.VerifiedHPReportBindingSource.EndEdit()
        Me.TableAdapterManager.UpdateAll(Me.PrintHPRep)
    End Sub

    Private Sub PrintDocument2_PrintPage(sender As System.Object, e As System.Drawing.Printing.PrintPageEventArgs) Handles PrintDocument2.PrintPage
        Dim font1 As Font = New Drawing.Font("Arial", 11, FontStyle.Underline)
        Dim font2 As Font = New Drawing.Font("Microsoft Sans Serif", 10)
        Dim font3 As Font = New Drawing.Font("arial", 10, FontStyle.Bold)
        e.Graphics.DrawString(Page2Combobox.Text, font1, Brushes.Black, 150, 195)
    End Sub

Hi,

I think these links will help you on how to use a select statement: msdn, w3schools

Hi,

try this:

Private Sub BtnPrintAll_Click(sender As System.Object, e As System.EventArgs) Handles BtnPrintAll.Click
        For i As Integer = 0 To VerifiedHPReportDataGridView.Rows.Count - 2
            'print row/s and remove one by one.
            If VerifiedHPReportDataGridView.Rows.Count - 1 > 0 Then
                'to avoid exception for new generated row
                PatientNameTextBox.Text = VerifiedHPReportDataGridView.Rows(i).Cells(PatientName.Name).Value.ToString()
                AgeTextBox.Text = VerifiedHPReportDataGridView.Rows(i).Cells(Age.Name).Value.ToString()
                PrintDtTextBox.Text = DateTimePicker1.Text
                Me.PrintStatusTextBox.Text = Label1.Text
                PrintDocument1.Print()
                'MessageBox.Show("Success...")
                'after print you can insert this code automatically.
                Me.Validate()
                Me.VerifiedHPReportBindingSource.EndEdit()
                Me.TableAdapterManager.UpdateAll(Me.PrintHPRep)

                'VerifiedHPReportDataGridView.Rows.RemoveAt(i) remove this code it won't be needed anymore since you are using filtering with typed dataset and to avoid deletion of row(s).

                i -= 1
            End If
            PatientNameTextBox.Text = ""
            AgeTextBox.Text = ""
            PrintDtTextBox.Text = ""
            PrintStatusTextBox.Text = ""
        Next
    End Sub

Hope it helps.

Hardz

Hi,

Sorry, I misunderstood the problem, an apology to you guys especially for zelrick. :). I never thought that the given data in excel is per cell, I just thought that it is a one line numerical characters. :)

Anyway, substing method could still do that, so by just modifying the code before, here is the result:

Dim cell1, cell3 As String
                    For Each row As DataRow In dts.Tables("[Sheet1$]").Rows
                        cell1 = row(0).ToString()
                        cell3 = row(2).ToString()
                        '4 columns only
                        DataGridView1.Rows.Add(cell1.Substring(0, cell1.Length - 3),
                                               cell1.Substring(2, cell1.Length - 2),
                                               cell3.Substring(0, cell3.Length - 4),
                                               cell3.Substring(1, cell3.Length - 2))
                    Next

Thanks,

Hardz

Hi,

I have no problem running this code without error. Maybe you can try this sample data and save it on new excel file, then run and let's see if there's still an error:

Number Format
98321 12345 67890 54321 09876
89123 51234 06789 15432 60987
78910 51234 47896 15432 60987
88995 51234 55663 15432 60987
12345 51234 67891 15432 60987

Hi,

are you sure that all of your data in excel have this kind of format:
'xxxxx xxxxx xxxxx xxxxx xxxxx'.
Because if not that error will occur.

Hi,

Am I wrong to make the ("ColumnName") to (0)?

changing it to 0 is a right way, knowing that row 1 at you excel file has no direct column name but a data instead. By the way, just add 5 columns to your DGV, since the code sample consists of 5, to see what the result is.

@shark: yes, you are right, the one that I have provided causes a code redundancy which I didn't notice. :) Thanks...

@chris: I think you just need to join the two tables, Departments and logins, in which Dept_id will be used as a reference. Follow these links on how to join the tables: link1Link2

Since you are using a typed dataset, you just need to modify your dgv Design name. Right click to your dgv and select edit columns, or from Properties menu, select Columns then press Collection ellipse button. At edit columns Property menu, click on the PatientName then change its Design (Name) column to PatientName for example, and follow the same procedure with the next column.

For i As Integer = 0 To VerifiedHPReportDataGridView.Rows.Count - 2
            'print row/s and remove one by one.
            If VerifiedHPReportDataGridView.Rows.Count - 1 > 0 Then
                'to avoid exception for new generated row
                PatientNameTextBox.Text = VerifiedHPReportDataGridView.Rows(i).Cells(PatientName.Name).Value.ToString()
                AgeTextBox.Text = VerifiedHPReportDataGridView.Rows(i).Cells(Age.Name).Value.ToString()
                PrintDocument1.Print()
                'MessageBox.Show("Success...")
                VerifiedHPReportDataGridView.Rows.RemoveAt(i)
                i -= 1
            End If
            PatientNameTextBox.Text = ""
            AgeTextBox.Text = ""
        Next

Hardz

Hi Zelrick,

Yes, ddanbe's suggestion is right: "I would rather process one excel cell, instead of first reading in a whole excel row and concatenating the cell values as string an then start to process the string". Anyway, Please see this link on how to import excel file to dgv: import data from excel to Dgv. If the code above is combined with this one may result with the following code:

Imports System.Data.OleDb
Imports System.IO

Public Class Form1

    Private Sub BtnExport_Click(sender As System.Object, e As System.EventArgs) Handles BtnExport.Click
        Dim dta As OleDbDataAdapter
        Dim dts As DataSet
        Dim excel As String
        Dim OpenFileDialog As New OpenFileDialog
        Dim value As String

        OpenFileDialog.InitialDirectory = My.Computer.FileSystem.SpecialDirectories.MyDocuments
        OpenFileDialog.Filter = "All Files (*.*)|*.*|Excel files (*.xlsx)|*.xlsx|CSV Files (*.csv)|*.csv|XLS Files (*.xls)|*xls"

        If (OpenFileDialog.ShowDialog(Me) = System.Windows.Forms.DialogResult.OK) Then

            Dim fi As New FileInfo(OpenFileDialog.FileName)
            Dim FileName As String = OpenFileDialog.FileName

            excel = fi.FullName
            Using conn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excel +
                                       ";Extended Properties=Excel 12.0;")
                conn.Open()
                dta = New OleDbDataAdapter("Select * From [Sheet1$]", conn)
                dts = New DataSet
                dta.Fill(dts, "[Sheet1$]")
                For Each row As DataRow In dts.Tables("[Sheet1$]").Rows
                    value = row("ColumnName").ToString() 'column name on excel table
                    'assuming there are five columns in a table including the original value
                    DataGridView1.Rows.Add(value,
                                           value.Substring(0, value.Length - 27),
                                           value.Substring(2, value.Length - 26),
                                           value.Substring(12, value.Length - 28),
                                           value.Substring(13, value.Length - 26))
                Next
            End Using
        End If
    End Sub
End Class

Hardz

Hi Christopher,

Use an indicator as departmentId so that you can login to either department1 or department2. And use parameterized query to avoid sql injection attack.

Private strCon As String = "Data Source=IT-Hardz\SqlExpress;Initial Catalog=CGHMS;Integrated Security=True "
    Private Sub btnLogIn_Click(sender As System.Object, e As System.EventArgs) Handles btnLogIn.Click
        Dim cmd As New SqlCommand()
        Using con As New SqlConnection(strCon)
            con.Open()
            cmd.Connection = con
            cmd.CommandText = "Select log_username, log_password, DepartmentId From Login Where log_username = @username And " + "log_password = @password "
            cmd.Parameters.AddWithValue("@username", txtUserName.Text)
            cmd.Parameters.AddWithValue("@password", txtPassword.Text)
            Using dr As SqlDataReader = cmd.ExecuteReader()
                Dim indicator As Boolean = False
                While dr.Read()
                    Dim mydept As String = dr("DepartmentId").ToString()
                    If dr.HasRows AndAlso mydept = "1" Then
                        MessageBox.Show("Department1")
                        department1.Show()
                        indicator = True
                    ElseIf dr.HasRows AndAlso mydept = "2" Then
                        MessageBox.Show("Department2")
                        department2.Show()
                        indicator = True
                    End If
                End While
                If Not indicator Then
                    MessageBox.Show("Invalid User name and password")
                End If
            End Using
        End Using
    End Sub

Hardz

Sorry, I thought I'm in a c# thread.. :)

Dim exdata As String() = {"98321 12345 67890 54321 09876", "89123 51234 06789 15432 60987"}
        For Each value As String In exdata
            DataGridView1.Rows.Add(value.Substring(0, value.Length - 27), value.Substring(2, value.Length - 26), value.Substring(12, value.Length - 28), value.Substring(13, value.Length - 26))
        Next

Hardz

Hi zelrick,

ddanbe provided you a great answer (link) to your problem.

string[] exdata = { "98321 12345 67890 54321 09876", "89123 51234 06789 15432 60987" };
            foreach (string value in exdata)
            {
                dataGridView1.Rows.Add(value.Substring(0, value.Length - 27),
                        value.Substring(2, value.Length - 26),
                        value.Substring(12, value.Length - 28),
                        value.Substring(13, value.Length - 26));
            }

Hardz

Hi Satyam,

yes this code is working based on your requirements. But I have made a modification of the code to follow the requirements based on my understanding. So using Northwind database:

Imports System.Data.SqlClient

Public Class Form1

    'handle cellenter event for data selection from dgv to textboxes, just in case.
    Private Sub dataGridView1_CellEnter(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellEnter
        TextBox1.Text = ""
        TextBox2.Text = ""
        TextBox3.Text = ""
        TextBox4.Text = ""
        If DataGridView1.Rows.Count - 1 > 0 Then
            TextBox1.Text = DataGridView1.CurrentRow.Cells("ProductName").Value.ToString()
            TextBox2.Text = DataGridView1.CurrentRow.Cells("CompanyName").Value.ToString()
            TextBox3.Text = DataGridView1.CurrentRow.Cells("CategoryName").Value.ToString()
            TextBox4.Text = DataGridView1.CurrentRow.Cells("Description").Value.ToString()
        End If
    End Sub

    Private conn As SqlConnection
    'sample connection string using northwind database
    Private strConn As String = "Data Source = IT-Hardz\SqlExpress; Database = 'NORTHWND'; Integrated Security = True"

    'loading/filtering data from the database
    Private Sub btnLoad_Click(sender As System.Object, e As System.EventArgs) Handles btnLoad.Click
        Using conn = New SqlConnection(strConn)
            conn.Open()
            Using cmd As New SqlCommand("Select ProductName, CompanyName, CategoryName, Description From " +
                                        "Products_By_Category_Supplier Where Description Like '%' + @desc + '%' ", conn)
                cmd.CommandTimeout = 3600
                cmd.Parameters.AddWithValue("@desc", txtFilter.Text)
                Dim da As New SqlDataAdapter()
                Dim ds As New DataSet()
                da.SelectCommand = cmd
                da.Fill(ds, "dsPro")
                DataGridView1.DataSource = ds.Tables("dsPro")
            End Using
        End Using
    End Sub

    'printing all data from dgv to textboxes.
    Private Sub btnPrintAll_Click(sender As System.Object, e As System.EventArgs) Handles btnPrintAll.Click
        'if dgv is autogenerated
        For i As Integer = 0 To DataGridView1.Rows.Count - 2
            'print row/s and remove one by one.
            If DataGridView1.Rows.Count - 1 > 0 Then
                'to avoid exception for new generated row
                TextBox1.Text = DataGridView1.Rows(i).Cells("ProductName").Value.ToString() …

Hi Satyam,

if my understanding is correct maybe you can try this code:

Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        Dim col1 As String, col2 As String, col3 As String
        'if dgv is autogenerated
        For i As Integer = 0 To DataGridView1.Rows.Count - 2
            'print row/s and remove one by one.
            If Not DataGridView1.CurrentRow.IsNewRow Then 'to avoid exception for new generated row
                'assuming there are 3 columns in a table
                col1 = DirectCast(DataGridView1.Rows(i).Cells("Column1").Value, String)
                col2 = DirectCast(DataGridView1.Rows(i).Cells("Column2").Value, String)
                col3 = DirectCast(DataGridView1.Rows(i).Cells("Column3").Value, String)
                print(col1, col2, col3)
                MessageBox.Show(col1 + ", " + col2 + ", " + col3)
                DataGridView1.Rows.RemoveAt(i)
                i -= 1
            End If
        Next

        'if not then modify the code:

        'For i As Integer = 0 To DataGridView1.Rows.Count - 1
        'If DataGridView1.Rows.Count > 0 Then
        'col1 = DirectCast(DataGridView1.Rows(i).Cells("Column1").Value, String)
        'col2 = DirectCast(DataGridView1.Rows(i).Cells("Column2").Value, String)
        'col3 = DirectCast(DataGridView1.Rows(i).Cells("Column3").Value, String)
        'print(col1, col2, col3)
        'MessageBox.Show(col1 + ", " + col2 + ", " + col3)
        'DataGridView1.Rows.RemoveAt(i)
        'i -= 1
        'End If
        'Next
    End Sub

    Private Sub print(col1 As String, col2 As String, col3 As String)
        'your code here...

    End Sub

Hardz

Hi Prince,

If I understand you correctly maybe this link can help you:
https://social.msdn.microsoft.com/Forums/vstudio/en-US/72bee14b-53d7-4a62-a6a0-381aeb8ef14b/copy-datagridview1-data-to-datagridview2

https://social.msdn.microsoft.com/Forums/en-US/8c4c8eda-10b5-4393-84c8-418ada34ae98/copy-selected-rowscells-from-one-datagridview-to-another

or using this sample code:

Public Class Form1

Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
    initializeDgv1()
    initializeDgv2()
End Sub

Dim tableOfDgv1, tableOfDgv2
Private Sub initializeDgv1()
    tableOfDgv1 = New DataTable()
    tableOfDgv1.Columns.Add("Dgv1_Column1")
    tableOfDgv1.Columns.Add("Dgv1_Column2")
    dataGridView1.DataSource = tableOfDgv1
End Sub

Private Sub initializeDgv2()
    tableOfDgv2 = New DataTable()
    tableOfDgv2.Columns.Add("Dgv2_Column1")
    tableOfDgv2.Columns.Add("Dgv2_Column2")
    dataGridView2.DataSource = tableOfDgv2
End Sub

Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
    tableOfDgv2.Clear()
    Dim table1Value As DataRow
    For i As Integer = 0 To DataGridView1.Rows.Count - 2
        table1Value = tableOfDgv2.NewRow()
        table1Value("Dgv2_Column1") = DataGridView1.Rows(i).Cells("Dgv1_Column1").Value.ToString()
        table1Value("Dgv2_Column2") = DataGridView1.Rows(i).Cells("Dgv1_Column2").Value.ToString()
        tableOfDgv2.Rows.Add(table1Value)
    Next
    DataGridView2.DataSource = tableOfDgv2
End Sub

End Class

Hi,

Anyway, i created a solution that solves for my problem in master/detail relationship, but not really the best one. so here is the revised code as shown below:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace MasterChild
{
    public partial class Form1 : Form
    {
        bool addIndicator = false;
        DataSet ds = new DataSet();
        int lastNoOfMasterId;
        public Form1()
        {
            InitializeComponent();
        }
        private void Form1_Load(object sender, EventArgs e)
        {
            this.masterTableAdapter.Fill(this.dsMaster.Master);
            detailLoadValue();
            lastno();
        }
        void lastno()
        {
            bnMoveLastItem.PerformClick();
            lastNoOfMasterId = int.Parse(txtMasterId.Text);
        }
        void detailLoad()
        {
            try
            {
                this.detailTableAdapter.Fill(this.dsDetail.Detail, new System.Nullable<int>(((int)(System.Convert.ChangeType(lblMasterId.Text, typeof(int))))));
            }
            catch (System.Exception ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message);
            }
        }
        void detailLoadValue()
        {
            lblMasterId.Text = txtMasterId.Text;
            detailLoad();
        }
        private void bindingNavigatorMoveNextItem_Click(object sender, EventArgs e)
        {
            detailLoadValue();
        }
        private void bindingNavigatorMoveLastItem_Click(object sender, EventArgs e)
        {
            detailLoadValue();
        }
        private void bindingNavigatorMovePreviousItem_Click(object sender, EventArgs e)
        {
            detailLoadValue();
        }
        private void bindingNavigatorMoveFirstItem_Click(object sender, EventArgs e)
        {
            detailLoadValue();
        }
        private void masterBindingNavigatorSaveItem_Click(object sender, EventArgs e)
        {
            this.Validate();
            ds.EnforceConstraints = false;
            this.masterBindingSource.EndEdit();
            this.masterTableAdapter.Update(this.dsMaster.Master);

            this.detailBindingSource.EndEdit();
            this.detailTableAdapter.Update(this.dsDetail.Detail);
            ds.EnforceConstraints = true;
            addIndicator = false;
        }
        private void bindingNavigatorAddNewItem_Click(object sender, EventArgs e)
        {
            if (addIndicator)
            {
                return;
            }
            else
            {
                detailLoadValue();
                lastNoOfMasterId += 1;
                addIndicator = true;
            }          
        }
        private void dgvDetail_RowEnter(object sender, DataGridViewCellEventArgs e)
        {
            if (addIndicator)
            {            
                lblMasterId.Text = lastNoOfMasterId.ToString();
            }
            else
            {
                lblMasterId.Text = txtMasterId.Text;
            }
        }
    }
}

So using the integer lastNoOfMasterId that determines the last number of MasterId and automatically incremented if the user press an add button, which is now a value …

Anyway, TeacherId is autoIncremented so if you press add at the navigator, its value is -1. From that sample i assume the value that is generated is 5.

Har

Hi,

For clearer explanation for this I will provide an example for adding an entry to teachers and student table below, coz im not too good in explanation.ü

Ex:

@Textboxes for teachers info:

TeacherID: 5, autoIncremented
Name: Linda
Bday: Jan. 25
Address:Main Rd.

@Datagridview for Students info:

Name Bday Address Absences

Ed Feb. 13 113 Vill 4
Rey Dec. 25 #56 1
Chad Oct 3 #12 0

So When i save this data using the code above produces an output at the database as shown:

For Teachers database:

TeacherID Name Bday Address

5 Linda Jan. 25 Main Rd.

For Students Database:

ID TeacherId Name Bday Address Absences

1 Null Ed Feb. 13 113 Vill 4
2 Null Rey Dec. 25 #56 1
3 5 Chad Oct 3 #12 0

From this output Ed and Rey have a null TeachersId value while only chad has a value, so I want rey and Ed to have a teachersId value, that is my problem using only one save button in my application, I need help for this problem coz i tried everything but nothing happens.

Tnx in advance.

Har

Hi,

I am using c# to update and view the student_Teacher simple database in sql. I used textboxes with binding navigator to view and update the teachers info and datagridview for the students, so 1 teacher per many students is the output. I have no problem for searching, editing and delete coz It is working properly, but my only problem is that adding a new record to the database. Actually, one of my solution for this is to use separate save button for textboxes and datagridview but it causes to a delayed and more hassle environment. So I just want it to have only one save button in a form that saves all the fields at the database. Is it possible to do this? Anyway, here is the list of code as shown:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
 
namespace TeachSudent
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            try
            {
                this .taTeachers.Fill(this .dsTeachers.Teachers);
                this .taStudents.Fill(this .dsStudent.Students, int .Parse(txtId.Text.ToString()));
            }
            catch (FormatException )
            {}
        }
    private void teachersBindingNavigatorSaveItem_Click_1(object sender, EventArgs e)
        {
            this .Validate();
            this .bsTeachers.EndEdit();
            this .tamTeacher.UpdateAll(this .dsTeachers);

            //TeacherId is the foreign key to Student
            //txtId.text is the input indicator for TeacherId at Student table,
            //so setting the property of txtTeacherId.text, Databindings: Text: bsStudents – TeacherId
             //Then the property for txtId.text, Databindings: Text: bsTeachers - ID

            txtTeacherId.Text = txtId.Text;
            this .Validate();
            this .bsStudents.EndEdit(); …

Hello,

Thanks my problem was solved. Anyway, i found the best solution for that kind of selection, coz instead of using datagrid view, I found an alternative toolbox that fits to that problem through the use of Listbox, coz it has a features of key in selection that automatically scroll or select the cell or list without using any codes.

Thank you very very much!

Hello,
How do i select or set the focus of a particular cell in a datagridview? Actually, this problem is simple but it is hard for me to solve it coz i'm only a beginner in c#. So here is my sample list of students as shown below:

Ann
Antoinette
Bernadette
Brenda
Carole
Dean
Millicent
Ralph
Suzie
Victoria

During runtime the default cursor position will be at the top which is "Ann", So if i key in a letter or name in a textbox for example "s", automatically the cursor @ the datagrid will set to suzie so the output will be look like this:

Ann
Antoinette
Bernadette
Brenda
Carole
Dean
Millicent
Ralph
Suzie
Victoria

So the position of the cursor was changed and "Suzie" was selected, and if i key in another letter at the textbox automatically the cursor position will change and so on. I think it is still part of sorting in sql and it is very useful in windows environment. I need help for this problem.

Thank you very very much!

Har

H!

Thank you very much! my problem was solved.

Hardz

H! There!

I have a problem in c# that require sorting of names of students in a sql database and i want it to be specific, i used data grid view to view the data of the students as shown below:

Students First Name:

Ann
Antoinette
Bernadette
Brenda
Jeannette
Judith
Lester
Santos
Sebastien
Suzie
Tim
Victoria

if i type in a letter or abbreviation @ a textbox1 for example "s", all Names that starts with letter s will be sorted first so the output now of the query will become:

Students First Name:

Santos
Sebastien
Suzie
Ann
Antoinette
Bernadette
Brenda
Jeannette
Judith
Lester
Tim
Victoria

So if i type j, all starts with j will be first and so on.

I need help for this problem coz it is so hard for me to output the students with this kind of sorting.

thanks in advance.

Hardz

Ok thanks my problem was solved...

I am using this code in C# as shown below, to view the students first name.

studentsBindingSource.filter = "[First Name] like '%" + txtFirstName.text + "%'";

This code is very useful and working well.

If i input at a textbox for example, a letter [M] or abbreviations, all the students with letter M will be displayed, so no problem with that.

But how about if i input an abbreviated name with spaces for example [M a] or [A u], [Ad Us], etc., that displays a two word name for having it specific. For this example a named with "M a" will be displayed like "Mary Ann", Mark Andrew and so on.

So i need help to solve this problem, coz i'm having trouble for this, coz everytime i use an abbreviation that uses spaces resulted to a no display data grid view.

thanks...

I am using treeview in c# with checkboxes in each node as shown below:

+[]Department
[]Dept1
[]Dept2

I am having trouble to remove the checkbox at department node, Is there a way to remove it coz it is not necessary to have a checkbox there without affecting the checkboxes at Dept1 and Dept2? Can you show me the code for this problem?

thanks in advance, god bless!

Ok thank you very very much sir my problem was solved...

I am using treeview with checkboxes in each node as shown below:
+[]Main
[]Child1
[]Child2

I have an indicator from form1 which is checkbox1 and checkbox2 that is uses to control the checkboxes of child1 and 2. If checkbox1 is true automatically child1 will be true, and if checkbox2 is true, child 2 will be true likewise, and so on.

So it hard for me to manipulate the child nodes using the indicator, i need help for this problem coz i am only new to c#, can you show me the code for this problem?

My 2nd question is that, Is there a way to remove the checkbox at Main node coz it is not necessary to have a checkbox there?

thanks in advance and god bless!

Hardz

Coz i have no authority to access a server, coz i am a still student at my school, and i was creating a project that uses c# and ms sql server, that's why i installed it at pc2 client.

So meaning, pc3 can access to pc2 to proper ip configuration, but since i am only a beginner i am having trouble to connect the database from one computer to another. So how can i configure the sql server so that pc3 can connect easily to pc2?

Thank you very much..

I have 3 computers connected to each other, pc1 is the server while pc2 and pc3 are clients. I created a windows application using c# and MS sql server as database at pc2 where in the sql server was installed, anyway, i didn't installed sql at pc1 server coz of some reasons and restrictions. Since i am new to sql it is hard for me to run my application at pc3 coz it didn't connect to sql server. So is there a possibility that client pc3 can connect to client pc2? if yes, how? tnx in advance and may god bless us!

I am a c# and MSSql Server beginner, one of the project that i created is the library system database, I used MSSql Server as my database and c# as my interface. I have no problem to run that application coz it runs smoothly and better without any error. One of the features of it is that it is a password protected and only the employees that i created from the database could access the system. So one of my problem is that when i run the exe file from another computer, i found out that it is always dependent from my database that i created and since it is a password protected it is hard for me to logged in and all the data is blank likewise. So How can i solve this problem? How do i create an exe file that include all the database records and is not dependent to the database? Should i use WCF service for hosting as my temporary database server?

So thanks a lot in advance, and may God bless us always...

Hardz....