Re: SqlParameter was expected, but it's been given! - Database Discussion Boards

srilu nagalla19-Oct-06 20:49

19-Oct-06 20:49

Hi,I have a small problem,first we used MySql as a database,The database contain realworld data,suddenly we have need to shift database to Sql Server
Is their any way to avoid refill data manually in SqlServer.

Plz,Help me in Solving the problem

Advanced Thanks,
Srilakshmi

Re: How to shift My Sql data to Sql Server

Edbert P19-Oct-06 21:11

Edbert P

19-Oct-06 21:11

You can use Enterprise Manager (if you have SQL 2000), or SQL Server Management Studio (if you have SQL 2005) to import your data (tables, columns, records) from MySQL.
The wizard to do import/export data should be simple enough to follow.

"A democracy is nothing more than mob rule, where fifty-one percent of the people may take away the rights of the other forty-nine." - Thomas Jefferson

"Democracy is two wolves and a lamb voting on what to have for lunch. Liberty is a well-armed lamb contesting the vote." - Benjamin Franklin

Edbert
Sydney, Australia

where can find A CADOConnection CLASS package

swgzj19-Oct-06 17:56

swgzj

19-Oct-06 17:56

where can find A ADOConnection CLASS package?
i want a CADOConnection ,CADORecordSet ,CADOCommand CALSS ,THANKS For your help first!
my email :swgzj@sina.com

Re: where can find A CADOConnection CLASS package

S Douglas23-Oct-06 0:58

S Douglas

23-Oct-06 0:58

swgzj wrote:
where can find A ADOConnection CLASS

A set of ADO classes - version 2.20
[^]

I'd love to help, but unfortunatley I have prior commitments monitoring the length of my grass. :Andrew Bleakley:

SqlParameter was expected, but it's been given!

Ed.Poore19-Oct-06 13:28

Ed.Poore

19-Oct-06 13:28

I've got a fairly simple bit of code which will call a stored proc with the following signature:

dbo.UpdateAssembler (@Id int, @Name char(20), @IsActive bit, @Stamp timestamp)

I've created and initialized an SqlCommand object with the appropriate parameters setup, the relevant one in this case "Id" has it's IsNullable property set to true. When I try and execute the command I get the following exception:

Procedure or Function 'UpdateAssembler' expects parameter '@Id', which was not supplied.

Hmm

, I've set Id in the previous lines of code (which could be an int or null, the stored proc does something different if Id is null).

Any ideas on what might be causing this exception, note that I have verified the following:

A parameter by the name of 'Id' does exist in the command's parameters (C# side of things)
Stored proc does want an Id parameter
The stored proc works fine if I invoke it through the UI in either VS2005 or the Management Studio.

Sig cops got me...

Re: SqlParameter was expected, but it's been given!

Colin Angus Mackay19-Oct-06 13:43

Colin Angus Mackay

19-Oct-06 13:43

Ed.Poore wrote:
A parameter by the name of 'Id' does exist in the command's parameters (C# side of things)

Okay... But it is looking for a parameter called @Id. Do you supply that parameter?

You might want to post your code - It sounds like a really simple thing like a typo you just need a second pair of eyes to see.

Upcoming Scottish Developers events:
* UK Security Evangelists On Tour (2nd November, Edinburgh)
* Developer Day Scotland: are you interested in speaking or attending?
My: Website | Blog

Re: SqlParameter was expected, but it's been given!

Ed.Poore19-Oct-06 14:00

Ed.Poore

19-Oct-06 14:00

Hmm, I seem to remember the last time I did something like this it didn't want the @ in the SqlParameter's name, but I shall check it, then I'll post the code if not successful since it's taking an age for VS to load.

Sig cops got me...

Re: SqlParameter was expected, but it's been given!

Ed.Poore19-Oct-06 14:11

Ed.Poore

19-Oct-06 14:11

Ok here's the code (putting the @ in made no difference)

