How to INSERT text that contains single quotes?

When users enter text into a textbox, to be INSERTed into my table, SQL Server throws an error if their text contains a single quote.

For example, if they enter "It's great!" then it causes this error:
Error: Incorrect syntax near 's'. Unclosed quotation mark after the character string ''.

How can I allow text with single quotes to be inserted into the table?

Here's my code:

string strInsert = "INSERT INTO [Comments] ([GameID], [UserID], [Comment]) VALUES (@.GameID, @.UserID, @.Comment)";

SqlConnection myConnection = new SqlConnection(<<myconnectionstuff>>);
SqlCommand myCommand = new SqlCommand(strInsert, myConnection);

myCommand.Parameters.Add( "@.GameID", Request.QueryString["GameID"] );
myCommand.Parameters.Add( "@.UserID", (string)Session["UserID"] );
myCommand.Parameters.Add( "@.Comment", ThisUserCommentTextBox.Text );

try {
myCommand.Connection.Open();
myCommand.ExecuteNonQuery();
}

catch (SqlException ex) {
ErrorLabel.Text = "Error: " + ex.Message;
}

finally {
myCommand.Connection.Close();
}

You write C# so they are called string literals in C# but in ANSI SQL they are Delimiters per ANSI SQL 92 and SQL Server is compliant so run a search for Qouted Identifier in SQL Server BOL(books online) and try the link below for SQL Server Delimiters. Hope this helps.

http://msdn2.microsoft.com/en-gb/library/ms176027.aspx

|||That code looks fine. Are you sure that is where the problem is?|||

(How can I allow text with single quotes to be inserted into the table?)

The person have not posted the code with the error but I replied the above and the error generated.

|||

Motley:

That code looks fine. Are you sure that is where the problem is?

Well it works fine unless I include a single quote in the text box somewhere.

Caddre, I've researched some more based on your reply, but I don't have easy access to the SQL Server settings - I'm on a shared hosting account. Is there some other workaround, maybe using a regular expression to replace a single quote with something else, at least temprarily while I'm processing the text? I tried entering /' instead, to see if an escape character would work, but SQL Server still won't take it.

|||

Yes there is SET QUOTED_IDENTIFIER ON/OFF will help you deal with it. Try the link below for details.

http://msdn2.microsoft.com/en-US/library/ms174393.aspx

|||

Caddre:

Yes there is SET QUOTED_IDENTIFIER ON/OFF will help you deal with it. Try the link below for details.

http://msdn2.microsoft.com/en-US/library/ms174393.aspx

I'm wondering how to send the "SET QUOTED_IDENTIFIER ON" command within my ASP.NET page.

I'm using a command called ExecuteNonQuery(); -- so does that mean I can just append it to the beginning of my INSERT string, like this?

string strInsert = "SET QUOTED_IDENTIFIER ON GO INSERT INTO [Comments] ([GameID], [UserID], [Comment]) VALUES ..."

Does it last only during this query or will it stay on somehow until I turn it off?

Sorry, I'm a newbie with SQL Server and ASP.NET.

|||You have to use it in your stored proc and ExecuteNonQuery takes stored proc. Hope this helps|||

Yes, I understand. It should work fine for any value you place within the textbox control. That includes quotes. I do this quite often with no problems.

As for can you put the SET statement before your query, the answer is yes, but do not separate the commands with GO. You may place a semi-colon between them if you want to however, like:

SET {stuff}; SELECT {stuff}

|||

OK, I am trying to make sense of this, and it's just not making any sense to me. (I have limited experience with SQL and SQL Server and VBScript; I paid a programmer to write a script and after delivering a partly-working script, he promised to fix it and then disappeared. I think he couldn't solve this problem himself.)

I am getting the error message "Unclosed quotation mark after the character string" when I execute this SQL statement from VBScript, butno error when I execute the same SQL statement from within SQL Management Studio. Any suggestions would be extremely welcome.

INSERT INTO Raw_Datafeeds (ProductID,Name,MerchantID,Merchant,Link,Thumbnail,BigImage,Price,RetailPrice,Category,SubCategory,Description,Custom1,Custom2,Custom3,Custom4,Custom5,LastUpdated,Status,Last_Loaded)
VALUES
('454848129','Healthy Ears Ear Cleanser - Buy 2 Get 1 Free','10052',
'Pet Nutrition Products','http://www.shareasale.com/m-pr.cfm?merchantID=10052&userID=YOURUSERID&productID=454848129',
'http://www.petnutritionproducts.com/images/products/100/cat_ears.jpg',
'http://www.petnutritionproducts.com/images/products/400/cat_ears.jpg',15.98,0,'Home/Family','Pets',
'Healthy Ears Ear Cleanser is a veterinarian formulated combination of gentle ingredients that aid in the removal of dirt, wax and other unwanted matter from your cat''s ears.',
'Dog','Ear Care',' ',' ',' ','6/6/2006 1:35:01 AM','instock','10/25/2006 4:06:00 PM')

|||

The original problem in this thread could probably be solved by specifying the type of each parameter rather than letting the system guess.

As for mark's problem, it's obviously related to "cat''s" in your insert string, however since you haven't posted the code you are using in VBScript, it'd be difficult to say where the problem is. As a quick hack, you can replace all quotes in any parameter with '+CHAR(39)+' so that cat's would then become cat'+CHAR(39)+'s.

How to Insert Summary Info in a table at midnight everyday?

I have to populate a summary table everyday.
Need to count the number of (STATUS =)'Opened', 'Testing', 'Closed' and
'Pending' from the Cases table and INSERT these values into the
'Summary' Table at 10:00 PM everyday.
How do I schedule an Auto INSERT? Any help will be greatly appriciated
You can simply schedule a job using the SQL Agent job scheduler. Look in
BooksOnLine for more details on scheduling or SQL Agent.
Andrew J. Kelly SQL MVP
"rabig" <rabig@.yahoo.com> wrote in message
news:1161137144.561928.202420@.i3g2000cwc.googlegro ups.com...
>I have to populate a summary table everyday.
> Need to count the number of (STATUS =)'Opened', 'Testing', 'Closed' and
> 'Pending' from the Cases table and INSERT these values into the
> 'Summary' Table at 10:00 PM everyday.
> How do I schedule an Auto INSERT? Any help will be greatly appriciated
>
sql

how to insert sp_helptext output into a table?

hi Freinds,
SQL 2000
How can I insert the output of sp_helptext mytriggername into a table?
Sp_helptext out put is a table ! I need to collect the rows into a custom
table.
Thanks in advance,
PatCREATE TABLE #trigger
(
[text] VARCHAR(8000)
)
INSERT #trigger EXEC sp_helptext insContactEmailFormatNull
SELECT * FROM #trigger
DROP TABLE #trigger
Of course, you could have silly triggers that go more than 8000 characters
without a carriage return. In that case I would suggest CREATE TABLE
#PeopleToShoot(EmployeeID INT) ...
A
"Patrick" <patriarck@.gmail.com> wrote in message
news:%23OW3DK1oFHA.3380@.TK2MSFTNGP12.phx.gbl...
> hi Freinds,
> SQL 2000
> How can I insert the output of sp_helptext mytriggername into a table?
> Sp_helptext out put is a table ! I need to collect the rows into a custom
> table.
> Thanks in advance,
> Pat
>|||> ... CREATE TABLE
#PeopleToShoot(EmployeeID INT) ...
I'd suggest a permanent table for that.
ML

How to insert resultset from SP into a table? or use Select * From

