Posts by kRod

What value are you trying to put into the textbox? What column and row in the underlying dataSource is it in ? Do you need to search the dataSource for the value or does your query select a single value --- 'Command.ExecuteScalar' As you can see there is a lot of info we would need to answer your question. Showing the code that retrieves the data would help alot....

You might try something like this.....

 Private Sub addSelectedClickHandlers(ByVal gb As GroupBox)


Try

            For Each selectedControl As Control In gb.Controls

                AddHandler selectedControl.MouseMove, AddressOf selectedControl_MouseMove

                AddHandler selectedControl.MouseLeave, AddressOf selectedControl_MouseLeave

            Next

        Catch ex As Exception

            Debug.Print(ex.ToString)

        End Try

    End Sub

Well I have come to the conclusion: Added a CheckBox to the Form for Has or HasNot Column Names, Let user Preview the WorkSheet, set Connection String from that. Kind of ruins any automation though.

Have you written anything yet? You should read this post http://www.daniweb.com/software-development/vbnet/threads/460782/help-with-accessing-data-from-an-ms-access-database-with-vb

I could not reproduce the problem you are having without using Me.Activate

Then I got a list of the Processes Names and ID's into a Dictionary and used The 'EXCEL' Process ID to Activate the WorkBook with AppActivate(procId)

 Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        TextBox1.Text = "" '(to empty up the search textbox to key in next excel file name )
        excel = New Microsoft.Office.Interop.Excel.Application
        Dim wBook As Microsoft.Office.Interop.Excel.Workbook
        Dim wSheet As Microsoft.Office.Interop.Excel.Worksheet


        wBook = excel.Workbooks.Open("C:\Users\Marijane\Desktop\Projects\DailySales.xlsx", False, True)


        '
        excel.Visible = True


        'excel.WindowState = XlWindowState.xlMaximized

        With excel
            .DisplayFullScreen = True
            .CommandBars("Full Screen").Visible = False
            .CommandBars("Worksheet Menu Bar").Enabled = False
        End With

        Me.Activate()

        Dim dictProcesses As New Dictionary(Of String, Integer)
        dictProcesses = GetProcesses()
        If dictProcesses.ContainsKey("EXCEL") Then
            AppActivate(dictProcesses.Item("EXCEL"))
        End If
        '        excel.Application.ActiveWorkbook.Activate()

    End Sub


     Private Function GetProcesses() As Dictionary(Of String, Integer)

        Dim ret As New Dictionary(Of String, Integer)
        Dim myProcesses() As Process
        Dim myProcess As Process
        myProcesses = Process.GetProcesses()
        ' Iterate through the process array.
        For Each myProcess In myProcesses
            If Not ret.ContainsKey(myProcess.ProcessName) Then
                ret.Add(myProcess.ProcessName, myProcess.Id)
            End If
            Console.WriteLine(myProcess.ProcessName)
        Next

        Return ret
    End Function

This moved my Form behind the WorkBook.

That's it exactly.

Good Day all,
My question is When I get the schema of an EXCEL worksheet with Column headers I get what's expected the names of the columns but when I get the schema of a worksheet without headers I get the first row of data as the Column Names. The trouble is in determining if a worksheet has headers or not, so as to program my connection string correctly. Anyone have thoughts on this? I created a worksheet with the schema as it is in the dataView.

Thank you
KRod

Give this page a Look Click Here

If all your tables are actually filled with data then

 DataGridView3.DataSource = query.ToList

should load your DataGridView

If you still can't find what's Checking Your CheckBox Try right-clicking on the CheckBox in Code and selecting "Find All References" go through each one and see where your CheckBox is being checked. It sounds like you have some code out of order. Also you could step through the code with the debugger to see when & where you are Checking the Mis-Behaved CheckBox.

You need to put it where it can be called as needed. Where ever your logic calls for it.

Me.FourPPMCheckBox1.Value = True 'Is Wrong
Me.FourPPMCheckBox1.Checked = False 'Is what you are wanting 

Also make sure as Begginnerdev has said you dont have a CheckChanged Event coded to undo what you are wanting

Im suprised these two commands didn't throw errors.

        Me.PlannedDurationTextBox = ""
        Me.PlannedRemarksTextBox = ""

