Posts by TomW

Relooking at my example, my syntax was incorrect and I hope you caught this mistake. I didnt use a comma between each of the columns & values and instead used the key word "And". Thats what I get for attempting to code in this webpage textbox... lol

strSQL = "Update [Employee] Set First_Name = @FName, Last_Name = @LName  Where Employee_ID =  @Id"

Parameters are pretty easy, I dont know why they dont push it more then the concatenated strings. Below is a quick example and here is a link for some more detailed info Configuring Parameters and Parameter Data Types (ADO.NET)

strSQL = "Update [Employee] Set First_Name = @FName And Last_Name = @LName  Where Employee_ID =  @Id"

command.Transaction = myTransaction       
command.CommandType = CommandType.Text            
command.CommandText = strSQL

Command.Parameters.AddWithValue("@FName", txtFName.Text)
Command.Parameters.AddWithValue("@LName", txtLName.Text)
Command.Parameters.AddWithValue("@Id", cint(txtStaffId.Text))

command.ExecuteNonQuery()

I'm not sure what is causing your datasource problem with crystal reports but just to offer an alternative suggestion...

When I create a crystal report I dont even assign it to a datasource or database; instead I connect it to a dataset. Then in my program, I fill my dataset to display to users and when there ready for the report, I just pass the filled dataset as the datasource. This has a few advantages such as the report not having to connect to the db and requery info that you may already be working with in a program.

Parameters are pretty easy, I dont know why they dont push it more then the concatenated strings. Below is a quick example and here is a link for some more detailed info Configuring Parameters and Parameter Data Types (ADO.NET)

strSQL = "Update [Employee] Set First_Name = @FName And Last_Name = @LName  Where Employee_ID =  @Id"

command.Transaction = myTransaction       
command.CommandType = CommandType.Text            
command.CommandText = strSQL

Command.Parameters.AddWithValue("@FName", txtFName.Text)
Command.Parameters.AddWithValue("@LName", txtLName.Text)
Command.Parameters.AddWithValue("@Id", cint(txtStaffId.Text))

command.ExecuteNonQuery()

Comma's look fine although the double set of quotes at the end of the where clause is not needed (it shouldnt hurt anything). Also in your catch block you should include a myTransaction.Rollback

Each of the string/text field values should be surrounded by single quotes in your update statement. However I would suggest changing this concatenated string to use parameters instead;. it will take care of not having to surrond the values in quotes and the database wont have to parse your statement multiple times times to format it for missing parameters.

You have to specify every single field that you want to search. You also have to specify if your looking for an exact match or perhaps one word (or even part of a word) within an individual field. For instance

Select *
From myTable
Where Column1 Like '%mySearchText%'
    Or Column2 Like '%mySearchText%'

The percent sign is a wild card telling it I dont care what letters/words are before or after my search text within that field. For exact matches you can simply use the equals sign followed by your search word(s).

Obviously that is if your are filtering during your actual query. To filter an already filled datatable, the method I mentioned earlier still applies. And yes you can add more then one column to be searched with that method. However searching every field is very process intensive I would suggest letting your database do that type of filter.

Just semantics/terminology but something to keep in mind, a datagridcontrol doesnt actually hold the data or connect directly to a database, you assign it a datasource such as a dataset/datatable etc... The underlying datasource is what you want to search & filter.

To answer your actual question, take a look at "DataView.RowFilter" in the help index. It will show you how to filter the data in your dataset/datatable and has an example. If you still need help just let me know.

Set the size of the form to the screen bound sizes. You will also need an API call in order to hide the taskbar; google, vb.net & Hide Taskbar

Me.Top = 0
Me.Left = 0
Me.Height = Screen.PrimaryScreen.Bounds.Height
Me.Width = Screen.PrimaryScreen.Bounds.Width
Me.FormBorderStyle = FormBorderStyle.None

There is no need to query the database multiple times for the same student record every time they click on an option.

Ardent, I would sugest starting by filling a DataTable with the (class) students you want to work with. Bind the Student Name to the DisplayMember of a Combobox and the StudentId to the ValueMember.

You can display the entire student table/records in a DataGridView (DGV) control with one line of code. When they select a student either from the ComboBox and/or a search TextBox, you can filter your DataTable (which you already have in memory) to display the selected record. Likewise when they double click on a record in the DGV you can bind the record fields right to there appropiate controls on a form for viewing/editing; without the need of re-querying the database or looping thru each record column to assign individual values to controls.

The height of a status strip is a set size. If you add an object such as a label to your status strip and change the statuslabel's font size to 28 (Tahoma), the statusstrip controls height would then change to 50.

Private Sub CheckedListBox1_ItemCheck( ) 

        If CheckedListBox1.CheckedItems.Count >= 7 AndAlso e.NewValue = CheckState.Checked Then
            lblStatus.Text = "To many items selected"
            e.NewValue = CheckState.Unchecked
        Else
            'Items selected are under 7
            'or they are in the action of deselecting an item
            'clear error message
            lblStatus.Text = ""
        End If

    End Sub

And just as an additional tip, any control that you are assigning values too, you should give a meaningfull name too. Label37 means little when your scrolling through hundreds if not thousands of lines of code.

Why is there two blocks of code for the same CheckedListBox control?

Also this is not needed
e.NewValue = CheckState.Unchecked = CheckState.Unchecked = True

Change to simply:
e.NewValue = CheckState.Unchecked