I tried this (and other variations) in Query Analyzer - which did not work.
select * from (exec stp_Report_MonthlyCountHistory_Summary '24', '%',
'3/13/06', '3/13/06')
Is there a way to Select/insert the resultset from an SP? How to do this?
Thanks,
RichRich,
Create a temporary or permanent table with the same structure as the result
of the sp. Use:
insert into #t1
exec dbo.usp_p1 ...
AMB
"Rich" wrote:

> I tried this (and other variations) in Query Analyzer - which did not work
.
> select * from (exec stp_Report_MonthlyCountHistory_Summary '24', '%',
> '3/13/06', '3/13/06')
> Is there a way to Select/insert the resultset from an SP? How to do this?
> Thanks,
> Rich
>

How to insert resultset from SP into a table? or use Select *

Thank you. Yes, I did see that this is the only way to do it. My objective
is that an SP which I did not create and don't want to modify returns a
dataset that I need to order. So I want to read/write that data into a temp
table so that I can order it.
"Alejandro Mesa" wrote:
> Rich,
> Create a temporary or permanent table with the same structure as the resul
t
> of the sp. Use:
> insert into #t1
> exec dbo.usp_p1 ...
>
> AMB
> "Rich" wrote:
>Rich,
If you can order the resultset at the client side, then do it and you will
be liberating some load from your sql server.
AMB
"Rich" wrote:
> Thank you. Yes, I did see that this is the only way to do it. My objecti
ve
> is that an SP which I did not create and don't want to modify returns a
> dataset that I need to order. So I want to read/write that data into a te
mp
> table so that I can order it.
> "Alejandro Mesa" wrote:
>sql

How to insert records into two tables that references each other?

I have two tables , A1 and B1 . B1 has a reference key pointing to A1.ID which is a primary key. Now I want to INSERT a record into both table.

CREATE TABLE a1 (
T_ID INTEGER IDENTITY(1,1) PRIMARY KEY,
T_Test INTEGER NOT NULL,
);

CREATE TABLE B1 (
B_ID INTEGER IDENTITY(1,1) PRIMARY KEY,
T_SID INTEGER NOT NULL REFERENCES a1(T_ID),
);

-- A function to get the next primary key value

-- Get the next unique key from A1
CREATE FUNCTION [dbo].[getTicketNo]
(
)
RETURNS INT
AS
BEGIN
RETURN (SELECT Max(T_ID) from A1)
END

I can only insert a record into a table at a time. and I rely on dbo.getTicketNo() to get the lastest T_ID

I wonder how do you insert a new record into A1 and B1 and maintain referential integrity? You must call Insert twice to accomplish the job right?

What I want to insert a record into B1.SID is the primary key value (T_ID) that was created during "Insert into A1 values (0)", using dbo.getTicketNo()
I worry if I am going to insert a record into A1 first, then insert a record into B1 next, there is a chance the data wont be consistent; as I illustrated a situation below:

t A1 B1
0 (0)Insert
1 (1)Insert
2 (0)Insert
3 (1)Insert

dbo.getTicketNo() at t3 may return the wrong value if another process (1)insert is executed, that will change the primary key number to 2 which is incorrect

Can I lock up A1 and then unlock A1 after I finished working with B1?

If so, what would happen to the application if it tries to access into A1 during it is locked?

If you ask me this not a good approach to get the identity value.
You can use the following objects to get the last inserted identity value.

@.@.Identity or Scope_Identity()

1.Insert the data on Master table (A1)
2.Then get the last inserted value from those system functions or global varibale
3.Use the values on Child table (B1)

Example,

Insert into A1(T_Test) values (@.T_Test);
Insert into B1(T_SID) values (@.@.Identity)
--or
Insert into B1(T_SID) values (Scope_Identity())

NOTE: Don't use MAX function to get the last inserted identity value. It may be
Inserted by other concurrent users.

|||

Thank you ManiD

From your code, is there any chance that another INSERT query is executed by other concurrent users after the 1st Insert and before the 2nd Insert in your case?

time 0:00:00 (User 0) Insert into A1(T_Test) values (@.T_Test);
time 0:00:20 (User 1) Insert into A1(T_Test) values (@.T_Test);
time 0:00:21 (User 0) Insert into B1(T_SID) values (@.@.Identity)

If it is possible, what is the best approach to ensure that the second Insert into B1 is consistent to the master A1?

Thanks for your precious time

|||

In this case,

@.@.Identity hold the User1 Identity data..

So it always good idea to use SCOPE_IDENTITY() instead of using @.@.Identity..

SCOPE_IDENTITY always hold the current user's identity data...

|||

Ok,,, thank you ManiD

Let me do some research on SCOPE_Indentity() ...

Thank you for your kind replies

|||

When I google it, found some people mispelled SCOPE_IDENTITY() to SCOPE_INDENTITY() .....

Here is the MSDN for SCOPE_IDENTITY() if anyone got the similar issues can have a look

http://msdn2.microsoft.com/en-us/library/ms190315.aspx

How to Insert Raw text into SQL table

HI all,
I have raw text like these:
test1
test2
test3
...
I want to pass these text in to a SP from Query Analyzer as a paremeter and
the SP will handle to import them to a sql table exactly like the format I
passed in: In this case sql table should store:
test1
test2
test3
...
How do you handle things like this.
Thanks,
tomtom d wrote:
> HI all,
> I have raw text like these:
> test1
> test2
> test3
> ...
> I want to pass these text in to a SP from Query Analyzer as a
> paremeter and the SP will handle to import them to a sql table
> exactly like the format I passed in: In this case sql table should
> store:
> test1
> test2
> test3
> ...
> How do you handle things like this.
> Thanks,
> tom
You can try using the DTS Import-Export Wizard or BCP.
David Gugick
Imceda Software
www.imceda.com|||Hi
Try:
create table mytext ( col1 int not null identity(1,1), textval text )
CREATE PROCEDURE InsertMyText ( @.txtval text )
AS
INSERT INTO MyText ( textval ) VALUES ( @.txtval )
EXEC InsertMyText 'text1
text2
text3'
EXEC InsertMyText '
test1
test2
test3'
select * from MyText
"tom d" wrote:

> HI all,
> I have raw text like these:
> test1
> test2
> test3
> ...
> I want to pass these text in to a SP from Query Analyzer as a paremeter an
d
> the SP will handle to import them to a sql table exactly like the format I
> passed in: In this case sql table should store:
> test1
> test2
> test3
> ...
> How do you handle things like this.
> Thanks,
> tom

how to insert picture into a table

