Field Formatting Formulas

I have made a report with multiple groups, when run the report displays just the first group header / footer and detail and will enable you to drill down into the next group which will display group 2 header / footer and detail, from here you can drill down to group 3 header / footer and detail and so on...

is there a way of formatting the borders of a field in the group footer dependant on what is being viewed on screen by the user, eg they will be viewing either group 1 details, group 2 or group 3 etc.

Is it possible to make some kinda of variable that will return true or false dependant on whether someone is viewing a certain group header, then use this variable in the border formatting options for the field i need to format in the group footer?

if someone could give us a clue along with the code that would be great...

thanks

btw... im using crystal v8.5Not sure what you are trying to do
In each group format the field to different borderssql

Fetch Returning Duplicate Last Record

Ok, this thing is returning the last record twice. If I have only one record it returns it twice, multiple records gives me the last one twice. I am sure some dumb pilot error is involved, HELP!

Thanks in advance, Larry

ALTER FUNCTION dbo.TestFoodDisLikes

(

@.ResidentID int

)

RETURNS varchar(250)

AS

BEGIN

DECLARE @.RDLike varchar(50)

DECLARE @.RDLikeList varchar(250)

BEGIN

SELECT @.RDLikeList = ''

DECLARE RDLike_cursor CURSOR

LOCAL SCROLL STATIC

FOR

SELECT FoodItem

FROM tblFoodDislikes

WHERE (ResidentID = @.ResidentID) AND (Breakfast = 'True')

OPEN RDLike_cursor

FETCH NEXT FROM RDLike_cursor

INTO @.RDLike

SELECT @.RDLikeList = @.RDLike

WHILE @.@.FETCH_STATUS = 0

BEGIN

FETCH NEXT FROM RDLike_cursor

INTO @.RDLike

SELECT @.RDLikeList = @.RDLikeList + ', ' + @.RDLike

END

CLOSE RDLike_cursor

DEALLOCATE RDLike_cursor

END

RETURN @.RDLikeList

END

Your selecting into the cursor before any operation in the begin statement. ****

ALTER FUNCTION dbo.TestFoodDisLikes
(
@.ResidentID int
)
RETURNS varchar(250)
AS
BEGIN
DECLARE @.RDLike varchar(50)
DECLARE @.RDLikeList varchar(250)
BEGIN
SELECT @.RDLikeList = ''
DECLARE RDLike_cursor CURSOR
LOCAL SCROLL STATIC
FOR
SELECT FoodItem
FROM tblFoodDislikes
WHERE (ResidentID = @.ResidentID) AND (Breakfast = 'True')
OPEN RDLike_cursor
FETCH NEXT FROM RDLike_cursor
INTO @.RDLike
SELECT @.RDLikeList = @.RDLike
WHILE @.@.FETCH_STATUS = 0
BEGIN --****

SELECT @.RDLikeList = @.RDLikeList + ', ' + @.RDLike--****
FETCH NEXT FROM RDLike_cursor INTO @.RDLike--****

END--****
CLOSE RDLike_cursor
DEALLOCATE RDLike_cursor
END
RETURN @.RDLikeList
END

|||

Steve,

It still duplicates one record, that just moved it to the beginning. If I am expecting to see somethimg like 'Ham, Beans, Biscuit, Apples, Beets' I get 'Ham, Ham, Beans, Biscuit, Apples, Beets'. Looks like the second part of the FETCH is starting all over again

|||Actually I would avoid using a cursor for this, according to the Northwind database it would be something like this:

DECLARE @.Cities VARCHAR(8000)

SET @.Cities = ''

SELECT @.Cities = CASE @.Cities

WHEN '' THEN City

ELSE @.Cities + ', ' + City

END

from CUstomers

Group by City

Select @.Cities

According to your problem it would be

DECLARE @.ResidentID VARCHAR(8000)

DECLARE @.FoodItems VARCHAR(8000)

SET @.FoodItems = ''

SELECT @.FoodItems =CASE @.FoodItems

WHEN '' THEN FoodItem

ELSE @.FoodItems + ', ' + FoodItem

END

from tblFoodDislikes

WHERE (ResidentID = @.ResidentID) AND (Breakfast = 'True')

Group by FoodItem

Select @.FoodItems

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks Jens, That works great and I did use it, seems faster. I am still at a loss as to why the fetch was returning a duplicate record but I like this better.

Larry

Fetch data from Multiple SQL Servers