and your label is not showing, because your setting it to a blank string

Anytime :)

My coding was in the CheckListBox's ItemCheck event, e is the event parameter that is automatically present if you are in the right control event. I truncated the control event parameters to save text but will show the whole line since it confused you.

Private Sub CheckedListBox1_ItemCheck(ByVal sender As Object, ByVal e As System.Windows.Forms.ItemCheckEventArgs) Handles CheckedListBox1.ItemCheck

Here lemme explain this better

Say i have 1 checked list box but i want to limit how many options people can click to 6 but when they hit 7 a label appears and say "To many options selected" but then gets rid of the 7th option they clicked

LOL, ok then its the reverse, through coding you would have to check the selected item count and prevent this.

Private Sub CheckedListBox1_ItemCheck( ) 

        If CheckedListBox1.CheckedItems.Count >= 7 AndAlso e.NewValue = CheckState.Checked Then
            Label1.Text = "To many items selected"
            e.NewValue = CheckState.Unchecked
        End If

    End Sub

There is no limitation to the amount of items you can select in a checkedlistbox. This sounds more like something in your code that is causing this.

DateTimePicker1.Value = Now.AddHours(2)

For the most part I absolutely agree with Sknake above. However in certain situations, such as working with the same group of records over & over that Im not updating only filtering for a result, I do find it useful to hold the date in memory rather then run a new query every minute.

With that said, with data that you are updating which it seems you are updating one record at a time here, it will only take milliseconds to query and return only the record(s) you want to work with. This outweighs the cost of keeping an entire table in memory.

Searching for Data in the DataSet
When querying a DataSet for rows that match particular criteria, you can increase the performance of your searches by taking advantage of index-based lookups. When you assign a PrimaryKey value to a DataTable, an index is created. When you create a DataView for a DataTable, an index is also created. Here are a few tips for taking advantage of index-based lookups.

• If the query is against the columns that make up the PrimaryKey of the DataTable, use DataTable.Rows.Find instead of DataTable.Select.

• For queries involving non-primary key columns, you can improve performance for multiple queries of the data using a DataView. When you apply a sort order to a DataView, an index is built that is used when searching. The DataView exposes the Find and FindRows methods to query the data in the underlying DataTable.

It depends on the data that your sending back but I was thinking you might also be able to return the info in a dataset or datarow.

A list or ListArray can be returned from a function as Ryshad suggests above. Another option is you can create a user defined structure and pass that back from your function.

Public Structure Student
    Public Name As String
    Public Age As Integer
    Public GradePointAverage As Decimal
End Structure

Public Function GetStudent() as Student
     Dim udtNew As Student
    
    udtNew.Name = "Johny"
    udtNew.Age = 12
    udtNew.GradePointAverage = 3.5

    Return udtNew
     
End Funtion

public static string BuildSqlNativeConnStr(string server, string database)
    {
      return string.Format("Data Source={0};Initial Catalog={1};Integrated Security=True;", server, database);
    }
    /* -------------------------------------------------------------------- */
    public static string BuildSqlNativeConnStr(string server, string database, string username, string password)
    {
      return string.Format("Data Source={0};Initial Catalog={1};Integrated Security=False;User Id={2};Password={3};", 
        server, 
        database,
        username,
        password);
    }

No need to build your own functions, there's an sqlConnectionStringBuilder method built in that does this for you.

Dim conBldr As New SqlClient.SqlConnectionStringBuilder

        With conBldr
            .DataSource = "ServerName"
            .InitialCatalog = "DataBaseName"
            .IntegratedSecurity = True
        End With

        MessageBox.Show(conBldr.ToString)

Anytime... dont forget to update the thread and mark it as solved.

There are many options for passing values.

01) you could use a public variable in a module file
02) You can pass it thru the constructor of the new form
03) You can create public properties in the new form and pass them thru that
04) You can access the data directly from the form1's controls

Sub Form2_Load()

myValue = Form1.TextBox1.Text

End Sub

Try this. And you can set the TextBox's ReadOnly property to true so that the user can not edit it. I did it in code below but you can set it right in the properties window so its not re-running every time (although it wont hurt anything just isnt needed to run more then once)

Dim dlgSaveFile As New SaveFileDialog()

        dlgSaveFile.Filter = "jpeg Image|*.jpg"
        dlgSaveFile.Title = "Locate Where To Save File Please."
        If dlgSaveFile.ShowDialog <> Windows.Forms.DialogResult.OK Then Exit Sub

        TextBox1.ReadOnly = True
        TextBox1.Text = dlgSaveFile.FileName

        'Code to save file(s) here....

There is a complete sample available for download in the help file and also a detailed example of working with the ping class. Just type in "Ping" in the help files index

No, there is no built in functionality in .Net for adding these features to a listview control. Although if you google the subject you can find some examples where people added coding in an attempt to add these features but I wouldnt even recommend it; all the ones I looked at were very buggy and caused memory problems. You would be better off purchasing a 3rd party control if this is a must have feature or looking for a different control that fits your needs.

If your only returning a single value, you do not need to use a reader object or the ExecuteReader method. I would suggest using ExecuteScalar instead, it will return the singular value and use less resources. The help file will show detailed info with an example. If you still need help just let me know. :)

I could not get it to work using the '?' as the parameter name. I had to give it an actual name; like CodeDoctor suggested above.

To comment about the above post, "Password" is not a reserved keyword in ms access.

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"

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.

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

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.

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 …

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.

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