|
web
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
last ditch attempt to try and get this workingrunning vb.net (2003) and SQL 2005.. the story thus far is with a lot of help and a prevailing wind i have come up with the following ( CON_BOSSCONNECTION is already connected ) Dim InputString() As String Dim ReturnValue As Integer InputString = Split(InputParm, ";") ' set the query commands STR_SQLCOMMAND.CommandText = "BossData.dbo.OperatorLogon" STR_SQLCOMMAND.CommandType = CommandType.StoredProcedure STR_SQLCOMMAND.CommandTimeout = 30 With STR_SQLCOMMAND ' set the query commands .CommandText = "BossData.dbo.OperatorLogon" .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 Dim SP_Result As Integer = CInt(STR_SQLCOMMAND.ExecuteScalar()) 'read the results Select Case SP_Result Case 0 '0 = Logon failed - unknown operator name and password Case 1 ' 1 = logon was a sucscess Case 2 ' 2 = logon failed - User already logged on and is not a master Case 3 ' 3 = logon failed - User password expired Case Else ' 4 = Unable to Mark Operator as logged on End Select Catch Err As SqlClient.SqlException Dim errorMessages As String Dim Counter As Integer For Counter = 0 To Err.Errors.Count - 1 errorMessages += "Message " & Err.Errors(Counter).Message & ControlChars.NewLine Next Console.WriteLine(errorMessages) End Try End With I have checked the parameter values and ther correct . SP_Result always returns a 0 .. now i have tried in a query editior to ececute the stored proc with the same input parms and got a 1 result which is correct declare @rtn int exec bossdata.dbo.OperatorLogon 'MyName', 'MyPassword', '999', @ReturnValue = @rtn output print @rtn ( @rtn = 1 ) is there any hope at getting this to work, or should i just pack it all in and give up. I know for certin the info being parmed in should return a 1 from the Stored Proc as a 1 result updates a table which is being done, so im assuming it the way im reading the returned value ? Peter,
Have you tried setting the to direction for the ReturnValue parameter to ParameterDirection.ReturnValue? -- Show quoteHide quoteCarsten Thomsen Communities - http://community.integratedsolutions.dk --------- Voodoo Programming: Things programmers do that they know shouldn't work but they try anyway, and which sometimes actually work, such as recompiling everything. (Karl Lehenbauer) --------- "Peter Newman" <PeterNew***@discussions.microsoft.com> wrote in message news:F1B01AA5-94D9-4A7A-825B-BA4CB5D1FFCF@microsoft.com... > Im still struggling to get this executing a stored proc working .. im > running vb.net (2003) and SQL 2005.. the story thus far is with a lot of > help and a prevailing wind i have come up with the following > ( CON_BOSSCONNECTION is already connected ) > > Dim InputString() As String > Dim ReturnValue As Integer > > InputString = Split(InputParm, ";") > ' set the query commands > STR_SQLCOMMAND.CommandText = "BossData.dbo.OperatorLogon" > STR_SQLCOMMAND.CommandType = CommandType.StoredProcedure > STR_SQLCOMMAND.CommandTimeout = 30 > > > With STR_SQLCOMMAND > ' set the query commands > .CommandText = "BossData.dbo.OperatorLogon" > .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 > Dim SP_Result As Integer = > CInt(STR_SQLCOMMAND.ExecuteScalar()) > > > 'read the results > Select Case SP_Result > Case 0 > '0 = Logon failed - unknown operator name and > password > Case 1 > ' 1 = logon was a sucscess > Case 2 > ' 2 = logon failed - User already logged on and is > not a master > Case 3 > ' 3 = logon failed - User password expired > Case Else > ' 4 = Unable to Mark Operator as logged on > End Select > > Catch Err As SqlClient.SqlException > Dim errorMessages As String > Dim Counter As Integer > > For Counter = 0 To Err.Errors.Count - 1 > errorMessages += "Message " & > Err.Errors(Counter).Message & ControlChars.NewLine > Next > Console.WriteLine(errorMessages) > End Try > End With > > I have checked the parameter values and ther correct . SP_Result always > returns a 0 .. now i have tried in a query editior to ececute the stored > proc with the same input parms and got a 1 result which is correct > > declare @rtn int > exec bossdata.dbo.OperatorLogon 'MyName', 'MyPassword', '999', > @ReturnValue > = @rtn output > print @rtn ( @rtn = 1 ) > > is there any hope at getting this to work, or should i just pack it all in > and give up. I know for certin the info being parmed in should return a 1 > from the Stored Proc as a 1 result updates a table which is being done, so > im > assuming it the way im reading the returned value ? CT, i have tried that but it returns an exception error
in t-query window its set as a output and it works fine Show quoteHide quote "CT" wrote: > Peter, > > Have you tried setting the to direction for the ReturnValue parameter to > ParameterDirection.ReturnValue? > > -- > Carsten Thomsen > Communities - http://community.integratedsolutions.dk > --------- > Voodoo Programming: Things programmers do that they know shouldn't work but > they try anyway, and which sometimes actually work, such as recompiling > everything. (Karl Lehenbauer) > --------- > "Peter Newman" <PeterNew***@discussions.microsoft.com> wrote in message > news:F1B01AA5-94D9-4A7A-825B-BA4CB5D1FFCF@microsoft.com... > > Im still struggling to get this executing a stored proc working .. im > > running vb.net (2003) and SQL 2005.. the story thus far is with a lot of > > help and a prevailing wind i have come up with the following > > ( CON_BOSSCONNECTION is already connected ) > > > > Dim InputString() As String > > Dim ReturnValue As Integer > > > > InputString = Split(InputParm, ";") > > ' set the query commands > > STR_SQLCOMMAND.CommandText = "BossData.dbo.OperatorLogon" > > STR_SQLCOMMAND.CommandType = CommandType.StoredProcedure > > STR_SQLCOMMAND.CommandTimeout = 30 > > > > > > With STR_SQLCOMMAND > > ' set the query commands > > .CommandText = "BossData.dbo.OperatorLogon" > > .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 > > Dim SP_Result As Integer = > > CInt(STR_SQLCOMMAND.ExecuteScalar()) > > > > > > 'read the results > > Select Case SP_Result > > Case 0 > > '0 = Logon failed - unknown operator name and > > password > > Case 1 > > ' 1 = logon was a sucscess > > Case 2 > > ' 2 = logon failed - User already logged on and is > > not a master > > Case 3 > > ' 3 = logon failed - User password expired > > Case Else > > ' 4 = Unable to Mark Operator as logged on > > End Select > > > > Catch Err As SqlClient.SqlException > > Dim errorMessages As String > > Dim Counter As Integer > > > > For Counter = 0 To Err.Errors.Count - 1 > > errorMessages += "Message " & > > Err.Errors(Counter).Message & ControlChars.NewLine > > Next > > Console.WriteLine(errorMessages) > > End Try > > End With > > > > I have checked the parameter values and ther correct . SP_Result always > > returns a 0 .. now i have tried in a query editior to ececute the stored > > proc with the same input parms and got a 1 result which is correct > > > > declare @rtn int > > exec bossdata.dbo.OperatorLogon 'MyName', 'MyPassword', '999', > > @ReturnValue > > = @rtn output > > print @rtn ( @rtn = 1 ) > > > > is there any hope at getting this to work, or should i just pack it all in > > and give up. I know for certin the info being parmed in should return a 1 > > from the Stored Proc as a 1 result updates a table which is being done, so > > im > > assuming it the way im reading the returned value ? > > > According to the MSDN help, SqlCommand.ExecuteScalar returns "The first
column of the first row in the result set, or a null reference if the result set is empty." To get the value of an output parameter, you have to check its .Value property after it has executed the stored procedure. Tony Show quoteHide quote "Peter Newman" wrote: > CT, i have tried that but it returns an exception error > > in t-query window its set as a output and it works fine > > "CT" wrote: > > > Peter, > > > > Have you tried setting the to direction for the ReturnValue parameter to > > ParameterDirection.ReturnValue? > > > > -- > > Carsten Thomsen > > Communities - http://community.integratedsolutions.dk > > --------- > > Voodoo Programming: Things programmers do that they know shouldn't work but > > they try anyway, and which sometimes actually work, such as recompiling > > everything. (Karl Lehenbauer) > > --------- > > "Peter Newman" <PeterNew***@discussions.microsoft.com> wrote in message > > news:F1B01AA5-94D9-4A7A-825B-BA4CB5D1FFCF@microsoft.com... > > > Im still struggling to get this executing a stored proc working .. im > > > running vb.net (2003) and SQL 2005.. the story thus far is with a lot of > > > help and a prevailing wind i have come up with the following > > > ( CON_BOSSCONNECTION is already connected ) > > > > > > Dim InputString() As String > > > Dim ReturnValue As Integer > > > > > > InputString = Split(InputParm, ";") > > > ' set the query commands > > > STR_SQLCOMMAND.CommandText = "BossData.dbo.OperatorLogon" > > > STR_SQLCOMMAND.CommandType = CommandType.StoredProcedure > > > STR_SQLCOMMAND.CommandTimeout = 30 > > > > > > > > > With STR_SQLCOMMAND > > > ' set the query commands > > > .CommandText = "BossData.dbo.OperatorLogon" > > > .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 > > > Dim SP_Result As Integer = > > > CInt(STR_SQLCOMMAND.ExecuteScalar()) > > > > > > > > > 'read the results > > > Select Case SP_Result > > > Case 0 > > > '0 = Logon failed - unknown operator name and > > > password > > > Case 1 > > > ' 1 = logon was a sucscess > > > Case 2 > > > ' 2 = logon failed - User already logged on and is > > > not a master > > > Case 3 > > > ' 3 = logon failed - User password expired > > > Case Else > > > ' 4 = Unable to Mark Operator as logged on > > > End Select > > > > > > Catch Err As SqlClient.SqlException > > > Dim errorMessages As String > > > Dim Counter As Integer > > > > > > For Counter = 0 To Err.Errors.Count - 1 > > > errorMessages += "Message " & > > > Err.Errors(Counter).Message & ControlChars.NewLine > > > Next > > > Console.WriteLine(errorMessages) > > > End Try > > > End With > > > > > > I have checked the parameter values and ther correct . SP_Result always > > > returns a 0 .. now i have tried in a query editior to ececute the stored > > > proc with the same input parms and got a 1 result which is correct > > > > > > declare @rtn int > > > exec bossdata.dbo.OperatorLogon 'MyName', 'MyPassword', '999', > > > @ReturnValue > > > = @rtn output > > > print @rtn ( @rtn = 1 ) > > > > > > is there any hope at getting this to work, or should i just pack it all in > > > and give up. I know for certin the info being parmed in should return a 1 > > > from the Stored Proc as a 1 result updates a table which is being done, so > > > im > > > assuming it the way im reading the returned value ? > > > > > > Peter,
Probably are you better of in the newsgroup microsoft.public.dotnet.adonet Your problem is in my idea mostly the SQL part. And this newsgroup is not really related to SQL scripts while the one I pointed you one is that more. However there are strange things. I see nowhere in your stored procedure the parameters. @Operatorname, @operatorpassword and @PasswordLife Despite of OleDB are the parameters in SQLClient real names. In your case I would see first if this credential class would bring a solution for me. http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.credential.aspx I think it does not helps anything but you never know. Cor Show quoteHide quote "Peter Newman" <PeterNew***@discussions.microsoft.com> schreef in bericht news:F1B01AA5-94D9-4A7A-825B-BA4CB5D1FFCF@microsoft.com... > Im still struggling to get this executing a stored proc working .. im > running vb.net (2003) and SQL 2005.. the story thus far is with a lot of > help and a prevailing wind i have come up with the following > ( CON_BOSSCONNECTION is already connected ) > > Dim InputString() As String > Dim ReturnValue As Integer > > InputString = Split(InputParm, ";") > ' set the query commands > STR_SQLCOMMAND.CommandText = "BossData.dbo.OperatorLogon" > STR_SQLCOMMAND.CommandType = CommandType.StoredProcedure > STR_SQLCOMMAND.CommandTimeout = 30 > > > With STR_SQLCOMMAND > ' set the query commands > .CommandText = "BossData.dbo.OperatorLogon" > .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 > Dim SP_Result As Integer = > CInt(STR_SQLCOMMAND.ExecuteScalar()) > > > 'read the results > Select Case SP_Result > Case 0 > '0 = Logon failed - unknown operator name and > password > Case 1 > ' 1 = logon was a sucscess > Case 2 > ' 2 = logon failed - User already logged on and is > not a master > Case 3 > ' 3 = logon failed - User password expired > Case Else > ' 4 = Unable to Mark Operator as logged on > End Select > > Catch Err As SqlClient.SqlException > Dim errorMessages As String > Dim Counter As Integer > > For Counter = 0 To Err.Errors.Count - 1 > errorMessages += "Message " & > Err.Errors(Counter).Message & ControlChars.NewLine > Next > Console.WriteLine(errorMessages) > End Try > End With > > I have checked the parameter values and ther correct . SP_Result always > returns a 0 .. now i have tried in a query editior to ececute the stored > proc with the same input parms and got a 1 result which is correct > > declare @rtn int > exec bossdata.dbo.OperatorLogon 'MyName', 'MyPassword', '999', > @ReturnValue > = @rtn output > print @rtn ( @rtn = 1 ) > > is there any hope at getting this to work, or should i just pack it all in > and give up. I know for certin the info being parmed in should return a 1 > from the Stored Proc as a 1 result updates a table which is being done, so > im > assuming it the way im reading the returned value ? Hello Peter,
I don't see the guts of the sp listed so I can only assume that you have defined an output parameter called @ReturnValue. I also assume that there is no other data being returned... no recordset. Given these assumptions, you will want to call STR_SQLCOMMAND.ExecuteNonQuery() (Oh, and by the way, YIKES on your naming convention). You then assign STR_SQLCOMMAND.Parameters("@ReturnValue").Value to SP_Result. Yer interested in the parameter value.. what in the world gave you the impression that the result of .ExecuteScalar would be your out parameter? May want to consider reading the documentation next time. I'm sure it mentions that .ExecuteScalar returns the value of the first field of the first row of a recordset given that the recordset has but a single record with a single field. -Boo 
Other interesting topics
Undefined function 'InStrRev' in expression.
Array Problems - still cant get something set up right. Dll fails to register error in the update Streamreader doesn't read the line properly identity /autonumber drives me nuts Click and Double click events are not fired in listview component in VB.NET Reading XML file getting error WithEvents code in module cannot change textbox on main form where shoule store the questions and answers |
|||||||||||||||||||||||
