Stored Proc to add a new Record and return an AutoValue - Database Discussion Boards

Chodici Mrkev24-Aug-04 21:46

24-Aug-04 21:46

Hi,
I am working on the guestbook in VB.NET with SQL db. I have a database for registered users, there are names, surnames, and mainly paths to user-defined images, which they want them to be seen together with their comments.
I have two possibilities how to do it:
1)I will authentify the user with the user control, load the guestbook.aspx - I will get all data from table GUESTBOOK only, when user submits the form, I will get the ImagePath from db and save the name, comment, AND IMAGEPATH to db table GUESTBOOK. I think this way is faster during the guestbook.aspx load, but I have to save the same ImagePaths each time the user sends the comment.

2)I will authentify the user with the user control, load the guestbook.aspx - I will get the names and comments from table GUESTBOOK and ImagePaths based on usernames from USERS table. When user submits the form, I will only save the GUESTBOOK table, not more.

What's better - faster?

DELETE table, ignore key constraints

Christian Graus24-Aug-04 13:54

Christian Graus

24-Aug-04 13:54

I've got a database, it's not set up with cascading delete, and I'd prefer it not be. Instead, I'd like to turn of foreign key checking when I delete all data from all the tables. Is there a way to delete all data without figuring out the correct delete order, by getting SQL Server to ignore the constraints ?

Christian

I have drunk the cool-aid and found it wan and bitter. - Chris Maunder

how do I write to an access DB using ADO & VisualC++ ?

Eduardo M. Rey24-Aug-04 9:48

Eduardo M. Rey

24-Aug-04 9:48

I am currently using the following statement to read data from a table, column
my_String = _strdup((char*) ((_bstr_t)(pConnectRead->GetFields()->GetItem("nom_medico")->GetValue())) );

How do i write data to this column? D'Oh! | :doh:

Please help,

THanks

Ed Rey

Re: how do I write to an access DB using ADO & VisualC++ ?

S Sansanwal24-Aug-04 12:23

S Sansanwal

24-Aug-04 12:23

pConnectRead->GetFields()->GetItem("nom_medico")->Value = (LPCTSTR)my_String;
pConnectRead->Update();

Sanjay Sansanwal
www.sansanwal.com

Using ADO with Visual C++

Eduardo M. Rey24-Aug-04 9:41

Eduardo M. Rey

24-Aug-04 9:41

I am referencing the libraries from msado15.dll and retreiving data from an access database using visual C++. I have already connected succesfully to the DB, and can read data. I use the following command to retreive information from a particular field in the table:
my_String = _strdup((char*) ((_bstr_t)(pConnectRead->GetFields()->GetItem("nom_medico")->GetValue())) );

How do i write a boolean value to a field, once i have established connection to the DB and currently in the row of interest. Confused | :confused:

Eduardo M. Rey

Re: Using ADO with Visual C++

S Sansanwal24-Aug-04 12:27

S Sansanwal

24-Aug-04 12:27

if(Condition is true)
pConnectRead->GetFields()->GetItem("nom_medico")->Value = true;
else
pConnectRead->GetFields()->GetItem("nom_medico")->Value = false;

pConnectRead->Update();

Sanjay Sansanwal
www.sansanwal.com

Re: Using ADO with Visual C++

Eduardo M. Rey25-Aug-04 5:18

Eduardo M. Rey

25-Aug-04 5:18

Thanks for your prompt reply,

I am using the statement you provided:
pConnectRead->GetFields()->GetItem("nom_medico")->Value = false;
within a try-catch loop. but it jumps to the _com_error as soon as it executes this statement. Maybe i am giving the wrong premissions in the statement right before:

pConnectRead->Open(commandString, strConectIt, adOpenStatic, adLockReadOnly, adCmdText);

Thanks again for your help, you were extremely helpful.

Ed

Eduardo M. Rey

Stored Proc to add a new Record and return an AutoValue

matthias s.24-Aug-04 8:49

matthias s.

24-Aug-04 8:49

Hi,

