Database

How many primary key constraints can we have in a table of ms-access?

18-Jun-09 2:42

Ebube wrote:
i develop my application backend on ms sql 2008. but my client is using 2000

This begs the question why you didn't find this out at the start.

Ebube wrote:
should i script all the database object( tables, storeprocedures .e.t.c)

You can get scripts generated automatically, however, you'll still have a lot of editing to do because there are lots of minor differences between the SQL used in SQL Server 2000 and the SQL used in SQL Server 2008.

sivakumar.mariappan18-Jun-09 0:31

sivakumar.mariappan

18-Jun-09 0:31

Hi,

How many primary key constraints can we have in a table of ms-access? Any other constraints available in ms-access other than primary key?

Please share your ideas...

Thanks,
Sivakumar.M.

Re: How many primary key constraints can we have in a table of ms-access?

Eddy Vluggen18-Jun-09 3:07

Eddy Vluggen

18-Jun-09 3:07

One table, one primary key. You can verify this in the documentation[^];
"You can use the PRIMARY KEY reserved words to designate one field or set of fields in a table as a primary key. All values in the primary key must be unique and not Null, and there can be only one primary key for a table."

I are troll Smile | :)

Re: How many primary key constraints can we have in a table of ms-access?

riced18-Jun-09 7:03

riced

18-Jun-09 7:03

As Eddy said one primary key per table.
But you can create indexes on non-key fields and set them to not allow duplicates. Effectively giving you a constraint that the column must contain unique values.
It's also possible to allow two (or more) columns to have duplicates while the combination of the two columns is unique - again by creating an appropriate index.

Regards
David R
---------------------------------------------------------------
"Every program eventually becomes rococo, and then rubble." - Alan Perlis

Re: How many primary key constraints can we have in a table of ms-access?

Niladri_Biswas19-Jun-09 4:03

Niladri_Biswas

19-Jun-09 4:03

How many primary key constraints can we have in a table of ms-access?

Ans: Only 1. Not only in MSAccess, in any database i.e. Oracle, Sql Server etc. It is a general database rule.

You can have many FK and Composite keys however.

Any other constraints available in ms-access other than primary key?

Ans:

Yes.

      Primary key constraints
      Foreign key constraints
      Check constraints
      Check constraint expressions
      Unique constraints
      Default constraints

A little search in google gave me the second answer

<a href="http://office.microsoft.com/en-us/access/HP030838791033.aspx">http://office.microsoft.com/en-us/access/HP030838791033.aspx</a>[<a href="http://office.microsoft.com/en-us/access/HP030838791033.aspx" target="_blank" title="New Window">^</a>]

Hope it helps
Smile | :)

Niladri Biswas

Creating primary key constraints on a table using VB.NET

sivakumar.mariappan18-Jun-09 0:30

sivakumar.mariappan

18-Jun-09 0:30

Hi,

I have developed a code to create a ms-access table through VB.NET as mentioned below.

ADOXtable = New ADOX.Table
ADOXtable.Name = "ACN_CHARX_INSTANCE"
ADOXtable.Columns.Append("LOGICAL_DB_NAME", ADOX.DataTypeEnum.adVarWChar, 32)
ADOXtable.Columns.Append("DIM_NAME", ADOX.DataTypeEnum.adVarWChar, 32)
ADOXtable.Columns.Append("CHARX_NAME", ADOX.DataTypeEnum.adVarWChar, 128)
ADOXtable.Columns.Append("CHARX_TABLE", ADOX.DataTypeEnum.adVarWChar, 128)
ADOXtable.Columns.Append("CHARX_VALUE_COL", ADOX.DataTypeEnum.adVarWChar, 128)
ADOXtable.Columns.Append("VALUES_UNIQUE", ADOX.DataTypeEnum.adInteger)
ADOXtable.Columns.Append("CHARX_ORDER_COL", ADOX.DataTypeEnum.adVarWChar, 128)
ADOXtable.Columns.Append("CHARX_ORDER", ADOX.DataTypeEnum.adInteger)
ADOXtable.Columns.Append("CHARX_NUMERIC_COL", ADOX.DataTypeEnum.adVarWChar, 128)
ADOXtable.Columns.Append("BITMAP_COL", ADOX.DataTypeEnum.adVarWChar, 128)

cat.Tables.Append(ADOXtable)

Now i want to set primary key constraints for this table through coding. Please share your ideas...

Thanks,
Sivakumar.M.

Re: Creating primary key constraints on a table using VB.NET

riced18-Jun-09 7:15

riced

18-Jun-09 7:15

Something like this might do it.

pk = New ADOX.Index
pk.Name = "PK"
pk.PrimaryKey = True
pk.Unique = True
pk.Columns.Append("TheKeyColumn")
ADOXtable.Indexes.Append(pk)

You might have to set some other properties for pk (e.g. IndexNulls) - have a look at the help/Intellisense.

Regards
David R
---------------------------------------------------------------
"Every program eventually becomes rococo, and then rubble." - Alan Perlis