Stored Procedure:

ALTER PROCEDURE dbo.UpdateAssembler (@Id int, @Name char(20), @IsActive bit, @Stamp timestamp) AS
BEGIN
    IF EXISTS(SELECT * FROM Assembler WHERE ID = @Id)
        /* Check the TimeStamp */
        IF EXISTS(SELECT * FROM Assembler WHERE (ID = @Id AND Stamp = @Stamp))
            UPDATE Assembler
                SET Name = @Name,
                    IsActive = @IsActive
                WHERE ID = @Id
        ELSE
            RAISERROR('Different TimeStamps',10,1)
    ELSE
        INSERT INTO Assembler
            (Name, IsActive)
            VALUES (@Name, @IsActive)
END

Building the SqlCommand (in a semi-factory like method thingy-ma-bob):

// Build the Command
SqlCommand command = new SqlCommand("UpdateAssembler");
command.CommandType = CommandType.StoredProcedure;

command.Parameters.Add("Id", SqlDbType.Int);
command.Parameters.Add("Stamp", SqlDbType.Timestamp);
command.Parameters["Id"].IsNullable = true;
command.Parameters["Stamp"].IsNullable = true;

Executing the command:

updateCommand.Parameters["Id"].Value = item.Id;			// an int? object
updateCommand.Parameters["Stamp"].Value = item.TimeStamp;	// byte[]
updateCommand.ExecuteNonQuery();

Note that certain irrelevant bits have been cut out.

Sig cops got me...

Re: SqlParameter was expected, but it's been given!

Colin Angus Mackay19-Oct-06 14:28

Colin Angus Mackay

19-Oct-06 14:28

Could you do a check on item.Id and if it is null replace the value sent as the parameter to DBNull.Value?

Upcoming Scottish Developers events:
* UK Security Evangelists On Tour (2nd November, Edinburgh)
* Developer Day Scotland: are you interested in speaking or attending?
My: Website | Blog

Re: SqlParameter was expected, but it's been given!

Ed.Poore19-Oct-06 22:45

Ed.Poore

19-Oct-06 22:45

I think I tried that, but it was before I noticed a couple of other mistakes so when I get the chance in a couple of hours I shall do that.

Sig cops got me...

Re: SqlParameter was expected, but it's been given!

Ed.Poore22-Oct-06 21:39

Ed.Poore

22-Oct-06 21:39

Yes that's cured it, thanks.

Sig cops got me...

Re: SqlParameter was expected, but it's been given!

Mairaaj Khan20-Oct-06 0:07

Mairaaj Khan

20-Oct-06 0:07

Ed.Poore wrote:
command.Parameters.Add("Id", SqlDbType.Int);
command.Parameters.Add("Stamp", SqlDbType.Timestamp);
command.Parameters["Id"].IsNullable = true;
command.Parameters["Stamp"].IsNullable = true;

If i'm not wrong the SqlParameters are prefixed with @ symbol, and the above lines should be like this:
command.Parameters.Add("@Id", SqlDbType.Int);
command.Parameters.Add("@Stamp", SqlDbType.Timestamp);
command.Parameters["@Id"].IsNullable = true;
command.Parameters["@Stamp"].IsNullable = true;

_____________________________
Success is not something to wait for, its something to work for.

Re: SqlParameter was expected, but it's been given!

Ed.Poore21-Oct-06 7:55

Ed.Poore

21-Oct-06 7:55

I've tried that and it doesn't seem to make any difference Sigh | :sigh:

Sig cops got me...

How to fill null data in control froms in VB.Net

rehan_cop19-Oct-06 7:09

rehan_cop

19-Oct-06 7:09

Please tell me how to fill null data in textbox or other form controls in VB.Net from Sql Server 2000. I found error in dataset on runtime the application.

Thanks

Re: How to fill null data in control froms in VB.Net

Mairaaj Khan19-Oct-06 22:34

Mairaaj Khan

