Posts by TomW

dim x = "1"
x 'just type x and wait the intellisense to showup it will tell you that x is declared as string

Thats funny because everything I said works fine here but your code apparently is not... Your choice, you can be lazy and save typing two whole words and spend hours asking for help because of it or you can learn to program correctly. Not turning on "Option Strict" & "Option Explicit" is more laziness in an attempt to have the program overlook errors.

01) Turn on Option Strict & Explicit
02) Explicitly declare your variable datatypes
03) Use prefixes in your controls & variable names
so that you know at a glance what datatype
there supposed to be.
04) If your having trouble with a "Select Case" statement
try looking in the help file at "Select Case"

Dim strMySearchValue As String = "1r"

        Select Case strMySearchValue
            Case "1r", "2r"
                MessageBox.Show("Case 1r or 2r")
            Case "3r", "44"
                MessageBox.Show("Case 3r or 4r")
            Case Else
                MessageBox.Show("strA is something else")
        End Select

Amazon has plenty of books on how to do dis...

You need to create an Array variable. Also you should turn on Option Strict & Option Explicit to have the design environment show you some mistake you are making with not explicitly converting your values to the proper datatypes.

Also it is not good programming practice to dimension variables without explicitly stating the datatype.

Change "Dim _FieldType =" to "Dim FieldType As String =".

Also you should always put a "Case Else" in your Select Case statement; even if there is no coding under it. Otherwise an error will occur if your search criteria isnt found in any of the other Case statements.

Replace or with a comma

Case "System.Int32", "System.Int64"

Also if you want to try a neat little control, similar the properties window in VB when you click on any of the form controls. Drag a PropertyGrid Control from the toolbox onto your form. In your code assign your table record/row to the PropertyGrid control and take a look at the results.

Dim rowMyData as DataRow = Nothing

myDataRow = m_dtData.FindByPrimaryKeyColumnName(ComboBox1.SelectedValue)

If myDataRow IsNot Nothing Then
    PropertyGrid.SelectedObject = rowMyData
End If

You can assign the dataset/datatable as the DataGridView's DataSource and filter the results so that it shows only the selected record from the combobox.

Dim m_dtData as New DataTable
m_dtData = FillDataTableCode
DataGridView1.DataSource = m_dtData

Private Sub Combobox1_SelectedIndexChanged()

    m_dtData.DefaultView.Filter = "ColumnName = 'Value'"

End Sub

As an example say I have two listbox's named (lstSource & lstTarget) and I want to transfer selected items from lstSource to lstTarget but first want to check that the item is not already in lstTarget:

Private Sub btnTransferItem_Click(...) Handles btnTransferItem.Click

        Dim intItemExists As Integer = -1
        Dim strTransferItem As String = ""

        'This can be done without the need of storing 
        'the selected/transfering item in a variable first. Using it
        'just for example purposes.

        strTransferItem = lstSource.Items(lstSource.SelectedIndex).ToString

        If lstTarget.FindStringExact(strTransferItem) = -1 Then
            'Item is not in the target listbox already
            lstTarget.Items.Add(strTransferItem)
        Else
            'Item found and already exists in the target listbox
            MessageBox.Show(String.Format( _
                            "Item ""{0}"" is already in the target list box" _
                            , strTransferItem))
        End If

    End Sub

If you look in the help file at the methods that I mentioned above, you will see that the ListBox.FindString & FindStringExact functions, will search the listbox for the string you provide. If the item is found within the list, it will return the index of that item in that listbox. If no items are found that match the search string it returns a -1 value.

The listbox has a FindString and FindStringExact methods available to search the items.

intIndex = ListBox2.FindStringExact("YourSearchItem")
If intIndex >= 0 then msgbox("Item Already Exists")

In your parameters you have several different datatypes defined. However each of the values that you are passing to them is text. You need to convert and assign the proper datatype values to these parameters.

Ex:

