ADODB

28 Jun 2006 3:34 PM

Peter Newman

Im still trying to get my head around this, but its the only way i have seen
to be able to create a 'recordset' and bind text boxex to it and navigate
throw each record by the click of a button ... ok it may not be the prettist
of code but i have that bit working . i have however stumbled across another
problem ...

I make a connection to a SQL2005 database when my app loads using the
following

        CON_BOSSCONNECTION.ConnectionString =
"DSN=DSN_NAME;System;UID=USERNAME;PWD=PASSWORD"
        Try
            CON_BOSSCONNECTION.ConnectionTimeout = 120
            CON_BOSSCONNECTION.Open()
            If CON_BOSSCONNECTION.State <> 1 Then
                Throw New System.Exception("Connection failed.")
            End If
etc

this throws no errors , but in my app i want to exec a storedproc that
parms in 3 x strings and gets an INT as a return

DIm CON_BOSSCONNECTION As New Connection
    Dim STR_SQLCOMMAND As New SqlCommand

        STR_SQLCOMMAND.CommandText = "BossData.dbo.SQL2005_OperatorLogon"
        STR_SQLCOMMAND.CommandType = CommandType.StoredProcedure
        STR_SQLCOMMAND.CommandTimeout = 30

        CON_BOSSCONNECTION.ConnectionString =
"DSN=vsDSN_Name;UID=vsUserName;PWD=UserPsw"
        Try
            CON_BOSSCONNECTION.ConnectionTimeout = 120
            CON_BOSSCONNECTION.Open()
            If CON_BOSSCONNECTION.State <> 1 Then
                Throw New System.Exception("Connection failed.")
            End If

        Catch ex As Exception
            MsgBox(ex.Message)

        End Try

        With STR_SQLCOMMAND
            ' set the query commands
            .CommandText = "TESTSQL2005_PROC"
            .CommandType = CommandType.StoredProcedure
            .CommandTimeout = 30
            ' set the connection
            .Connection = CON_BOSSCONNECTION
            ' Set the 1st Parameter
            .Parameters.Add("@OperatorName", SqlDbType.VarChar, 20).Value =
InputString(0).ToString
            ' Set the 2nd Parameter
            .Parameters.Add("@OperatorPassword", SqlDbType.VarChar,
20).Value = InputString(1).ToString
            ' Set the 3rd Parameter
            .Parameters.Add("@PasswordLife", SqlDbType.VarChar, 3).Value =
InputString(2).ToString
            ' Set the returned Paramater
            .Parameters.Add("@ReturnValue", SqlDbType.Int).Direction =
ParameterDirection.Output
            Try
                STR_SQLCOMMAND.ExecuteNonQuery()
                'read the results
                Select Case STR_SQLCOMMAND.Parameters("@ReturnValue").Value
                    Case 0
                        ' '0 = Logon failed - unknown operator name and
password
                        LogonUsers = 0
                        Exit Try
                    Case 1
                        ' 1 = logon was a suscess
                        LogonUsers = 1
                        Exit Try
                    Case 2
                        ' 2 = logon failed - User already logged on and is
not a master
                        LogonUsers = 0
                        Exit Try
                    Case 3
                        ' ' 3 = logon failed - User password expired
                    Case Else
                        ' 4 = Unable to Mark Operator as logged on
                        LogonUsers = 0
                        Exit Try
                End Select

            Catch ex As Exception
                Console.WriteLine(ex.Message)
            End Try
        End With

this throws up an error. I carnt see the wood for the trees, can anybody
shed any light ?

28 Jun 2006 4:11 PM

admspam

> this throws no errors , but in my app i want to exec a storedproc that
> parms in 3 x strings and gets an INT as a return

what error are you getting?

Peter Newman wrote:
Show quoteHide quote