Anyway I do not see where you are calling
Me.FourPPMCheckBox1.Value = False

How about reworking your code to say

        For Each Item In ComboBox1.Items
            If ListBox1.Items.Contains(Item) Then
                MsgBox("You can't add those items twice.", MsgBoxStyle.Exclamation)
            Else
                ListBox1.Items.Add(Item)
            End If
        Next

What is the datatype of your UserID in the "User" table? You are providing String Type to check against them in your table.

I'm working on a small database app to organize my song files and threw together this little sub to demonstrate a Null value in one of the columns of a record. It's OldeDB but should easily be adapted to SQL.

I purposely left the Album name out on one of the records and it worked as expected! Below are the Column Names.

ID  ALBUM   ARTIST  TITLE   RELEASED    LOCATION    BIT_RATE    [SIZE]  DURATION    GENRE




    Private Sub Search_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Dim startUp As String = Environment.CurrentDirectory
        Dim conStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & startUp & "\MusicList.accdb;Persist Security Info=False;"

        Dim sql As String = "Select * From SONGS"
        Dim conn As New OleDb.OleDbConnection(conStr)

        Dim cmd As New OleDb.OleDbCommand()
        With cmd
            .CommandText = sql
            .CommandType = CommandType.Text
            .Connection = conn
        End With

        Dim rdr As OleDb.OleDbDataReader

        conn.Open()
        rdr = cmd.ExecuteReader
        While rdr.Read
            'Check if the Second Column is null
            'if so report the song name 
            'first column is AutoNumber "ID" Second Column is the "Album"
            If rdr.IsDBNull(1) Then 'Checking 2nd Column for null
                MsgBox(rdr.GetValue(3).ToString)
            End If
        End While

       conn.Close
    End Sub

Hope this points you in the right direction...

If you used a DataAdapter DataSet and bound your datagrid with a BindingSource all you would have to do is call bindingSource1.ResetBindings(False) you would get the functionality you're looking for. Here is some sample code to point you in that direction.

Imports System.Data.OleDb

Public Class Form1


    Dim conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\yourAccess.mdb")
    Dim bs As New BindingSource
    Dim ds As New DataSet
    Dim da As New OleDbDataAdapter()

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim sql As String = "Select * From tbl_Inventory Order By Item_Name ASC;"

        Dim cmd As New OleDbCommand()
        With cmd
            .CommandType = CommandType.Text
            .CommandText = sql
            .Connection = conn
        End With

        With da
            .SelectCommand = cmd
            .Fill(ds, "tbl_Inventory")
        End With
        Dim cmdBuilder As New OleDbCommandBuilder(da)

        bs.DataSource = ds.Tables(0)

        dgv1.DataSource = bs

        dgv1.Columns(0).ReadOnly = True
        dgv1.Columns(0).HeaderText = "ID"
        dgv1.Columns(1).ReadOnly = True
        dgv1.Columns(1).HeaderText = "ITEM NAME"
        dgv1.Columns(2).Visible = False
        dgv1.Columns(3).Visible = False
        dgv1.Columns(4).Visible = False
        dgv1.Columns(5).Visible = False
        dgv1.Columns(6).Visible = False
        dgv1.Columns(7).HeaderText = "ACTIVE"

    End Sub

    Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
        Try
            bs.ReSetBindings(False) 'Updates the DataGridView
            da.Update(ds, "tbl_Inventory") 'Updates the Access DataTable

        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub

End Class

When I use a DataAdapter and the CommandBuilder with a BindingSource I call the Update on the DataAdapter as follows.

 da.Update(ds, "TableName")

You are creating a new DataSet each time you run this code so the table your DataGridView is using gets refreshed with only the record queried for.

You might try using the Datareader to fill the DataTable you're using for your DataGridViews DataSource.

You can create some variables to hold the values read from the DataReader then ad them to your DataTable
You should also use the BindingSource so its easier to refresh your DataGridView.

 Dim newRow As DataRow = ds.Tables("TableName").NewRow()

        newRow("FieldName1") = "SomeValue1"
        newRow("FieldName1") = "SomeValue2"

        ds.Tables("TableName").Rows.Add(newRow)

Give me a shout if you need help setting it up.

I'm not trying to step on any toes, I just wanted to offer another approach. Also love the way Regex works.