'.Add("@months", OleDbType.Integer).Value = txtMonth.Text
    .Add("@months", OleDbType.Integer).Value = CInt(txtMonth.Text)

Dim m_datTarget As DateTime = CDate("09/21/2009 7:00PM")

    Private Sub Timer1_Tick(...) Handles Timer1.Tick

        Dim tsDiff As TimeSpan

        If m_datTarget < Now Then
            Timer1.Enabled = False
            'Put your shut down code here
            MessageBox.Show("They end...")
            Exit Sub
        End If

        tsDiff = m_datTarget.Subtract(Now)
        Label1.Text = String.Format("Time Remaining : {0}", FormatElapsedTime(tsDiff))

    End Sub

    Public Function FormatElapsedTime(ByVal ts As TimeSpan) As String

        Dim strResult As String = "0 Milliseconds"

        If ts.Hours > 0 Then
            strResult = String.Format("{0:#0}:{01:00}:{02:00} hours", _
                                            ts.Hours, ts.Minutes, ts.Seconds)
        ElseIf ts.Minutes > 0 Then
            strResult = String.Format("{0:#0}:{01:00} minutes", _
                                            ts.Minutes, ts.Seconds)
        ElseIf ts.Seconds > 0 Then
            strResult = String.Format("{0:#0} seconds", ts.Seconds)

        ElseIf ts.Milliseconds > 0 Then
            strResult = String.Format("{0:##0} milliseconds", _
                                            ts.Milliseconds)
        End If

        Return strResult

    End Function

Read the text file into a dataset/datatable and then simple set the datagrid's datasource = to the datatable

DataGrid1.DataSource = myDataSet.Tables(0)

Thats an odd struture for a data file (two lines per record). Usually its one line of data per record with some type of delimiter or fixed length to define each of the fields.

If your sure that is the right file structure, GeekByChoiCe gives a good example of how to input the file. Also I would add that you could add your listbox items right in the same loop rather then adding a second loop like you have done in the original example.

Sknake is right, you need to provide the error message for anyone to provide detailed help other then guessing at whatelse might be going on in your code. For instance, I do not see where you are opening the database connection although you may be doing that elsewhere in the program.

Also is this a single user program, meaning there can be only one person with an account and login? I dont see a where clause in your update query string meaning if there is more then one account, it will update every record in that table.

You can set the visible properties back and forth appropiately. Another way is to set the z-order of the control to be layered on top of the other control.

PictueBox1.BringToFront()

Char.IsDigit will return false for decimal points. Especially with using the keypress event where it is not comparing the whole input only the individual character. Example. (Textbox1.text = 1.3) would return 3 results; true-false-true. However this may not be a concern since the original poster hasnt mentioned if it will be whole numbers or not.

I dont want to sound like an advocate for Crystal - personally Ive been looking to find something better myself, I just wanted to point out that it is free and a has whole design environment buit in to Visual Studio. And yes there licensing has changed from previous years, it used to be an add in product and is now fully part of VS. I remember in 2000 they had some ridiculous requirements at the time...

I do agree with Code Doctor that Sql Server Reporting Services are worth checking into and seeing if they meet your needs. Of course I dont even know if your using Sql Server...

One of the disadvantages of Crystal is slow load times. You can google that and see many people mention the same; most of which tends to be a poorly designed report in the first place. But even when properly designed, when you use a crViewer.Load object it takes a few seconds to load into memory.

With displaying reports you probably wouldnt even notice a few seconds of the report loading but priority for me at the moment is after a lot of processing is done in the background the end results are the reports being sent directly to different printers. I dont know if SSRS will solve this for me since it seems browser based.

What I do like about CR is that I can design a report based soley on a typed dataset (.xsd) without …

It depends on the type of numbers you are inputting. If whole numbers, I would suggest using the numericupdown control such as suggested above. Other options such as suggested above will be triggered with each key stroke, just of the textbox's validating event might be better since it would not trigger as much and wait for the full input before checking. Also keep in mind if your values contain a decimal point, neither the isnumeric of char.isdigit methods will work.