> Im still trying to get my head around this, but its the only way i have seen
> to be able to create a 'recordset' and bind text boxex to it and navigate
> throw each record by the click of a button ... ok it may not be the prettist
> of code but i have that bit working . i have however stumbled across another
> problem ...
>
> I make a connection to a SQL2005 database when my app loads using the
> following
>
>         CON_BOSSCONNECTION.ConnectionString =
> "DSN=DSN_NAME;System;UID=USERNAME;PWD=PASSWORD"
>         Try
>             CON_BOSSCONNECTION.ConnectionTimeout = 120
>             CON_BOSSCONNECTION.Open()
>             If CON_BOSSCONNECTION.State <> 1 Then
>                 Throw New System.Exception("Connection failed.")
>             End If
> etc
>
> this throws no errors , but in my app i want to exec a storedproc that
> parms in 3 x strings and gets an INT as a return
>
>
> DIm CON_BOSSCONNECTION As New Connection
>     Dim STR_SQLCOMMAND As New SqlCommand
>
>         STR_SQLCOMMAND.CommandText = "BossData.dbo.SQL2005_OperatorLogon"
>         STR_SQLCOMMAND.CommandType = CommandType.StoredProcedure
>         STR_SQLCOMMAND.CommandTimeout = 30
>
>         CON_BOSSCONNECTION.ConnectionString =
> "DSN=vsDSN_Name;UID=vsUserName;PWD=UserPsw"
>         Try
>             CON_BOSSCONNECTION.ConnectionTimeout = 120
>             CON_BOSSCONNECTION.Open()
>             If CON_BOSSCONNECTION.State <> 1 Then
>                 Throw New System.Exception("Connection failed.")
>             End If
>
>
>         Catch ex As Exception
>             MsgBox(ex.Message)
>
>         End Try
>
>
>         With STR_SQLCOMMAND
>             ' set the query commands
>             .CommandText = "TESTSQL2005_PROC"
>             .CommandType = CommandType.StoredProcedure
>             .CommandTimeout = 30
>             ' set the connection
>             .Connection = CON_BOSSCONNECTION
>             ' Set the 1st Parameter
>             .Parameters.Add("@OperatorName", SqlDbType.VarChar, 20).Value =
> InputString(0).ToString
>             ' Set the 2nd Parameter
>             .Parameters.Add("@OperatorPassword", SqlDbType.VarChar,
> 20).Value = InputString(1).ToString
>             ' Set the 3rd Parameter
>             .Parameters.Add("@PasswordLife", SqlDbType.VarChar, 3).Value =
> InputString(2).ToString
>             ' Set the returned Paramater
>             .Parameters.Add("@ReturnValue", SqlDbType.Int).Direction =
> ParameterDirection.Output
>             Try
>                 STR_SQLCOMMAND.ExecuteNonQuery()
>                 'read the results
>                 Select Case STR_SQLCOMMAND.Parameters("@ReturnValue").Value
>                     Case 0
>                         ' '0 = Logon failed - unknown operator name and
> password
>                         LogonUsers = 0
>                         Exit Try
>                     Case 1
>                         ' 1 = logon was a suscess
>                         LogonUsers = 1
>                         Exit Try
>                     Case 2
>                         ' 2 = logon failed - User already logged on and is
> not a master
>                         LogonUsers = 0
>                         Exit Try
>                     Case 3
>                         ' ' 3 = logon failed - User password expired
>                     Case Else
>                         ' 4 = Unable to Mark Operator as logged on
>                         LogonUsers = 0
>                         Exit Try
>                 End Select
>
>             Catch ex As Exception
>                 Console.WriteLine(ex.Message)
>             End Try
>         End With
>
>
> this throws up an error. I carnt see the wood for the trees, can anybody
> shed any light ?

28 Jun 2006 4:13 PM

admspam

Oh, and do you really mean you are using "recordsets" and ADODB, or are
you using ADO.NET and datasets? I'm not sure the answer to that
question is entirely relevant to this discussion at this point, but it
would help to understand the big picture, I think.

Peter Newman wrote:
Show quoteHide quote

> Im still trying to get my head around this, but its the only way i have seen
> to be able to create a 'recordset' and bind text boxex to it and navigate
> throw each record by the click of a button ... ok it may not be the prettist
> of code but i have that bit working . i have however stumbled across another
> problem ...
>
> I make a connection to a SQL2005 database when my app loads using the
> following
>
>         CON_BOSSCONNECTION.ConnectionString =
> "DSN=DSN_NAME;System;UID=USERNAME;PWD=PASSWORD"
>         Try
>             CON_BOSSCONNECTION.ConnectionTimeout = 120
>             CON_BOSSCONNECTION.Open()
>             If CON_BOSSCONNECTION.State <> 1 Then
>                 Throw New System.Exception("Connection failed.")
>             End If
> etc
>
> this throws no errors , but in my app i want to exec a storedproc that
> parms in 3 x strings and gets an INT as a return
>
>
> DIm CON_BOSSCONNECTION As New Connection
>     Dim STR_SQLCOMMAND As New SqlCommand
>
>         STR_SQLCOMMAND.CommandText = "BossData.dbo.SQL2005_OperatorLogon"
>         STR_SQLCOMMAND.CommandType = CommandType.StoredProcedure
>         STR_SQLCOMMAND.CommandTimeout = 30
>
>         CON_BOSSCONNECTION.ConnectionString =
> "DSN=vsDSN_Name;UID=vsUserName;PWD=UserPsw"
>         Try
>             CON_BOSSCONNECTION.ConnectionTimeout = 120
>             CON_BOSSCONNECTION.Open()
>             If CON_BOSSCONNECTION.State <> 1 Then
>                 Throw New System.Exception("Connection failed.")
>             End If
>
>
>         Catch ex As Exception
>             MsgBox(ex.Message)
>
>         End Try
>
>
>         With STR_SQLCOMMAND
>             ' set the query commands
>             .CommandText = "TESTSQL2005_PROC"
>             .CommandType = CommandType.StoredProcedure
>             .CommandTimeout = 30
>             ' set the connection
>             .Connection = CON_BOSSCONNECTION
>             ' Set the 1st Parameter
>             .Parameters.Add("@OperatorName", SqlDbType.VarChar, 20).Value =
> InputString(0).ToString
>             ' Set the 2nd Parameter
>             .Parameters.Add("@OperatorPassword", SqlDbType.VarChar,
> 20).Value = InputString(1).ToString
>             ' Set the 3rd Parameter
>             .Parameters.Add("@PasswordLife", SqlDbType.VarChar, 3).Value =
> InputString(2).ToString
>             ' Set the returned Paramater
>             .Parameters.Add("@ReturnValue", SqlDbType.Int).Direction =
> ParameterDirection.Output
>             Try
>                 STR_SQLCOMMAND.ExecuteNonQuery()
>                 'read the results
>                 Select Case STR_SQLCOMMAND.Parameters("@ReturnValue").Value
>                     Case 0
>                         ' '0 = Logon failed - unknown operator name and
> password
>                         LogonUsers = 0
>                         Exit Try
>                     Case 1
>                         ' 1 = logon was a suscess
>                         LogonUsers = 1
>                         Exit Try
>                     Case 2
>                         ' 2 = logon failed - User already logged on and is
> not a master
>                         LogonUsers = 0
>                         Exit Try
>                     Case 3
>                         ' ' 3 = logon failed - User password expired
>                     Case Else
>                         ' 4 = Unable to Mark Operator as logged on
>                         LogonUsers = 0
>                         Exit Try
>                 End Select
>
>             Catch ex As Exception
>                 Console.WriteLine(ex.Message)
>             End Try
>         End With
>
>
> this throws up an error. I carnt see the wood for the trees, can anybody
> shed any light ?