Imports System.Text.RegularExpressions

Module Module1

    Sub Main()

        Dim s As String = "ajasasaj$testing%878788%%$lkshhajsjah"

        Console.WriteLine("The Number Of Records in the line = " & NumberOfRecords(s, "$").ToString)
        Console.WriteLine()
        Console.WriteLine("Records with the $ replaced with a blankspace ..." & vbCrLf & _
                          NewRecordLine(s, "$"))
        Console.ReadLine()
    End Sub


    Private Function NumberOfRecords(ByVal records As String, ByVal splitOn As String) As Integer

        Dim r As Regex = New Regex(splitOn)
        Dim re() As String = r.Split(records)
        Return re.Count + 1

    End Function



    Private Function NewRecordLine(ByVal records As String, ByVal replace As String) As String

        replace = "\" & replace
        Return Regex.Replace(records, replace, " ")

    End Function

End Module

If your DataGridView has a datatable as its datasource why not make use of the datatable read and write xml methods

How did you load the DataGridView? Where did that data come from?

vanbane27 you need to start a new thread. I will also say that you need to provide a little more info about what you are trying to accomplish and show what you have tried.

Try using the GetValue method and the ordinal position of the field you want from the query.
This would be "Select Field From Table" you are requesting 1 field so that's position 0

 ComboBox1.Items.Add(CStr(DataReader.GetValue(0)))

 Private Sub CreateNewAccessTable()
        Dim dataBasePath As String = My.Computer.FileSystem.SpecialDirectories.Desktop & "\access.mdb"
        Dim tableName As String = "nameOfYourNewTable"

        'Create a new table using OLEDB Provider  
        Dim con As New OleDb.OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source =" & dataBasePath)
        con.Open()

        'Get data Table schema  
        Dim dbSchema As DataTable = con.GetOleDbSchemaTable(OleDb.OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, tableName, "TABLE"})
        con.Close()
        ' If the table exists, the count = 1 
        If dbSchema.Rows.Count > 0 Then
            'if the table exists
            'inform user and exit Sub

            MsgBox("Table Exists")
        Else

            'Create the new table 
            Dim cmd As New OleDb.OleDbCommand("CREATE TABLE [" + tableName + "] ([Field1] TEXT(10), [Field2] TEXT(10))", con)
            con.Open()
            cmd.ExecuteNonQuery()
            MessageBox.Show("Table Created Successfully")
            con.Close()
        End If

    End Sub

Why do you need to create an entire new database? Why not just create a new table to hold the new year's data? If you are set on creating a new DB then check out this link Click Here

Write it Once and call it from the two places that you wanted to write. I used the following

 Private Sub dgv1_UserAddedRow(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowEventArgs) Handles dgv1.UserAddedRow
        bs.ResetBindings(False)
        **UpdateDatabase()**
    End Sub

    Private Sub dgv1_UserDeletedRow(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowEventArgs) Handles dgv1.UserDeletedRow
        bs.ResetBindings(False)
        **UpdateDatabase()**
    End Sub

   ** Private Sub UpdateDatabase()
        Try
            da.Update(ds, "SAMPLES")
        Catch ex As OleDbException
            MsgBox(ex.ToString)
        End Try
    End Sub**

Do you actually have a Bridge_Number Field in your Table?
If you are using An Access DataBase I would first get the Query string into the "SQL View" of the Query Design window in MS-Access and test it. Also make sure you have spelled the Field names correctly. They have to be EXACT . You also have to have them in the correct order. You will need to make sure you are using the correct Data Type for each one.

You could just put Exit Sub or Function when you get to the Else after the Error Message. You should break them up into 2 different Functions, A Labor Function and A Parts Price Function Return A Boolean and then when both Return True Execute the rest of your code.

Hope this gets you started, it creates the insert query "q". You will need a connection and a command object.... You should read up on Datasets - Datatables - Datagridview - MS ACCESS. There are much easier ways of doing what you want. Please show what you have tried.

For r = 0 To DataGridView1.Rows.Count - 1
    q = "insert into data values('" & DataGridView1.Rows.Cells(0).Value & " ', ' " & DataGridView1.Rows.Cells(1).Value & "')"
Next

How about trying this