how do I do that?
can I do this in query analyzer?Depends on your coding language, you should check in google for stream
+upload +sql server +<yourcodinglanguage>
Or just write your coding language here, perhaps we can provide you with
some interesting links.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:OHvwa4UtFHA.1444@.TK2MSFTNGP10.phx.gbl...
> how do I do that?
> can I do this in query analyzer?
>|||my table pictures is empty.
CREATE TABLE [dbo].[pictures] (
[pid] [int] NULL ,
[picture] [image] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
I use textcopy.exe in dos environment, but I got error.
WHAT HAPPEN? I don't understand ERROR: Row retrieval failed.
I want to insert it, not update it. does that mean textcopy.exe only do
update?
TEXTCOPY Version 1.0
DB-Library version 8.00.2039
Type the SQL Server to connect to: LOCALW
Type your login: brit
Type your password: brit
Type the database: Northwind
Type the table: pictures
Type the text or image column: picture
Type the where clause: where 1=1
Type the file: C:\teaser_head2.jpg
Type the direction ('I' for in, 'O' for out): I
ERROR: Row retrieval failed.
"Jens Smeyer" < Jens@.remove_this_for_contacting_sqlserve
r2005.de> wrote in
message news:OF28HKVtFHA.3236@.TK2MSFTNGP14.phx.gbl...
> Depends on your coding language, you should check in google for stream
> +upload +sql server +<yourcodinglanguage>
> Or just write your coding language here, perhaps we can provide you with
> some interesting links.
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Britney" <britneychen_2001@.yahoo.com> wrote in message
> news:OHvwa4UtFHA.1444@.TK2MSFTNGP10.phx.gbl...
>|||Jen,
I don't want to use coding language such as C# or java to insert
picture,
I only want to use SQL build-in utility and/or straight SQL query to do
that. so I don't think textcopy is an option since it only does update.
"Jens Smeyer" < Jens@.remove_this_for_contacting_sqlserve
r2005.de> wrote in
message news:OF28HKVtFHA.3236@.TK2MSFTNGP14.phx.gbl...
> Depends on your coding language, you should check in google for stream
> +upload +sql server +<yourcodinglanguage>
> Or just write your coding language here, perhaps we can provide you with
> some interesting links.
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Britney" <britneychen_2001@.yahoo.com> wrote in message
> news:OHvwa4UtFHA.1444@.TK2MSFTNGP10.phx.gbl...
>|||You can simply use DDL:
CREATE TABLE [dbo].[pictures] (
[pid] [int] NULL ,
[picture] [image] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
insert into pictures
values (1,0x000000000101020201010)
So that part is easy. The hard bit is that you have to take your image and
turn it into the hexidecimal format. Can't help you there, I regret.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:%238hiZfVtFHA.3080@.TK2MSFTNGP15.phx.gbl...
> Jen,
> I don't want to use coding language such as C# or java to insert
> picture,
> I only want to use SQL build-in utility and/or straight SQL query to do
> that. so I don't think textcopy is an option since it only does update.
>
>
>
> "Jens Smeyer" < Jens@.remove_this_for_contacting_sqlserve
r2005.de> wrote
> in message news:OF28HKVtFHA.3236@.TK2MSFTNGP14.phx.gbl...
>|||One way to hack around it is to initialize row for textcopy and then update
picture in that row using textcopy.exe
so when you do select picture from the table, you 'll see hexidecimal code.
unfortunately, I can't set image to a local variable in sql server. I guess
I can't avoid application programming then.
thanks
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:efRiZ5VtFHA.1168@.TK2MSFTNGP10.phx.gbl...
> You can simply use DDL:
> CREATE TABLE [dbo].[pictures] (
> [pid] [int] NULL ,
> [picture] [image] NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
> insert into pictures
> values (1,0x000000000101020201010)
> So that part is easy. The hard bit is that you have to take your image
> and turn it into the hexidecimal format. Can't help you there, I regret.
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often
> convincing." (Oscar Wilde)
> "Britney" <britneychen_2001@.yahoo.com> wrote in message
> news:%238hiZfVtFHA.3080@.TK2MSFTNGP15.phx.gbl...
>

how to insert only distinct values from a Flat File

I have to insert the Values from the Flat Files , My table structures have Primary keys , how do i insert only the distinct values into the table without the ERROR VIOLATION OF Primary Key already exists a record.

Dropping and Adding Relationships after insert is a way but doesnt serve the whole purpose is there a way we can eliminate duplicate records based on their Primary key before inserting them into the Database.

You can do a lookup against your target table. Redirect the error output and send these rows to a sql destination for the target table (i.e. if the lookup didn't find it, it is a new row)

See the following thread...

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1211340&SiteID=1|||

Sort transformation has a check box that allows you to eliminate duplicates; another approach could be to use a agregation transformation.|||Thanks for the Quick Solutions i will do that and come back with the feedback, what about the performance which one is better the sort or Aggregates using group by or Lookup|||Are there duplicate primary key's in your flat file? If so you will need to go with either the aggregate or sort solutions (I may have misread your question, the lookup will find any primary keys already in the table before you start to insert your new records). I don't know which of these will be more effecient for you, but you can always try them both out for a few test runs and see for yourself as it will often depend on environment and data being loaded as to which will come out ahead.|||

The Lookup approach will not detect duplicates within the batch being processed. You would need to change it to no-cache mode. Sort and aggregation transformation should be give you about the same performance. In general the no-cache lookup, sort or aggregation approach are not great from the performance standpoint; but that would depend on many factors; so test and measure yourself.

Sort/aggregation transformation cache the full set of rows in RAM; so if the volume of data to be de-duplicated is huge; the system could run out of memory.

An alternative could be to use an staging table and then let the DB engine to do the dedup work. (e.g. http://rafael-salas.blogspot.com/2007/04/remove-duplicates-using-t-sql-rank.html )|||

I got to filter out the distinct values using aggregate transformation but i am unsure baout how to get all the columns into the output ... as the Aggregate is returning only the columns used to get the distinct on the Primary Keys.

|||There are two fundamentals being discussed in this thread. We've yet to get the crucial question answered though:

Are you trying to dedupe the source data before going into the destination table, or are you trying to prevent duplicate records from getting inserted (unique data from the source, but not necessarily all unique in the destination) and hence raising a primary key violation?

Deduping the source data can be done with an aggregate or the sort transformations. You could also load the data into a staging table (as Rafael stated) and then run a SQL statement against that data (select DISTINCT perhaps). Using the techniques described in the thread linked to earlier, you can ensure that your data does not violate primary keys by using a lookup. Note that you may have to do a combination of all of the above.|||

Dev2624 wrote:

I got to filter out the distinct values using aggregate transformation but i am unsure baout how to get all the columns into the output ... as the Aggregate is returning only the columns used to get the distinct on the Primary Keys.

Use the sort transformation instead.|||I am trying to Prevent the Duplicate Records from getting inserted . i am working on it will post back with the results. Thanks!!!sql

How to insert on table with a variable name

Here is the jist of what I want to do, but I know the syntax is wrong...please help

CREATE PROCEDURE mySP
(
@.tablename As varChar(50),
@.input_name As varChar(100)
)
As
INSERT @.tablename (name)
VALUES (@.input_name)

assume the table exists already, and has a 'name' field in it.

thanks guysKatie,

I can't see why you would want to do that. However, the exact code would be:

exec ('INSERT INTO ' + @.tableName + ' VALUES (' + @.input_name + ')')

Be aware that

a) You really should avoid using 'exec' where possible since 'exec' will hinder Sql Server in pre-compiling the Stored Procedure
b) You really want to use a column-list with insert.

regards,

Kristof|||Originally posted by beyond cool
Katie,

I can't see why you would want to do that. However, the exact code would be:

exec ('INSERT INTO ' + @.tableName + ' VALUES (' + @.input_name + ')')

Be aware that

a) You really should avoid using 'exec' where possible since 'exec' will hinder Sql Server in pre-compiling the Stored Procedure
b) You really want to use a column-list with insert.

regards,

Kristof

is there a way around using 'exec'?|||Originally posted by beyond cool
Katie,

I can't see why you would want to do that. However, the exact code would be:

exec ('INSERT INTO ' + @.tableName + ' VALUES (' + @.input_name + ')')

Be aware that

a) You really should avoid using 'exec' where possible since 'exec' will hinder Sql Server in pre-compiling the Stored Procedure
b) You really want to use a column-list with insert.

regards,

Kristof

Yea, I just know basics of stored procedures, I'd love to hear how this sort of thing is usually done.

How to insert numbers from a text box to a Sql database table column’s type numeric?

