Re: Update a datatable to database - Database Discussion Boards

#realJSOP14-Feb-08 4:53

14-Feb-08 4:53

I want to use xp_cmdshell to export the contents of a table to an xml file. Here's the sql I'm trying to use:

use MyDatabase

declare @dtStart datetime;
declare @dtEnd datetime;
declare @publisher varchar(50);
declare @outpath varchar(255);
declare @sql varchar(512);
declare @params varchar(512);

set @publisher = 'SomePublisher';
set @dtStart = '2008-01-01 00:00:00.000';
set @dtEnd   = '2008-01-01 11:59:59.999';

set @sql     = 'select * from MyDatabase.dbo.downloads where endtimestamp between ''' +
               Convert(varchar(50), @dtStart, 121) + ''' and ''' + 
               Convert(varchar(50), @dtEnd, 121) + ''' and publishername=''' + @publisher + 
               ''' order by endtimestamp, serialnumber';
set @outpath = '\\program files\appfolder\' + @publisher + '_January01.xml';

set @params  = 'bcp "' + @sql + '" queryout "' + @outpath + '" -T -c -r -t -x'

execute master..xp_cmdshell @params;

When I try to run this code, I get the following errors

<br />
SQLState = 08001, NativeError = 2 <br />
Error = [Microsoft][SQL Native Client]Named Pipes Provider: Could not open a connection to SQL Server [2]. <br />
<br />
SQLState = HYT00, NativeError = 0<br />
Error = [Microsoft][SQL Native Client]Login timeout expired <br />
<br />
SQLState = 08001, NativeError = 2<br />
Error = [Microsoft][SQL Native Client]An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the  default settings SQL Server does not allow remote connections.<br />
NULL

If I've logged into the sql server with Management Studio, and am running this from a query window, why do I get this error?

[EDIT]

I had to add "-S sqlservername" to the commandline

"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"...the staggering layers of obscenity in your statement make it a work of art on so many levels." - Jason Jystad, 10/26/2001

modified on Thursday, February 14, 2008 12:12 PM

One to Many Atomic Update

Brady Kelly14-Feb-08 1:32

Brady Kelly

14-Feb-08 1:32

I have a table that links 0 - many reports to a form, called ReportLinks. I'm wondering how best to do an atomic update after a web user changes the reports linked to the form, i.e., deselects and selects reports as linked to the form. Please can somebody suggest an elegant way of doing this update.

Update a datatable to database

Bluebamboo14-Feb-08 1:02

Bluebamboo

14-Feb-08 1:02

Hi guys,

I got a datatable from a excel file, and try to update this table onto sql server, in other words, the users can change the table in sql server based on excel file.

I use SqlDataAdapter to update the table, but the problem is no change happen on my database.

<br />
'...<br />
<br />
Dim cmdSql As SqlCommand = New SqlCommand<br />
cmdSql.Connection = connSql<br />
cmdSql.CommandText = "UPDATE pfSchool SET notes = @notes WHERE ID = @id"<br />
<br />
cmdSql.Parameters.Add(New SqlParameter("@notes", SqlDbType.NText))<br />
cmdSql.Parameters("@notes").SourceColumn = "notes"<br />
<br />
cmdSql.Parameters.Add(New SqlParameter("@id", SqlDbType.Int))<br />
cmdSql.Parameters("@id").SourceColumn = "ID"<br />
<br />
' modify the data for testing<br />
dtData.Rows(0)("notes") = "test2"<br />
<br />
Dim daSql As SqlDataAdapter = New SqlDataAdapter<br />
daSql.UpdateCommand = cmdSql<br />
daSql.UpdateCommand.Connection.Open()<br />
<br />
daSql.Update(dtData)<br />
<br />
'...<br />

Above is a piece of codes which i made for testing.

Any suggestion would be appreciated!

Ming

Re: Update a datatable to database

Joe14-Feb-08 1:27

Joe

14-Feb-08 1:27

Bluebamboo wrote:
cmdSql.CommandText = "UPDATE pfSchool SET notes = @notes WHERE ID = @id"

I believe that the problem lies in here. You'll have to pass values with the variable enclosed between Quotes and Ampersand '" & notes & "'

Having said that,you might want to restructure your code as it is vulnerable to Sql Injections[^].

Give a man a fish, he'll eat for a day. Teach a man how to fish, he'll eat for lifetime.
Pradeep Joe

Re: Update a datatable to database

Bluebamboo14-Feb-08 4:32

Bluebamboo

14-Feb-08 4:32

Thanks for your reply

I don't think that causes the problem, you can check the solution provided by Micsoft support

http://support.microsoft.com/kb/308055[^]

and the solution declare a SqlCOmmand with such a query

DAUpdateCmd = New SqlCommand("Update CustTest set CustName = @pCustName where CustId = @pCustId", da.SelectCommand.Connection)<br />

which is actually perform same function as what I did

Re: Update a datatable to database

Joe14-Feb-08 5:30

Joe

14-Feb-08 5:30

Bluebamboo wrote:
http://support.microsoft.com/kb/308055[^]

Apologize for my ignorance. I didn't know that before. I tried out that sample that you've guided me with, it works like charm.

Can you try providing a mapping table name when you fill and update the adapter (in case you are using a dataset) ?

da = New SqlDataAdapter("select * from CustTest order by CustId", cn)<br />
<br />
da.Fill(CustomersDataSet, "Customers")   <br />
da.Update(CustomersDataSet, "Customers")

If you already have that in place, kindly update me on how you got it working once you achieve it.

Good Luck friend Smile | :)

Give a man a fish, he'll eat for a day. Teach a man how to fish, he'll eat for lifetime.
Pradeep Joe

Re: Update a datatable to database

Bluebamboo14-Feb-08 8:41

Bluebamboo

14-Feb-08 8:41

Hi Joe,

Thanks for you suggestion,

Problem has been *solved*, I haven't found where exactly cause the problem, because it worked once suddenly, and now it always works. Amazing!

I think it is not a problem with the code I pasted, probably it was something wrong with other potential reasons, which I don't know exactly, but one of them what I guess could be this[^]

Thanks.

Ming

Re: Update a datatable to database

Joe14-Feb-08 18:48

Joe

14-Feb-08 18:48

Glad that it works now. Thanks for sharing that piece of information Smile | :)

Give a man a fish, he'll eat for a day. Teach a man how to fish, he'll eat for lifetime.
Pradeep Joe

Passing Parameters to Strored Procedure

dnsl13-Feb-08 23:36

dnsl

13-Feb-08 23:36

Parameters are passed to Stored Procedure from VB.NET Application like

Dim param As SqlParameter
param = New SqlParameter("@name",DbType.String)
param.Direction = ParameterDirection.Input
param.Value = "ABC"
comand.Parameters.Add(param)

But is there any way to specify parameters without specifying the
parameter name and type like

Dim param As SqlParameter
param = New SqlParameter
param.Direction = ParameterDirection.Input
param.Value = "ABC"
comand.Parameters.Add(param)
But when i try like this i am getting an error saying that
"Parameter1 is not a parameter in the procedure"...
Can anybody tell me how to solve this .
Thanx in advance

Re: Passing Parameters to Strored Procedure [modified]

Vimalsoft(Pty) Ltd14-Feb-08 20:13

Vimalsoft(Pty) Ltd

14-Feb-08 20:13

hi dnsl

Number one, The name of your Parameter you are declaring in SQL, should be the same name as your Parameter you Add in your Vb code.

|dnsl Wrote:

 But is there any way to specify parameters without specifying the
parameter name and type like..

No you cannot , that code you give, will give you an Exception

Hope it helps

Vuyiswa Maseko,

Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding

VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za

modified on Friday, February 15, 2008 3:39 AM

Re: Passing Parameters to Strored Procedure

Ashfield15-Feb-08 9:48

Ashfield

15-Feb-08 9:48

What you are attempting to do is pass values by position. This is generally considered bad practice as a change to the stored proc (ie adding a new parameter but not to the end of the list) could result in values being passed to the wrong column.

You can always do:

command.Parameters.Add(new SQLParameter("@name",value))

This uses the overload for new SQLParameter(name as String, Value as Object)

Better still, write a set of overloaded functions such as:

Public Overloads Sub AddParameter(ByVal parameterName As String, _<br />
                                      ByVal parameterValue As Boolean) <br />
        AddParameter(parameterName, parameterValue, System.Data.DbType.Boolean, System.Data.ParameterDirection.Input)<br />
    End Sub<br />
    Public Overloads Sub AddParameter(ByVal parameterName As String, _<br />
                                      ByVal parameterValue As Int32) <br />
        AddParameter(parameterName, parameterValue, System.Data.DbType.Int32, System.Data.ParameterDirection.Input)<br />
    End Sub<br />
<br />
 Public Overloads Sub AddParameter(ByVal parameterName As String, _<br />
                                      ByVal parameterValue As Object, _<br />
                                      ByVal dbType As System.Data.DbType, _<br />
                                      ByVal direction As System.Data.ParameterDirection)<br />
        Try<br />
            ' Add new parameter to command object - if already exists then simply update the value<br />
            If m_cmd.Parameters.Contains(parameterName) Then<br />
                m_cmd.Parameters(parameterName).Value = parameterValue<br />
            Else<br />
                Dim prm As SqlParameter = New SqlParameter<br />
<br />
                With prm<br />
                    .ParameterName = parameterName<br />
                    .Direction = direction<br />
                    .DbType = dbType<br />
                    .Value = parameterValue<br />
                End With<br />
                m_cmd.Parameters.Add(prm)<br />
            End If<br />
<br />
        Catch ex As Exception<br />
            Throw New ApplicationException(String.Format("Unable to add parameter {0} to command", parameterName), ex)<br />
        End Try<br />
    End Sub

Bob
Ashfield Consultants Ltd

error in local works fine in server

new2pgrmg13-Feb-08 23:21

new2pgrmg

13-Feb-08 23:21

When i run this query in my local machine i get an n error --Msg 170, Level 15, State 1, Line 20
Line 20: Incorrect syntax near '('.

USE CustList_PGE_0131
GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO
CREATE TABLE CustList_PGE013108(
[ClientCustID] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CustomerName] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Address] [varchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[City] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[State] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Zip] [char](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MailAddress] [varchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MailCity] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MailState] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MailZip] [char](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Type] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ClientCoCustListID] [int] IDENTITY(1,1) NOT NULL,
[Ucode] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Flag] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Program_ID] [tinyint] NULL CONSTRAINT [DF_CustList_PGE051007_Program_ID] DEFAULT ((1)),
CONSTRAINT PK_CustList_PGE013108 PRIMARY KEY CLUSTERED
(
[ClientCoCustListID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
)
ON [PRIMARY]

GO
SET ANSI_PADDING OFF

But if irun the same query in my server it createsd the table without any problem.

So is there some thing that i am overseeing in my local.

Thanks

Re: error in local works fine in server

Joe14-Feb-08 0:03

Joe

14-Feb-08 0:03

I believe it could be because of compatability issues. Your local box might be running Sql Server 2000 whereas your server might be running Sql Server 2005

Take a look at the Breaking Changes to database engine in Sql Server 2005[^]

On your local box, have the script modified in this fashion

USE CustList_PGE_0131
GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO
CREATE TABLE CustList_PGE013108(
[ClientCustID] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CustomerName] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Address] [varchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[City] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[State] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Zip] [char](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MailAddress] [varchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MailCity] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MailState] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MailZip] [char](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Type] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ClientCoCustListID] [int] IDENTITY(1,1) NOT NULL,
[Ucode] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Flag] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Program_ID] [tinyint] NULL CONSTRAINT [DF_CustList_PGE051007_Program_ID] DEFAULT ((1)),
CONSTRAINT PK_CustList_PGE013108 PRIMARY KEY CLUSTERED 
(
[ClientCoCustListID] ASC
)
) 
ON [PRIMARY]

GO

Create Unique Index [IndexName] on [dbo].[CustList_PGE013108][(ClientCustID)]
With IGNORE_DUP_KEY on [Primary]

Go
SET ANSI_PADDING OFF

Ref: http://www.codeprof.com/dev-archive/75/19-85-759122.shtm[^]

Give a man a fish, he'll eat for a day. Teach a man how to fish, he'll eat for lifetime.
Pradeep Joe

Re: error in local works fine in server

new2pgrmg14-Feb-08 1:13

new2pgrmg

14-Feb-08 1:13

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author: <author,,jiju>
-- Create date: <create date,14.2.2008,="">
-- Description: <description,stored procedure="" to="" create="" tables,="">
-- =============================================
alter PROCEDURE [dbo].[CreateTable]
-- Add the parameters for the stored procedure here
@databaseName nvarchar(100),
@databaseTable nvarchar(100),
@primaryKey nvarchar(100)

AS

declare @qry nvarchar(1500)

BEGIN

SELECT @qry =

'CREATE TABLE '+@databaseName +'.dbo. '+ @databaseTable +'(
[ClientCustID] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CustomerName] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Address] [varchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[City] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[State] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Zip] [char](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MailAddress] [varchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MailCity] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MailState] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MailZip] [char](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Type] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ClientCoCustListID] [int] IDENTITY(1,1) NOT NULL,
[Ucode] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Flag] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Program_ID] [tinyint] NULL CONSTRAINT [DF_CustList_PGE051007_Program_ID] DEFAULT ((1)),
CONSTRAINT '+ @primaryKey +' PRIMARY KEY CLUSTERED
(
[ClientCoCustListID] ASC
)
) ON [PRIMARY]

'

print @qry

--To execute the query string
exec SP_Executesql @qry

END
GO

--To execute the stored procedure and create the tables

exec createTable 'CustList_PGE_0131','CustList_PGE013108','PK_CustList_PGE013108'

Saving Image

mwanyu13-Feb-08 21:11

mwanyu

13-Feb-08 21:11

Hello,

I am trying to retrieve an image from SQL but the line didPreviouslyConnect = true; is giving an error.HELP!!ME!!
I have the code writen

private void RetrieveImage()
{
try
{

SqlConnection PicCon = new SqlConnection();
SqlDataAdapter da = new SqlDataAdapter("SELECT photo FROM Upload where Account_No = '" + Txt_balance.Text + "," + Txt_Bankcharge.Text + "," + Txt_Refundamount.Text + "," + Txt_TellerID.Text + "," + Txt_Tellername.Text + "'", PicCon);
SqlCommandBuilder cbd = new SqlCommandBuilder(da);
dsPictures = new DataSet();
da.Fill(dsPictures);
didPreviouslyConnect = true;
byte[] arrPicture = (byte[])dsPictures.Tables[0].Rows[0]["photo"];
MemoryStream ms = new MemoryStream(arrPicture);
pictureBox2_Accountholder.Image = Image.FromStream(ms);
pictureBox2_Accountholder.SizeMode = PictureBoxSizeMode.StretchImage;
pictureBox2_Accountholder.BorderStyle = BorderStyle.Fixed3D;
ms.Close();

}
catch (Exception e1)
{
MessageBox.Show(e1.Message.ToString());
}

KIDO KIDI MINI REZA
HERE FIX DI MATURITY
SAMBA RAMBA DIBLO
STYLE ANTIDIZASTA
BLISH MANTIRIOLOJ

Re: Saving Image

Giorgi Dalakishvili13-Feb-08 22:43

Giorgi Dalakishvili

13-Feb-08 22:43

What is the error you are getting?

#region signature
my articles
#endregion

replace '1/1/1900' to NULL in MS SQL

John Sundar13-Feb-08 20:39

John Sundar

13-Feb-08 20:39

Hi,

Can anyone give me a idea to convert or replace the existing date content '1/1/1900' to NULL while insert and update a table.

Thanks,
John

Re: replace '1/1/1900' to NULL in MS SQL

Colin Angus Mackay13-Feb-08 21:11

Colin Angus Mackay

13-Feb-08 21:11

UPDATE MyTable<br />
SET TheDateColumn = NULL<br />
WHERE TheDateColumn = '1900-01-01'

Upcoming FREE developer events:
* Developer Day Scotland

Recent blog posts:
* Different ways to add point data in SQL Server 2008
* Spatial References in SQL Server 2008

My website |

Including MSSQL in a C# application for a client.

AlainMesias13-Feb-08 15:51

AlainMesias

13-Feb-08 15:51

Hi all,

I am developing a calendar application in C# that will require to store certain data (like date events, calendar style and some other preferences)in a database.

My preference will be MS SQL 2005 (though could be MySQL, or similar), however I wish to know if I was to use MS SQL 2005, could I include some dlls or other resource in the install of the application, purely for the application to run, without having to install another copy of MS SQL 2005 in the client PC? Like a cut down version of MS SQL 2005 as client site install? Otherwise I may have to go for MySQL or MS Access 2003, to avoid extra cost of the application.

Any ideas an suggestions are welcome.

Thanks in advance.

Smile | :)

Alain

copy datarow

Smithers-Jones13-Feb-08 10:01

Smithers-Jones

13-Feb-08 10:01

i need to make a copy of a datatable's row (access database).
since the table has quite a lot of columns i don't want to do it column by column:
INSERT INTO myTable(Col1, Col2, Col3) SELECT Col1, Col2, Col3 FROM myTable WHERE ID = 2
but by inserting the whole row like this:
INSERT INTO myTable SELECT * FROM myTable WHERE ID = 2
i think i found an example like that somewhere on the net. but it doesn't work for me. any solution?

"I love deadlines. I like the whooshing sound they make as they fly by." (DNA)

Re: copy datarow

pmarfleet13-Feb-08 11:08

pmarfleet

13-Feb-08 11:08

Smithers-Jones wrote:
think i found an example like that somewhere on the net. but it doesn't work for me

Why doesn't it work? What error do you get?

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

Re: copy datarow

Smithers-Jones13-Feb-08 11:45

Smithers-Jones

13-Feb-08 11:45

it says
"an unattended exception of the type 'system.data.oledb.oledbexception' occured in system.data.dll"
(hope i translated it correct to english).

i created a module to do all my database-connections, in this module i have among others a sub to which i pass sql-commands as string and that then executes the sql-command.
the error occurs when executing the oledbcommand (myOleDbCommand.ExecuteNonQuerry).
i experienced this error usually only, when there is some error in the sql-command.
that's why i think the sql-command is incorrect or not working with access.

"I love deadlines. I like the whooshing sound they make as they fly by." (DNA)

Stored procedure returning dataset

#realJSOP13-Feb-08 8:26

#realJSOP

13-Feb-08 8:26

Just out of curiosity, how many records can a query return to a calling .net app before weird stuff starts happening? I'm writing code that could potentially return millions of records in a single query.

Re: Stored procedure returning dataset

pmarfleet13-Feb-08 11:07

pmarfleet

13-Feb-08 11:07

John Simmons / outlaw programmer wrote:
I'm writing code that could potentially return millions of records in a single query.

What are you going to do with these records? If you are presenting them to the user, you should consider using some sort of paging mechanism to limit the number of rows returned from the database.

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

Deleting top 3 from a select [modified]

Dio2213-Feb-08 7:42

Dio22

13-Feb-08 7:42

I'm trying to order a table by a datetime column (ASC) , then delete the top 3 rows. Help out the n00b. Here's what doesn't work..

DELETE TOP(3)
FROM Table
ORDER BY DateTime ASC

DELETE TOP(3)
FROM Table
WHERE (SELECT * FROM Table ORDER BY DateTime ASC)

i've tried hundreds of other combinations.. Thanks guys/gals.

modified on Wednesday, February 13, 2008 1:58 PM

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.