Posts by TomW

Its a completly different direction but I would again suggest changing it to a DataTable instead of a class where your doing so much coding. Also there are just so many advantages as I pointed out above, specially when you want to do things such as work with many student records at a time.

'Creating a new record

Dim row as myTable.NewRow
row("ClassID ") = intNewId
row("StudentID")   = intStudentId
row("Grade") = strGrade
row("Exercise") = strExercise
myTable.Rows.Add(row)

'Displaying all records
DataGridView1.DataSource = myTable

Loading the records from a file is as simple as

myTable.LoadXml("MyFile")

'Saving
myTable.WriteXml("MyFile")

Sorting, Searching and filtering can be done by any column in the table

'Show all student records for a specified class id
myTable.DefaultView
myTable.Filter = "ClassId = " & intClassId

Updating a record

Dim row As DataRow
row = myTable.FindByStudentId(123)
row("Grade") = A+          'New grade

'Displaying and printing reports

Dim Report As MyReportName
Report.DataSource = myTable
report.PrintToPrinter(1, False, 0, 0)

Plus working with typed datasets give you automatic validation on datatypes, ability to set default values, limit string length etc

Dont forget to mark the thread as solved and rep babbu for helping ya.

Move your static countdown variable to form level.
In the btnReset click event, assign the new time value of 3 secons to your form level variable (m_CountDown)
And re-enable your timer if necessary

I'm not sure what exactly your question is here? Also can you give more details about the id number itself and what your trying to accomplish? Is this a barcode?

One suggestion right off the top is to change this into a Function to return true if valid and false if not.

You can do this by creating a macro in Excel itself but then it kinda defeats your VB plug-in...

Cheat Tip:
When trying to figure out VB syntax for automating Excel, I'll set it to record a macro and then perform whatever function I want. Then all I have to do is stop the macro, open it up for editing and steal the syntax.

You can do this as a class or even as a user defined structure (just discussed something similar here). However for this particular situation I would suggest creating a Typed Dataset with a DataTable inside that meets each of your fields. There are many benfits from adding it to a table such as giving you search, filter, displaying records, sending to reports and outputting the info to a file becomes simple to save, load and edit. Dataset.WriteXml("FileName") all done.

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.

In the Combobox's property window

Set AutoCompleteMode to Append
Set AutoCompleSource to ListItems

No I want to see exactly what the Insert Into statement that the command builder made looks like.

Hmmmm check a couple of things.
01) Have a messagebox display the actual comandbuilder.GetInsertCommand.ToString and let me know exactly what the insert statement looks like.

02) Does your table have a primary key?

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

i am aware of the insert query. but i wanted to add data using command builder.

See my above example, creating the command builder an attaching it to your dataadapter, only allows the commandbuilder to get the structure of the tables from your selectcommand. You still have to individually assign each of the new commands to the appropiate commands in the dataadapter.

m_DataAdapter.InsertCommand = m_CommandBuilder.GetInsertCommand        
m_DataAdapter.UpdateCommand = m_CommandBuilder.GetUpdateCommand        
m_DataAdapter.DeleteCommand = m_CommandBuilder.GetDeleteCommand

On an additional note; I would suggest not using "Select *" in conjunction with a command builder; instead explicitily write out each of the column names you want to work with. Previously I have run into problems doing the former mentioned.

Using the "Select *" method in conjuction with typed datasets causes an error with the command builder if you ever make changes to the database table in the future such as adding additional columns, regardless if you need to use them or not in your program, the typed dataset then no longer matches the command builders table structure. You would then need to go and update the typed datasets and redistribute the program with every change.

Your syntax is correct. You do not have the coding in the Form_Load event, you have it in the button click event, you must click the button before you should see the changes.

You can set the datatable to DefaultView and then use the RowFilter method, to only show the records that meet your filter critera. There is a general example below:

Sub ListBox1_SelectedIndexChanged

    If m_blnLoading = True Then Exit Sub

    With MyDataSet.MyTable.DefaultView
        Select Case FilterCritera
            Case Critera1
                .RowFilter = "Column1 = 0"
            Case Critera2
                .RowFilter = "Column1 = 1"
            Case Else
                .RowFilter = "Column1 = 3"
        End Select
    End With

End Sub

Good to hear you corrected the problem; what was it?

Ok, let me start by asking a few questions?