Hi,
I have 10+ SQL Servers, from my head office server i want
to connect to all branch server and execute a sql
statement and results to be inserted into a table of Head
Office Server.
RegardsYou can use a linked server... ( See linked Servers in Books on line.) Set
up a linked server then use the 4 part name
insert into mytable select * from server1.pubs.dbo.titles
etc
Or you could use replication to move the data on a regular basis...
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Asmath" <anonymous@.discussions.microsoft.com> wrote in message
news:1071d01c3f3c4$e43bcdd0$a001280a@.phx.gbl...
> Hi,
> I have 10+ SQL Servers, from my head office server i want
> to connect to all branch server and execute a sql
> statement and results to be inserted into a table of Head
> Office Server.
>
> Regards|||Hi,
In my head office server, i found the list of all branch
server in Security->Remote Servers.
I found no entries in Security->LinkedServers, when i try
to add linked server, error appears already exists?
And how to use authentication process?
>--Original Message--
>You can use a linked server... ( See linked Servers in
Books on line.) Set
>up a linked server then use the 4 part name
>insert into mytable select * from server1.pubs.dbo.titles
>etc
>Or you could use replication to move the data on a
regular basis...
>--
>Wayne Snyder, MCDBA, SQL Server MVP
>Computer Education Services Corporation (CESC),
Charlotte, NC
>www.computeredservices.com
>(Please respond only to the newsgroups.)
>I support the Professional Association of SQL Server
(PASS) and it's
>community of SQL Server professionals.
>www.sqlpass.org
>
>"Asmath" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1071d01c3f3c4$e43bcdd0$a001280a@.phx.gbl...
>> Hi,
>> I have 10+ SQL Servers, from my head office server i
want
>> to connect to all branch server and execute a sql
>> statement and results to be inserted into a table of
Head
>> Office Server.
>>
>> Regards
>
>.
>

Fetch data from Multiple SQL Servers

Hi,
I have 10+ SQL Servers, from my head office server i want
to connect to all branch server and execute a sql
statement and results to be inserted into a table of Head
Office Server.
RegardsYou can use a linked server... ( See linked Servers in Books on line.) Set
up a linked server then use the 4 part name
insert into mytable select * from server1.pubs.dbo.titles
etc
Or you could use replication to move the data on a regular basis...
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Asmath" <anonymous@.discussions.microsoft.com> wrote in message
news:1071d01c3f3c4$e43bcdd0$a001280a@.phx
.gbl...
> Hi,
> I have 10+ SQL Servers, from my head office server i want
> to connect to all branch server and execute a sql
> statement and results to be inserted into a table of Head
> Office Server.
>
> Regards|||Hi,
In my head office server, i found the list of all branch
server in Security->Remote Servers.
I found no entries in Security->LinkedServers, when i try
to add linked server, error appears already exists?
And how to use authentication process?

>--Original Message--
>You can use a linked server... ( See linked Servers in
Books on line.) Set
>up a linked server then use the 4 part name
>insert into mytable select * from server1.pubs.dbo.titles
>etc
>Or you could use replication to move the data on a
regular basis...
>--
>Wayne Snyder, MCDBA, SQL Server MVP
>Computer Education Services Corporation (CESC),
Charlotte, NC
>www.computeredservices.com
>(Please respond only to the newsgroups.)
>I support the Professional Association of SQL Server
(PASS) and it's
>community of SQL Server professionals.
>www.sqlpass.org
>
>"Asmath" <anonymous@.discussions.microsoft.com> wrote in
message
> news:1071d01c3f3c4$e43bcdd0$a001280a@.phx
.gbl...
want
Head
>
>.
>

Feedback on use of multiple instances