28 Jun 2006 4:33 PM

Peter Newman

hi,

sorry it was a bit vauge, it must show how confused i am ... im using
ADODB ( its all i know )

the error i get is at

> > ' set the connection
> > .Connection = CON_BOSSCONNECTION

the error is 'An Unhandled execption of type 'System.InvalidCastException'
occurred in Boss.Net.exe

Additional information: Specified cast is not valid '

Im always open to new idea, providing theres some one to hold my hand when i
get stuck. It seemed so easy in vb6 ...

Show quoteHide quote

"admspam@yahoo.com" wrote:

> Oh, and do you really mean you are using "recordsets" and ADODB, or are
> you using ADO.NET and datasets? I'm not sure the answer to that
> question is entirely relevant to this discussion at this point, but it
> would help to understand the big picture, I think.
>
>
> Peter Newman wrote:
> > Im still trying to get my head around this, but its the only way i have seen
> > to be able to create a 'recordset' and bind text boxex to it and navigate
> > throw each record by the click of a button ... ok it may not be the prettist
> > of code but i have that bit working . i have however stumbled across another
> > problem ...
> >
> > I make a connection to a SQL2005 database when my app loads using the
> > following
> >
> >         CON_BOSSCONNECTION.ConnectionString =
> > "DSN=DSN_NAME;System;UID=USERNAME;PWD=PASSWORD"
> >         Try
> >             CON_BOSSCONNECTION.ConnectionTimeout = 120
> >             CON_BOSSCONNECTION.Open()
> >             If CON_BOSSCONNECTION.State <> 1 Then
> >                 Throw New System.Exception("Connection failed.")
> >             End If
> > etc
> >
> > this throws no errors , but in my app i want to exec a storedproc that
> > parms in 3 x strings and gets an INT as a return
> >
> >
> > DIm CON_BOSSCONNECTION As New Connection
> >     Dim STR_SQLCOMMAND As New SqlCommand
> >
> >         STR_SQLCOMMAND.CommandText = "BossData.dbo.SQL2005_OperatorLogon"
> >         STR_SQLCOMMAND.CommandType = CommandType.StoredProcedure
> >         STR_SQLCOMMAND.CommandTimeout = 30
> >
> >         CON_BOSSCONNECTION.ConnectionString =
> > "DSN=vsDSN_Name;UID=vsUserName;PWD=UserPsw"
> >         Try
> >             CON_BOSSCONNECTION.ConnectionTimeout = 120
> >             CON_BOSSCONNECTION.Open()
> >             If CON_BOSSCONNECTION.State <> 1 Then
> >                 Throw New System.Exception("Connection failed.")
> >             End If
> >
> >
> >         Catch ex As Exception
> >             MsgBox(ex.Message)
> >
> >         End Try
> >
> >
> >         With STR_SQLCOMMAND
> >             ' set the query commands
> >             .CommandText = "TESTSQL2005_PROC"
> >             .CommandType = CommandType.StoredProcedure
> >             .CommandTimeout = 30
> >             ' set the connection
> >             .Connection = CON_BOSSCONNECTION
> >             ' Set the 1st Parameter
> >             .Parameters.Add("@OperatorName", SqlDbType.VarChar, 20).Value =
> > InputString(0).ToString
> >             ' Set the 2nd Parameter
> >             .Parameters.Add("@OperatorPassword", SqlDbType.VarChar,
> > 20).Value = InputString(1).ToString
> >             ' Set the 3rd Parameter
> >             .Parameters.Add("@PasswordLife", SqlDbType.VarChar, 3).Value =
> > InputString(2).ToString
> >             ' Set the returned Paramater
> >             .Parameters.Add("@ReturnValue", SqlDbType.Int).Direction =
> > ParameterDirection.Output
> >             Try
> >                 STR_SQLCOMMAND.ExecuteNonQuery()
> >                 'read the results
> >                 Select Case STR_SQLCOMMAND.Parameters("@ReturnValue").Value
> >                     Case 0
> >                         ' '0 = Logon failed - unknown operator name and
> > password
> >                         LogonUsers = 0
> >                         Exit Try
> >                     Case 1
> >                         ' 1 = logon was a suscess
> >                         LogonUsers = 1
> >                         Exit Try
> >                     Case 2
> >                         ' 2 = logon failed - User already logged on and is
> > not a master
> >                         LogonUsers = 0
> >                         Exit Try
> >                     Case 3
> >                         ' ' 3 = logon failed - User password expired
> >                     Case Else
> >                         ' 4 = Unable to Mark Operator as logged on
> >                         LogonUsers = 0
> >                         Exit Try
> >                 End Select
> >
> >             Catch ex As Exception
> >                 Console.WriteLine(ex.Message)
> >             End Try
> >         End With
> >
> >
> > this throws up an error. I carnt see the wood for the trees, can anybody
> > shed any light ?
>
>