Hi,

I am getting an error when I try to insert a number typed in a text box control into a Sql database table column's type numeric(6,2). For example: If I type 35,22 into the text box, or 35, and then I submit the form to insert the data into database, I get the follow error:

System.FormatException: Input string was not in a correct format.
Line 428: CmdInsert.Parameters.Add(New SqlParameter("@.Measure", SqlDbType.decimal, "Measure"))

A piece of the SP inside Sql server:

USE market26
GO
ALTER PROC new_offer
@.Offer_id bigint, @.Measure numeric(6,2) = null, …

What is wrong? Why it doesn' t accept the number typed in my text box?

Thank you,
CesarThis is not correct:

CmdInsert.Parameters.Add(New SqlParameter("@.Measure", SqlDbType.Decimal, "Measure"))

This would probably work (not tested):

SqlParameter mParm = new SqlParameter("@.measure", SqlDbType.Decimal);
mParm.Size = 13; // Max size in Bytes of the Decimal number
mParm.Precision = 8; // Total number of digits allowed (right + left of decimal point
mParm.Scale = 2; // Set the number of decimal places the Parameter value is resolved
mParm.set_IsNullable(true);
mParm.Value = decimal.Parse(myTextBox.Text.Trim());
CmdInsert.Parameters.Add(mParm)

|||Hi,

I always write my ASP.NET commands to talk with Sql Stored Procedures thus:

CmdInsert.Parameters.Add(New SqlParameter("@.Measure", SqlDbType.Decimal, "Measure"))

I think that it is correct, and it works fine.

I have never used a text field to insert numeric data into Sql database, but, I have to define all these parameter properties in order to pass a numeric data type to the database?

Thanks|||That's interesting. There is no Constructor attributed to the SqlParameter Class which accepts a Constructor looking like this:

New SqlParameter(<string>,<SqlDbType Enum>, <string>)

as far as I know...But hey, go for it!|||Take a look at this piece of an article and let me know what do you think about it:

Dim sConnectionString As String = _
 "server=localhost;uid=sa;pwd=;database=Northwind"
Dim cnNorthwind As New SqlConnection(sConnectionString)
Dim cmdOrders As New SqlCommand("CustOrderHist", cnNorthwind)
cmdOrders.CommandType = CommandType.StoredProcedure
' Set up parameter for stored procedure
Dim prmCustomerID As New SqlParameter()
prmCustomerID.ParameterName = "@.CustomerID"
prmCustomerID.SqlDbType = SqlDbType.VarChar
prmCustomerID.Size = 5
prmCustomerID.Value = "ALFKI"
cmdOrders.Parameters.Add(prmCustomerID)
Dim daGetOrders As New SqlDataAdapter(cmdOrders)
Dim dsOrders As New DataSet()
daGetOrders.Fill(dsOrders, "Orders")
DataGrid1.DataSource = dsOrders.Tables("Orders")

This example makes all the parameter settings explicit. Some developers like this style, and it's good for instructional purposes. However, some developers prefer an equivalent alternative that has fewer lines of code:

Dim sConnectionString As String = _
 "server=localhost;uid=sa;pwd=;database=Northwind"
Dim cnNorthwind As New SqlConnection(sConnectionString)
Dim cmdOrders As New SqlCommand("CustOrderHist", cnNorthwind)
cmdOrders.CommandType = CommandType.StoredProcedure
cmdOrders.Parameters.Add(New _
 SqlParameter("@.CustomerID", SqlDbType.VarChar, 5))
cmdOrders.Parameters("@.CustomerID").Value = "ALFKI"
Dim daGetOrders As New SqlDataAdapter(cmdOrders)
Dim dsOrders As New DataSet()
daGetOrders.Fill(dsOrders, "Orders")
DataGrid1.DataSource = dsOrders.Tables("Orders")

This code behaves exactly the same as the previous example. However, it only needs two lines of code for each parameter rather than six. When a stored procedure has a lot of parameters (as some of our later examples do), this can be quite a difference on lines of code required, so we'll use that form from this point onward.

The complete article is here: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadvnet/html/vbnet09102002.asp|||Hey! Perhaps you have misunderstood me. I meant that I always use this structure:
(Which I knew that something was wrong)

CmdInsert.Parameters.Add(New SqlParameter("@.Measure", SqlDbType.Decimal, "Measure"))

Instead of this:

SqlParameter mParm = new SqlParameter("@.measure", SqlDbType.Decimal)
mParm.SourceColumn = "Measure"
CmdInsert.Parameters.Add(mParm)

I knew that something was wrong, I suspected that something it lacked in my SqlParameter, and you helped me with your example!, it lacked only the size property which in my case is 5. I am sorry, the only thing that I saw strange is the structure you wrote, and the amount of parameters which I don' t need to use (in this case). I always use the shorter alternative ;-).

So, the correct SqlParameter in my case is:

CmdInsert.Parameters.Add(New SqlParameter("@.Measure", SqlDbType.Decimal,5, "Measure"))

And now works fine.
Thank you very much!
Cesar

How to insert NULL when using bcp_moretext?

