Hey everyone,
I'm using CTEs to try and get the totals of two different criteria queries that I want to group by Month depending on the associated Inquiry Date. Here is what I have right now;
;With CTE(total, InitDate) as
(
SELECT count(Inquiry.ID), Inquiry.Date from Inquiry
Inner Join Inquirer on Inquirer.ID = Inquiry.InquirerID_fk
Left Join Transfer on Transfer.TransferInquiryID_fk = Inquiry.ID
WHERE (Inquiry.Date >= '3/1/2012' AND Inquiry.Date <= '9/26/2012' AND Inquiry.Date IS NOT NULL)
AND (Inquirer.Program = 'Res. Referral Coord.')AND TransferInquiryID_fk IS NULL
Group By Inquiry.Date
)
,CTE2(total, TransDate) as
(
SELECT count(Inquiry.ID), Inquiry.Date from Inquiry
Inner Join Inquirer on Inquirer.ID = Inquiry.InquirerID_fk
Left Join Transfer on Transfer.TransferInquiryID_fk = Inquiry.ID
WHERE (Inquiry.Date >= '3/1/2012' AND Inquiry.Date <= '9/26/2012' AND Inquiry.Date IS NOT NULL)
AND (Inquirer.Program = 'Res. Referral Coord.') AND TransferInquiryID_fk IS NOT NULL
Group By Inquiry.Date
)
SELECT A.InitDate, B.TransDate, Sum(A.total) As InitCount, Sum(ISNULL(B.total,0)) As TransCount from CTE A
left outer Join CTE2 B on A.InitDate = B.TransDate
Group By Month(A.InitDate), A.InitDate, B.TransDate
CTE finds the initial inquiries for the respective date range, and CTE2 finds what inquiries are transfers, the problem is that when running the queries individuall CTE has a total of 170, and CTE2 has a total of 26, but when I run the SELECT Statement with them
SELECT A.InitDate, B.TransDate, Sum(A.total) As InitCount, Sum(ISNULL(B.total,0)) As TransCount from CTE A
left outer Join CTE2 B on A.InitDate = B.TransDate
Group By Month(A.InitDate), A.InitDate, B.TransDate
I get 170 for InitCount, but for TransCount I only get 19, not 26. I assume it is with my left outer join statement grouping but I am not sure how I would change this to get the proper counts. All I want to do is group the values together depending on the month they were done in.
Thanks for any help,
NickG
I tried doing that as well, I should have posted it with the DISTINCT, the counts still remain different by 1.
Thanks for your quick suggestion though
Hey everyone, I have the following two queries, and want to know why the first one would return a count of 52, but the second would return a count of 51 when technically they should be the same;
SELECT count(*) from Inquiry
Inner Join NoDecision on Inquiry.ID = NoDecision.InquiryID_fk
Inner Join Inquirer on Inquirer.ID = Inquiry.InquirerID_fk
WHERE (Inquiry.DecisionMade = 'No decision yet') AND
(Inquiry.Date >= '4/1/2012' AND Inquiry.Date <= '9/17/2012') AND
Inquirer.Program IN('Appleton','Brook','Fernside','Gunderson','Lincoln','Pavilion')
SELECT count(*) from Inquiry
Inner Join Inquirer on Inquirer.ID = Inquiry.InquirerID_fk
WHERE (DecisionMade = 'No decision yet') AND
(Inquiry.Date >= '4/1/2012' AND Inquiry.Date <= '9/17/2012') AND
Inquirer.Program IN('Appleton','Brook','Fernside','Gunderson','Lincoln','Pavilion')
I understand that I am joining the NoDecision table, but I don't understand why that would increase the count by 1 in the first query.
Any help would be appreciated.
Thanks,
NickG
Hey kk,
Thank you for your suggestion, that actually is working almost exactly how I want it to. The one issue I am having is with the
When Grouping(Month(A.InitDate)) = 1 Then ''
If i'm correct with reading the resultset that I can that basically totals each individual month and puts it into a row that looks like this;
InitDate InitCount TransCount
NULL 25 3
Where 25 is the total of InitCount for the month and 3 is the total of TransCount which is correct in the totalling, it's just that when I try to use this query in Report Builder, it considers the NULL a date value which defaults to 01-01-1900 in SQL Server. Do you know any way to work around that?
Thank you very much for all your help, I wouldn't be able to get this done without you,
Nick G
Thanks for your suggestion, I tried that and still receive the same error as above. If I take out the "with rollup" than it let's the query be successful, but as I mentioned it only allows the second query to return results that have the exact same date as the first query when ultimately I just want to group them by month.
Hey Everyone,
I am using the below query to try and group together values by the month of the date selected. the problem is that sometimes CTE2 will not have the same dates as CTE and those values end up omitted. Is there anyway I can just group these on a different parameter, or just on the month of the date?
Any help would be great.
;With CTE(total, InitDate) as
(
SELECT count(Inquiry.ID) as total, Inquiry.Date as InqDate from Inquiry
Inner Join Inquirer on Inquirer.ID = Inquiry.InquirerID_fk
Left Join Transfer on Transfer.TransferInquiryID_fk = Inquiry.ID
WHERE (Inquiry.Date >='6/1/2012' AND Inquiry.Date <= '9/6/2012' AND Inquiry.Date IS NOT NULL)
AND (Inquirer.Program = 'Res. Referral Coord.')AND TransferInquiryID_fk IS NULL
Group By Inquiry.Date
)
,CTE2(total, TransDate) as
(
SELECT count(Inquiry.ID) as total, Inquiry.Date as InqDate from Inquiry
Inner Join Inquirer on Inquirer.ID = Inquiry.InquirerID_fk
Left Join Transfer on Transfer.TransferInquiryID_fk = Inquiry.ID
WHERE (Inquiry.Date >='6/1/2012' AND Inquiry.Date <= '9/6/2012' AND Inquiry.Date IS NOT NULL)
AND (Inquirer.Program = 'Res. Referral Coord.') AND TransferInquiryID_fk IS NOT NULL
Group By Inquiry.Date
)
SELECT CASE
When Grouping(A.InitDate) = 1 THEN 'Date'
When Grouping(A.InitDate) = 1 THEN ''
ELSE A.InitDate
End As 'InitDate'
,Sum(A.total) As InitCount, Sum(ISNULL(B.total,0)) As TransCount from CTE A
left outer Join CTE2 B on A.InitDate = B.TransDate
group by A.InitDate with rollup
Thank you,
NickG
sorry for the formatting issues it took me 15 minutes just to get this to post, I don't understand why DaniWeb made such a ridiculous way to try to post code and queries.
Hey everyone,
I am looking to write a query that I think can be solved a nested select but I am not yet that fluent in MySQL to be able to get the query working correctly.
Basically what I want to do is this;
SELECT count(*) from Inquiry
Inner Join Inquirer on Inquirer.ID = Inquiry.InquirerID_fk
WHERE Inquirer.Program = 'value' AND Inquiry.ID
does not exist in (SELECT TransferInquiryID_fk from Transfer)
as you can see I want all results where the Inquiry.ID of the record is not contained in the list of TransferInquiryID's
If anyone could help me out with this that would be great.
Thank you very much,
NickG
Hey everyone,
Basically everything im trying to do is summed up in the subject line. I have a "DatePicker" control on my site, that when a date is selected, is displayed in the textbox as "April 12, 2012" or whatever the subsequent date may be. However, when I save this into my DB in a "Date" field, it saves in the format of "2012-04-12" which is fine with me, I just need to know the function in order to compare the DB value to a value in the DateSelector textbox. This is for a system I am building in case a user wants to "edit" a previously stored record, in case they change the subsequent date.
I have tried using
DateTime.Parse(PlanAdmit).ToString("YYYY-mm-dd")
but get the error of;
Conversion from string "YYYY-00-16" to type 'Date' is not valid.
When the date selected was April, 16 2012
Any help is much appreciated.
Thank you for your time,
NickG
The exception is being thrown here;
If (DecisionDate.Text = "") Then
DecDateMade = Nothing <-------
Else
DecDateMade = Convert.ToDateTime(DecisionDate.Text)
End If
and I have tried using DBNull as well as System.DbNull.Value, as well as just empty double quotes "", converted to a string, and tried. been trying everything I can really think of. Also, tried putting condition into the stored procedure like
.parameters.add(@DecDate, sqldbtype.date)
If DecDateMade.text = "" then
.paramater(@DecDate).value = DBNull
else
.parameter(@DecDate).value = Convert.toDateTime(DecDateMade.Text)
end if
I know that might not be perfectly correct syntax, I was just giving an example.
Thanks for taking a look
Hey everyone,
I know that there are hundreds of articles online about this same error, but I have been going at this for almost 2 days and it's time I just started a thread of my own.
In my application, I have a textbox that has a CalendarExtender hooked up to it as shown here;
<asp:TextBox ID="ActualAdmitDate" runat="server"></asp:TextBox>
<asp:CalendarExtender ID="Calendarextender2" runat="server" CssClass="calStyle"
Enabled="True" Format="MMMM d, yyyy" PopupButtonID="calImg3"
TargetControlID="ActualAdmitDate">
</asp:CalendarExtender><img id="calImg3" alt="" class="style10" src="Styles/calendar.png" />I also have more textboxes and controls that take information, but this issue is only happening with the date selections.
Basically, it's possible that a user doesn't select a date out of this CalendarExtender textbox, leaving the value as ""
I am trying to figure out how I can either store a Null value or "" in my database if the user doesn't select a date in the CalendarExtender. Here is the code I am currently using, along with the stored procedure that the values are passed into. Here is one condition of the code being used
The DecDateMade variable is the variable that is throwing the error.
Dim DecMadeBy As String
Dim DecDateMade As Date
Dim DecTimeMade As String
Dim ContID As String
Dim ContText As String
Dim ReferElsewhere As String
Dim ReferLocation As String
If B4A.Checked = True Then
ReferElsewhere = "Yes"
If B4McLean.SelectedIndex <> 0 Then
ReferLocation = B4McLean.SelectedValue
Else
ReferLocation = B4Else.Text
End If
ElseIf B4B.Checked = True Then
ReferElsewhere = "No"
ReferLocation = "N/A"
End If
If CheckBoxList1.SelectedValue = "" And CheckBoxList2.SelectedValue = "" And PatNoCriteria.Checked = False And ClosedOther.Checked = False Then
DecMadeBy = MadeBy.Text
[B] If (DecisionDate.Text = "") Then
DecDateMade = Nothing
Else
DecDateMade = Convert.ToDateTime(DecisionDate.Text)
End If[/B]
If (DecisionTime.Text.ToString = "") Then
DecTimeMade = "N/A"
Else
DecTimeMade = DecisionTime.Text
End If
ContID = ""
ContText = ""
AdmDecNoAdmit(DecDateMade, DecTimeMade, DecMadeBy, ContID, ContText, ReferElsewhere, ReferLocation)
End IfStored Procedure:
Protected Sub AdmDecNoAdmit(ByVal DecDateMade As Date, ByVal DecTimeMade As String, ByVal NoAdmDecMadeBy As String, ByVal Cont As String, ByVal ContText As String, ByVal ReferElsewhere As String, ByVal ReferLocation As String)
Dim strSQL As String = "INSERT into NoAdmit(InquiryID_fk,ContID, ContText, ReferElsewhere, ReferLocation, MadeBy, DecisionDate, DecisionTime)VALUES(@InquiryID, @ContID, @ContText, @ReferElsewhere, @ReferLocation, @MadeBy, @DecDateMade, @DecTimeMade)SELECT ID = SCOPE_IDENTITY()"
Dim myConn As New SqlConnection(StringConnection)
Dim queryCommand As New SqlCommand
queryCommand.Connection = myConn
With queryCommand
.CommandType = CommandType.Text
.CommandText = strSQL
.Parameters.AddWithValue("@InquiryID", Session("NewInquiryID"))
.Parameters.AddWithValue("@ContID", Cont)
.Parameters.AddWithValue("@ContText", ContText)
.Parameters.AddWithValue("@ReferElsewhere", ReferElsewhere)
.Parameters.AddWithValue("@ReferLocation", ReferLocation)
.Parameters.AddWithValue("@MadeBy", NoAdmDecMadeBy)
.Parameters.AddWithValue("@DecDateMade", DecDateMade)
.Parameters.AddWithValue("@DecTimeMade", DecTimeMade)
myConn.Open()
Session("DecID") = .ExecuteScalar()
Session("DecID") = Convert.ToInt32(Session("DecID"))
'MsgBox(Session("DecID"), MsgBoxStyle.Critical)
myConn.Close()
End With
queryCommand.Dispose()
Dim UpdSQL As String = "Update Inquiry SET DecisionMade = 'Do Not Admit', DecisionID='" & Session("DecID") & "' WHERE Inquiry.ID = '" & Session("NewInquiryID") & "'"
Dim UpdateCommand As New SqlCommand
UpdateCommand.Connection = myConn
With UpdateCommand
.CommandType = CommandType.Text
.CommandText = UpdSQL
myConn.Open()
.ExecuteNonQuery()
myConn.Close()
End With
myConn.Dispose()
UpdateCommand.Dispose()
End SubHey everyone,
I am lost on the exception that I have getting thrown at me right now.
SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.
What I am trying to do on my site, which is Windows Authenticated on an internal network, is capture the users ID that is logged in, and then depending on which page(s) the user clicks, store them in a database.
This is the code to check if the users ID has been set, and to add the entry into the database via a parameterized query.
Initial Setting of Session for the UsersID
Dim LoggedIn As String = User.Identity.Name
If LoggedIn.Contains("PARTNERS") Then
Session("LoggedIn") = LoggedIn.Substring(9)
Else
Session("LoggedIn") = LoggedIn
End IfAfterwards the user can either go into an "Edit.aspx" page or "View.aspx" page.
Depending on which page they choose, I want to store that in a database that I have called tblView. HEre is the structure of the table.
ID : Integer : Primary Key, auto-incremented
InquiryID_fk : Integer : Foreign Key; the inquiry id that is being viewed or edited
Page : Varchar(10) : Will store either "View" or "Edit" depending on the page entered
User : varchar(10) : Stores the userID : which is a max of 6 characters
Date : Date : Want to store the system date
Time : Time(7) : want to store the system time
Here is the code in the "View" page to store into the db, which throws the error
Dim InquiryID As String = Request.QueryString("id")
Dim InquirySQL As String
Session("InquiryID") = InquiryID
If Not Session("LoggedIn") Is Nothing Then
Dim ViewSQL As String
ViewSQL = "Insert into tblView(InquiryID_fk, Page, User, Date, Time)Values(@InqID, @Page, @User, @ActDate, @ActTime)"
Dim querycommand As New SqlCommand
querycommand.Connection = Conn
With querycommand
.CommandType = CommandType.Text
.CommandText = ViewSQL
.Parameters.AddWithValue("@InqID", Session("InquiryID"))
.Parameters.AddWithValue("@Page", "View")
.Parameters.AddWithValue("@User", Session("LoggedIn"))
.Parameters.AddWithValue("@ActDate", Date.Today)
.Parameters.AddWithValue("@ActTime", TimeOfDay)
Conn.Open()
.ExecuteScalar()
Conn.Close()
End With
querycommand.Dispose()
End IfWhen I debug and put break points throughout the code, as well as after the error is thrown and i just highlight over the "Date.Today" or "TimeOfDay" variables, they are displaying the proper date and time from my machine. Does this mean they are out of sync with my server, running SQL Server 2008?
Any help would be excellent, this has been pretty frustrating.
Thank you in advance,
NickG
Hey everybody,
I've been scouring the internet and forums for days now trying to solve this issue, that seems to me like it should be pretty simple. Basically I have a multi-step form with multiple tabs in a TabContainer. There are a couple radiobuttonlists that perform autopostbacks, and I want to let the users focus go back on the radiobuttonlist so that they aren't sent back to the top of the page and lose their page state.
I have tried all of the usual stuff using SetFocus() on a control, or a certain item in the radiobuttonlist. Have used the .focus() command on every control as well as HiddenFields, and textboxes with visible="false" just to try to get the user back to that area of the page. Have also tried to set the focus to a button, all to no avail.
I'm beginning to think this is because they are located inside of a TabContainer and TabPanel, which still seems a little ridiculous to me. Everything I read keeps saying TrackFocus() has been implemented since .Net 2.0 but there is no documentation or implementation work for it anywhere. Does anyone have a suggestion for me how to approach this. I'll put up a little bit of code but basically it's in the Page_Load()
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If Not IsPostBack Then
TabContainer1.ActiveTabIndex = 0
Else
If radInqRel.SelectedIndex = 3 Then
ModalPopupExtender1.Show()
ElseIf radInqRel.SelectedIndex = 2 Then
MoreInfo.Visible = False
End If
txtFocus.Focus()
End If
End SubThat is basically what I am trying to do, if it's a postback then set the focus to a control. I'd prefer to use the TrackFocus() to be sure that the postback goes right back to the proper place on the page, but just getting the user to the vicinity will suffice if need be. I'm not very fluent in javascript, even though most suggest using it, I just assumed that by now Microsoft would have this implemented properly. Seems like a simple thing, but certainly isn't. Thanks for any help in advance.
NickG
Here's the radiobuttonlist code;
<asp:Panel runat="server">
<asp:RadioButtonList ID="radInqRel" OnSelectedIndexChanged="radInqRel_SelectedIndexChanged" runat="server" RepeatColumns="2"
Width="500px" CssClass="style66" AutoPostBack="True">
<asp:ListItem>a. Patient - Self</asp:ListItem>
<asp:ListItem>b. Family / Friend</asp:ListItem>
<asp:ListItem>c. Clinician, McLean</asp:ListItem>
<asp:ListItem>d. Clinician, outside McLean</asp:ListItem>
<asp:ListItem>e. Other (e.g. Court, lawyer)</asp:ListItem>
</asp:RadioButtonList>
<asp:TextBox ID="txtFocus" runat="server" Visible = "False"></asp:TextBox><asp:HiddenField ID="HiddenField1" runat="server" />
</asp:Panel>
Hey everyone, I'm going to do my best to explain this easily, even though the code might not be the easiest to follow.
I have an "Edit" form made out of a TabContainer and TabPanels that I am populating with results from a few different datasets, after a user clicks the "Next" or "Submit" buttons on the form, I iterate through the control set of that TabPanel, and compare them to the original values, if they are different than I store that in an ArrayList and later sieve through it to add records to the Update Database Table.
I have this functionality working for all the controls inside a TabPanel that are not enclosed in another "Panel" control on the site. The code below is what I am using to jump into the panel inside a TabPanel that contains only checkboxes and textboxes, which are directly related to one another (AKA if a user clicks the checkbox, it enables the textbox for text entry).
What I am trying to do is loop through these checkbox controls and then compare them to what is already in the database, if the record exists in the database (AKA the checkbox is already checked) than compare the textbox values, and update if the values are different.
If a new checkbox has been checked (AKA checkbox text value doesn't exist in the database) I would like to store the new value in an arrayList and later iterate through it and store that in a subsequent table, which I have already coded.
My problem is that when I iterate through the controls, if the control is checked, I store the text value of the new control that has been checked to be compared to the DataSet results.
What I want to do is basically, if the new checkbox text isn't apparent in the dataset, than store that information as a New addition and later i will purge the arrayList and store all of the info.
The problem I am having is that once I am in the control loop and access the loop through the dataset, the code will compare the Control to every record in the DataSet result, even if it has already discovered that it is stored in the Database.
I have elaborated even more in comments within my code below.
For Each innerctrl As Object In c.Controls
Dim id As String = innerctrl.ID
Dim ctrlValue As String
Dim ctrlDesc As String
'If the control is a panel, than it contains other controls, mainly checkboxes and textbox, check through that control and store these results as well
If TypeOf innerctrl is Panel Then
'If the control inside the tabpanel is a "Panel" then continue
Dim pnlControls As Control = TryCast(innerctrl, Control)
'use variable pnlControls as a reference to the controls inside this panel
For Each pnlCtrl As Control In pnlControls.Controls
'for each object/control inside this panel control set do the followin
id = Nothing
id = pnlCtrl.ID
MsgBox(id)
'clear the id variable to be filled depending on control type
If TypeOf pnlCtrl Is CheckBox Then
'If control is a checkbox, which most likely is
If DirectCast(pnlCtrl, CheckBox).Checked = True Then
'If the checkbox is checked inside the panel
Dim idText As String = DirectCast(pnlCtrl, CheckBox).Text
Dim txtValue As String
'Set the id equal to the text of the checkbox such as "Present Suicide Ideation"
Select Case idText
'Check for the different possible values of id
Case "Harm to Others"
txtValue = OthersHarmText.Text
Case "Present Suicide Ideation"
txtValue = PresSuiText.Text
Case "History of Suicide Attempts"
txtValue = SuiHistText.Text
Case "Self-Harm"
txtValue = SelfHarmText.Text
Case "Present Eating Disorder"
txtValue = EatDisText.Text
Case "Fire-Setting"
txtValue = FireStartText.Text
Case "Access to Firearms"
txtValue = GunAccessText.Text
End Select
'After seeing the control was a checkbox and storing the coinciding textbox value check against values in db
'Here are where my issues begin.......
'Basically if idText = "Harm to Others" and the first Row Value = "Harm to Others" and the txtValue = DBCheckVal (the textbox value is the same now as what is stored in the db)
'then I want to jump back into the control loop until the next Checkbox that is checked is found, but the DataSet continues to be looped through before jumping back into the Controls.
'How could I fix that?
Dim dt As System.Data.DataTable
dt = Session("CopySafety").tables(0)
If idText IsNot Nothing Then
For i = 0 To dt.Rows.Count - 1
'if id contains a value
'while iterating through the original data compare values from DB to values submitted
Dim DBCheckVal As String = dt.Rows(i).Item("SafChecked").ToString
Dim DBTextVal As String = dt.Rows(i).Item("SafText").ToString
If idText = DBCheckVal Then
'if the id stored from submitted control equals ID in the database
If txtValue <> DBTextVal Then
'If value in the textbox not equal to value in the database change occurred
_UpdateItemsList.Add("Changed " & idText & " From " & DBTextVal & " to " & txtValue)
End If
Else
'if id is not equal to the stored id, than it is a new insert and update
_UpdateItemsList.Add("Added " & idText & " : " & txtValue)
MsgBox("Added " & idText & " : " & txtValue)
End If
DBCheckVal = Nothing
DBTextVal = Nothing
Next
End If
End If
End If
Next
'After this isn't involved in where I am having the problems, all the basic controls are working great.
ElseIf TypeOf innerctrl Is TextBox Then
ctrlValue = DirectCast(innerctrl, TextBox).Text
ctrlDesc = DirectCast(innerctrl, TextBox).ToolTip
ElseIf TypeOf innerctrl Is CheckBox Then
ctrlValue = DirectCast(innerctrl, CheckBox).Text
ctrlDesc = DirectCast(innerctrl, CheckBox).ToolTip
ElseIf TypeOf innerctrl Is DropDownList Then
ctrlValue = DirectCast(innerctrl, DropDownList).SelectedValue
ctrlDesc = DirectCast(innerctrl, DropDownList).ToolTip
ElseIf TypeOf innerctrl Is RadioButtonList Then
ctrlValue = DirectCast(innerctrl, RadioButtonList).SelectedValue
ctrlDesc = DirectCast(innerctrl, RadioButtonList).ToolTip
End If
If id IsNot Nothing And ctrlValue IsNot Nothing Then
Dim dt As System.Data.DataTable
dt = Session("CopyDS").Tables(0)
For i = 0 To dt.Rows(0).ItemArray.Count - 1
Dim ColName As String = dt.Columns(i).ToString
Dim RowVal As String = dt.Rows(0).Item(i).ToString
'copyData.Text += dt.Columns(i).ToString & " : "
'copyData.Text += dt.Rows(0).Item(i).ToString & "<br/>"
If ColName = id Then
'MsgBox("Colname and ID are equal to : " & ColName)
If RowVal <> ctrlValue Then
_UpdateItemsList.Add("Changed " & ctrlDesc & " From " & RowVal & " to " & ctrlValue)
MsgBox("Changed " & ctrlDesc & " from " & RowVal & " to " & ctrlValue)
End If
End If
ColName = Nothing
RowVal = Nothing
Next
End If
ctrlValue = Nothing
ctrlDesc = Nothing
NextHey everyone, my issue is a little strange, I have a form that has checkboxes, and depending on the selection, a textbox might appear for further info. I want to loop through the controls that have been selected, if it's only a checkbox than just add the Value of the checkbox, if there is also a textbox associated, add the text to the database as well. I keep getting the error that @chkText either has already been set, or that i have to declare the scalar variable @chkText. I dont know if this is even possible or if my approach is completely wrong, but any help is appreciated.
Also, I dont know if this will help, but when I comment out all of the database stuff, my message boxes that i have set up for testing work perfectly. They iterate through and they pop-up the Checkbox value, followed by corresponding textbox values in the exact right order;
Here is the code with the SQL stuff commented out;
Protected Sub Button7_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button7.Click
InsertPatientInfo.Insert()
InsInqInfo.Insert()
InsOtherInfo.Insert()
InsClinInfo.Insert()
InsInquiryInfo.Insert()
'Dim myConn As New SqlConnection("Data Source=W0121968;Initial Catalog=ResInquiryDev;Integrated Security=True;Pooling=False")
'Dim queryCommand As SqlCommand = myConn.CreateCommand()
'myConn.Open()
'queryCommand.CommandText = "INSERT into tblReferral(RefInquiryID, RefSource, RefSourceText)VALUES(@InquiryID, @chkText, @txtText)"
'queryCommand.Parameters.Add("@InquiryID", SqlDbType.Int)
'queryCommand.Parameters("@InquiryID").Value = Convert.ToInt32(Session("NewInquiryID"))
For Each ctrl As Control In Panel7.Controls
If TypeOf ctrl Is CheckBox Then
If CType(ctrl, CheckBox).Checked = True Then
For i = 0 To ctrl.Controls.Count
Dim str As String
str = DirectCast(ctrl, CheckBox).Text
MsgBox(str, MsgBoxStyle.Critical)
'queryCommand.Parameters.Add("@chkText", SqlDbType.VarChar)
'queryCommand.Parameters("@chkText").Value = str.ToString
Next
End If
End If
If TypeOf ctrl Is TextBox Then
If CType(ctrl, TextBox).Text <> "" Then
For i = 0 To ctrl.Controls.Count
Dim txtstring As String
txtstring = DirectCast(ctrl, TextBox).Text
MsgBox(txtstring, MsgBoxStyle.Critical)
'queryCommand.Parameters.Add("@txtText", SqlDbType.VarChar)
'queryCommand.Parameters("@txtText").Value = txtstring.ToString
Next
End If
End If
'queryCommand.ExecuteNonQuery()
Next
'myConn.Close()
MultiView1.ActiveViewIndex = 4
End SubHello everyone,
I am making a set of tables that have relational keys associated with them like an order system. Basically I am trying to store the Customer Info, then the order and so on and so forth, but I need to store the "customer" ID in the Order table in a relational field, I thought I could accomplish this using Scope_identity() but apparently I can't figure out how to do it correctly. I will include my code with a stepthrough of the process and any help would be great.
Thanks in advance,
Nick G
First I insert the into into tblPatInfo, which works correctly;
<asp:SqlDataSource ID="InsertPatInfo" runat="server" ConnectionString="<%$ ConnectionStrings:DataConnectionString %>"
providername="<%$ ConnectionStrings:DataConnectionString.ProviderName %>"
InsertCommand = "Insert into tblPatInfo(PatName, PatAge, PatState, PatCountry, PatPhone, PatCell) VALUES
(@PatName, @PatAge, @PatState, @PatCountry, @PatPhone, @PatCell); SET @NewPatID = SCOPE_IDENTITY()">
<InsertParameters>
<asp:ControlParameter ControlID = "PatInfoName" Name="PatName" PropertyName="text"/>
<asp:ControlParameter ControlID = "PatInfoAge" Name="PatAge" PropertyName="text" />
<asp:ControlParameter ControlID = "PatInfoState" Name="PatState" PropertyName="selectedvalue" />
<asp:ControlParameter ControlID = "PatInfoCountry" Name="PatCountry" PropertyName="selectedvalue" />
<asp:ControlParameter ControlID = "PatInfoPhone" Name="PatPhone" PropertyName = "text" />
<asp:ControlParameter ControlID = "PatInfoCell" Name="PatCell" PropertyName="Text" />
<asp:Parameter DbType="Int32" Direction="Output" Name="NewPatID" />
</InsertParameters>I then use the event_handler for the inserted information to assign the NewPatID variable.
Protected Sub InsertPatInfo_Inserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles InsertPatInfo.Inserted, InsInqInfo.Inserted
Dim NewPatID As Integer = e.Command.Parameters("@NewPatID").Value
End SubI then try to use this variable in the next insert statement in a different SqlDataSource;
<asp:SqlDataSource ID="InsInqInfo" runat="server" ConnectionString="<%$ ConnectionStrings:DataConnectionString %>"
providerName="<%$ ConnectionStrings:DataConnectionString.ProviderName %>"
InsertCommand = "Insert into tblInquirer(InqPatID, InqName, InqState, InqCountry, InqPhone, InqRelation, InqVia, InqCareLevel, InqProgram) VALUES
(@NewPatID, @InqName, @InqState, @InqCountry, @InqPhone, @InqRelation, @InqVia, @InqCareLevel, @InqProgram)">
<InsertParameters>
<asp:ControlParameter ControlID="NewPatID" Name="NewPatID" PropertyName="value" />
<asp:ControlParameter ControlID = "InqName" Name="InqName" PropertyName="text"/>
<asp:ControlParameter ControlID = "InqStateList" Name="InqState" PropertyName="selectedvalue" />
<asp:ControlParameter ControlID = "InqCountry" Name="InqCountry" PropertyName="selectedvalue" />
<asp:ControlParameter ControlID = "InqPhone" Name="InqPhone" PropertyName="Text" />
<asp:ControlParameter ControlID = "radInqRel" Name="InqRelation" PropertyName="selectedvalue" />
<asp:ControlParameter ControlID = "InitInqVia" Name="InqVia" PropertyName = "selectedvalue" />
<asp:ControlParameter ControlID = "CareLevel" Name="InqCareLevel" PropertyName="selectedvalue" />
<asp:ControlParameter ControlID = "ProgSelect" Name="InqProgram" PropertyName="selectedvalue" />
</InsertParameters>
</asp:SqlDataSource>However, I keep receiving the error that @NewPatID isn't set and I just don't know where to go from here, I have been digging for days now, and still no luck.
Here is my button click_event call for these datasources, as well as more that I currently am not executing but will also use the @NewPatID variable as well as the scope_identity() I will pull from the Insert into tblInquirer once I figure out how to do it correctly. Thank you for taking the time to look through this, anything pointing me in the right direction will be great.
Protected Sub Button7_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button7.Click
InsertPatInfo.Insert()
'InsInquiryInfo.Insert()
'InsClinInfo.Insert()
InsInqInfo.Insert()
MultiView1.ActiveViewIndex = 4
End SubI don't think that i'm getting the PatID value either, that is what my question is, what about my code is wrong that I wouldn't be returning the value? Even if it didn't return the most recent row I have other data that should be returning a value......?
Hey everyone,
What i'm doing is building a step through form that will basically store relational keys between databases. I begin by storing Information about the individual asking about the program, I then want to query the table this was stored in to get the Unique ID that is being associated with that entry, and use that ID in an InsertCommand statement for the next table information is being inserted to. Here is my code;
Here are the two Query calls;
InsertPatInfo.Insert()
InsInqInfo.Insert()
Here are the corresponding SqlDataSource events;
InsertPatInfo:
<asp:SqlDataSource ID="InsertPatInfo" runat="server" ConnectionString="<%$ ConnectionStrings:DataConnectionString %>"
providername="<%$ ConnectionStrings:DataConnectionString.ProviderName %>"
InsertCommand = "Insert into tblPatInfo(PatName, PatAge, PatState, PatCountry, PatPhone, PatCell) VALUES
(@PatName, @PatAge, @PatState, @PatCountry, @PatPhone, @PatCell)">
<InsertParameters>
<asp:ControlParameter ControlID = "PatInfoName" Name="PatName" PropertyName="text"/>
<asp:ControlParameter ControlID = "PatInfoAge" Name="PatAge" PropertyName="text" />
<asp:ControlParameter ControlID = "PatInfoState" Name="PatState" PropertyName="selectedvalue" />
<asp:ControlParameter ControlID = "PatInfoCountry" Name="PatCountry" PropertyName="selectedvalue" />
<asp:ControlParameter ControlID = "PatInfoPhone" Name="PatPhone" PropertyName = "text" />
<asp:ControlParameter ControlID = "PatInfoCell" Name="PatCell" PropertyName="Text" />
</InsertParameters>
</asp:SqlDataSource>InsInqInfo:
<asp:SqlDataSource ID="InsInqInfo" runat="server" ConnectionString="<%$ ConnectionStrings:DataConnectionString %>"
providerName="<%$ ConnectionStrings:DataConnectionString.ProviderName %>"
SelectCommand = "SELECT TOP 1 PatId from tblPatInfo OrderBy PatID desc"
InsertCommand = "Insert into tblInquirer(InqPatID, InqName, InqState, InqCountry, InqPhone, InqRelation, InqVia, InqCareLevel, InqProgram) VALUES
(@PatID, @InqName, @InqState, @InqCountry, @InqPhone, @InqRelation, @InqVia, @InqCareLevel, @InqProgram)">
<SelectParameters>
<asp:QueryStringParameter Name="PatID" QueryStringField="PatId" Type="String" />
</SelectParameters>
<InsertParameters>
<asp:ControlParameter ControlID = "InqName" Name="InqName" PropertyName="text"/>
<asp:ControlParameter ControlID = "InqStateList" Name="InqState" PropertyName="selectedvalue" />
<asp:ControlParameter ControlID = "InqCountry" Name="InqCountry" PropertyName="selectedvalue" />
<asp:ControlParameter ControlID = "InqPhone" Name="InqPhone" PropertyName="Text" />
<asp:ControlParameter ControlID = "radInqRel" Name="InqRelation" PropertyName="selectedvalue" />
<asp:ControlParameter ControlID = "InitInqVia" Name="InqVia" PropertyName = "selectedvalue" />
<asp:ControlParameter ControlID = "CareLevel" Name="InqCareLevel" PropertyName="selectedvalue" />
<asp:ControlParameter ControlID = "ProgSelect" Name="InqProgram" PropertyName="selectedvalue" />
</InsertParameters>
</asp:SqlDataSource>I'm basically just receiving the error that there is no value stored in the PatID variable for the second insert statement. Other than that all of the data is being stored properly, just don't think the query is working correctly to get the tblPatInfo.PatID descending.
Any help would be great.
Thank you,
NickG
I figured it out, I didn't realize that you weren't supposed to declare the Auto-Increment primary keys, i've been coding PHP for too long, and used the wrong syntax
Hey everyone,
I'm fairly new to .Net and I am just starting to write some queries to begin using my database files, and I keep running into these errors. I have stepped through numerous tutorials and reference guides and still cannot seem to get things working properly. Basically I have a very basic database table im trying to store some information in using the follow code. I will show the web.config code for my connection as well as the query itself and where the query is getting called. Please let me know if anyone can be of assistance.
Thank you,
Nick G
Web.Config
<connectionStrings>
<add name="ApplicationServices" connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\aspnetdb.mdf;User Instance=true"
providerName="System.Data.SqlClient" />
<add name="DataConnectionString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|DevInquiry.accdb;"
providerName="System.Data.OleDb"/>
</connectionStrings>SqlDataSource Declaration;
I placed this inside the <asp:content tags, at the bottom of the page, I dont know if this is correct.
<asp:SqlDataSource ID="InsertPatInfo" runat="server" ConnectionString="<%$ ConnectionStrings:DataConnectionString %>"
providername="<%$ ConnectionStrings:DataConnectionString.ProviderName %>"
InsertCommand = "Insert into tblPatInfo(PatID, PatName, PatAge, PatState, PatCountry, PatPhone, PatCell) VALUES
('',@PatName, @PatAge, @PatState, @PatCountry, @PatPhone, @PatCell)">
<InsertParameters>
<asp:ControlParameter ControlID = "PatInfoName" Name="PatName" PropertyName="text"/>
<asp:ControlParameter ControlID = "PatInfoAge" Name="PatAge" PropertyName="text" />
<asp:ControlParameter ControlID = "PatInfoState" Name="PatState" PropertyName="selectedvalue" />
<asp:ControlParameter ControlID = "PatInfoCountry" Name="PatCountry" PropertyName="selectedvalue" />
<asp:ControlParameter ControlID = "PatInfoPhone" Name="PatPhone" PropertyName = "text" />
<asp:ControlParameter ControlID = "PatInfoCell" Name="PatCell" PropertyName="Text" />
</InsertParameters>
</asp:SqlDataSource>Here is the call to the statement.
InsertPatInfo.Insert() ; thrown after a button is clicked.
RSS Feed