Problems inserting into table

I am trying to write an insert statement. I insert into 1 table test with th
e
statements below and what I want is I want to use this table to insert value
s
in
table test1 but I want be able to insert everything from the keyword product
so the new table description is always beginning from Product......
Since the length of month varies before the keyword I cannot use a static
substring or trim functions.
My insert will be something like this in test1.
id desc
-- --
1 Product dispatch to website...(individual number)
2 Product dispatch to website...(individual number)
Can anyone help me .
create table test(
Sno Int,
description varchar(2000),
cyats datetime default getdate() )
create table test1(
id int,
description varchar(2000),
cyats datetime default getdate())
insert into test(sno,description)
values(1,'Integrated Release August 2005. Product despatched to website.
Website address for tracking consignment is http://www.track.com,your number
is :...')
insert into test(sno,description)
values(1,'Integrated Release December 2004. Product despatched to website.
Website address for tracking consignment is http://www.track.com,your number
is ...')
insert into test(sno,description)
values(1,'Integrated Release June 2005. Product despatched to website.
Website address for tracking consignment is http://www.track.com,your number
is ...')First suggestion clean this data before getting into SQL Server using
whatever tool you have (like DTS or whatever.) as it will be far more
natural. However, a possible way is to reformat as a select in whatever
code you are using to build this data:
SELECT 1,substring('Integrated Release August 2005. Product despatched to
website.
Website address for tracking consignment is http://www.track.com,your
number
is :...', charindex('product', 'Integrated Release August 2005. Product
despatched to website.
Website address for tracking consignment is http://www.track.com,your
number
is :...'), 4000)
You could change the string to a variable, but I wouldn't unless you are
hand typing this stuff.
----
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)
"Anup" <Anup@.discussions.microsoft.com> wrote in message
news:8B17F575-0318-4317-A318-2E7F543D1A31@.microsoft.com...
>I am trying to write an insert statement. I insert into 1 table test with
>the
> statements below and what I want is I want to use this table to insert
> values
> in
> table test1 but I want be able to insert everything from the keyword
> product
> so the new table description is always beginning from Product......
> Since the length of month varies before the keyword I cannot use a static
> substring or trim functions.
> My insert will be something like this in test1.
> id desc
> -- --
> 1 Product dispatch to website...(individual number)
> 2 Product dispatch to website...(individual number)
> Can anyone help me .
> create table test(
> Sno Int,
> description varchar(2000),
> cyats datetime default getdate() )
> create table test1(
> id int,
> description varchar(2000),
> cyats datetime default getdate())
> insert into test(sno,description)
> values(1,'Integrated Release August 2005. Product despatched to website.
> Website address for tracking consignment is http://www.track.com,your
> number
> is :...')
> insert into test(sno,description)
> values(1,'Integrated Release December 2004. Product despatched to website.
> Website address for tracking consignment is http://www.track.com,your
> number
> is ...')
> insert into test(sno,description)
> values(1,'Integrated Release June 2005. Product despatched to website.
> Website address for tracking consignment is http://www.track.com,your
> number
> is ...')

Problems Inserting DateTimeStamp into database

Here's the error I get trying to run the code posted below..."Prepared statement '(@.datestamp datetime,@.PrevValue real,@.NewValue real,@.IPaddress r' expects parameter @.datestamp, which was not supplied."

I've tried it without the quotes around the DateTime.Now and also adding the # around them. Without the quotes, I do get a different error because it doesn't like the spaces in the DateTime text. Also, the datestamp data in the database is formatted as a datetime (MS SQL Server 2005).

Here's my code:

PrivateSub UpdateRefresh()

UpdateConnection.Open()

UpdateDataAdapter.InsertCommand.CommandText = "INSERT INTO dbo.tbDeploy(datestamp," & _

"PrevValue, NewValue, IPaddress, HighCapability, LowCapability, Share) VALUES" & _

"('" & DateTime.Now & "', '" & lblCurrent.Text & "', '" & lblCurrent.Text & _

"', '" & lblIP.Text & "', '" & lblHigh.Text & "', '" & lblLow.Text & "', '" & lblShare.Text & "')"

UpdateDataAdapter.InsertCommand.ExecuteNonQuery()

UpdateConnection.Close()

EndSub

Use parameterized queries. Your problem will be solved. You can also prevent SQL Injection attacks.

Problems Inserting Data- Am I doing something wrong?

I am having problem inserting data obtained from a adhoc sql query. Am I doing something wrong here (the select statement in below mentioned query works fine)

Insert

into customer(CustomerID, LastName, FirstName, BillingAddress, City,State, Country, Zipcode, PhoneNumber, EmailAddress)

select

CustomerID,(left(ContactName,charindex(' ', ContactName)-1))as LastName,

(right(

ContactName,charindex(' ',reverse(ContactName))-1))as FirstName,

Address

, City,Region,PostalCode,Country,Phonefrom Northwind.dbo.Customers

I am just doing it this way since Customer's name (both last name & first name ) are stored in a single column in Northwind database. I want to break it into two columns...The reason I am trying to get this to work is...it would be less of a work for me in inserting test data for my application

Any help would be of great

It looks like you list 10 rows to insert to, and then only select 9 rows.  Make sure the formatting is correct.

INSERT INTO table (Column1,Column2,Column3)VALUES (Value1,Value2,Value3)

This is what your query is trying to do now:

FROM column ->INTO columnCustomerID -> CustomerIDLastName -> LastNameFirstName -> FirstNameAddress -> BillingAddressCity -> CityRegion -> StatePostalCode -> CountryCountry -> ZipcodePhone -> PhoneNumber***nothing*** -> EmailAddress

Try something like this:

INSERT INTO customer
(CustomerID, LastName, FirstName, BillingAddress, City, State,
Country, Zipcode, PhoneNumber, EmailAddress)
SELECT
CustomerID,
(left(ContactName, charindex(' ', ContactName)-1))as LastName,
(right(ContactName, charindex(' ', reverse(ContactName))-1))as FirstName,
Address,
City,
Region, ***this goes into'State' ***
Country,
PostalCode,
Phone,
**EmailAddress** [insert correct column name]
FROM Northwind.dbo.Customers

|||

Hi Stew,

I corrected it in the actual query (when I executed) and it gives me this error message....any pointers??

Msg 8152, Level 16, State 13, Line 1

String or binary data would be truncated.

Thank you

|||Look at the size constraints of the columns you are inserting too and see if they make sense against what is being inserted. You may have a string field going into a char field, or something of the sort.|||

Stew312:

It looks like you list 10 rows to insert to, and then only select 9 rows.  Make sure the formatting is correct.

INSERT INTO table (Column1,Column2,Column3)VALUES (Value1,Value2,Value3)

This is what your query is trying to do now:

FROM column ->INTO columnCustomerID -> CustomerIDLastName -> LastNameFirstName -> FirstNameAddress -> BillingAddressCity -> CityRegion -> StatePostalCode -> CountryCountry -> ZipcodePhone -> PhoneNumber***nothing*** -> EmailAddress

Try something like this:

INSERT INTO customer
(CustomerID, LastName, FirstName, BillingAddress, City, State,
Country, Zipcode, PhoneNumber, EmailAddress)
SELECT
CustomerID,
(left(ContactName, charindex(' ', ContactName)-1))as LastName,
(right(ContactName, charindex(' ', reverse(ContactName))-1))as FirstName,
Address,
City,
Region, ***this goes into'State' ***
Country,
PostalCode,
Phone,
**EmailAddress** [insert correct column name]
FROM Northwind.dbo.Customers

Looks like the problem is this:

This is what my query is trying to do now:

FROM column ->INTO columnCustomerID -> CustomerIDLastName -> LastNameFirstName -> FirstNameAddress -> BillingAddressCity -> CityRegion -> StatePostalCode -> CountryCountry -> ZipcodePhone -> PhoneNumber

There is only one FROM COLUMN (ie., Contactname) but is repeated twice...I mean to say in

the Northwind customer database both the last name and first name are put in a single column

and I am trying to separate it into two colums (Lastname , Firstname).....Is it a wrong way of doing things.....I had been breaking my on this for couple of hours

your help is greatly appreciated

|||Too embrassing and a costly mistake (had to waste most of my time).........the order of columns I was trying to insert was wrong....though embrassing, I am sharing because some people (including me) tend to forget minor details while focusing on other major problems.....so moral of story....Pay attention to detail and don't forget basics.

Problems converting varchar to smallmoney

Hi

My ticket engine stores values in varchar. The sql db-field that
corresponds was created as smallmoney.

The below statement works for conversion of "leavedays" if the given
value is entered without any decimal places (E.G. 4)

As soon as a user enters a value that includes decimal places (E.G.
4.5) the conversion will not work. In this case the value 4.5 is
rounded to 5.

What do i have to do to convert the value as it is entered by the user?

Thanks in advance

t.

Statement:

INSERT INTO leavereq (mitarbeiter, startdate, enddate, leavedays,
remainingdays, approvedby, approvedon) SELECT {0} , convert(datetime,
{1}) , convert(datetime, {2}), convert(numeric, {3}), convert(numeric,
{4}),{5}, getdate()

DDL for concerned database:

CREATE TABLE [dbo].[leavereq] (
[mitarbeiter] char(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[startdate] datetime NULL,
[enddate] datetime NULL,
[leavedays] smallmoney NULL,
[remainingdays] smallmoney NULL,
[approvedon] datetime NULL,
[approvedby] char(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
ON [PRIMARY]
GO

I am little confused. In your INSERT INTO statement, you are converting {3} and {4} to numeric, while the datatype for these two columns are smallmoney. Why don't you use smallmoney directly in the INSERT statement?

The reason it rounds up for you is that the default scale (max number of decimal digits) of numeric data type is 0. It would work fine if you use numeric(20, 2) for example. See "decimal and numeric (Transact-SQL)" in SQL Server Book Online for details.

|||

thx

seems to work now using smallmoney directly or numeric (20,2)

Problems connection the SQL Server database

I build a small application using VS 2005. The application just builds a report using basic Select statement from SQL Server db. Here is my problem:

When I tried to run the application using VS 2005 by pressing F5 . The applicaiton launches using VS webserver and everything test fine. But when I put the site in IIS created website . I get an error "Login failed for user '<serverName>\<userName>

In my connection string I have Integrated Security = SSPI

Please suggest.

Thanks

Thismight be the problem (if the IIS created website you mentioned is on another machine).

On your machine, the "user" that logs into SQL Server is MYMACHINE\ASPNET.

When you deploy to another machine, the "user" that is trying to log into SQL Server is OTHERMACHINE\ASPNET. Maybe the other machine isn't aware of that "user"

problems building sql statement

Hello,

I'm having problems building an sql stament that joins a few tables. I can seem to get my head around the structure!

I have to try and link up four different tables to try and get my result.

Here are the 4 table structures...

Web_Users
------
User_ID
Name

Tags_Table
------
Tags_ID
User_ID
Group_ID
Title

Created_Groups
--------
Group_ID
Group_Name

Tags_To_Groups
--------
Group_Link_ID
Group_ID
Tag_ID

Basically, this database, has four tables; One table (Web_Users) that contains a users name, and assigns a unique ID (User_ID), another table that stores a users tags they have created, and also links it to a group_ID. The created_groups table, contains group names and assigns a unique id also. And the last table, Tags_To_Groups, links tags to groups.

So this is what I'm trying to do...

I'm trying to get the Group_name field from Created_Groups table, of a tag , that belongs to a certain user. If sounds easy when I say it like that, but I've been inner joining tables all night and failing every time.

Does this make sense? Can anyone help?

Thank you

Hmm... maybe I didn't understand, but here's what I came up with:

select cg.Group_Name
from Created_Groups cg (nolock)
join Tags_Table tt (nolock) on tt.Group_ID = cg.Group_ID
and tt.User_ID = @.User_ID

|||

select cg.Group_Name
from Created_Groups cg
inner join Tags_Table tt on cg.Group_ID = tt.Group_ID
and tt.User_ID = ?

or:

select cg.Group_Name
from Created_Groups cg
inner join Tags_Table tt on cg.Group_ID = tt.Group_ID
inner join Web_Users wu on tt.User_ID = wu.User_ID
and wu.User_Name = '?'
or

select cg.GroupName
from Web_Users wu
inner join Tags_Table tt on wu.User_ID = tt.User_ID
and wu.User_Name = '?'
inner join Created_Groups cg on tt.Group_ID = cg.Group_ID

Depending upon indexes, some ways will be faster than others.

|||

amazing. thank you both!

problem with UPDATE statement

i am using visual web developer 2005 and SQL Express 2005 and VB as the code behind

i am using the following statement to update details in the database.it doesn't work

Protected Sub processbutton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles processbutton.Click
Dim update As New SqlDataSource()
update.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString").ToString()
update.UpdateCommandType = SqlDataSourceCommandType.Text
update.UpdateCommand = "UPDATE orderdetail SET fromdesignstatus = 11 , fromdesignlink = " + TextBox1.Text.ToString() +
"WHERE order_id =" + ordersid.ToString()
update.Update()
End Sub

the value of TextBox1 is obtained like this

Protected Sub uploadbutton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles uploadbutton.Click
If FileUpload1.HasFile Then
FileUpload1.SaveAs("G:\project\My Project\OTS\ots\design\uploads\" + FileUpload1.FileName)
HyperLink1.Text = FileUpload1.FileName
HyperLink1.NavigateUrl = "uploads\" + FileUpload1.FileName
TextBox1.Text = FileUpload1.FileName
End If
End Sub

my ultimate aim is to upload the file and store the path in the database

what is wrong in my code ?

please help me

try this remember to put single quotes around text values and to have space between value and where clause (add extra space at the begin of every string you add when you build your query). I inserted quotes in red around your fromdesignlink field and I put space after it before where clause:

Protected Sub processbutton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles processbutton.Click
Dim update As New SqlDataSource()
update.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString").ToString()
update.UpdateCommandType = SqlDataSourceCommandType.Text
update.UpdateCommand = "UPDATE orderdetail SET fromdesignstatus = 11 , fromdesignlink ='" + TextBox1.Text.ToString() +
"' WHERE order_id =" + ordersid.ToString()
update.Update()
End Sub

Problem with update statement

No matter what I do I cant get this update statement to work..
UPDATE MedOrder
SET AdditionalRefills = 0
WHERE AdditionalRefills IS NULL
I get this error message:
Server: Msg 512, Level 16, State 1, Procedure MEDORDER_MODIFIED, Line 6
Subquery returned more than 1 value. This is not permitted when the subquery
follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
Ive even tried it this way:
UPDATE MedOrder
SET AdditionalRefills = 0
WHERE [med order id ] in
(SELECT [med order id]
FROM medorder
WHERE AdditionalRefills IS NULL)
And this way:
declare @.temp table
(
medid int
)
insert into @.temp select [med order id] from medorder where
Additionalrefills is null
UPDATE MedOrder
SET AdditionalRefills = 0
WHERE [med order id] in
(select medid from @.temp)
All give me the same error message..how can I accomplish this?
thanksThere was a bug in my trigger..that was the problem..|||What you did first looks fine. But it does not have six lines of code
or a subquery. Is there some other stuff in the procedure you are not
showing us?|||The problem is not actually with this update... it looks like you have a
trigger that is "fired" then the row is updated...or when the
AdditionalRefills column is updated.
Check the query that is being executed on the rigger...
"MEDORDER_MODIFIED"
Server: Msg 512, Level 16, State 1, Procedure MEDORDER_MODIFIED, Line 6
Subquery returned more than 1 value. This is not permitted when the subquery
Message posted via http://www.webservertalk.com|||Code looks good from here. Is there something else that could be affecting
it? BTW, you might consider setting a default of 0 on that column.
"Jim" <Jim@.discussions.microsoft.com> wrote in message
news:B95757DE-593A-40A4-BD57-B2499048903A@.microsoft.com...
> No matter what I do I cant get this update statement to work..
> UPDATE MedOrder
> SET AdditionalRefills = 0
> WHERE AdditionalRefills IS NULL
> I get this error message:
> Server: Msg 512, Level 16, State 1, Procedure MEDORDER_MODIFIED, Line 6
> Subquery returned more than 1 value. This is not permitted when the
> subquery
> follows =, !=, <, <= , >, >= or when the subquery is used as an
> expression.
> The statement has been terminated.
> Ive even tried it this way:
> UPDATE MedOrder
> SET AdditionalRefills = 0
> WHERE [med order id ] in
> (SELECT [med order id]
> FROM medorder
> WHERE AdditionalRefills IS NULL)
>
> And this way:
> declare @.temp table
> (
> medid int
> )
> insert into @.temp select [med order id] from medorder where
> Additionalrefills is null
>
> UPDATE MedOrder
> SET AdditionalRefills = 0
> WHERE [med order id] in
> (select medid from @.temp)
> All give me the same error message..how can I accomplish this?
> thanks
>

Problem with Update command

I use a simple update statement to update 1 smalldatetime format field, but I always get a Timeout error.

UPDATE TableName SET Field1 = '8/2/2007' WHERE (Field2 = 'SomeCondition')

Why do I always get this timeout error?

What client are you using to invoke the update statement?

How many rows are in the table?

How many rows will it affect?

Do you have an index on Field2?

|||Through a connection in Visual Basic 2005, I passing the update statement to a SQL server 2000.

The table has barely 10,000 records, and it would affect approx. 30 lines per day.

|||

Here are the key items that you need to watch out for

Try comparing the execution time when running thru Query Analyzer and from your application. If the application and the database resides on different server then how good is your network? See if you have any triggers associated with this table when updating the information. Do you have indexes created for the column your referring to.|||Ok, thank you guys for the prompt replies!

I think the issue is in the code in VB, because with Query Analyzer there's no issue.