Insert and Variables driving me crazy! Newbie...

Discussion in 'ASP .Net' started by =?Utf-8?B?VGltOjouLg==?=, Dec 20, 2004.

  1. Hi,

    Can someone please help me with what I presume is probably a relatively
    simple problem! I am trying to return two values (PageID and OfficeID) and
    then insert them into another table. My problem is that the values for
    PageID and OfficeID are contained in seperate Subs and I don't know or
    understand how I should get these values into a third sub that carries out
    the insert!

    Please see the code below! Thanks for any help!



    ...CODE

    Private PageID As Integer
    ' Provides the pageID of the record just inserted
    Public Sub DGPages_Insert(ByVal sender As Object, ByVal e As
    DataGridCommandEventArgs)
    If e.CommandName = "Insert" Then
    Dim modDate As String
    Dim dtNow As DateTime = DateTime.Now
    Dim description As String
    Dim txtdescription As TextBox
    Dim title As String
    Dim txtTitle As TextBox


    Dim strSQL As String
    modDate = dtNow.Date
    'Read in the values of the TextBoxes


    txtdescription = e.Item.FindControl("add_description")
    description = txtdescription.Text
    txtTitle = e.Item.FindControl("add_Title")
    title = txtTitle.Text


    'Create the appropriate SQL statement
    Dim Myconn As New
    SqlConnection(ConfigurationSettings.AppSettings("strConn"))
    Dim cmd As New SqlCommand("PageAdd", Myconn)
    cmd.CommandType = CommandType.StoredProcedure

    Myconn.Open()

    ' Adds Information from the insert area within the datagrid into
    tblPageContent
    ' and returns the record ID
    Dim objModDate, objDescription, objTitle, objPageID, objOffice
    As SqlParameter
    objModDate = cmd.Parameters.Add("@modDate", SqlDbType.DateTime)
    objDescription = cmd.Parameters.Add("@description",
    SqlDbType.NVarChar)
    objTitle = cmd.Parameters.Add("@title", SqlDbType.NVarChar)
    objPageID = cmd.Parameters.Add("@PageID", SqlDbType.Int)

    objModDate.Direction = ParameterDirection.Input
    objDescription.Direction = ParameterDirection.Input
    objTitle.Direction = ParameterDirection.Input
    cmd.Parameters("@PageID").Direction = ParameterDirection.Output

    objModDate.Value = modDate
    objDescription.Value = description
    objTitle.Value = title


    Dim myReader As SqlDataReader = cmd.ExecuteReader()

    myReader.Read()
    myReader.Close()
    PageID = cmd.Parameters("@PageID").Value
    Myconn.Close()

    'Rebind the DataGrid
    DGPages.EditItemIndex = -1
    BindData()
    End If

    End Sub

    ' Gets the OfficeID from tblOffice
    Public Function GetOfficeID_for_Insert() As DataSet
    Dim cn As New SqlConnection("strConn")
    Dim da As New SqlDataAdapter
    Dim ds As New DataSet
    Dim cmd As New SqlCommand("SelectOfficeID", cn)
    cmd.CommandType = CommandType.StoredProcedure
    Dim param As New SqlParameter("@offName", 5)
    param.Direction = ParameterDirection.Input
    cmd.Parameters.Add(param)
    da.SelectCommand = cmd
    da.Fill(ds)
    Return ds
    End Function



    ' Under Construction
    ' Should insert the pageID and OfficeID into tblOfficePage
    Sub InsertPageID_OfficeID(ByVal sender As Object, ByVal e As
    DataGridCommandEventArgs)
    If e.CommandName = "Insert" Then
    Dim OfficeID As DataSet
    Dim PageID As Integer = PageID

    OfficeID = GetOfficeID_for_Insert()


    'Create the appropriate SQL statement
    Dim Myconn As New
    SqlConnection(ConfigurationSettings.AppSettings("strConn"))
    Dim cmd As New SqlCommand("OfficePageAdd", Myconn)
    cmd.CommandType = CommandType.StoredProcedure

    Myconn.Open()

    ' Adds the OfficeID and PageID into tblOfficePageContent
    Dim objOfficeID, objPageID As SqlParameter
    objOfficeID = cmd.Parameters.Add("@officeID", SqlDbType.NVarChar)
    objPageID = cmd.Parameters.Add("@PageID", SqlDbType.NVarChar)

    objOfficeID.Direction = ParameterDirection.Input
    objPageID.Direction = ParameterDirection.Input

    objOfficeID.Value = OfficeID
    objPageID.Value = PageID

    Dim myReader As SqlDataReader = cmd.ExecuteReader()
    myReader.Read()

    myReader.Close()
    Myconn.Close()
    End If
     
    =?Utf-8?B?VGltOjouLg==?=, Dec 20, 2004
    #1
    1. Advertising

  2. Hi,

    There are quite a few things that you should look at with this code. You
    seem to use dataset's in almost every case for fetching transient records
    that you only need once. You can use execute scalar and execute reader to
    return single records and transient data that you don't need to persist.
    Also this line won't ever work : Dim cn As New SqlConnection("strConn")
    = you are really using the AppSettings collection. Check the code for this
    sort of error!

    You need to learn some basic programming skills before embarking on this
    sort of a project. Your basic mistake is returning a dataset from each of
    your ID lookup functions rather than the value itself. Presuming that the ID
    is numerical you want to do this sort of thing

    public sub Main()

    DoInsert(LookupPageId(), LookupOfficeId())

    End Sub

    Public Sub DoInsert(PageId as Long, OfficeId as Long)
    'open a connection and fire the insert sql here
    End sub

    Private Function LookupPageId() as Long
    'open a connection, and executeReader()
    dim dtr as datareader

    dtr.read() 'move the head to the first record
    return dtr.items(0)

    End Function

    Private Function LookupOfficeID() as Long
    'as above
    End Function


    Hope this points you in the right direction

    Nick


    "Tim::.." <myatix_at_hotmail.com> wrote in message
    news:...
    > Hi,
    >
    > Can someone please help me with what I presume is probably a relatively
    > simple problem! I am trying to return two values (PageID and OfficeID) and
    > then insert them into another table. My problem is that the values for
    > PageID and OfficeID are contained in seperate Subs and I don't know or
    > understand how I should get these values into a third sub that carries out
    > the insert!
    >
    > Please see the code below! Thanks for any help!
    >
    >
    >
    > ..CODE
    >
    > Private PageID As Integer
    > ' Provides the pageID of the record just inserted
    > Public Sub DGPages_Insert(ByVal sender As Object, ByVal e As
    > DataGridCommandEventArgs)
    > If e.CommandName = "Insert" Then
    > Dim modDate As String
    > Dim dtNow As DateTime = DateTime.Now
    > Dim description As String
    > Dim txtdescription As TextBox
    > Dim title As String
    > Dim txtTitle As TextBox
    >
    >
    > Dim strSQL As String
    > modDate = dtNow.Date
    > 'Read in the values of the TextBoxes
    >
    >
    > txtdescription = e.Item.FindControl("add_description")
    > description = txtdescription.Text
    > txtTitle = e.Item.FindControl("add_Title")
    > title = txtTitle.Text
    >
    >
    > 'Create the appropriate SQL statement
    > Dim Myconn As New
    > SqlConnection(ConfigurationSettings.AppSettings("strConn"))
    > Dim cmd As New SqlCommand("PageAdd", Myconn)
    > cmd.CommandType = CommandType.StoredProcedure
    >
    > Myconn.Open()
    >
    > ' Adds Information from the insert area within the datagrid

    into
    > tblPageContent
    > ' and returns the record ID
    > Dim objModDate, objDescription, objTitle, objPageID, objOffice
    > As SqlParameter
    > objModDate = cmd.Parameters.Add("@modDate",

    SqlDbType.DateTime)
    > objDescription = cmd.Parameters.Add("@description",
    > SqlDbType.NVarChar)
    > objTitle = cmd.Parameters.Add("@title", SqlDbType.NVarChar)
    > objPageID = cmd.Parameters.Add("@PageID", SqlDbType.Int)
    >
    > objModDate.Direction = ParameterDirection.Input
    > objDescription.Direction = ParameterDirection.Input
    > objTitle.Direction = ParameterDirection.Input
    > cmd.Parameters("@PageID").Direction =

    ParameterDirection.Output
    >
    > objModDate.Value = modDate
    > objDescription.Value = description
    > objTitle.Value = title
    >
    >
    > Dim myReader As SqlDataReader = cmd.ExecuteReader()
    >
    > myReader.Read()
    > myReader.Close()
    > PageID = cmd.Parameters("@PageID").Value
    > Myconn.Close()
    >
    > 'Rebind the DataGrid
    > DGPages.EditItemIndex = -1
    > BindData()
    > End If
    >
    > End Sub
    >
    > ' Gets the OfficeID from tblOffice
    > Public Function GetOfficeID_for_Insert() As DataSet
    > Dim cn As New SqlConnection("strConn")
    > Dim da As New SqlDataAdapter
    > Dim ds As New DataSet
    > Dim cmd As New SqlCommand("SelectOfficeID", cn)
    > cmd.CommandType = CommandType.StoredProcedure
    > Dim param As New SqlParameter("@offName", 5)
    > param.Direction = ParameterDirection.Input
    > cmd.Parameters.Add(param)
    > da.SelectCommand = cmd
    > da.Fill(ds)
    > Return ds
    > End Function
    >
    >
    >
    > ' Under Construction
    > ' Should insert the pageID and OfficeID into tblOfficePage
    > Sub InsertPageID_OfficeID(ByVal sender As Object, ByVal e As
    > DataGridCommandEventArgs)
    > If e.CommandName = "Insert" Then
    > Dim OfficeID As DataSet
    > Dim PageID As Integer = PageID
    >
    > OfficeID = GetOfficeID_for_Insert()
    >
    >
    > 'Create the appropriate SQL statement
    > Dim Myconn As New
    > SqlConnection(ConfigurationSettings.AppSettings("strConn"))
    > Dim cmd As New SqlCommand("OfficePageAdd", Myconn)
    > cmd.CommandType = CommandType.StoredProcedure
    >
    > Myconn.Open()
    >
    > ' Adds the OfficeID and PageID into tblOfficePageContent
    > Dim objOfficeID, objPageID As SqlParameter
    > objOfficeID = cmd.Parameters.Add("@officeID",

    SqlDbType.NVarChar)
    > objPageID = cmd.Parameters.Add("@PageID", SqlDbType.NVarChar)
    >
    > objOfficeID.Direction = ParameterDirection.Input
    > objPageID.Direction = ParameterDirection.Input
    >
    > objOfficeID.Value = OfficeID
    > objPageID.Value = PageID
    >
    > Dim myReader As SqlDataReader = cmd.ExecuteReader()
    > myReader.Read()
    >
    > myReader.Close()
    > Myconn.Close()
    > End If
     
    Nick Stansbury, Dec 20, 2004
    #2
    1. Advertising

  3. Hi Nick,

    Thanks for the response! I have an issue with LookupPageID as I return the
    PageID Identity value when data is inserted into that table! How would I use
    a seperate function to return this value when the function doesn't do the
    insert !

    Thanks!

    ''' CODE

    Private Function LookupPageId() As Long
    'open a connection, and executeReader()
    Dim Myconn As New
    SqlConnection(ConfigurationSettings.AppSettings("strConn"))
    Dim cmd As New SqlCommand("SelectOfficeID", Myconn)
    cmd.CommandType = CommandType.StoredProcedure

    Myconn.Open()


    Dim dtr As SqlDataReader = cmd.ExecuteReader()

    dtr.Read() 'move the head to the first record
    dtr.Close()
    Return dtr.Item(0)
    Myconn.Close()

    End Function

    "Nick Stansbury" wrote:

    > Hi,
    >
    > There are quite a few things that you should look at with this code. You
    > seem to use dataset's in almost every case for fetching transient records
    > that you only need once. You can use execute scalar and execute reader to
    > return single records and transient data that you don't need to persist.
    > Also this line won't ever work : Dim cn As New SqlConnection("strConn")
    > = you are really using the AppSettings collection. Check the code for this
    > sort of error!
    >
    > You need to learn some basic programming skills before embarking on this
    > sort of a project. Your basic mistake is returning a dataset from each of
    > your ID lookup functions rather than the value itself. Presuming that the ID
    > is numerical you want to do this sort of thing
    >
    > public sub Main()
    >
    > DoInsert(LookupPageId(), LookupOfficeId())
    >
    > End Sub
    >
    > Public Sub DoInsert(PageId as Long, OfficeId as Long)
    > 'open a connection and fire the insert sql here
    > End sub
    >
    > Private Function LookupPageId() as Long
    > 'open a connection, and executeReader()
    > dim dtr as datareader
    >
    > dtr.read() 'move the head to the first record
    > return dtr.items(0)
    >
    > End Function
    >
    > Private Function LookupOfficeID() as Long
    > 'as above
    > End Function
    >
    >
    > Hope this points you in the right direction
    >
    > Nick
     
    =?Utf-8?B?VGltOjouLg==?=, Dec 20, 2004
    #3
  4. Tim,
    Sorry this isn't really clear to me. I think what you are saying is that
    LookUpPageId() uses a stored procedure to insert a page and then returns the
    latest ID.
    Firstly : If it returns an id then it should be via a return parameter -
    which is a bit more complex to implement. You have to add parameters to the
    SQLCommand, and specify the direction as OUTPUT.

    See:
    http://msdn.microsoft.com/library/d.../vbtskSettingGettingDataCommandParameters.asp

    As regards returning the PageID from a separate function - if I
    understand correctly you can assign the value to a local variable...but I'm
    not sure I really understand the problem :

    dim NewPageID as long
    NewPageId = LookupPageID()

    InsertNewRecord(NewPageID, LookupOfficeId())

    Regards,

    Nick


    "Tim::.." <myatix_at_hotmail.com> wrote in message
    news:...
    > Hi Nick,
    >
    > Thanks for the response! I have an issue with LookupPageID as I return the
    > PageID Identity value when data is inserted into that table! How would I

    use
    > a seperate function to return this value when the function doesn't do the
    > insert !
    >
    > Thanks!
    >
    > ''' CODE
    >
    > Private Function LookupPageId() As Long
    > 'open a connection, and executeReader()
    > Dim Myconn As New
    > SqlConnection(ConfigurationSettings.AppSettings("strConn"))
    > Dim cmd As New SqlCommand("SelectOfficeID", Myconn)
    > cmd.CommandType = CommandType.StoredProcedure
    >
    > Myconn.Open()
    >
    >
    > Dim dtr As SqlDataReader = cmd.ExecuteReader()
    >
    > dtr.Read() 'move the head to the first record
    > dtr.Close()
    > Return dtr.Item(0)
    > Myconn.Close()
    >
    > End Function
    >
    > "Nick Stansbury" wrote:
    >
    > > Hi,
    > >
    > > There are quite a few things that you should look at with this code.

    You
    > > seem to use dataset's in almost every case for fetching transient

    records
    > > that you only need once. You can use execute scalar and execute reader

    to
    > > return single records and transient data that you don't need to persist.
    > > Also this line won't ever work : Dim cn As New

    SqlConnection("strConn")
    > > = you are really using the AppSettings collection. Check the code for

    this
    > > sort of error!
    > >
    > > You need to learn some basic programming skills before embarking on

    this
    > > sort of a project. Your basic mistake is returning a dataset from each

    of
    > > your ID lookup functions rather than the value itself. Presuming that

    the ID
    > > is numerical you want to do this sort of thing
    > >
    > > public sub Main()
    > >
    > > DoInsert(LookupPageId(), LookupOfficeId())
    > >
    > > End Sub
    > >
    > > Public Sub DoInsert(PageId as Long, OfficeId as Long)
    > > 'open a connection and fire the insert sql here
    > > End sub
    > >
    > > Private Function LookupPageId() as Long
    > > 'open a connection, and executeReader()
    > > dim dtr as datareader
    > >
    > > dtr.read() 'move the head to the first record
    > > return dtr.items(0)
    > >
    > > End Function
    > >
    > > Private Function LookupOfficeID() as Long
    > > 'as above
    > > End Function
    > >
    > >
    > > Hope this points you in the right direction
    > >
    > > Nick

    >
     
    Nick Stansbury, Dec 20, 2004
    #4
  5. Hi Nick,

    If you look at the first piece of code I sent you I have already returned
    the pageID!
    I just don't know how to get this variable from the Public Sub
    DGPages_Insert into your sub DoInsert ???

    I would be really grateful if you could answer this and then I will promise
    to stop pestering!!!

    Thanks for all you help!


    ''' CODE
    Public Sub DGPages_Insert(ByVal sender As Object, ByVal e As
    DataGridCommandEventArgs)
    If e.CommandName = "Insert" Then
    Dim modDate As String
    Dim dtNow As DateTime = DateTime.Now
    Dim description As String
    Dim txtdescription As TextBox
    Dim title As String
    Dim txtTitle As TextBox


    Dim strSQL As String
    modDate = dtNow.Date
    'Read in the values of the TextBoxes


    txtdescription = e.Item.FindControl("add_description")
    description = txtdescription.Text
    txtTitle = e.Item.FindControl("add_Title")
    title = txtTitle.Text


    'Create the appropriate SQL statement
    Dim Myconn As New
    SqlConnection(ConfigurationSettings.AppSettings("strConn"))
    Dim cmd As New SqlCommand("PageAdd", Myconn)
    cmd.CommandType = CommandType.StoredProcedure

    Myconn.Open()

    ' Adds Information from the insert area within the datagrid into
    tblPageContent
    ' and returns the record ID
    Dim objModDate, objDescription, objTitle, objPageID, objOffice
    As SqlParameter
    objModDate = cmd.Parameters.Add("@modDate", SqlDbType.DateTime)
    objDescription = cmd.Parameters.Add("@description",
    SqlDbType.NVarChar)
    objTitle = cmd.Parameters.Add("@title", SqlDbType.NVarChar)
    objPageID = cmd.Parameters.Add("@PageID", SqlDbType.Int)

    objModDate.Direction = ParameterDirection.Input
    objDescription.Direction = ParameterDirection.Input
    objTitle.Direction = ParameterDirection.Input
    cmd.Parameters("@PageID").Direction = ParameterDirection.Output

    objModDate.Value = modDate
    objDescription.Value = description
    objTitle.Value = title


    Dim myReader As SqlDataReader = cmd.ExecuteReader()

    myReader.Read()
    myReader.Close()
    PageID = cmd.Parameters("@PageID").Value
    Myconn.Close()

    'Rebind the DataGrid
    DGPages.EditItemIndex = -1
    BindData()
    End If

    End Sub


    "Nick Stansbury" wrote:

    > Tim,
    > Sorry this isn't really clear to me. I think what you are saying is that
    > LookUpPageId() uses a stored procedure to insert a page and then returns the
    > latest ID.
    > Firstly : If it returns an id then it should be via a return parameter -
    > which is a bit more complex to implement. You have to add parameters to the
    > SQLCommand, and specify the direction as OUTPUT.
    >
    > See:
    > http://msdn.microsoft.com/library/d.../vbtskSettingGettingDataCommandParameters.asp
    >
    > As regards returning the PageID from a separate function - if I
    > understand correctly you can assign the value to a local variable...but I'm
    > not sure I really understand the problem :
    >
    > dim NewPageID as long
    > NewPageId = LookupPageID()
    >
    > InsertNewRecord(NewPageID, LookupOfficeId())
    >
    > Regards,
    >
    > Nick
    >
    >
    > "Tim::.." <myatix_at_hotmail.com> wrote in message
    > news:...
    > > Hi Nick,
    > >
    > > Thanks for the response! I have an issue with LookupPageID as I return the
    > > PageID Identity value when data is inserted into that table! How would I

    > use
    > > a seperate function to return this value when the function doesn't do the
    > > insert !
    > >
    > > Thanks!
    > >
    > > ''' CODE
    > >
    > > Private Function LookupPageId() As Long
    > > 'open a connection, and executeReader()
    > > Dim Myconn As New
    > > SqlConnection(ConfigurationSettings.AppSettings("strConn"))
    > > Dim cmd As New SqlCommand("SelectOfficeID", Myconn)
    > > cmd.CommandType = CommandType.StoredProcedure
    > >
    > > Myconn.Open()
    > >
    > >
    > > Dim dtr As SqlDataReader = cmd.ExecuteReader()
    > >
    > > dtr.Read() 'move the head to the first record
    > > dtr.Close()
    > > Return dtr.Item(0)
    > > Myconn.Close()
    > >
    > > End Function
    > >
    > > "Nick Stansbury" wrote:
    > >
    > > > Hi,
    > > >
    > > > There are quite a few things that you should look at with this code.

    > You
    > > > seem to use dataset's in almost every case for fetching transient

    > records
    > > > that you only need once. You can use execute scalar and execute reader

    > to
    > > > return single records and transient data that you don't need to persist.
    > > > Also this line won't ever work : Dim cn As New

    > SqlConnection("strConn")
    > > > = you are really using the AppSettings collection. Check the code for

    > this
    > > > sort of error!
    > > >
    > > > You need to learn some basic programming skills before embarking on

    > this
    > > > sort of a project. Your basic mistake is returning a dataset from each

    > of
    > > > your ID lookup functions rather than the value itself. Presuming that

    > the ID
    > > > is numerical you want to do this sort of thing
    > > >
    > > > public sub Main()
    > > >
    > > > DoInsert(LookupPageId(), LookupOfficeId())
    > > >
    > > > End Sub
    > > >
    > > > Public Sub DoInsert(PageId as Long, OfficeId as Long)
    > > > 'open a connection and fire the insert sql here
    > > > End sub
    > > >
    > > > Private Function LookupPageId() as Long
    > > > 'open a connection, and executeReader()
    > > > dim dtr as datareader
    > > >
    > > > dtr.read() 'move the head to the first record
    > > > return dtr.items(0)
    > > >
    > > > End Function
    > > >
    > > > Private Function LookupOfficeID() as Long
    > > > 'as above
    > > > End Function
    > > >
    > > >
    > > > Hope this points you in the right direction
    > > >
    > > > Nick

    > >

    >
    >
    >
     
    =?Utf-8?B?VGltOjouLg==?=, Dec 20, 2004
    #5
  6. Tim,
    I see what you mean now - sorry. You want to call DoInsert() at a later
    date, and want to pass it the value of PageId()

    Firstly - if you don't call it later then you could just call DoInsert from
    DGPages_Insert - if it is basically all one process then why not just call
    DoInsert from DGPages_Insert?

    Alternatively you have to keep PageId in scope and acceesible from outside
    of DGPages_Insert. This could be a property on a persisted object, or in
    session (are you on an asp.net page or in a windows form?) memory. So
    presuming it is an asp.net page - without trawling through your code - try
    declaring a private property:

    private _NewPageID as long

    Then just allocate it from DGPages_Insert :

    Me._NewPageID = PageId

    Then we could call do insert as follows:

    DoInsert(_NewPageId, LookupOfficeId())

    - does that answer your question?

    Nick






    "Tim::.." <myatix_at_hotmail.com> wrote in message
    news:...
    > Hi Nick,
    >
    > If you look at the first piece of code I sent you I have already returned
    > the pageID!
    > I just don't know how to get this variable from the Public Sub
    > DGPages_Insert into your sub DoInsert ???
    >
    > I would be really grateful if you could answer this and then I will

    promise
    > to stop pestering!!!
    >
    > Thanks for all you help!
    >
    >
    > ''' CODE
    > Public Sub DGPages_Insert(ByVal sender As Object, ByVal e As
    > DataGridCommandEventArgs)
    > If e.CommandName = "Insert" Then
    > Dim modDate As String
    > Dim dtNow As DateTime = DateTime.Now
    > Dim description As String
    > Dim txtdescription As TextBox
    > Dim title As String
    > Dim txtTitle As TextBox
    >
    >
    > Dim strSQL As String
    > modDate = dtNow.Date
    > 'Read in the values of the TextBoxes
    >
    >
    > txtdescription = e.Item.FindControl("add_description")
    > description = txtdescription.Text
    > txtTitle = e.Item.FindControl("add_Title")
    > title = txtTitle.Text
    >
    >
    > 'Create the appropriate SQL statement
    > Dim Myconn As New
    > SqlConnection(ConfigurationSettings.AppSettings("strConn"))
    > Dim cmd As New SqlCommand("PageAdd", Myconn)
    > cmd.CommandType = CommandType.StoredProcedure
    >
    > Myconn.Open()
    >
    > ' Adds Information from the insert area within the datagrid

    into
    > tblPageContent
    > ' and returns the record ID
    > Dim objModDate, objDescription, objTitle, objPageID, objOffice
    > As SqlParameter
    > objModDate = cmd.Parameters.Add("@modDate",

    SqlDbType.DateTime)
    > objDescription = cmd.Parameters.Add("@description",
    > SqlDbType.NVarChar)
    > objTitle = cmd.Parameters.Add("@title", SqlDbType.NVarChar)
    > objPageID = cmd.Parameters.Add("@PageID", SqlDbType.Int)
    >
    > objModDate.Direction = ParameterDirection.Input
    > objDescription.Direction = ParameterDirection.Input
    > objTitle.Direction = ParameterDirection.Input
    > cmd.Parameters("@PageID").Direction =

    ParameterDirection.Output
    >
    > objModDate.Value = modDate
    > objDescription.Value = description
    > objTitle.Value = title
    >
    >
    > Dim myReader As SqlDataReader = cmd.ExecuteReader()
    >
    > myReader.Read()
    > myReader.Close()
    > PageID = cmd.Parameters("@PageID").Value
    > Myconn.Close()
    >
    > 'Rebind the DataGrid
    > DGPages.EditItemIndex = -1
    > BindData()
    > End If
    >
    > End Sub
    >
    >
    > "Nick Stansbury" wrote:
    >
    > > Tim,
    > > Sorry this isn't really clear to me. I think what you are saying is

    that
    > > LookUpPageId() uses a stored procedure to insert a page and then returns

    the
    > > latest ID.
    > > Firstly : If it returns an id then it should be via a return

    parameter -
    > > which is a bit more complex to implement. You have to add parameters to

    the
    > > SQLCommand, and specify the direction as OUTPUT.
    > >
    > > See:
    > >

    http://msdn.microsoft.com/library/d.../vbtskSettingGettingDataCommandParameters.asp
    > >
    > > As regards returning the PageID from a separate function - if I
    > > understand correctly you can assign the value to a local variable...but

    I'm
    > > not sure I really understand the problem :
    > >
    > > dim NewPageID as long
    > > NewPageId = LookupPageID()
    > >
    > > InsertNewRecord(NewPageID, LookupOfficeId())
    > >
    > > Regards,
    > >
    > > Nick
    > >
    > >
    > > "Tim::.." <myatix_at_hotmail.com> wrote in message
    > > news:...
    > > > Hi Nick,
    > > >
    > > > Thanks for the response! I have an issue with LookupPageID as I return

    the
    > > > PageID Identity value when data is inserted into that table! How would

    I
    > > use
    > > > a seperate function to return this value when the function doesn't do

    the
    > > > insert !
    > > >
    > > > Thanks!
    > > >
    > > > ''' CODE
    > > >
    > > > Private Function LookupPageId() As Long
    > > > 'open a connection, and executeReader()
    > > > Dim Myconn As New
    > > > SqlConnection(ConfigurationSettings.AppSettings("strConn"))
    > > > Dim cmd As New SqlCommand("SelectOfficeID", Myconn)
    > > > cmd.CommandType = CommandType.StoredProcedure
    > > >
    > > > Myconn.Open()
    > > >
    > > >
    > > > Dim dtr As SqlDataReader = cmd.ExecuteReader()
    > > >
    > > > dtr.Read() 'move the head to the first record
    > > > dtr.Close()
    > > > Return dtr.Item(0)
    > > > Myconn.Close()
    > > >
    > > > End Function
    > > >
    > > > "Nick Stansbury" wrote:
    > > >
    > > > > Hi,
    > > > >
    > > > > There are quite a few things that you should look at with this

    code.
    > > You
    > > > > seem to use dataset's in almost every case for fetching transient

    > > records
    > > > > that you only need once. You can use execute scalar and execute

    reader
    > > to
    > > > > return single records and transient data that you don't need to

    persist.
    > > > > Also this line won't ever work : Dim cn As New

    > > SqlConnection("strConn")
    > > > > = you are really using the AppSettings collection. Check the code

    for
    > > this
    > > > > sort of error!
    > > > >
    > > > > You need to learn some basic programming skills before embarking

    on
    > > this
    > > > > sort of a project. Your basic mistake is returning a dataset from

    each
    > > of
    > > > > your ID lookup functions rather than the value itself. Presuming

    that
    > > the ID
    > > > > is numerical you want to do this sort of thing
    > > > >
    > > > > public sub Main()
    > > > >
    > > > > DoInsert(LookupPageId(), LookupOfficeId())
    > > > >
    > > > > End Sub
    > > > >
    > > > > Public Sub DoInsert(PageId as Long, OfficeId as Long)
    > > > > 'open a connection and fire the insert sql here
    > > > > End sub
    > > > >
    > > > > Private Function LookupPageId() as Long
    > > > > 'open a connection, and executeReader()
    > > > > dim dtr as datareader
    > > > >
    > > > > dtr.read() 'move the head to the first record
    > > > > return dtr.items(0)
    > > > >
    > > > > End Function
    > > > >
    > > > > Private Function LookupOfficeID() as Long
    > > > > 'as above
    > > > > End Function
    > > > >
    > > > >
    > > > > Hope this points you in the right direction
    > > > >
    > > > > Nick
    > > >

    > >
    > >
    > >
     
    Nick Stansbury, Dec 20, 2004
    #6
  7. Nice one!

    You are the man!!! That is exactly what I'm trying to do!

    Cheers

    "Nick Stansbury" wrote:

    > Tim,
    > I see what you mean now - sorry. You want to call DoInsert() at a later
    > date, and want to pass it the value of PageId()
    >
    > Firstly - if you don't call it later then you could just call DoInsert from
    > DGPages_Insert - if it is basically all one process then why not just call
    > DoInsert from DGPages_Insert?
    >
    > Alternatively you have to keep PageId in scope and acceesible from outside
    > of DGPages_Insert. This could be a property on a persisted object, or in
    > session (are you on an asp.net page or in a windows form?) memory. So
    > presuming it is an asp.net page - without trawling through your code - try
    > declaring a private property:
    >
    > private _NewPageID as long
    >
    > Then just allocate it from DGPages_Insert :
    >
    > Me._NewPageID = PageId
    >
    > Then we could call do insert as follows:
    >
    > DoInsert(_NewPageId, LookupOfficeId())
    >
    > - does that answer your question?
    >
    > Nick
    >
    >
    >
    >
    >
    >
    > "Tim::.." <myatix_at_hotmail.com> wrote in message
    > news:...
    > > Hi Nick,
    > >
    > > If you look at the first piece of code I sent you I have already returned
    > > the pageID!
    > > I just don't know how to get this variable from the Public Sub
    > > DGPages_Insert into your sub DoInsert ???
    > >
    > > I would be really grateful if you could answer this and then I will

    > promise
    > > to stop pestering!!!
    > >
    > > Thanks for all you help!
    > >
    > >
    > > ''' CODE
    > > Public Sub DGPages_Insert(ByVal sender As Object, ByVal e As
    > > DataGridCommandEventArgs)
    > > If e.CommandName = "Insert" Then
    > > Dim modDate As String
    > > Dim dtNow As DateTime = DateTime.Now
    > > Dim description As String
    > > Dim txtdescription As TextBox
    > > Dim title As String
    > > Dim txtTitle As TextBox
    > >
    > >
    > > Dim strSQL As String
    > > modDate = dtNow.Date
    > > 'Read in the values of the TextBoxes
    > >
    > >
    > > txtdescription = e.Item.FindControl("add_description")
    > > description = txtdescription.Text
    > > txtTitle = e.Item.FindControl("add_Title")
    > > title = txtTitle.Text
    > >
    > >
    > > 'Create the appropriate SQL statement
    > > Dim Myconn As New
    > > SqlConnection(ConfigurationSettings.AppSettings("strConn"))
    > > Dim cmd As New SqlCommand("PageAdd", Myconn)
    > > cmd.CommandType = CommandType.StoredProcedure
    > >
    > > Myconn.Open()
    > >
    > > ' Adds Information from the insert area within the datagrid

    > into
    > > tblPageContent
    > > ' and returns the record ID
    > > Dim objModDate, objDescription, objTitle, objPageID, objOffice
    > > As SqlParameter
    > > objModDate = cmd.Parameters.Add("@modDate",

    > SqlDbType.DateTime)
    > > objDescription = cmd.Parameters.Add("@description",
    > > SqlDbType.NVarChar)
    > > objTitle = cmd.Parameters.Add("@title", SqlDbType.NVarChar)
    > > objPageID = cmd.Parameters.Add("@PageID", SqlDbType.Int)
    > >
    > > objModDate.Direction = ParameterDirection.Input
    > > objDescription.Direction = ParameterDirection.Input
    > > objTitle.Direction = ParameterDirection.Input
    > > cmd.Parameters("@PageID").Direction =

    > ParameterDirection.Output
    > >
    > > objModDate.Value = modDate
    > > objDescription.Value = description
    > > objTitle.Value = title
    > >
    > >
    > > Dim myReader As SqlDataReader = cmd.ExecuteReader()
    > >
    > > myReader.Read()
    > > myReader.Close()
    > > PageID = cmd.Parameters("@PageID").Value
    > > Myconn.Close()
    > >
    > > 'Rebind the DataGrid
    > > DGPages.EditItemIndex = -1
    > > BindData()
    > > End If
    > >
    > > End Sub
    > >
    > >
    > > "Nick Stansbury" wrote:
    > >
    > > > Tim,
    > > > Sorry this isn't really clear to me. I think what you are saying is

    > that
    > > > LookUpPageId() uses a stored procedure to insert a page and then returns

    > the
    > > > latest ID.
    > > > Firstly : If it returns an id then it should be via a return

    > parameter -
    > > > which is a bit more complex to implement. You have to add parameters to

    > the
    > > > SQLCommand, and specify the direction as OUTPUT.
    > > >
    > > > See:
    > > >

    > http://msdn.microsoft.com/library/d.../vbtskSettingGettingDataCommandParameters.asp
    > > >
    > > > As regards returning the PageID from a separate function - if I
    > > > understand correctly you can assign the value to a local variable...but

    > I'm
    > > > not sure I really understand the problem :
    > > >
    > > > dim NewPageID as long
    > > > NewPageId = LookupPageID()
    > > >
    > > > InsertNewRecord(NewPageID, LookupOfficeId())
    > > >
    > > > Regards,
    > > >
    > > > Nick
    > > >
    > > >
    > > > "Tim::.." <myatix_at_hotmail.com> wrote in message
    > > > news:...
    > > > > Hi Nick,
    > > > >
    > > > > Thanks for the response! I have an issue with LookupPageID as I return

    > the
    > > > > PageID Identity value when data is inserted into that table! How would

    > I
    > > > use
    > > > > a seperate function to return this value when the function doesn't do

    > the
    > > > > insert !
    > > > >
    > > > > Thanks!
    > > > >
    > > > > ''' CODE
    > > > >
    > > > > Private Function LookupPageId() As Long
    > > > > 'open a connection, and executeReader()
    > > > > Dim Myconn As New
    > > > > SqlConnection(ConfigurationSettings.AppSettings("strConn"))
    > > > > Dim cmd As New SqlCommand("SelectOfficeID", Myconn)
    > > > > cmd.CommandType = CommandType.StoredProcedure
    > > > >
    > > > > Myconn.Open()
    > > > >
    > > > >
    > > > > Dim dtr As SqlDataReader = cmd.ExecuteReader()
    > > > >
    > > > > dtr.Read() 'move the head to the first record
    > > > > dtr.Close()
    > > > > Return dtr.Item(0)
    > > > > Myconn.Close()
    > > > >
    > > > > End Function
    > > > >
    > > > > "Nick Stansbury" wrote:
    > > > >
    > > > > > Hi,
    > > > > >
    > > > > > There are quite a few things that you should look at with this

    > code.
    > > > You
    > > > > > seem to use dataset's in almost every case for fetching transient
    > > > records
    > > > > > that you only need once. You can use execute scalar and execute

    > reader
    > > > to
    > > > > > return single records and transient data that you don't need to

    > persist.
    > > > > > Also this line won't ever work : Dim cn As New
    > > > SqlConnection("strConn")
    > > > > > = you are really using the AppSettings collection. Check the code

    > for
    > > > this
    > > > > > sort of error!
    > > > > >
    > > > > > You need to learn some basic programming skills before embarking

    > on
    > > > this
    > > > > > sort of a project. Your basic mistake is returning a dataset from

    > each
    > > > of
    > > > > > your ID lookup functions rather than the value itself. Presuming

    > that
    > > > the ID
    > > > > > is numerical you want to do this sort of thing
    > > > > >
    > > > > > public sub Main()
    > > > > >
    > > > > > DoInsert(LookupPageId(), LookupOfficeId())
    > > > > >
    > > > > > End Sub
    > > > > >
    > > > > > Public Sub DoInsert(PageId as Long, OfficeId as Long)
    > > > > > 'open a connection and fire the insert sql here
    > > > > > End sub
    > > > > >
    > > > > > Private Function LookupPageId() as Long
    > > > > > 'open a connection, and executeReader()
    > > > > > dim dtr as datareader
    > > > > >
    > > > > > dtr.read() 'move the head to the first record
    > > > > > return dtr.items(0)
    > > > > >
    > > > > > End Function
    > > > > >
    > > > > > Private Function LookupOfficeID() as Long
    > > > > > 'as above
    > > > > > End Function
    > > > > >
    > > > > >
    > > > > > Hope this points you in the right direction
    > > > > >
    > > > > > Nick
    > > > >
    > > >
    > > >
    > > >

    >
    >
    >
     
    =?Utf-8?B?VGltOjouLg==?=, Dec 21, 2004
    #7
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. William Gower

    ItemTemplate error driving me crazy

    William Gower, Apr 23, 2004, in forum: ASP .Net
    Replies:
    0
    Views:
    331
    William Gower
    Apr 23, 2004
  2. Shapper
    Replies:
    0
    Views:
    341
    Shapper
    Apr 29, 2005
  3. Joe Blanchard via .NET 247
    Replies:
    1
    Views:
    775
    Eliyahu Goldin
    May 15, 2005
  4. Replies:
    1
    Views:
    496
    Cowboy \(Gregory A. Beamer\)
    Dec 5, 2005
  5. papaja
    Replies:
    0
    Views:
    445
    papaja
    Jul 13, 2006
Loading...

Share This Page