How do you insert a NULL value when using bcp_moretext?
If I have a table with two 'text' or 'image' columns, and I am using bcp_mor
etext to insert a very large LOB into one of them, then BCP requires that I
use bcp_moretext for both of them ("use it for one 'text'/'image', then must
use it for all").
(In fact, I've discovered that I can bcp_bind a NULL with a non-NULL vardata
address for earlier 'text'/'image', but once I bcp_bind a NULL vardata, ind
icating my wish to use bcp_moretext, then all following 'text'/'image' colum
ns in ordinal order must al
so be bound with a NULL vardata -- i.e. once you use bcp_moretext for a 'tex
t'/'image', it's really all *following* that must be also sent with bcp_more
text. At least that's the behavior I see.)
I'm using the SQL Server 7 ODBC driver extensions (rather than 6.5 DB-LIB BC
P). Setting the param length to zero (bcp_bind or bcp_collen) for 7.0 BCP te
lls it that you are sending a zero-length param. You still need to call bcp_
moretext later with length
0, for that column. Setting length to SQL_NULL_DATA will cause an error late
r, whether you try to call bcp_moretext with length SQL_NULL_DATA, with leng
th 0, or try to skip calling bcp_moretext for this column.
I don't believe it's possible to send NULL, via BCP, to a 'text' or 'image'
column, once you've sent 0 or >0 length data to any earlier (in ordinal orde
r) 'text' or 'image' column using bcp_moretext. But, I would like Microsoft
to verify this, since the d
ocumentation doesn't cover this at all.
Thanks,
Jim FloodCan I expect some sort of response from Microsoft in this group within two d
ays, as part of the benefit of my MSDN subscription?
-- Jim Flood wrote: --
How do you insert a NULL value when using bcp_moretext?
If I have a table with two 'text' or 'image' columns, and I am using bcp_mor
etext to insert a very large LOB into one of them, then BCP requires that I
use bcp_moretext for both of them ("use it for one 'text'/'image', then must
use it for all").
(In fact, I've discovered that I can bcp_bind a NULL with a non-NULL vardata
address for earlier 'text'/'image', but once I bcp_bind a NULL vardata, ind
icating my wish to use bcp_moretext, then all following 'text'/'image' colum
ns in ordinal order mu
st also be bound with a NULL vardata -- i.e. once you use bcp_moretext for a
'text'/'image', it's really all *following* that must be also sent with bcp
_moretext. At least that's the behavior I see.)
I'm using the SQL Server 7 ODBC driver extensions (rather than 6.5 DB-LIB BC
P). Setting the param length to zero (bcp_bind or bcp_collen) for 7.0 BCP te
lls it that you are sending a zero-length param. You still need to call bcp_
moretext later with le
ngth 0, for that column. Setting length to SQL_NULL_DATA will cause an error
later, whether you try to call bcp_moretext with length SQL_NULL_DATA, with
length 0, or try to skip calling bcp_moretext for this column.
I don't believe it's possible to send NULL, via BCP, to a 'text' or 'image'
column, once you've sent 0 or >0 length data to any earlier (in ordinal orde
r) 'text' or 'image' column using bcp_moretext. But, I would like Microsoft
to verify this, since
the documentation doesn't cover this at all.
Thanks,
Jim Flood|||I'm checking with our BCP guy.
Brannon Jones
Developer - MDAC
This posting is provided "as is" with no warranties and confers no rights.
"Jim Flood" <bezspam@.bezspam.cz> wrote in message
news:C95C8F5A-BF58-48F3-A516-AC5980947BDE@.microsoft.com...
> Can I expect some sort of response from Microsoft in this group within two
days, as part of the benefit of my MSDN subscription?
> -- Jim Flood wrote: --
> How do you insert a NULL value when using bcp_moretext?
> If I have a table with two 'text' or 'image' columns, and I am using
bcp_moretext to insert a very large LOB into one of them, then BCP requires
that I use bcp_moretext for both of them ("use it for one 'text'/'image',
then must use it for all").
> (In fact, I've discovered that I can bcp_bind a NULL with a non-NULL
vardata address for earlier 'text'/'image', but once I bcp_bind a NULL
vardata, indicating my wish to use bcp_moretext, then all following
'text'/'image' columns in ordinal order must also be bound with a NULL
vardata -- i.e. once you use bcp_moretext for a 'text'/'image', it's really
all *following* that must be also sent with bcp_moretext. At least that's
the behavior I see.)
> I'm using the SQL Server 7 ODBC driver extensions (rather than 6.5
DB-LIB BCP). Setting the param length to zero (bcp_bind or bcp_collen) for
7.0 BCP tells it that you are sending a zero-length param. You still need to
call bcp_moretext later with length 0, for that column. Setting length to
SQL_NULL_DATA will cause an error later, whether you try to call
bcp_moretext with length SQL_NULL_DATA, with length 0, or try to skip
calling bcp_moretext for this column.
> I don't believe it's possible to send NULL, via BCP, to a 'text' or
'image' column, once you've sent 0 or >0 length data to any earlier (in
ordinal order) 'text' or 'image' column using bcp_moretext. But, I would
like Microsoft to verify this, since the documentation doesn't cover this at
all.
> Thanks,
> Jim Flood|||Hello Brannon,
Have you heard back about the BCP problem yet? Is there anything else I need
to do besides post to this group, to get the two-day response from Microsof
t? It's been well over two days.
Thanks,
Jim Flood|||Hi Jim,
I've faced the same problem. I can't insert NULLs using bcp_moretext.
Have you found a solution?
Thanks in advance,
Alberto.
"Jim Flood" <anonymous@.discussions.microsoft.com> escribi en el mensaje
news:0BB0853D-430D-4299-88D2-7343C6C024A8@.microsoft.com...
> How do you insert a NULL value when using bcp_moretext?
> If I have a table with two 'text' or 'image' columns, and I am using
bcp_moretext to insert a very large LOB into one of them, then BCP requires
that I use bcp_moretext for both of them ("use it for one 'text'/'image',
then must use it for all").
> (In fact, I've discovered that I can bcp_bind a NULL with a non-NULL
vardata address for earlier 'text'/'image', but once I bcp_bind a NULL
vardata, indicating my wish to use bcp_moretext, then all following
'text'/'image' columns in ordinal order must also be bound with a NULL
vardata -- i.e. once you use bcp_moretext for a 'text'/'image', it's really
all *following* that must be also sent with bcp_moretext. At least that's
the behavior I see.)
> I'm using the SQL Server 7 ODBC driver extensions (rather than 6.5 DB-LIB
BCP). Setting the param length to zero (bcp_bind or bcp_collen) for 7.0 BCP
tells it that you are sending a zero-length param. You still need to call
bcp_moretext later with length 0, for that column. Setting length to
SQL_NULL_DATA will cause an error later, whether you try to call
bcp_moretext with length SQL_NULL_DATA, with length 0, or try to skip
calling bcp_moretext for this column.
> I don't believe it's possible to send NULL, via BCP, to a 'text' or
'image' column, once you've sent 0 or >0 length data to any earlier (in
ordinal order) 'text' or 'image' column using bcp_moretext. But, I would
like Microsoft to verify this, since the documentation doesn't cover this at
all.
> Thanks,
> Jim Flood|||Hello Alberto,
If all of your NULLs are lined up contiguous to the left in the set of colum
ns for a given row, then just avoid bcp_moretext for those columns -- althou
gh, according to the documentation, this isn't supposed to work. But, once y
ou've set bcp_moretext for
a column, going left-to-right, then all other columns to the right will have
to be bcp_moretext'ed as well, and then you can't set any of them to NULL.
So, I have no solution.
Can I please get some kind of response from Microsoft on this problem? I bel
ieve my MSDN subscription should guarantee a two-day response time, and it h
as been *far* more than two days.
Jim Flood

How to Insert Null Values into a DataBase Field

I have a function that updates a table in sql server

If a field is left blank I want the database field set to Null. I use:
sqlCmd.Parameter.Add("@.somefield, someintvalue) to pass all my values to the database.

if someintvalue is null I want to set @.somefield to dbnull

How can I do this? I get errors if I submit a null integer when there is a foreign key constraint. What is the best way to handle this? should I just make all parameters objects? so that I can set them to DBNull.Value?

Also on a side note, when you are populating fields from a datatable, is there a better way to set the values (i.e. of a textbox) than cheking for DBNull before assigning?

check this out|||

FOREIGN KEY can allow NULL values if it is a UNIQUE constraint. Run a search in the BOL(books online) enable NULL on FOREIGN KEY. The following is from the BOL. Hope this helps.

"A FOREIGN KEY constraint does not have to be linked only to a PRIMARY KEY constraint in another table; it can also be defined to reference the columns of a UNIQUE constraint in another table. A FOREIGN KEY constraint can containnull values; however, if any column of a composite FOREIGN KEY constraint containsnull values, then verification of the FOREIGN KEY constraint will be skipped."

sql

How to insert NULL char values in SQLSERVER with a SQL sentence?

Hi everyone!
I am working with Delphi v7 and MS SQLServer.
I am trying to insert data in a table with a SQL sentence. Some of the
fields of my table are type char or varchar, and they can have null
values.
What do i have to write in the SQL sentence to insert a null value in
those fields?
I tried with '', an empty String, but it doesnt work, the tables
stores an empty String (logical :-)).
In the SQLServer GUI you have to press CTRL + 0 to insert a NULL
value, but how can i tell this to the SQLServer through a SQL
Sentence?

Well, thank you very much.On 29 Jul 2004 04:15:15 -0700, schumacker wrote:

>Hi everyone!
>I am working with Delphi v7 and MS SQLServer.
>I am trying to insert data in a table with a SQL sentence. Some of the
>fields of my table are type char or varchar, and they can have null
>values.
>What do i have to write in the SQL sentence to insert a null value in
>those fields?
>I tried with '', an empty String, but it doesnt work, the tables
>stores an empty String (logical :-)).
>In the SQLServer GUI you have to press CTRL + 0 to insert a NULL
>value, but how can i tell this to the SQLServer through a SQL
>Sentence?
>Well, thank you very much.

