I have a connection (SqlConnection1) established through the GUI. Here's the
code:
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
'Create and initialise the command object
Dim cmd As New SqlCommand("GetAddress", SqlConnection1)
'State that the type of this command is a stored procedure
cmd.CommandType = CommandType.StoredProcedure
'Create the input paramater
cmd.Parameters.Add("@.BizName", SqlDbType.VarChar, 50)
cmd.Parameters("@.BizName").Direction = ParameterDirection.Input
cmd.Parameters("@.BizName").Value = TextBox1.Text
'Create the output paramater
cmd.Parameters.Add("@.BizAddress", SqlDbType.VarChar, 50)
cmd.Parameters("@.BizAddress").Direction = ParameterDirection.Output
'Enusre the connection is open
If (cmd.Connection.State <> ConnectionState.Open) Then
cmd.Connection.Open()
End If
'Execute the command object
cmd.ExecuteNonQuery()
'Assign the returned value of the output paramater
TextBox2.Text = cmd.Parameters("@.BizAddress").Value
'Close the connection
cmd.Connection.Close()
End Sub
This code allows a textbox (textbox1) to fill an input paramater and
displays the contents of the returned value from the output paramater in a
textbox (textbox2). It works fine with s imple select statement. But with a
stored procedure i get this error:
Cast from type 'DBNull' to type 'String' is not valid.
Description: An unhandled exception occurred during the execution of the
current web request. Please review the stack trace for more information abou
t
the error and where it originated in the code.
Exception Details: System.InvalidCastException: Cast from type 'DBNull' to
type 'String' is not valid.
Source Error:
Line 63:
Line 64: 'Assign the returned value of the output paramater
Line 65: TextBox2.Text = cmd.Parameters("@.BizAddress").Value
The code for the stored procedure:
CREATE PROCEDURE GetAddress
@.BizName varchar,
@.BizAddress varchar output
AS
SELECT @.BizAddress = Address FROM nabilTable WHERE Name = @.BizName
GO
Where did i go wrong?
Thanks for any insights.
NabYour Stored Procedure is returning a NULL value in the @.BizAddress output
parameter. You need to assign the value to an Object and check it for
DBNull.value before converting to string:
Dim o As Object
o = cmd.Parameters("@.BizAddress").Value
If (o Is Nothing OrElse o Is DBNull.Value) Then
TextBox2.Text = ""
Else
TextBox2.Text = Convert.ToString(o)
EndIf
"Nab" <Nab@.discussions.microsoft.com> wrote in message
news:96300218-F090-44B3-A5E9-F30FCE80D711@.microsoft.com...
>I have a connection (SqlConnection1) established through the GUI. Here's
>the
> code:
> Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
> System.EventArgs) Handles Button1.Click
> 'Create and initialise the command object
> Dim cmd As New SqlCommand("GetAddress", SqlConnection1)
> 'State that the type of this command is a stored procedure
> cmd.CommandType = CommandType.StoredProcedure
> 'Create the input paramater
> cmd.Parameters.Add("@.BizName", SqlDbType.VarChar, 50)
> cmd.Parameters("@.BizName").Direction = ParameterDirection.Input
> cmd.Parameters("@.BizName").Value = TextBox1.Text
> 'Create the output paramater
> cmd.Parameters.Add("@.BizAddress", SqlDbType.VarChar, 50)
> cmd.Parameters("@.BizAddress").Direction = ParameterDirection.Output
> 'Enusre the connection is open
> If (cmd.Connection.State <> ConnectionState.Open) Then
> cmd.Connection.Open()
> End If
> 'Execute the command object
> cmd.ExecuteNonQuery()
> 'Assign the returned value of the output paramater
> TextBox2.Text = cmd.Parameters("@.BizAddress").Value
> 'Close the connection
> cmd.Connection.Close()
> End Sub
> This code allows a textbox (textbox1) to fill an input paramater and
> displays the contents of the returned value from the output paramater in a
> textbox (textbox2). It works fine with s imple select statement. But with
> a
> stored procedure i get this error:
> Cast from type 'DBNull' to type 'String' is not valid.
> Description: An unhandled exception occurred during the execution of the
> current web request. Please review the stack trace for more information
> about
> the error and where it originated in the code.
> Exception Details: System.InvalidCastException: Cast from type 'DBNull' to
> type 'String' is not valid.
> Source Error:
>
> Line 63:
> Line 64: 'Assign the returned value of the output paramater
> Line 65: TextBox2.Text = cmd.Parameters("@.BizAddress").Value
>
> The code for the stored procedure:
> CREATE PROCEDURE GetAddress
> @.BizName varchar,
> @.BizAddress varchar output
> AS
> SELECT @.BizAddress = Address FROM nabilTable WHERE Name = @.BizName
> GO
> Where did i go wrong?
> Thanks for any insights.
> Nab
>
>|||A NULL @.BizAddress value will be returned when no data is found and this
cannot be converted to a .Net string data type. You can check for NULL
using DbNull.Value:
If cmd.Parameters("@.BizAddress").Value Is DBNull.Value Then
MessageBox.Show("BizName not found")
Else
TextBox2.Text = cmd.Parameters("@.BizAddress").Value
End If
Also, you need specify varchar(50) in your stored procedure parameter
declaration. The default length is 1.
CREATE PROCEDURE GetAddress
@.BizName varchar(50),
@.BizAddress varchar (50) output
AS
SELECT @.BizAddress = Address FROM nabilTable WHERE Name = @.BizName
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Nab" <Nab@.discussions.microsoft.com> wrote in message
news:96300218-F090-44B3-A5E9-F30FCE80D711@.microsoft.com...
>I have a connection (SqlConnection1) established through the GUI. Here's
>the
> code:
> Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
> System.EventArgs) Handles Button1.Click
> 'Create and initialise the command object
> Dim cmd As New SqlCommand("GetAddress", SqlConnection1)
> 'State that the type of this command is a stored procedure
> cmd.CommandType = CommandType.StoredProcedure
> 'Create the input paramater
> cmd.Parameters.Add("@.BizName", SqlDbType.VarChar, 50)
> cmd.Parameters("@.BizName").Direction = ParameterDirection.Input
> cmd.Parameters("@.BizName").Value = TextBox1.Text
> 'Create the output paramater
> cmd.Parameters.Add("@.BizAddress", SqlDbType.VarChar, 50)
> cmd.Parameters("@.BizAddress").Direction = ParameterDirection.Output
> 'Enusre the connection is open
> If (cmd.Connection.State <> ConnectionState.Open) Then
> cmd.Connection.Open()
> End If
> 'Execute the command object
> cmd.ExecuteNonQuery()
> 'Assign the returned value of the output paramater
> TextBox2.Text = cmd.Parameters("@.BizAddress").Value
> 'Close the connection
> cmd.Connection.Close()
> End Sub
> This code allows a textbox (textbox1) to fill an input paramater and
> displays the contents of the returned value from the output paramater in a
> textbox (textbox2). It works fine with s imple select statement. But with
> a
> stored procedure i get this error:
> Cast from type 'DBNull' to type 'String' is not valid.
> Description: An unhandled exception occurred during the execution of the
> current web request. Please review the stack trace for more information
> about
> the error and where it originated in the code.
> Exception Details: System.InvalidCastException: Cast from type 'DBNull' to
> type 'String' is not valid.
> Source Error:
>
> Line 63:
> Line 64: 'Assign the returned value of the output paramater
> Line 65: TextBox2.Text = cmd.Parameters("@.BizAddress").Value
>
> The code for the stored procedure:
> CREATE PROCEDURE GetAddress
> @.BizName varchar,
> @.BizAddress varchar output
> AS
> SELECT @.BizAddress = Address FROM nabilTable WHERE Name = @.BizName
> GO
> Where did i go wrong?
> Thanks for any insights.
> Nab
>
>|||Nab wrote:
> I have a connection (SqlConnection1) established through the GUI.
> Here's the code:
>
You really should post these client-side questions to a more appropriate
newsgroup. Here are some suggestions:
microsoft.public.dotnet.languages.vb.data
microsoft.public.dotnet.framework.adonet
More below:
> Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
> System.EventArgs) Handles Button1.Click
> 'Create and initialise the command object
> Dim cmd As New SqlCommand("GetAddress", SqlConnection1)
> 'State that the type of this command is a stored procedure
> cmd.CommandType = CommandType.StoredProcedure
> 'Create the input paramater
> cmd.Parameters.Add("@.BizName", SqlDbType.VarChar, 50)
> cmd.Parameters("@.BizName").Direction = ParameterDirection.Input
> cmd.Parameters("@.BizName").Value = TextBox1.Text
> 'Create the output paramater
> cmd.Parameters.Add("@.BizAddress", SqlDbType.VarChar, 50)
> cmd.Parameters("@.BizAddress").Direction =
> ParameterDirection.Output
>
<snip>
> 'Execute the command object
> cmd.ExecuteNonQuery()
> 'Assign the returned value of the output paramater
> TextBox2.Text = cmd.Parameters("@.BizAddress").Value
>
<snip>
> Exception Details: System.InvalidCastException: Cast from type
> 'DBNull' to type 'String' is not valid.
>
<snip>
> The code for the stored procedure:
> CREATE PROCEDURE GetAddress
> @.BizName varchar,
> @.BizAddress varchar output
Always, always, ALWAYS set the length of your parameters:
@.BizName varchar(50),
@.BizAddress varchar(50) output
Do not depend on the default values,
> AS
> SELECT @.BizAddress = Address FROM nabilTable WHERE Name = @.BizName
> GO
>
From online help:
If the ParameterDirection is output, and execution of the associated
SqlCommand does not return a value, the SqlParameter contains a null value.
So I would guess that the value of @.BizName is not getting set. Use SQL
Profiler to verify this.
Bob Barrows
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||Thanks Dan. Stating the size in the stored procedure did the trick. Cheers.
Nab
"Dan Guzman" wrote:
> A NULL @.BizAddress value will be returned when no data is found and this
> cannot be converted to a .Net string data type. You can check for NULL
> using DbNull.Value:
> If cmd.Parameters("@.BizAddress").Value Is DBNull.Value Then
> MessageBox.Show("BizName not found")
> Else
> TextBox2.Text = cmd.Parameters("@.BizAddress").Value
> End If
> Also, you need specify varchar(50) in your stored procedure parameter
> declaration. The default length is 1.
> CREATE PROCEDURE GetAddress
> @.BizName varchar(50),
> @.BizAddress varchar (50) output
> AS
> SELECT @.BizAddress = Address FROM nabilTable WHERE Name = @.BizName
> GO
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Nab" <Nab@.discussions.microsoft.com> wrote in message
> news:96300218-F090-44B3-A5E9-F30FCE80D711@.microsoft.com...
>
>
s, scans, etc., but has the text to explain it], and