01) Do you know how to create a stored procedure in your database?
02) Can you create a "typed" dataset
03) Have you created any Crystal Reports yet?
04) Your previous query example is selecting all the records in the table. How are you limiting it to the record that you want display & print?

Creating a Stored Procedure you can do right in the query window of Sql Server. As an example (again I would need to know your columns and what filter critera you want to use).

Create Procedure spClientSelect

	@Account As Int

As

Select
	Client,
	PhoneNo,
	Directory,
	Details
From Clients
Where AccountNumber = @Account

A general example of how I would create my connection, call the stored procedure, fill the dataset, load it to a report and send it to the printer.

Public Sub PrintClientLabel()

        Dim dsClient As New myTypedDataSetNameHere
        Dim report As New MyReport

        Using con As New SqlConnection(strDbConnection)
            Dim cmdSelect As New SqlCommand
            Dim da As New SqlDataAdapter

            With cmdSelect
                .Connection = con
                .CommandType = CommandType.StoredProcedure
                .CommandText = "spClientSelect"
                .Parameters.AddWithValue("@Account", intAccountNumber)
            End With

            da.SelectCommand = cmdSelect
            da.Fill(dsClient, "Clients")

            da.Dispose()
            cmdSelect.Dispose()
        End Using 'automatically closes & disposes the con object

        report.SetDataSource(dsClient)
        report.PrintToPrinter(1, False, 0, 0)

        report.Dispose()
        dsClient.Dispose()
End Sub

thanks

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)

Unfortunately no. You can open older versions of coding in 2008 but can not open 2008 in older versions.

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

Concatenating your query strings is poor coding. It is much better to use parameters. Take a look at sqlCommand.Parameters.AddWithValue. If you have any additional questions about it just let me know.

Another consideration is putting your query directly into a stored procedure in your database. Then you just have to link the command object to the stored procedure name and pass it the proper parameters.

Im not sure exactly what your question is here... The title mentions writing to a file but you dont mention that in your post. Not sure what your trying to get but you can add icons to a listview control.

This might be what your after:
How to: Enable Tile View in a Windows Forms ListView Control

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....

Keeping pictures in a database is not a good idea. It is much better to store the path to the picture file and use that to load your pictures.

Pictures can make your database grow very large very fast. As an example, every time you update a record, even though your not changing the actual picture, a whole new copy of the record is made in the database (until you you do database maintainance).

Changes below. Also I dont know what fields your table has but the CommandBuilder object requires a primary key.

m_DataAdapter = New OleDb.OleDbDataAdapter("Select * from [Base Colours]", oledbcon)
        m_DataAdapter.Fill(m_DataTable)
        m_CommandBuilder = New OleDb.OleDbCommandBuilder(m_DataAdapter)

        m_DataAdapter.InsertCommand = m_CommandBuilder.GetInsertCommand
        m_DataAdapter.UpdateCommand = m_CommandBuilder.GetUpdateCommand
        m_DataAdapter.DeleteCommand = m_CommandBuilder.GetDeleteCommand
        DataGridView1.DataSource = m_DataTable

Retrieving Identity or Autonumber Values

A bit of a pain to do with using a dataadapter to execute the queries but the above link will show you an example of doing so.

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

Using CultureInfo you can display the numeric values to specific formats.

Formatting Numeric Data for a Specific Culture

the data adapter handles my update insert and delete query...

The dataadapter does not automatically create any of these commands.

If you are referring to the use of a commandbuilder attached to your dataadapter to generate the insert, update & delete statements; I would like to see the block of coding that creates this and fills your dataadapter so that I may see where it is causing you problems.

Also does the table have a primary key/unique field, the command builder needs this in order to be able to perform these statements.

Ok you did explicity give it a table name during the fill. However you didnt explicity asign the comandbuilder commands that you want to use after assigning the select command to the dataadapter. Take a look at my changes below.

Also Im assuming MessageId is a primarykey or unique identifier in the table; which is needed by the command builder.

Cutting out the seek sort and display stuff here are the key chunks of code :