Hi schumacker,

INSERT INTO MyTable (Col1, Col2, Col3)
VALUES (1, NULL, 3)

or

INSERT INTO MyTable (Col1, Col2, Col3)
SELECT 1, NULL, 3

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Given:

create table foo
(col1 char(1) not null,
col2 char(1) null)

you can insert nulls to col2 by either explicitly specifying a null for the
content:

insert foo (col1, col2) values ('a',null)

or by skipping it in the column list and SQL Server will automatically
insert null:

insert foo (col1) values ('b')

Check it:

select * from foo

outputs:

col1 col2
-- --
a NULL
b NULL

"schumacker" <miguelcampoy@.hotmail.com> wrote in message
news:e1eadaf3.0407290315.99cd0d2@.posting.google.co m...
> Hi everyone!
> I am working with Delphi v7 and MS SQLServer.
> I am trying to insert data in a table with a SQL sentence. Some of the
> fields of my table are type char or varchar, and they can have null
> values.
> What do i have to write in the SQL sentence to insert a null value in
> those fields?
> I tried with '', an empty String, but it doesnt work, the tables
> stores an empty String (logical :-)).
> In the SQLServer GUI you have to press CTRL + 0 to insert a NULL
> value, but how can i tell this to the SQLServer through a SQL
> Sentence?
> Well, thank you very much.

how to insert multiple records into table

insert into table1 (colname) values (value1)

can only insert one record into the table. How to insert multiple records as value1, value2, value3... into a table?

We can of course use the above repeatedly, but if I don't know how many records (which is a variable), and I want to write a code which just take

value1, value2, value3 ...

from the clipboard, to paste as a input. How to insert those multiple records into table without split it. Thanks

What is the source of the data? from another table?

|||

If you are taking values from another table then you can insert multiple records.

Any ways i think it internally dosen't make much difference if you use multiple records or single record multiply internall it will fire that many insert statements only.

I find this intresting article guess it will help

http://blog.sqlauthority.com/2007/06/08/sql-server-insert-multiple-records-using-one-insert-statement-use-of-union-all/

|||

U want place into the same table r other clarity is required

Insert into x select * from x

insert into x select * from y

If u want to copy the data of entire table use * other wise U have to specify column clause

Thank u

Baba

Please remember to click "Mark as Answer" on this post if it helped you.

|||

I hope I'm getting your question right.

Are you looking for something like

insert into table1 ( colname )
select value1 union all
select value2 union all
.
.
.
select valueN

If you can specify your problem in details then we can help you better.

|||

Thank everyone for your input. What I want was taking the contents of multiple records delimited by delimiter from clipboard as a string, then insert into a table. I complished by writing a user function which takes a string, then output a table with multiple records, it works as

insert into table1 (ID)

select values from fn_StringToTable(@.myString, @.delimiter) -- here @.myString='00a1,00a2,00a3,...'; @.delimiter=','

output table1 got the IDs from the clipboard which has'00a1,00a2,00a3,...

Thanks

How to Insert Multiple Records into sql 2000 table at once ?

hello,
I am new to Slq 2000 Database,Now I create an asp.net application with sql 2000,
in my database I have two 2 table lets' say "OrderHead" and "OrderDetail",they look like this:
OrderHead orderdetail
--order no --orderno
--issuedate --itemname
--supplier --desccription
--amount --price
--Qty
Now I created a user-defined Collection class to storage order detail data in memory
and bind to a datagrid control.
I can transfer Collection data to xml file ,my problem as below :
There have multiple records data in my xml file,and I want to send the xml file as argument to a store procedure in sql 2000

anyone can give me some advise or some sample code ?

thanks in advanced!See links below:

1. http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm
2. http://www.eggheadcafe.com/articles/20030627c.asp|||hi, thanks a lot,I will browse the web page you give me .

How to insert more then one order into customers orders table