I am currently looking into the high-availability and
consolidations features in SQL Server 2000. MS implies
that running multiple instances of sql server on the same
server will result in a degregation of performance. I am
looking to install a solution that uses Quad 2.5 GB
processor, 4 GB ram server and possibly a cluster. I was
wonder if performance would be an issue for running 10 -
16 instances of sql on that.
Does anyone have a similar setup? If so, how many
instances do you use?
Is this something is widely used among corporations?
Thanks in advance,
SarahSarah,
It's not that simple. If you are talking about consolidating 10 to 16
individual servers into 1 quad processor server then that may be a bit much.
Not because SQL Server can't handle 16 instances but it may simply be a
matter or resources. Of coarse this all depends on what your doing with the
16 instances. Why that many instances vs just a few? You don't need a
separate instance for each db or application. What kind of apps / db's are
you planning on having in this server. You can't take 10 servers that had
anywhere from 10 - say 20 processors, each with say 1GB of ram and simply
through them all onto 1 quad proc server with 4GB and expect to have similar
performance. Unless of coarse they were all well under utilized to begin
with. The bottom line is that there is nothing inherently wrong with
upsizing and consolidating but you need to make sure you have all your ducks
in a row before deciding what type os server to use and how to allocate the
resources.
--
Andrew J. Kelly
SQL Server MVP
"Sarah" <anonymous@.discussions.microsoft.com> wrote in message
news:0c3401c3a476$d0af6e40$a401280a@.phx.gbl...
> I am currently looking into the high-availability and
> consolidations features in SQL Server 2000. MS implies
> that running multiple instances of sql server on the same
> server will result in a degregation of performance. I am
> looking to install a solution that uses Quad 2.5 GB
> processor, 4 GB ram server and possibly a cluster. I was
> wonder if performance would be an issue for running 10 -
> 16 instances of sql on that.
> Does anyone have a similar setup? If so, how many
> instances do you use?
> Is this something is widely used among corporations?
> Thanks in advance,
> Sarah|||Thank you for your response.
So then, what are the peformance differences if say you
had 10 databases on one instances of sql on a single
machine verses 10 instances of sql w/ one database each.
What I am looking to do is basically keep the
databases/application isolated from one another from a
performance and security standpoint. All of these are for
the most part low usage.
The current directive is to place all or most db's on the
same server & same instance. I'm trying to decide if
going w/ separate instances would be a better option.
Thanks again,
Sarah
>--Original Message--
>Sarah,
>It's not that simple. If you are talking about
consolidating 10 to 16
>individual servers into 1 quad processor server then that
may be a bit much.
>Not because SQL Server can't handle 16 instances but it
may simply be a
>matter or resources. Of coarse this all depends on what
your doing with the
>16 instances. Why that many instances vs just a few?
You don't need a
>separate instance for each db or application. What kind
of apps / db's are
>you planning on having in this server. You can't take 10
servers that had
>anywhere from 10 - say 20 processors, each with say 1GB
of ram and simply
>through them all onto 1 quad proc server with 4GB and
expect to have similar
>performance. Unless of coarse they were all well under
utilized to begin
>with. The bottom line is that there is nothing
inherently wrong with
>upsizing and consolidating but you need to make sure you
have all your ducks
>in a row before deciding what type os server to use and
how to allocate the
>resources.
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"Sarah" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0c3401c3a476$d0af6e40$a401280a@.phx.gbl...
>> I am currently looking into the high-availability and
>> consolidations features in SQL Server 2000. MS implies
>> that running multiple instances of sql server on the
same
>> server will result in a degregation of performance. I
am
>> looking to install a solution that uses Quad 2.5 GB
>> processor, 4 GB ram server and possibly a cluster. I
was
>> wonder if performance would be an issue for running 10 -
>> 16 instances of sql on that.
>> Does anyone have a similar setup? If so, how many
>> instances do you use?
>> Is this something is widely used among corporations?
>> Thanks in advance,
>> Sarah
>
>.
>|||"Sarah" <anonymous@.discussions.microsoft.com> wrote in message
news:01e301c3a488$72b6b570$a101280a@.phx.gbl...
> Thank you for your response.
> So then, what are the peformance differences if say you
> had 10 databases on one instances of sql on a single
> machine verses 10 instances of sql w/ one database each.
> What I am looking to do is basically keep the
> databases/application isolated from one another from a
> performance and security standpoint. All of these are for
> the most part low usage.
Performance will most likely be worse. Each instance has its own overhead
in processor time and memory usage. So if each database is using only 1% of
the available processor time and memory in a single instance, you would
probably see zero change in performance if you ran 10 instances each with
its own database. But if each database were using 10% of the available
resources in one instance, splitting these into 10 instances your available
resources will no longer be the same and each database's chunk of available
memory and/or processor time will be lower, thereby resulting in worse
performance.
Plus there's the management issue. Managing 10 instances is going to be
quite a bit more work than managing a single instance. From a security
standpoint, databases can be isolated very easily by simply managing logins
to the server. What benefit are you hoping to get, security-wise, from
multiple instances?|||if all the db's are single company internal users, i would
try to run as many db apps within a single instance as
practical
i would recommend multi-instance for ISP environments
where each instance belongs to a completely different
company/customer and security/isolation is required.
>--Original Message--
>Sarah,
>It's not that simple. If you are talking about
consolidating 10 to 16
>individual servers into 1 quad processor server then that
may be a bit much.
>Not because SQL Server can't handle 16 instances but it
may simply be a
>matter or resources. Of coarse this all depends on what
your doing with the
>16 instances. Why that many instances vs just a few?
You don't need a
>separate instance for each db or application. What kind
of apps / db's are
>you planning on having in this server. You can't take 10
servers that had
>anywhere from 10 - say 20 processors, each with say 1GB
of ram and simply
>through them all onto 1 quad proc server with 4GB and
expect to have similar
>performance. Unless of coarse they were all well under
utilized to begin
>with. The bottom line is that there is nothing
inherently wrong with
>upsizing and consolidating but you need to make sure you
have all your ducks
>in a row before deciding what type os server to use and
how to allocate the
>resources.
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"Sarah" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0c3401c3a476$d0af6e40$a401280a@.phx.gbl...
>> I am currently looking into the high-availability and
>> consolidations features in SQL Server 2000. MS implies
>> that running multiple instances of sql server on the
same
>> server will result in a degregation of performance. I
am
>> looking to install a solution that uses Quad 2.5 GB
>> processor, 4 GB ram server and possibly a cluster. I
was
>> wonder if performance would be an issue for running 10 -
>> 16 instances of sql on that.
>> Does anyone have a similar setup? If so, how many
>> instances do you use?
>> Is this something is widely used among corporations?
>> Thanks in advance,
>> Sarah
>
>.
>|||Sarah,
I pretty much agree with Adam. With proper security you can still isolate
the db's from each others users without going to the trouble of putting them
in their own instance. Again as Adam points out performance would most
likely be better for those reasons and sql server will not have to
dynamically adjust memory between the 10 instances. From the little info we
know of your situation it does not sound like it is worth the trouble of
having a separate instance for each db.
--
Andrew J. Kelly
SQL Server MVP
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in message
news:u2HcEnIpDHA.2776@.tk2msftngp13.phx.gbl...
> "Sarah" <anonymous@.discussions.microsoft.com> wrote in message
> news:01e301c3a488$72b6b570$a101280a@.phx.gbl...
> > Thank you for your response.
> > So then, what are the peformance differences if say you
> > had 10 databases on one instances of sql on a single
> > machine verses 10 instances of sql w/ one database each.
> > What I am looking to do is basically keep the
> > databases/application isolated from one another from a
> > performance and security standpoint. All of these are for
> > the most part low usage.
> Performance will most likely be worse. Each instance has its own
overhead
> in processor time and memory usage. So if each database is using only 1%
of
> the available processor time and memory in a single instance, you would
> probably see zero change in performance if you ran 10 instances each with
> its own database. But if each database were using 10% of the available
> resources in one instance, splitting these into 10 instances your
available
> resources will no longer be the same and each database's chunk of
available
> memory and/or processor time will be lower, thereby resulting in worse
> performance.
> Plus there's the management issue. Managing 10 instances is going to be
> quite a bit more work than managing a single instance. From a security
> standpoint, databases can be isolated very easily by simply managing
logins
> to the server. What benefit are you hoping to get, security-wise, from
> multiple instances?
>|||Here's where I'm coming from...
I am used to envrinoment where every application got it's
own sql db on it's own sql server. Now I am in an
environment where they all have to be placed on the same
server. How can I guarantee up time to my users when
mutliple applications have access to the same sql server.
If I have to stop one, they all get stopped... serivce
packs/ upgrades, errors, runaway transactions... etc.
I was thinking that by using a resource manager and
separate instances, I could limit a lot of the interaction.
But then I was unsure if other people actually did that
and if it would work properly.
As you can tell... I'm not really a dba, just a windows
admin that has to work w/ sql server...
>--Original Message--
>"Sarah" <anonymous@.discussions.microsoft.com> wrote in
message
>news:01e301c3a488$72b6b570$a101280a@.phx.gbl...
>> Thank you for your response.
>> So then, what are the peformance differences if say you
>> had 10 databases on one instances of sql on a single
>> machine verses 10 instances of sql w/ one database each.
>> What I am looking to do is basically keep the
>> databases/application isolated from one another from a
>> performance and security standpoint. All of these are
for
>> the most part low usage.
> Performance will most likely be worse. Each instance
has its own overhead
>in processor time and memory usage. So if each database
is using only 1% of
>the available processor time and memory in a single
instance, you would
>probably see zero change in performance if you ran 10
instances each with
>its own database. But if each database were using 10% of
the available
>resources in one instance, splitting these into 10
instances your available
>resources will no longer be the same and each database's
chunk of available
>memory and/or processor time will be lower, thereby
resulting in worse
>performance.
> Plus there's the management issue. Managing 10
instances is going to be
>quite a bit more work than managing a single instance.
From a security
>standpoint, databases can be isolated very easily by
simply managing logins
>to the server. What benefit are you hoping to get,
security-wise, from
>multiple instances?
>
>.
>|||"Sarah" <anonymous@.discussions.microsoft.com> wrote in message
news:02fc01c3a494$656e5330$a101280a@.phx.gbl...
> Here's where I'm coming from...
> I am used to envrinoment where every application got it's
> own sql db on it's own sql server. Now I am in an
> environment where they all have to be placed on the same
> server. How can I guarantee up time to my users when
> mutliple applications have access to the same sql server.
> If I have to stop one, they all get stopped... serivce
> packs/ upgrades, errors, runaway transactions... etc.
> I was thinking that by using a resource manager and
> separate instances, I could limit a lot of the interaction.
> But then I was unsure if other people actually did that
> and if it would work properly.
> As you can tell... I'm not really a dba, just a windows
> admin that has to work w/ sql server...
You shouldn't have to stop and restart the server very often (almost
never, unless something is very wrong). Just like normal Windows
administration, you'll install service packs and upgrades during planned
downtime or off hours... And now that you're administrating SQL Server I'd
like to welcome you to the world of being a DBA :)sql

