ASP.NET variable not getting assigned values

Question

Im having problem with this asp.net code. the variables qty and itname are not getting valid values ...can anyone find out the problem ?

Imports System.Data
Imports System.Data.SqlClient
Partial Class consolidate
    Inherits System.Web.UI.Page

    Public lastreq_no As Int32

    Protected Sub btnconsolidate_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnconsolidate.Click
        Dim qtypen As Integer
        Dim qtypencil As Integer
        Dim qtygbag As Integer
        Dim qtysugar As Integer
        Dim i As Integer
        Dim req As Integer
        Dim qty As Integer
        Dim itname As String = ""
        Dim sqlcon As New SqlConnection("Data Source=user-hp\sqlexpress;initial catalog=campco;integrated security=true;")
        If sqlcon.State = ConnectionState.Open Then
            sqlcon.Close()
        End If

        sqlcon.Open()
        Dim str As String
        str = "Select Req_no from Requirements "
        Dim cmd As New SqlCommand(str, sqlcon)
        Dim sdr As SqlDataReader
        sdr = cmd.ExecuteReader()
        sdr.Read()
        lastreq_no = sdr.GetInt32(sdr.VisibleFieldCount - 1)
        For i = 0 To sdr.VisibleFieldCount - 1
            req = sdr.GetInt32(i)
            While req > lastreq_no
                Dim selcomnd1 As String
                Dim selcomnd2 As String
                selcomnd1 = "Select @itname=It_name from Requirements where Req_no= @req"
                selcomnd2 = "Select @qty= Quantity from Requirements where Req_no= @req"
                Dim sqlcomnd1 As New SqlCommand(selcomnd1, sqlcon)
                Dim sqlcomnd2 As New SqlCommand(selcomnd2, sqlcon)
                sqlcomnd1.Parameters.AddWithValue("@itname", itname)
                sqlcomnd2.Parameters.AddWithValue("@qty", qty)
                sqlcomnd1.ExecuteScalar()
                sqlcomnd2.ExecuteScalar()
                TextBox1.Text = itname
                TextBox2.Text = qty
                sqlcon.Close()
                sqlcon.Open()
                Select Case (itname)
                    Case "Pen"
                        qtypen += qty
                        lastreq_no = req
                    Case "Pencil"
                        qtypencil += qty
                        lastreq_no = req
                    Case "Gunny bag"
                        qtygbag += qty
                        lastreq_no = req
                    Case "Sugar"
                        qtysugar += qty
                        lastreq_no = req
                End Select
            End While
        Next

        sqlcon.Close()
        If sqlcon.State = ConnectionState.Open Then
            sqlcon.Close()
        End If
        sqlcon.Open()
        Dim comm As String
        comm = "Insert into Consolidate (lastr_no,qtypen,qtypencil,qtygunnybag,qtysugar)values('" + lastreq_no.ToString + "','" + qtypen.ToString + "','" + qtypencil.ToString + "','" + qtygbag.ToString + "','" + qtysugar.ToString + "')"
        Dim sqlcomm As New SqlCommand(comm, sqlcon)
        Dim s As String
        s = sqlcomm.ExecuteNonQuery()
        sqlcon.Close()
    End Sub
End Class

Instead of posting this whole bunch of code and ask for help, I think you should probably discuss the problem you've faced when you try to run the code. And also, is it valid to write the query like you wrote in selcommand1 and selcommand2. I don't think so. — icemojo, Feb 25 '13 at 13:16

score 1 · Answer 1 · answered Feb 25 '13 at 13:14

To start with, neither scalar statement is valid. Have you attempted to run those statements in SQL Management Studio or similar program to test the statements themselves? They should be something like:

selcomnd1 = "Select It_name from Requirements where Req_no=@req"
selcomnd2 = "Select Quantity from Requirements where Req_no=@req"

And then you would assign them in this manner:

itname = CType(sqlcmnd1.ExecuteScalar(), String)  ' .ToString() would probably work here as well
qty = Convert.Int32(sqlcmnd2.ExecuteScalar())

Or you could use .TryParse for the qty:

Integer.TryParse(sqlcmnd2.ExecuteScalar(), qty)

Yap, this is the kind of sql query I'm familiar with. The query should return the scaler value inside the database. — icemojo, Feb 25 '13 at 13:20

score 0 · Answer 2 · edited May 23 '17 at 12:03

0

The line

sqlcomnd1.Parameters.AddWithValue("@itname", itname)

provides an input parameter with the value itname. No value has been assigned to this variable.

You need to add an output parameter: see here for how to do this.

Get output parameter value in ADO.NET

edited May 23 '17 at 12:03

Community

1
1

answered Feb 25 '13 at 13:14

Tim Rogers

21,297
6
52
68

ASP.NET variable not getting assigned values

2 Answers2