Hi there,
How do I insert into Customers and Orders table in Northwind database like
One customer and three orders at the same time like
Insert into Customers(ComapnyName,ContactName) Values('abc','xyz')
Insert into Orders (CustomerID,OrderDate)
VAULES
('abc','1/1/2006')
('abc','2/1/2006')
('abc','3/1/2006')
somthing like that -> multiple insert statement to one to many relationship
table
Thanks,
Oded DrorOded, shalom
Create a stored procedure and issue
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
Insert into Customers(ComapnyName,ContactName) Values('abc','xyz')
Insert into Orders (CustomerID,OrderDate) VAULES ('abc','20060101')
Insert into Orders (CustomerID,OrderDate) VAULES ('abc','20060102')
Insert into Orders (CustomerID,OrderDate) VAULES ('abc','20060103')
COMMIT TRANSACTION
"Oded Dror" <odeddror@.cox.net> wrote in message
news:OK9DoDKTGHA.4608@.tk2msftngp13.phx.gbl...
> Hi there,
> How do I insert into Customers and Orders table in Northwind database like
> One customer and three orders at the same time like
> Insert into Customers(ComapnyName,ContactName) Values('abc','xyz')
> Insert into Orders (CustomerID,OrderDate)
> VAULES
> ('abc','1/1/2006')
> ('abc','2/1/2006')
> ('abc','3/1/2006')
> somthing like that -> multiple insert statement to one to many
> relationship table
> Thanks,
> Oded Dror
>
>|||I did something like this
Insert into Orders (CustomerID,OrderDate)
select 'abc','1/1/2006' union
select 'abc','2/1/2006' union
select 'abc','3/1/2006'
not tested but it should work.
Grant
Who gives a {censored} if I am wrong.
"Oded Dror" <odeddror@.cox.net> wrote in message
news:OK9DoDKTGHA.4608@.tk2msftngp13.phx.gbl...
> Hi there,
> How do I insert into Customers and Orders table in Northwind database like
> One customer and three orders at the same time like
> Insert into Customers(ComapnyName,ContactName) Values('abc','xyz')
> Insert into Orders (CustomerID,OrderDate)
> VAULES
> ('abc','1/1/2006')
> ('abc','2/1/2006')
> ('abc','3/1/2006')
> somthing like that -> multiple insert statement to one to many
> relationship table
> Thanks,
> Oded Dror
>
>|||Uri,
The problem is when you enter a new Customer with Identity Key set to yes
How do you assign the CustomerID (you don't know what is it!) to the
Orders.CustomerID table
What I did is after finishing inserts command I added
update tblOrders set CustomerID =(select top 1 CustomerID from tblCustomers
order by CustomerID Desc)
where CustomerID IS NULL
That way I'm assigning the tblCustomer.CustomerID to tblOrders.CustomerID
It work but I'm not sure if is it the right solution?
Thanks,
Oded
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e%23vaqvKTGHA.1728@.TK2MSFTNGP11.phx.gbl...
> Oded, shalom
> Create a stored procedure and issue
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> GO
> BEGIN TRANSACTION
> Insert into Customers(ComapnyName,ContactName) Values('abc','xyz')
> Insert into Orders (CustomerID,OrderDate) VAULES ('abc','20060101')
> Insert into Orders (CustomerID,OrderDate) VAULES ('abc','20060102')
> Insert into Orders (CustomerID,OrderDate) VAULES ('abc','20060103')
> COMMIT TRANSACTION
>
>
> "Oded Dror" <odeddror@.cox.net> wrote in message
> news:OK9DoDKTGHA.4608@.tk2msftngp13.phx.gbl...
>|||Hi, Oded
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
DECLARE @.new_custid INT
BEGIN TRANSACTION
Insert into Customers(ComapnyName,ContactName) Values('abc','xyz')
SELECT @.new_custid=SCOPE_IDENTITY()
Insert into Orders (CustomerID,ComapnyName,OrderDate) VAULES
(@.new_custid,'abc','20060101')
Insert into Orders (CustomerID,ComapnyName,OrderDate) VAULES
(@.new_custid,'abc','20060102')
Insert into Orders (CustomerID,ComapnyName,OrderDate) VAULES (@.new_custid
,'abc','20060103')
COMMIT TRANSACTION
"Oded Dror" <odeddror@.cox.net> wrote in message
news:OjOoLxOTGHA.5500@.TK2MSFTNGP12.phx.gbl...
> Uri,
> The problem is when you enter a new Customer with Identity Key set to yes
> How do you assign the CustomerID (you don't know what is it!) to the
> Orders.CustomerID table
> What I did is after finishing inserts command I added
> update tblOrders set CustomerID =(select top 1 CustomerID from
> tblCustomers order by CustomerID Desc)
> where CustomerID IS NULL
> That way I'm assigning the tblCustomer.CustomerID to tblOrders.CustomerID
> It work but I'm not sure if is it the right solution?
> Thanks,
> Oded
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:e%23vaqvKTGHA.1728@.TK2MSFTNGP11.phx.gbl...
>|||Uri,
When I did
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
DECLARE @.new_custid int
BEGIN TRANSACTION
Insert into tblCustomers (FirstName,LastName,State)
Values('Bill','Gates','2')
SELECT @.new_custid = SCOPE_IDENTITY()
Insert into tblCards (StoreID,CustomerID,CardNumber)
Values('2','@.new_custid','111')
Insert into tblCards (StoreID,CustomerID,CardNumber)
Values('4','@.new_custid','222')
Insert into tblCards (StoreID,CustomerID,CardNumber)
Values('23','@.new_custid','333')
COMMIT TRANSACTION
I got an error message:
Msg 245, Level 16, State 1, Line 5
Conversion failed when converting the varchar value '@.new_custid' to data
type int.
Thanks,
Oded
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:Oxqp06OTGHA.1688@.TK2MSFTNGP11.phx.gbl...
> Hi, Oded
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> GO
> DECLARE @.new_custid INT
> BEGIN TRANSACTION
> Insert into Customers(ComapnyName,ContactName) Values('abc','xyz')
> SELECT @.new_custid=SCOPE_IDENTITY()
> Insert into Orders (CustomerID,ComapnyName,OrderDate) VAULES
> (@.new_custid,'abc','20060101')
> Insert into Orders (CustomerID,ComapnyName,OrderDate) VAULES
> (@.new_custid,'abc','20060102')
> Insert into Orders (CustomerID,ComapnyName,OrderDate) VAULES (@.new_custid
> ,'abc','20060103')
> COMMIT TRANSACTION
>
> "Oded Dror" <odeddror@.cox.net> wrote in message
> news:OjOoLxOTGHA.5500@.TK2MSFTNGP12.phx.gbl...
>|||Yes, sure .

> Insert into tblCards (StoreID,CustomerID,CardNumber)
> Values('2','@.new_custid','111')
Why did you put an quotation on '@.new_custid'? Is it VARCHAR(n) or INTEGER?
Insert into tblCards (StoreID,CustomerID,CardNumber)
Values('2',@.new_custid,'111')
"Oded Dror" <odeddror@.cox.net> wrote in message
news:u404$kVTGHA.4520@.TK2MSFTNGP10.phx.gbl...
> Uri,
> When I did
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> GO
> DECLARE @.new_custid int
> BEGIN TRANSACTION
> Insert into tblCustomers (FirstName,LastName,State)
> Values('Bill','Gates','2')
> SELECT @.new_custid = SCOPE_IDENTITY()
> Insert into tblCards (StoreID,CustomerID,CardNumber)
> Values('2','@.new_custid','111')
> Insert into tblCards (StoreID,CustomerID,CardNumber)
> Values('4','@.new_custid','222')
> Insert into tblCards (StoreID,CustomerID,CardNumber)
> Values('23','@.new_custid','333')
> COMMIT TRANSACTION
> I got an error message:
> Msg 245, Level 16, State 1, Line 5
> Conversion failed when converting the varchar value '@.new_custid' to data
> type int.
> Thanks,
> Oded
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:Oxqp06OTGHA.1688@.TK2MSFTNGP11.phx.gbl...
>|||Uri
It works!
Thanks you very much
Oded
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uzbR9oXTGHA.5908@.TK2MSFTNGP14.phx.gbl...
> Yes, sure .
>
> Why did you put an quotation on '@.new_custid'? Is it VARCHAR(n) or
> INTEGER?
> Insert into tblCards (StoreID,CustomerID,CardNumber)
> Values('2',@.new_custid,'111')
>
> "Oded Dror" <odeddror@.cox.net> wrote in message
> news:u404$kVTGHA.4520@.TK2MSFTNGP10.phx.gbl...
>sql

How to insert into Temp Table

i have temp table name "#TempResult" with column names Memberid,Month,Year. Consider this temp table alredy has some rows from previuos query. I have one more table name "Rebate" which also has columns MemberID,Month, Year and some more columns. Now i wanted to insert rows from "Rebate" Table into Temp Table where MemberID.Month and Year DOES NOT exist in Temp table.

MemberID + Month + Year should ne unique in Temp table

Maybe something like?

insert into #TempResult
select distinct
Memberid,
Month,
Year
from Rebate a
where not exists
( select 0 from #TempResult b
where a.memberid = b.memberid
and a.month = b.month
and a.year = b.year
)

Dave

|||

If you are using SQL Server 2005, you can also use the EXCEPT operator:

insert into #TempResult
select Memberid,
Month,
Year
from Rebate

except
select Memberid,
Month,
Year
from #TempResult

|||Mugambo.. thanks. but why i have to use "select 0 "instead of "select * "?|||

Lax:

You do not; I choose zero because the column selected in this case doesn't matter; what matters is whether or not the row exists. This is a semi-join? Can somebody confirm the semi-join?

Dave

|||( Maybe a left anti semi join )

How To Insert into a table with a uniqueidentifier as primary key?

I would like to insert into a table with a primary key that has a uniqueidentifier. I would like it to go up by one each time I execute this insert statement. It would be used as my ReportId

My VB code is this.

ProtectedSub btncreate_Click(ByVal senderAsObject,ByVal eAs System.EventArgs)Handles btncreate.Click'set connection stringDim errstrAsString =""Dim conn =New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;Integrated Security=True;User Instance=True")'set parameters for SPDim cmdcommand =New SqlCommand("sprocInsertNewReport", conn)

cmdcommand.commandtype = CommandType.StoredProcedure

cmdcommand.parameters.add(

"@.UserName", Session("UserName"))

cmdcommand.parameters.add(

"@.Week", vbNull)

cmdcommand.parameters.add(

"@.Date", vbDate)

cmdcommand.parameters.add(

"@.StartTime", vbNull)

cmdcommand.parameters.add(

"@.EndTime", vbNull)

cmdcommand.parameters.add(

"@.HeatTicket", vbNull)

cmdcommand.parameters.add(

"@.Description", vbNull)

cmdcommand.parameters.add(

"@.TakenAs", vbNull)

cmdcommand.parameters.add(

"@.Dinner", vbNull)

cmdcommand.parameters.add(

"@.Hours", vbNull)

cmdcommand.parameters.add(

"@.Rate", vbNull)

cmdcommand.parameters.add(

"@.PayPeriod", vbNull)

cmdcommand.parameters.add(

"@.LastSave", vbNull)

cmdcommand.parameters.add(

"@.Submitted", vbNull)

cmdcommand.parameters.add(

"@.Approved", vbNull)

cmdcommand.parameters.add(

"@.PagerDays", vbNull)

cmdcommand.parameters.add(

"@.ReportEnd", vbNull)Try'open connection here

conn.Open()

'Execute stored proc

cmdcommand.ExecuteNonQuery()

Catch exAs Exception

errstr =

""'An exception occured during processing.'Print message to log file.

errstr =

"Exception: " & ex.MessageFinally'close the connection immediately

conn.Close()

EndTryIf errstr =""Then

Server.Transfer(

"TimeSheetEntry.aspx")EndIf

My SP looks like this

ALTER PROCEDURE

sprocInsertNewReport

@.UserNamenvarchar(256),

@.Week

Int,

@.Date

Datetime,

@.StartTime

Datetime,

@.EndTime

DateTime,

@.HeatTicket

int,

@.Description

nvarchar(max),

@.TakenAs

nchar(10),

@.Dinner

Nchar(10),

@.Hours

Float,

@.Rate

Float,

@.PayPeriod

int,

@.LastSave

Datetime,

@.Submitted

Datetime,

@.Approved

DateTime,

@.PagerDays

int,

@.ReportEnd

DateTimeASINSERT INTO

ReportDetails

(

rpUserName,

rpWeek,

rpDate,

rpStartTime,

rpEndTime,

rpHeatTicket,

rpTicketDescription,

rpTakenAs,

rpDinnerPremium,

rpHours,

rpRate,

rpPayPeriod,

rpLastSaveDate,

rpSubmittedDate,

rpApprovedDate,

rpPagerDays,

rpReportDueDate

)

VALUES

(

@.Username,

@.Week,

@.Date,

@.StartTime,

@.EndTime,

@.HeatTicket,

@.Description,

@.TakenAs,

@.Dinner,

@.Hours,

@.Rate,

@.PayPeriod,

@.LastSave,

@.Submitted,

@.Approved,

@.PagerDays,

@.ReportEnd

)

RETURN

Any Ideas?

thx!

I am not sure what you mean by "go up one each time." Uniqueidentifiers don't work that way. You have three choices:

1 - generate a guid on the front end and pass it to the proc as the id

2 - Generate the guid within the proc

3 - set the default value for the key field to newid()

|||Why not use an identity column?|||

mpswaim:

Why not use an identity column?

Good question because a GUID is 16bytes binary data type to be used with care, while IDENTITY is INT and a property to the column.

|||

I am using VS2005 Server Explorer, is there an option to set and say int to an identity?

I do not see any ways to do so

|||

I changed it to an int and found the as Identity property and it works!

Thanks for the help!

|||

You can do it with SQL Management Studio (I believe that there's a free version available), or Enterprise Manager. Unfortunately I don't see a good way to alter a table to make an existing column an identity column.

|||You just do ANSI SQL ALTER table SET IDENTITY because IDENTITY is defined in ANSI SQL. Hope this helps.

How to insert into a table with a function?

I want to add a record into a table, using a function.

This function needs to give back the number of records of a given table.

Function ImportResults

@.NumberOfRecords int

@.TableName varchar(60)

@.ImportDate datetime

@.Result bit

@.Result = 1

@.ImportDate =getdate()

--Open Table with tablenames

select TableName from Tbl_ImportTables

For each record in Tbl_ImportTables

@.TableName = Tbl_ImportTables.TableName

@.NumberOfRecords = select count(*) from @.TableName

-- add to Import Results

Insert into Tbl_Import_Results

(ImportDate

,TableName

,NumberOfRecords

)

VALUES

(

@.ImportDate

, @.TableName

, @.NumberOfRecords

)

-- Check 0 records (I only want to know if there is a table with 0 records)

IF @.NumberOfRecords=0 then @.Result=0

Next record

RETURN @.Result

End function

Can somebody help me with the right syntax (I am new to functions and sp's)?

Is this a scalar valued function?

Thanks,

Frans

DML operations (except local table variables) are not allowed in SQL Functions, you will have to use a stored procedure for that.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||Yes, this is really a stored procedure.

As you are dynamically selecting the tablename you may want to look at the following topics in Books Online:

1) Cursors - to replace your For Each Loop
2) EXEC or sp_executesql - to dynamically execute a sql string built.

You may want to consider inserting the importdate and tablename first and then just updating the numberofrecords value in your cursor:

ie INSERT INTO Tbl_Import_Results (ImportDate, TableName) SELECT GETDATE(), TableName FROM tbl_ImportTables

HTH!

|||

OK,

I have started to built a basic function in which a sp is called.

ALTER FUNCTION [dbo].[fnImportResult]

RETURNS bit

AS

BEGIN

EXEC dbo.[TestInsert]

@.ControleDatum= '12-10-2007'

,@.BronDatabase = N'aaa'

,@.Tabelnaam = N'bbb'

,@.AantalRecords = 3333

RETURN 0

END

If I run this function I get the following message:

Only functions and extended stored procedures can be executed from within a function.

When only the EXEC part is run then no error message

What am I doing wrong?

Thanks again,

Frans

|||As the message indicates, you cannot call Stored Procedures from a UDF.

|||

OK, so no DML operation or calling a Stored Procedure are allowed in a user defined function. Get it now

Is it correct that a sp cannot return a result?

If so, how can I make it to work? I need to know if one of the records which has been inserted intoTbl_Import_Results has 0 records (for the right ImportDate).

Please advise how you would do this (headlines are fine).

Thanks,

Frans

|||

Stored Procedures can return a results set. They can also return values to indicate success or failure or some other integer value.

Check out OUTPUT parameters and Return Codes in Books Online.

Good Luck!

|||

Slowly I am getting there. The Fetch is working

Now syntax is the problem:

This works:

set @.NumberOfRecords = select count(*) from [1_Exact_DS].[dbo].artbst

But not this (Error converting data type varchar to bigint.)

declare @.TableName as varchar(110)

set @.TableName= '[1_Exact_DS].[dbo].artbst'

set @.NumberOfRecords = (select count(*) from @.TableName)

This is not allowed:

set @.NumberOfRecords = EXEC(select count(*) from @.TableName)

How can I take care that first the @.TableName is resolved and than the select string?

Thanks again,

Frans

|||

You need to dynamically build the sql string to use EXEC

EXEC('SELECT COUNT(*) FROM ' + @.TableName)

|||

Thanks for your quick response

This gives 'incorrect syntax' :

set @.NumberOfRecords = EXEC('select count(*) from ' + @.TableName)

The result of the EXEC should go into the variable @.NumberOfRecords. But how?

Thanks,

Frans

|||

In this case, you won't be able to use EXEC to set the result of a query to a variable. You can however use sp_executesql instead of EXEC:

Code Snippet

declare @.TableName varchar(110)
declare @.NumberOfRecords int
declare @.sql nvarchar(200)

set @.TableName = 'dbo.sysobjects'

set @.sql = 'SELECT @.out = COUNT(*) FROM ' + @.TableName

exec sp_executesql @.SQL, N'@.out INT OUTPUT', @.NumberOfRecords output

select @.NumberOfRecords

HTH!

|||

This works great!!!! Exactly what I wanted

I even start to understand the syntax now.....

Thanks a lot for your help.

Frans