I am looking to store the different forms and data in our database. We have several different forms and contains different information. I am looking for different approaches to model this table structure.
Also, I need to make sure the table structure will allow for new forms.
I'd like to create a table that will store different order items. Several order items make up one single order. Order items can have 0 or more children (max depth will never be deeper than one). Order items can have up to 150 attributes/values. The way I think this should be done is using XML column instead of the EAV type of model. My table structure currently looks like this:
* child_order_item_id (PK)
* parent_order_item_id (FK to child_order_item_id)
* order_id (FK to Order table)
* product_id (FK to Product table)
* price
* attribute_XML
How my attribute_XML should look like or how to validate the xml.
Im trying to determine the best way to store data gathered from a form that a user will fill out online. The form is dynamic and is customized at run time based on group-specific criteria. The end result is a form that might have 3 extra text boxes, 2 extra sets of radio buttons and a freeform textbox, whereas for another group, there might be a slightly different set of input fields. Now comes the issue of storing this data. Since the fields can be somewhat dynamic, it could get tricky to define table columns for each possible input field. So Im considering storing the data as xml. Has anyone else had to build custom forms and ended up storing the data as xml ?
View 2 Replies View RelatedI have a requirement to provide data in a denormalized form from normalized tables. Working in SSIS.
I have two tables: EmployeeCountry and Country.
EmployeeCountry
EmployeeId (PK)(FK)
CountryId(PK)(FK)
Country
CountryId (PK)
CountryName
There will only be a max of 3 Country entries for each Employee. So I want to select the EmployeeId and get the three CountryIds so it would look like this:
Employee
EmployeeId
CountryId1
CountryId2
CountryId3
In one store procedure I do insert same data into two tables (They have the same structure): OrderA and OrderB
insert into OrderA select * from OrderTemp
insert into OrderB select * from OrderTemp
And then got an error for code below.
"Multi-part identifier "dbo.orderB.OrderCity" could not be bound
IF dbo.OrderB.OrderCity=''
BEGIN
update dbo.OrderB
set dbo.orderB.OrderCity='London'
END
I have a form and a connectionString to a SQL database. If the textbox at the form is empty i want to store a null value there but when i pass this value as a parameter it brings the following error: Failed to convert parameter value from a String to a Int32..
cmd.Parameters("@Segundo_nombre").Value = txtSecondName.Text.ToString --> suposing is null it brings an Error.
cmd.Parameters.Add(Apellido)
How can i manage this? I want to store this value if it is null or not.
Also i don't know how to assign a null value to a variable. I tried with
v_flag = check_selection.check_string(v_idioma)
If v_flag = 1 Then 'la variable posee el texto Seleccione
v_idioma = DBNull.Value
but it's not working.
Thanks!!
Assume that if i have a product called Jacket that has the following descriptions, which written in bullet form
• 600 Denier Carbolex® coated 1000mm Polyurethane
• Anatomically fitted for the riding position
• Ballistic nylon in protective areas
• FS Rainguard® 100% waterproof/breathable jet-liner
• Polyester mesh non-allergenic liner
• Removable CE armour equipped in the hips and knees
• Padding at knees, shin, hips and kidney area
• Removable zip-out satin quilted liner
• Reflective Phoslite®
• Stretch in the knees
• Gusseted with Velcro front zip closure
• Velcro adjustable waist
• 2 front pockets
• Storm flap system
How am i going to store in the Products table ? But not all products have these descriptions, other products may have no description at all.... If I store them in a text/memo DESCRIPTION field, then , I do not know how to format them into bullet form...
I would appreciate anyone knows the solution for me... Thanks in advance !
A DB2 store procedure returns two data sets, when executed from SSMS, using linked server. Do we have any simple way to save the two data sets in two different tables ?
View 1 Replies View RelatedWe are storing changed data of tables into XML format for auditing purpose. The functionality is already achieved. We are using FOR XML Path clause to convert relational data of tables into XML format.
Now, a table is having column name with '(' . For example name of the column is, ColumnName(). In this case we can not convert into XML using For XML clause. Showing error as,
Column name 'columnName()' contains an invalid XML identifier as required by FOR XML; '(' (0x0028) is the first character at fault.
I recently set up a SQL 2012 FCI with a NetApp fileshare to store the data files. The install worked just fine, but I can't run an integrity check for any of my databases. Whenever I try, I get these error messages:
Msg 1823, Level 16, State 2, Line 1
A database snapshot cannot be created because it failed to start.
Msg 1823, Level 16, State 8, Line 1
A database snapshot cannot be created because it failed to start.
Msg 5120, Level 16, State 104, Line 1
Unable to open the physical file "path-to-fileshareMSSQL11.MSSQLSERVERMSSQLDATAmodel.mdf:MSSQL_DBCC12". Operating system error 1: "1(Incorrect function.)".
Msg 7928, Level 16, State 1, Line 1
The database snapshot for online checks could not be created. Either the reason is given in a previous error or one of the underlying volumes does not support sparse files or alternate streams. Attempting to get exclusive access to run checks offline.The error message suggests SQL had a problem creating the snapshot, but I checked through some NetApp documentation for configuring SMB 3.0 for SQL.
We have a typical issue with Column Store Index, we have a procedure which does 2 activities - Switch & Reverse Switch
Switch:
1. Fetch the Partitions needed to be switched
2. Switch the data from Main Table to Switch table
2. Disable the Column store on Switch table
SSIS Package:
3. Load data to Switch (Insert / Update)
Reverse Switch:
4. Enable the Switch
5. Switch back the data from Switch table to Main table
Issue: Some time the Column store is not getting disabled, and the package fails complaining try disabling the Column store index and try loading data.
If we re-run the procedure, the column store gets disabled.
Hi All, i want to store the word document files as xml files in the sql server 2000 . then i have to retrive the XML Files in to aspx pages. the document files size are 5 mb , 6 mb like that . plz give the solution that how to do it .Thanks in Advance . Regards ,raja
View 2 Replies View RelatedI am using below code to get patient status as an out put to my form. not sure whats happening but each time I run this its not executing my last "IF" if set to "N" show me N if not show me "Y" but it is by passing my first "IF" condition and jumps to last?
The column alerts_ind shows only Y or N in the table patient_status.
The table patient_status_mstr show the description of the patient which "discharged". All I want to do is if the patient is flagged with "discharge" the columns "alerts_ind" shows "Y". but something wrong? below is the code.
Alter PROCEDURE GBCheckPatientStatus (@enc_id varchar(36), @data_ind Char(1) OUTPUT)
as
begin
declare
@alerts_ind char(1);
select @alerts_ind =pm.alerts_ind
[Code] ....
Hi
Does any body know that creating free form rports using Report Builder is possible?
Free-form report - in the sense - that user should be able to drag the fields from the leftside fields pane and drop on body as we do in report designer using BI studio.
I could not figure out a way to do so. Is this a limitation?
Thanks
Raj Yennam
I have web forms with about 10-15 optional search parameters (fields) for a give table. Each item (textbox) in the form is treated as an AND condition.
Right now I build complex WHERE clauses based on wheather data is present in a textbox and AND each one in the clause. Also, if a particular field is "match any word", i get a ANDed set of OR's. As you can imagine, the WHERE clause gets quite large.
I build clauses like this (i.e., 4 fields shown):
SELECT * from tableName WHERE (aaa like '%data') AND (bbb = 'data') AND (ccc like 'data%') AND ( (xxx like '%data') OR (yyy like '%data%') )
My question is, are stored procedures better for building such dynamic SQL clauses? I may have one field or all fifteen. I've written generic code for building the clauses, but I don't know much about stored procedures and am wondering if I'm making this more difficult on myself.
Afternoon
I'm getting the below error message:
Autogrow of file 'FORMS' in database 'FORMS' cancelled or timed out after 30547 ms. Use ALTER DATABASE to set a smaller FILEGROWTH or to set a new size.
FORMS.LDF file is 7613952 KBand the growth is 512MB .
By how much should I set the filegrowth? The users are complaining that the application is freezing on them.
This is sqlserver 2000.
can you have constraints as such [CreateBy] [nvarchar](30) NOT NULL DEFAULT (suser_sname()),on a table that has a column store index in SQL Server 2012,2014, or 2016?
View 3 Replies View RelatedThis is my table named - CV
*CVID is a primary key
This is my table named - CVEducation
*CV to CVEducation is 1 to Many. CVEducation(CVID) is a foreign key
This is my table named - Level
*LevelID is stored in CVEducation. So, we can know - This education is Diploma or Degree or Masters or so on
This is my table named - StudyField
*StudyFieldID is stored in CVEducation. Som we can know - What kind of Education they have.
This is the example of Job Vacancy 1
1- Need Diploma in Chemistry OR Degree in Chemistry.
2- CGPA at least - 2.5
*May be need to store 2 rows with each CGPA
This is the example of Job Vacancy 2
1- Need Diploma in Commerce OR Diploma in Economics OR Degree in Biology
2- CGPA at least - 2.8
*May be need to store 3 rows with each CGPA
How to design my table to store Job Vacancy Minimal Requirement? I know, there's some cases we cannot computerized. But, at least some of Job Vacancy Requirement we can computerized.
I tried samples of FileTable in SQL Server 2012 to store files in database. I have following questions.
1. When I right click on FileTable, and say Explore File Table Directory, I was taken to a directory. But if I search the same directory in my server/machine, the directory is not available.
2. Can I use some external server/disk as a directory for FileTable?
3. How easy is to use FileTables using .Net web applications?
I could really use some suggestions on the best way to get the results of a pretty complex aspx form. There are a whole bunch of search criteria. Most of the criteria are ranges of numbers such as a starting number and ending number but other criteria need to search text fields for keywords. Most fields are optional which means that many values will not be submitted. Not that there is a right and wrong way to do it, but what typically works out better for these things, an aspx procedure that puts together a highly complicated select statement or writing a highly complicated stored procedure? Either way I see a ton of if statements, or am I making this a lot harder then it needs it needs to be?
Thanks.
giving a use access to enter data into one database table.
I know Access is an option, but I was wondering if there was another, like MS Word or Adobe.
This is a SSMS database, 2008.
I try to add data to SQL via Access Forms. When I add a data and open tables in Access which are connected to SQL, I can see my new datas. But if I open tables in SQL Server Management Studio I can not see any of those new datas. What should I do to solve this problem. I will be very happy if you can help me about this matter.
Thanks in advance...
Berk
Hello,
My company Intranet has a form that agents can use to post their comments about the company to upper management, but our customer service department would like to modify the form so that the agent has to pick from a comment type.
The dropdown options on the form will be as follows:
ComplimentsComplaintsGeneral CommentsSuggestions
Each dropdown option has a designated table in a SQL DB.Using postback on the same page, I need to change which fields of the form are visible based upon which dropdown selection the user chooses, and I need the fields to then be inserted into the table that corresponds with the dropdown selection item.
For example: If the Compliments dropdown selection is picked, I need a text box to show for the user's location, the name of the customer, account number, and the message box. Once the submit button is clicked, the characters in these boxes need to be inserted into the Compliments table using its data adapter.
However, if the user selects Suggestions, the name of the customer and the account number should not be visible, since these fields do not exist and when the submit button is pressed, the Suggestions table should be updated.
If you need more information, I will provide whatever is needed.
As always, thanks for everyone's assistance.
Chris
HiI am using Visual Web Developer 2005 Express and SQL Server Express 2005.From Northwind I can display data to Gridview.Could someone point me in the right direction on two points.How do I start a new database (.mdf) - I cannot find this option.I can change Northwind around - but I want to start my own database.
Once I have that sorted - how can I "insert" data into database using a dropdown box?A "dropdown" box such as you would find online.
Thanks in advance.Stephen
We have a table with 10 columns each column is of datatype int and can accept nulls, when I do a save is it better to have 0 inserted in to the column or just insert null?
View 9 Replies View RelatedI need help to resolve the following problem.
I have a form that is supposed to insert data into my data base. But I get an error message when I click submit.
Below is the Code and after that is the error message that I get. Thank you in advance.
<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="cc1" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
End SubProtected Sub insertSubmitBtn_Click(ByVal sender As Object, ByVal e As System.EventArgs)
SqlDataSource1.Insert()End Sub
</script><html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Untitled Page</title>
<script type="text/javascript">
function pageLoad() {
}
</script>
<style type="text/css">
.style1
{width: 34%;
}
.style2
{width: 144px;
}</style>
</head><body>
<br />
<form id="form1" runat="server">
<div>
<asp:ScriptManager ID="ScriptManager1" runat="server" />
<br /><asp:SqlDataSource ID="SqlDataSource1" runat="server"
InsertCommand="INSERT INTO property(provinceName, cityName, ownerName, ownerPhone, numOfRooms, rent, ownerEmail, listPeriod, addInfo)
VALUES(@provinceName, @cityName, @ownerName, @ownerPhone, @numOfRooms, @rent, @ownerEmail, @listPeriod, @addInfo)">
<InsertParameters><asp:ControlParameter ControlID="provinceddl2" Name="provinceName"
PropertyName="SelectedValue" /><asp:ControlParameter ControlID="cityddl2" Name="cityName"
PropertyName="SelectedValue" /><asp:ControlParameter ControlID="nameTxtBox" Name="ownerName"
PropertyName="Text" /><asp:ControlParameter ControlID="phoneTxtBox" Name="ownerPhone"
PropertyName="Text" /><asp:ControlParameter ControlID="roomsTxtBox" Name="numOfRooms"
PropertyName="Text" />
<asp:ControlParameter ControlID="rentTxtBox" Name="rent" PropertyName="Text" /><asp:ControlParameter ControlID="EmailTxtBox" Name="ownerEmail"
PropertyName="Text" /><asp:ControlParameter ControlID="listPeriodddl" Name="listPeriod"
PropertyName="SelectedValue" /><asp:ControlParameter ControlID="addInfoTxtBox" Name="addInfo"
PropertyName="Text" />
</InsertParameters>
</asp:SqlDataSource>
<br />
<br />
<br />
<br />
<table cellspacing="1" class="style1">
<tr>
<td class="style2">
Contact Name:</td>
<td>
<asp:TextBox ID="nameTxtBox" runat="server" Width="200px"></asp:TextBox>
</td>
</tr>
<tr>
<td class="style2">
Contact phone:</td>
<td>
<asp:TextBox ID="phoneTxtBox" runat="server" Width="200px"></asp:TextBox>
</td>
</tr>
<tr>
<td class="style2">
Province:</td>
<td>
<asp:DropDownList ID="provinceddl2" runat="server" Width="205px">
</asp:DropDownList>
</td>
</tr>
<tr>
<td class="style2">
City:</td>
<td>
<asp:DropDownList ID="cityddl2" runat="server" Width="205px">
</asp:DropDownList>
</td>
</tr>
<tr>
<td class="style2">
Email:</td>
<td>
<asp:TextBox ID="EmailTxtBox" runat="server" Width="200px"></asp:TextBox>
</td>
</tr>
<tr>
<td class="style2">
Number of Rooms:</td>
<td>
<asp:TextBox ID="roomsTxtBox" runat="server" Width="80px"></asp:TextBox>
</td>
</tr>
<tr>
<td class="style2">
Monthly Rent:</td>
<td>
<asp:TextBox ID="rentTxtBox" runat="server" Width="80px"></asp:TextBox>
</td>
</tr>
<tr>
<td class="style2">
Period to list:</td>
<td><asp:DropDownList ID="listPeriodddl" runat="server"
DataSourceID="listPeriodSqlDataSource" DataTextField="listDays"
DataValueField="listDays" Width="85px">
</asp:DropDownList><asp:SqlDataSource ID="listPeriodSqlDataSource" runat="server"
ConnectionString="<%$ ConnectionStrings:PlaceConnectionString1 %>"
SelectCommand="SELECT [listDays] FROM [listPeriod]"></asp:SqlDataSource>
</td>
</tr>
<tr>
<td class="style2">
Additional Infomation:</td>
<td><asp:TextBox ID="addInfoTxtBox" runat="server" Rows="5" TextMode="MultiLine"
Width="200px"></asp:TextBox>
</td>
</tr>
</table>
<br />
; <asp:Button ID="insertSubmitBtn" runat="server" Text="Submit" Width="140px"
onclick="insertSubmitBtn_Click" />
<br/>
<cc1:CascadingDropDown
ID="Provincecdd2"
runat="server"
TargetControlID="Provinceddl2"
Category="Province"
PromptText="Select a province"
ServicePath="Location.asmx"
ServiceMethod="GetProvince" />
<br /><cc1:CascadingDropDown
ID="Citycdd2"
runat="server"
TargetControlID="Cityddl2"
ParentControlID="Provinceddl2"
Category="City"
PromptText="Select a city"
ServicePath="Location.asmx"
ServiceMethod="GetCity" />
</div></form>
</body>
</html>
Server Error .
Invalid postback or callback argument. Event validation is enabled using <pages enableEventValidation="true"/> in configuration or <%@ Page EnableEventValidation="true" %> in a page. For security purposes, this feature verifies that arguments to postback or callback events originate from the server control that originally rendered them. If the data is valid and expected, use the ClientScriptManager.RegisterForEventValidation method in order to register the postback or callback data for validation.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.ArgumentException: Invalid postback or callback argument. Event validation is enabled using <pages enableEventValidation="true"/> in configuration or <%@ Page EnableEventValidation="true" %> in a page. For security purposes, this feature verifies that arguments to postback or callback events originate from the server control that originally rendered them. If the data is valid and expected, use the ClientScriptManager.RegisterForEventValidation method in order to register the postback or callback data for validation.Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace:
[ArgumentException: Invalid postback or callback argument. Event validation is enabled using <pages enableEventValidation="true"/> in configuration or <%@ Page EnableEventValidation="true" %> in a page. For security purposes, this feature verifies that arguments to postback or callback events originate from the server control that originally rendered them. If the data is valid and expected, use the ClientScriptManager.RegisterForEventValidation method in order to register the postback or callback data for validation.]
System.Web.UI.ClientScriptManager.ValidateEvent(String uniqueId, String argument) +2132728
System.Web.UI.Control.ValidateEvent(String uniqueID, String eventArgument) +108
System.Web.UI.WebControls.DropDownList.LoadPostData(String postDataKey, NameValueCollection postCollection) +55
System.Web.UI.WebControls.DropDownList.System.Web.UI.IPostBackDataHandler.LoadPostData(String postDataKey, NameValueCollection postCollection) +11
System.Web.UI.Page.ProcessPostData(NameValueCollection postData, Boolean fBeforeLoad) +353
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1194
Hotkwesi
Hello,
I'm trying to make a query to insert data form xls file to my database and it's working
Here's my code
INSERT INTO MY_TABLE
SELECT SAP_NBR, PERIOD, ANSWER, OSAT, SCORE
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="C:
ipTMP_Aubrygsi_osat.xls";
User ID=Admin;Password=;Extended properties=Excel 8.0')...Sheet1$
WHERE SAP_NBR is not null
AND PERIOD is not null
AND ANSWER is not null
AND OSAT is not null
AND SCORE is not null
And I would like to verify if there no sap_nrb and period already the same in the database
so I add :
AND SAP_NBR not in MY_TABLE.SAP_NBR
AND PERIOD not in MY_TABLE.PERIOD
but it doesn't work
what's the correct syntax to make it work ???
Thanks a lot & merry Xmas... :)
Our company purchased a app. Is there any way to find out which store procedures were fired once a button was clicked?
View 3 Replies View RelatedI have been asked to look into using Filestream for centralising MS Office documents (mostly excel).I am worried about the "user interface" aspect.I read that there are "this and that" APIs to read/write data to the filestream but surely one would need to write a specific interface to Word/Excel... which feels like far too hard.I am a great admirer of SQL Server but is it the right tool for document management?
We use SQL Server 2012 and have offices round the world with various internet connection quality.Our main aim is to stop the current "spreadsheet nightmare" so common with Excel.
I do have very old versions of duplicate store procedures on my databases. I know there is no "safe" way to do this using DMVs, so I am planning to combine that with a trace. But I would like to get others opinions about that.
Here's the DMV I am planning to use:
SELECT
CASE WHEN database_id = 32767 then 'Resource' ELSE DB_NAME(database_id)END AS DBName
,OBJECT_SCHEMA_NAME(object_id,database_id) AS [SCHEMA_NAME]
,OBJECT_NAME(object_id,database_id)AS [OBJECT_NAME]
,cached_time
,last_execution_time
,execution_count
[Code] ....
I will save that on a local table and run it every 5 min maybe? Or at an interval equal or lower than PLE?
While writing store procedure in db most of Time i will Use common Table to write select queries for selecting more than seven table whether it reduce speed performance or it won't
;with cte
{
}
We have an issue with the Version Store growing constantly. According to sys.dm_os_performance_counters, "Version Generation rate (KB/s)" is growing, but "Version Cleanup rate (KB/s)" isn't. We use read-committed snapshot isolation
While dbcc opentran and sys.dm_exec_requests don't show any long running transactions, I wrote a query looking at sys.dm_tran_ active_snapshot_ database_transactions. This shows a number of long running transactions but, according to sys.dm_exec_sessions, they are all sleeping. The transactions that are running come and go very quickly, as I would expect.Could these sleeping transactions be responsible for preventing the version store from cleaning up?
When we use Partition switch and load the data to a table, can we refresh the indexes for specific partition, so that we don't need to rebuild / refresh for complete is this possible ?
View 1 Replies View Related