Need help setting up cast...

  • Thread starter Thread starter JJ297
  • Start date Start date
J

JJ297

Here's my stored procedure:

Create procedure Test

@Button varchar(30),
@mod char (10),
@FromDate datetime,
@ToDate datetime


AS
if @button='Button10'
begin
select * from spslist
where mod=@mod and pmtdate between @fromdate and @ToDate and PMTAMT <=
53000.01
order by pmtamt
end

if @button='Button20'
begin
select * from spslist
where mod=@mod and pmtdate between @fromdate and @ToDate and pmtamt
between 49999.99 and 53000.01
order by pmtamt
end

Here's the code behind page. How do I cast @button? Tried this below
but it's not working.

Dim conn As New
Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("SPSConnectionString").ConnectionString)

Dim cmd As New Data.SqlClient.SqlCommand
Dim cmdSampling As New Data.SqlClient.SqlCommand

With cmdSampling
.CommandType = Data.CommandType.StoredProcedure

.CommandText = "Test"
.Connection = conn

.Parameters.AddWithValue("@mod",
DropDownList1.SelectedValue)
.Parameters.AddWithValue("@Button", CType(@button,
varchar(30))
.Parameters.AddWithValue("@FromDate", FromDte.Text)
.Parameters.AddWithValue("@ToDate", ToDTE.Text)

End With
 
JJ297 said:
Here's my stored procedure:

Create procedure Test

@Button varchar(30),
@mod char (10),
@FromDate datetime,
@ToDate datetime


AS
if @button='Button10'
begin
select * from spslist
where mod=@mod and pmtdate between @fromdate and @ToDate and PMTAMT <=
53000.01
order by pmtamt
end

if @button='Button20'
begin
select * from spslist
where mod=@mod and pmtdate between @fromdate and @ToDate and pmtamt
between 49999.99 and 53000.01
order by pmtamt
end

Here's the code behind page. How do I cast @button? Tried this below
but it's not working.

Dim conn As New
Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("SPSConnectionString").ConnectionString)

Dim cmd As New Data.SqlClient.SqlCommand
Dim cmdSampling As New Data.SqlClient.SqlCommand

With cmdSampling
.CommandType = Data.CommandType.StoredProcedure

.CommandText = "Test"
.Connection = conn

.Parameters.AddWithValue("@mod",
DropDownList1.SelectedValue)
.Parameters.AddWithValue("@Button", CType(@button,
varchar(30))
.Parameters.AddWithValue("@FromDate", FromDte.Text)
.Parameters.AddWithValue("@ToDate", ToDTE.Text)

End With
Click to expand...

Since the type of parameter is varchar the value passed should be a string.
Your code does not indicate where you get the value from. If you are
checking to see if a particular button was pressed then in the button
clicked event get the name of the button from code and pass that as value.

Hope this helps
Lloyd Sheen
 
Since the type of parameter is varchar the value passed should be a string..
Your code does not indicate where you get the value from.  If you are
checking to see if a particular button was pressed then in the button
clicked event get the name of the button from code and pass that as value.

Hope this helps
Lloyd Sheen- Hide quoted text -

- Show quoted text -
Click to expand...

Yes I want to see which button is clicked, Sorry I left out some of
the code.

Tried this but it's still not working

.Parameters.AddWithValue("@Button" Button10)

Code below

Protected Sub Button10_Click(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Button10.Click
Session("Mod") = DropDownList1.SelectedValue
Session("Fromdate") = FromDte.Text 'used to grab info from the
two text boxes on the aspx page
Session("toDate") = ToDTE.Text


Dim conn As New
Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("SPSConnectionString").ConnectionString)

Dim cmd As New Data.SqlClient.SqlCommand
Dim cmdSampling As New Data.SqlClient.SqlCommand

With cmdSampling
.CommandType = Data.CommandType.StoredProcedure

.CommandText = "GetSampling"
.Connection = conn

.Parameters.AddWithValue("@mod",
DropDownList1.SelectedValue)
.Parameters.AddWithValue("@Button" Button10)
.Parameters.AddWithValue("@FromDate", FromDte.Text)
.Parameters.AddWithValue("@ToDate", ToDTE.Text)


Response.Redirect("SamplingReport.aspx")
End With
End Sub
 
Since the type of parameter is varchar the value passed should be a
string.
Your code does not indicate where you get the value from. If you are
checking to see if a particular button was pressed then in the button
clicked event get the name of the button from code and pass that as value.

Hope this helps
Lloyd Sheen- Hide quoted text -

- Show quoted text -
Click to expand...

Yes I want to see which button is clicked, Sorry I left out some of
the code.

Tried this but it's still not working

.Parameters.AddWithValue("@Button" Button10)

Code below

Protected Sub Button10_Click(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Button10.Click
Session("Mod") = DropDownList1.SelectedValue
Session("Fromdate") = FromDte.Text 'used to grab info from the
two text boxes on the aspx page
Session("toDate") = ToDTE.Text


Dim conn As New
Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("SPSConnectionString").ConnectionString)

Dim cmd As New Data.SqlClient.SqlCommand
Dim cmdSampling As New Data.SqlClient.SqlCommand

With cmdSampling
.CommandType = Data.CommandType.StoredProcedure

.CommandText = "GetSampling"
.Connection = conn

.Parameters.AddWithValue("@mod",
DropDownList1.SelectedValue)
.Parameters.AddWithValue("@Button" Button10)
.Parameters.AddWithValue("@FromDate", FromDte.Text)
.Parameters.AddWithValue("@ToDate", ToDTE.Text)


Response.Redirect("SamplingReport.aspx")
End With
End Sub

You are trying to use the Button rather than either the Button.text or
Button.clientid.

You should turn option strict and option explicit on. If you do then you
will get much more meaningful error messages before you attempt to test.

Hope this helps
LLoyd Sheen
 
Yes I want to see which button is clicked, Sorry I left out some of
the code.

Tried this but it's still not working

  .Parameters.AddWithValue("@Button" Button10)

Code below

 Protected Sub Button10_Click(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Button10.Click
        Session("Mod") = DropDownList1.SelectedValue
        Session("Fromdate") = FromDte.Text 'used to grab info from the
two text boxes on the aspx page
        Session("toDate") = ToDTE.Text

        Dim conn As New
Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("SPSCon­nectionString").ConnectionString)

        Dim cmd As New Data.SqlClient.SqlCommand
        Dim cmdSampling As New Data.SqlClient.SqlCommand

        With cmdSampling
            .CommandType = Data.CommandType.StoredProcedure

            .CommandText = "GetSampling"
            .Connection = conn

            .Parameters.AddWithValue("@mod",
DropDownList1.SelectedValue)
            .Parameters.AddWithValue("@Button" Button10)
            .Parameters.AddWithValue("@FromDate", FromDte.Text)
            .Parameters.AddWithValue("@ToDate", ToDTE.Text)

            Response.Redirect("SamplingReport.aspx")
        End With
    End Sub

You are trying to use the Button rather than either the Button.text or
Button.clientid.

You should turn option strict and option explicit on.  If you do then you
will get much more meaningful error messages before you attempt to test.

Hope this helps
LLoyd Sheen- Hide quoted text -

- Show quoted text -
Click to expand...

Thanks got it now!
 
Back
Top