Database Discussion Boards

Kschuler19-Feb-08 10:52

19-Feb-08 10:52

Not sure why that doesn't work, but you could always try doing this:

WHERE (CDate([d.downloadutctime])> CDate(#2/2/2008#) And CDate([d.downloadutctime])<CDate(#2/15/2008#)) OR
CDate([d.downloadutctime])= CDate(#2/2/2008#) OR CDate([d.downloadutctime])=CDate(#2/15/2008#)

Problem in Stored Procedure

Syed Ali Raza18-Feb-08 19:11

Syed Ali Raza

18-Feb-08 19:11

Hi
I m using this procedure and want to insert data in multiple table.
First it insert data in ST_STUDENT which auto generates the 'Identity' value of 'Student_Code' which i want to use in other tables for 'Student_Code' field.
I m using this line for getting the last generated 'Student_Code' but it gives me this error
'Expects Parameter '@STUDENT_CODE" WHICH WAS NOT SUPPLIED"
what is wrong in my procedure.
Kindly Tell me

alter Procedure SP_INSERT_STUDENT_COMPLETEINFO_BYAGENT
@STUDENT_CODE varchar(4),
@FIRST_NAME VARCHAR(50),
@LAST_NAME VARCHAR(50),
@EMAIL_ADDRESS VARCHAR(50),
@HOME_PHONE VARCHAR(50),
@HOMEEXT VARCHAR(10),
@OFFICE_PHONE VARCHAR(50),
@OFFICEEXT VARCHAR(50),
@MOBILE VARCHAR(50),
@HIGHEST_EDUCATION_LEVEL VARCHAR(50),
@DOB DATETime,
@COUNTRY_OF_RESIDENCE VARCHAR(50),
@COUNTRY_OF_NATIONALITY VARCHAR(50),
@US_SECURITY_NUMBER VARCHAR(50),
@GENDER VARCHAR(20),
@MARITAL_STATUS VARCHAR(20),
@ETHNIC_GROUP VARCHAR(20),
AS

INSERT INTO ST_STUDENT(FIRST_NAME,LAST_NAME,EMAIL_ADDRESS,HOME_PHONE,HOMEEXT,OFFICE_PHONE,OFFICEEXT,MOBILE)
VALUES(@FIRST_NAME,@LAST_NAME,@EMAIL_ADDRESS,@HOME_PHONE,@HOMEEXT,@OFFICE_PHONE,@OFFICEEXT,@MOBILE)

SET @STUDENT_CODE =(SELECT @@IDENTITY)

Insert into ST_STUDENTBIODATA(HIGHEST_EDUCATION_LEVEL,DOB,COUNTRY_OF_RESIDENCE,COUNTRY_OF_NATIONALITY,
US_SECURITY_NUMBER,GENDER,MARITAL_STATUS,ETHNIC_GROUP,STUDENT_CODE)
VALUES(@HIGHEST_EDUCATION_LEVEL,@DOB,@COUNTRY_OF_RESIDENCE,@COUNTRY_OF_NATIONALITY,@US_SECURITY_NUMBER,@GENDER,@MARITAL_STATUS,
@ETHNIC_GROUP,@STUDENT_CODE)

GO

Regards

Re: Problem in Stored Procedure

Paddy Boyd19-Feb-08 0:24

Paddy Boyd

19-Feb-08 0:24

You have a comma just before your AS.

call DB2 stored procedure?

jchigg200018-Feb-08 4:35

jchigg2000

18-Feb-08 4:35

has anyone here used DB2Connect to call a DB2 stored procedure. I'm having trouble registering the input/output parameters.

here is my code

Dim conn As New DB2Connection("DATABASE=DB2DSST;UID=c132;PWD=*******;")
conn.Open()
Dim trans As DB2Transaction = conn.BeginTransaction()
Dim cmd As DB2Command = conn.CreateCommand()
Dim procName As String = "MG1006SP"
Dim procCall As String = "CALL MG1006SP (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
cmd.Transaction = trans
cmd.CommandType = CommandType.Text
cmd.CommandText = procCall

' Register input-output and output parameters for the DB2Command
cmd.Parameters.Add("DB2_ID", "DSST")
cmd.Parameters.Add("GRP_ID", "19 ")
cmd.Parameters.Add("LN1_ADDR", DB2Type.Char, 55)
cmd.Parameters.Add("LN2_ADDR", DB2Type.Char, 55)
cmd.Parameters.Add("CITY_NM", DB2Type.Char, 30)
cmd.Parameters.Add("ST_CD", DB2Type.Char, 2)
cmd.Parameters.Add("ZIP5_CD", DB2Type.Char, 5)
cmd.Parameters.Add("ZIP4_CD", DB2Type.Char, 4)
cmd.Parameters.Add("INFO_CHG_DT", DB2Type.Date, 4)
cmd.Parameters.Add("AIBRETRN", DB2Type.Integer, 4)
cmd.Parameters.Add("AIBREASN", DB2Type.Integer, 4)
cmd.Parameters.Add("SQLCODE", DB2Type.Integer, 4)
cmd.Parameters.Add("SQLSTATE", DB2Type.Char, 5)
cmd.Parameters.Add("SQLMESSAGE", DB2Type.VarChar, 1320)
cmd.Parameters.Add("APPL_RTN_CD", DB2Type.Char, 5)
cmd.Parameters.Add("APPL_MESSAGE", DB2Type.VarChar, 500)

' Call the stored procedure
Console.WriteLine(" Call stored procedure named " & procName)
cmd.ExecuteNonQuery()

Re: call DB2 stored procedure?

Mike Dimmick18-Feb-08 7:08

Mike Dimmick

18-Feb-08 7:08

We use DataDirect Connect for .NET[^] to connect to DB2.

We simply set the CommandText property to the name of the stored procedure and set CommandType to CommandType.StoredProcedure.

For an example of using IBM's data provider - which appears to use the same scheme - see the SpClient.vb sample[^].

DoEvents: Generating unexpected recursion since 1991

how to get the primarykey value of a table after copying a row

Exelioindia18-Feb-08 2:38

Exelioindia

18-Feb-08 2:38

HI,

Can any one suggest me how to do the following.

I am having a two table, table1,table 2. table 2 fkey depends on table 1 primary key(auto generated). My doubt is if i copy a row with in table1, a new primary key is generated,how to retrive this new auto generated primary key, because using this new primary key i have to copy the rows with in table 2. for eg:

table 1:

id name
1 aaa
2 aaa

table 2:
id fid age
1 1 23
2 2 23

please suggest me

thanks in advance

Know is Drop, Unknown is Ocean

Re: how to get the primarykey value of a table after copying a row

SimulationofSai18-Feb-08 3:40

SimulationofSai

18-Feb-08 3:40

You haven't mentioned which Database/version you use. If you're using SQL Server 2005, you can get the last inserted identity value using Scope_Identity() function.

SG
Cause is effect concealed. Effect is cause revealed.

Re: how to get the primarykey value of a table after copying a row

Exelioindia18-Feb-08 9:33

Exelioindia

18-Feb-08 9:33

hi,

Thanks for your reply
we are using SQL 2000. is there any other alternate?

please suggest me

Know is Drop, Unknown is Ocean

Re: how to get the primarykey value of a table after copying a row

pmarfleet18-Feb-08 11:16

pmarfleet

18-Feb-08 11:16

SCOPE_IDENTITY() works in SQL 2000.

You should really consider reading documentation and trying things for yourself before asking questions on forums.

Paul Marfleet

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

C# MySQL ----parameter '?9' not found in the collection...huh?

js8008518-Feb-08 2:17

js80085

18-Feb-08 2:17

I am having some problems calling stored functions from my c# code, sometimes it works and sometimes it doesn't. When it doesnt I get an error message

"parameter '?9' not found in the collection"

I dont have a parameter with the name '9' in the function I am trying to call! it does it when I call some functions but not others. Another function I call it says it can't find parameter '?50' again I don't have such a parameter

Anyone got any ideas? I have a feeling I'm missing something obvious

///

/// This method executes a stored function
///

/// <param name="functionName" />Name of the function
/// <param name="parameters" />A list of parameters to be passed into the function
/// <returns>The result of the function
public object executeFunction(string functionName, List<parameter> parameters)
{
//this is the variable that will contain the return value from the function
DbParameter returnParam = factory.CreateParameter();

try
{
conn.Open(); //open the connection

//start a transaction
DbTransaction trans;
trans = conn.BeginTransaction();

DbCommand command = factory.CreateCommand(); //create a command
command.Transaction = trans; //set the transaction for the command
command.CommandText = functionName; //set the command text to the name if the function
command.Connection = conn; //set the commands connection
command.CommandType = CommandType.StoredProcedure; //set the type to stored procedure

//add each of the input parameters to the command
foreach (Parameter a in parameters)
{
DbParameter param = factory.CreateParameter();
param.ParameterName = a.name;
param.Value = a.value;
command.Parameters.Add(param);
}

//initialise the return parameter and add it to the command
returnParam.ParameterName = "?rv";
command.Parameters.Add(returnParam);
returnParam.Direction = ParameterDirection.ReturnValue;

command.ExecuteNonQuery(); //execute the function

trans.Commit(); //finish the transaction
conn.Close(); //close the connection

}
catch (Exception ex)
{
throw new Exception("the following exception was thrown: \n" + ex.Message + "\n\nyou probably have an error in your parameters");
}
finally
{
conn.Close();
}

return returnParam.Value; //return the result

}

Re: C# MySQL ----parameter '?9' not found in the collection...huh?

sugo11-Apr-08 4:30

sugo

11-Apr-08 4:30

Hi,

I have the same kind of error with MySQL and the ADO .Net connector. When i call the stored procedure from my C# program, I get an exception telling me that a parameter '@,' is missing in my parameter collection and of course i don't have such a parameter in the definition of the stored procedure.

Does anyone have an idea about this ?

Thanks,
Stephane

Password! [modified]

kibromg18-Feb-08 1:29

kibromg

18-Feb-08 1:29

Hi all,

can you please let me know how to recover a database script?

Thank you

modified on Monday, February 18, 2008 9:28 AM

Re: Password!

Michael Potter18-Feb-08 6:10

Michael Potter

18-Feb-08 6:10

Google: SQL decryptor

There are many products that handle this task.

Dynamic Parameters for report designed in SQL Server Reporting Services 2005

Senthil_M17-Feb-08 21:40

Senthil_M

17-Feb-08 21:40

Hi all,

I have designed an .rdl report in my server(SSRS).
I have a client program which will render this report and export.

How do I pass connection parameters to the report dynamically?
I tried using report parameters. I can pass only server name and database name.
I want to pass User id and password too.

Please help

Thanks in advance

Senthil

Re: Dynamic Parameters for report designed in SQL Server Reporting Services 2005

r aa j18-Feb-08 2:18

r aa j

18-Feb-08 2:18

for user name,passward,servername, u can set using connection string....
and just using dataset....try this

Rajendran.AL

security in sql server 2000 database [modified]

Sonia Gupta17-Feb-08 19:15

Sonia Gupta

17-Feb-08 19:15

is there any way to set the password while somebody try to access the sql server 2000 database .Like in the Mirosoft Access database , i can set the security , to prevent the malacious access.

Yesterday is a canceled check. Tomorrow is a promissory note. Today is the ready cash. USE IT.

modified on Monday, February 18, 2008 1:28 AM

Re: security in sql server 2000 database

N a v a n e e t h17-Feb-08 19:42

N a v a n e e t h

17-Feb-08 19:42

Yes. SQL Server has a good security system. You can create users and set passwords and access rights for them.

All C# applications should call Application.Quit(); in the beginning to avoid any .NET problems.- Unclyclopedia

My Website | Ask smart questions

Re: security in sql server 2000 database

Sonia Gupta17-Feb-08 19:53

Sonia Gupta

17-Feb-08 19:53

actually it is an intranet application i am working now-a-days.Problem is sometimes an unklnown person comes to server and open the enterprise manager , this is what i want to prevent , i want to prevet executing the sql server enterprise manager.

Yesterday is a canceled check. Tomorrow is a promissory note. Today is the ready cash. USE IT.

Re: security in sql server 2000 database

N a v a n e e t h17-Feb-08 21:10

N a v a n e e t h

17-Feb-08 21:10

Sonia Gupta wrote:
i want to prevet executing the sql server enterprise manager.

You can't prevent enterprise manager from opening. You can create new users and give permission to access the database. Deny permission to access the database for all other users. This will prevent other person accessing database.

All C# applications should call Application.Quit(); in the beginning to avoid any .NET problems.- Unclyclopedia

My Website | Ask smart questions

Primark key

laleh.rajabi17-Feb-08 16:23

laleh.rajabi

17-Feb-08 16:23

hello.
i need to access to table's primary keys in SQL in code. how di i can do it?

Re: Primark key

Christian Graus17-Feb-08 18:44

Christian Graus

17-Feb-08 18:44

What do you mean by 'access to' ? You can read them with a select statement.

Christian Graus - Microsoft MVP - C++

"also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )

Re: Primark key

SimulationofSai18-Feb-08 0:12

SimulationofSai

18-Feb-08 0:12

If you're asking for the list of primary keys in a table, use the sql below

SELECT 
cu.COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE cu 
WHERE EXISTS 
( 
	SELECT tc.* FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
	WHERE tc.TABLE_NAME = 'table_name' AND 
	tc.CONSTRAINT_TYPE = 'PRIMARY KEY' AND
	tc.CONSTRAINT_NAME = cu.CONSTRAINT_NAME 
)ORDER BY cu.ordinal_position

SG
Cause is effect concealed. Effect is cause revealed.

Remove identity property

Xmen Real 17-Feb-08 13:53