I've got a table (whith an index on the CarId field (IdentityIncrement). Now I'd like to write a stored procedure which inserts a new record into this table and returns the CarId of the newly generated record. But I don't know how to do this w/o using a SELECT after the INSERT. Can anyone pls. help?

Thanks in advance.

Matthias

If eell I ,nust draw to your atenttion to het fakt that I can splel perfrectly well - i;ts my typeying that sukcs.
(Lounge/David Wulff)

www.emvoid.de

Re: Stored Proc to add a new Record and return an AutoValue

Colin Angus Mackay24-Aug-04 11:21

Colin Angus Mackay

24-Aug-04 11:21

Create your stored procedure specifying which parameter(s) is/are output parameters like this

CREATE PROCEDURE GetInformation
(
    @SomeKey int,
    @SomeResult int OUTPUT
)
AS
SELECT @SomeResult = AnswerField 
FROM MyTable 
WHERE ThePrimaryKey = @SomeKey
GO

Then in your .NET application when you specify the parameters for the stored procedure, make sure you specify which parameter(s) is/are output.

SqlParameter someResult = new SqlParameter("@SomeKey", SqlDbType.Int);
someResult.Direction = ParameterDirection.Output;

When you have run your query, you can then get the value of the output parameter with

int result = (int)someResult.Value;

Does this help?

"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell

Not getting the response you want from a question asked in an online forum: How to Ask Questions the Smart Way!

Re: Stored Proc to add a new Record and return an AutoValue

cmk24-Aug-04 13:08

cmk

24-Aug-04 13:08

After the insert @@identity will contain the new CarId value.

e.g.

CREATE PROCEDURE  InsCar
	@carType  int
AS 
BEGIN
	insert  into  car ( CarType, AddDtTm )  values( @carType, GetDate() )
	select  @@identity as UID
END
GO

[EDIT]
... or, instead of 'select @@identity ...' specify an OUTPUT parameter as Colin mentioned.
[/EDIT]

...cmk

Save the whales - collect the whole set

Create Table, Default Value

si_6924-Aug-04 1:37

si_69

24-Aug-04 1:37

hi all,

i want to run the below query on a Ms-Access database

CREATE TABLE products (
product_no integer PRIMARY KEY,
name text,
price numeric DEFAULT 9.99
);

when i run it, its seems that access doesnt like the DEFAULT 9.99 for some reason

however if i run the query in SQL Server it works fine, but i need to run it on
an access database

ne1 any ideas of a way round this ???

thanks

si

Re: Create Table, Default Value

Anonymous24-Aug-04 3:54

Anonymous

24-Aug-04 3:54

You can use the MS-Access table designer screen to set the column's default value.

If you want to do this programatically then try using the "Default" property in the ADOX Column Properties collection. The SQL syntax in MS-Access does not seem to support this.

Andy

Re: Create Table, Default Value

S Sansanwal24-Aug-04 12:18

S Sansanwal

24-Aug-04 12:18

You need to make Access Table/Queries compatiable with SQL ANSI.
Go to Tools -> Options -> Table/Queries Tab
Now, check the "This database" checkbox under "SQL Server compatiable syntax" option on bottom right side.
Now, you would be able to run this query.

Sanjay Sansanwal
www.sansanwal.com

How do I run my SQL script from .msi ??

IrishSonic23-Aug-04 23:20

IrishSonic

23-Aug-04 23:20

Hiya I have a script that I want run when installing my app.

How do I do this??

Thanks.

Re: How do I run my SQL script from .msi ??

Colin Angus Mackay23-Aug-04 23:30

Colin Angus Mackay

23-Aug-04 23:30

IrishSonic wrote:
How do I do this??

Create a project in .NET that contains an custom installer action.

This article from MSDN should guide you in the right direction: Walkthrough: Using a Custom Action to Create a Database During Installation[^]

Re: How do I run my SQL script from .msi ??

IrishSonic24-Aug-04 1:39

IrishSonic

24-Aug-04 1:39

Colin

You are a star!!
Cheers.

Update database from a XML dataset file ?

zWyThoS23-Aug-04 12:22

zWyThoS

23-Aug-04 12:22