hi tom thanks, are there any sites that can tutor me on working with datasets?

Yes there are many resources & examples available on the internet. If I can recommend one particular book that will fully explain the many different ways of programming with VB & databases it would be Pro ADO.NET 2.0 I think there may be a downloadable version of this book available too if you search for it.

You using a DataReader in this coding; hence you can only read the values not write back to the database.... You need to spend some time looking through some tutorials about working with datasets

I agree, the control events are specific to that form therefore they should stay in that form. However the code within the events can be broken up into sub or functions. The subs and functions can be in the same for or a module file.

Button1_Click
Call Sub1
Call Funtion2
Call Sub3
End Sub

If the sub or function you define is specific to the one form, then I would code it within that form. If the sub/function could be used by multiple forms then you should put it into the module file, so that all forms can call that sub/function and you dont have to write the same coding more then once.

Also you can use the Region method to help group and organaize your coding better within any of the files. Type #Region followed by the name you want to use within double quotes. Then you can move some of the coding subs/functions/events into each of the Regions you define. You can then expand or collapse the region by clicking on the + or - sign shown.

#Region " Form Button Events "

Button1_Click...

Button2_Click...

Button3_Click...

End Region

My fault, Sknake is right - the command object is actually sqlCommand not datacommand. See what I get for coding in this text box with out the autocomplete features... lol

You could create a loop to test each char to see if it is a number or mathmatical operator. Also keep in mind with testing with methods such as IsNumeric it doesnt see decimal points as numeric.

Here is an example.

Also in you example, a dataview is just making an additional copy of your datasets table. You dont really need it unless you want to filter the results further.

sqlstr = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' ORDER BY TABLE_NAME"

Dim ConnString As String = mySQLconn

        Dim ds As DataSet
        Dim dv As DataView
        Dim cmd As New DataCommand

        Try
            Dim SQLConn As New SqlConnection(ConnString) 'The SQL Connection

            ds = New DataSet
            'dv = New DataView

            Dim ad As New SqlDataAdapter()
            
            cmd.Connection = SQLConn
            cmd.CommandType = CommandType.Text
            cmd.CommandText = "SELECT * FROM @TableName"
            cmd.Parameters.AddWithValue("@TableName", "[" & lbListAllTables.SelectedItem & "]")

            ad.SelectCommand = cmd
            ad.Fill(ds, "SelectedTable")

            'dv.Table = ds.Tables("SelectedTable")

            Me.DataGridView1.DataSource = ds.Tables(0)
        Catch ex As Exception
            MessageBox.Show(ex.Message)

        End Try

You can build crystal reports right in the visual studio software too without the need of purchasing any full seperate editions of crystal reports.

Add a module file to your project and change the declaration of your subs and functions to public in the module file. And if your coding in the seperate file is naming something specific on your form such as Label1.Text =; you need to specific in the module the name of the form such as Form1.Label1.Text = ""

As for variable declaration, in a module form you want to use the keyword Public instead of Dim, if you want the variable values to be seen from other forms.

Its kind of a real pain in the butt to sort a listview besides its typical sort property that is provided. You need to define some functions in able to sort specific columns. An example can be found at this link on MSDN

haha just kidding, it depends on how your querying the data for me to say exactly how to check.

If your results are being returned to a DataSet/DataTable, you can check the row count and if it is zero, you can display the message.

If myTable.Rows.Count = 0 Then MessageBox.Show("Better luck next time")

If your using something like ExecuteScalar, a DataReader etc you may need to check that the object is not null.

MessageBox.Show("No record found")

First of all, you should use parameters instead of concatenating query strings. That in itself might resolve the issue but either way you can add brackets around the [table name] so the database knows.

