Insert and Variables driving me crazy! Newbie...

[Guest]

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]

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

 

[Guest]

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]

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

 

[Guest]

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]

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

 

[Guest]

Nice one!

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

Cheers