28 Jun 2006 4:50 PM

admspam

Your front-end is written in VB.NET, right? Then you should be using
ADO.NET instead of ADODB. It'll save you about a million headaches.

In general, use datasets (the .NET equivalent of recordsets), and bind
your forms/controls to those.

If you are new to .NET, I strongly recommend the book "Programming
Microsoft Visual Basic .NET" by Francesco Balena (ISBN 0735620598). On
page 527, you will find exactly what you are looking for. I am pretty
new to .NET, and use this excellent book almost everyday.

In the meantime, check out DataBindings, BindingManagerBase, and
BindingContext.

Although this doesn't answer your question directly, I hope it will
make things easier.

Peter Newman wrote:
Show quoteHide quote

> hi,
>
> sorry it was a bit vauge, it must show how confused i am ... im using
> ADODB ( its all i know )
>
> the error i get is at
>
> > >             ' set the connection
> > >             .Connection = CON_BOSSCONNECTION
>
> the error is 'An Unhandled execption of type 'System.InvalidCastException'
> occurred in Boss.Net.exe
>
> Additional information: Specified cast is not valid '
>
> Im always open to new idea, providing theres some one to hold my hand when i
> get stuck.   It seemed so easy in vb6 ...
>
>
>
> "admspam@yahoo.com" wrote:
>
> > Oh, and do you really mean you are using "recordsets" and ADODB, or are
> > you using ADO.NET and datasets? I'm not sure the answer to that
> > question is entirely relevant to this discussion at this point, but it
> > would help to understand the big picture, I think.
> >
> >
> > Peter Newman wrote:
> > > Im still trying to get my head around this, but its the only way i have seen
> > > to be able to create a 'recordset' and bind text boxex to it and navigate
> > > throw each record by the click of a button ... ok it may not be the prettist
> > > of code but i have that bit working . i have however stumbled across another
> > > problem ...
> > >
> > > I make a connection to a SQL2005 database when my app loads using the
> > > following
> > >
> > >         CON_BOSSCONNECTION.ConnectionString =
> > > "DSN=DSN_NAME;System;UID=USERNAME;PWD=PASSWORD"
> > >         Try
> > >             CON_BOSSCONNECTION.ConnectionTimeout = 120
> > >             CON_BOSSCONNECTION.Open()
> > >             If CON_BOSSCONNECTION.State <> 1 Then
> > >                 Throw New System.Exception("Connection failed.")
> > >             End If
> > > etc
> > >
> > > this throws no errors , but in my app i want to exec a storedproc that
> > > parms in 3 x strings and gets an INT as a return
> > >
> > >
> > > DIm CON_BOSSCONNECTION As New Connection
> > >     Dim STR_SQLCOMMAND As New SqlCommand
> > >
> > >         STR_SQLCOMMAND.CommandText = "BossData.dbo.SQL2005_OperatorLogon"
> > >         STR_SQLCOMMAND.CommandType = CommandType.StoredProcedure
> > >         STR_SQLCOMMAND.CommandTimeout = 30
> > >
> > >         CON_BOSSCONNECTION.ConnectionString =
> > > "DSN=vsDSN_Name;UID=vsUserName;PWD=UserPsw"
> > >         Try
> > >             CON_BOSSCONNECTION.ConnectionTimeout = 120
> > >             CON_BOSSCONNECTION.Open()
> > >             If CON_BOSSCONNECTION.State <> 1 Then
> > >                 Throw New System.Exception("Connection failed.")
> > >             End If
> > >
> > >
> > >         Catch ex As Exception
> > >             MsgBox(ex.Message)
> > >
> > >         End Try
> > >
> > >
> > >         With STR_SQLCOMMAND
> > >             ' set the query commands
> > >             .CommandText = "TESTSQL2005_PROC"
> > >             .CommandType = CommandType.StoredProcedure
> > >             .CommandTimeout = 30
> > >             ' set the connection
> > >             .Connection = CON_BOSSCONNECTION
> > >             ' Set the 1st Parameter
> > >             .Parameters.Add("@OperatorName", SqlDbType.VarChar, 20).Value =
> > > InputString(0).ToString
> > >             ' Set the 2nd Parameter
> > >             .Parameters.Add("@OperatorPassword", SqlDbType.VarChar,
> > > 20).Value = InputString(1).ToString
> > >             ' Set the 3rd Parameter
> > >             .Parameters.Add("@PasswordLife", SqlDbType.VarChar, 3).Value =
> > > InputString(2).ToString
> > >             ' Set the returned Paramater
> > >             .Parameters.Add("@ReturnValue", SqlDbType.Int).Direction =
> > > ParameterDirection.Output
> > >             Try
> > >                 STR_SQLCOMMAND.ExecuteNonQuery()
> > >                 'read the results
> > >                 Select Case STR_SQLCOMMAND.Parameters("@ReturnValue").Value
> > >                     Case 0
> > >                         ' '0 = Logon failed - unknown operator name and
> > > password
> > >                         LogonUsers = 0
> > >                         Exit Try
> > >                     Case 1
> > >                         ' 1 = logon was a suscess
> > >                         LogonUsers = 1
> > >                         Exit Try
> > >                     Case 2
> > >                         ' 2 = logon failed - User already logged on and is
> > > not a master
> > >                         LogonUsers = 0
> > >                         Exit Try
> > >                     Case 3
> > >                         ' ' 3 = logon failed - User password expired
> > >                     Case Else
> > >                         ' 4 = Unable to Mark Operator as logged on
> > >                         LogonUsers = 0
> > >                         Exit Try
> > >                 End Select
> > >
> > >             Catch ex As Exception
> > >                 Console.WriteLine(ex.Message)
> > >             End Try
> > >         End With
> > >
> > >
> > > this throws up an error. I carnt see the wood for the trees, can anybody
> > > shed any light ?
> >
> >

