Posts by Reverend Jim

Can you post what the connection string looks like when you hard code it, then also post what it looks like when you build it? Perhaps seeing them next to each other would provide a clue.

When I say "post" I don't mean "type in what you think it is". I want you to actually display the string in a msgbox or debug output, then copy and paste.

You start by reading books. "Wrox Professional Visual Basic" and "Sybex Mastering Visual Basic" both contain very good sections on database programming. Don't expect us to be personal tutors and hold your hand through the learning process.

Change it to

Query = "update Prod_DB_Laminate_Raw set [Stock Level] = '" & TextBox1.Text & "' where Laminate = '" & ComboBox1.Text & "'"

Wnen you run into a problem like this it is best to display the actual query string as submitted to SQL. In most cases the error will be obvious. An extra line of

msgbox(Query)

Can open your eyes.

I find it useful to type these lines into a non auto-formatting window (Notepad, for example) then copy and paste into VB once it looks right. I suggest adding a line to display the connection string before you connect so you can verify that the string you see is the same as the one in your first example. You can set Consts DATABASENAME, USERID and PASSWORD.

Public urlMySQLDatabase1 As String =                  _
      "Server="   & frmLogin.txtServerIP.Text   & ";" _
    & "Port="     & frmLogin.txtServerPort.Text & ";" _
    & "Database=" & DATABASENAME                & ";" _
    & "Uid="      & USERID                      & ";" _
    & "Pwd="      & PASSWORD                    & ";"

I can't help you with the web code but your code just above still has a problem. You declare X as Integer but thge first time through the loop it doesn't have a value. This is not good practice. You should declare it as (for example)

Dim X as Integer = 0

You will also get an error if TextBox6.Text cannot be converted to an Integer.

I'll add to my earli
er caveat, most of my database code was written in vbScript so I leaned heavily on ADO (which was the technology Microsoft was pushing at the time). Now there are other methods available to VB users that may be more flexible. I tend to stay with what I am familiar. I understand that even streams use recordsets as their underlying access, their main advantage being that they allow you to process records immediately instead of having to wait for the entire recordset to be retrieved.

Here is one way.

Private Sub cmbLetters_TextChanged(sender As System.Object, e As System.EventArgs) Handles cmbLetters.TextChanged

    Dim conn As New ADODB.Connection
    Dim rset As New ADODB.Recordset
    Dim init As String = DirectCast(sender, ComboBox).Text

    conn.Open("Driver={SQL Server};Server=jim-pc\sqlexpress;Database=NorthWind;Trusted_Connection=yes;")
    rset.Open("select CustomerID from Customers where CustomerID like '" & init & "%'", conn, ADODB.CursorTypeEnum.adOpenForwardOnly)

    cmbNames.Items.Clear()

    Do Until rset.EOF
        cmbNames.Items.Add(rset(0).Value)
        rset.MoveNext()
    Loop

    rset.Close()
    conn.Close()

End Sub

cmbLetters contains the letters A to Z. Replace NorthWind (MS sample DB) and CustomerID with your specific items. There are likely other ways to do this by linking the control directly to a datatable or whatever but I believe this is the most straightforward. If your names are not in alphabetical order you can add "order by CustomerID ASC" to the end of the query.

You haven't declared X to be of any particular type. TextBox5.Text is a string and you are adding one to it so I presume that makes X an integer (assuming it is declared at the class level). In your Do Until statement you are comparing X (possibly an integer) to TextBox6.Text (definitely NOT an integer). These two things will never be equal.

Are you searching an archive, a file hierarchy or a database? You seem to be referencing all three. And I am not sure what you mean by a cabinet? What, exactly, is the format of the stuff (for lack of a more precise term) that you want to search?

Let me get this right. You have a dataset in an Excel spreadsheet that may or may not have the field names in the first row. Why not just open it with Hdr=No and examine the first record (row) in your code? If there is a row header then you can close it and reopen it with Hdr=Yes.

Probably means "officially" supported. Try it anyway. If there is a problem then set your program to run in XP compatibility mode.

