Hi All,
I am having trouble with a simplet INSERT statement. I want to insert a record. The field "dbid" is the primary key and should be autonumbering. What do I need to add to my code?
Dim myconnection As SqlConnection
myconnection = New SqlConnection()
myconnection.ConnectionString = _
ConfigurationManager.ConnectionStrings("infoNoticeDBConnectionString").ConnectionString
Dim strSQL As String = "INSERT INTO users " & _
"(dbid, infoid) VALUES (@dbid, @infoid)"
Dim dbComm As New SqlCommand(strSQL, myconnection)
dbComm.Parameters.Add("infoid", SqlDbType.NVarChar, 50, "@infoid")
Try
myconnection.Open()
dbComm.ExecuteNonQuery()
Catch ex As Exception
Response.Write(ex.Message)
Response.End()
Finally
If myconnection.State = ConnectionState.Open Then
myconnection.Close()
End If
End Try
Response.Write("A new record has been added")
Response.End()
My question: I'm sure I read somewhere that ideally you shouldn't use the auto-increment facility in a table's id field but should generate you're own when you insert. Does this make any sense to anyone or is it just some rubbish I read online somewhere.
Hullo!Doubt it matters too much, but I'll just start off by saying that I'm using C# ASP.NET 2.0 in Visual Studio 2005.I currently have a UID field entitled "ID", and I'd like to make it autonumbering so that users can input stuff into it through a simple web interface and not have to worry about the user's ID.Anyone know how I could go about doing this? I can't seem to find a way through VS2005's GUI, and I can't seem to find anything on the internet about doing it through an SQL statement.Thanks!
I am moving from Foxpro to SQL. I am little confused about one aspect dealing with multiple autonumbering columns.
In foxpro it will allow you to set your fields to "autonumber" so you can achieve having 3 or 4 fields for example with unique numbers, depending on the application function.
When I try to do this in SQL I can setup my first identity and PK and it autonumbers just fine, when I try to setup a second autonumber column with a unique I cant seem to get control of the identity to set the starting seed, Unless I select that column in the identity to yes, but it seems to disable the the first identity column I setup.
Can anyone tell what I missed or doing wrong ?
One last note, I am still using the Studio Manager Still learning the programming portion..
I created table with "id" field as "uniqueidentifier" (primary key).I connect via ODBC from MS-Acces.-->>> Don't know how to something like "Autonumber" in MS-Access.Let me know If any trigger or something like this is required.Please post some code of trigger if need.Until now I have only developed some native MS-Accessdatabases. I hope there is some solution about "Autonumbering records"in SQLServer.SqlServer 2000, Access Xp.Marek Bħbski
Gents,I have come into a system that uses a secondary table to generate (forwant of a better word) Identities.egcreate table myidents( name sysname not null, ident int not null)create procedure getnextident @table sysname, @ident int outputasbeginif not exists (select top 1 1 from myidents where name = @table)insert into myidents values (@table, 0)update myidentsset @ident = ident = ident + 1where name = @tableendnow, (ignoring for now the use of reserved words) the problem is thatthis is called frequently, from other procedures. Trouble is that thecalling procedures call it from within a transaction. We now have awickedly hot spot on this table, with frequent deadlocks.Is there any relatively quick fix for this? Some locking hints orwhatever.Or do we need to go and recode, moving this kind of thing outside thetransaction (which are all rather too long for my liking), and evencosidering using identity columns as a replacement?Thanks
I've been assigned to do the data access layer for an existing SQL database created by someone else. Amazingly enough, the primary table, the one containing all of the records that are central to the db and the whole project, has over 3000 entries and no unique identifer (autonumbered ID) column. The data in all of the other columns is repetitious so none of them can be used as a primary key. I have added a column called TaskAssignmentID, designated as an int data type. The goal is to somehow autofill this column with sequential numbers and then designate that column as the primary key. (don't you just love fixing other people's mistakes?) Any suggestions/examples on how to accomplish this the easiest, most efficient way?
For reference purposes, the table is called tbl_MCHS and the newly created column is called TaskAssignmentID. The data type of the column can be changed, if that would make it easier.
Tools available include Visual Studio 2005 (very familiar) and SQL Server 2005's SQL Server Management Studio (less familiar).
Things I am restricted from doing include blowing away the table and starting over or strangling the previous developer (I asked about both). :-)
Any and all suggestions, steps, or examples will be appreciated.
I've have a need with SQL Server 2005 (so I've no MERGE statement), I have to merge 2 tables, the target table has 10 fields, the first 4 are the clustered index and primary key, the source table has the same fields and index.Since I can't use the MERGE statement (I'm in SQL 2005) I have to make a double step operation, and INSERT and an UPDATE, I can't figure how to design the WHERE condition for the insert statement.
An insert statement was not inserting all the data into a table. Found it very strange as the other fields in the row were inserted. I ran SQL profiler and found that sql statement had all the fields in the insert statement but some of the fields were not inserted. Below is the sql statement which is created dyanmically by a asp.net C# class. The columns which are not inserted are 'totaltax' and 'totalamount' ...while the 'shipto_name' etc...were inserted.there were not errors thrown. The sql from the code cannot be shown here as it is dynamically built referencing C# class files.It works fine on another test database which uses the same dlls. The only difference i found was the difference in date formats..@totalamount=1625.62,@totaltax=125.62are not inserted into the database.Below is the statement copied from SQL profiler.exec sp_executesql N'INSERT INTO salesorder(billto_city, billto_country, billto_line1, billto_line2, billto_name,billto_postalcode, billto_stateorprovince, billto_telephone, contactid, CreatedOn, customerid, customeridtype,DeletionStateCode, discountamount, discountpercentage, ModifiedOn, name, ordernumber,pricelevelid, salesorderId, shipto_city, shipto_country,shipto_line1, shipto_line2, shipto_name, shipto_postalcode, shipto_stateorprovince,shipto_telephone, StateCode, submitdate, totalamount,totallineitemamount, totaltax ) VALUES(@billto_city, @billto_country, @billto_line1, @billto_line2,@billto_name, @billto_postalcode, @billto_stateorprovince, @billto_telephone, @contactid, @CreatedOn, @customerid,@customeridtype, @DeletionStateCode, @discountamount,@discountpercentage, @ModifiedOn, @name, @ordernumber, @pricelevelid, @salesorderId,@shipto_city, @shipto_country, @shipto_line1, @shipto_line2,@shipto_name, @shipto_postalcode, @shipto_stateorprovince, @shipto_telephone,@StateCode, @submitdate, @totalamount, @totallineitemamount, @totaltax)',N'@billto_city nvarchar(8),@billto_country nvarchar(13),@billto_line1 nvarchar(3),@billto_line2 nvarchar(4),@billto_name nvarchar(15),@billto_postalcode nvarchar(5),@billto_stateorprovince nvarchar(8),@billto_telephone nvarchar(3),@contactid uniqueidentifier,@CreatedOn datetime,@customerid uniqueidentifier,@customeridtype int,@DeletionStateCode int,@discountamount decimal(1,0),@discountpercentage decimal(1,0),@ModifiedOn datetime,@name nvarchar(33),@ordernumber nvarchar(18),@pricelevelid uniqueidentifier,@salesorderId uniqueidentifier,@shipto_city nvarchar(8),@shipto_country nvarchar(13),@shipto_line1 nvarchar(3),@shipto_line2 nvarchar(4),@shipto_name nvarchar(15),@shipto_postalcode nvarchar(5),@shipto_stateorprovince nvarchar(8),@shipto_telephone nvarchar(3),@StateCode int,@submitdate datetime,@totalamount decimal(6,2),@totallineitemamount decimal(6,2),@totaltax decimal(5,2)',@billto_city=N'New York',@billto_country=N'United States',@billto_line1=N'454',@billto_line2=N'Road',@billto_name=N'Hillary Clinton',@billto_postalcode=N'10001',@billto_stateorprovince=N'New York',@billto_telephone=N'124',@contactid='8DAFE298-3A25-42EE-B208-0B79DE653B61',@CreatedOn=''2008-04-18 13:37:12:013'',@customerid='8DAFE298-3A25-42EE-B208-0B79DE653B61',@customeridtype=2,@DeletionStateCode=0,@discountamount=0,@discountpercentage=0,@ModifiedOn=''2008-04-18 13:37:12:013'',@name=N'E-Commerce Order (Before billing)',@ordernumber=N'BRKV-CC-OKRW5764YS',@pricelevelid='B74DB28B-AA8F-DC11-B289-000423B63B71',@salesorderId='9CD0E11A-5A6D-4584-BC3E-4292EBA6ED24',@shipto_city=N'New York',@shipto_country=N'United States',@shipto_line1=N'454',@shipto_line2=N'Road',@shipto_name=N'Hillary Clinton',@shipto_postalcode=N'10001',@shipto_stateorprovince=N'New York',@shipto_telephone=N'124',@StateCode=0,@submitdate=''2008-04-18 14:37:10:140'',@totalamount=1625.62,@totallineitemamount=1500.00,@totaltax=125.62
Hello I have a problem with setting relations properly when inserting data using adonet. Already have searched for a solutions, still not finding a mistake... Here's the sql management studio diagram :
and that causes (at line 67):"The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Question_SurveyTemplate". The conflict occurred in database "ankietyzacja", table "dbo.SurveyTemplate", column 'id'. The statement has been terminated. at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount) at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount) at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping) at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping) at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable) at AnkietyzacjaWebService.Service1.createSurveyTemplate(Object[] o) in J:\PL\PAI\AnkietyzacjaWebService\AnkietyzacjaWebServicece\Service1.asmx.cs:line 397"
Could You please tell me what am I missing here ? Thanks a lot.
This problem is being seen on SQL 2005 SP2 + cumulative update 4
I am currently successfully using the output clause of an insert statement to return the identity values for inserted rows into a table variable
I now need to add an "instead of insert" trigger to the table that is the subject of the insert.
As soon as I add the "instead of insert" trigger, the output clause on the insert statement does not return any data - although the insert completes successfully. As a result I am not able to obtain the identities of the inserted rows
Note that @@identity would return the correct value in the test repro below - but this is not a viable option as the table in question will be merge replicated and @@identity will return the identity value of a replication metadata table rather than the identity of the row inserted into my_table
Note also that in the test repro, the "instead of insert" trigger actually does nothing apart from the default insert, but the real world trigger has additional code.
To run the repro below - select each of the sections below in turn and execute them 1) Create the table 2) Create the trigger 3) Do the insert - note that table variable contains a row with column value zero - it should contain the @@identity value 4) Drop the trigger 5) Re-run the insert from 3) - note that table variable is now correctly populated with the @@identity value in the row
I need the behaviour to be correct when the trigger is present
GO /************************************************ 2) - Create the trigger ************************************************/ CREATE TRIGGER [dbo].[trig_my_table__instead_insert] ON [dbo].[my_table] INSTEAD OF INSERT AS BEGIN
INSERT INTO my_table ( forename, surname) SELECT forename, surname FROM inserted
END
/************************************************ 3) - Do the insert ************************************************/
INSERT INTO my_table ( forename , surname ) OUTPUT inserted.my_table_id INTO @my_insert VALUES( @forename , @surname )
select @@identity -- expect this value in @my_insert table select * from @my_insert -- OK value without trigger - zero with trigger
/************************************************ 4) - Drop the trigger ************************************************/
drop trigger [dbo].[trig_my_table__instead_insert] go
/************************************************ 5) - Re-run insert from 3) ************************************************/ -- @my_insert now contains row expected with identity of inserted row -- i.e. OK
Hi All, I've looked through the forum hoping I'm not the only one with this issue but alas, I have found nothing so I'm hoping someone out there will give me some assistance. My problem is the case statement in my Insert Statement. My overall goal is to insert records from one table to another. But I need to be able to assign a specific value to the incoming data and thought the case statement would be the best way of doing it. I must be doing something wrong but I can't seem to see it.
Here is my code: Insert into myTblA (TblA_ID, mycasefield = case when mycasefield = 1 then 99861 when mycasefield = 2 then 99862 when mycasefield = 3 then 99863 when mycasefield = 4 then 99864 when mycasefield = 5 then 99865 when mycasefield = 6 then 99866 when mycasefield = 7 then 99867 when mycasefield = 8 then 99868 when mycasefield = 9 then 99855 when mycasefield = 10 then 99839 end, alt_min, alt_max, longitude, latitude ( Select MTB.LocationID MTB.model_ID MTB.elevation, --alt min null, --alt max MTB.longitude, --longitude MTB.latitude --latitude from MyTblB MTB );
The error I'm getting is: Incorrect syntax near '='.
I have tried various versions of the case statement based on examples I have found but nothing works. I would greatly appreciate any assistance with this one. I've been smacking my head against the wall for awhile trying to find a solution.
I'm quite stuck with this:I have an import table called ReferenceMatchingImport which containsdata that has been sucked from a data submission. The contents ofthis table have to be imported into another table ExternalReferencewhich has various foreign keys.This is simple but one of these keys says that the value inExternalReference.CompanyRef must be in the CompanyReference table.Of course if this is an initial import then it will not be so as partof my script I must insert a new row into CompanyReference andpopulate ExternalReference.CompanyRef with the identity column of thistable.I thought a good idea would be to use an SP which inserts a new rowand returns @@Identity as the value to insert. However this doesn'twork as far as I can tell. Is there a approved way to perform thissort of opperation? My code is below.Thanks.ALTER PROCEDURE SP00ReferenceMatchingImportAS/*Just some integrity checking going on here*/INSERT ExternalReference(ExternalSourceRef,AssetGroupRef,CompanyUnitRef,EntityTypeCode,CompanyRef, --this is the unknown ref which is returned by the spExternalReferenceTypeCode,ExternalReferenceCompanyReferenceMapTypeCode,StartDate,EndDate,LastUpdateBy,LastUpdateDate)SELECT rmi.ExternalDataSourcePropertyRef,rmi.AssetGroup,rmi.CompanyUnit,rmi.EntityType,SP01InsertIPDReference rmi.EntityType, --here I'm trying to run thesp so that I can use the return value as the insert value1,1,GETDATE(),GETDATE(),'RefMatch',GETDATE()FROM ReferenceMatchingImport rmiWHERE rmi.ExternalDataSourcePropertyRef NOT IN (SELECT ExternalSourceRefFROM ExternalReference)
I was wondering if any SQL people out there know if i can INSERT INTO 2 tables at the same time? I havent found a yes to the question so i thought i would throw it out there and see what everyone has to say. word
I have put together the following code, but am getting a syntax error on the Insert Into statement. Could someone kindly tell me what I am doing wrong? Thanks 1 Protected Sub SubmitButton2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles SubmitButton2.Click2 authAndSaveToDB()3 End Sub4 Private Sub authAndSaveToDB()5 On Error GoTo errhand6 If connectToDB() Then 'connection to database was successful7 'insert candidate in the database 8 insertCandidate(ddlPositions.Text, txtName.Text, txtBio.Text, conn)9 Response.Redirect("FileUpload.aspx")10 11 Else 12 Response.Write("<font color = red><strong>Database connection failure. Please try again in a few minutes or contact system administrator.</strong></font>")13 End If 14 closeDB() 'end the connection to the database 15 Exit Sub 16 errhand:17 Response.Write("<br>ERROR: " & Err.Description & "<br>")18 End Sub19 Private Sub insertCandidate(ByVal strPositions As String, ByVal strCandidate As String, ByVal strBiography As String, ByVal myconn As OleDb.OleDbConnection)20 Dim cmd As OleDb.OleDbCommand21 Dim strSQL As String 22 23 strSQL = "INSERT INTO Position (Positions,Candidate,Biography) VALUES ('" & _ 24 strPositions & "'," & _ 25 "'" & strCandidate & "'," & _ 26 "'" & strBiography & "')" 27 cmd = New OleDb.OleDbCommand(strSQL, myconn)28 cmd.ExecuteNonQuery()29 End Sub30 End Class
Hi, I am retriving the value time from my database and i have the convert function on it CONVERT (varchar, Time, 8) When i edit this and try and insert it back to the database i am losing the information. I am not sure how the insert statement shoulld read in order to do this. Thanks Mike
Hi forum, currently I insert a mobile phone number into colomn OK but what I need to do is add +61 as a prefix.Users provide 055 555 555, I want to store number as +61 55 555 555 (note this also drops the 0) my SQL skills a little lacking so appreciate all good info, cheers P
I am having difficulties with my first hand coded insert statement. The record inserts BUT the first item VALUE is selected for all the drop down lists ( 3 of them are optional) I have Prerenders on the page to insert a null value at the top of the list. For the dropdown that is mandatory it only enter first item. Thats without specifying type. As soon as I specify type I get - Input string not in correct format - doesn't actually tell me which one!!!!!! All char types match the database.
Here is my insert:
Sub Add_Click(sender as object, e as EventArgs)
Dim conBooks As SqlConnection Dim strInsert As String Dim cmdInsert As SqlCommand
Hi All, I am trying to insert data into table1 by getting data from table2, table3 and looking whether the data is already not exist in table1. Here is my code... insert into table1 (first_name, last_name, user_login, email, organization_name) values (select ru.firstname, ru.lastname, ru.UserID, ru.EmailAddress, ru.BusinessName from table2 ru, table3 wepsUsers where ru.id = wepsUsers.user_id and not exists (select user_login from table1 where user_login = ru.UserID))
When I excute the code, I am getting the following error..... Server: Msg 156, Level 15, State 1, Line 3Incorrect syntax near the keyword 'select'.Server: Msg 170, Level 15, State 1, Line 7Line 7: Incorrect syntax near ')'. But, If execute the following code.... select ru.firstname, ru.lastname, ru.UserID, ru.EmailAddress, ru.BusinessName from table2 ru, table3 wepsUsers where ru.id = wepsUsers.user_id and not exists (select user_login from table1 where user_login = ru.UserID).... I am able to see the values.
Can any one shade on my code? and please let me know, where I am doing wrong. I am appreciate your help.
Is there anyway that we can use insert into statement to insert data into the first row of a table, or initialize the column with the new inserted data.
I am trying to do an insert in a particular View...one of the field has got an 'Apostrophe'.(Ex: SQl Server's). When i do this i get an error. the system does not allow me to insert.... is there any way i can do an Insert with that...???
How do I 1.) take two seperate columns and combine the data 2.) take that combined data and populate a third column.
Here is an example of what I've been trying to do:
Insert INTO Table_1 (Column1) Select Column2 + Column3 From Table_1
I continue to get this error message:
Server: Msg 515, Level 16, State 2, Line 1 Cannot insert the value NULL into column 'Column_2', table 'DB1.dbo.Table_1'; column does not allow nulls. INSERT fails. The statement has been terminated.
If some one could please give me a general script that could do this I would appreciate it. I'm new to using SQL and I'm a little bit lost.
I need an insert statement that will grab the maximum of TimeGenerated from the Events table and insert it into LastEventTime in the Host Table. But it has to be inserted based on the relationship of SystemNetName.
This is what I had so far but didn't work: Insert into host(LastEventTime) Select(Max(TimeGenerated) ) from Events Where Events.SystemNetName = Host.SystemNetName
Is is possible to use a Where Clause in a Insert statement in MS SQL? I keep getting a syntax error near 'Where'. Is there something wrong with this?
Insert INTO CPU (CPUNumber, Name, MaxClockSpeed, SystemNetName) Values('CPU2', 'Intel', '2392', 'TWEETY') Where 'CPU2' NOT IN (Select CPUNumber from CPU)
I want to insert into the cpu table as long as what I am inserting is not already in the table.
Can someone help me parse this oracle insert statement into SQL Server please?
insert into notify_template (subject, body, priority) values ( ' Budgeted time exceeded for task [18040] ', ' Actual Time exceeds Budgeted time for the following task' || chr(10) || chr(10) || 'Task Name : [18040] Root Program Name : [18049] Actual Days : [2000000100] Budgeted Days : [2000000101] Estimated Time : [2000000102]' || chr(10) || chr(10) || ' The following resource(s) in the above task have exceeded the budgeted time:' || chr(10) || chr(10) || ' [2000000784]' || chr(10) || chr(10) || 'Click here : [12865] ', 2)
i am writing a trigger that inserts from one table to the next. i have an issue with the table being inserted into have 2 more columns than the one being inserted from. Here is the trigger just in case
CREATE TRIGGER [Insert40801] ON [dbo].[tPA10801] FOR INSERT
I need to insert over 600 similar records in a table. The only way I know is to write insert into <table> command 600 times. Is there another way which is easier?
I was wondering how we can insert new records into a table which has records already.
The first col. is an identity col and a primary key. I have 3000 records and the first col. has unique id but then I need to need to insert new records without inserting all records again.
