Database

29-Nov-06 7:04

I am building a program linked to an access database and i have managed to code in all the operation i need. For some reason, which is probably something im just overlooking, after i input some rows of data i can exit the program go to
C:\mydocuments\Visual Studio 2005\Projects\Project1\Project1\Bin\Debug\Resources\myDatabase
And look at the table and the data is there!! So i have created the data, inserted the data, accepted the changes, closed the program and i can go to the database and view the data in the table, BUT when i start the program over and go to the database again the data in the table is gone. Any Ideas??

ChandraRam29-Nov-06 22:47

ChandraRam

29-Nov-06 22:47

I dont know... maybe your program starts off with deleting the data?

Pete O'Hanlon30-Nov-06 1:24

Pete O'Hanlon

30-Nov-06 1:24

It sounds as though the database may be being dropped and recreated. Is this possible? To test this, rename the database and then run your program again. If it works OK, then your DB is being recreated every time.

Arthur Dent - "That would explain it. All my life I've had this strange feeling that there's something big and sinister going on in the world."
Slartibartfast - "No. That's perfectly normal paranoia. Everybody in the universe gets that."

Deja View - the feeling that you've seen this post before.

Karma3125130-Nov-06 3:51

30-Nov-06 3:51

Hey Pete,
I did what you said the when the program started it through and error and would not run because it wouldnt find the database so i guess thats not the problem. I was thinking mabye there is some sort of auto clear option im missing?

Karma3125130-Nov-06 4:06

30-Nov-06 4:06

Ok so i did a little further testing and The database file in the Resource folder is not being recreated and the program will not run without it. BUT!!!!!, the database file in the bin\debug\ folder is getting recreated. Also, when i go to check the data its never in the resource folder database its always in the debug folder database. So it needs the resource folder database to run, but it doest actually copy the data there!?!? Therefore the data is vanishing because the debug folder database is getting recreated everytime i run the program. So a few Questions to anyone? Why is the debug folder database recreating itself everytime i run the program? Why is the data not going to the resource folder database? Im thinking it might have something to do with the fact that im still in the build process? im using Microsoft Visual Basic 2005 Express Edition.

Pete O'Hanlon30-Nov-06 5:05

Pete O'Hanlon

30-Nov-06 5:05

You're right that it's because you are in the build process. Those helpful guys at MS developed things so that items that your projects depend on are automatically copied across whenever you run things, e.g. the App.Config file is copied and renamed to <appname>.exe.config automatically.

Karma3125130-Nov-06 5:08

dynamic table creation in sql server 2000

30-Nov-06 5:08

GOT IT!!!!!!

http://blogs.msdn.com/smartclientdata/archive/2005/08/26/456886.aspx[^]

Pradip Kishore29-Nov-06 4:57

Pradip Kishore

29-Nov-06 4:57

hi i have a textbox & a button in my form.i have conneted the form to the database in the page load event. now when i insert a name in the textbox & click the button,i want that a table should be created dynamically by name which is entered in textbox1 in the specified database.(i m using vb.net 2002 and sql server 2000)
its urgent ..
thanks in advance..

pradip kishore

Re: dynamic table creation in sql server 2000

Dominic Pettifer29-Nov-06 13:46

Dominic Pettifer

29-Nov-06 13:46

Use a CREATE TABLE statement eg.

Dim sql As String = "CREATE TABLE " & txtTextBox.Text & "( Column1 INT )"

You'll have to specify at least one column though if you're creating a table dynamically.

Dominic Pettifer

Blog: www.dominicpettifer.co.uk

How do i update a select?

FIFI529-Nov-06 4:03

29-Nov-06 4:03

hi there,
i have this problem where i did a select with an aggregate:
----------------------------------------------------------------------
select distinct s.Sender_FName,s.Sender_LName, max(s.createdate) as recent_date,s.User_Flag
from tblSender s
group by s.Sender_FName,s.Sender_LName,s.User_Flag
order by s.Sender_LName,s.Sender_FName
-----------------------------------------------------------------------

