'Insert Into' For Multiple Values Given A Table Into Which The Values Need To Go
Sep 1, 2007
Please be easy on me...I haven't touched SQL for a year. Why given;
Code Snippet
USE [Patients]
GO
/****** Object: Table [dbo].[Patients] Script Date: 08/31/2007 22:09:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Patients](
[PID] [int] IDENTITY(1,1) NOT NULL,
[ID] [varchar](50) NULL,
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[DOB] [datetime] NULL,
CONSTRAINT [PK_Patients] PRIMARY KEY CLUSTERED
(
[PID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
do I get
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ','.
for the following;
Code Snippet
INSERT INTO Patients
(ID, FirstName,LastName,DOB) VALUES
( '1234-12', 'Joe','Smith','3/1/1960'),
( '5432-30','Bob','Jones','3/1/1960');
Thank you,
hazz
View 3 Replies
ADVERTISEMENT
Apr 30, 2015
table2 is intially populated (basically this will serve as historical table for view); temptable and table2 will are similar except that table2 has two extra columns which are insertdt and updatedt
process:
1. get data from an existing view and insert in temptable
2. truncate/delete contents of table1
3. insert data in table1 by comparing temptable vs table2 (values that exists in temptable but not in table2 will be inserted)
4. insert data in table2 which are not yet present (comparing ID in t2 and temptable)
5. UPDATE table2 whose field/column VALUE is not equal with temptable. (meaning UNMATCHED VALUE)
* for #5 if a value from table2 (historical table) has changed compared to temptable (new result of view) this must be updated as well as the updateddt field value.
View 2 Replies
View Related
Feb 28, 2008
Hi,
I've been working as web dev for quite sometime now but there are still few things that i would like to clarify. Hope you guys can shed lights.
I currently have several tables and all this tables are having some 1 to many relationships. I know that how i insert value into my tables are very inefficient. I do it in this manner..
-> Insert a value in TableA
-> Query the latest ID that i have inserted in Table A then
-> Insert this value in TableB. And so on and so forth.
I believe there is an efficient way to to do this but i'm not sure how? Could anyone shed me a light on this matter?
Your reply would be highly appreciated. TIA.
View 6 Replies
View Related
Nov 21, 2007
I have a form to assign JOB SITES to previously created PROJECT. The JOB SITES appear in the DataList as it varies based on customer. It can be 3 to 50 JOB SITES per PROJECT.
I have "PROJECT" table with all necessary fields for project information and "JOBSITES" table for job sites. I also created a new table called "PROJECTSITES" which has only 2 columns: "ProjectId" and "SiteId".
What I am trying to do is to insert multiple rows into that "PROJECTSITES" table based on which checkbox was checked. The checkbox is located next to each site and I want to be able to select only the ones I need. Btw the Datalist is located inside of a formview and has it's own datasource which already distincts which JOBSITES to display.
Sample:
ProjectId - SiteId
1 - 5
1 - 9
1 - 16
1 - 18
1 - 20
1 - 27
1 - 31
ProjectId stays the same, only values for SiteId are being different.
I hope I explaining it right. Do I have to use some sort of loop to go through the automatically populated DataList records and how do I make a multiple inserts to database table? We use SQL Server 2005 and VB for code behind. Please ask if I missed on some information. Thank you in advance.
View 10 Replies
View Related
Apr 26, 2015
I have two tables A(uname,address,full_name) and B(uname,full_name). I want to update table A for all matching case of uname in table B.
View 5 Replies
View Related
Oct 25, 2007
hi everyone
how do i insert multiple rows in a database ?
i came up with something like this after googling but it does not work
INSERT INTO tblSold (LID, BuyerID,Date)select ('759','2106','2441') UNION ALLselect ('0','0','0') UNION ALLselect ('10/25/2007','10/25/2007','10/25/2007')
View 8 Replies
View Related
Jan 5, 2000
i have about 2,000 record and i need to insert them in my table.
how do i insert these informations using this syntax??
au_id au_name au_fname
1003 vivian latin
1005 cecy mani
1004 bili david
insert into autors (au_id, au_name,au_fname)
Values ('1101', 'Rabit','jesicca')
thanks for your time and help.
View 1 Replies
View Related
Apr 14, 2014
this is my data
AM1WSJZ1241
AM1WLSU7162
AM1SXBI5100
AM1TWXX0477
AM1MSMQ6167
AM1WRQP1810
AM1HNME1411
i want a query to insert a data in table
View 1 Replies
View Related
Jul 24, 2006
i can't believe my situation is unique, but my searches for answers have proven fruitless, so please bear with me.i have a date field in my database, and have previously used a simple textbox to allow users to insert a date. i want to make this a little step a little nicer, however, and have added a dropdown for the month, a textbox for the date (i'm not "good" enough to dynamically change this around for a dropdown list), and a dropdown list for the next few years.what "insert" statement would allow me to accomplish this? what i currently have doesn't blow up, but neither does it actually insert anything. here's my statment:<asp:SqlDataSource ID="sdsVersion" runat="server" ConnectionString="<%$ ConnectionStrings:DB %>" InsertCommand="INSERT INTO [Version] ([Major], [Minor], [Sub], [Rev], [Date]) VALUES (@Major, @Minor, @Sub, @Rev, @Month + '-' + @Day + '-' + @Year)"> <InsertParameters> <asp:Parameter Name="Major" Type="Int32" /> <asp:Parameter Name="Minor" Type="Int32" /> <asp:Parameter Name="Sub" Type="Int32" /> <asp:Parameter Name="Rev" Type="Int32" /> <asp:ControlParameter ControlID="ddlMonths" Name="Month" PropertyName="SelectedValue" Type="Int32" /> <asp:ControlParameter ControlID="tbDate" Name="Day" PropertyName="Text" Type="Int32" /> <asp:ControlParameter ControlID="ddlYears" Name="Year" PropertyName="SelectedValue" Type="Int32" /> </InsertParameters></asp:SqlDataSource>
View 7 Replies
View Related
Dec 30, 2004
I am dynamically creating a sql insert query from a web page that can potentially have multiple values to be inserted into a table. The query I have right now my query is shown below. When it runs, it only inserts one record (the values 24,3). Any ideas what I am doing wrong?
INSERT INTO [tblWebPageByRank]
([WebPageID],[RankID])
Values (24,3,24,5)
View 3 Replies
View Related
Oct 26, 2004
I want to know if MS SQL has ability to INSERT multiple values with one statement. For example MySQL is able to do it like so
INSERT INTO myTable (col1,col2,col3)
VALUES
('1','1','1'),
('2','2','2'),
('3','3','3'),
('4','4','4');
and this will insert four rows.
Does MS SQL Have anything similar or do I have to repeat the INSERT statement if I need to generate a dynamic insert?
Thanks,
Lito
View 9 Replies
View Related
Dec 20, 2007
I am trying to add records to 2 separate tables and each table has a unique ID from the other table. I need to find the highest number ID from both tables (and add 1) and add a record with both new values to both tables.
I have tried the following, but it is not working. Any help would be appreciated, Thanks (my tables are MEMOS and PDSMSGC)
INSERT INTO MEMOS (MemoID, ParentID, FieldName, MemoText)
SELECT MAX(MemoID) + 1 FROM "MEMOS", MAX(MessageID) +1 FROM "PDSMSGC", 'pmc:MemoID', 'Hi my name is bob'
INSERT INTO PDSMSGC (MessageID, MemoID) SELECT MAX(MessageID) + 1 FROM "PDSMSGC", MAX(MemoID) FROM "MEMOS"
View 5 Replies
View Related
Jun 17, 2012
I am SSRS user, We have a .net UI from where we want to pass multi select values, but these values are comma separated in the database. how can I write a sql query such that when I select multi values on my UI, the comma separated values are take care of.
View 5 Replies
View Related
Jan 19, 2008
Hi, im a newbie. using VB.net / visual studio 2005 and SQL express
I have pasted my page code below
Basically i have a lisbox which is bound to an sqldatasource. It allows the user to select multiple names. I then have another datasource which when the page is submitted it posts various values from different fields into my db. The insert works fine.My problem is that the multiple selection wont allow me to insert into my db, it just captures the first value.I used this sub below to prove that when i click a button on my page it takes all my selected choices and puts them inside a text box, this works i can see them all. I then bound the textbox text value to the one im inserting into the db, however once again its only inserting the first value from the text box, how is this possible, the text box shows all the values and its set to string, it should insert everything in the text box Protected Sub selectedContact_Click(ByVal sender As Object, ByVal e As System.EventArgs)
Dim alternatives As ListBox = CType(UploadPictureUI.FindControl("alternatives"), ListBox)
Dim TextBox1 As TextBox = CType(UploadPictureUI.FindControl("TextBox1"), TextBox)
For intLoopIndex As Integer = 0 To alternatives.Items.Count - 1
If alternatives.Items(intLoopIndex).Selected Then
TextBox1.Text &= alternatives.Items(intLoopIndex).Text & _
ControlChars.CrLf
End If
Next
End Sub
Here is my full page code
1 <%@ Page Language="VB" MasterPageFile="~/MasterPage.master" Title="Create Profile" Debug="true" %>
2 <script runat="server">
3 Protected Sub UploadPictureUI_ItemInserting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewInsertEventArgs) Handles UploadPictureUI.ItemInserting
4 'Reference the FileUpload control
5 Dim UploadedFile As FileUpload = CType(UploadPictureUI.FindControl("UploadedFile"), FileUpload)
6
7 'Make sure a file has been successfully uploaded
8 If UploadedFile.PostedFile Is Nothing OrElse String.IsNullOrEmpty(UploadedFile.PostedFile.FileName) OrElse UploadedFile.PostedFile.InputStream Is Nothing Then
9 ShowErrorMessage("No file was uploaded. Please make sure that you've selected a file to upload.")
10 e.Cancel = True
11 Exit Sub
12 End If
13
14 'Make sure we are dealing with a JPG or GIF file
15 Dim extension As String = Path.GetExtension(UploadedFile.PostedFile.FileName).ToLower()
16 Dim MIMEType As String = Nothing
17
18 Select Case extension
19 Case ".gif"
20 MIMEType = "image/gif"
21 Case ".jpg", ".jpeg", ".jpe"
22 MIMEType = "image/jpeg"
23 Case ".png"
24 MIMEType = "image/png"
25
26 Case Else
27 'Invalid file type uploaded
28 ShowErrorMessage("Only GIF, JPG, and PNG files can be uploaded.")
29 e.Cancel = True
30 Exit Sub
31 End Select
32
33 'Specify the values for the MIMEType and ImageData parameters
34 e.Values("MIMEType") = MIMEType
35
36 'Load FileUpload's InputStream into Byte array
37 Dim imageBytes(UploadedFile.PostedFile.InputStream.Length) As Byte
38 UploadedFile.PostedFile.InputStream.Read(imageBytes, 0, imageBytes.Length)
39 e.Values("imageData") = imageBytes
40 End Sub
41
42 Private Sub ShowErrorMessage(ByVal msg As String)
43 ErrorMessage.Text = msg
44 ErrorMessage.Visible = True
45 End Sub
46
47 Protected Sub UploadPictureUI_ItemInserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewInsertedEventArgs) Handles UploadPictureUI.ItemInserted
48 'If item successfully inserted, send user back to default
49 If e.Exception Is Nothing Then
50 Response.Redirect("~/confirmation.aspx")
51 End If
52 End Sub
53
54 Protected Sub selectedContact_Click(ByVal sender As Object, ByVal e As System.EventArgs)
55 Dim alternatives As ListBox = CType(UploadPictureUI.FindControl("alternatives"), ListBox)
56 Dim TextBox1 As TextBox = CType(UploadPictureUI.FindControl("TextBox1"), TextBox)
57 For intLoopIndex As Integer = 0 To alternatives.Items.Count - 1
58 If alternatives.Items(intLoopIndex).Selected Then
59 TextBox1.Text &= alternatives.Items(intLoopIndex).Text & _
60 ControlChars.CrLf
61 End If
62 Next
63 End Sub
64
65
66 </script>
67 <asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
68 <table border="0" cellpadding="0" cellspacing="0" style="vertical-align: middle;
69 width: 795px; text-align: center">
70 <tr>
71 <td align="center" style="height: 650px" valign="middle">
72 <asp:DetailsView ID="UploadPictureUI" runat="server" AutoGenerateRows="False" CssClass="createProfile"
73 DataKeyNames="id" DataSourceID="accessProfiles" GridLines="None" DefaultMode="Insert">
74 <Fields>
75 <asp:TemplateField HeaderText="Create Profile">
76 <InsertItemTemplate>
77 <table style="width: 350px">
78 <tr>
79 <td align="center" style="width: 150px; height: 30px">
80 Name:</td>
81 <td align="center" style="width: 200px; height: 30px">
82 <asp:TextBox ID="Nametxtbx" runat="server" CssClass="textboxstyle" Text='<%# Bind("Name") %>' MaxLength="20"></asp:TextBox>
83 <asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ControlToValidate="Nametxtbx"
84 CssClass="errorMessage" ErrorMessage="First Name is Required" ForeColor="">*</asp:RequiredFieldValidator></td>
85 </tr>
86 <tr>
87 <td align="center" style="width: 150px; height: 30px">
88 Email:</td>
89 <td align="center" style="width: 200px; height: 30px">
90 <asp:TextBox ID="emailtxtbx" runat="server" CssClass="textboxstyle" Text='<%# Bind("email") %>'></asp:TextBox>
91 <asp:RequiredFieldValidator ID="RequiredFieldValidator3" runat="server" ControlToValidate="emailtxtbx"
92 CssClass="errorMessage" ErrorMessage="Email Address is Required" ForeColor="">*</asp:RequiredFieldValidator>
93 <asp:RegularExpressionValidator ID="RegularExpressionValidator1" runat="server" ControlToValidate="emailtxtbx"
94 ErrorMessage="RegularExpressionValidator" ForeColor="" ValidationExpression="w+([-+.']w+)*@w+([-.]w+)*.w+([-.]w+)*">*</asp:RegularExpressionValidator></td>
95 </tr>
96 <tr>
97 <td align="center" style="width: 150px; height: 30px">
98 Mobile:</td>
99 <td align="center" style="width: 200px; height: 30px">
100 <asp:TextBox ID="mobiletxtbx" runat="server" CssClass="textboxstyle" Text='<%# Bind("mobile") %>' MaxLength="11"></asp:TextBox>
101 <asp:RequiredFieldValidator ID="RequiredFieldValidator4" runat="server" ControlToValidate="mobiletxtbx"
102 CssClass="errorMessage" ErrorMessage="Mobile Number is Required" ForeColor="">*</asp:RequiredFieldValidator>
103 </td>
104 </tr>
105 <tr>
106 <td align="center" style="width: 150px; height: 30px">
107 Extension:</td>
108 <td align="center" style="width: 200px; height: 30px">
109 <asp:TextBox ID="extensiontxtbox" runat="server" CssClass="textboxstyle" Text='<%# Bind("extension") %>' MaxLength="11"></asp:TextBox>
110 <asp:RequiredFieldValidator ID="RequiredFieldValidator5" runat="server" ControlToValidate="extensiontxtbox"
111 CssClass="errorMessage" ErrorMessage="Phone Extension is Required" ForeColor="">*</asp:RequiredFieldValidator></td>
112 </tr>
113 <tr>
114 <td align="center" style="width: 150px; height: 30px">
115 Alternative Contact:</td>
116 <td align="center" style="width: 200px; height: 30px">
117 <asp:ListBox ID="alternatives" runat="server" CssClass="listboxstyle"
118 DataSourceID="getAlternatives" DataTextField="Name" DataValueField="Name" SelectedValue='<%# Bind("alternativeContact") %>'
119 SelectionMode="Multiple"></asp:ListBox>
120 <asp:RequiredFieldValidator ID="RequiredFieldValidator6" runat="server" ControlToValidate="alternatives"
121 CssClass="errorMessage" ErrorMessage="Alternative Contact is Required" ForeColor="">*</asp:RequiredFieldValidator></td>
122 </tr>
123 <tr>
124 <td align="center" style="width: 150px; height: 30px">
125 Job Role:</td>
126 <td align="center" style="width: 200px; height: 30px">
127 <asp:DropDownList ID="DropDownList2" runat="server" CssClass="dropdownliststyle" DataSourceID="accessrole"
128 DataTextField="role" DataValueField="roleID" AppendDataBoundItems="True" SelectedValue='<%# Bind("roleID") %>'>
129 <asp:ListItem Value="" Selected="False">- Please Select -</asp:ListItem>
130 </asp:DropDownList>
131 <asp:RequiredFieldValidator ID="RequiredFieldValidator7" runat="server" ControlToValidate="DropDownList1"
132 CssClass="errorMessage" ErrorMessage="Job Role is Required" ForeColor="">*</asp:RequiredFieldValidator></td>
133 </tr>
134 <tr>
135 <td align="center" style="width: 150px; height: 30px">
136 Responsibilities:</td>
137 <td align="center" style="width: 200px; height: 30px">
138 <asp:TextBox ID="resposibilities" runat="server" CssClass="textareastyle" Text='<%# Bind("responsibilities") %>'
139 TextMode="MultiLine" MaxLength="200"></asp:TextBox>
140 <asp:RequiredFieldValidator ID="RequiredFieldValidator8" runat="server" ControlToValidate="resposibilities"
141 CssClass="errorMessage" ErrorMessage="Role Responsibilities are Required" ForeColor="">*</asp:RequiredFieldValidator></td>
142 </tr>
143 <tr>
144 <td align="center" style="width: 150px; height: 29px">
145 Hobbies:</td>
146 <td align="center" style="width: 200px; height: 29px">
147 <asp:TextBox ID="hobbiestxtbx" runat="server" CssClass="textareastyle" Text='<%# Bind("hobbies") %>'
148 TextMode="MultiLine" MaxLength="200"></asp:TextBox></td>
149 </tr>
150 <tr>
151 <td align="center" style="width: 150px; height: 30px">
152 Team Name:</td>
153 <td align="center" style="width: 200px; height: 30px">
154 <asp:DropDownList ID="DropDownList1" runat="server" CssClass="dropdownliststyle" DataSourceID="accessTeam"
155 DataTextField="TeamName" DataValueField="TeamID" AppendDataBoundItems="True" SelectedValue='<%# Bind("TeamID") %>'>
156 <asp:ListItem Value="" Selected="False">- Please Select -</asp:ListItem>
157 </asp:DropDownList>
158 <asp:RequiredFieldValidator ID="RequiredFieldValidator9" runat="server" ControlToValidate="DropDownList2"
159 CssClass="errorMessage" ErrorMessage="Team Name is Required" ForeColor="">*</asp:RequiredFieldValidator></td>
160 </tr>
161 <tr>
162 <td align="center" style="width: 150px; height: 30px">
163 I am a Manager:</td>
164 <td align="center" style="width: 200px; height: 30px">
165 <asp:CheckBox ID="isManager" runat="server" Checked='<%# Bind("isManager") %>' Text="Yes" /></td>
166 </tr>
167 <tr>
168 <td align="center" style="width: 150px; height: 30px">
169 I am a Team Manager:</td>
170 <td align="center" style="width: 200px; height: 30px"><asp:CheckBox ID="isTeamManager" runat="server" Checked='<%# Bind("isTeamManager") %>' Text="Yes" /></td>
171 </tr>
172 <tr>
173 <td align="center" style="width: 150px; height: 30px">
174 MyManager is:</td>
175 <td align="center" style="width: 200px; height: 30px">
176 <asp:DropDownList ID="myManagerIs" runat="server" CssClass="dropdownliststyle" DataSourceID="MyManager"
177 DataTextField="Name" DataValueField="id" AppendDataBoundItems="true" SelectedValue='<%# Bind("teamManagerID") %>'>
178 <asp:ListItem Value="" Selected="False">- Please Select -</asp:ListItem>
179 </asp:DropDownList>
180 </td>
181 </tr>
182 <tr>
183 <td align="center" style="width: 150px; height: 30px">
184 Upload Image:</td>
185 <td align="center" style="width: 200px; height: 30px">
186 <asp:FileUpload ID="UploadedFile" runat="server"
187 Width="150px" /> </td>
188 </tr>
189 <tr>
190 <td align="center" style="width: 150px; height: 30px">
191 Image Title:</td>
192 <td align="center" style="width: 200px; height: 30px">
193 <asp:TextBox ID="imagetitle" runat="server" CssClass="textboxstyle" Text='<%# Bind("imageTitle") %>' MaxLength="30"></asp:TextBox>
194 <asp:RequiredFieldValidator ID="RequiredFieldValidator10" runat="server" ControlToValidate="imagetitle"
195 CssClass="errorMessage" ErrorMessage="Image Title is Required" ForeColor="">*</asp:RequiredFieldValidator></td>
196 </tr>
197 <tr>
198 <td align="center" style="width: 150px; height: 30px">
199 Hide This Profile:</td>
200 <td align="center" style="width: 200px; height: 30px">
201 <asp:CheckBox ID="profileHide" runat="server" Checked='<%# Bind("Display") %>' /></td>
202 </tr>
203 <tr>
204 <td align="center" colspan="2" style="height: 30px">
205 <asp:TextBox ID="TextBox1" runat="server" Text='<%# Eval("alternativeContact") %>'></asp:TextBox><br />
206 <asp:LinkButton ID="selectedContact" runat="server" OnClick="selectedContact_Click" OnClientClick="selectedContact_Click">Add Selected Contacts</asp:LinkButton></td>
207 </tr>
208 <tr>
209 <td align="center" colspan="2" style="height: 30px">
210 <asp:ValidationSummary ID="ValidationSummary1" runat="server" CssClass="errorMsgstyle"
211 ForeColor="" />
212 </td>
213 </tr>
214 </table>
215 </InsertItemTemplate>
216 <HeaderTemplate>
217
218 </HeaderTemplate>
219 </asp:TemplateField>
220 <asp:TemplateField ShowHeader="False">
221 <EditItemTemplate>
222
223 </EditItemTemplate>
224 <InsertItemTemplate>
225 <asp:LinkButton ID="btnInsert" runat="server" CausesValidation="True" CommandName="Insert"
226 Text="Insert"></asp:LinkButton>
227 <asp:LinkButton ID="btnCancel" runat="server" CausesValidation="False" CommandName="Cancel"
228 Text="Cancel"></asp:LinkButton>
229 </InsertItemTemplate>
230 <ItemTemplate>
231
232 </ItemTemplate>
233 </asp:TemplateField>
234 </Fields>
235 </asp:DetailsView>
236 <br />
237 <asp:Label ID="ErrorMessage" runat="server" CssClass="errortext"></asp:Label> <br />
238
239 </td>
240 </tr>
241 <tr>
242 <td style="width: 100px">
243 <asp:SqlDataSource ID="accessProfiles" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
244 InsertCommand="INSERT INTO [employeeDetails] ([isManager], [isTeamManager], [Name], [RoleID], , [extension], [mobile], [alternativeContact], [imageTitle], [imageMimeType], [imageData], [TeamID], [Display], [teamManagerID], [responsibilities], [hobbies]) VALUES (@isManager, @isTeamManager, @Name, @RoleID, @email, @extension, @mobile, @alternativeContact, @imageTitle, @MimeType, @imageData, @TeamID, @Display, @teamManagerID, @responsibilities, @hobbies)" >
245 <InsertParameters>
246 <asp:Parameter Name="isManager" Type="byte" DefaultValue="0" Size="1" />
247 <asp:Parameter Name="isTeamManager" DefaultValue="0" Size="1" Type="Byte" />
248 <asp:Parameter Name="Name" Type="String" />
249 <asp:Parameter Name="RoleID" Type="Int32" />
250 <asp:Parameter Name="email" Type="String" />
251 <asp:Parameter Name="extension" Type="String" />
252 <asp:Parameter Name="mobile" Type="String" />
253 <asp:Parameter Name="alternativeContact" Type="String" />
254 <asp:Parameter Name="imageTitle" Type="String" />
255 <asp:Parameter Name="MimeType" Type="String" />
256 <asp:Parameter Name="imageData" />
257 <asp:Parameter Name="TeamID" Type="Int32" />
258 <asp:Parameter Name="Display" type="Byte" DefaultValue="1" Size="0" />
259 <asp:Parameter Name="teamManagerID" Type="Int32" />
260 <asp:Parameter Name="responsibilities" Type="String" />
261 <asp:Parameter Name="hobbies" Type="String" />
262 </InsertParameters>
263 </asp:SqlDataSource>
264 <asp:SqlDataSource ID="accessrole" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
265 SelectCommand="SELECT [role], [RoleID] FROM [role] ORDER BY [role] DESC"></asp:SqlDataSource>
266 <asp:SqlDataSource ID="MyManager" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
267 SelectCommand="SELECT id, Name, isManager, isTeamManager FROM employeeDetails WHERE (isManager > 0) OR (isTeamManager > 0) ORDER BY Name DESC">
268 </asp:SqlDataSource>
269 <asp:SqlDataSource ID="accessTeam" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
270 SelectCommand="SELECT [TeamID], [TeamName] FROM [Team] ORDER BY [TeamName] ASC"></asp:SqlDataSource>
271 <asp:SqlDataSource ID="getAlternatives" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
272 SelectCommand="SELECT [Name] FROM [employeeDetails] ORDER BY [Name]"></asp:SqlDataSource>
273 </td>
274 </tr>
275 </table>
276 </asp:Content>
277
278
Thanks for your help
View 1 Replies
View Related
Jul 22, 2014
I need to write SP where user select SUN to MON check boxes. If user select Class A with sun,mon and wed check boxes then i need to insert data as below
CLASS Days
A sun
A Mon
A wed
View 6 Replies
View Related
May 6, 2014
I have 2 identical tables one contains current settings, the other contains all historical settings.I could create a union view to display the current values from table A and all historical values from table B, butthat would also require a Variable to hold the tblid for both select statements.
Q. Can this be done with one joined or conditional select statement?
DECLARE @tblid int = 501
SELECT 1,2,3,4,'CurrentSetting'
FROM TableA ta
WHERE tblid = @tblid
UNION
SELECT 1,2,3,4,'PreviosSetting'
FROM Tableb tb
WHERE tblid = @tblid
View 9 Replies
View Related
Jun 6, 2007
If I have a Select statement like this in my C# code:
Select * From foods Where foodgroup In (@foodgroup)
And I want @foodgroup to have these values ... "meat", "dairy", fruit", what is the correct way to add the parameter?
I tried
meat, dairy, fruit
'meat', 'dairy', 'fruit'
but neither worked. Is this possible?
View 2 Replies
View Related
Mar 24, 2008
Is there a way to avoid entering column names in the excel template for me to create an excel file froma dynamic excel using openrowset.
I have teh following code but it works fien when column names are given ahead of time.
If I remove the column names from the template and just to Select * from the table and Select * from sheet1 then it tells me that column names donot match.
Server: Msg 213, Level 16, State 5, Line 1Insert Error: Column name or number of supplied values does not match table definition.
here is my code...
SET @sql1='select * from table1'SET @sql2='select * from table2'
IF @File_Name = '' Select @fn = 'C:Test1.xls' ELSE Select @fn = 'C:' + @File_Name + '.xls' -- FileCopy command string formation SELECT @Cmd = 'Copy C:TestTemplate1.xls ' + @fn
-- FielCopy command execution through Shell Command EXEC MASTER..XP_CMDSHELL @cmd, NO_OUTPUT -- Mentioning the OLEDB Rpovider and excel destination filename set @provider = 'Microsoft.Jet.OLEDB.4.0' set @ExcelString = 'Excel 8.0;HDR=yes;Database=' + @fn
exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT * FROM [Sheet1$]'') '+ @sql1 + '') exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT * FROM [Sheet2$]'') '+ @sql2 + ' ')
View 4 Replies
View Related
Jan 4, 1999
My process return a date in char format like "3/12/1998",
How could I put this data ( in char ) directly to date ISQ Server field ?
Thank's
View 1 Replies
View Related
Jan 31, 2008
I have created a table Table with name as Varchar and id as int. Now i have started inserting the rows like, insert into Table values ('arun',20).Yes i have inserted a row in the table. Now i have got the values " arun's ", 50. insert into Table values('arun's',20) My sqlserver is giving me an error instead of inserting the row. How will you solve this problem?
View 3 Replies
View Related
Sep 24, 2014
I have a table as dbo.Bewegungen and it will be updated every day with new dates
I inserted once the values of this table to another table with group by. I want to know how should i insert the new values every day to the new table? i don't want to insert the previous values again.
it has 5 columns as primary key and one of them is Date.
View 2 Replies
View Related
Oct 2, 2007
Hi,
I am trying to insert multiple values from another table as well as an addition value defined by me. Here's my code which is obviously incorrect at the AND statement:
Insert Into table_1 (ID, Col_1, Col_2)Select ID, Col_A, Col_B From table_A AND table1.Col_3 = 'XYZ'
Where table_A.Col_A IS NOT NULL
Pls advise on the correct way of constructing this statement.
Many Thanks
View 8 Replies
View Related
Apr 29, 2008
I have a sql select query that I'm pulling from a "Years" table to link to 3 columns in an Items table.ZCValuesYear table has two colums: YearID and YearYearID Year1 20042 20053 20064 20075 2008...I want to bind the "Year" value to the three colums in the ZCItem table: ItemUseFirstYearID ItemUseLastYearID ItemYearIDThe query below will pull all the "ID's" for each of the colums, but how would I make it pull the "Year" value (instead of record 4, it would pull 2007 instead)?<asp:SqlDataSource ID="sqlItemSelect" runat="server" ConnectionString="<%$ ConnectionStrings:MyConnString %>" SelectCommand="SELECT ZCPartVault.PartVaultID, ZCPartVault.PartVaultItemID, ZCValuesYear.Year, ZCItem.ItemName, ZCItem.ItemUseFirstYearID, ZCItem.ItemUseLastYearID FROM ZCPartVault FULL OUTER JOIN ZCItem ON ZCPartVault.PartVaultItemID = ZCItem.ItemID FULL OUTER JOIN ZCValuesYear ON ZCItem.ItemUseLastYearID = ZCValuesYear.YearID AND ZCItem.ItemUseFirstYearID = ZCValuesYear.YearID AND ZCItem.ItemYearID = ZCValuesYear.YearID" > </asp:SqlDataSource>
View 2 Replies
View Related
Mar 2, 2004
I am converting a site from asp to .net and the previus writer did something I never saw.(I am somewhat new to programming all together). He put multiple values in a single cell of the database. I have always learned to use a seperate column for each value.
Is this a classic asp thing and there are better ways now? Or is this something I should do myself? The columns type is text and the contents look like this:<Details Expertise="bla bla" Description="We are a three-divisional company ...," WebSite="www.blabla.com" AccountLevel="Free" AccountStatus="Active" WorkHomeZIP="22222" ContractStartDate="01/01/2003" ContractEndDate="12/31/2003"><Address Location="Work" State="Oh" ZIP="22222"/><Email Location="Work" Value=""/><Phone Location="Fax" Code="9801" Ext=""/></Details>
View 1 Replies
View Related
Jun 30, 2007
i am able to store multiple email ids in table (separated by comma).
now the problem is how do i retrieve those email ids from the table so that i can send mails to those email ids??
thanks
Jaimin
View 2 Replies
View Related
Jul 26, 2014
I am relatively new to SQL and as a project I have been asked to create the SQL for a simple database to record train details. I want to implement a check constraint which will prevent data from being inserted into a table if the weight of the train is more than the maximum towing weight of the locomotive. FOr instance, I need to add the unladen weight and maximum capacity of each wagon (located in the wagon type table) and compare it against the locomotive maximum pulling weight (the locomotive class table). I have the following SQL but it will not work:
check((select SUM(fwt.unladen_weight+fwt.maximum_payload) from
hauls as h,freight_wagon as fw,freight_wagon_type as fwt,train as t where
h.freight_wagon_serial_number = fw.freight_wagon_serial_number and
fw.freight_wagon_type = fwt.freight_wagon_type and
h.train_number = t.train_number) <
(select lc.maximum_towing_weight from locomotive_class as lc,locomotive as l,train as t where
lc.locomotive_class = l.locomotive_class and l.locomotive_serial_number = t.locomotive_serial_number))
The hauls table is where the constraint has been placed and is the intermediary table between train and freight wagon.
I may not have explained this very well; but in short, i need to compare the sum of two values in one table against a values located in another table...At present I keep getting a message telling me the sub query cannot return more than one row.
View 2 Replies
View Related
Dec 4, 2006
Hello,I have 3 tableTable 1 : list of "whatever" programTable 2: list of tasks for each programTable 3: list of user for each taskWhen I have a new program, I want to select existing task and copy them and assign them to my new program. But I also want to copy the list of user of each task.Is there a way to do that in sql?I do not really want to go through each single task, then copy it with the new program, then get the @@identity of the inserted task and then assign the same user to the newly inserted task.Thanks
View 3 Replies
View Related
Mar 17, 2008
What is the easiest way to insert a new row in a existing sql table through web developer.net (visual basic)?
E.g. a database called Names and the columns Firstname and Surname and you want to insert "anna","johnsson"?
thank you very much.
View 3 Replies
View Related
Jun 2, 2004
Hi, I want to INSERT INTO [Table] ([Field]) VALUES('I Have a ' in value')
please teach me how to
xxx
View 2 Replies
View Related
May 1, 2014
I have a table (tblCustomer) with three fields (customer_id, s_id, s_string)
I want to fill in this table with two fixed values ??and customer_id from another table.
Every customer that starts at P in tblMainCustomer.customer_nr should be entered in table tblCustomer.
Select customer_id from tblMainCustomer where customer_nr like 'P%'
Customer_id taken from tblMainCustomer and s_id, s_string these fixed values ??that are equal for each customer.
These fixed values ??are:
100-----Gold
101-----Steel
1002----Super Copper
Example: If I have a client who has has a customer_id 45 so it will be like this in tblCustomer:
customer_id----s_id----s_string
-------------------------------
45-------------100-----Gold
45-------------101-----Steel
45-------------102-----Super Copper
I am stuck, how do I do this the best way?
View 6 Replies
View Related
Dec 14, 2007
Hi all!
I want to fill a table with random values. In each line should be a different value - "independet" from the value the row above.
what i tried didnt work, it always produced the same value.
Maybe you have an idea
Thankx in advance and greetings from Vienna
Landau
View 2 Replies
View Related
May 4, 2008
Hi all:
I have a trouble getting the value for a field from the insert table, well, this is the problem:
I have to make a string from the insert command over the table, e.g.: 'insert into demo(campo) values('hello')'
this string have 2 parts: a) 'insert into demo(campo) values(' ---> I already have it , but the second, the core of the problem, I can't get it
this is my code:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: Marco Antonio García León
-- Create date: 28.04.2008
-- Description: Trigger para INSERT
-- =============================================
ALTER TRIGGER [ti_Prueba]
ON [dbo].[PRUEBA] FOR INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @MyCursor CURSOR --Cursor donde se aloja la estructura
DECLARE @cmd nVARCHAR(1000) --Comando
DECLARE @Cadena nVARCHAR(4000) --Cadena SQL a ejecutar
DECLARE @Campo AS nVarchar(1024) --Nombre del Campo
DECLARE @Tipo AS Integer --Tipo del campo
DECLARE @Valor AS nvarchar(1024) --Valor del campo
DECLARE @Cadena_Valor AS varchar(1000)
DECLARE NewRow CURSOR FOR
SELECT * FROM Inserted
DECLARE cursorStruc CURSOR FOR
SELECT SysColumns.Name, SysColumns.xType
FROM SysObjects JOIN
SysColumns ON(SysObjects.ID=SysColumns.ID)
WHERE SysObjects.Name LIKE 'prueba'
SET @MyCursor = cursorStruc
SET @Cadena = 'INSERT INTO Prueba('
SET @Cadena_Valor = 'VALORES: '
print 'Cargando la data al temporal';
select * into ##tempo from inserted;
-- Insert statements for trigger here
OPEN cursorStruc
-- Perform the first fetch.
FETCH NEXT FROM cursorStruc INTO @Campo, @Tipo
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
--SET @cCadena = (select cursorStruc.Name from cursorStruc)
--SET @cmd = 'SET @Valor="' + @Campo + '" FROM Inserted;';
--SET @cmd = 'DECLARE @Valor VARCHAR(1000); ' +
-- 'SELECT @Valor="' + @Campo + '" FROM #tempo;';
SET @cmd = 'DECLARE @Valor VARCHAR(1000); ' +
'SELECT @Valor=' + @Campo + ' FROM ##tempo;';
SET @Cadena = (@Cadena + @Campo + ', ');
SET @Cadena_Valor = (@Cadena_Valor + @Valor + ', ');
PRINT 'Cadena @cmd: ' + @cmd;
PRINT 'Campo: ' + @Campo;
PRINT 'Valor: ' + @Valor;
--EXECUTE sp_executesql @cmd; --, N'@campo';
EXEC sp_executesql @cmd, @campo, @Valor OUTPUT;
PRINT 'Valor: ' + @Valor;
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM cursorStruc INTO @Campo, @Tipo
END
SET @Cadena = SUBSTRING(@Cadena, 1, LEN(@Cadena)-1) + ') VALUES('
PRINT '@Cadena: ' + @Cadena
PRINT '@Cadena_Valor: ' + @Cadena_Valor
select * from ##tempo;
CLOSE cursorStruc
DEALLOCATE cursorStruc
END
Thanks a lot for your help
View 10 Replies
View Related
Sep 17, 2015
I am trying to insert values from a temp table #TableName to a db table.
CATEGORY table- CategoryId, CategoryName, CategoryDescription
Then I did this :
Select CategoryName
into #TableName
from CATEGORY
Now I'm doing this :
Insert into #TestTable values(1,'This is:'+CategoryName)
select CategoryName from #Test
CategoryID right now is not PK.
So, the intention is to insert as many CategoryNames available into TestTable with id 1 and category name edited as shown in Inset statement.
What is wrong with this query. Its not working.
View 12 Replies
View Related