"SELECT * FROM Appointments WHERE TimeAvail=True AND Format(AptDate,'dddd') = 'Monday';"

As found on GOOGLE

public static boolean isLeapYear(int year) {
    assert year >= 1583; // not valid before this date.
    return ((year % 4 == 0) && (year % 100 != 0)) || (year % 400 == 0);
}

abdalqader.abdelhalem according to your post you are using ms access to fill the datagridview, I will assume (and I hate to do that) you are filling a datatable and using it as the source for the DataGridView. Why not query the datatable for your string using LINQ. If the data you are searching for is in a single column of the table it is very easy and fast to get the row number.

Do you have any hidden columns? if so you might need to unhide them run your sub and then re-hide them.

Killer88 You might try doing some reading on database access in vb.net. There are many threads here on Daniweb and Google has thousands. Your question says you are trying to run before you can walk.
Click Here

If you're looking for the number of days in a given month use the following

Dim daysInMonth as Integer = System.DateTime.DaysInMonth(YYYY, MonthName)

If that's not what you're looking for you will have to give a little more of what you want.

try this.

Provider := Microsoft.ACE.OLEDB.12.0;Data Source="C:\temp\MyExcel.xlsx";Extended Properties="Excel 12.0 Xml;HDR=YES;“;Jet OLEDB:Database Password=”";

Try this

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;
Extended Properties="Excel 12.0 Xml;HDR=YES";

[Click Here](http://www.connectionstrings.com/excel-2007#ace-oledb-12-0)

You could use the DataView to find and compare your values. From MSDN -->
Click Here

You could just create a windows form put the Username and Password textboxes with the appropriate buttons for the login and 12 hidden buttons for the Excel files. When user logs in successfully hide the login boxes and buttons and make your 12 Excel buttons visible. Then when the button is clicked run Process.Start(XL_FILE_NAME) in the click event. You could also just use a combobox with the XL file names in it and you wouldn’t have to create so many buttons.

It depends on the security and type of database. There is the Connection.GetSchema method to return what you are looking for. From MSDN Click Here

Read This
Click Here

joyvin: I'm certain you cannot run an insert command with the InsertCommand.ExecuteReader command.

How about just using a query to do the work unless you want to edit the data.

INSERT INTO newTable (fieldList)
SELECT fieldlist
FROM oldTable

You must have something missing or wrong in your connection string. You need to just set up event to open and close your connection wrapped in a try catch block to see what's wrong with it.

 Try
         conn.open
         msgbox(conn.ConnectionString & VBCRLF & "Connection Open")
        Catch ex As Exception
         msgbox(ex.tostring)
        Finally
         conn.Close
        End Try

From MSDN

Private Sub RetrieveMultipleResults(ByVal connection As SqlConnection)
    Using connection
        Dim command As SqlCommand = New SqlCommand( _
          "SELECT CategoryID, CategoryName FROM Categories;" & _
          "SELECT EmployeeID, LastName FROM Employees", connection)
        connection.Open()

        Dim reader As SqlDataReader = command.ExecuteReader()

        Do While reader.HasRows
            Console.WriteLine(vbTab & reader.GetName(0) _
              & vbTab & reader.GetName(1))

            Do While reader.Read()
                Console.WriteLine(vbTab & reader.GetInt32(0) _
                  & vbTab & reader.GetString(1))
            Loop

            reader.NextResult()
        Loop
    End Using
End Sub

Just off hand you call the ExecuteReader twice. You also have to get the value of the field from an ordinal and value while casting it to the typw you expect.

Hi cmstoner , what is the access database data type of your Business Phone field from the design view in ACCESS? If this is correct could you post the entire Insert command after you have built it?

HibaPro, you can get the totalseconds of your timespan and use that to create a double then do the multiplaction. TimeSpan.TotalSeconds

The text file with this post has one line of text and a newline. You can use either VBCRLF or VBNEWLINE

 Dim txt As String = My.Computer.FileSystem.ReadAllText(My.Computer.FileSystem.SpecialDirectories.Desktop & "\newLine.txt")

 If txt.Contains(vbNewLine) Then
            MsgBox(txt.IndexOf(vbNewLine))
        End If

Thank you TnTinMN for your quick response. I appreciate the rework. That was my next goal to make this into a control that could be used whenever it was needed. Nice Work