28 Jun 2006 4:57 PM

admspam

Oh, and see this nice tutorial:

http://samples.gotdotnet.com/quickstart/winforms/

(Thanks to Cor L. for the link, via another thread.)

admspam@yahoo.com wrote:
Show quoteHide quote

> Your front-end is written in VB.NET, right? Then you should be using
> ADO.NET instead of ADODB. It'll save you about a million headaches.
>
> In general, use datasets (the .NET equivalent of recordsets), and bind
> your forms/controls to those.
>
> If you are new to .NET, I strongly recommend the book "Programming
> Microsoft Visual Basic .NET" by Francesco Balena (ISBN 0735620598). On
> page 527, you will find exactly what you are looking for. I am pretty
> new to .NET, and use this excellent book almost everyday.
>
> In the meantime, check out DataBindings, BindingManagerBase, and
> BindingContext.
>
> Although this doesn't answer your question directly, I hope it will
> make things easier.
>
>
> Peter Newman wrote:
> > hi,
> >
> > sorry it was a bit vauge, it must show how confused i am ... im using
> > ADODB ( its all i know )
> >
> > the error i get is at
> >
> > > >             ' set the connection
> > > >             .Connection = CON_BOSSCONNECTION
> >
> > the error is 'An Unhandled execption of type 'System.InvalidCastException'
> > occurred in Boss.Net.exe
> >
> > Additional information: Specified cast is not valid '
> >
> > Im always open to new idea, providing theres some one to hold my hand when i
> > get stuck.   It seemed so easy in vb6 ...
> >
> >
> >
> > "admspam@yahoo.com" wrote:
> >
> > > Oh, and do you really mean you are using "recordsets" and ADODB, or are
> > > you using ADO.NET and datasets? I'm not sure the answer to that
> > > question is entirely relevant to this discussion at this point, but it
> > > would help to understand the big picture, I think.
> > >
> > >
> > > Peter Newman wrote:
> > > > Im still trying to get my head around this, but its the only way i have seen
> > > > to be able to create a 'recordset' and bind text boxex to it and navigate
> > > > throw each record by the click of a button ... ok it may not be the prettist
> > > > of code but i have that bit working . i have however stumbled across another
> > > > problem ...
> > > >
> > > > I make a connection to a SQL2005 database when my app loads using the
> > > > following
> > > >
> > > >         CON_BOSSCONNECTION.ConnectionString =
> > > > "DSN=DSN_NAME;System;UID=USERNAME;PWD=PASSWORD"
> > > >         Try
> > > >             CON_BOSSCONNECTION.ConnectionTimeout = 120
> > > >             CON_BOSSCONNECTION.Open()
> > > >             If CON_BOSSCONNECTION.State <> 1 Then
> > > >                 Throw New System.Exception("Connection failed.")
> > > >             End If
> > > > etc
> > > >
> > > > this throws no errors , but in my app i want to exec a storedproc that
> > > > parms in 3 x strings and gets an INT as a return
> > > >
> > > >
> > > > DIm CON_BOSSCONNECTION As New Connection
> > > >     Dim STR_SQLCOMMAND As New SqlCommand
> > > >
> > > >         STR_SQLCOMMAND.CommandText = "BossData.dbo.SQL2005_OperatorLogon"
> > > >         STR_SQLCOMMAND.CommandType = CommandType.StoredProcedure
> > > >         STR_SQLCOMMAND.CommandTimeout = 30
> > > >
> > > >         CON_BOSSCONNECTION.ConnectionString =
> > > > "DSN=vsDSN_Name;UID=vsUserName;PWD=UserPsw"
> > > >         Try
> > > >             CON_BOSSCONNECTION.ConnectionTimeout = 120
> > > >             CON_BOSSCONNECTION.Open()
> > > >             If CON_BOSSCONNECTION.State <> 1 Then
> > > >                 Throw New System.Exception("Connection failed.")
> > > >             End If
> > > >
> > > >
> > > >         Catch ex As Exception
> > > >             MsgBox(ex.Message)
> > > >
> > > >         End Try
> > > >
> > > >
> > > >         With STR_SQLCOMMAND
> > > >             ' set the query commands
> > > >             .CommandText = "TESTSQL2005_PROC"
> > > >             .CommandType = CommandType.StoredProcedure
> > > >             .CommandTimeout = 30
> > > >             ' set the connection
> > > >             .Connection = CON_BOSSCONNECTION
> > > >             ' Set the 1st Parameter
> > > >             .Parameters.Add("@OperatorName", SqlDbType.VarChar, 20).Value =
> > > > InputString(0).ToString
> > > >             ' Set the 2nd Parameter
> > > >             .Parameters.Add("@OperatorPassword", SqlDbType.VarChar,
> > > > 20).Value = InputString(1).ToString
> > > >             ' Set the 3rd Parameter
> > > >             .Parameters.Add("@PasswordLife", SqlDbType.VarChar, 3).Value =
> > > > InputString(2).ToString
> > > >             ' Set the returned Paramater
> > > >             .Parameters.Add("@ReturnValue", SqlDbType.Int).Direction =
> > > > ParameterDirection.Output
> > > >             Try
> > > >                 STR_SQLCOMMAND.ExecuteNonQuery()
> > > >                 'read the results
> > > >                 Select Case STR_SQLCOMMAND.Parameters("@ReturnValue").Value
> > > >                     Case 0
> > > >                         ' '0 = Logon failed - unknown operator name and
> > > > password
> > > >                         LogonUsers = 0
> > > >                         Exit Try
> > > >                     Case 1
> > > >                         ' 1 = logon was a suscess
> > > >                         LogonUsers = 1
> > > >                         Exit Try
> > > >                     Case 2
> > > >                         ' 2 = logon failed - User already logged on and is
> > > > not a master
> > > >                         LogonUsers = 0
> > > >                         Exit Try
> > > >                     Case 3
> > > >                         ' ' 3 = logon failed - User password expired
> > > >                     Case Else
> > > >                         ' 4 = Unable to Mark Operator as logged on
> > > >                         LogonUsers = 0
> > > >                         Exit Try
> > > >                 End Select
> > > >
> > > >             Catch ex As Exception
> > > >                 Console.WriteLine(ex.Message)
> > > >             End Try
> > > >         End With
> > > >
> > > >
> > > > this throws up an error. I carnt see the wood for the trees, can anybody
> > > > shed any light ?
> > >
> > >