19-Oct-06 22:34

rehan_cop wrote:
how to fill null data in textbox or other form controls in VB.Net

try following these steps.
1. check the retrieved data for null value, if it is Nothing then
2. set the textbox.Text to empty string or set the controls with suitable values.

rehan_cop wrote:
I found error in dataset on runtime the application

what sort of error it is? its not clear.

AND
read this[^]

_____________________________
Success is not something to wait for, its something to work for.

selecting data for crystal reports

haseeb_saeed18-Oct-06 21:14

haseeb_saeed

18-Oct-06 21:14

hello...
i am using crystal reports 9 to make a prescription of a patient from the patienttable and disease table.the disease table takes patient id as a foriegn key. now i have 2 problems:
1. i am unable to get patients name of the patient table using the foriegn key
2. how would i show multiple diseases with out getting the disease label printed time and again on the prescription page.
3. how would i use a view... if that is a better option.

thanx...

haseeb

catching sql exception type

DownBySpj18-Oct-06 21:01

DownBySpj

18-Oct-06 21:01

I have a problem. Whenever we pass an insert statement against database table the exception can be cathced by using sqlexception parameter. I have done with exception where there is a problem with the primary key because i know the error code it genereates when there is a primary key error. But how can i do the same type of checking when there is an error related to foreign key. Wht is the error code for foreign key related errors. Something like below

try<br />
        {<br />
           insert into database;<br />
           if (success)<br />
              then do nothing;<br />
           else<br />
              check about the cause of exception<br />
        }<br />
    catch<br />
      {<br />
          if (primary key exception)<br />
              dosomething;<br />
          else if(foreign key exception)<br />
              dosomething;<br />
          else<br />
               dosomething;<br />
       }

any help will be wellcomed.

Any suggestion is well received. thanx in advance

Re: catching sql exception type

Colin Angus Mackay19-Oct-06 5:18

Colin Angus Mackay

19-Oct-06 5:18

If I have a problem like this I create a simple test application and experiment. I would create an INSERT that will cause a foreign key violation and see what it returns.

How did you find the value of the Primary Key error in the first place?

Upcoming Scottish Developers events:
* UK Security Evangelists On Tour (2nd November, Edinburgh)
* Developer Day Scotland: are you interested in speaking or attending?
My: Website | Blog

Re: SQL error :Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.??? [help]

Mike Dimmick19-Oct-06 6:51

Mike Dimmick

19-Oct-06 6:51

It should be telling you that bizdate isn't contained in an aggregate or in a GROUP BY clause. As soon as you roll up results in an aggregate function, everything in the SELECT list must be aggregated or one of the fields used to group.

You'll have to select the value separately, unless you want to show figures grouped for each distinct value in that column, in which case you should use GROUP BY. If you want the latest date/time that this occurred, you could use MAX(bizdate) instead.

Stability. What an interesting concept. -- Chris Maunder

how to display daily sales between the date? [urgent]

campbells18-Oct-06 18:58

campbells

18-Oct-06 18:58

I have create a SQL which calculate the daily sale but it will only show 1 day when i run it

<br />
<br />
select t1.supermarket,t1.softline, t1.hardline, (t1.supermarket+t1.softline+ t1.hardline)as TOTAL <br />
from (select (select sum(totalsales+returnamt)as TotalSoft from tot_item <br />
where bizdate='2006-10-1'and (deptcode= 11 or deptcode=12 or <br />
deptcode=13 or deptcode=14 or deptcode= 15 or deptcode= 16 or deptcode= 17  ))as softline,<br />
<br />
(select sum (totalsales+returnamt)as TotalHard from tot_item <br />
where bizdate='2006-10-1'and (deptcode= 21 or deptcode=22 or deptcode=23 or deptcode=<br />
24 or deptcode= 25 or deptcode=26 or deptcode= 27 or deptcode= 28 or deptcode= 29 ) )as hardline,<br />
<br />
(select sum(totalsales+returnamt)as TotalSupermarket from tot_item <br />
where bizdate='2006-10-1' and (deptcode= 30 or deptcode=40 or deptcode=50 or deptcode=<br />
60 or deptcode= 70 or deptcode=80 or deptcode= 90)) as supermarket <br />
 )as t1 <br />