Fan Trap or Multiple one-to-many joins

I'm having a problem creating a view on some data that involves two one-to-many joins like this:

tbl1 m--> tbl2 <--n tbl3

and I need to create a view on the data from all three tables without duplicates from tbl1 and tbl3.

The problem is that tbl1 and tbl3 are not related at all, except that they are linked by data in tbl2.

Think of it like this: you have a project, which can have multiple consultants, and multiple stakeholders, and the data must be returned in such a way that each consultant and each stakeholder appears once in the output (the project name must appear multiple times of course). The issue is that the following two datasets are logically distinct but semantically identical:

proj A, consultant A, stakeholder A
proj A, consultant B, stakeholder B
--
proj A, consultant B, stakeholder A
proj A, consultant A, stakeholder B

but what I'm aiming for is:

proj A, consultant A, stakeholder A
proj A, consultant B, stakeholder B

I've heard this described as a fan trap, but usual solutions involve reorganzing the data so that tbl3 joins tbl1 joins tbl2, but in my case there is no link there

Incidently, the intended platform for this is DB2 and/or SQL Server. Any help appreciated, thanks.your examples are not very clear

you start out by diagramming tbl1, tbl2, tbl3, and then immediately switch to projects, consultants, and shareholders, without showing the actual data in these tables, just some apparent cross join query results