29 Jun 2006 9:50 PM

CAnderson [MVP]

If I may, there's a couple of ADO.NET tutorials that work pretty well of
going through some of the basics (and I means basics). Be sure to get the
right ones (VB6 & ADO vs VB.NET and ADO.NET)

I appologize for their crudeness and lack of screen shots (even though they
are referenced in the text) but I'm in the process of migrating them to a new
location.

-ca

Show quoteHide quote

"admspam@yahoo.com" wrote:

> Oh, and see this nice tutorial:
>
> http://samples.gotdotnet.com/quickstart/winforms/
>
> (Thanks to Cor L. for the link, via another thread.)
>
> admspam@yahoo.com wrote:
> > Your front-end is written in VB.NET, right? Then you should be using
> > ADO.NET instead of ADODB. It'll save you about a million headaches.
> >
> > In general, use datasets (the .NET equivalent of recordsets), and bind
> > your forms/controls to those.
> >
> > If you are new to .NET, I strongly recommend the book "Programming
> > Microsoft Visual Basic .NET" by Francesco Balena (ISBN 0735620598). On
> > page 527, you will find exactly what you are looking for. I am pretty
> > new to .NET, and use this excellent book almost everyday.
> >
> > In the meantime, check out DataBindings, BindingManagerBase, and
> > BindingContext.
> >
> > Although this doesn't answer your question directly, I hope it will
> > make things easier.
> >
> >
> > Peter Newman wrote:
> > > hi,
> > >
> > > sorry it was a bit vauge, it must show how confused i am ... im using
> > > ADODB ( its all i know )
> > >
> > > the error i get is at
> > >
> > > > >             ' set the connection
> > > > >             .Connection = CON_BOSSCONNECTION
> > >
> > > the error is 'An Unhandled execption of type 'System.InvalidCastException'
> > > occurred in Boss.Net.exe
> > >
> > > Additional information: Specified cast is not valid '
> > >
> > > Im always open to new idea, providing theres some one to hold my hand when i
> > > get stuck.   It seemed so easy in vb6 ...
> > >
> > >
> > >
> > > "admspam@yahoo.com" wrote:
> > >
> > > > Oh, and do you really mean you are using "recordsets" and ADODB, or are
> > > > you using ADO.NET and datasets? I'm not sure the answer to that
> > > > question is entirely relevant to this discussion at this point, but it
> > > > would help to understand the big picture, I think.
> > > >
> > > >
> > > > Peter Newman wrote:
> > > > > Im still trying to get my head around this, but its the only way i have seen
> > > > > to be able to create a 'recordset' and bind text boxex to it and navigate
> > > > > throw each record by the click of a button ... ok it may not be the prettist
> > > > > of code but i have that bit working . i have however stumbled across another
> > > > > problem ...
> > > > >
> > > > > I make a connection to a SQL2005 database when my app loads using the
> > > > > following
> > > > >
> > > > >         CON_BOSSCONNECTION.ConnectionString =
> > > > > "DSN=DSN_NAME;System;UID=USERNAME;PWD=PASSWORD"
> > > > >         Try
> > > > >             CON_BOSSCONNECTION.ConnectionTimeout = 120
> > > > >             CON_BOSSCONNECTION.Open()
> > > > >             If CON_BOSSCONNECTION.State <> 1 Then
> > > > >                 Throw New System.Exception("Connection failed.")
> > > > >             End If
> > > > > etc
> > > > >
> > > > > this throws no errors , but in my app i want to exec a storedproc that
> > > > > parms in 3 x strings and gets an INT as a return
> > > > >
> > > > >
> > > > > DIm CON_BOSSCONNECTION As New Connection
> > > > >     Dim STR_SQLCOMMAND As New SqlCommand
> > > > >
> > > > >         STR_SQLCOMMAND.CommandText = "BossData.dbo.SQL2005_OperatorLogon"
> > > > >         STR_SQLCOMMAND.CommandType = CommandType.StoredProcedure
> > > > >         STR_SQLCOMMAND.CommandTimeout = 30
> > > > >
> > > > >         CON_BOSSCONNECTION.ConnectionString =
> > > > > "DSN=vsDSN_Name;UID=vsUserName;PWD=UserPsw"
> > > > >         Try
> > > > >             CON_BOSSCONNECTION.ConnectionTimeout = 120
> > > > >             CON_BOSSCONNECTION.Open()
> > > > >             If CON_BOSSCONNECTION.State <> 1 Then
> > > > >                 Throw New System.Exception("Connection failed.")
> > > > >             End If
> > > > >
> > > > >
> > > > >         Catch ex As Exception
> > > > >             MsgBox(ex.Message)
> > > > >
> > > > >         End Try
> > > > >
> > > > >
> > > > >         With STR_SQLCOMMAND
> > > > >             ' set the query commands
> > > > >             .CommandText = "TESTSQL2005_PROC"
> > > > >             .CommandType = CommandType.StoredProcedure
> > > > >             .CommandTimeout = 30
> > > > >             ' set the connection
> > > > >             .Connection = CON_BOSSCONNECTION
> > > > >             ' Set the 1st Parameter
> > > > >             .Parameters.Add("@OperatorName", SqlDbType.VarChar, 20).Value =
> > > > > InputString(0).ToString
> > > > >             ' Set the 2nd Parameter
> > > > >             .Parameters.Add("@OperatorPassword", SqlDbType.VarChar,
> > > > > 20).Value = InputString(1).ToString
> > > > >             ' Set the 3rd Parameter
> > > > >             .Parameters.Add("@PasswordLife", SqlDbType.VarChar, 3).Value =
> > > > > InputString(2).ToString
> > > > >             ' Set the returned Paramater
> > > > >             .Parameters.Add("@ReturnValue", SqlDbType.Int).Direction =
> > > > > ParameterDirection.Output
> > > > >             Try
> > > > >                 STR_SQLCOMMAND.ExecuteNonQuery()
> > > > >                 'read the results
> > > > >                 Select Case STR_SQLCOMMAND.Parameters("@ReturnValue").Value
> > > > >                     Case 0
> > > > >                         ' '0 = Logon failed - unknown operator name and
> > > > > password
> > > > >                         LogonUsers = 0
> > > > >                         Exit Try
> > > > >                     Case 1
> > > > >                         ' 1 = logon was a suscess
> > > > >                         LogonUsers = 1
> > > > >                         Exit Try
> > > > >                     Case 2
> > > > >                         ' 2 = logon failed - User already logged on and is
> > > > > not a master
> > > > >                         LogonUsers = 0
> > > > >                         Exit Try
> > > > >                     Case 3
> > > > >                         ' ' 3 = logon failed - User password expired
> > > > >                     Case Else
> > > > >                         ' 4 = Unable to Mark Operator as logged on
> > > > >                         LogonUsers = 0
> > > > >                         Exit Try
> > > > >                 End Select
> > > > >
> > > > >             Catch ex As Exception
> > > > >                 Console.WriteLine(ex.Message)
> > > > >             End Try
> > > > >         End With
> > > > >
> > > > >
> > > > > this throws up an error. I carnt see the wood for the trees, can anybody
> > > > > shed any light ?
> > > >
> > > >
>
>