<br />

but i want to show like the daily result between day 1- day 15 so i create this SQL but the result not wat i expected, the RESULT should the TOTAL sum of this 15 days.... i want to show 1 by 1 for the daily sale... how?

select t1.supermarket,t1.softline, t1.hardline, (t1.supermarket+t1.softline+ t1.hardline)as TOTAL <br />
from (select (select sum(totalsales+returnamt)as sototal from tot_item <br />
where bizdate<='2006-10-15' and bizdate>='2006-10-01'and (deptcode= 11 or deptcode=12 or <br />
deptcode=13 or deptcode=14 or deptcode= 15 or deptcode= 16 or deptcode= 17 ))as softline,<br />
<br />
(select sum(totalsales+returnamt)as htotal from tot_item <br />
where bizdate<='2006-10-15' and bizdate>='2006-10-01'and (deptcode= 21 or deptcode=22 or deptcode=23 or deptcode=<br />
24 or deptcode= 25 or deptcode=26 or deptcode= 27 or deptcode= 28 or deptcode= 29 ) )as hardline,<br />
<br />
(select sum(totalsales+returnamt)as sutotal from tot_item <br />
where bizdate<='2006-10-15' and bizdate>='2006-10-01'and (deptcode= 30 or deptcode=40 or deptcode=50 or <br />
deptcode=60 or deptcode= 70 or deptcode=80 or deptcode= 90 )) as supermarket <br />
 )as t1 <br />

Re: how to display daily sales between the date? [urgent]

Edbert P19-Oct-06 15:25

Edbert P

19-Oct-06 15:25

Hmm...it's a bit hard to understand your query as it's too complicated.
You might want to simplify it a bit.
First, use IN instead of deptcode = 14 or deptcode = 15 and so on.

So let's format it first into:

SELECT t1.supermarket,t1.softline, t1.hardline, (t1.supermarket+t1.softline+ t1.hardline) AS TOTAL
FROM 
(SELECT (SELECT SUM(totalsales+returnamt) AS TotalSoft 
        FROM tot_item
        WHERE bizdate='2006-10-1' AND deptcode IN (11, 12, 13, 14, 15, 16, 17)) AS softline,

        (SELECT SUM(totalsales+returnamt) AS TotalHard 
        FROM tot_item
        WHEREbizdate='2006-10-1' AND deptcode IN (21, 22, 23, 24, 25, 26, 27, 28, 29)) AS hardline,

        (SELECT sum(totalsales+returnamt) AS TotalSupermarket 
        FROM tot_item
        WHERE bizdate='2006-10-1' AND deptcode IN (30, 40, 50, 60, 70, 80, 90)) AS supermarket
) AS t1

Now that it's more simplified, I found that you can actually separate softline, hardline, and supermarket into 3 separate queries to save the confusion, and we can use GROUP BY to show the sum for each bizdate.

Query1:

SELECT bizdate, SUM(totalsales+returnamt) AS TotalSoft
FROM tot_item
WHERE deptcode IN (11, 12, 13, 14, 15, 16, 17)
GROUP BY bizdate

Query2:

SELECT bizdate, SUM(totalsales+returnamt) AS TotalHard
FROM tot_item
WHERE deptcode IN (21, 22, 23, 24, 25, 26, 27, 28, 29) AS hardline
GROUP BY bizdate

Query3:

SELECT bizdate, sum(totalsales+returnamt) AS TotalSupermarket
FROM tot_item
WHERE deptcode IN (30, 40, 50, 60, 70, 80, 90) AS supermarket
GROUP BY bizdate