you might wish to show a more comprehensive example, because so far, it's hard to understand what you're asking|||I've heard this described as many things, most of which aren't polite to repeat. ;)

You're trying to figure out how to build a join to show the relationship between consultants and shareholders, to produce a one-to-one join between two tables that explicitly have no relationship. If you figure out how to make this happen, please let me know... I'm sure that I'll be fascinated by the explanation!

You've got a clear relationship between project and shareholder, and another relationship between project and consultant. You don't explicitly state that there must be a shareholder or a consultant for any given project, and at some point in the project's life I can guarantee that there will not be one of either. You don't explicitly state that there must be one shareholder for every consultant. If you think about these requirements, unless at least one of these requirements is false, you can't get the output you want... There ain't no way to git there from here.

You need to rethink either the specifications or the requirements. Something has got to give because using the definitions that you've given, the present problem can't be solved.

-PatP|||ah, so that's what that is -- i had never heard that terminology before

this pdf is a pretty good explanation --
http://support.businessobjects.com/documentation/installation_resources/5i/tips_and_tricks/pdf/universe_design/ut001.pdf

i would solve this problem with a UNION query --

proj A, consultant A
proj A, consultant B
proj A, stakeholder A
proj A, stakeholder B

Failure to insert multiple rows into SQL database

I have an app that imports data from a csv file into a dataset. The user views the dataset and then decides to import the data into the database by clicking the code below.