what i want to is, to update the results of this query by setting the User_Flag value to 1. Is there some way of doing it like this, or any other way that this can be done without me having to use a loop?

Update (select .....)
set user_flag =1

Thanks,

FIFI

Paddy Boyd29-Nov-06 4:44

Paddy Boyd

29-Nov-06 4:44

Does this not work:

select distinct s.Sender_FName,s.Sender_LName, max(s.createdate) as recent_date, 1 AS User_Flag
from tblSender s
group by s.Sender_FName,s.Sender_LName
order by s.Sender_LName,s.Sender_FName

FIFI529-Nov-06 5:02

29-Nov-06 5:02

Nice try, but this wouldn't work because it doesn't update the table. I need the tblSender Table to reflect this value too.

What i realise too is that i could use the result of the query and store it in a Temp table or something, and then use that to update the Sender table. BUT,...and this is a big BUT... i would need to extract the Sender_id field in the query too and then use that to match it back to the tblSender table, but when i include this field it messes up the result.

But all in all, if i can just get the result to be updated in the query that would be great.....

Michael Potter29-Nov-06 5:51

Michael Potter

29-Nov-06 5:51

I believe your UPDATE should look something like this:

UPDATE
    tblSender 
SET
    User_Flag = 1
WHERE
    createdate = (SELECT 
                      MAX(s.createdate)
                  FROM
                      tblSender s
                  WHERE
                      s.Sender_FName = tblSender.Sender_FName AND
                      s.Sender_LName = tblSender.Sender_LName)

My 2 cents:
DISTINCT is a waste of syntax since you are using a GROUP BY Clause.
I see more and more examples on this board of tables without proper primary keys. Last/First names are lousy columns to use for an update (or a primary key).

FIFI529-Nov-06 7:44

29-Nov-06 7:44

thanks alot, this seems to do the job. I'm gonna look it over and let u know

FIFI

FIFI530-Nov-06 4:29

Re: SQL Server with InfoPath

30-Nov-06 4:29

yeah man this works great. Thanks alot.

SQL Server with InfoPath

lyntonS29-Nov-06 2:56

lyntonS

29-Nov-06 2:56

Please can someone assist me with hooking-up an InfoPath Button event to a SQL Database. Button controls inserting of new data? Thanks for the rescue!

Kamalonline44-Dec-06 20:30

Kamalonline4

4-Dec-06 20:30

Hello...Unfortunately infopath dosent provide inserting/Updating data from Infopath to SQl server.Only we can only retrive data from SQL Server - Infopath but not insert/update.
However we can do this by implementing webservice.

1.Create webservice that can interact with sql server(write functionality Update/Delete what ever you want)
2.Create connection to webservice in infopath(Tools>connectins)
3.Submit data to webservice from infopath.

How ever some of professional thirdparty tools available to interact with sql server,like QDABRA DBXL tool.

Regards
Kamalonline4@yahoo.com

database design

Rupa Kalluru29-Nov-06 0:28

Rupa Kalluru

29-Nov-06 0:28

Hi everbody,

Iam currently working on datbase design.
Iam stucked at:If a primary key is assigned in database,I need to know all the foreign key relation of this PK on other tables in that database.

please help out on this,

thanks in advance,
Rupa

Rupa

User 171649229-Nov-06 1:09

User 1716492

29-Nov-06 1:09

Seems to me you need to learn the basics.

http://www.htmlgoodies.com/primers/database/article.php/3478051[^] is a series of articles.

You should use google [^] to search for Database basics tutorials

modified 1-Aug-19 21:02pm.

ed^nrgc29-Nov-06 7:04

ed^nrgc

29-Nov-06 7:04

Primary keys and foreign keys are the backbone for how tables relate to each other.

Rupa Kalluru29-Nov-06 16:37

Rupa Kalluru

29-Nov-06 16:37

I need to know the tables which are in related with the same foreign key.
Pls do tell the table which constitutes the FK available in the database.

Means that IS there any table in the database in SQL SERVER which is having the FK's.sitting in it.

Please try to understand the query first,then reply it in more meaning manner

Surya