Excellent advice about the Using clause(s). I never (at least since Ive learned better) define an sql connection outside of the individual sub that it is being used, and that within a using clause. I store the connection string in a global variable.

Below is an example of a call I just finished for my own program. It does the same as yours, calls a stored procedure (SP) to return a result set. Well Im using the single SP and call to return multiple result sets.

The results will fill two different tables within my dataset appropiatley. The only difference in the coding, since multiple results are being returned is that you have to tell it exactly which result set goes to which dataset table. This can be done thru the DataAdapters TableMappings method.

The SP returns the results in the order the queries were written and assigns the name "Table" to the first result set, name "Table1" to the second, "Table2" to the third etc...

Private Sub cmdExecuteSproc_Click()

        Using con As New SqlConnection(g_strDbConnection)
            Dim cmd As New SqlCommand
            Dim da As New SqlDataAdapter

            cmd.Connection = con
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "spOrdersCustomersSelect"
            cmd.Parameters.AddWithValue("@PkId", txtPkId.Text)
            cmd.Parameters.AddWithValue("@CustKey", CInt(txtCust.Text))

            da.TableMappings.Add("Table", "tblCustomers")
            da.TableMappings.Add("Table1", "tblOrders")
            da.SelectCommand = cmd
            da.Fill(m_ds)

            da.Dispose()
            cmd.Dispose()
        End Using 

End Sub

As mentioned by Sknake there are benefits to the using block, if your db connection is closed it will open it for you (similar to the DataAdapter). At the end of the block, it will …

Indirectly, yes. A datagrid doesnt actually contain data it only displays the data from the datasource you have attached to it. For example, if you populate a dataset, datatable, array etc and assign it to the datagrids datasource you can see the data. The user can then select a row to delete, and you delete the record from the attached datasource, not actually the dg itself.

Try this:

Select Distinct Format(dates, "mmmm") AS [dates] From...

DateName must be specific to Sql Server which im more familiar with. There should be an equivlant type function in Access, its just a matter of looking up the syntax in its help file.

You defined & attached an insert command to your dataadapter. Now when you call the da.Update method, it will look for any new records in your dataset and insert them into your database. The problem of course is you never added any new records into the dataset.

You should always open & close the connects as needed and not leave it open for the life of the program. Otherwise you are not optimizing connection pooling.

You call to your stored procedure was made with a dataadapter. When you use a dataadapter, it will actually open the connection to make the call and close it afterwards.

For example, you could execute commands directly to the database from the command object, without ever using a dataadapter but with the command object you will need to explicitly tell it to open & close.

You can set you form's topmost property to true, to keep it above other forms. You can set the form's opacity property by percentage to see thru your form.

Are the same mapped drives available while logged on as a user? If its a security error, you need to lower the security settings on your lan

Select Distinct DATENAME(mm, CONVERT(CHAR(19), [dates])) AS [dates] From....

That will fomat the name of the month to be returned. However concatenating query strings like that is very poor programming format. You should look into how to convert that and use parameters.

'Create the stored procedure in your database
'it is cleaner and actually will run faster!

Create Procedure spGetConcessionDate

	@ConcessionId As VarChar(25)

As

Select PLIExpiry 
From tblConcessions 
Where ConcessionId = @ConcessionId

Public Function GetConcessionExpirationDate(ByVal strCompanyId As String) As Date

        Dim dtExp As Date = Nothing

        Using con As New SqlConnection()
            Dim cmd As New SqlCommand

            cmd.Connection = con
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "spGetConcessionDate"
            cmd.Parameters.AddWithValue("@ConcessionId", strCompanyId)

            con.Open()
            dtExp = CDate(cmd.ExecuteScalar)
            con.Close()

            cmd.Dispose()
            con.Dispose()
        End Using

        Return dtExp

    End Function

That is a really awfull way of trying to get your results. As to your answer though, your function is returning a string datatype of a date but you need to apply an actual datetime datatype to your datetimepicker.value.