Expert On SQL Queries Needed For Help Updating Record.
Mar 23, 2008
Hi, I need help with updating a record. When I run the following code underneath the update button, the record does not update and I get no error message. Anyone have any ideas?
Code Snippet
Dim ListingID As String = Request.QueryString("id").ToString
Dim Description As String = DescriptionTB.Text
Dim PlaceName As String = PlaceNameTB.Text
Dim Location As String = LocationTB.SelectedValue
Dim PropertyType As String = LocationTB.SelectedValue
Dim Price As Integer = PriceTB.Text
Dim con As New SqlConnection(ListingConnection)
Dim sql As String = "Update Listings SET Description = ?, PlaceName = ?, Location = ?, PropertyType = ?, Price = ? WHERE ListingID = ?"
Dim cmd As New SqlCommand(Sql, con)
cmd.Parameters.AddWithValue("Description", Description)
cmd.Parameters.AddWithValue("PlaceName", PlaceName)
cmd.Parameters.AddWithValue("Location", Location)
cmd.Parameters.AddWithValue("PropertyType", PropertyType)
cmd.Parameters.AddWithValue("Price", Price)
Try
con.Open()
cmd.ExecuteNonQuery()
con.Close()
Catch ex As Exception
View 4 Replies
ADVERTISEMENT
Feb 20, 2001
Has anyone any suggestions how I might find the most recent of a series of dates from different tables? I am joining tables which all have a date_altered field and I am only interested in displaying the most recent of these. Eg.
select a.x,b.y,c.z, a.date_altered, b.date_altered, c.date_altered
from a inner join b on (a.id=b,id) inner join c on (b.id=c.id)
What I would like is in effect a function Highest() Eg.
select a.x,b.y,c.z, highest(a.date_altered, b.date_altered, c.date_altered) as last_alteration
from a inner join b on (a.id=b,id) inner join c on (b.id=c.id)
I am using SQL Server 7 so cannot write myself a function to achieve this - frustrating, as it is something I could have done 4 years ago when I used mostly Oracle.
Many thanks
View 2 Replies
View Related
Dec 20, 2004
Idea here is to output results of a stored proc to a text file..
I am trying to use this query to execute a stored proc which in turn accepts 3 parameters. Can some one help me as I am getting syntax errors when I try this way:
EXEC sp_querytofile 'MyDB', "exec sp_myproc ''11/03/04'', ''ABK'', ''TFC'", 'c:sp_myproc_out.txt'
I tried different combination of quotes but couldn't make it work! Any one to help?
--------code for sp_QueryToFile---------
Create PROCEDURE sp_QueryToFile
( @db sysname,
@query VARCHAR(1000),
@file VARCHAR(255)
)
AS
SET NOCOUNT ON
SET @Query = 'SET NOCOUNT ON ' + @Query
EXECUTE ('master..xp_cmdshell ''osql -w8000 -S sqldev14corpappsdev -r -s" ", -h-1 -o' + @file + ' -d'+ @db + ' -Q"' + @query + '" -E''')
/* Procedure Ends */
View 2 Replies
View Related
Sep 10, 2004
I know, it's a job posting site, but our company is urgently looking for an expert senior level sql server DBA who can be rated as 9.5 out of 10 in sql server dba activities who are hard to find! Top hourly pay on a long contract in East Coast! so anyone interested? please rush resume to MessageDBfan@yahoo.com
View 1 Replies
View Related
Sep 27, 2004
Hi all,
We found a SQL problem when searching in a large communty (GCN). Please, we need someone how can help with this problem. It's urgent.
For details see post:
Page 1: http://www.asp.net/Forums/ShowPost.aspx?tabindex=1&PostID=693806
Page 2: http://www.asp.net/Forums/ShowPost.aspx?tabindex=1&PostID=693806&PageIndex=2
Thanks in advance, Tomas.
View 1 Replies
View Related
Sep 3, 2006
I have done a couple of little projects in VB express and was pleased with the results. Now I have something bigger in mind and need to know if VB express version of SQL will support merge replication. If not will the full version support it?
Thanks
Dave
View 3 Replies
View Related
Apr 7, 2008
Hi,
We're running a Sage CRM install with a SQL Server 2000 database at the back end. We're using the Sage web services API for updating data and a JDBC connection to retrieve data as it's so much quicker.
If I retrieve a record using the JDBC connection and then try and update the same record through the web services, the query times out as if the record is locked for updates. Has anyone experienced anything similar or know what I'm doing wrong? If I just use DriverManager.getConnection() to establish the connection instead of the datasource, and then continue with the same code I don't get these record locking problems. Please find more details below.
Thanks,
Sarah
The JDBC provider for the datasource is a WebSphere embedded ConnectJDBC for SQL Server DataSource, using an implementation type of 'connection pool datasource'. We are using a container managed J2C authentication alias for logging on.
This is running on a Websphere Application Server v6.1.
Code snippet - getting the record thru JDBC:
DataSource wsDataSource = serviceLocator.getDataSource("jdbc/dsSQLServer");
Connection wsCon = wsDataSource.getConnection();
// wsCon.setAutoCommit(false); //have tried with and without this flag - same results
Statements stmt = wsCon.createStatement();
String sql = "SELECT * FROM Person where personID = 12345";
ResultSet rs = stmt.executeQuery(sql);
if(rs.next()){
System.out.println(rs.getString("lastName"));
}
if (rs != null){
rs.close();
}
if (stmt != null) {
stmt.close();
}
if (wsCon != null) {
wsCon.close();
}
View 1 Replies
View Related
Sep 7, 2007
priceStockID productID supplierID price
1 1 1 100
2 1 2 110
3 2 1 10
4 2 3 20
5 3 1 30
6 3 2 20
7 3 3 15
8 4 1 40
how can I get the priceStockID for the lowest price of each product in the most efficient way
the result table should be like below
priceStockID productID supplierID price
1 1 1 100
3 2 1 10
7 3 3 15
8 4 1 40
View 2 Replies
View Related
Jan 22, 2008
I am using a poker application that imports all played hands into a PostgreSQL database. I'm trying to write some queries for that database so I can make good use of the data. I figure PostgreSQL and SQL server probably have similar language. And I know these forums are great. So I'm posting here for help.
This is a working code snippet that retreives three properties of a player:
Code Snippet
SELECT sum(totalhands) AS HANDS, sum(vpiphands) AS VPIPHANDS, sum(pfrhands) AS PFRHANDS
FROM compiledplayerresults WHERE compiledplayerresults_id in (
SELECT compiledplayerresults_id FROM compiledresults WHERE play
SELECT player_id FROM players WHERE playername='PLAYERNAME'));
This is all I can do with my skills right now. However I very much want to learn to query for more things:
- I'd like to be able to make a query for several players at the same time
- I'd like to be able to get the aggregate properties for all players who are not PLAYERNAME1 or PLAYERNAME2
- I'd like to be able to get the aggregate vpiphands property from all players whose totalhandsis > 100 and < 1000.
I hope you can help me with that. If you can, that will make me very happy and grateful! Thanks!
View 4 Replies
View Related
Jan 5, 2004
I have two tables in seperate dbs that I need to match up users and update the passwords in the second db table. I know how to match up the data and I'm sure I can do this using a cursor but I was wondering if there is a way I can do this with an update statement. Any suggestions(including telling me I'm stuck using a cursor) would be great.
Thanks ahead of time
EDIT: Nevermind, figured it out :D Thanks anyway.
View 1 Replies
View Related
Jun 1, 2007
Hi,Ive got a class in my code which contains data from several tables. A user profile has a single record in a user_profile tables, for each of these records there is several records in a another table, which itself has several records in a tags table.So far the save method on the class saves the record to the user profile table, then loops through the next two tables info to insert each record. I want to decrease the number of trips to the database and am wondering if anyone can advise on the best way to do this.I am considering using a dataset to retrieve and store the records, instead of a datareader Im using currently. Then when Im done altering the data in the dataset (all three tables) I can commit all the changes in one database transaction?? I have been avoiding datasets due to the fact datareaders are apparently faster.My other option is to try make three calls to the database, one per table, updating my stored procedures to accept arrays of data using XML?? Can anyone tell me what is the best option for me?Thanks,C
View 5 Replies
View Related
Nov 17, 2004
Hello, I'm new to SQL, I need to HtmlEncode a column in all my records in a table, how can i construct my sql string? I have the following...
Cmd = "UPDATE articulos SET DescripcionCorta = HtmlEncode(DescripcionCorta) WHERE codigo='x'"
All I need is to Encode my "DescripcionCorta" Field.
Regards.
Roberto.
View 6 Replies
View Related
Jun 9, 2008
Hi all, I'm new to SQL and have been trying to update a record in a table, something I imagine would be quite simple. The record holds a INT value and I wish to test the value to see if it is greater than 70, if it is....reset the value to 1
I have tried various methods, aiming at using a stored procedure that will run once a day at midnight.
Any help would be great.
Abbo
View 4 Replies
View Related
Sep 23, 2007
Hello
I need an alarm or raise an event from SQL Server after updating to get it in .net.
I use SQL Server 2005 Express Edition and vb.net2 for my programming language.
Suppose that in a windows form I have a grid and I'm working with this page
another client is working with this same page .He is editing the information of
a record of a grid and save it in database so I want to give an alarm or an event
that raise from SQL Server (for example in a trigger) , what can I do?
Thanks.
View 3 Replies
View Related
Mar 23, 2008
Hi, I have a problem, it is that when I try to update a record in my SQL server database, it is not updated and I recieve no error messages. This is the code behind the update button. The stored procedure is "sqlupdate".
Code Snippet
Dim ListingID As String = Request.QueryString("id").ToString
Dim con As New SqlConnection(ListingConnection)
Dim cmd As New SqlCommand("sqlupdate", con)
cmd.CommandType = CommandType.StoredProcedure
Dim id As SqlParameter = cmd.Parameters.Add("@id", SqlDbType.UniqueIdentifier)
id.Direction = ParameterDirection.Input
id.Value = ListingID
Dim PlaceName As SqlParameter = cmd.Parameters.Add("@PlaceName", SqlDbType.VarChar)
PlaceName.Direction = ParameterDirection.Input
PlaceName.Value = PlaceNameTB.Text
Dim Location As SqlParameter = cmd.Parameters.Add("@Location", SqlDbType.VarChar)
Location.Direction = ParameterDirection.Input
Location.Value = LocationTB.Text
Dim PropertyType As SqlParameter = cmd.Parameters.Add("@PropertyType", SqlDbType.VarChar)
PropertyType.Direction = ParameterDirection.Input
PropertyType.Value = PropertyTypeTB.Text
Dim Description As SqlParameter = cmd.Parameters.Add("@Description", SqlDbType.VarChar)
Description.Direction = ParameterDirection.Input
Description.Value = DescriptionTB.Text
Try
con.Open()
cmd.ExecuteNonQuery()
con.Close()
Catch ex As Exception
End Try
View 3 Replies
View Related
Apr 10, 2008
I'm using VB.Net 2008 with SQL Compact 3.5. After trying forever, I finally got my app to open a retrieve a record from a table. Now, when I try to update the record, I get an error on the ExecuteNonQuery statement. The error says I am "attempting to read or write protected memory". The code works perfectly with an Access database.
Here is the code I am using:
Dim objConnection As Data.OleDb.OleDbConnection
Dim objCommand As Data.OleDb.OleDbCommand
Dim sConnection As String
Dim sTableName As String
Try
'Set Table Name
sTableName = "Settings"
'Build SQL and Connection strings
'sConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mstrDataPath
sConnection = "Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=" & mstrDataPath
objConnection = New Data.OleDb.OleDbConnection(sConnection)
'Create the UpdateCommand
objCommand = New Data.OleDb.OleDbCommand("UPDATE " & sTableName & " SET " & _
"OwnerName = ?, " & _
"Address1 = ?, " & _
"Address2 = ?, " & _
"City = ?, " & _
"State = ?, " & _
"ZipCode = ?, " & _
"Phone = ?, " & _
"EmailAddress = ? ", objConnection)
objCommand.Parameters.Add(New Data.OleDb.OleDbParameter)
objCommand.Parameters(0).Value = mstrOwnerName
objCommand.Parameters.Add(New Data.OleDb.OleDbParameter)
objCommand.Parameters(1).Value = mstrAddress1
objCommand.Parameters.Add(New Data.OleDb.OleDbParameter)
objCommand.Parameters(2).Value = mstrAddress2
objCommand.Parameters.Add(New Data.OleDb.OleDbParameter)
objCommand.Parameters(3).Value = mstrCity
objCommand.Parameters.Add(New Data.OleDb.OleDbParameter)
objCommand.Parameters(4).Value = mstrState
objCommand.Parameters.Add(New Data.OleDb.OleDbParameter)
objCommand.Parameters(5).Value = mstrZipCode
objCommand.Parameters.Add(New Data.OleDb.OleDbParameter)
objCommand.Parameters(6).Value = mstrPhone
objCommand.Parameters.Add(New Data.OleDb.OleDbParameter)
objCommand.Parameters(7).Value = mstrEmailAddress
objConnection.Open()
objCommand.ExecuteNonQuery()
objConnection.Close()
objCommand.Dispose()
UpdateRecord = True
Catch objException As Exception
UpdateRecord = False
MessageBox.Show(objException.ToString, "Error")
End Try
View 21 Replies
View Related
Mar 7, 2004
hi Friends...
I have got a ordermaster table in which the orderid field is autoincremental.Now I also have an Ordercode field which is a combination of [current (orderid)-LICON)]
eg 1-LICON ..
I achieve this using the following query ...
insert into ordermaster(ordercode,orderamt,orderdate) select cast(max(orderid)+1 as varchar)+'-LICON' as ordercode,1000,'3/6/2004' from ordermaster
But I think the above query could lead to an inconsistent state ....If say following occurs :
1) USER1 reads max orderid as 1
2) User2 also reads max orderid as 1
3)USER 1 inserts record with ordercode as 2-LICON.
4)USER 2 will also insert record for ordercode as 2-LICON since max orderid read by USER2 is also the same as USER1.
How do I avoid this....I know I there are LOcktypes. But aren't locktypes used when we are updating same records....
Please help me out .I really need help in ths context
View 2 Replies
View Related
Dec 4, 2006
I have an access 2003 Data access page published to the web that gets its data successfully from an MS SQL 2005 server. I need the "page when it opens" to display a new / blank record (not the first record in the table as it does by default) any pointers would be nice thanks Pete...
View 6 Replies
View Related
Jan 27, 2008
Hello, i currently have a gridview component displaying data from a SQLSERVER2005 database. I have created an 'edit' hyperlink on each record so that when pressed, the primary key of a record is sent in the querystring to another page when pressed. example http://localhost/Prog/EditAppointment.aspx?AppointmentId=1
In the edit page, i have dragged and dropped a sqldatasource that contains the select and update statements. i would now like to use textboxes and dropdowns to display the data for the particular record selected (in this case appointmentid=1). however i dont want to use a formview or automated component, just to drag and drop the textboxes myself. how do i bind the textboxes in the HTML code to the data from the select statement? My current select parameters look as follows.
<SelectParameters>
<asp:QueryStringParameter Name="AppointmentId" QueryStringField="AppointmentId" Type="Int32" />
<asp:ControlParameter ControlID="Textbox1" Name="PatientNo" PropertyName="Text" Type="String" />
</SelectParameters>
Perhaps there is an error in my select parameters or does the problem lay elsewhere? Any help would be appreicated.
Thanks,
James.
View 1 Replies
View Related
Oct 1, 2004
The process of adding a column with DEFAULT (0) to a table that has 15million records takes a despicable amount of time (too much time) and the transaction log of the database grew to an unacceptable size. I would like to accomplish the same task using this procedure:
·Add the column to the table with null value.
·Loop through the table (500000 records at a time) and SET the value in the newly added column to 0.
·Issue a commit statement after each batch
·Issue a checkpoint statement after each batch.
·Alter the table and SET the column to NOT Null DEFAULT (0)
Here is my Sample script
ALTER TABLE EMPLOYEE ADD EZEVALUE NUMERIC (9,6) NULL
Go
Loop
UPDATE EMPLOYEE SET EZEVALUE = 0
Commit Tan
CHECKPOINT
END (Repeat the loop until the rows in EMPLOYEE have the value 0)
Go
ALTER TABLE EMPLOYEE ALTER COLUMN EZEVALUE NUMERIC (9,6) NOT NULL DEFAULT (0)
My problem is with the loop section of this script. How do I structure the loop section of this script to loop through the employee table and update the EZEVALUE column 500000 rows at a time, issue a Commit Tran and a CHECKPOINT statement until the whole table has been updated. Does anyone out there know how to accomplish this task? Any information would be greatly appreciated.
Thanks in advance
View 5 Replies
View Related
Sep 24, 2007
Hi People,
hope someone can help me out here with a little problem.
Basically i've go a asp.net page which has a listbox on. This list box is populated from a SQL database table with the datetime of the a selected field. Thus in the list box you get a list of strings looking like this "24/09/07 12:58"
Also on the page is a submit button, and some other editing textboxes. The main issue here is the when the submit button is used i get the currently selected listbox timedate string and then pass this along with other items to update a record in the database based on the datetime in the listbox control.
Below is how i get the string from the listbox control
Dim except_time As DateTime
except_time = DropDownList1.SelectedValue
The expect_time is then passed to store procedure along with some other vars, which looks like this
-- =============================================-- Author: Lee Trueman-- Create date: 1st Sept 2007-- =============================================CREATE PROCEDURE [dbo].[spExcept_UpdateData] -- Add the parameters for the stored procedure here @validated bit, @update_time datetime, @except_time datetimeASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON
-- Insert statements for procedure here UPDATE exceptions SET validated = @validated, update_time = @update_time WHERE (except_time = @except_time)END
So validated and update_time should be updated when except_time is equal to @except_time
My problem is that the database never updates. If i debug the asp.net page the watch var shows the datetime in US format (I.e "09/24/07 12:58"), if this is true then this would explain why when it is passed to the stored proc nothing gets updated, as there would not be a date match.
can anyone see any silly mistakes i'm doing here ????
View 2 Replies
View Related
Oct 18, 2004
I am trying to write a stored procedure that updates a value in a table based on the sort order. For example, my table has a field "OfferAmount". When this field is updated, I need to resort the records and update the "CurrRank" field with values 1 through whatever. As per my question marks below, I am not sure how to do this.
Update CurrRank = ??? from tblAppKitOffers
where appkitid = 3 AND (OfferStatusCode = 'O' OR OfferStatusCODE = 'D')
ORDER BY tblAppKitOffers.OfferAmount Desc
All help is greatly appreciated.
View 2 Replies
View Related
Sep 30, 2004
I need to update one row in a SQL Server 6.5 DB from a row in SQL 2000 server DB. What would be the best way to do this?
I have my 2000 server defined as a Remote Server in 6.5, however I get the error message:
contains more than the maximum number of prefixes. The maximum is 2.
View 9 Replies
View Related
Aug 19, 2006
I created a package in SSIS to export data from mutiple SQL server tables to a single flat file. Once the export is completed i need to update 2 tables ( a batch table and a history table) In the batch table I need to insert a record with batch # (system generated),batch date, status( success/failed), record count( no of records in the flat file), batch filename). In the history table, i need to insert a record for each of the rows in the flat file with the foll info. batch number,datetime,status(success/failed)
My question is how do I get the batch status,record count, batchfilename for batch table update and how do i get to update the history table.
BTW, i am executing this package as a SQL Server Agent job.
I am new to Integration Services and feel lost.
Any help ?
View 8 Replies
View Related
Dec 7, 2006
Hi,
I am currently using VC++/Cli 2005. In a project, I'm using a DataGridView control to show records from a SQL Express 2005 table. Instead of updating a specific item directly within DataGridView control, I would like to open a new form and allow user to update selected record/item within that form. The reason to update this way is conditionned by the fact that I have 3 levels of detail as following:
Level 0 Level 1 Level 2
1:N 1:N
Furniture --> Component --> Component
You all understand that update form of Level1 will/must include, as Level0 do, another DataGridView control to display/detail all related items issued from next level. This explains why I can't allow user to update Level 1 directly from DataGridView control.
I've searched in MSDN and even bought a few books on subject but unfortunately I found nothing on how to do it this way. All articles on DataGridView control were only showing how to update record directly from control.
My approach, I think, would be to transmit to my level1 updating form, as a single parameter, the selected DataRow object (or a brand new one if currently adding) issued from DataGridView and let user update it's content. When User finally leaves level0 update form, then I presume that DataGridView corresponding table would be automatically updated according to DataGridView's content.
What would be the proper way to do it? I would certainly appreciate to hear you view on this.
Also, what can I do if I want to refresh DataGridView's content when coming back from update form. Is it done automatically? I would certainly be sure that it reflects the reality, not only when I update it myself but also especially when other users could concurrently update same records?
Thanks in advance,
Stéphane
View 7 Replies
View Related
Dec 18, 2007
HI all,
I have a windows application which runs a process,
I am updating database column "Status" with Processing when the application is running, and on completion I update it with Staus="Completed" or in case I close the application
I update db with Status="Interupted" .
I have problem that in case while proces is running, power supply or system turns off, the db Status="Processing", but in actual it is interupted.
How will i update?
Please help.
View 1 Replies
View Related
Nov 14, 2007
Hi,
Inside a single transaction I'm inserting/updating multiple records into multiple tables, in this order:
table 1 record 1
table 2 record 1
table 3 record 1
table 1 record 2
table 2 record 1
table 1 record 3
table 2 record 3
table 3 record 3
Now I'm getting an unspecified error on a certain table:
Unspecified error [ 3,-1300704038,-1834095882,activitypointerBase,x lock (x blocks),PAG (idx): 1078 ]
From msdn I see that:
PAG (idx) means a lock on an index page.
x lock means an exclusive lock:
Indicates a data modification, such as an insert, an update, or a deletion. Ensures that multiple updates cannot be made to the same resource at the same time. (I assume that multiple updates within the SAME transaction can be made, only multiple updates from different transaction cannot be made, right?)
I cannot find any reference to this error message and don't know what the numbers mean. Maybe it relates to data that can be found in the sys.lock_information table like explained here, http://technet.microsoft.com/en-us/library/ms172932.aspx, but I'm not sure.
Furthermore, the sys.lock_information table is empty. I haven't been able to reproduce the problem myself. I only received an error log and the database to investigate it.
So, does anybody have an idea what this error message means and what I can do to troubleshoot this?
Thanks,
Jeffry
View 3 Replies
View Related
Jun 14, 2002
I am looking into a position requiring "SQL PLUS Expert" - anbody out there heard of this - is it a querying tool a la query analyzer of PL SQL.
View 2 Replies
View Related
Jun 14, 2002
I am looking into a position requiring a "SQL PLUS Expert" - anybody out ther heard of this - is it a querying tool a la query analyzer or PL SQL ?
View 1 Replies
View Related
Dec 11, 2006
Hello,
Lets just say that I have really only logged into phpmyadmin once and messed up my forum.
What I was trying to do? Install a shoutbox (chatroom type software).
When I logged into phpmyadmin I clicked the phpbb_config tab on the left hand side. Then I clicked the SQL tab and pasted the code required for the shoutbox accordingly and hit the "Go" button. But what I did next is what created problems.
I thought I may have put extra spaces or loaded the code wrong in the SQL area because the shoutbox did not work properly after everything was installed. So while in phpmyadmin and after clicking phpbb_config agian, instead of hitting SQL, I hit the "Empty" button thinking that I could just erase what I had previously inserted and then try inserting it again to make sure the code was inserted properly.
If you havn't figured it out by now, I clearly don't have a clue what I am doing, what exactly I did, or what I can do to fix it.
Click on the link to see the error..
http://www.cflzone.com/forum/index.php
Can someone that knows what they are doing please help me???
thanks,
- Reider
View 3 Replies
View Related
Feb 27, 2007
Hello,Consider I have a String:Dim MyString As String = "Hello"or an Integer:Dim MyInteger As Integer = 100or a class which its properties:Dim MyClass As New MyCustomClass
MyClass.Property1 = "Hello"
MyClass.Property2 = Unit.Pixel(100)
MyClass.Property3 = 100Or even a control:Dim MyLabel As Label
MyLabel.Id = "MyLabel"
MyLabel.CssClass = "MyLabelCssClass" Is there a way to save a String, an Integer, a Boolean, a Class, a Control in an SQL database?Something like: Define something (Integer, String, Class, Control, etc) Save in SQL 2005 Database Later in code Retrive from database given its IDIs this possible?How should I do this?What type of SQL 2005 table field should be used to store the information?Thanks,Miguel
View 1 Replies
View Related
Jul 12, 2004
Fact is:
I have two tables Orderposreg and Temp1 (both in [My Database]), Orderposreg is from 1994, Temp1 from 1995 and i need to Update the old table with the new one.
This should my Query do: I need it to Update old primary keys, or if the row does not exist to insert the new primary key into the Orderposreg.
But if I start the Query i get this failure message:
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'dbo.Temp1' does not match with a table name or alias name used in the query.
It seems that the colums do not have the same name, but the column of both tables have exactly the same column name and data type just the table name is different.
For your information the whole Code:
Code:
declare @error varchar, @rowcount varchar
select count(*) from dbo.temp1
if (@@error <> 0)
begin
Print 'An error occurred reading the temporary import table'
goto exitpoint
end
begin transaction
update dbo.Orderposreg
set dbo.Orderposreg.Ordernr = dbo.Temp1.Ordernr,
dbo.Orderposreg.Pos = dbo.Temp1.Pos,
dbo.Orderposreg.Produktnr = dbo.Temp1.Produktnr,
dbo.Orderposreg.Artbenämn = dbo.Temp1.Artbenämn,
dbo.Orderposreg.Artikelgrupp = dbo.Temp1.Artikelgrupp,
dbo.Orderposreg.Förs_pris = dbo.Temp1.Förs_pris,
dbo.Orderposreg.Kvant = dbo.Temp1.Kvant,
dbo.Orderposreg.Total_Intäkt = dbo.Temp1.Total_Intäkt,
dbo.Orderposreg.SSort = dbo.Temp1.Sort,
dbo.Orderposreg.Datum = dbo.Temp1.Datum,
dbo.Orderposreg.Utskriven = dbo.Temp1.Utskriven,
dbo.Orderposreg.Levtid_Ö = dbo.Temp1.Levtid_Ö,
dbo.Orderposreg.Levtid_L = dbo.Temp1.Lev_kvant,
dbo.Orderposreg.Inköpskostnad = dbo.Temp1.Inköpskostnad,
dbo.Orderposreg.Vikt_tol = dbo.Temp1.Vikt_tol,
dbo.Orderposreg.AntalSt = dbo.Temp1.AntalSt,
dbo.Orderposreg.Spec_nr = dbo.Temp1.Spec_nr,
dbo.Orderposreg.Spec_utgåva = dbo.Temp1.Spec_utgåva,
dbo.Orderposreg.ID_Beräknad = dbo.Temp1.ID_Beräknad,
dbo.Orderposreg.Bredd = dbo.Temp1.Bredd,
dbo.Orderposreg.Tjocklek = dbo.Temp1.Längd,
dbo.Orderposreg.ValsnGrad_kod = dbo.Temp1.ValsnGrad_kod,
dbo.Orderposreg.Kant_Kod = dbo.Temp1.Kant_Kod,
dbo.Orderposreg.Planhets_kod = Temp1.Yt_kod,
dbo.Orderposreg.LevForm_kod = dbo.Temp1.LevForm_kod,
dbo.Orderposreg.Rakhets_kod = dbo.Temp1.Rakhets_kod,
dbo.Orderposreg.BreddTol_kod = dbo.Temp1.Breddtol_kod,
dbo.Orderposreg.TjocklTol_kod = dbo.Temp1.TjockTol_kod,
dbo.Orderposreg.LängdTol_kod = dbo.Temp1.LängdTol_kod,
dbo.Orderposreg.Stål_kod = dbo.Temp1.Stäl_kod,
dbo.Orderposreg.TotaltSek = dbo.Temp1.TotaltSek,
dbo.Orderposreg.Tullstatnr = dbo.Temp1.Tullstatnr,
dbo.Orderposreg.Fakturera = dbo.Temp1.Fakturera,
dbo.Orderposreg.Leveransstatus = dbo.Temp1.Leveransstatus,
dbo.Orderposreg.Momsbelopp = dbo.Temp1.Momsbelopp,
dbo.Orderposreg.Total_inkl_moms = dbo.Temp1.Total_inkl_moms,
dbo.Orderposreg.KloMPS = dbo.Temp1.KloMPS,
dbo.Orderposreg.ShopFloor = dbo.Temp1.ShopFloor,
dbo.Orderposreg.Status = dbo.Temp1.Status,
dbo.Orderposreg.Stålkod = dbo.Temp1.Stålkod,
dbo.Orderposreg.Kontonyckel = dbo.Temp1.Kontonyckel,
dbo.Orderposreg.PlanLevDat = dbo.Temp1.PlanLevDat,
dbo.Orderposreg.Legtillägg = dbo.Temp1.Legtillägg,
dbo.Orderposreg.StålGrp = dbo.Temp1.StålGrp,
dbo.Orderposreg.KundNr = dbo.Temp1.KundNr,
dbo.Orderposreg.FKundNr = dbo.Temp1.FKundNr,
dbo.Orderposreg.Godsmärke = dbo.Temp1.Godsmärke,
dbo.Orderposreg.LagerMtr = dbo.Temp1.LagerMtr,
dbo.Orderposreg.Sortkvant = dbo.Temp1.Sortkvant,
dbo.Orderposreg.Sortpris = dbo.Temp1.Sortpris,
dbo.Orderposreg.KundsProdNr = dbo.Temp1.KundsProdNr,
dbo.Orderposreg.Godsmärke2 = dbo.Temp1.Godsmärke2,
dbo.Orderposreg.RegDatum = dbo.Temp1.RegDatum,
dbo.Orderposreg.PlanBetDag = dbo.Temp1.PlanBetDag,
dbo.Orderposreg.Säkring = dbo.Temp1.Säkring
where dbo.Orderposreg.Ordernr_o_pos = dbo.Temp1.ordernr_o_pos
select @error = @@error,@rowcount=@@rowcount
if @error <> 0
begin
print ' Error updating - Error number '+@error+'. Rolling back'
--this reverses your updates
rollback
--this jumps to the end
goto exitpoint
end
print @rowcount+' rows updated'
insert into dbo.Orderposreg
(Ordernr, Pos, Ordernr_o_pos, Produktnr, Artbenämn, Artikelgrupp, Förs_pris, Kvant, Total_Intäkt, Sort, Datum, Utskriven, Levtid_Ö, Levtid_L, Lev_kvant, Inköpskostnad, Vikt_tol, AntalSt, Spec_nr, Spec_utgåva, ID_Beräknad, Bredd, Tjocklek, Längd, ValsnGrad_kod, Kant_kod, Planhets_kod, Yt_kod, LevForm_kod, Rakhets_kod, BreddTol_kod, TjocklTol_kod, LängdTol_kod, Stål_kod, TotaltSek, Tullstatnr, Fakturera_kvant, Leveransstatus, Fri_text, Momsbelopp, Total_inkl_moms, KloMPS, ShopFloor, Status, Stålkod_text, Kontonyckel, PlanLevDat, Legtillägg, StålGrp, KundNr, FKundNr, Godsmärke1, LagerMtr, Sortkvant, Sortpris, KundsProdNr, Godsmärke2, RegDatum, PlanBetDag, Säkring)
select
Ordernr, Pos, Ordernr_o_pos, Produktnr, Artbenämn, Artikelgrupp, Förs_pris, Kvant, Total_Intäkt, Sort, Datum, Utskriven, Levtid_Ö, Levtid_L, Lev_kvant, Inköpskostnad, Vikt_tol, AntalSt, Spec_nr, Spec_utgåva, ID_Beräknad, Bredd, Tjocklek, Längd, ValsnGrad_kod, Kant_kod, Planhets_kod, Yt_kod, LevForm_kod, Rakhets_kod, BreddTol_kod, TjocklTol_kod, LängdTol_kod, Stål_kod, TotaltSek, Tullstatnr, Fakturera_kvant, Leveransstatus, Fri_text, Momsbelopp, Total_inkl_moms, KloMPS, ShopFloor, Status, Stålkod_text, Kontonyckel, PlanLevDat, Legtillägg, StålGrp, KundNr, FKundNr, Godsmärke1, LagerMtr, Sortkvant, Sortpris, KundsProdNr, Godsmärke2, RegDatum, PlanBetDag, Säkring
from dbo.Temp1
where dbo.Temp1.Ordernr_o_pos not in (select Ordernr_o_pos from dbo.Orderposreg)
select @error = @@error,@rowcount=@@rowcount
if @error <> 0
begin
print ' Error inserting - Error number '+@error
print ' Rolling back updates and inserts'
--this reverses your updates and inserts
rollback
--this jumps to the end
goto exitpoint
end
print @rowcount+' rows were inserted'
--this saves your data
commit
exitpoint:
Thanks a million for somebody who helps me!!!
View 2 Replies
View Related
Mar 16, 2004
Hi !
I have been worked with VC++, MS SQL SERVER, Transact-SQL for
3 years. I made an axtended stored procedure (xp_test) which returns
an recordset.
From Query Analizer, I can see the recordest : exec xp_test
I want to make an User Defined Function - MyTest which return
the recordset that it is returned by xp_test after its execution.
Something like that :
CREATE function dbo.MyTest ( )
RETURNS @table ...
AS
BEGIN
exec master.. xp_test table1 output -- can I do this ?
RETURN table1
END
Table and table1 are the same design.
Thank you very much !
View 4 Replies
View Related