Showing posts with label insert. Show all posts
Showing posts with label insert. Show all posts

Monday, March 26, 2012

How to use UPDATETEXT within the context of inserting a new row

All -

I'm trying to insert a row into a table. One of the columns in this table is a text field. I can't figure out how to use UPDATETEXT to insert data into this text field for a brand new row. The examples I see all require a text pointer to the column. However - I haven't even inserted the row - so how would I create a text pointer?

Here's the example from Books Online -

DECLARE @.ptrval binary(16)
SELECT @.ptrval = TEXTPTR(pr_info) FROM pub_info pr, publishers p WHERE p.pub_id = pr.pub_id AND p.pub_name = 'New Moon Books'
UPDATETEXT pub_info.pr_info @.ptrval 88 1 'b' GO

How would I create the text pointer (TEXTPTR) for a record that hasn't even been inserted yet?

Incidentally - my issues would be solved if the data for this text column was always less than 8K (then I could use varchar(8000) - but that isn't always the case.

Help!

I'm assuming that you're running SS2000.

UPDATETEXT operates on tables.

If the data you want to manipulate isn't in a table, where is it?

|||

Note that UPDATETEXT is going away in a future version of SQL Server. Use varchar(max) if this is a 2005 database and then use the .WRITE construct in the UPDATE statement.

Well, you can just use a text parameter to a stored procedure and just pass in all of the text... That is the simplest answer. You will have to create the row first, and create the text pointer:

drop table test
go
create table test
(
testId int,
value text
)
go
insert into test(testId)
values (1)
go

Update test
set value = 'foo'
where testId = 1
and value is null

DECLARE @.ptrval binary(16)
SELECT @.ptrval = TEXTPTR(value)
FROM test
WHERE testId = 1


UPDATETEXT test.value @.ptrval null null 'b'
GO

select *
from test

Returns:

testId value

--

1 foob

|||Thanks so much - this seemed to work. And yes I am stuck with SQL Server 2000 right now. It seems to me so clunky to update text fields this way - so I'll be real happy once I migrate to 2005.

Thanks again for the help.

How to use TSQL to monitor replication status

To monitor the status of replication in SQL 2000 we insert the resuts of sp_MShelp_replication_status into a temp table and review the status. In SQL 2005 this stored procedure has changed and now does it's own insert into execute command. Since insert execute commands can not be nested we must find another way to programically monitor replication using TSQL. We could call sys.sp_replmonitorhelppublisherhelper but we can't seem to find it in the distribution database. It must be hidden somehow. Any ideas?

Thanks,

Danny

>> We could call sys.sp_replmonitorhelppublisherhelper but we can't seem to find it in the distribution database.

What do you mean? Do you mean you can call this stored proc but can't find it in management stuidio. This is a system stored procedure

The general topic on monitering replication in T-SQL can be found at http://msdn2.microsoft.com/fr-fr/library/ms147874.aspx

BTW, to work around nested INSERT EXEC issue, you can take a look at OPENROWSET (Note: server option "Ad Hoc Distributed Queries" is necessary, which is off by default).

Peng

|||

Please see Books Online topic "How to: Programmatically Monitor Replication (Replication Transact-SQL Programming)" - http://msdn2.microsoft.com/en-us/library/ms147874.aspx. There's also a similar article to do this via RMO.

|||

Look at topics:

How to: Programmatically Monitor Replication (Replication Transact-SQL Programming): http://msdn2.microsoft.com/zh-cn/library/ms147874.aspx

How to: Programmatically Monitor Replication (RMO Programming): http://msdn2.microsoft.com/fr-fr/library/ms147926.aspx

And yes, sp_replmonitorhelppublisherhelper resides in the resource database and not in distribution database. Hence you are not able to see it. You will need to start the server in single user mode to view its definition.

Also look at: sp_replmonitorhelpmergesession and sp_replmonitorhelpmergesessiondetail and see if they are heplful for you.

|||Thanks. I'll look at using openrowset. sys.sp_replmonitorhelppublisher checks for access and database name. The it calls sys.sp_replmonitorhelppublisherhelper as shown below. This system stored procedure or extended stored procedure doesn't appear to be exposed. I've checked all user and system databases for it's name and don't see it anywhere. Anyone know how that's possible or am I missing it?

select @.cmd = quotename(@.distdb) + '.sys.sp_replmonitorhelppublisherhelper'

exec @.retcode = @.cmd @.publisher = @.publisher, @.refreshpolicy = @.refreshpolicy

sql

Friday, March 23, 2012

How to use tables from two servers under the same group?

Hi
I have two servers under the same group in my enterprise manager, I need to run insert query on a table in one server and select into the insert from the other server.
So what is the syntax to do it ?? hope it's possible...
Thanks,
Inon.First of all, the group in Enterprise Manager doesn't dictate whether this is possible or not, so that isn't a factor, really.
The factor in this case is whether the two servers can "see" eachother, meaning if you can get a result when connected to one of the server executing a query like this

select * from [OtherServername].[DBName].dbo.[tablename] where 1=2
alt.
select * from [InstanceName\OtherServername].[DBName].dbo.[tablename] where 1=2

(Yes, even if you replace the placeholders, I wrote this query on purpose to return 0 rows, all I'm interessted in for the moment is wheter you get a result or not.)

If one of the two above queries work, you've can write as I did, and if it doesn't, you need to setup a link between the two servers as described in the SQL Server books online help on the command sp_addlinkedserver.|||I added a linked server, I can almost connect to it... the only thing left is to specify the login information since the defualt 'sa' can't login on the linked server.

So, I guess it's in the provider string (@.provstr), can you remind me what is the syntax to specify the login information ? And correct me if it's not supposed to be in the provider string.

Thanks,

Inon.|||Ummmm ... don't know exactly what you are trying to do here, but it sounds like you are trying to login to a server you aren't supposed to be able to login to. If you don't know the sa password you can use another user/password that has access to the tables you need, but I would strongly suggest you get hold of the sa password and use it in the link between the two servers, that way you won't have any access limitations at all, if that is possible ...|||Ummmm ... don't know exactly what you are trying to do here, but it sounds like you are trying to login to a server you aren't supposed to be able to login to. If you don't know the sa password you can use another user/password that has access to the tables you need, but I would strongly suggest you get hold of the sa password and use it in the link between the two servers, that way you won't have any access limitations at all, if that is possible ...

No no no... I know the sa password, I asked how/where do I specify it ?
You see, I added a linked server without specifing the login information, now, when I try to connect I get message "Login failed for sa..." this is because I did not specify the password anywhere, my question is, where do I specify it ??

Inon.|||If you're using the Enterprise Manager you can specify the password in the tab under Security/Linked servers (Properties/Security).

There you should see a text saying something like :

"For a login not defined in the list above, connections will be made with the following context :" followed by 4 options. Tick the last one, fill in remote login (sa) and the password for sa in the next field.

I'm sure there is a way to specify it in a command, but I just aint got the time to check it in the help for the moment.

Wednesday, March 21, 2012

how to use select statement in insert query

hi my self avi
i want to copy data from one table to other table,by giving certain
condition and i want o use insert statement .in this i want to pass some
value directly and some value from select statement , if i try i ll get
error i.e all column of destination table (i.e in which i want to insert
data) should match with all columns in values column some thing like
this.
plz give me some helpful suggetion on this"avinash" <pawar_avinash@.rediffmail.com> wrote in message
news:f9b4f44fb15b1beabe792c1f314e7f86@.localhost.ta lkaboutdatabases.com...
> hi my self avi
> i want to copy data from one table to other table,by giving certain
> condition and i want o use insert statement .in this i want to pass some
> value directly and some value from select statement , if i try i ll get
> error i.e all column of destination table (i.e in which i want to insert
> data) should match with all columns in values column some thing like
> this.
> plz give me some helpful suggetion on this

Something like this?

insert into dbo.Table1
(col1, col2, col3, col4)
select 'A', co11, 7, col8
from dbo.Table2
where...

In the SELECT statement, you can mix constant values ('A' and 7) with column
values (col1 and col8) - I think this is what you want to do, if I
understood you correctly. If this doesn't help, then I suggest you post
CREATE TABLE statements for both tables, and the INSERT statement you're
trying to make work, as that will avoid any confusion and guesswork.

Simon

Friday, March 9, 2012

How to use INSERT INTO.... SELECT FROM query to export MS SQL data in FoxPro 2.6 DBF

Hi all,

This is my very first post to this forum. How to use the INSERT INTO... SELECT FROM query to export MS SQL 2000 data into FoxPro 2.6 DBF file? I want to write a VB 6.0 program, and already connected to MS SQL 2000 Database by MS SQL ODBC connection. Now I want to Export this MS SQL data to FoxPro 2.6 DBF. I found ROWSET option, but don't know how to use it for FoxPro.
Please help me.

Thanks in Advance.

Regards,
Rajeev VandakarYou can try using a DTS package to export the data directly and you can schedule the same too.|||

Hi Rajeev,

If you want to just copy the whole table then Satya's idea is great. If you want to select specific rows then here's one way that works:

Download and install the FoxPro and Visual FoxPro OLE DB data provider from msdn.microsoft.com/vfoxpro/downloads/updates. It works for all versions of Fox tables.

Create a linked server:

EXEC master.dbo.sp_addlinkedserver
@.server = N'VFP_Test',
@.srvproduct=N'Microsoft Visual FoxPro OLE DB Data Provider',
@.provider=N'VFPOLEDB',
@.datasrc=N'C:\Temp\',
@.provstr=N'VFPOLEDB.1'

After that you can access data with the four-part naming convention: LinkedServerName...TableName. Note the 3 dots as FoxPro has no catalog or schema. I tested by creating a FoxPro 2.x format table C:\Temp\Table_1. I also created a table in my SQL Server test database called Table_1 with the same structure as the Fox table. Then I inserted data successfully by running the following command in a SQL code window:

Insert Into VFPTest...Table_1 Select * From Table_1

The following VB6 program worked for me:

Public Sub Main()

Set conn1 = New ADODB.Connection
conn1.Open _
"Provider=SQLOLEDB.1;Integrated Security=SSPI;" & _
"Persist Security Info=False;" & _
"Initial Catalog=Test;Data Source=."

Dim sql1 As String

sql1 = _
"Insert Into VFPTest...Table_1 Select * From Table_1"

Set cmd1 = New ADODB.Command
With cmd1
.CommandType = adCmdText
.ActiveConnection = conn1
.CommandText = sql1
.Execute
End With

End Sub

|||

Thanks

Cindy Winegarden,

I had created the Linked Server for the FoxPro 2.6 DBF and its functioning as I wanted.

|||

Cindy,

I am trying to do something similar, but cannot get variations of your approach to work. I have successfully created a Linked Server (MBFOXPRO) to a free directory of VFP tables using the VFPOLEDB Provider, and I can select data all day long. However, any sort of update or insert fails.

If I try:

Code Snippet

update openquery(MBFOXPRO,'select * from tkflin where recnum=500 and linnum=68')

set prtdsc='test update'

I get the error:

Msg 7301, Level 16, State 2, Line 3

Cannot obtain the required interface ("IID_IRowsetChange") from OLE DB provider "VFPOLEDB" for linked server "MBFOXPRO".

If I try:

Code Snippet

update MBFOXPRO...tkflin

set prtdsc='test update' where recnum=500 and linnum=68

I get the error:

OLE DB provider "VFPOLEDB" for linked server "MBFOXPRO" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

Msg 7333, Level 16, State 2, Line 15

Cannot fetch a row using a bookmark from OLE DB provider "VFPOLEDB" for linked server "MBFOXPRO".

I have read a lot of different posts on this subject and some older ones indicate that updates through a Linked Server cannot be done at all (e.g., http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=144712&SiteID=1).

I am pretty confident I have the latest and greatest VFPOLEDB Provider.

My config info is:

SQL Server 2005 Developer Edition running on Windows SBS 2003. I'm not sure the version of the FoxPro tables (they are part of a 3rd party accounting package) but I can manipulate them just fine using VFP 9.

I can run updates using Ad Hoc SQL statements from VB.Net just fine, but it sure would be handy to be able to keep all of my data stuff neatly on the SQL Server side of the fence.

Any suggestions?

Thanks,

Todd

How to use INSERT INTO.... SELECT FROM query to export MS SQL data in FoxPro 2.6 DBF

Hi all,

This is my very first post to this forum. How to use the INSERT INTO... SELECT FROM query to export MS SQL 2000 data into FoxPro 2.6 DBF file? I want to write a VB 6.0 program, and already connected to MS SQL 2000 Database by MS SQL ODBC connection. Now I want to Export this MS SQL data to FoxPro 2.6 DBF. I found ROWSET option, but don't know how to use it for FoxPro.
Please help me.

Thanks in Advance.

Regards,
Rajeev VandakarYou can try using a DTS package to export the data directly and you can schedule the same too.|||

Hi Rajeev,

If you want to just copy the whole table then Satya's idea is great. If you want to select specific rows then here's one way that works:

Download and install the FoxPro and Visual FoxPro OLE DB data provider from msdn.microsoft.com/vfoxpro/downloads/updates. It works for all versions of Fox tables.

Create a linked server:

EXEC master.dbo.sp_addlinkedserver
@.server = N'VFP_Test',
@.srvproduct=N'Microsoft Visual FoxPro OLE DB Data Provider',
@.provider=N'VFPOLEDB',
@.datasrc=N'C:\Temp\',
@.provstr=N'VFPOLEDB.1'

After that you can access data with the four-part naming convention: LinkedServerName...TableName. Note the 3 dots as FoxPro has no catalog or schema. I tested by creating a FoxPro 2.x format table C:\Temp\Table_1. I also created a table in my SQL Server test database called Table_1 with the same structure as the Fox table. Then I inserted data successfully by running the following command in a SQL code window:

Insert Into VFPTest...Table_1 Select * From Table_1

The following VB6 program worked for me:

Public Sub Main()

Set conn1 = New ADODB.Connection
conn1.Open _
"Provider=SQLOLEDB.1;Integrated Security=SSPI;" & _
"Persist Security Info=False;" & _
"Initial Catalog=Test;Data Source=."

Dim sql1 As String

sql1 = _
"Insert Into VFPTest...Table_1 Select * From Table_1"

Set cmd1 = New ADODB.Command
With cmd1
.CommandType = adCmdText
.ActiveConnection = conn1
.CommandText = sql1
.Execute
End With

End Sub

||| Thanks Cindy Winegarden,

I had created the Linked Server for the FoxPro 2.6 DBF and its functioning as I wanted.

|||

Cindy,

I am trying to do something similar, but cannot get variations of your approach to work. I have successfully created a Linked Server (MBFOXPRO) to a free directory of VFP tables using the VFPOLEDB Provider, and I can select data all day long. However, any sort of update or insert fails.

If I try:

Code Snippet

update openquery(MBFOXPRO,'select * from tkflin where recnum=500 and linnum=68')

set prtdsc='test update'

I get the error:

Msg 7301, Level 16, State 2, Line 3

Cannot obtain the required interface ("IID_IRowsetChange") from OLE DB provider "VFPOLEDB" for linked server "MBFOXPRO".

If I try:

Code Snippet

update MBFOXPRO...tkflin

set prtdsc='test update' where recnum=500 and linnum=68

I get the error:

OLE DB provider "VFPOLEDB" for linked server "MBFOXPRO" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

Msg 7333, Level 16, State 2, Line 15

Cannot fetch a row using a bookmark from OLE DB provider "VFPOLEDB" for linked server "MBFOXPRO".

I have read a lot of different posts on this subject and some older ones indicate that updates through a Linked Server cannot be done at all (e.g., http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=144712&SiteID=1).

I am pretty confident I have the latest and greatest VFPOLEDB Provider.

My config info is:

SQL Server 2005 Developer Edition running on Windows SBS 2003. I'm not sure the version of the FoxPro tables (they are part of a 3rd party accounting package) but I can manipulate them just fine using VFP 9.

I can run updates using Ad Hoc SQL statements from VB.Net just fine, but it sure would be handy to be able to keep all of my data stuff neatly on the SQL Server side of the fence.

Any suggestions?

Thanks,

Todd

how to use insert into with IDENTITY ( seed ,increment )

when i insert data from outside
i use insert into ....select...
can i use IDENTITY ( seed ,increment ) in my statement.
for example i have table
aaaaa AAA
bbbbb BBB
ccccc CCC

can i insert into new table with data

1 aaaaa AAA
2 bbbbb BBB
3 ccccc CCC
thanks

You can use cast (or convert)

create table test
(
integerValue int
)
insert into test
select 1
union all
select 232525
union all
select 2325
union all
select 525
union all
select 99232525

select integerValue, cast(integerValue as varchar(10)) as varcharValue
from test

integerValue varcharValue

1 1

232525 232525

2325 2325

525 525

99232525 99232525

|||

Well, first, don't change the meaning of a thread...I already answered it as it was originally asked Smile


create table test
(
value char(5),
otherValue char(3)
)
insert into test
select 'aaaaa','AAA'
union all
select 'bbbbb','BBB'
union all
select 'ccccc','CCC'

You can do this:


select identity(int,1,1) as test2Id,
value,
otherValue
into test2
from test

Or if you just wanted numbers, you can do this (in 2005) to get control over the values.


select row_number() over (order by value) as test3Id,
value,
otherValue
into test3
from test
go

|||sorry, i have change my question. can you answer me.

How to use For Each loop to Read one record and insert the same into destination

Hi,

My procedure to implement a task is like this

I will be using execute SQL task to fetch the records from source,after this wanna use For each loop to access each record one at a time,perform some trnsformations and insert that record into destination.

Help me in accessing the data stored in the Variable(SQL task) in Dataflow task of foreach loop.

Why do you need to do this one record at a time - is the transformation different for each record ?

Can you explain the transformations you need to do?

|||

In the Exec SQL task Editor:

1. General Tab - Result should be of type Full Resultset.

2. ResultSet tab - Set Result Name =0 , Variable Name= your variable of type object (you can create a new variable from the Variable Name dropdown itself, or in the main Variables Pane). Variable type =Object.

http://technet.microsoft.com/en-us/library/ms141689.aspx has details.

3. Add a For Each Loop Container.

4. In the forEach Loop editor, Collection Tab, Set Enumerator=For Each ADO Enumerator

5. In the Variable Mapping, map your column variables to index 0,1,2 (assuming you have three columns in your resultset). You can create your own variables for each column. For each iteration i.e. for each row of the resultset, these variables will hold the value of the respective columns for the current row.

6. Perform your transformations

7. Use a OLEDB Command destination to insert/update your row to the table.

HTH

Kar

|||The requriment from is in that way,I have proposed for a bulk process.But m not sure if i will get the approval.

|||

The index value to be set are in tenh order of retreival from source.

How to access those variables inTansformations can u be specific using Derived column.

|||

You'll get much better performance if you use an OLE DB Source inside a data flow, rather than iterating through a resultset with a For Each. You might want to revisit your design, or post more specifics so we can help you a little more.

|||

Hi,

I will be having a SQL query to retreive data from source,incorporated in Execute SQL task.

Result will be stored in a Result Set.

After this inside a ForEach loop i have to access single record contained in the result set apply some transformations and send the data into destination.

So my requirement is to process one row of data at a time and whole data processing must take place in a loop.

My problem is,How do i process this data in Data Flow task inside For Each loop.

|||

Hi,

You can achive this by this method(i have tried it out)

Use source query and populate the Recordset destination(variable must be of type object)

For each loop(enumerte through adonet)

Based on number of columns declare those many variables and access them in a script componet(source)

and output those variables as columns,

Do the transformations and insert into destination finally.

Any queries post it here.I will help u out.

|||Sorry, but I'm still not understanding why you have to loop through the recordset rather than using an OLEDB Source. However, if it works for you, stick with it.

Wednesday, March 7, 2012

How to use CTRL+SHIFT+INSERT to insert template in Management Stud

Hi,
I am trying to create a simple template in Managament Studio editor that has
"Select * from " and try to invoke it by keyboard short cut so that I don't
have to type this everytime I want to type in "Select * from ". Anyone who
has done this kinda thing?
Thanks for the help!
-Josh
You 'could' go to [Tools], [Options], [Environment], [Keyboard], and add a
'hot-key' combination for that string.
However, it is widely considered outside the scope of 'Best Practices' to
use [SELECT *].
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Kodi" <Kodi@.discussions.microsoft.com> wrote in message
news:30E4B93A-C842-4D03-BF18-B6C5317C8809@.microsoft.com...
> Hi,
> I am trying to create a simple template in Managament Studio editor that
> has
> "Select * from " and try to invoke it by keyboard short cut so that I
> don't
> have to type this everytime I want to type in "Select * from ". Anyone who
> has done this kinda thing?
> Thanks for the help!
> -Josh

How to use CTRL+SHIFT+INSERT to insert template in Management Stud

Hi,
I am trying to create a simple template in Managament Studio editor that has
"Select * from " and try to invoke it by keyboard short cut so that I don't
have to type this everytime I want to type in "Select * from ". Anyone who
has done this kinda thing?
Thanks for the help!
-Josh
Hi
Having SELECT * FROM in production code is not recommended therefore the
need to do this would only be in ad-hoc queries or possibly EXIST clauses,
therfore the need to do this may not be as great as you expect, expecially as
it is not saving that much typing.
You can assign keys in management studio to run procedures, but not to
insert text. What you may want to look at is the ability to create SELECT
statements from the object explorer (F8) by right clicking the stored
procedure and using the script option to script a select statement to file,
window or the clipboard. Also if you want to insert object names into a
window the object can be dragged across from the object explorer and the name
will be inserted. If you drag the columns branch of a table across you will
get a list of all columns.
You can also create your own templates (see the template explort CTRL+ALT+T)
if you have more complex code needs (such as a stored procedure with standard
error handling block). Templates can contain substitute values such as
SELECT * FROM <schema_name, sysname, dbo>.<table_name, sysname, sample_table>
John
"Kodi" wrote:

> Hi,
> I am trying to create a simple template in Managament Studio editor that has
> "Select * from " and try to invoke it by keyboard short cut so that I don't
> have to type this everytime I want to type in "Select * from ". Anyone who
> has done this kinda thing?
> Thanks for the help!
> -Josh

How to use CTRL+SHIFT+INSERT to insert template in Management Stud

Hi,
I am trying to create a simple template in Managament Studio editor that has
"Select * from " and try to invoke it by keyboard short cut so that I don't
have to type this everytime I want to type in "Select * from ". Anyone who
has done this kinda thing?
Thanks for the help!
-JoshHi
Having SELECT * FROM in production code is not recommended therefore the
need to do this would only be in ad-hoc queries or possibly EXIST clauses,
therfore the need to do this may not be as great as you expect, expecially a
s
it is not saving that much typing.
You can assign keys in management studio to run procedures, but not to
insert text. What you may want to look at is the ability to create SELECT
statements from the object explorer (F8) by right clicking the stored
procedure and using the script option to script a select statement to file,
window or the clipboard. Also if you want to insert object names into a
window the object can be dragged across from the object explorer and the nam
e
will be inserted. If you drag the columns branch of a table across you will
get a list of all columns.
You can also create your own templates (see the template explort CTRL+ALT+T)
if you have more complex code needs (such as a stored procedure with standar
d
error handling block). Templates can contain substitute values such as
SELECT * FROM <schema_name, sysname, dbo>.<table_name, sysname, sample_table
>
John
"Kodi" wrote:

> Hi,
> I am trying to create a simple template in Managament Studio editor that h
as
> "Select * from " and try to invoke it by keyboard short cut so that I don'
t
> have to type this everytime I want to type in "Select * from ". Anyone who
> has done this kinda thing?
> Thanks for the help!
> -Josh

How to use Convert date statement in cmdInsert.Parameters.Add("Date",SqlDbType.DateTime).V

Hi

I am using SQL 2005, VB 2005

I am trying to insert a record using parameters using the following code as per MotLey suggestion and it works fine

string insertSQL;

insertSQL ="INSERT INTO Issue(ProjectID, TypeofEntryID, PriorityID ,Title, Area) VALUES (@.ProjectID, @.TypeofEntryID, @.PriorityID ,@.Title, @.Area)";

cmdInsert SqlCommand;

cmdInsert=new SqlCommand(insertSQL,conn);

cmdInsert.Parameters.Add("@.ProjectID",SqlDbType.Varchar).Value=ProjectID.Text;

My query is how to detail with dates my previous code was

insertSQL +="convert(datetime,'" +DateTime.Now.ToString("dd/MM/yy") +"',3), '";

I tried the code below but the record doesn't save?

string date = DateTime.Now.ToString("dd/MM/yy");

insertSQL = "INSERT INTO WorkFlow(IssueID, TaskID, TaskDone, Date ,StaffID) VALUES (@.IDIssue, @.IDTask, @.TaskDone, convert(DateTime,@.Date,3),@.IDStaff)";

cmdInsert.Parameters.Add("IDIssue", SqlDbType.Int).Value = IDIssue.ToString();

cmdInsert.Parameters.Add("IDTask",SqlDbType.Int).Value = IDTask.Text;

cmdInsert.Parameters.Add("TaskDone",SqlDbType.VarChar).Value = TaskDoneTxtbox.Text;

cmdInsert.Parameters.Add("Date",SqlDbType.DateTime).Value = date;

cmdInsert.Parameters.Add("IDStaff",SqlDbType.Int).Value = IDStaff.Text;

Could someone point to me in the right direction?

Thanks in advance

yazzy:


string insertSQLstring date = DateTime.Now.ToString("dd/MM/yy");

insertSQL = "INSERT INTO WorkFlow(IssueID, TaskID, TaskDone, Date ,StaffID) VALUES (@.IDIssue, @.IDTask, @.TaskDone, convert(DateTime,@.Date,3),@.IDStaff)";

cmdInsert.Parameters.Add("IDIssue", SqlDbType.Int).Value = IDIssue.ToString();

cmdInsert.Parameters.Add("IDTask",SqlDbType.Int).Value = IDTask.Text;

cmdInsert.Parameters.Add("TaskDone",SqlDbType.VarChar).Value = TaskDoneTxtbox.Text;

cmdInsert.Parameters.Add("Date",SqlDbType.DateTime).Value = date;

cmdInsert.Parameters.Add("IDStaff",SqlDbType.Int).Value = IDStaff.Text;

Why don't you just directly pass DateTime.Now to the @.Date parameter (SqlDbType.DateTime)?

|||

Sorry,

I am having a hard time trying to figure out what it is that you are trying to do. Convert(datetime,@.Date,3) does NOTHING. @.Date is already a datetime, and the third parameter isn't valid for datetime to datetime ?conversions?.

I think you are trying to store a date into a datetime column? Or is the date field a varchar?

If it's a datetime, then change:

insertSQL = "INSERT INTO WorkFlow(IssueID, TaskID, TaskDone, Date ,StaffID) VALUES (@.IDIssue, @.IDTask, @.TaskDone, convert(DateTime,@.Date,3),@.IDStaff)";

to:

insertSQL = "INSERT INTO WorkFlow(IssueID, TaskID, TaskDone, Date ,StaffID) VALUES (@.IDIssue, @.IDTask, @.TaskDone, @.Date,@.IDStaff)";

andchange:

cmdInsert.Parameters.Add("Date",SqlDbType.DateTime).Value = date;

to:

cmdInsert.Parameters.Add("Date",SqlDbType.DateTime).Value = Now.Date

*OR* change your insert to:

insertSQL = "INSERT INTO WorkFlow(IssueID, TaskID, TaskDone, Date ,StaffID) VALUES (@.IDIssue, @.IDTask, @.TaskDone, DATEADD(d,0,DATEDIFF(d,0,getdate())),@.IDStaff)";

and remove the Date parameter completely (Which is the method I prefer, since I use the SQL Server's clock exclusively).

|||

Hi Motley

I am trying to store a date into a datetime column. The thrid parameter is to store the date in 'dd/MM/yy' formate (i.e UK date)

Because I have to store date in the above formate, it is causing when I try to insert/update a record.

Sorry I had not included this information.

Speak soon

how to use convert data type from int to char in select statement

when i insert data from outside
i use insert into ....select...
can i use IDENTITY ( seed ,increment ) in my statement.
for example i have table
aaaaa AAA
bbbbb BBB
ccccc CCC

can i insert into new table with data

1 aaaaa AAA
2 bbbbb BBB
3 ccccc CCC
thanks

You can use cast (or convert)

create table test
(
integerValue int
)
insert into test
select 1
union all
select 232525
union all
select 2325
union all
select 525
union all
select 99232525

select integerValue, cast(integerValue as varchar(10)) as varcharValue
from test

integerValue varcharValue

1 1

232525 232525

2325 2325

525 525

99232525 99232525

|||

Well, first, don't change the meaning of a thread...I already answered it as it was originally asked Smile


create table test
(
value char(5),
otherValue char(3)
)
insert into test
select 'aaaaa','AAA'
union all
select 'bbbbb','BBB'
union all
select 'ccccc','CCC'

You can do this:


select identity(int,1,1) as test2Id,
value,
otherValue
into test2
from test

Or if you just wanted numbers, you can do this (in 2005) to get control over the values.


select row_number() over (order by value) as test3Id,
value,
otherValue
into test3
from test
go

|||sorry, i have change my question. can you answer me.