How do I do that? [modified]

kripa2117-Jun-09 22:48

17-Jun-09 22:48

I have car entry screen, in which user can insert a car details such as number, color, model etc. & amenities (select multiple for 1 car).
Now search screen, when user selects aminities from list., I want to show only those cars who have all selected amminities.

modified on Thursday, June 18, 2009 6:25 AM

Colin Angus Mackay17-Jun-09 23:00

17-Jun-09 23:00

kripa ostwal wrote:
I have car entry screen, in which user can insert a car details such as number, color, model etc. & amenities (select multiple for 1 car).
Now search screen, when user selects aminities from list., I want to show only those cars who have all selected amminities.

Assuming your question is: How do I do that?

You are going to have to show us the data model. We cannot help you construct a query without knowing what the data actually looks like (what the user can do on the front end isn't so useful as it may not match the data model)

kripa2117-Jun-09 23:30

17-Jun-09 23:30

I have database with carMaster,AmenityMST & carAmm.

CarMST

carCode carNumber color
1 MH12-KO1212 blue
2 MH42-jk1234 red

AmenityMST

ammCode ammName
1 mobile charger
2 video coach
3 AC

carAmm
carCode ammCode
1 1
1 3
2 2
2 3

Now I want, when user search car with 'video coach' & 'AC', he should get only 1 car i.e. 2
I used
"select carCode from carAmm where ammCode in (2,3)"
but it returns me both cars, even car have not 'video coach'

Colin Angus Mackay17-Jun-09 23:45

17-Jun-09 23:45

kripa ostwal wrote:
where ammCode in (2,3)

That is because you are asking for any cars with ammCode of 2 OR 3.

Try a join instead between two result sets.

First set:
SELECT carCode from carAmm WHERE ammCode = 2

Second Set:
SELECT carCode from carAmm WHERE ammCode = 3

Something like this:

SELECT c1.carCode
FROM carAmm AS c1
INNER JOIN carAmm AS c2 ON c1.carCode = c2.carCode
WHERE
c1.ammCode = 2
AND
c2.ammCode = 3

kripa2118-Jun-09 0:00

18-Jun-09 0:00

s, bt i wrote store procedure for it coz there will be n number of aminities in future for search criteria.
& I passed a string as '2,3'
Can I split it in store procedure & create joins?
or any other way to do this?

Colin Angus Mackay18-Jun-09 0:05

18-Jun-09 0:05

kripa ostwal wrote:
s,

kripa ostwal wrote:
I passed a string as '2,3'

If you are using SQL Server 2008 you can pass tables. That way you have no crazy string manipulation to get the data you need.

kripa ostwal wrote:
Can I split it in store procedure & create joins?

Yes, but the splitting operation is a PITA.

kripa ostwal wrote:
or any other way to do this?

You must already be using dynamic SQL, so just variations on a theme (unless you are using SQL Server 2008)

kripa2118-Jun-09 0:14

2 people create new record in datagridview on dataset, both save, last to save gets primary key violation - fix?

18-Jun-09 0:14

i m using sql server 2000 with asp.net

sherifffruitfly17-Jun-09 13:57

sherifffruitfly

17-Jun-09 13:57

I'm certain this is about as noob-ish as it gets, but I'd like to know what typical solutions to this issue are.

2 people (on two computers) get passed their own dataset instances, representing 1 table in the database. They both want to add a row to the db table. They both click on the new row in the datagridview, and it happily inserts a new row on their monitors, with a new Primary Key value (identity column). Problem is, the number is the same on both computers. So the first person can save just fine, but the second person naturally receives an PK violation error when he or she saves.

How is this issue supposed to be dealt with (i.e. avoided)? Avoid creating the PK value at all on the client side,allowing the database to create the PK when insert has been performed, and just refresh the datagridview with that value (created by the database)?

Some other approach?

Thanks for ideas,

cdj

Re: 2 people create new record in datagridview on dataset, both save, last to save gets primary key violation - fix?

Colin Angus Mackay17-Jun-09 14:47

Re: 2 people create new record in datagridview on dataset, both save, last to save gets primary key violation - fix?

17-Jun-09 14:47

I generally avoid PK creation on the client-side. The database alone should be responsible for that. However, if that isn't possible, then my next preferred solution is to use a GUID. That way the two machines will generate different numbers.

Of course, your DBA may baulk at the use of GUIDs as the data will be inserted randomly (and since the PK is often the clustered index, that can slow the performance of the database if you are doing a lot of inserts or do searches on sequential [or near-sequential] ranges of PK values)

sherifffruitfly17-Jun-09 14:51

sherifffruitfly

17-Jun-09 14:51

Yep - as I was typing the question, I realized that the answer was to simply not create the pk client side.

Smile | :)

Thanks!

cdj

SQL Server Backup and Restore questions

Hypermommy17-Jun-09 8:43

Hypermommy

17-Jun-09 8:43

Hi all,