Now in your final query you can link all the total using the following:

SELECT Query1.bizdate, TotalSoft, TotalHard, TotalSupermarket
FROM Query1
LEFT JOIN Query2 ON Query1.bizdate = Query2.bizdate
LEFT JOIN Query3 ON Query1.bizdate = Query3.bizdate

This will return your daily result of the sale.
From there you can specify the 15 days total sum.

If you want to put them all as one big query, here's the query:

SELECT Query1.bizdate, TotalSoft, TotalHard, TotalSupermarket
FROM (SELECT bizdate, SUM(totalsales+returnamt) AS TotalSoft 
        FROM tot_item
        WHERE deptcode IN (11, 12, 13, 14, 15, 16, 17)
        GROUP BY bizdate) AS Query1
LEFT JOIN (SELECT bizdate, SUM(totalsales+returnamt) AS TotalHard 
        FROM tot_item
        WHERE deptcode IN (21, 22, 23, 24, 25, 26, 27, 28, 29) AS hardline
        GROUP BY bizdate) AS Query2 
ON Query1.bizdate = Query2.bizdate
LEFT JOIN (SELECT bizdate, sum(totalsales+returnamt) AS TotalSupermarket 
        FROM tot_item
        WHERE deptcode IN (30, 40, 50, 60, 70, 80, 90) AS supermarket
        GROUP BY bizdate) AS Query3 
ON Query1.bizdate = Query3.bizdate

I hope the explanation above helps in your problem.
Note: I'm blind coding, so please check the syntax for me, ok Wink | ;)

Cheers,

Edbert

Re: how to display daily sales between the date? [urgent]

campbells19-Oct-06 15:49

campbells

19-Oct-06 15:49

i got this error:

Server: Msg 156, Level 15, State 1, Line 8<br />
Incorrect syntax near the keyword 'AS'.<br />
Server: Msg 156, Level 15, State 1, Line 12<br />
Incorrect syntax near the keyword 'AS'.<br />

actaully where do i put the date that i want them to show on??
like
where bizdate<='2006-10-15' and bizdate>='2006-10-01which it will show between '2006-10-01' and'2006-10-15'

Re: how to display daily sales between the date? [urgent]

Edbert P19-Oct-06 16:02

Edbert P

19-Oct-06 16:02

Which query did you run? Does each query run?
You can put the WHERE condition at the end of the query, before the GROUP BY if there is any.

Re: how to display daily sales between the date? [urgent]

Edbert P19-Oct-06 16:04

Edbert P

19-Oct-06 16:04

Oh. Scratch that. I forgot to delete the AS hardline and AS supermarket.
The code below should (hopefully) work Laugh | :laugh:

(Please delete the AS hardline and AS supermarket in Query2 and Query3 respectively)

SELECT Query1.bizdate, TotalSoft, TotalHard, TotalSupermarket
FROM (SELECT bizdate, SUM(totalsales+returnamt) AS TotalSoft 
        FROM tot_item
        WHERE deptcode IN (11, 12, 13, 14, 15, 16, 17)
        GROUP BY bizdate) AS Query1
LEFT JOIN (SELECT bizdate, SUM(totalsales+returnamt) AS TotalHard 
        FROM tot_item
        WHERE deptcode IN (21, 22, 23, 24, 25, 26, 27, 28, 29)
        GROUP BY bizdate) AS Query2 
ON Query1.bizdate = Query2.bizdate
LEFT JOIN (SELECT bizdate, sum(totalsales+returnamt) AS TotalSupermarket 
        FROM tot_item
        WHERE deptcode IN (30, 40, 50, 60, 70, 80, 90)
        GROUP BY bizdate) AS Query3 
ON Query1.bizdate = Query3.bizdate

Re: how to display daily sales between the date? [urgent]

campbells19-Oct-06 16:37

campbells

19-Oct-06 16:37

yes is working!! Thank you so much ^.^v

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