Public Class Form1
    Dim inc As Integer
    Dim con As New OleDb.OleDbConnection
    Dim da As OleDb.OleDbDataAdapter
    Dim cb As OleDb.OleDbCommandBuilder = Nothing
    Dim ds As New DataSet
    Dim dbProvider As String
    Dim dbSource As String
    Dim sql As String
    Dim MaxRows As Integer

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

        dbProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"
        dbSource = "Data Source= d:/pricing/pricingV21.mdb"
        con.ConnectionString = dbProvider & dbSource
        con.Open()
        sql = "SELECT Message.messageID, Message.quoteRef, Message.MessageTitle, Message.MessageNotes, Message.MessageDate, Message.MessageReviewDate, Message.MessageCompleteDate, Message.MessageType, Message.MessageTrigger, Quote.QuoteAddress, Quote.QuoteClient, Quote.QuotePhone, [messagereviewdate]-Now() AS DateDiff FROM Message INNER JOIN Quote ON Message.quoteRef = Quote.QuoteID WHERE(((Message.MessageDate) <= Now()) And ((Message.MessageCompleteDate) Is Null))ORDER BY Message.MessageReviewDate"
        da = New OleDb.OleDbDataAdapter(sql, con)

        cb = New OleDb.OleDbCommandBuilder (da)        
        da.InsertCommand = cb.GetInsertCommand()
        da.UpdateCommand = cb.GetUpdateCommand()
        da.DeleteCommand = cb.GetDeleteCommand() 

       da.Fill(ds, "messages")
        MaxRows = ds.Tables("messages").Rows.Count
        inc = 0
        msgProgress.Minimum = 0
        msgProgress.Maximum = MaxRows
        NavigateRecords()

    End Sub

after all the action

Private Sub msgHappy_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles msgHappy.Click
        ds.Tables("messages").Rows(inc).Item("messageCompleteDate") = Now()
        da.Update(ds, "messsages")
     End Sub

If I am missing something obvious be gentle with me!

It will all depend on what the rest of your coding looks like. For example if I retrieve data from a table named Owners, it doesnt mean i put it in a dataset or datatable in the program thats named the same. I'm assuming this is the problem here.

Also I dont know what your update statement looks like or if you even created and assigned on you your dataadapter.

What i need to do in this program is only display the information found by the telephone, like ClientID, Telephone, Name, Address and Details, in some text boxes I named in previous post

clientebox
telefono
namebox
dirbox
details

After that im printing it like a receipt, this program will function as a Delivery Food Label in a box, just making it simpler for the user to find a client's information and printing it in a pizza box, making it easier for the delivery boy to have details on where to take the pizza

Ok this can work with the reader then.... I also print package labels and just to take a moment to explain how I do it, might give you some ideas for the future.

01) Select statement is created in a stored procedure in a database.
02) A typed dataset is created in the program that matches the data being retrieved.
03) Create a Crystal Report using the dataset as its field structure

In the coding then, all I have to do is assign the SP name and Parameter value to the command object and have it fill the datatable. I then just assign the datatable to the CR report which is sent right to the printer.

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.

Thats not an insert query...

[Date Sent] should be a date/time column, I dont understand why your converting the entire column to date.... Also try applying pound signs (#) before & after the date instead of single quotes.

That much looks correct, I'm assuming the error is in your actual insert statement connected to your adapter. Can you provide that query? Also add the exact error message that you are receiving.

Great, glad it helped.

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. :)

There are many errors. The first of which seems to be creating the connection to the database. First resolve the connection issue. (see my first post for an explaination), Im guessing that the datasource is incorrect but only you know the name and place of your datasource & database, so I cant give you the exact answer. For corrections to your comand object take a look at the below:

Dim strCon As String = ""

        strCon = "Data Source=.\ISCDEV;Initial Catalog=mangiamos;Integrated Security=True"

        'The using block is usefull for limiting
        'the life of object created within it.
        'Also when it hits the end using, the 
        'obj (con) is automatically disposed of

        Using con As New SqlConnection(strCon)
            Dim cmdSelect As New SqlCommand
            Try
                con.Open()


                '01) Assign the connect to the command object
                '02) Specify whether your inclusing a query string or using a stored procedure
                '03) Added "Where" statement, single quotes around comparison
                '    are no longer needed since a parameter will be used instead
                '04) Created a parameter and assigned it to the name given in the query
                '    also assigned its value.

                cmdSelect.Connection = con  '01
                cmdSelect.CommandType = CommandType.Text    '02
                cmdSelect.CommandText = "Select * from Where Clientes = @Telefono"  '03
                cmdSelect.Parameters.AddWithValue("@Telefono", txtTelefonobox.Text.Trim) '04

                'Before I go further I wanted to ask whether the 
                'reader will be enough? As it applies, it will only
                'display data. If you need to add, delete, manipulate and/or update
                'this data, the reader should not be used and instead be replaced
                'to hold the data …