The Windows Imaging Automation component might do it for you. http://www.microsoft.com/download/en/details.aspx?id=18287

The Windows Image Acquisition Automation Library v2.0 provides basic image acquisition and manipulation functionality to Visual Basic and Scripting applications.

I liked the post. Simple examples that illustrate one or two (at most) techniques are the best. I learned ADO as an access technique when I started developing DB apps years ago. Recently I stumbled upon an article which reflected my opinion on Microsoft's data access philosophy. It stated (in part)

When I was first working for Microsoft, I expressed as an issue for developers outside the Redmond Bubble. I called it ‘Shiny new toy syndrome’. It was a problem in that Microsoft every few months would release a new way of doing the same thing, or that they’d suddenly drop something that they’d been pushing hard, and move in a new direction. Think data access from JET Engine, to OLEDb, to DAO, ADO, EF, ODBC yadda yadda yadda. Each of these technologies did the same thing - give programmers the ability to access data, but MS would release each one with a huge fanfare, with evangelists, trainers etc. all teaching folks how to do it. In the end, developers would get sick of it, and the meme would be ‘Just show me how to use data’.

The entire post can be found at http://www.netnavi.tv/2011/12/27/12-things-microsoft-should-do-in-2012/

Here is an example of using ADO to import records from Excel. In my example, the data on Sheet1 looks like

lname   fname   age   gender
Smith   John    32    M
Doe     Jane    41    F

Imports ADODB

Public Class Form1

    Private Sub btnFetch_Click(sender As System.Object, e As System.EventArgs) Handles btnFetch.Click

        Dim conn As New Connection
        Dim rset As New Recordset
        Dim buff As String

        conn.ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=d:\temp\test.xls;"
        conn.Open()

        rset.Open("select * from [Sheet1$]", conn, CursorTypeEnum.adOpenForwardOnly)

        Do Until rset.EOF
            buff = rset("lname").Value & " " & rset("fname").Value & " " & rset("age").Value & " " & rset("gender").Value
            txtExcel.AppendText(buff & vbCrLf)
            rset.MoveNext()
        Loop

        rset.Close()
        conn.Close()

    End Sub

End Class

In this case, the first row is expected to contain the column headers (field names). If there are no column headers than the parameter ";HDR=No" must be included in the connection string.

Note that it is not necessary for any AccessDB components to be used (or even present on the user's computer).

to waqasaslammmeo

Can you please explain to me how

Importing Data from excel to Mysql using VB.net

can be interpreted as "I want to import Excel to an AccessDB?". I'm not trying to be difficult or argumentative but if I can misunderstand such a simple request than I obviously have no command of the English language whatsoever.

I'd also like to remind you of the forum rules/guidelines. To wit

Do not write in all uppercase or use "leet", "txt" or "chatroom" speak

Typing "u", "ur", etc makes it more difficult for non-English (as a first language) forum members to follow conversations.

If TextBox1.Text.Contains("$1$") and TextBox1.Text.Contains("abcdefg") and Len(TextBox1.Text) = 32 then
    MsgBox("hit")
End If

Can you dump the records from record_count as text and post them? I'll try it locally. Also, please post the SQL commands to create the table. If you are running SQL_Express I strongly suggest (if you don't already have it) downloading the free Microsoft SQL Server Management Studio.

I tried it on one of my tables and got the correct record count.

I only have experience with MS SQL. I seem to recall that you can use a setting called referential integrity. This is supposed to ensure that when you delete a record in a table, all other recoeds in tables that depend on that record are automatically deleted. Wrox Press Professional SQL Server states

SQL Server 2000 was the first version to support two of the most commonly requested forms of referential integrity actions: cascade updates and cascade deletes. These were common complaint areas, but Microsoft left some other areas of ANSI referential integrity support out. These were added in SQL Server 2005. You look at cascading and other ANSI referential integrity actions in detail when you look at FOREIGN KEY constraints.

You might also check http://www.cs.sfu.ca/CourseCentral/354/zaiane/material/notes/Chapter6/node7.html