Hi !

I have a problem. I need to update my database from a xml dataset file but it doesn't work.

1)I fill my dataset with the dataAdapter.
2)I change some data in my datagrid
3)I save my dataset with de writeXml method. ( this part is ok)
4)I fill my dataset with the xml file, readXml (this part is ok)
5) Update ( It doesn't work. my update command is ok and I have no error)

I also tried the getChange method, and saved the result into a dataset saved in xml format. The modified rows(from the dataset created with the getChange method) are insert in the database (not update).

I need help Wink | ;)

Thanks

Re: Update database from a XML dataset file ?

S Sansanwal23-Aug-04 13:17

S Sansanwal

23-Aug-04 13:17

do dataSet.AcceptChanges() after filling dataset

Sanjay Sansanwal
www.sansanwal.com

Re: Update database from a XML dataset file ?

mikasa25-Aug-04 12:27

mikasa

25-Aug-04 12:27

Thr problem with this is that you need to save the XML using a "DiffGram" model. By default, this is not used in the WriteXML and when you Save the XML file and Read it back in, the DataSet thinks there are no changes to be updated. However, one last Quirk, you also need to save the Schema into a different file as well, since you cannot save both the Schema AND the DiffGram into one file.

DISTINCT values of Datatable columns???

mittalpa23-Aug-04 11:43

mittalpa

23-Aug-04 11:43

Hi
I have a dataset with one table (with 5 columns). Now, I want to extract the DISTINCT values of two columns (OrgID, OrgDesc) and bind it to a combo box.

How can I do that? Please advice. Thanks

PJ

Follow your goals, Means will follow you ---Gandhi---

Re: DISTINCT values of Datatable columns???

S Sansanwal23-Aug-04 13:24

S Sansanwal

23-Aug-04 13:24

Check
http://support.microsoft.com/default.aspx?scid=kb;EN-US;326176

Sanjay Sansanwal
www.sansanwal.com

Re: DISTINCT values of Datatable columns???

mittalpa24-Aug-04 4:23

mittalpa

24-Aug-04 4:23

thanks .. works great. Smile | :)

Follow your goals, Means will follow you ---Gandhi---

SmallDateTime and changing to DateTime

Paul Watson23-Aug-04 8:37

Paul Watson

23-Aug-04 8:37

So you would think a SQL expert like me *ok you didn't have to laugh so loud* would already know this, but I didn't and got burnt.

SmallDateTime rounds seconds to the nearest minute. Fair enough. I didn't know but hey, we have DateTime so lets change the column datatype to that.

Only existing records are not affected. WTF! They act like the column is still a SmallDateTime. Manually adding in a second to the value doesn't help existing records either alas.

So I convert to NVarchar as a temporary hack and use GetDate. Charming, still no seconds. WTF!

So my question is; apart from recreating the whole column and loosing that data, how do I get what was a former SmallDateTime and is now a DateTime to store seconds on existing rows?

I am sure this is one of those Design Features too.

regards,
Paul Watson
Bluegrass
South Africa

Christopher Duncan wrote:
"I always knew that somewhere deep inside that likable, Save the Whales kinda guy there lurked the heart of a troublemaker..."

Crikey! ain't life grand?

Re: SmallDateTime and changing to DateTime

Colin Angus Mackay23-Aug-04 10:57

Colin Angus Mackay

23-Aug-04 10:57

I never knew that either. Thanks for the heads up.

A proposal to getting it working might be to create a new column, copy the data to the new column, then remove the old column and finally change the name of the column. That way, at least you get to keep the data.

Re: SmallDateTime and changing to DateTime

Steven Campbell23-Aug-04 11:16

Steven Campbell

23-Aug-04 11:16

I think you must be having a blond moment. The datetime type has been used by many people, and it works just fine. Perhaps your particular view of the data is not showing the seconds, but they most certainly are there.

my blog

Last Visit: 31-Dec-99 19:00 Last Update: 5-Dec-23 15:48

Refresh

ᐊ Prev 1...2210 2211 2212 221322142215 2216 2217 2218 2219 Next ᐅ

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