hi

i'm trying to add a feature to an application where users of my application (installed on different computers) can share access db files when updates are made by one user but dont know where to start. Is there a tutorial i could look at or sample source code?

If your users are all on the same network then it should be one database that all the users are pointed too. If not on a network then as mentioned above, you can put a database on the internet and have all the users connect to the same source of data.

Try
            conn.Open()

            'There are a few problems with "cmdSearch"
            '01) Your query has no Where clause
            '02) Single quotes are not placed around your comparison
            '03) Concatenating strings wastes memory resources 
            '    and should be using parameters anyway
            '04) the command isnt given a connection to use
            Dim cmdSearch As New SqlCommand("Select * from Clientes = " & Me.telefonobox.Text & "")


            Dim stReader As SqlDataReader

            stReader = cmdSearch.ExecuteReader

            If stReader.Read Then

                'you are applying an object to a text value
                'without explicitly retrieving its proper datatype value
                'also if any of these values are null
                'you will receive an error since you
                'are not checking for null values
                Me.clientebox.Text = stReader(0)
                Me.telefono.Text = stReader(1)
                Me.nombrebox.Text = stReader(2)
                Me.dirbox.Text = stReader(3)
                Me.detallesbox.Text = stReader(4)
                ' Me.txtPicture.Text = stReader(3)

                stReader.Close()
                conn.Close()

            Else
                MsgBox("No entry has been found", MsgBoxStyle.Information = MsgBoxStyle.OkOnly)
                stReader.Close()
                conn.Close()

            End If

            'Could be written here once
            'instead of twice above
            'stReader.Close()
            'conn.Close()

        Catch ex As Exception
            'Option Strict would disallow the below conversion
            MsgBox(ex.Message.ToString, MsgBoxStyle.Information = MsgBoxStyle.Information)

If that much isnt working then either the name given to your DataSource or Database is incorrect. Unless of course its not actually windows authenticated and you need a login & password.

Dim strCon As String = ""
strCon = "Data Source=.\ISCDEV;Initial Catalog=mangiamos;Integrated Security=True" 

Using con As New SqlConnection(strCon)
    Try
        con.Open
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try
End Using

This seems to be some type of class project so what are you learning if one of us writes the entire thing for you? I'd be glad to help in any area of it that your having problems or help critique it when your done. Wow you could have coded the whole thing in the time it took you to write out that post...

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.

I dont know what the values are that you are passing. Are you sure that the control is not being added off the sides of the form? Run your control count after the loop to see.

Looking in the help file will show how to add and retrieve combobox items.

No you cant call it by name, thats why Im using the loop to itterate thru each textbox's and compare names in an IF statement.

The output parameter has to specified & declared in the actual stored procedure too. Also in the vb end, after execute the command you have to retrieve the value.

Create Procedure Blah

  @ReturnVal as Int Output,
  @Filter As VarChar(25)

Update tblBlah
Set...
Where Column = @Filter

@ReturnVal = Select Count(WhatEver) From Blah

cmd.Parameters("@ReturnVal").Direction = ParameterDirection.Output
 cmd.ExecuteNonQuery()
 intNewValue = CInt(.Parameters("@ReturnVal").Value)

Look up working with Stored Procedures and "Output Parameters"

Assign each of the new textbox's a name when creating them. Then in your sub, loop thru the textbox controls on the form until you find the one with the name you want.

Dim txtLetter(0) As TextBox
        txtLetter(0) = New TextBox
        txtLetter(0).Name = "txtLetter1"
        Me.Controls.AddRange(txtLetter)
        txtLetter(0).Location = New System.Drawing.Point(121, 0)
        txtLetter(0).Size = New System.Drawing.Size(30, 20)
        txtLetter(0).MaxLength = 1

For Each obj As Control In Me.Controls
            If TypeOf (obj) Is TextBox And obj.Name = "txtLetter1" Then
                MsgBox(obj.Text)
            End If
        Next