If you already have a dateadded field then just go with that. It will work like you want. You don't need a separate table. To get some numbers you could do a query like (I am using a local table here)

select COUNT(*) from employee where hire_date between '1989-01-01' and '1989-12-31'

I'm assuming you are keeping the data in some sort of database (text, XML, Excel, Access, SQL, etc). In that case you wil have to create a table to keep track of the additions. Keep more info rather than less. For example, you asked how to keep track of the number of students. I suggest you keep track of which students as well. That way you can query not just how many students were added on a particular day (or days), but which students as well. The table might look like

RecordNum (primary key - autonumber)
DateAdded
StudentID

The exact structure would depend on your current implementation.

Public Class Form1

    Private Sub btnAdd_Click(sender As System.Object, e As System.EventArgs) Handles btnAdd.Click

        ComboBox1.Items.Add(Now)

    End Sub

End Class

Each time you click "Add", the current date/time will be added to the combobox. Just replace the argument with the text you want to add.

My point is that the Else clause is not necessary. The only purpose of that code is to ensure that the connection is open. To be picky, it should be in a Try block.

I'm not sure what your intention is in Sub Admin_Reg_Load but the logic is

if the connection is closed then
    open it
else
    close it
end if

I can't imagine why you would need a Sub to toggle the state of the connection. Usually you would have code that says

if the connection is closed then
    open it
end if

or

if the connection is open then
    close it
end if

That is apparently another feature your version does not support. Just replace it with the actual path where you want to save the log file.

dim myarray(20) as parts

This will create an array where each item can contain a reference to a part, however, the objects themselves do not exist until they are instantiated (with New). In order to actually create an array of part objects you have to do

for i as integer = 0 to 20
    myarray(i) = New parts
next

A method that should work is to use the scripting runtime library. In that case, make sure you add a reference (under the COM tab) to "Microsoft Scripting Runtime" (the actual file is c:\windows\system32\scrrun.dll), then use the following code

Public Class Form1

    Private fso As New Scripting.FileSystemObject

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

        Dim filename As String = Application.StartupPath & "\test.log"
        Dim ts As Scripting.TextStream = fso.OpenTextFile(filename, Scripting.IOMode.ForAppending, True)

        ts.WriteLine(Now() & " " & "sample log file entry")
        ts.Close()

    End Sub

End Class

Read books. Write code.

You learn to draw by drawing. You learn guitar by playing. Programming is the same. Wrox Press has several good books on VB programming. Also, Sybex. Check their websites. Books that claim that you can "Learn Visual Basic in 24 Hours" or even a week are lies. Programming is not something you get good at in just a few days. To think so is insulting to those of use who spent years honing our skills (and continue to do so over even after decades of practice). That doesn't mean you can't learn to write useful apps in the short term and it doesn't mean you can't have fun doing it. Just don't expect miracles overnight.

And if you get stuck, that's what we're here for.

What version of Visual Studio are you using (I have VS 2010)? What is the specific error you get?

Imports System.Text.RegularExpressions

Public Class Form1

    Private Sub btnValidate_Click(sender As System.Object, e As System.EventArgs) Handles btnValidate.Click

        Dim pattern As String = "^(19|20)\d\d[- /.](0[1-9]|1[012])[- /.](0[1-9]|[12][0-9]|3[01])$"
        Dim regex As New Regex(pattern)

        If regex.IsMatch(txtInput.Text) Then
            MsgBox("is valid")
        Else
            MsgBox("is not valid")
        End If

    End Sub

End Class

You have to show us what code you have in order for us to see where it is going wrong.

It would be easier to use isnumeric or isdate but he asked about regex so I thought I'd answer.

Please see http://www.regular-expressions.info/dates.html

Well don't I just feel like a complete moron? isdate() (DOH!).

As for regex, I use them so infrequently that when I need a particular one I can usually just find it online or in a book. Wrox Press Beginning Regular Expressions by Andrew Watt is excellent. When you discover that this "intro" book is almost 800 pages you'll get the idea that regular expressions are non-trivial.

An example of how to write to a log file is

