|
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
|
|
|
|
|
Question:- is that only the field3 didn't change?how about others fields?
but as i experience before once i add a new row, in the event, i've to put last command --> Page.databind() to bind data again.
maybe you can try, Hope can help.
|
|
|
|
|
How do I Validate DateTime in storeproc for a particular format say 'ddmm'?
|
|
|
|
|
Look at the IsDate function.
<br />
select IsDate('11/15/05')<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)
|
|
|
|
|
Thanks for the reply.
Actually IsDate is not available in SYBASE. So I had to write custom validation.
Thanks again.
|
|
|
|
|
I have a stored procedure that look slike this (it's much bigger, this example is for simplicity):
INSERT INTO Table1 ( Field1,Field2,Field3 )<br />
<br />
SELECT Field1,Field2,Field3 from Table2 where Field4 = @Var
It is grabbing all of the data from one table and importing it into another
here is my problem, i need the proc to be able to tell if the value in Field1 in Table2 already already exists in Table1. If it does, i need it to update table1, otherwise do an insert, as it is new data.
I have seen how to do this with an individual record, but i need to be able to do it with a mass set. the above code currently handles about 29 rows from Table2
______________________
Mr Griffin, eleventy billion is not a number...
|
|
|
|
|
Break this down into a SELECT first:
SELECT Field1, Field2, Field3
FROM Table2
WHERE Field1 NOT IN (SELECT Field1 FROM Table1)
Then merge them together:
INSERT INTO Table1 (Field1, Field2, Field3)
SELECT Field1, Field2, Field3
FROM Table2
WHERE Field1 NOT IN (SELECT Field1 FROM Table1)
The update is a little more problimatic:
UPDATE Table1
SET Field2 = (SELECT Field2 FROM Table2 WHERE Table1.Field1 = Table2.Field2),
SEt Field3 = (SELECT Field3 FROM Table2 WHERE Table1.Field1 = Table2.Field2)
WHERE Table1.Field1 IN (SELECT Field1 FROM Table2)
If you don't want redundant UPDATEs, do the UPDATE call first.
|
|
|
|
|
That is beautiful
Here's a funny story. One of my coworkers came up with a solution for the problem i was trying to address with the above question. (As it turns out, this would not have worked for it).
However, the exact same stored procedure made changes to 2 other tables, and your code here was the perfect solution for them.
For the first time in almost a week, this damn app is actually working the way it was designed
______________________
Mr Griffin, eleventy billion is not a number...
|
|
|
|
|
Hi, I have a question in regards to oracle database.. I have a oracle database installed on a server which is a linux system. And now I want to store data into the database which is chinese/jap/korean characters. I used INSERT INTO table VALUES ('X'), where X is a chinese character, but when I retrive the data back from the database it returns as a "?". So, I want to know is there any configuration or anything that I need to set in order to store multi byte char into the database?? I used (VARCHAR) in the database. Thanks
|
|
|
|
|