I am getting the error message below when the app gets to the line 'objCommand.ExecuteNonQuery()'

"Message="The variable name '@.PartNumber' has already been declared. Variable names must be unique within a query batch or stored procedure.
Incorrect syntax near '?'."

My questions are:

1) Why is the code failing at this point ? ( i have hard coded the part number and part name to test my code)

2) How do i pass the actual value of PartNumber and PartName from each datarow into the parameter ?

Private Sub btnImport_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnImport.Click

Dim objCommand As SqlCommand = New SqlCommand

Dim datatable As DataTable

DataTable = dsimport.Tables(0)

Dim dataRow As DataRow

'Setup SQLcommand

objCommand.Connection = objConnection

objCommand.CommandText = "INSERT into Part (PartNumber,PartName) VALUES (?,?)"

objCommand.CommandType = CommandType.Text

For Each dataRow In dsimport.Tables(0).Rows

'Parameter for the PartNumber field...

objCommand.Parameters.AddWithValue("PartNumber", "2R8T-14A005-AA")

'Parameter for the PartName field...

objCommand.Parameters.AddWithValue("PartName", "Test3")

Next

'Open the connection...

objConnection.Open()

'Execute the SqlCommand object to update the data...

objCommand.ExecuteNonQuery()

'Close the connection...

objConnection.Close()

objCommand = Nothing

objConnection = Nothing

End Sub

This article talks about where you can use (?) placeholder.

http://authors.aspalliance.com/aspxtreme/adonet/usingstoredprocedureswithcommand.aspx

In your case you can use OleDbCommand to accomplish the same thing. I have an example below in which I read couple of rows from an excel spreadsheet and insert them into SQL Database.

-

Dim cn As New OleDbConnection, cn1 As New OleDbConnection
Dim adapter As New OleDbDataAdapter
Dim dtset As New DataSet
Dim cmd As New OleDbCommand
Dim dr As DataRow

cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data source= C:\Test.xls;" + "Extended Properties=""Excel 8.0;HDR=Yes;"""
cn1.ConnectionString = "Provider=SQLOLEDB;Data Source=.;Integrated Security=SSPI;"

cn.Open()
cn1.Open()

cmd.Connection = cn
cmd.CommandText = "Select * from TestTable"
adapter.SelectCommand = cmd
adapter.Fill(dtset)

cmd.Connection = cn1
cmd.CommandText = "Insert into Parts (PartNumber,PartName) Values(?,?)"
cmd.CommandType = CommandType.Text

cmd.Parameters.Add("PartNumber", OleDbType.VarChar, 20)
cmd.Parameters.Add("PartName", OleDbType.VarChar, 20)
For Each dr In dtset.Tables(0).Rows
cmd.Parameters("PartNumber").Value = dr(0).ToString()
cmd.Parameters("PartName").Value = dr(1).ToString()
cmd.ExecuteNonQuery()
Next

cn.Close()
cn1.Close()

-

Hope this helps

|||

Using SqlCommand you can use this example:

Dim cn As New OleDbConnection
Dim sqlcn As New SqlConnection
Dim adapter As New OleDbDataAdapter
Dim dtset As New DataSet
Dim cmd As New OleDbCommand
Dim sqlcmd As New SqlCommand
Dim dr As DataRow

cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data source= C:\Test.xls;" + "Extended Properties=""Excel 8.0;HDR=Yes;"""
sqlcn.ConnectionString = "Data Source=.;Integrated Security=SSPI;"

cn.Open()
sqlcn.Open()

cmd.Connection = cn
cmd.CommandText = "Select * from TestTable"
adapter.SelectCommand = cmd
adapter.Fill(dtset)

sqlcmd.Connection = sqlcn
sqlcmd.CommandText = "Insert into Parts (PartNumber,PartName) Values(@.a,@.b)"
sqlcmd.CommandType = CommandType.Text

sqlcmd.Parameters.Add("@.a", SqlDbType.VarChar, 20)
sqlcmd.Parameters.Add("@.b", SqlDbType.VarChar, 20)

For Each dr In dtset.Tables(0).Rows
sqlcmd.Parameters("@.a").Value = dr(0).ToString()
sqlcmd.Parameters("@.b").Value = dr(1).ToString()
sqlcmd.ExecuteNonQuery()
Next

cn.Close()
sqlcn.Close()

Hope this helps