Imports System.IO
Imports System.IO.File

Public Class Form1

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

        Dim filename As String = Application.StartupPath & "\test.log"
        Dim sw As StreamWriter = AppendText(filename)

        sw.WriteLine(Now() & " " & "sample log file entry")
        sw.Close()

    End Sub

End Class

The log file will be created if it doesn't exist. All of the applications that I wrote and maintained were written in vbScript. Because almost all of the required a logger, I created a logger class that I could include. A simplified example of using the logger class was

logger = New LoggerClass
logger.LogType = "daily"
logger.Open("myapp.log")

Anytime I wanted to write to the log file my apps would

logger.Write("any text")

The Write method would prepend YYYY-MM-DD HH:MM:SS to every line before writing. The LogType parameter was "daily", "monthly", "yearly" or blank. This would cause the creation of a new log file for every day, month or year (it would add the date to the name of the log file like "myapp 2012-01-09.log" as needed). Blank would just use one log file with no mod to the file name. You can get as fancy as you like but the basic method is

app start
open log file
log
log
log
.
.
.
close log file
app exit

The easiest way would be to split it into three strings as mystring.Split("\") then validate each field separately. A more complicated way would be to use a regular expression.

Normally I would disagree because most retrievals on that table would not be based on a incident number (which imparts no useful information other than sequence), but on student ID or date (or date range). You want to index your table based on the most likely method of access. However, because the table is not likely to be unwieldy there will likely not be a noticible performance hit.

Further thought, you might want to add another field to contain the student IDs of any other students also involved in the incident. The other IDs could be included as part of the note text but that would make it more difficult to extract those IDs automatically.

If you just want a simple text file then you only require simple text output (make sure you open the log file in append mode). I have a suggestion that should be obvious but wasn't to some of our in-house developers. Make sure every line that you write to the log file begins with the date and time in the format

YYYY/MM/DD HH:MM:SS

or

YYYY-MM-DD HH:MM:SS

Where all fields are zero padded (eg use 03 for March instead of just 3) and the time is in 24 hour format. It makes it much easier to correlate events when you are troubleshooting and looking at logs from other apps (or system). One developer in particular would write out the date and time once in long format, then spew out 50-60 lines of information. It was difficult trying to find the start of each block and impossible to determine exactly when each piece of the block was generated. And trying to compare "September 12, 2007 9:53:17 PM" from his log to "2007-09-12 21:53:17" in another was not pleasant.

Without knowing about the structure of your tables, you could have a table just for notes with a structure like

*Student_ID
*Incident_Date
Note

Where * indicates primary key (compound key consisting of the first two fields). A compound key would ensure a unique primary key.

I'm not saying that rahmnbcdefgijklopqstuvwxyz is the wrong answer. I'm saying that if rahmnbcdefgijklopqstuvwxyz is the right answer then you will have to explain why it is the right answer. In other words, what is the algorithm you are using to generate c? If you don't know the answer to that then you can't write a program to do it.

You can validate any data as long as you know what that data is supposed to be. If you are asking how to determine what formatting was set for a particular cell in the spreadsheet then I don't know. If you are importing data from a spreadsheet into VB then you should already have some idea of what format the data should be in so you shouldn't need to know the Excel formatting.

In any case. it may be afternoon in Rizal but it is after midnight here so I'm off to bed.

First let's get the algorithm correct. We start by comparing a(0) to b(0). They are not equal so c gets b(0). What are the next two things to compare? Do we compare a(0) to b(1) or a(1) to b(1)? When do we increment the index into a? If we are never going to increment the index for string "a" then what is the point of comparing chars in string "a" to chars in string "b"?

As far as I know there is no validation done. Even in Excel things are lax. For example, if you set the format for a cell in Excel to "currency", Excel will not prevent you from entering a string (or even warn you). At least not in Excel 2003 (I don't know about newer versions).

Note - the following code assumes that len(b) <= len(a)

Dim a As String = "abcdefghijklmnopqrstuvwxyz"
Dim b As String = "rahman"
Dim c As String = ""
Dim ch As Char

For i As Integer = 0 To Len(a) - 1

    'If there are still chars left in b then pick a char from a (if a=b)
    'or from b (if a <> b). If there are no more chars in b then just take
    'the next char from a

    If i < Len(b) Then
	ch = IIf(a(i) = b(i), a(i), b(i))
    Else
	ch = a(i)
    End If

    'If the selected char is not already in c then add it to c

    If InStr(c, ch) = 0 Then
	c += ch
    End If

Next

MsgBox(c)

That is most definitely NOT the case. If the Excel spreadsheet is organized as a proper database table (regular rows and columns) then the spreadsheet can be opened as an ADO data source and records can be retrieved just like any other actual database using a connection object and a recordset. If it is not then the data can be read by creating an Excel application object and reading the data cell by cell, then added to the database record by record. In any case, the OP is using MySql and there is no reason to involve Access.

What is your level of expertise? Do you know anything about programming? Have you taken any training? What have you done so far? Is the Excel file in a format that supports ADO or some other level of abstraction? What kind of database? If you want answers you have to provide some information.

You still haven't said how to combine the two strings. Why does c get "rahmn", then "bcde..."? Why not c = "rahmanabcdef...". Unless I know what the reasoning is I can't help you with the code.

How about this.

• Put a timer on the form
• Enable the timer on mouse down
• Disable the timer on mouse up or max volume reached

The timer could be set to an interval so that the min to max volume can be traversed in (for example) five seconds. Each tick of the timer would increase the volume by a set amount. The up and down buttons could set the increment (or decrement) value so the same timer could be used for both up and down. I tried this out and I think it will do what you want. Here is an example.

Public Class Form1

    Private current As Integer = 
    Private increment As Integer = 0

    Private Sub btnUp_MouseDown(sender As System.Object, e As System.Windows.Forms.MouseEventArgs) Handles btnUp.MouseDown
        increment = 1
        Timer1.Enabled = True
    End Sub

    Private Sub btnUp_MouseUp(sender As System.Object, e As System.Windows.Forms.MouseEventArgs) Handles btnUp.MouseUp
        Timer1.Enabled = False
    End Sub

    Private Sub btnDown_MouseDown(sender As System.Object, e As System.Windows.Forms.MouseEventArgs) Handles btnDown.MouseDown
        increment = -1
        Timer1.Enabled = True
    End Sub

    Private Sub btnDown_MouseUp(sender As System.Object, e As System.Windows.Forms.MouseEventArgs) Handles btnDown.MouseUp
        Timer1.Enabled = False
    End Sub

    Private Sub Timer1_Tick(sender As Object, e As System.EventArgs) Handles Timer1.Tick

        If increment > 0 Then
            If current = 20 Then
                Timer1.Enabled = False
                Exit Sub
            End If
        Else
            If current = 0 Then
                Timer1.Enabled = False
                Exit Sub
            End If
        End If

        current += increment
        Me.Text = current

    End Sub

End Class

The value of "current" will be written to the title bar.

how do i compare each of them and insert to new array c

You'll have to be clearer on what you are trying to do. You have a string (not an array as you stated), a, that contains "ABCDEFGHIJKLMNOPQRSTUVWXYZ" and a string (again, not an array), b that contains "RAHMAN". How do you determine what you want to put into array (this time you are correct), c?

The first thing that comes to mind is to check if anything in your code is setting the button state to visible. If you find any code that does this then set a breakpoint at that line and rerun.

What version of Visual Studio are you using? I have Visual Studio 2010. I created a project that has a form with one button. The first time you run the program, the button will have no label. Click it and the label will be set to the current date and time. When you close the program, then rerun it, the label will be what it was when you last closed the app.

I believe this is what you said you wanted. Add extra settings for extra buttons and add extra code based on what I gave you.

The zip of the project is attached.

In the button click handler you can identify the button either by name or by label text (the label text is probably more informative depending on how you name your controls)

Dim button As Button = DirectCast(sender, Button)
MsgBox(button.Text & " " & button.Name)