I need to make a copy of a SQL Server (2000, I think) database and a friend told me the easiest way to do this was to back up the database and then restore it to a different name. Does anyone know a good source for a step by step guide on how to do this? The database I have to back up is a production database and then I'm going to restore it to a different name to make a test copy of it and I'm just nervous working with production stuff. So a step by step guide would be very helpful.

Also... if the hosting provider is backing up the server every night, does that also back up the SQL Server database? I'm just wondering if I need to tell him some specific steps to backing up the database, but if backing up the whole server does the trick then I don't need to.

Thanks!

Denise "Hypermommy" Duggan

Re: SQL Server Backup and Restore questions

Colin Angus Mackay17-Jun-09 14:53

Re: SQL Server Backup and Restore questions

17-Jun-09 14:53

Hypermommy wrote:
The database I have to back up is a production database and then I'm going to restore it to a different name to make a test copy of it and I'm just nervous working with production stuff.

You should be restoring it onto a non-production server. That way you are isolated from the production server and it would be near impossible to damage the production system while you were working on your newly created test/development system.

Hypermommy wrote:
Also... if the hosting provider is backing up the server every night, does that also back up the SQL Server database?

Most likely, but that is a question for your hosting provider.

SQL Backups work differently from file backups because the SQL Server will be on-line and modifying its files. The hosting provider would have to be taking specific SQL backups. They are likely to be doing this already.

Hypermommy30-Jul-09 1:13

Hypermommy

30-Jul-09 1:13

I appreciate it. Yes, ideally I'd be using a different server. Unfortunately, with this client that's not do-able. It's something I hope to be able to convince them of the necessity of in the future but for now.....

As far as SQL Server being backed up... I doubt it. If they have to do something different from a regular Windows backup then I don't believe it's happening. Just something else I need to talk to them about.

Figuring that the hosting provider (a small company) is not doing a separate SQL Server backup, is it possible to point me to some quick literature of just a couple pages that I could send him to say "do this"?

Denise "Hypermommy" Duggan

"Make sure you have not released a resource before attempting to use it". HELP!!!!

ChiSmile17-Jun-09 7:26

ChiSmile

17-Jun-09 7:26

Hello there!
My code is highlighted below.I have five tabpages(two is shown here!) on a form called vwpatient that runs each time a tabpage is selected.The code below works but my problem is this: there is a textbox called sachPatient.patID from another form called sachPatient, the code pulls data from the database on clicking one or two pages, but if more than two is clicked, an exception fires saying "Make sure you have not released a resource before attempting to use it". I have tried endlessly to fix this problem to no avail. And am still trying!!.
I would appreciate if someone can help me.
Thanks in advance!!!

Private Sub TabControl1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles TabControl1.SelectedIndexChanged


For Each tabcont As Control In Me.Controls
            If TypeOf tabcont Is TabControl Then
                For Each tp As Control In tabcont.Controls
                    If TypeOf tp Is TabPage Then
                         Select Case tp.Name
                            Case "vwProfTabPg"

                                Try

                                    LoadData()
                                    ' Configure and execute the command.
                                    cmd = New System.Data.SqlClient.SqlCommand("SELECT * FROM Patient_profile where patient_Id = @Pat_ID")
                                    cmd.Connection = cn
                                    Dim paraPatId As SqlParameter = cmd.Parameters.Add("@Pat_ID", sachPatient.patID.Text)
                                    ' Load the DataTable.
                                    datadapt = New System.Data.SqlClient.SqlDataAdapter(cmd)
                                    datadapt.Fill(ds)
                                    ds.Tables.Add(dt)

                                    ' Bind the DataGrid.

                                    vwProfDG.DataSource = ds.Tables(0)
                                    vwProfDG.Visible = True

                                Catch ex As SqlException
                                    MsgBox(ex.Message.ToString())
                                Finally
                                    cn.Close()
                                End Try

                            Case "vwLabTabPg"
                                Try

                                    LoadData()

                                    ' Configure and execute the command.
                                    cmd = New System.Data.SqlClient.SqlCommand("SELECT * FROM lab where patient_Id = @PatLa_ID")
                                    cmd.Connection = cn
                                    Dim paraPatId As SqlParameter = cmd.Parameters.Add("@PatLa_ID", sachPatient.patID.Text)
                                    ' Load the DataTable.
                                    datadapt = New System.Data.SqlClient.SqlDataAdapter(cmd)
                                    datadapt.Fill(ds)
                                    ds.Tables.Add(dt)

                                    ' Bind the DataGrid.

                                    vwLabDatGrid.DataSource = ds.Tables(0)
                                    vwLabDatGrid.Visible = True

                                Catch ex As SqlException
                                    MsgBox(ex.Message.ToString())
                                Finally
                                    cn.Close()
                                End Try
                        End Select
                    End If
                Next
            End If
        Next

Re: "Make sure you have not released a resource before attempting to use it". HELP!!!!

Colin Angus Mackay17-Jun-09 15:01