28 Jun 2006 4:58 PM

Peter Newman

thank you , i will have a good look at it

Show quoteHide quote

"admspam@yahoo.com" wrote:

> Your front-end is written in VB.NET, right? Then you should be using
> ADO.NET instead of ADODB. It'll save you about a million headaches.
>
> In general, use datasets (the .NET equivalent of recordsets), and bind
> your forms/controls to those.
>
> If you are new to .NET, I strongly recommend the book "Programming
> Microsoft Visual Basic .NET" by Francesco Balena (ISBN 0735620598). On
> page 527, you will find exactly what you are looking for. I am pretty
> new to .NET, and use this excellent book almost everyday.
>
> In the meantime, check out DataBindings, BindingManagerBase, and
> BindingContext.
>
> Although this doesn't answer your question directly, I hope it will
> make things easier.
>
>
> Peter Newman wrote:
> > hi,
> >
> > sorry it was a bit vauge, it must show how confused i am ... im using
> > ADODB ( its all i know )
> >
> > the error i get is at
> >
> > > >             ' set the connection
> > > >             .Connection = CON_BOSSCONNECTION
> >
> > the error is 'An Unhandled execption of type 'System.InvalidCastException'
> > occurred in Boss.Net.exe
> >
> > Additional information: Specified cast is not valid '
> >
> > Im always open to new idea, providing theres some one to hold my hand when i
> > get stuck.   It seemed so easy in vb6 ...
> >
> >
> >
> > "admspam@yahoo.com" wrote:
> >
> > > Oh, and do you really mean you are using "recordsets" and ADODB, or are
> > > you using ADO.NET and datasets? I'm not sure the answer to that
> > > question is entirely relevant to this discussion at this point, but it
> > > would help to understand the big picture, I think.
> > >
> > >
> > > Peter Newman wrote:
> > > > Im still trying to get my head around this, but its the only way i have seen
> > > > to be able to create a 'recordset' and bind text boxex to it and navigate
> > > > throw each record by the click of a button ... ok it may not be the prettist
> > > > of code but i have that bit working . i have however stumbled across another
> > > > problem ...
> > > >
> > > > I make a connection to a SQL2005 database when my app loads using the
> > > > following
> > > >
> > > >         CON_BOSSCONNECTION.ConnectionString =
> > > > "DSN=DSN_NAME;System;UID=USERNAME;PWD=PASSWORD"
> > > >         Try
> > > >             CON_BOSSCONNECTION.ConnectionTimeout = 120
> > > >             CON_BOSSCONNECTION.Open()
> > > >             If CON_BOSSCONNECTION.State <> 1 Then
> > > >                 Throw New System.Exception("Connection failed.")
> > > >             End If
> > > > etc
> > > >
> > > > this throws no errors , but in my app i want to exec a storedproc that
> > > > parms in 3 x strings and gets an INT as a return
> > > >
> > > >
> > > > DIm CON_BOSSCONNECTION As New Connection
> > > >     Dim STR_SQLCOMMAND As New SqlCommand
> > > >
> > > >         STR_SQLCOMMAND.CommandText = "BossData.dbo.SQL2005_OperatorLogon"
> > > >         STR_SQLCOMMAND.CommandType = CommandType.StoredProcedure
> > > >         STR_SQLCOMMAND.CommandTimeout = 30
> > > >
> > > >         CON_BOSSCONNECTION.ConnectionString =
> > > > "DSN=vsDSN_Name;UID=vsUserName;PWD=UserPsw"
> > > >         Try
> > > >             CON_BOSSCONNECTION.ConnectionTimeout = 120
> > > >             CON_BOSSCONNECTION.Open()
> > > >             If CON_BOSSCONNECTION.State <> 1 Then
> > > >                 Throw New System.Exception("Connection failed.")
> > > >             End If
> > > >
> > > >
> > > >         Catch ex As Exception
> > > >             MsgBox(ex.Message)
> > > >
> > > >         End Try
> > > >
> > > >
> > > >         With STR_SQLCOMMAND
> > > >             ' set the query commands
> > > >             .CommandText = "TESTSQL2005_PROC"
> > > >             .CommandType = CommandType.StoredProcedure
> > > >             .CommandTimeout = 30
> > > >             ' set the connection
> > > >             .Connection = CON_BOSSCONNECTION
> > > >             ' Set the 1st Parameter
> > > >             .Parameters.Add("@OperatorName", SqlDbType.VarChar, 20).Value =
> > > > InputString(0).ToString
> > > >             ' Set the 2nd Parameter
> > > >             .Parameters.Add("@OperatorPassword", SqlDbType.VarChar,
> > > > 20).Value = InputString(1).ToString
> > > >             ' Set the 3rd Parameter
> > > >             .Parameters.Add("@PasswordLife", SqlDbType.VarChar, 3).Value =
> > > > InputString(2).ToString
> > > >             ' Set the returned Paramater
> > > >             .Parameters.Add("@ReturnValue", SqlDbType.Int).Direction =
> > > > ParameterDirection.Output
> > > >             Try
> > > >                 STR_SQLCOMMAND.ExecuteNonQuery()
> > > >                 'read the results
> > > >                 Select Case STR_SQLCOMMAND.Parameters("@ReturnValue").Value
> > > >                     Case 0
> > > >                         ' '0 = Logon failed - unknown operator name and
> > > > password
> > > >                         LogonUsers = 0
> > > >                         Exit Try
> > > >                     Case 1
> > > >                         ' 1 = logon was a suscess
> > > >                         LogonUsers = 1
> > > >                         Exit Try
> > > >                     Case 2
> > > >                         ' 2 = logon failed - User already logged on and is
> > > > not a master
> > > >                         LogonUsers = 0
> > > >                         Exit Try
> > > >                     Case 3
> > > >                         ' ' 3 = logon failed - User password expired
> > > >                     Case Else
> > > >                         ' 4 = Unable to Mark Operator as logged on
> > > >                         LogonUsers = 0
> > > >                         Exit Try
> > > >                 End Select
> > > >
> > > >             Catch ex As Exception
> > > >                 Console.WriteLine(ex.Message)
> > > >             End Try
> > > >         End With
> > > >
> > > >
> > > > this throws up an error. I carnt see the wood for the trees, can anybody
> > > > shed any light ?
> > >
> > >
>
>