Posts by TomW

Time will automatically be applied as 12am thats why its better to use a between statement to give you all records for the specified day.

Also dont forget to include the # signs surrounding your date

Database's hold a DateTime value so its important to include the time such as using a between Todays date at 12:00am and tomorrows date

The field parameters seem to be correct unless im missing a misspelling somewhere between them. I would start by making two changes and see if you still have the same problem.

01) Convert the @DesignationID into an in (CInt(TxtDesigCode.Text))
02) Remove assigning them into PARAM (no clue what that is even doing there or where you are using it. However cmd is your command object and adding the parameters directly to that is all that is needed.

Last suggest is while stepping through the code, see exactly what values are being passed at the time of execution. If you are still having problems please give more detail to the error message that you are receiving.

You need to take a look at some tutorials on how to program for databases. What you are asking is rather simple but it seems that you dont yet have a grasp on even connecting to a database since your question isnt more exact.

Here is a link to the MSDN that will give you some examples and get you started. If you are still stuck after that be more specific about where exactly you are stuck trying to retrieve the data.

Data Walkthroughs

Just to clarify, you want to show a messagebox when the form first loads correct?

Public Class Form1

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

        MessageBox.Show("Hello World" _
                        , "Messagebox Caption Here" _
                        , MessageBoxButtons.OK _
                        , MessageBoxIcon.Information)
    End Sub

End Class

Can you show your insert statement from the stored procedure? Without seeing it, I'm guessing either the parameters are wrong, missing or misspelled.

Also assuming that DesignationID is an numeric field I would suggest converting the textbox value to a numeric datatype (CInt(TxtDesigCode.Text))

Lastly there is no need of assigning anything to Param (Param = ...) change that so it just starts with the cmd.Parameters.AddWithValue(...

In that example you are still not closing the connection if there is an error. I would add a FINALLY section to that Try/Catch block to close the connection.

Your error should be tripped on the line actually executing your statement. Therefore all that coding in your class sub should be within a Try Catch Finally block. In the finally section you should add the statement to close your connection so that it is closed whether or not an error occurs.

Also you may want to consider just holding your connection string at a global level and recreate db connections as needed in a USING block. I know it sounds odd to keep recreating the same object over & over but my testing has shown it to be faster then re-using it. Also this ensures the connections life only lasts for that of the USING block and that connection pooling can be reset for each of your calls.

You have to point the datagridview's datasource property to the table you are storing the results

DataGridView1.DataSource = myDataset.Tables("myTable")

As to the pictures, this can be done but is not recommended. It is much better to store the files path to a database and use that path to load the image when the individual record is selected.

Storing images will greatly increase the size of your database and slow the speed of any queries. The pictures themselves take up a lot of space plus every time you edit a record that contains a picture (doesnt even matter if your changing the image or not) the existing record is discarded but still held in storage and a new record created in the database. So if you have two columns (colName & colImage) that has a size of 5mb and you update the name column, you will now have a size of 10mb's in the database for that one record. Doing database maintainaze to shrink or compact the database will help but again its much better to just store the file path compared to storing the pictures.

The following link has many walkthrough examples of how to work with vb.net & sql server. If you still are having a problem, let me know

Data Walkthroughs

I actually agree with you. I definitely think OOP has its place but for the most part I limit it to things such as needing multiple instances with different data at the same time. If its a case of only needing things one at a time, a regular module file may be more helpful where you can limit the objects/variables life span to the running of the individual function/sub that is called.

Other then that the best you can do is attempt to limit the lifespan of data, such as creating only what is needed and destroying as soon as done with it. I think adding an IDisposable method to dispose of your objects after each use is probably the best you can do as far as cleanup. Even calling the garbage collector doesnt actually mean it will be released as soon as you call it.

If you want to run an experiment of creating objects and see how lousy the memory usage is handled. Create a simple blank project where the only thing the main form in a button click event is creates an instance of a new form and shows it. Run your task manager and keep opening forms and see how it just accumalates in memory. Then try adding the garabage collector to clean up after you close the form and you wont see much difference.

After that change the call from showing the form to showdialog. Now for some reason the Form.Show does …

So really what I'm asking is, is that for any object created that has a number of functions, procedures, and maybe constants defined - are these functions/procedures/constants taking up memory for each object defined? For instance, if a word object has the function "getWord()" defined in memory, if I create 50 word objects, will "getWord()" be defined in memory 50 separate places?

Yes

I would pick something worth showing potential employers in the future. Make sure it includes common functionality that any company could use such as intensive data processing/ importing-exporting files, reports, graphs etc.

When I went thru school I seen to many immature programs (fantasy sports seemed to be the #1 topic, beer inventory, games, hacker utilities etc) made and although some of them were very good it wasnt something professional to go and show at interviews.

Leo, yes you can (and should) store the path and retrieve it and use that to load your images. To backtrack, you can upload pictures to the database (it is possible) but is a very bad practice to do so for reasons mentioned above. I'm glad you are reasonable enough to take the suggestions and opt to use a more efficient method.

As to your question, it is very general. What exactly do you need help with, creating a connection string, executing a query, loading the image?
Here is a link that will provide a lot of usefull examples for database programming from the MSDN:
Data Walkthroughs

I would need more details in order to help. What database are you using, what is you connection string etc.... Mark this thread as solved and start a new one about the connection strings and I will see if I can help.

When you created the report using the wizard, did you point it to a database or directly to your typed dataset (.xsd file)? Im wondering if the report is somehow attempting to pull the data from the db rather then use the dataset set being passed to it. Either way I cant really tell without some examples to see exactly what you have done.

Glad to help. Sorry there isnt an easier way or atleast one that I can find, I definitely need to accomplish the same thing for some forms. There is also one other way I know of that consists of using subreports. I didnt think to mention it because I have discounted them myself previously, too slow to load and print...

Glad it worked. You should have gotten a more clear descriptive error msg.... :(

Is this something you can upload including the db, so I can take a look at it?

Well in that update statement alone, the only think I can think of is UserName as being null when not expected, Did you change the column name Sales Orders in your database to be a single word? This has to match exactly? And I can picture an order number having a decimal point, are your sure the datatype in your database is the same? Lastly LockedForEdit is a string datatype in the db?

What is UserName and where is it coming from?

Saving pictures to a database isnt optimal and should be avoided if possible. Including pictures can greatly increase the size of your database and reduce the speed of its processing. Likewise everytime you update a record that contains a picture, even if your not touching the column with the picture, that record size is doubled in your db making it continue to grow.

It is much better to store the path of your picture in a database column and use that path to load your pictures when needed.

If you need any help just let me know. My example sends the report directly to the printer without displaying it on the screen first, but the same applies, set the footer size before displaying the reportviewer.

In case I made the above sound more complicated then it is, basically all Im doing is using the blank footer space to emulate the white space of any missing records so that in appearance the details section appears to be a fixed size.

I cant believe that there wasnt an easier solution to this but as said, I had the same problem and spent time researching it and this is the best that I could come up with for resolving the problem.

When you bind data to a listbox, most of the time the indexs are the same order as the data records in your table so it might not be a good idea to change item indexs directly.

You can use the valuemember property of an item to hold an invisible value that can equate to your table index.

Just went through this exact situation a few weeks ago myself. Suprisingly there is not an easy solution for this although I did find a work around.

There is no way to set the total height of the details section iteself, only the height of the individual item records. So the first thing you want to do is calculate the indivual record height and then the total for all 10 records. So say the height is 136 for each individual details record, total for 10 shown records should equal 1,360.

Ok under the details section add a Footer section to the report. If you are actually using a footer section, you can add an additional footer. You just want to make sure this blank footer is directly underneath the details section. Set this new blank footer to your total size: 1,360

Now Im not sure how your report is set us to draw the data. Are you allowing the report itself to query directly from the database or passing it a filled dataset? I'm doing the latter. Either way before you display the report you need to know how many records will be displayed so you can adjust the height programmiticaly.

Now the footer does allow us to adjust its height from VB. So I'm creating a blank section that would appear to be the size of 10 records if no records at all were returned. Now what we want to do is shrink this footer …

I dont think this is your problem but your use of transactions is incorrect. In order for a transaction to work you either have to commit the changes or roll them back if it errors which I dont see being done.

The only other mistake I see is "Sales Order" if you have a space in your column name words, you much enclose the column name in brackets. [Sales Order]

Thats not a short answer to give so instead I'll give you a link that offers some step by step database programming examples. If you're still stuck on something specific let me know.
Data WalkThroughs

If your app is running on a network I would say use the Active Directory to validate there login and assign security level but the groups in which they belong too. However you seem to be creating your own login system where the info is held in the database itself. So essientially you connecting to the db already for validation & security levels. So by that, it is your own design how you want to go about creating restrictions.

A messagebox should not be returning a value of zero. It is better to use the DialogResult constants to see what the values mean but again either way none of these values should be zero. Even clicking on the X button in the top right corner would return a default value.

Dialog Returned Values:
OK : 1
Cancel : 2
Abort : 3
Retry : 4
Ignore : 5
Yes : 6
No : 7

Private Sub Button1_Click(...) Handles Button1.Click

        Dim dlgResult As DialogResult = Nothing

        dlgResult = MessageBox.Show("Do you want to continue?" _
                                    , "Msg Caption" _
                                    , MessageBoxButtons.YesNoCancel _
                                    , MessageBoxIcon.Question)

        'Display selected values from previous messagebox
        MessageBox.Show(String.Format( _
                        "dlgResult String Value : {0}{1}" & _
                        "dlgResult Numeric Value: {2}" _
                        , dlgResult.ToString _
                        , ControlChars.NewLine _
                        , CInt(dlgResult)))

End Sub

If you need anymore help just let me know.

NO, there should be no reason whatsoever to call refresh. That only redraws whats already in there. If retrieving new data from the database is what you want, you have to requery the database.

Has a control or reference changed in the project? Also do you still have the problem after rebooting?

Yes that is a bit confusing, attaching the code would be helpful.

Wow the command builder is creating that statement? It has a parameter (?) where a field name should be and its leaving spaces between multi word field names without placing brackets around it. Where is the begining of the statement, Update Table / Insert etc?

To get a more detailed description of your query statement, add a line of code between the GetUpdateCommand and Adapter.Update lines, msgbox(Adapter.UpdateCommand.ToString)

I think it might be easier to dump the command builder (but I have a bias to command builders, always problematic) and just add your own Insert & Update statements to the data adapter. I can show you how if you need help.

Ok its a bit difficult to offer suggestions since I dont know what the rest of your program is doing but from the code snippet shown, your retrieving all records in a table just to update a single record? If thats the case, can we just execute an update statement directly to the database without even the need to return all records first?

Can you give me a summary of what the form is doing overall and the usuage of this update? Is it a one time update or something done multiple times or throughtout multiple records etc?

When you call a datasets AcceptChanges method it automatically sets all rows to unmodified, removing and new, updated or deleted work you did to the dataset... So by the time you call update a few lines later, theres nothing to update

Also when you use a dataadapter, you dont have to explicitly open & close the db connection, the dataadapter will automatically do it for you.

Not sure why your disposing of your dataset after updating but then filling it right after that. Also I dont know what values are in your dataset or what your SearchResults function is doing to help much further about your label problem.

Dim ds As New DataSet

Using con As New SqlConnection(strDbConnectionHere)
    Dim cmd As New SqlCommand
    Dim da As SqlDataAdapter = Nothing

    cmd.Connection = con
    cmd.CommandText = "Select * From Member Order By Names"

    da = New SqlDataAdapter(cmd)
    da.Fill(ds, "Member")

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

ListBox2.DataSource = ds.Tables("Member") 'Binds the table to your listbox
ListBox2.DisplayMember = "Name" 'The col in the tbl you want to display

Just replace the sql objects with the equivelent OLEDB objects

A bit different but I wanted to offer a suggestion. You are first querying the DB and manually entering each name into your listbox and then re-querying the DB when a selection is made to get the specified record.

I'm thinking that it would be easier and more efficient if you just filled a dataset table to start, bind the name column to the listbox without having to itterate thru a loop of all records and when a selection is made, you only need to filter your existing datatable in memory rather then requerying the database for the record. Storing the results in a dataset would also be benificial for changes to the records (delete, update, insert etc)

However the size of your database would determine whether or not this would be optimal, I wouldnt suggest keeping that many records in memory for instance if there were tens of thousands of records to choose from. But at the same time I wouldnt recommend filling a listbox that large either... :)

Using sw As New System.IO.StreamWriter("C:\Sample.txt")    'Opens/creates file & StreamWriter object
    sw.WriteLine(TextBox1.Text)    'Outputs textbox value
End Using    'Closes & disposes StreamWriter object

Attached is a small example that shows just one of many ways that can be used to pass values between forms. This particular method creates a public property on the child form that can be accessed from the calling form.

You can use a select statement to fill a DataSet/DataTable. You can then export the entire dataset to an xml file with a single call, youDataSet.WriteXml(strYourFileNameAndPath)

Exporting to a text file is a bit more involved, you have to loop thru each of the records in the datatable to extract each column field and format your line object to use the StreamWriter.

You can create public properties in your dialog form that can be accessed and the values can be passed back to the main form.

Yes you can. Take a look at the following link that will provide some guidance for working with databases Data Walkthroughs

Take a look at the Array.IndexOf method in the help file. It will show how to search an array and return the index of the result if found or -1 if not found.

You stated in another post that this assignment is already completed, if so you may want to mark this as solved. Also since someone else may have helped you through this, I want to ask if you actually understand the work in its entirety. If not please give an example of the coding and your question about it and I will try to help.

CR has always seemed difficult to use, the documentation for it is very difficult to understand or even find what you are looking for. I dont think the lack of responses are due to your questions just that most have a problem working with CR themselves.

As for passing parameters, I would first create the parameter field on the report, say the name is "Par1". I would then call and pass the parameter value as follows:

rpt.Load
rpt.SetDataSource(myDataSet)
rpt.SetParameterValue("Par1", txtReportParameter.Text.Trim)

As far as passing a file path to load the picture, I dont think this can be done. Of course I would think that this would/should be possible but I havent seen a way of doing so, except what Ive shown you before as an example.

Have to agree with ithelp, if you have others do it for you, you wont learn it for yourself. You would get much better results and help from others if you at least give it a try and then state where exactly you may be stuck.

You do not need to explicitly close the reader & writer in this situation. The use of the "USING" block will open, close & dispose of the object when it exits the USING block.

Without using the mouse to move the group boxes, assign them all the same location in the properties window. This will position them on top of each other and should prevent them from becoming a child control.

It sounds as if, the groupboxes 2-4 are contained within the first groupbox container... If you move groubbox1 around on the form, does all the other groupboxes move with it?

Running code in a constant/endless loop would be exactly that, your program freezing up the pc because its constantly running something over & over to search your server's status.

If keeping in a console app, I would set it up so that the windows task scheduler runs the process once at interval set times or create it as a windows process. Another option is to create a windows app on a timer basis but again, this would be a program constantly running in the background.