G
Guest
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 ?
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 ?