slight confusion over SQL

[mcfly]

Hi All,

This I am hoping will be simple I have the following code:

Imports System.Data.OleDb
Imports System.Data.SqlClient

Partial Class _Default
    Inherits System.Web.UI.Page

    Public aos_code, aos_period, str_field As String

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        lblDetails.Text = Right(HttpContext.Current.User.Identity.Name, Len(HttpContext.Current.User.Identity.Name) - 8) + " :: " + HttpContext.Current.Server.MachineName + " :: " + Format(Now(), "dd/MM/yyyy h:mm")

        If (aos_code <> "") And (aos_period <> "") Then
            MsgBox("Your aos_code is " & aos_code & "Your aos_period is " & aos_period)
        End If
    End Sub

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button1.Click
        ' Get SqlDataSource Select Parameters

        Dim txt As New TextBox()

        txt = CType(FindControl("TextBox1"), TextBox)

        aos_code = txt.Text

        txt = CType(FindControl("TextBox2"), TextBox)

        aos_period = txt.Text

        Dim sqldbconn As New Data.SqlClient.SqlConnection
        Dim ds As New DataSet
        Dim da As New SqlClient.SqlDataAdapter
        Dim cmd As New SqlClient.SqlCommand

        sqldbconn.ConnectionString = "Data Source=*********;Initial Catalog=******;Integrated Security=True"

        cmd.CommandType = CommandType.Text

        cmd.CommandText = "SELECT * FROM ****** WHERE aos_code = @aos_code AND aos_period = @aos_period"

        cmd.Parameters.AddWithValue("@aos_code", SqlDbType.VarChar).Value = aos_code

        cmd.Parameters.AddWithValue("@aos_period", SqlDbType.VarChar).Value = aos_period

        Dim newSTR As Integer

        newSTR = cmd.Parameters("str_field").Value

        cmd.Connection = sqldbconn

        da.SelectCommand = cmd

        sqldbconn.Open()

        'for testing
        MsgBox("Your aos_code is " & aos_code & "Your aos_period is " & aos_period & "Your str_field is " & newSTR)

        da.Fill(ds)

        sqldbconn.Close()
    End Sub
End Class

I've removed the connection string details but the error I am getting is:

An SqlParameter with ParameterName 'str_field' is not contained by this SqlParameterCollection.

I am getting two values that are given by the user, aos_code and aos_period,I then need the str_field. I can can pass the parameter ok, but the problem is I can't get a value back from the table the error occurs at this line:

newSTR = cmd.Parameters("str_field").Value

I am very new to and have been trying now for hours, any one any ideas?...

 

[Menthos]

So str_field is the field you're trying to retrieve? Why not just specify that in the commandText instead of pulling back all the fields and then grab it from the dataset? or perhaps use the ExecuteScalar method on the command object if you know there's only going to be one result?

 

[mcfly]

yes thats correct str_field is what i need, i was thinking i had already selected just the row where the aos_code and aos_period equalled the values so thought I could somehow just select another column and write the value I wanted to a variable...

there will be only one occurance of what the user enters, so I could use this ExecuteScalar method but I have never used it before. I'm very new to this, so perhaps i am going about the whole situation from the wrong angle...

 

[mcfly]

Tried again, I now don't get an error but get a value of 8, which is very random as I can't see where this value would be comming from, here's the code:

Imports System.Data.OleDb
Imports System.Data.SqlClient

Partial Class _Default
    Inherits System.Web.UI.Page

    Public aos_code, aos_period, str_field As String

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        lblDetails.Text = Right(HttpContext.Current.User.Identity.Name, Len(HttpContext.Current.User.Identity.Name) - 8) + " :: " + HttpContext.Current.Server.MachineName + " :: " + Format(Now(), "dd/MM/yyyy h:mm")

        If (aos_code <> "") And (aos_period <> "") Then
            MsgBox("Your aos_code is " & aos_code & "Your aos_period is " & aos_period)
        End If
    End Sub

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button1.Click
        ' Get SqlDataSource Select Parameters

        Dim txt As New TextBox()

        txt = CType(FindControl("TextBox1"), TextBox)

        aos_code = txt.Text

        txt = CType(FindControl("TextBox2"), TextBox)

        aos_period = txt.Text

        Dim sqldbconn As New Data.SqlClient.SqlConnection
        Dim ds As New DataSet
        Dim da As New SqlClient.SqlDataAdapter
        Dim cmd As New SqlClient.SqlCommand

        sqldbconn.ConnectionString = "Data Source=******;Initial Catalog=******;Integrated Security=True"

        cmd.CommandType = CommandType.Text

        cmd.CommandText = "SELECT * FROM ******* (aos_code = @aos_code) AND (aos_period = @aos_period)"

        cmd.Parameters.AddWithValue("@aos_code", SqlDbType.VarChar).Value = aos_code

        cmd.Parameters.AddWithValue("@aos_period", SqlDbType.VarChar).Value = aos_period

        Dim newSTR As New OleDbParameter("@str_field", Data.SqlDbType.Int)

        Dim newSTR2 As Integer = newSTR.Value

        cmd.Connection = sqldbconn

        da.SelectCommand = cmd

        sqldbconn.Open()

        da.Fill(ds)

        'Dim newSTR As String = "text_data"

        'for testing
        MsgBox("Your aos_code is " & aos_code & "Your aos_period is " & aos_period & "Your str_field is " & newSTR2)

        sqldbconn.Close()
    End Sub
End Class

 

[Menthos]

At a guess you have 8 fields per row, right? without testing it out, it looks like you're pulling the field count.

'personally' I'd use the following to get the result (assuming str_field is the record field you're after)... not sure why you'd need to use a dataset for pulling back a single field, although obviously if you start wanting to pull back multiple records then datatables and datasets become more useful.

BTW, i've not tested the below, it's just off the top of my head and I'd usually pass the parameters in the SQL string rather than use actual parameter objects, but I'm sure what you're doing is the recommended way of doing things.

                Dim sqldbconn As New Data.SqlClient.SqlConnection
        
    Dim cmd As New SqlClient.SqlCommand

        sqldbconn.ConnectionString = "Data Source=******;Initial Catalog=******;Integrated Security=True"

        cmd.CommandType = CommandType.Text

        cmd.CommandText = "SELECT str_field FROM ******* (aos_code = @aos_code) AND (aos_period = @aos_period)"

        cmd.Parameters.AddWithValue("@aos_code", SqlDbType.VarChar).Value = aos_code

        cmd.Parameters.AddWithValue("@aos_period", SqlDbType.VarChar).Value = aos_period

        cmd.Connection = sqldbconn
    

    Try
            sqldbconn.Open()

            Dim strFieldVal as Integer = cmd.ExecuteScalar()

            Dim newSTR As String = strFieldVal.toString()

            'for testing
            MsgBox("Your aos_code is " & aos_code & "Your aos_period is " & aos_period & "Your str_field is " & newSTR )

    Finally

           sqldbconn.Close()

    End Try

 

[mcfly]

this code now works, thank you.