Adding A Field And Inserting A Value Into It.
Apr 24, 2002
how do i add a new field and insert a value into it?
I'm importing data from another table into a new one, but my source table has a char field and the target filed is (int) can I add a new field to the source file and add a value to it? and how?
I have in my source table a field call companyName that contains text and the target table has a companyId which is an INT.
If I create a field call companyId and add a value of '1' depending on the value of CompanyName...
This is what i want ot do
if CompanyName = LA then insert 1 into new filed companyID
How can I do that? I have to do this for 100 or more tables. Can I write a script that will do that for me?
does it make sence?
View 2 Replies
ADVERTISEMENT
Aug 24, 2006
Hi,
I have a concern about adding a new field to a table with image field - which is huge.
Will there be a problem with some databases, where they have a hard time locating data correctly after such a large field?
Previously this happened to me, and what was advised to put all the big fields at the end of the table.
Thanks.
View 1 Replies
View Related
Feb 29, 2008
does anyone know if there is a way, or perhaps a custom toolbox control that is already developed that allows you to drag an entire dataset into a table, instead of pulling everything in field by field...?
was just curious.
-dk
View 3 Replies
View Related
Apr 18, 2006
I need to add a field to a query which i have created in SQL Server, i have done this in Access no problem and it works fine but when icopy the SQL code from Access to SQL it just falls over. This is my code in Access...
SELECT AIMPDTA_CDADDR.CDCUST, AIMPDTA_CDADDR.CDADDC, AIMPDTA_CDADDR.CDNAME, AIMPDTA_CDADDR.CDADD1, AIMPDTA_CDADDR.CDADD2, AIMPDTA_CDADDR.CDADD3, [CDCUST] & [CDADDC] AS Expr1
FROM AIMPDTA_CDADDR LEFT JOIN AIMPDTA_ASAREA ON AIMPDTA_CDADDR.CDNAME = AIMPDTA_ASAREA.ASNAME
WHERE (((AIMPDTA_CDADDR.CDADDC)<>" "));
Any suggestions would be much appreciated.
Thanks
View 2 Replies
View Related
Feb 7, 2007
I keep getting the following error message but I don't see what's wrong with my code
Server Error in '/Admin' Application.
Arithmetic overflow error converting expression to data type datetime.The statement has been terminated.
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.Data.SqlClient.SqlException: Arithmetic overflow error converting expression to data type datetime.The statement has been terminated.Source Error:
Line 147:cmdSql.Parameters.Add("@event_active","1")
Line 148:
Line 149:cmdSql.ExecuteNonQuery()
Line 150:
Line 151:pnlForm.Visible = FalseSource File: c:hostingwebhost4lifememberNYACOAadmincalendar.aspx Line: 149 Stack Trace:
[SqlException (0x80131904): Arithmetic overflow error converting expression to data type datetime.
The statement has been terminated.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +857466
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +735078
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +149
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +886
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +132
System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +415
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +135
ASP.calendar_aspx.SubmitForm(Object s, EventArgs E) in c:hostingwebhost4lifememberNYACOAadmincalendar.aspx:149
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210
1 <%@ Register TagPrefix="cc1" Namespace="ManyMonkeys.Web.HtmlEditorLibrary.Controls" Assembly="HtmlEditorLibrary" %>
2 <%@ Page Language="VB" masterpagefile="nyacoaadmin.master" debug="true" %>
3 <%@ mastertype virtualpath="nyacoaadmin.master" %>
4 <%@ import Namespace="System" %>
5 <%@ import Namespace="System.Data" %>
6 <%@ import Namespace="System.Data.SqlClient" %>
7 <script runat="server">
8
9 Dim Connection As SqlConnection
10 Dim Sql As String
11 Dim cmdSql As SqlCommand
12 Dim dtr As SqlDataReader
13
14 Sub Page_Load
15
16 Connection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
17 Connection.Open()
18
19 If Not isPostback then
20
21 Sql = "SELECT chapter_id, chapter_name FROM chapter ORDER BY chapter_id"
22 cmdSql = New SqlCommand (Sql, Connection)
23 dtr = cmdSql.ExecuteReader()
24
25 ddlChapter.DataSource = dtr
26 ddlChapter.DataTextField = "chapter_name"
27 ddlChapter.DataValueField = "chapter_name"
28 ddlChapter.DataBind()
29
30 dtr.Close
31
32 ddlChapter.Items.Insert(0, New ListItem(" ", ""))
33
34 Call ShowList()
35
36 End If
37
38 End Sub
39
40 Sub Page_Unload
41
42 'Connection.Close()
43
44 End Sub
45
46 Sub ShowList()
47
48 Sql = "SELECT event_id, event_name, event_date, chapter_name FROM event WHERE event_active = '1' ORDER BY event_id"
49 cmdSql = New SqlCommand(sql, Connection)
50 dtr = cmdSql.ExecuteReader()
51
52 dgrdList.DataSource = dtr
53 dgrdList.DataBind()
54 dtr.Close()
55
56 pnlList.Visible = True
57
58 End Sub
59
60 Sub dgrdList_ItemCommand(s As Object, E As DataGridCommandEventArgs)
61
62 If e.CommandName = "edit" then
63 Call EditForm(dgrdList.DataKeys(e.Item.ItemIndex))
64 Else If e.CommandName = "delete" then
65 Call ConfirmDelete(dgrdList.DataKeys(e.Item.ItemIndex))
66 End If
67
68 End Sub
69
70 Sub ConfirmDelete(idVal As Integer)
71
72 pnlList.Visible = False
73 pnlConfirmDelete.Visible = True
74
75 sql = "SELECT event_name FROM event WHERE event_id = " & idVal
76 cmdSql = New SqlCommand(sql, connection)
77 litDelete.Text = cmdSql.ExecuteScalar()
78
79 ViewState("idVal") = idVal
80
81 End Sub
82
83 Sub DeleteForm (s As Object, E As Eventargs)
84
85 sql = "UPDATE event SET event_active = '0' WHERE event_id = " & ViewState("idVal")
86 cmdSql = New SqlCommand(sql, connection)
87 cmdSql.ExecuteNonQuery()
88
89 pnlList.Visible = True
90 pnlConfirmDelete.Visible = False
91
92 Call ShowList()
93
94 End Sub
95
96 Sub ShowCalendar(s As Object, E As ImageClickEventargs)
97
98 pnlEventDate.Visible = True
99
100 End Sub
101
102 Sub UpdateDate (s As Object, E As Eventargs)
103
104 pnlEventDate.Visible = False
105 txtEventDate.text=eventCalendar.SelectedDate.ToShortDateString()
106
107 End Sub
108
109 Sub SubmitForm (s As Object, E As Eventargs)
110
111 If ViewState("submitType") = "edit" then
112 sql = "UPDATE event SET event_name=@event_name, event_date=@event_date, chapter_name=@chapter_name, event_description=@event_description, event_last_updated_by=@event_last_updated_by, event_active=@event_active WHERE event_id = " & ViewState("idVal")
113 Else
114 sql = "INSERT INTO event (event_name, event_date, chapter_name, event_description, event_added_by, event_last_updated_by, event_active) VALUES (@event_name, @event_date, @chapter_name, @event_description, @event_added_by, @event_last_updated_by, @event_active)"
115 End If
116
117 cmdSql = New SqlCommand(sql, Connection)
118
119 If Len(txtEventName.Text) = 0 then
120 cmdSql.Parameters.Add("@event_name",DbNull.Value)
121 Else
122 cmdSql.Parameters.Add("@event_name",txtEventName.Text)
123 End If
124
125 If Len(txtEventDate.Text) = 0 then
126 cmdSql.Parameters.Add("@event_date",DbNull.Value)
127 Else
128 cmdSql.Parameters.Add("@event_date",txtEventDate.Text & " " & ddlEventHour.SelectedValue & ":" & ddlEventMinute.SelectedValue & " " & ddlEventAMPM.SelectedValue)
129 End If
130
131 If Len(ddlChapter.SelectedValue) = 0 then
132 cmdSql.Parameters.Add("@chapter_name",DbNull.Value)
133 Else
134 cmdSql.Parameters.Add("@chapter_name",ddlChapter.SelectedValue)
135 End If
136
137 If Len(txtEventDescription.Text) = 0 then
138 cmdSql.Parameters.Add("@event_description",DbNull.Value)
139 Else
140 cmdSql.Parameters.Add("@event_description",txtEventDescription.Text)
141 End If
142
143 cmdSql.Parameters.Add("@event_added_by",session("admin_username"))
144
145 cmdSql.Parameters.Add("@event_last_updated_by",session("admin_username"))
146
147 cmdSql.Parameters.Add("@event_active","1")
148
149 cmdSql.ExecuteNonQuery()
150
151 pnlForm.Visible = False
152 pnlList.Visible = True
153
154 Call ShowList()
155
156 End Sub
157
158 Sub AddForm(s As Object, E As Eventargs)
159
160 pnlForm.Visible = True
161 pnlList.Visible = False
162
163 txtEventName.Text = ""
164 txtEventDate.Text = ""
165 ddlChapter.ClearSelection()
166 txtEventDescription.Text = ""
167
168 ViewState("submitType") = "add"
169
170 End Sub
171
172 Sub EditForm (idVal As Integer)
173
174 pnlList.Visible = False
175 pnlForm.Visible = True
176
177 sql = "SELECT * FROM event WHERE event_id = " & idVal
178 cmdSql = New SqlCommand(sql, connection)
179 dtr = cmdSql.ExecuteReader()
180
181 If dtr.Read() then
182
183 txtEventName.Text = dtr("event_name") & ""
184 txtEventDate.Text = dtr("event_date") & ""
185 txtEventDescription.Text = dtr("event_description") & ""
186 ddlChapter.ClearSelection()
187 Try
188 ddlChapter.Items.FindByValue(dtr("chapter_name")).Selected = True
189 Catch Ex As Exception
190 End Try
191
192 End If
193
194 dtr.Close()
195
196 ViewState("submitType") = "edit"
197 ViewState("idVal") = idVal
198
199 End Sub
200
201 Sub DeleteCancel (s As Object, E As Eventargs)
202
203 pnlConfirmDelete.Visible = False
204 pnlList.Visible = True
205
206 End Sub
207
208 </script>
209
210 <asp:Content contentplaceholderid="cplhMainContent" runat="server">
211
212 <asp:Panel runat="server" id="pnlList" Visible="False">
213
214 <asp:DataGrid runat="server" id="dgrdList" AutoGenerateColumns="False" CellPadding="3" CellSpacing="0" width="550" Border="0" DataKeyField="event_id" onItemCommand="dgrdList_ItemCommand">
215 <HeaderStyle BackColor="#CDAD00" Font-Names="Tahoma,Arial,Helvetica,sans-serif" Font-Bold="true" Font-Size="10pt" HorizontalAlign="center" ForeColor="white" />
216 <ItemStyle BackColor="white" Font-Names="Tahoma,Arial,Helvetica,sans-serif" Font-Size="10pt" ForeColor="black" VerticalAlign="top" />
217 <AlternatingItemStyle BackColor="#dcdcdc" Font-Names="Tahoma,Arial,Helvetica,sans-serif" Font-Size="10pt" ForeColor="black" VerticalAlign="top" />
218 <Columns>
219 <asp:ButtonColumn Text="Edit" ButtonType="PushButton" CommandName="edit" />
220 <asp:ButtonColumn Text="Delete" ButtonType="PushButton" CommandName="delete" />
221 <asp:BoundColumn DataField="event_name" HeaderText="Event Name" />
222 <asp:BoundColumn DataField="event_date" HeaderText="Date" />
223 <asp:BoundColumn DataField="chapter_name" HeaderText="Sponsoring Chapter" />
224 </Columns>
225 </asp:DataGrid>
226
227 <asp:Button runat="server" Text="Add New Event" onClick="AddForm"></asp:Button>
228
229 </asp:Panel>
230
231 <asp:Panel runat="server" id="pnlForm" Visible="False">
232
233 Name of Event: <asp:TextBox id="txtEventName" runat="server"></asp:TextBox>
234 <br />Chapter: <asp:DropDownList id="ddlChapter" runat="server" AutoPostBack="False" />
235 <br />Date: <asp:TextBox id="txtEventDate" runat="server" ReadOnly="true" BackColor="Gray"></asp:TextBox> <asp:ImageButton id="CalButton" runat="server" OnClick="ShowCalendar" ImageUrl="images/cal.gif" />
236 <br /><asp:Panel runat="server" id="pnlEventDate" Visible="False">
237 <asp:Calendar id="eventCalendar" runat="server" FirstDayOfWeek="Sunday" Font-Names="Tahoma" Font-Size="8pt" OnSelectionChanged="UpdateDate">
238 <SelectedDayStyle BackColor="#EC9900" Font-Bold="True" />
239 <TodayDayStyle BackColor="#FFC0C0" />
240 <SelectorStyle Font-Names="Tahoma" Font-Size="8pt" />
241 <DayStyle Font-Names="Tahoma" Font-Size="8pt" />
242 <WeekendDayStyle BackColor="#E0E0E0" />
243 <OtherMonthDayStyle ForeColor="Silver" />
244 <DayHeaderStyle BackColor="#334B79" Font-Names="Tahoma" Font-Size="8pt" ForeColor="White" />
245 <TitleStyle BackColor="#CCCCFF" Font-Bold="True" Font-Names="Tahoma" Font-Size="10pt" />
246 </asp:Calendar>
247 <br />
248 </asp:Panel>
249 Time: <asp:DropDownList ID="ddlEventHour" runat="server" >
250 <asp:ListItem Text="12" Value="12" Selected="true"/>
251 <asp:ListItem Text="1" Value="1" />
252 <asp:ListItem Text="2" Value="2" />
253 <asp:ListItem Text="3" Value="3" />
254 <asp:ListItem Text="4" Value="4" />
255 <asp:ListItem Text="5" Value="5" />
256 <asp:ListItem Text="6" Value="6" />
257 <asp:ListItem Text="7" Value="7" />
258 <asp:ListItem Text="8" Value="8" />
259 <asp:ListItem Text="9" Value="9" />
260 <asp:ListItem Text="10" Value="10" />
261 <asp:ListItem Text="11" Value="11" />
262 </asp:DropDownList>
263 :
264 <asp:DropDownList ID="ddlEventMinute" runat="server">
265 <asp:ListItem Text="00" Value="00" Selected="true" />
266 <asp:ListItem Text="15" Value="15" />
267 <asp:ListItem Text="30" Value="30" />
268 <asp:ListItem Text="45" Value="45" />
269 </asp:DropDownList>
270 <asp:DropDownList ID="ddlEventAMPM" runat="server">
271 <asp:ListItem Text="AM" Value="AM" />
272 <asp:ListItem Text="PM" Value="PM" Selected="true" />
273 </asp:DropDownList>
274 <br /><br />
275 Content:
276 <br /><cc1:HtmlEditor id="txtEventDescription" runat="server" Height="300px" Width="575px"></cc1:HtmlEditor>
277 <br /><br />
278 <asp:Button onclick="SubmitForm" runat="server" Text="Submit"></asp:Button>
279
280 </asp:Panel>
281
282 <asp:Panel runat="server" id="pnlConfirmDelete" Visible="False">
283
284 Are you sure you wish to delete <asp:Literal runat="server" id="litDelete"></asp:Literal>?
285 <br /><br />
286 <asp:Button runat="server" Text="Yes - DELETE" onClick="DeleteForm"></asp:Button> <asp:Button runat="server" Text="No - GO BACK" onClick="DeleteCancel"></asp:Button>
287
288 </asp:Panel>
289
290 </asp:Content>
View 4 Replies
View Related
Jul 29, 2007
Hi All,I am trying to insert symbols in a field of a table. I am using the datatype nvarchar for the field.However when i tried to insert a statement with the symbol pi, it comes out as following:(A) 100? cm2 (B) 140? cm2 (C) 200? cm2All the pi symbol are converted to ?.Can ne 1 tell me how i can store such strings in the field.Thanks
View 1 Replies
View Related
Apr 21, 2008
Hello,
I'm trying to insert a string expression into a varbinary field. I've tried it several ways, but the data does not seem to get inserted.
I map DT_STR field ("T") with varbinary field in destination table and the package executes properly, but when I see the data that it has been loaded I only see empty values (0x).
I have also tried other approaches, like converting to DT_BYTES during SSIS flow, but I always get the same result.
Any idea of how to achieve this?
View 1 Replies
View Related
Nov 15, 2006
I have a table with the following schema:
CREATE TABLE [itis].[wrk_taxon_authors] (
[wb_taxon_author_id] [int] NOT NULL ,
[taxon_author_id] [int] IDENTITY (1, 1) NOT NULL ,
[taxon_author] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[kingdom_id] [smallint] NOT NULL
) ON [PRIMARY]
GO
I am trying to insert the following data (as you can see fields are seperated with the | ):
19||Flores-Villela and Sánchez-H., 2003|5|
20||Wiegmann, 1828|5|
16|17482|Gray, 1838|5|
17|9823|(Wiegmann, 1828)|5|
I get the following error:Server: Msg 4869, Level 16, State 1, Line 1
Bulk Insert failed. Unexpected NULL value in data file row 1, column 2. Destination column (taxon_author_id) is defined NOT NULL.
Server: Msg 4869, Level 16, State 1, Line 1
Bulk Insert failed. Unexpected NULL value in data file row 2, column 2. Destination column (taxon_author_id) is defined NOT NULL.
Since I have defined column 2 as an identity column, I don't understand why SQL Query analyzer is upset when I do not have a value in that field. To me, I would think it would auto-insert an integer (starting at seed 1 and incrementing by 1), but it doesn't. Could someone tell me what I'm doing wrong?
View 7 Replies
View Related
Jul 30, 2007
Hi All,
I am trying to insert symbols in a field of a table.
I am using the datatype nvarchar for the field.
However when i tried to insert a statement with the symbol pi, it comes out as following:
(A) 100? cm2 (B) 140? cm2 (C) 200? cm2
All the pi symbol are converted to ?.
Can ne 1 tell me how i can store such strings in the field.
Thanks
View 3 Replies
View Related
Aug 30, 2000
I am trying to add a field on a view which should be query-able.
Please what is the best way to go about it. HELP PLEASE!!!
View 1 Replies
View Related
Mar 19, 2003
Hi,
I have this problem: I'm creating a weblinked database using Access and ASP. The problem is that the fields in the table won't be always the same. E.g. sometimes there will be need for an extra field, but after a while it may not be necessary anymore. Users should be able to add a field to the table (and if possible delete one later on)
I don't whether it is possible to use sql to add a field to an accessdbase. Or is there another way to solve this problem?
thanks for your reply,
yours sincerely,
Dimitri De Vos
View 2 Replies
View Related
May 30, 2008
Have a table with the following data.
Table1
Ord_no inv_no amt Account freefield3
25 125 100.00 1444
25 125 10.00 3555 Rebate
25 125 10.00 3555 Rebate
After the records are inserted I need to add the amt's where freefield3 equals rebate then subtract that sum from the amt where account = 1444. So my final result would be to have 80.00 in the amt field where account equals 1444
View 4 Replies
View Related
Feb 21, 2007
I am trying to insert 1000000.00 into my sql table from a webpage. I as long as the amount is 999.99 or less it works fine, once higher then that amount it gives me an error.
Below is the code I am using to do the insert, it gets the error on the insert and the update both:
I am getting the error on the price inserting the FormatCurrency(txtprice.Text)
SelectStatement = "Insert crewchief (crewchief, price, car_num) Select '" & txtcrewchief.Text & "', " & FormatCurrency(txtprice.Text) & ", '" & txtcarnum.Text & "'"
Adapter.SelectCommand = New SqlClient.SqlCommand(SelectStatement, myConnection)
MyCommandBuilder = New SqlClient.SqlCommandBuilder(Adapter)
Adapter.Fill(MatcherDS, "temp")
Any ideas on why?
Thank you
View 2 Replies
View Related
Aug 17, 2007
I have a datetime field in a database which I am programmatically inserting values into. The field can be null or not null. However, I am having problems inserting NULL as part of my SQLCommand.
The value is pulled from a text box (linked to a calendar extender) and when I select a value it is being inserted fine. If I just leave the box blank though, I want the field to be set to NULL. I have tried adding in ,DBNULL.Value, as part of my VALUES(…) string, but this throws an exception. I Have tried just inserting ‘’ but that also throws an exception (“The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value�), so I don’t know how I can insert this value when the field is blank?
Can anyone shed some light please? Thanks
View 2 Replies
View Related
Apr 6, 2008
I have a field in a db table which is like a chat history.I need to update the field with a user's input (put into a multiline text box) and append that to the beginning of the field.The problem is that I want to maintain line breaks when I retrieve the field into a multiline textbox (aka textarea) and can't figure it out.I've searched around and have seen lots of solutions for converting line breaks into <br> tags, but those people are trying to output to a label.I'm using an update command in my SqlDataSource like this: UpdateCommand="UPDATE [account] SET [history] = @history + history WHERE [id] = 1"With a parameter declaration like this: <UpdateParameters> <asp:ControlParameter Name="history" ControlID="TextBox1" PropertyName="Text" /> </UpdateParameters>If I try to concatenate in a or CHR(10), the characters are literally inserted. Like this for example (this is the last variation I tried).... UpdateCommand="UPDATE [account] SET [history] = @history + CHR(10) + history WHERE [id] = 1"How do I store a CrLf programatically?
View 6 Replies
View Related
Jun 2, 2008
Hello Everyone,
I have looked and looked for any information on how to insert data from a db into a text field using a stored procedure. If anyone has any helpful links or suggestions, my sanity would greatly appreciate the help.
Thank you
View 1 Replies
View Related
Feb 26, 2006
Hello,
I was wondering if someone could give me some insight to why i may be having this SQL 05 problem. I have setup a series of cells using say vchar(max). However when i go into add data in the table if I enter anything on of the word i.e. insert(Hello my name is) This red circle with an exclamation mark ( ! ) pops up and then if i continue to do another row it wont let me continue.
The message i get is
Error Source: .netSql Client Data provider
Error message: String or binary data would be truncated.
The statement has been terminated,
Then it says to fix this or press escape, i have never seen this before can help me out as I have no idea why this is happening.
Any help much appreciated.
View 1 Replies
View Related
Mar 22, 2006
HiI am trying to insert value retrieved from Now() into a datetime field in my MSDE database, but I am getting the following error, and I have no idea what is going wrong.Arithmetic overflow error converting expression to data type datetime.The statement has been terminated.Here is the code I am using:Dim user As String = MyContext.User.Identity.Name.ToString
Dim TimeDate As DateTime = Now()
Dim status As String = "Pending"
With SqlOrders.InsertParameters
.Item("UserName").DefaultValue = user
.Item("OrderDate").DefaultValue = TimeDate
.Item("Status").DefaultValue = status
End With
SqlOrders.Insert()The date is being returned in this format 23/03/2006 02:01:52, which is the same format as it should appear in the database. could anyone please tell me where I am going wrong?
View 2 Replies
View Related
May 9, 2000
Can some one please tell me how to update a field in a table with multiple
values for each of the values in the other fields?
Thanks in advance.
View 1 Replies
View Related
Feb 15, 2006
can we insert multiple values into the same field. as we do for the mailing list. that is can we use commas to enter multiple values into the same field
View 10 Replies
View Related
Mar 14, 2008
I need to insert rows into a table which contains a smallint field
for time. The times are stored in that colum as integers (898,
11345, 1259, etc.) How can I enter a time like 9:15 AM into this field? I know how to display integer data in hh:mm format but I'm stumped on how I can do the reverse.
Thanks for any help offered.
View 12 Replies
View Related
Nov 12, 2007
Can a bitmap be added to a Varbinary field using the INSERT statement. If not what other method can I use to add a photo image (bmp or gif) to my Sql database?
View 4 Replies
View Related
Sep 17, 2007
Hi!
I want to add a word to a value if the value already exists in that field. How to do this? Please help me. In detail, i have 'id', 'name' and 'info' three columns in one Data Table. When I inserted one value to id field, if the value already exists it should add a word to that value and it should get inserted. Please help me to do this?
Thanks in advance!
View 5 Replies
View Related
Jan 13, 2005
I want to add a computed field to an existing SQL table:
where Field1 is >0 and field2 is not null
set newfield = 'Y'
else set newfield = 'N'
I want to keep this existing table because I'm using it as the basis for an Access Report that is nearly complete.
BTW this table is the result of a DTS package that is comprised of several SQL executables so I need to be able to repeat this as part of a larger process.
Thanks!
sgreene
View 2 Replies
View Related
Jul 20, 2005
Newbie question:Aside from the single quote (i.e. chr(39)) what other characters can causeMS-SQLserver to cough-up the insertion / update back in your face?TIA
View 1 Replies
View Related
May 18, 2007
Hi all,
I am stuck in the following situation, I have following query:
SELECT EM1.VALUE AS 'P_ABC', EM2.VALUE AS 'P_XYZ', COUNT(EM1.VALUE) AS 'COUNTS'
FROM TABLE_1 EM1, TABLE_1 EM2
WHERE EM1.EXTENDED_PROPERTY_GID IN (SELECT GID FROM TABLE_3 WHERE NAME = 'ABC' )
AND EM2.EXTENDED_PROPERTY_GID IN (SELECT GID FROM TABLE_3 WHERE NAME = 'XYZ' )
AND EM1.DOCUMENT_METADATA_ENTRY_GID = EM2.DOCUMENT_METADATA_ENTRY_GID
group by EM1.VALUE,EM2.VALUE
There is foreign key relation between EXTENDED_PROPERTY_GID OF TABLE_1 AND GID FROM TABLE_3
Now, I want to create an ad hoc report but the problem is when I add EM1.VALUE to display the P_ABC, I am not able to add EM2.VALUE after that, may be because refere to same column of same table. I have to add EM1.VALUE and EM2.VALUE both to display result but I am not able to do it.
What is the solution for this problem ? Its kind of urgent.
Thanks,
prashant
View 1 Replies
View Related
Jun 5, 2007
what i understand if if the data field is integer or money, not string, then i need to do a convert(datatype, value) in the insert but how come its still not working INSERT INTO [Product] ([Title], [Description], [Processor], [Motherboard], [Chipset], [RAM], [HDD], [OpticalDrive], [Graphics], [Sound], [Speakers], [LCD], [Keyboard], [Mouse], [Chassis], [PSU], [Price]) VALUES (@Title, @Description, @Processor, @Motherboard, @Chipset, @RAM, @HDD, @OpticalDrive, @Graphics, @Sound, @Speakers, @LCD, @Keyboard, @Mouse, @Chassis, @PSU, convert(smallmoney, @Price))
View 1 Replies
View Related
Jun 30, 2007
See my code below... it nicely insert in database data...
but I wish: if user of program in field "txtPozicija" try insert in
database same data detail like is in "Pozicija" field in database that
he be stopped and informed by message: you can not insert TWO SAME data
in the "Pozicija" table.
any advice here?
I AM TRY SOLVE THIS LIKE BELOW, NO ERROR BUT NOT WORK
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
public partial class adminpanel_Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
lblHR.Text = txtPozicija.Text;
lblEN.Text = txtLinkZaViseoMjestu.Text;
lblIT.Text = ddRegija.Text;
}
// On dropbox in the html added OnDataBound="MyDataBound" Occurs after the server control binds to a data source.
protected void myDataBound(Object sender, EventArgs e)
{
ddRegija.Items.Insert(0, "--- Selektiraj ---");
}
void SaveDataDB()
{
string ConnStr = ConfigurationManager.ConnectionStrings["croestate_dbConnectionString"].ConnectionString;
SqlConnection Conn = new SqlConnection(ConnStr);
try
{
Conn.Open();
String sqlQuery = "INSERT INTO PozicijaObjekta VALUES(@Pozicija, @LinkzaViseoMjestu, @Regija)";
SqlCommand cmd = new SqlCommand(sqlQuery, Conn);
cmd.Parameters.AddWithValue("@Pozicija", txtPozicija.Text);
cmd.Parameters.AddWithValue("@LinkzaViseoMjestu", txtLinkZaViseoMjestu.Text);
cmd.Parameters.AddWithValue("@Regija", ddRegija.SelectedValue);
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Conn.Close();
Response.Write(ex.Message);
Wizard1.ActiveStepIndex = 0;
}
}
protected void Wizard1_NextButtonClick(object sender, WizardNavigationEventArgs e)
{
if (e.CurrentStepIndex == 0)
{
SaveDataDB();
}
if (e.CurrentStepIndex == 1)
{
//Register user into the database not hear because wizard have one step only
// SaveDataDB();
}
}
protected void CustomValidator1_ServerValidate(object source, ServerValidateEventArgs args)
{
if (ddRegija.SelectedIndex == 0)
args.IsValid = false;
}
protected void CustomValidatorPozicija_ServerValidate(object source, ServerValidateEventArgs args)
{
string ConnStr = ConfigurationManager.ConnectionStrings["croestate_dbConnectionString"].ConnectionString;
SqlConnection Conn = new SqlConnection(ConnStr);
try
{
Conn.Open();
String sqlQuery = "SELECT Pozicija FROM PozicijaObjekta WHERE Pozicija='" + txtPozicija.TemplateControl + "'";
SqlCommand cmd = new SqlCommand(sqlQuery, Conn);
SqlDataReader dr = cmd.ExecuteReader();
if (dr.Read() == true)
args.IsValid = false;
}
catch (Exception ex)
{
Conn.Close();
Response.Write(ex.Message);
Wizard1.ActiveStepIndex = 0;
}
}
}
View 10 Replies
View Related
Apr 24, 2004
Hi, I have a problem when I insert a date in a datetime field in a MSSQLServer.
That's my problem:
if the server is in english version, I have to insert date with this code:
DateTime.Today.ToString("MM/dd/yyyy")
instead if the server is in italian version, I have to insert date with this code:
DateTime.Today.ToString("dd/MM/yyyy")
Is there a way to insert a date in standard way, without knowing the server version?
bye and thanks in advance
View 3 Replies
View Related
Dec 17, 2004
Edited by SomeNewKid. Please post code between <code> and </code> tags.
This is probaly the easiest question you've ever read but here goes.
I have a simple checkbox value that i want to insert into the database but whatever i do it does not seem to let me.
Here is my code:
Sub AddSection_Click(Sender As Object, e As EventArgs)
Dim myCommand As SqlCommand
Dim insertCmd As String
' Build a SQL INSERT statement string for all the input-form
' field values.
insertCmd = "insert into Customers values (@SectionName, @SectionLink, @Title, @NewWindow, @LatestNews, @Partners, @Support);"
' Initialize the SqlCommand with the new SQL string.
myCommand = New SqlCommand(insertCmd, myConnection)
' Create new parameters for the SqlCommand object and
' initialize them to the input-form field values.
myCommand.Parameters.Add(New SqlParameter("@SectionName", SqlDbType.nVarChar, 50))
myCommand.Parameters("@SectionName").Value = Section_name.Value
myCommand.Parameters.Add(New SqlParameter("@SectionLink", SqlDbType.nVarChar, 80))
myCommand.Parameters("@SectionLink").Value = Section_link.Value
myCommand.Parameters.Add(New SqlParameter("@Title", SqlDbType.nVarChar, 50))
myCommand.Parameters("@Title").Value = Section_title.Value
If New_window.Checked = false Then
myCommand.Parameters.Add(New SqlParameter("@NewWindow", SqlDbType.bit, 1))
myCommand.Parameters("@NewWindow").Value = 0
else
myCommand.Parameters.Add(New SqlParameter("@NewWindow", SqlDbType.bit, 1))
myCommand.Parameters("@NewWindow").Value = 1
End If
If Latest_news.Checked = false Then
myCommand.Parameters.Add(New SqlParameter("@LatestNews", SqlDbType.bit, 1))
myCommand.Parameters("@LatestNews").Value = 0
else
myCommand.Parameters.Add(New SqlParameter("@LatestNews", SqlDbType.bit, 1))
myCommand.Parameters("@LatestNews").Value = 1
End If
If Partners.Checked = false Then
myCommand.Parameters.Add(New SqlParameter("@Partners", SqlDbType.bit, 1))
myCommand.Parameters("@Partners").Value = 0
else
myCommand.Parameters.Add(New SqlParameter("@Partners", SqlDbType.bit, 1))
myCommand.Parameters("@Partners").Value = 1
End If
If Support.Checked = false Then
myCommand.Parameters.Add(New SqlParameter("@Support", SqlDbType.bit, 1))
myCommand.Parameters("@Support").Value = 0
else
myCommand.Parameters.Add(New SqlParameter("@Support", SqlDbType.bit, 1))
myCommand.Parameters("@Support").Value = 1
End If
myCommand.Connection.Open()
' Test whether the new row can be added and display the
' appropriate message box to the user.
Try
myCommand.ExecuteNonQuery()
Message.InnerHtml = "Record Added<br>" & insertCmd
Catch ex As SqlException
If ex.Number = 2627 Then
Message.InnerHtml = "ERROR: A record already exists with " _
& "the same primary key"
Else
Message.InnerHtml = "ERROR: Could not add record, please " _
& "ensure the fields are correctly filled out"
Message.Style("color") = "red"
End If
End Try
myCommand.Connection.Close()
BindGrid()
End Sub
Any response would be appreciated
View 2 Replies
View Related
Sep 13, 2004
I have a colum of numbers, which datatype is nvarchar;
157853
4389
1999999999556
4568987
I need to insert a hyphen, 3 spaces from the right (to left) My numbers would look like this:
157-853
4-389
1999999999-556
4568-987
...do I use the rtrim function ?...what would the syntax look like ?
Thank you
View 3 Replies
View Related
Oct 7, 2004
I've just noticed some strange behavior that seems like a bug to me.
It's much easier to follow an example of it that to outright explain it, so here goes.
I have a table defined with a NOT NULL constraint on a column and a default clause:
-- DROP TABLE TestTable
CREATE TABLE TestTable ( TestField0 varchar(10), TestField1 varchar(10) NOT NULL DEFAULT ('a') )
I have a view defined on the table, in this example case, the view just mirrors the table one to one:
-- DROP VIEW TestView
CREATE VIEW TestView as SELECT TestField0, TestField1 FROM TestTable
So far so good, if I run this statement, it works as I would expect and inserts the value and the default goes into the other field:
INSERT INTO TestView (TestField0) SELECT 'test'
Now... If I add an INSTEAD OF trigger to the view, and have it perform the insert for me, I get an error with the same insert stmt:
-- DROP TRIGGER TestTrigger
CREATE TRIGGER TestTrigger ON TestView
INSTEAD OF INSERT AS
BEGIN
INSERT INTO TestTable (TestField0, TestField1)
SELECT TestField0, COALESCE(TestField1, 'X')
FROM inserted
END
Notice the trigger will ensure that a null value cannot be inserted into TestField1. If I run this insert stmt though I get an error:
INSERT INTO TestView (TestField0) SELECT 'test'
Server: Msg 233, Level 16, State 2, Line 1
The column 'TestField1' in table 'TestView' cannot be null.
Am I missing something or is this a bug?
Thanks
View 1 Replies
View Related
Jul 20, 2005
I have a stored procedure where I want to select all fields matchingthe query into another table. In addition, I want to add a commongroupID to each of the records that are being inserted into the secondtable.I can get the results that I want by using a temporary table but needto know if there is a way to do it directly..below is the code that uses the temporary table..CREATE TABLE #tempStore_DeliveryAddress ([AddressId] [int] IDENTITY (1, 1) NOT NULL ,[UserId] [int] NOT NULL ,[Title] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,[FirstName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL ,[SpouseName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL ,[MiddleName] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL ,[SpouseMiddleName] [varchar] (10) COLLATESQL_Latin1_General_CP1_CI_AS NOT NULL ,[LastName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL ,[SpouseLastName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_ASNOT NULL ,[Suffix] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,[SpouseSuffix] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL ,[Company] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL ,[Address1] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL ,[Address2] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL ,[Address3] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL ,[City] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,[State] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,[PostalCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL ,[Country] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL ,[ForeignFlag] [int] NULL CONSTRAINT[DF_Store_DeliveryAddress_ForeignFlag] DEFAULT (0),[Email] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,[Greeting] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL ,[FullName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL ,[ShortName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL ,[OptOut] [int] NULL CONSTRAINT [DF_Store_DeliveryAddress_OptOut]DEFAULT (0),[Modified] [datetime] NULL CONSTRAINT[DF_Store_DeliveryAddress_Modified] DEFAULT (getdate()),[Modifer] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULLCONSTRAINT [DF_Store_DeliveryAddress_Modifer] DEFAULT ('DBA'),[Created] [datetime] NULL CONSTRAINT[DF_Store_DeliveryAddress_Created] DEFAULT (getdate()),[Creator] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULLCONSTRAINT [DF_Store_DeliveryAddress_Creator] DEFAULT ('DBA'),[MailListID] [int] NULL ,CONSTRAINT [PK_Store_DeliveryAddress] PRIMARY KEY CLUSTERED([AddressId]) ON [PRIMARY]) ON [PRIMARY]INSERT INTO #tempStore_DeliveryAddress([UserId], [Title], [FirstName],[SpouseName], [MiddleName], [SpouseMiddleName], [LastName],[SpouseLastName], [Suffix], [SpouseSuffix], [Company], [Address1],[Address2], [Address3], [City], [State], [PostalCode], [Country],[ForeignFlag], [Email], [Greeting], [FullName], [ShortName], [OptOut],[Modified], [Modifer], [Created], [Creator])(SELECT [UserId], [Title], [FirstName], [SpouseName], [MiddleName],[SpouseMiddleName], [LastName], [SpouseLastName], [Suffix],[SpouseSuffix], [Company], [Address1], [Address2], [Address3], [City],[State], [PostalCode], [Country], [ForeignFlag], [Email], [Greeting],[FullName], [ShortName], [OptOut], [Modified], [Modifer], [Created],[Creator]FROM [ntmportal].[dbo].[Store_AddressBook]WHERE [AddressID] in (Select AddressID From Store_AddressesForGroupwhere AddressGroupID = 322))UPDATE #tempStore_DeliveryAddress set MailLISTID = 422INSERT INTO Store_DeliveryAddress([UserId], [Title], [FirstName],[SpouseName], [MiddleName], [SpouseMiddleName], [LastName],[SpouseLastName], [Suffix], [SpouseSuffix], [Company], [Address1],[Address2], [Address3], [City], [State], [PostalCode], [Country],[ForeignFlag], [Email], [Greeting], [FullName], [ShortName], [OptOut],[Modified], [Modifer], [Created], [Creator], [MailListID])(Select [UserId], [Title], [FirstName], [SpouseName], [MiddleName],[SpouseMiddleName], [LastName], [SpouseLastName], [Suffix],[SpouseSuffix], [Company], [Address1], [Address2], [Address3], [City],[State], [PostalCode], [Country], [ForeignFlag], [Email], [Greeting],[FullName], [ShortName], [OptOut], [Modified], [Modifer], [Created],[Creator], [MailListID]FROM #tempStore_DeliveryAddress)
View 2 Replies
View Related
