|
Hey all, I have a problem with openxml. In my ASP.NET application I use XmlSerializer to turn a object into XML, things work fine. I then send the xml to a stored procedure on the SQL server (2000). The weird thing is the same XML can be sent to two different SPs and one does not work when declaring one of the XML fields as ntext.
ie. in one I do an insert,
insert into table1 (cust_id, job_requested, job_status)
select @useCustId,
xmlDetails.Description,
xmlDetails.StatusId,
from OPENXML(@idoc, 'JobList/Job', 2)
WITH (CustomerId int, Description ntext, StatusId int) xmlDetails
Now this is trimed down, but it works fine. now when the same XML gets sent to another SP that updates like:
update job_main set
job_requested = xmlDetails.Description,
job_status = xmlDetails.StatusId,
from OPENXML(@idoc, 'JobList/Job', 2)
with (Id int, Description ntext, StatusId int) xmlDetails
where job_id = xmlDetails.Id
Now this one fails with 'Internal SQL Server error.' And I have no idea why, as it is the same XML as in the insert statement. If i change the ntext to nvarchar or varchar it works, but text and ntext do not.
Anyone seen anything like this, or know if i'm doing something stupid? I have no idea.
Thanks all
Luke
|
|
|
|
|
Is it possible to have 2 fields to be a datamember of a listbox? I want to have last and first name display. Thanks
|
|
|
|
|
Concat them together in your query.
<br />
select firstname + ' ' + lastname as fullname<br />
Michael
I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious.
Vince Lombardi (1913-1970)
|
|
|
|
|
I am a new teacher and the program we use with the students won't uplaod and keeps giving me these error messages everytime we try to log on! I know that my server isn't reading my database but what do I do? The computer lab is closed until I can fix this! Please Help!
|
|
|
|
|
I'm going to guess that JZooL should have been 'JZ001'. Looking for JZ001 and JZ006 on a search engine found this page[^] on Sybase's support site for their JDBC provider.
JZ001 sounds like a configuration error or someone typing the wrong user name. It looks like the user name is limited to 30 characters for this database.
JZ006 sounds like a general network error - that the server cannot be contacted. Check that the correct server name/IP is specified in the configuration. If it is, this is a task for your network administrators (if any) to work out why the one cannot see the other. Presumably this worked before the summer break? Check that no firewalls have been added or reconfigured between the client and the server. See whether a ping works (it may not do even if the server is working and contactable since it's now common for firewall software or hardware to reject pings).
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Hi All,
I am getting the following error,
Msg 245, Level 16, State 1, Line 13
Syntax error converting the nvarchar value 'INSERT INTO [mydb].[dbo].[temp]
(
[f1],
[f2],
[f3],
[f4]
)
VALUES(
222,
333,
44,
' to a column of data type int.
while executing the below code
DECLARE @t_f1 as int
DECLARE @t_f2 as nvarchar(50)
DECLARE @t_f3 as nvarchar(50)
DECLARE @t_f4 as nvarchar(50)
set @t_f1 = 111
set @t_f2 = 222
set @t_f3e = 333
set @t_f4 = 44
declare @q as nvarchar(500)
set @q = 'INSERT INTO [mydb].[dbo].[temp]
(
[fld1],
[fld2],
[fld3],
[fld4]
)
VALUES(
'+@t_f1+',
'+@t_f2+',
'+@t_f3+',
'+@t_f4+'
)'
exec @q
My database table structure is as follows,
CREATE TABLE temp
(
fld1 int,
fld2 nvarchar(50),
fld3 nvarchar(20),
fld4 nvarchar(20),
)
Thanks in advance...
~Mahantesh V H
|
|
|
|
|
Hi!!!!
Execute statement only takes nvarchar,char....datatype only
so u change data type like
DECLARE @t_f1 as nvarchar(50)
DECLARE @t_f2 as nvarchar(50)
DECLARE @t_f3 as nvarchar(50)
DECLARE @t_f4 as nvarchar(50)
Regards 
Shashank
|
|
|
|
|
Is there any other alternative means for integer data type?
~Mahantesh V H
|
|
|
|
|
You probably can use the CONVERT() function to convert the int variable to nvarchar.
Try CONVERT(@t_f1 AS NVARCHAR(50))
Ed
|
|
|
|
|
Hi,
I want to insert int not char...
~Mahantesh
|
|
|
|
|
Anonymous wrote: I want to insert int not char...
Yes, I know. But from what I see it seems that the SQL is complaining that you are adding the nvarchar 'INSERT INTO [mydb].[dbo].[temp]..' into @t_f1 (which is an integer) when concatenating the @q.
The convert is just to make sure your @q can be created by concatenating nvarchars instead of adding it to an int.
When you execute it it should insert an integer instead of an nvarchar.
Btw, why are you using exec @q anyway? You don't seem to need to create a dynamic SQL statement for that one.
Ed
|
|
|
|
|
Can someone tell me what is wrong with the following Insert Function?
Thanks...
The TABLE Properties
CreatedBy int 4 NULLS
The CODE
Dim intUser As Integer
Public Function InsertFiscalYear()
sqlConn.ConnectionString = "Server=" & MZKSrv & ";User id=sa;Password=" & MZKPswd & "; Initial Catalog=" & MZKDB & ";"
sqlStr = "INSERT INTO FIN_FiscalYear (Name, StartDate, EndDate, CreatedOn, CreatedBy, CreatedIPAdd) VALUES('strName', 'strStartDate', 'strEndDate', 'strCreatedOn', 'intUser', 'strCreatedIPAdd')"
sqlCmd.Parameters.Add("Name", strName)
sqlCmd.Parameters.Add("StartDate", strStartDate)
sqlCmd.Parameters.Add("EndDate", strEndDate)
sqlCmd.Parameters.Add("CreatedOn", strCreatedOn)
sqlCmd.Parameters.Add("CreatedBy", intUser)
sqlCmd.Parameters.Add("CreatedIPAdd", strCreatedIPAdd)
sqlConn.Open()
sqlTrans = sqlConn.BeginTransaction()
sqlInsert.Connection = sqlConn
sqlInsert.Transaction = sqlTrans
Try
sqlInsert.CommandText = sqlStr
sqlInsert.ExecuteNonQuery()
sqlTrans.Commit()
Debug.WriteLine("Records are written to database.")
Catch e As Exception
Try
sqlTrans.Rollback()
Catch ex As SqlException
If Not sqlTrans.Connection Is Nothing Then
Debug.WriteLine("An exception of type " & ex.GetType().ToString() & " was encountered while attempting to roll back the transaction.")
End If
End Try
Debug.WriteLine("An exception of type " & e.GetType().ToString() & " was encountered while inserting the data.")
Debug.WriteLine("Record was NOT written to database.")
Finally
sqlConn.Close()
End Try
End Function
End Class
Values shown in the Autos window as the program is running just prior to "BeginTransaction".
intUser 1 Integer
Error Message "Syntax error converting the varchar value 'intUser' to a column of data type int."
|
|
|
|
|
You should try using @ParamName when declaring parameters so it is clear.
You are missing the intUser as parameter and you should not put single apostrophe (') in between parameter name, that's why it is taking intUser as a string instead of parameter name
Try this:
sqlStr = "INSERT INTO FIN_FiscalYear (Name, StartDate, EndDate, CreatedOn, CreatedBy, CreatedIPAdd) VALUES(@Name, @StartDate, @EndDate, @CreatedOn, <code>@User</code>, @CreatedIPAdd)"
sqlCmd.Parameters.Add("@Name", strName)
sqlCmd.Parameters.Add("StartDate", strStartDate)
sqlCmd.Parameters.Add("EndDate", strEndDate)
sqlCmd.Parameters.Add("CreatedOn", strCreatedOn)
<code>sqlCmd.Parameters.Add("User", intUser)</code>
sqlCmd.Parameters.Add("CreatedIPAdd", strCreatedIPAdd)
Ed
|
|
|
|
|
I have removed the single apostrophe (') on the sqlStr line and replaced them with a leading (@) in the VALUES section. I have also placed the (@) infront of all the names inside the quotes on the sqlCmd.Parameters.Add statements.
Now the error message is: "Must declare the variable '@Nmae'."
My new lines:
sqlStr = "INSERT INTO FIN_FiscalYear (Name, StartDate, EndDate, CreatedOn, CreatedBy, CreatedIPAdd) VALUES(@Name, @StartDate, @EndDate, @CreatedOn, @CreatedBy, @CreatedIPAdd)"
sqlCmd.Parameters.Add("@Name", strName)
sqlCmd.Parameters.Add("@StartDate", strStartDate)
sqlCmd.Parameters.Add("@EndDate", strEndDate)
sqlCmd.Parameters.Add("@CreatedOn", strCreatedOn)
sqlCmd.Parameters.Add("@CreatedBy", intUser)
sqlCmd.Parameters.Add("@CreatedIPAdd", strCreatedIPAdd)
|
|
|
|
|
PAEC wrote: Now the error message is: "Must declare the variable '@Nmae'."
You have made a spelling mistake in your sql statement. Check your sql and change @Nmae into @Name and things should be running smoothly.
Ed
|
|
|
|
|
The spelling mistake was my typing of the message. The copy/paste from the section of code shows the correct spellings. Pasted again here:
The error message is: "Must declare the variable '@Name'."
sqlStr = "INSERT INTO FIN_FiscalYear (Name, StartDate, EndDate, CreatedOn, CreatedBy, CreatedIPAdd) VALUES(@Name, @StartDate, @EndDate, @CreatedOn, @CreatedBy, @CreatedIPAdd)"
sqlCmd.Parameters.Add("@Name", strName)
sqlCmd.Parameters.Add("@StartDate", strStartDate)
sqlCmd.Parameters.Add("@EndDate", strEndDate)
sqlCmd.Parameters.Add("@CreatedOn", strCreatedOn)
sqlCmd.Parameters.Add("@CreatedBy", intUser)
sqlCmd.Parameters.Add("@CreatedIPAdd", strCreatedIPAdd)
|
|
|
|
|
PAEC wrote: The error message is: "Must declare the variable '@Name'."
If you have not resolved this issue by now, as memory serves the santax should be like this.
sqlStr = "INSERT INTO FIN_FiscalYear (Name, StartDate, EndDate, CreatedOn, CreatedBy, CreatedIPAdd) VALUES('@Name', 'StartDate', 'EndDate', 'CreatedOn', CreatedBy, 'CreatedIPAdd')"
sqlCmd.Parameters.Add("@Name", strName)
sqlCmd.Parameters.Add("StartDate", strStartDate)
sqlCmd.Parameters.Add("EndDate", strEndDate)
sqlCmd.Parameters.Add("CreatedOn", strCreatedOn)
sqlCmd.Parameters.Add("CreatedBy", intUser)
sqlCmd.Parameters.Add("CreatedIPAdd", strCreatedIPAdd)
The ambiguity comes in with reserved words like Name, Date, and Time etc. The ' is used for strings now integers.
DEBUGGING : Removing the needles from the haystack.
|
|
|
|
|
I have a table called IMSUB with fields SUB, SUBV, & SUBD
pri key is SUB, SUBV
SUB = subassy number, not auto number
SUBV = subassy version, not autonumber
SUBD = new name of subassy
I want to insert a new subassy, therefore, I need to get the maximum value for SUB add 1 to it, set SUB = 1 and set the SUBD to the new name. How can I do this in SQL? MS Access! Thx to all!
|
|
|
|
|
SELECT SUM(SUB)+1 FROM IMSUB
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
thanks for the reply, just a question.
the statement SELECT SUM(SUB) + 1 from IMSUB, does this sum all the SUB numbers and add 1? I was trying the following statement:
INSERT INTO IMSUB SELECT MAX(SUB) + 1 AS sub1, 1 as SUBV1, "Name of new sub" as SUBD1;
DO you think this will work and is it atomic, so that multiple users will not collide?
|
|
|
|
|
Sorry, I'm obviously having a bad day - It should have been MAX(SUB)+1
mmcsherr wrote:
DO you think this will work and is it atomic, so that multiple users will not collide?
It is atomic, so multiple users won't collide - although I have to say Access is not very good for multiple users.
Create a test database and try it out - much faster than waiting for an answer from a guy who's already made one blindingly daft mistake today.
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
No problem, thanks for the help anyway, you are right about Access, but thatis what I am stuck with....
|
|
|
|
|
hi all,
IN Sql Server:
i need to create trigger on table A as
when i insert new row in Table A new record will be inserted in Table B with the same primary key of Table A
please Help
thank you
|
|
|
|
|
To copy all the column values from A to B, use:
CREATE TRIGGER Insert_B_On_Insert_A
ON A
FOR INSERT
AS
INSERT INTO B
SELECT * FROM inserted The inserted pseudo-table inside a trigger definition contains all the rows that were inserted, or for a FOR/INSTEAD OF UPDATE trigger, the new values of the rows.
Why use INSERT/SELECT rather than INSERT/VALUES? The trigger is called once for each INSERT statement, not once for each row. If table A is loaded using INSERT/SELECT (where the SELECT returns more than one row) or a BULK INSERT more than one row will appear in the inserted table.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
I have a dataset that has a table in it called say myTable.
There is a column in this table called field3
I have a textbox which displays the value of field3 using binding:
this.textbox1.DataBindings.Add("Text", myTable, "field3");
I have an event for when an Add button is pressed which Adds a new row to myTable:
DataRow newDR = myTable.NewRow();
newDR["field3"] = "Some Text";
myTable.Rows.Add(newDR);
The problem I have is that the texbox does not change to show the new value. It still shows the previous value.
So basically when I add a new row to the table I want to be able to point controls that have been binded to this new row. I've spent 2hrs trying to figure out how to do this and am at a loss 
-- modified at 20:59 Tuesday 11th October, 2005
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
