[RESOLVED] Am I going about this all wrong?

[Pasvorto]

I am moving systems from VB6 to VS2008. I am trying to do things in the "NET" way. On this particular form I had a FlexGrid. I entered an employee number and it went to the time card database and filled the grid with the employee's punches. In 2008 there is no Flexgrid. I have a data grid. I use a reader to get the employee information and another reader to get the time card data. What i don't get is how to fill the cells/rows in the data grid. Do I use the sql statement in the 2nd read as the data source for the datagrid? Or, am I just using VB 2008 to write VB6 code?

Dim reader As SqlDataReader
            Dim sql As New SqlClient.SqlCommand("SELECT * From EMPFILE WHERE [EMPNUM] = " & CInt(txtEmpNumber.Text))
            sql.Connection = cnxntech
            sql.CommandType = CommandType.Text
            reader = sql.ExecuteReader
            Using reader
                If reader.HasRows Then
                    lblEmpName.Text = reader("NAMEF") & " " & reader("NAMEL")
                    lblEmpName.Refresh()
                    '
                    '------------------------------------------------------------
                    ' get time card data
                    '------------------------------------------------------------
                    '
                    Dim reader1 As SqlDataReader
                    Dim sql1 As New SqlClient.SqlCommand("SELECT * From TIMECARDS WHERE [EMPNUM] = " & CInt(txtEmpNumber.Text) & " ORDER BY [PAYDATE] DESC, [SHIFTDATE] DESC")
                    sql1.Connection = cnxntech
                    sql1.CommandType = CommandType.Text
                    reader1 = sql1.ExecuteReader
                    Using reader1
                        If reader1.HasRows Then
                            While reader1.Read
                                '-------must figure out how to get to columns
                            End While
                        End If
                    End Using
                    sql1.Dispose()
                    reader1.Close()
                    '===============
                End If
            End Using

Any guidance will be appreciated as I will be using the same sort of processing in many places withing this app.

[amrita]

Yes. Fill a datatable using dataadapter and assign the datatable to datasource of grid.

[GaryMazzone]

I would change this:

Dim sql1 As New SqlClient.SqlCommand("SELECT * From TIMECARDS WHERE [EMPNUM] = " & CInt(txtEmpNumber.Text) & " ORDER BY [PAYDATE] DESC, [SHIFTDATE] DESC")

Like this

Dim empNum As Integer
If Not Interger.TryParse(Me.txtEmpNumber.Txt,empNum) Then
    MessageBox.Show("Please enter a valid Employee Number")
    Exit Sub
End IF

Dim sql1 As New SqlClient.SqlCommand("SELECT * From TIMECARDS WHERE [EMPNUM] =  @EmpNum ORDER BY [PAYDATE] DESC, [SHIFTDATE] DESC")
sql1.ParametersAddWithValue (@EmpNum) = @empNumber

Then for the datagrid

                       Using reader1
                        If reader1.HasRows Then
                              Me.DataGrid1.DataSource = reader1
                        End If
                    End Using
                    sql1.Dispose()

[Pasvorto]

Thanks. That's kind of what I thought. However, I am getting an error for this line:

sql1.ParametersAddWithValue (@EmpNum) = @empNumber

'ParametersAddWithValue' is not a member of 'System.data.sqlclient.sqlcommand.'

[GaryMazzone]

I miss typed should be sql1.Parameters.AddWithValue


Should be like this
sql1.Parameters.AddWithValue("@EmpNum", empNumber)

[Pasvorto]

Cool. It seems to like this:

sql1.Parameters.AddWithValue("@EmpNum", reader("empNum"))

I will use this as a template.

[Pasvorto]

Not quite working yet. It is not populating the data grid. I stepped through the code to get the time card data and it is executing every step.

            '---------------------------------------------------
            ' get employee data
            '---------------------------------------------------
            '
            cnxntech = New SqlClient.SqlConnection(cnxntechstring)
            cnxntech.Open()
            Dim reader As SqlDataReader
            Dim sql As New SqlClient.SqlCommand("SELECT * From EMPFILE WHERE [EMPNUM] = " & CInt(txtEmpNumber.Text))
            sql.Connection = cnxntech
            sql.CommandType = CommandType.Text
            reader = sql.ExecuteReader
            Using reader
                If reader.HasRows Then
                    reader.Read()
                    Dim empnum As Integer
                    empnum = reader("empnum")
                    lblEmpName.Text = reader("NAMEF") & " " & reader("NAMEL")
                    lblEmpName.Refresh()
                    '
                    '------------------------------------------------------------
                    ' get time card data
                    '------------------------------------------------------------
                    '
                    cnxn = New SqlClient.SqlConnection(cnxnstring)
                    cnxn.Open()
                    Dim reader1 As SqlDataReader
                    Dim sql1 As New SqlClient.SqlCommand("SELECT * From TIMECARDS WHERE [EMPNUM] =  @EmpNum ORDER BY [PAYDATE] DESC, [SHIFTDATE] DESC")
                    sql1.Parameters.AddWithValue("@EmpNum", empnum)
                    sql1.Connection = cnxn
                    sql1.CommandType = CommandType.Text
                    reader1 = sql1.ExecuteReader
                    Using reader1
                        If reader1.HasRows Then
                            dgv1.DataSource = reader1
                            dgv1.Refresh()
                        End If
                    End Using
                    sql1.Dispose()
                    reader1.Close()
                    '===============
                    cnxn.Close()
                    cnxn.Dispose()
                End If
            End Using
            sql.Dispose()
            cnxntech.Close()
            cnxntech.Dispose()

It is executing every line in the get time card data routine. Just no data appears in the grid.

[GaryMazzone]

Look at this thread:

http://www.vbforums.com/showthread.php?t=522723


I would also change that first select statement

Dim sql As New SqlClient.SqlCommand("SELECT * From EMPFILE WHERE [EMPNUM] = " & CInt(txtEmpNumber.Text))

to

Dim empNum as Integer
If Not Integer.TryParse(txtEmpNUmber.Text,empNum) Then
     MessageBox.Show("Not a valid Employee Number")
     Me.txtEmpoyeeNumber.SetFocus
     Exit Sub
End If

Dim sql As New SqlClient.SqlCommand("SELECT * From EMPFILE WHERE [EMPNUM] =  @EmpNum"
sql.Parameters.AddWithValue("@EmpNum",empNum)

You should be using parameters where ever possible in you SQL queries. Also I would be using the .Net functions and not the VB6 functions (Integer.TryParse instead of CInt)

[Pasvorto]

Perfect! Thank so much.