Datatype for Primary key fields ...

Hi,
As far as my understanding goes, normally PK would be set on fields whose
datatype is INT. But in one of the project I saw 99% of the tables they have
used Varchar datatype for PK fields.
This internally means that it would string comparisons. I was arguing that
SQL server isn't good at 'String comparisons'. Am I right? or Am i missing
something? Any pointers on this topic would be of great help to me.
Best Regards
Vadivel
http://vadivel.blogspot.comVadivel wrote:

> Hi,
> As far as my understanding goes, normally PK would be set on fields whose
> datatype is INT. But in one of the project I saw 99% of the tables they ha
ve
> used Varchar datatype for PK fields.
> This internally means that it would string comparisons. I was arguing that
> SQL server isn't good at 'String comparisons'. Am I right? or Am i missing
> something? Any pointers on this topic would be of great help to me.
> Best Regards
> Vadivel
> http://vadivel.blogspot.com
This is the wrong question because the datatype for keys is determined
by the data you need to model in the table. For example how are you
going to represent names using an INTEGER?
If you meant to ask "what should I use for an artiificial surrogate
key?" then you can search the archives of this group for many previous
discussions on that topic.
David Portas
SQL Server MVP
--|||"Vadivel" <Vadivel@.discussions.microsoft.com> wrote in message
news:F2B2A710-79AB-460F-8057-46B4A0166183@.microsoft.com...
> Hi,
> As far as my understanding goes, normally PK would be set on fields whose
> datatype is INT. But in one of the project I saw 99% of the tables they
> have
> used Varchar datatype for PK fields.
> This internally means that it would string comparisons. I was arguing that
> SQL server isn't good at 'String comparisons'. Am I right? or Am i missing
> something? Any pointers on this topic would be of great help to me.
> Best Regards
> Vadivel
> http://vadivel.blogspot.com
If you don't use surrogates, I'd say that most natural primary keys are
strings.
Even telephone numbers, license plate numbers, zip codes, serial codes,
credit card numbers, bar codes, invoice numbers, bank account numbers... are
in reality, strings.|||Are you speaking of a natural or surrogate primary key?
http://www.aspfaq.com/show.asp?id=2504
Natural keys can be a combination of most any data type. However, if the key
in it's basic form is numeric (such as SSN, CustomerNo, or PhoneNumber) then
do try to define it as integer, becuase it's storage will be smaller than
character, and it will thus result in less memory usage and fewer index
pages to traverse.
If you plan to use an identity column as a surrogate primary key, then I
don't see a reason to use anything but an integer.
http://www.windowsitpro.com/Article...ArticleID=23449
Data Type Performance Tuning Tips for Microsoft SQL Server
http://www.sql-server-performance.com/datatypes.asp
In general integer based comparisons are more efficient than Char or VarChar
comparisons, but I don't know of SQL Server specifically not being not good
at character comparisons relative to any other DBMS system such as Oracle or
DB2.
"Vadivel" <Vadivel@.discussions.microsoft.com> wrote in message
news:F2B2A710-79AB-460F-8057-46B4A0166183@.microsoft.com...
> Hi,
> As far as my understanding goes, normally PK would be set on fields whose
> datatype is INT. But in one of the project I saw 99% of the tables they
> have
> used Varchar datatype for PK fields.
> This internally means that it would string comparisons. I was arguing that
> SQL server isn't good at 'String comparisons'. Am I right? or Am i missing
> something? Any pointers on this topic would be of great help to me.
> Best Regards
> Vadivel
> http://vadivel.blogspot.com|||I know that telephone numbers, Credit card nos, Zipcode all would be varchar
fields only. As we won't be doing any mathematical calculation based on that
data there isn't a need for us to go for INT datatype. Even though u would
have those fields as Varchar in ur DB won't you have a ID field in that
table? Won't that ID field be of INT datatype?
Best Regards
Vadivel
http://vadivel.blogspot.com
"Raymond D'Anjou" wrote:

> "Vadivel" <Vadivel@.discussions.microsoft.com> wrote in message
> news:F2B2A710-79AB-460F-8057-46B4A0166183@.microsoft.com...
> If you don't use surrogates, I'd say that most natural primary keys are
> strings.
> Even telephone numbers, license plate numbers, zip codes, serial codes,
> credit card numbers, bar codes, invoice numbers, bank account numbers... a
re
> in reality, strings.
>
>|||"Vadivel" <Vadivel@.discussions.microsoft.com> wrote in message
news:1CCC4883-5193-4706-9F62-CBEECE8FBF4C@.microsoft.com...
>I know that telephone numbers, Credit card nos, Zipcode all would be
>varchar
> fields only. As we won't be doing any mathematical calculation based on
> that
> data there isn't a need for us to go for INT datatype. Even though u would
> have those fields as Varchar in ur DB won't you have a ID field in that
> table? Won't that ID field be of INT datatype?
> Best Regards
> Vadivel
Read JTs response including the links.
Your ID field (column) is a surrogate.
The use of surrogates (including Identity) has been discussed in this
newsgroup "ad nauseum".
Even though you can use surrogates as keys, there should always exist a
"natural" primary key in your tables.|||"Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
news:u8lYzCMAGHA.3936@.TK2MSFTNGP12.phx.gbl...
> "Vadivel" <Vadivel@.discussions.microsoft.com> wrote in message
> news:1CCC4883-5193-4706-9F62-CBEECE8FBF4C@.microsoft.com...
> Read JTs response including the links.
> Your ID field (column) is a surrogate.
> The use of surrogates (including Identity) has been discussed in this
> newsgroup "ad nauseum".
> Even though you can use surrogates as keys, there should always exist a
> "natural" primary key in your tables.
One other thing:
Concerning your "performance hit" using INTs versus VARCHARs.
From what I've read, especially in today's databases, if there is one it's
negligable.|||Thanks for the links JT.
Lets assume that I have a table with just two fields,
CityID Varchar(20) -- PK
CityName Varchar(50)
Now I would be using this field CityID in some other table as a FK field. Is
that ok? Or is it advisable / mandatory to have another column with INT
datatype and use it in other tbls as FK?
Best Regards
Vadivel
http://vadivel.blogspot.com
"JT" wrote:

> Are you speaking of a natural or surrogate primary key?
> http://www.aspfaq.com/show.asp?id=2504
> Natural keys can be a combination of most any data type. However, if the k
ey
> in it's basic form is numeric (such as SSN, CustomerNo, or PhoneNumber) th
en
> do try to define it as integer, becuase it's storage will be smaller than
> character, and it will thus result in less memory usage and fewer index
> pages to traverse.
> If you plan to use an identity column as a surrogate primary key, then I
> don't see a reason to use anything but an integer.
> http://www.windowsitpro.com/Article...ArticleID=23449
> Data Type Performance Tuning Tips for Microsoft SQL Server
> http://www.sql-server-performance.com/datatypes.asp
> In general integer based comparisons are more efficient than Char or VarCh
ar
> comparisons, but I don't know of SQL Server specifically not being not goo
d
> at character comparisons relative to any other DBMS system such as Oracle
or
> DB2.
>
> "Vadivel" <Vadivel@.discussions.microsoft.com> wrote in message
> news:F2B2A710-79AB-460F-8057-46B4A0166183@.microsoft.com...
>
>|||If CityID is the primary key, then that is the column you want to use as the
foreign key when joining referencing tables. However, I don't understand why
CityID would be 20 characters long. Is this something like an ISO code
assigned to every city on the planet? If CityID contains embedded attributes
like geographic coordinates or nation, state, county codes, then split those
attributes out as seperate columns.
Provide more details about what CityID means and how it's values are
assigned.
"Vadivel" <Vadivel@.discussions.microsoft.com> wrote in message
news:67DAA04A-A277-49AB-8745-521699D2F06F@.microsoft.com...
> Thanks for the links JT.
> Lets assume that I have a table with just two fields,
> CityID Varchar(20) -- PK
> CityName Varchar(50)
> Now I would be using this field CityID in some other table as a FK field.
> Is
> that ok? Or is it advisable / mandatory to have another column with INT
> datatype and use it in other tbls as FK?
> Best Regards
> Vadivel
> http://vadivel.blogspot.com
>
> "JT" wrote:
>

DataSource Not Found

hi... i am very new to SQL server.. previously was using MySQL...
now i am trying to connect my project to SQL Server..
but i wasnt able to...
i keep getting errors

System.Data.Odbc.OdbcException: ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified at System.Data.Odbc.OdbcConnection.Open() at icms.DB.q(String mySTR) in C:\icms\DB.vb:line 30

below is my webconfig
i am not very sure about the value for the user.. because if it is MySQL i can get it from my control centre.. but what about SQL server ? where should i get my value ?

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<appSettings>
<add key="db" value="icms" />
<add key="db_user" value="sqladmin" />
<add key="db_server" value="server" />
<add key="db_pwd" value="*****" />
<add key="session_timeout" value="600" />
</appSettings
<system.web
<compilation defaultLanguage="vb" debug="true" />
<trace enabled="true"/>
<customErrors mode="RemoteOnly" /
</system.web
</configuration
======================

Dim myCMD As New SqlCommand
Public Sub New()
Dim db_server = AppSettings("db_server")
Dim db = AppSettings("db")
Dim db_user = AppSettings("db_user")
Dim db_pwd = AppSettings("db_pwd")
Dim DBConnection As String = "DRIVER={SQL Server};" & _ '<==== is my driver correct?
"SERVER=" & db_server & ";" & _
"DATABASE=" & db & ";" & _
"UID=" & db_user & ";" & _
"PASSWORD=" & db_pwd & ";" & _
"OPTION=3;"
myDB.ConnectionString = DBConnection
myCMD.Connection = myDB
End Sub
'Public Function q(ByVal mySTR As String) As OdbcDataReader 'SQL Query
Public Function q(ByVal myStr As String) As SqlDataReader
myCMD.CommandText = myStr
Try
myDB.Open()
q = myCMD.ExecuteReader(Data.CommandBehavior.CloseConnection)
Catch ex As Exception
Err(ex.ToString)
End Try

End Function
Public Sub c(ByVal mySTR As String)
' COMMAND ONLY
Try
myCMD.Connection.Open()
myCMD.CommandText = mySTR
myCMD.ExecuteNonQuery()
myCMD.Connection.Close()
Catch ex As Exception
Err(ex.ToString)
End Try
End SubThe connection string is wrong.

Look here:

http://www.connectionstrings.com/|||Dim DBConnection As String = "Driver={SQL Server};Server=server;Database=icms;Uid=sqladmin;Pwd=*****;"

this is what i put in but still error =(

System.Data.Odbc.OdbcException: ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified at System.Data.Odbc.OdbcConnection.Open() at icms.DB.q(String mySTR) in C:\icms\DB.vb:line 30|||Is this your actual string, well apart from the password?|||Show the declarations for all objects. Your error message shows ODBC error messages, other code shows a SqlDataReader.

EXACTLY what is the Connection object declared as?

DataSet - Should be easy but I dont know how

I am working on a WebMatrix ASP project. I have a query that returns a System.Data.DataSet but I don't know how to assign the DataSet to a variable so that I can run some validation tests on it.

This is what I have so far...What I want to do is assign the dataset to a variable and to see if it is NULL or Not. I don't how? Any help would be awesome.

Sub Button1_Click(sender As Object, e As EventArgs)
?? = MyQueryMethod(txtPhone.Text)

Thanks,
Matt

Dim oDataSet as System.Data.DataSet = MyQueryMethod(txtPhone.Text)
If oDataSet = Nothing Then
 ' your dataset is jacked up
Else
 ' You got a dataset to work with
End If
' Release memory allocations to the variable.
oDataSet.Dispose()

|||Simply amazing! It's so easy once someone tells you how it is. Worked like a charm...Now I'm all jacked up!!!

Much Respect!!!!

Thanks,
Matt|||Well actually, it didn't quite do as I expected but I'm close. When I execute this code, I am expecting the dataset to have no records or one record, but either way the DataSet is never nothing (at least from my testing).

Maybe I'm just going about my problem the wrong way. I am trying to validate phone number so that I don't have someone enter a duplicate key in my database. So I run an SQL select statement that returns a dataset. The dataset should have one phone number or no phone number and this is how I'm trying to ward off any duplicate keys.

Is there an easier way to prevent a duplicate key entry? Sorry, I'm new at this stuff!!|||You can code as

If oDataSet.Tables(0).Rows.Count = 0 Then
 ' your have no records
Else
 'Response.Write(oDataSet.Tables(0).Rows(0)(0).ToString())
End If

HTH|||Perfect...thanks.

DataRow in a CLR Stored Procedure

I'm using Visual Studio 2005, C#, and SQL 2005. In Visual Studio, I've
created a Database project where I've written some simple CLR Stored
Procedures. I can deploy and call the simple CLR Stored Procedures from my
host WinForm application. This all works great.
I'd now like to write a CLR Stored Procedure with a parameter of type
System.Data.DataRow. Doing this compiles just fine, but when I attempt to
deploy, I get the following error:
Cannot find data type DataRow.
Any suggestions on what I might do to get a CLR Stored Procedure with a
DataRow parameter to compile AND deploy?
Thanks,
--
Randyexamnotes <randy1200@.newsgroups.nospam> wrote in
news:4A6B0CA3-733E-4E43-AA94-CD9D98B295C2@.microsoft.com:

> Any suggestions on what I might do to get a CLR Stored Procedure with a
> DataRow parameter to compile AND deploy?
>
Can not be done. Your CLR procedures can only have params of types that are
T-SQL compatible.
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb at develop dot com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||randy1200 (randy1200@.newsgroups.nospam) writes:
> I'm using Visual Studio 2005, C#, and SQL 2005. In Visual Studio, I've
> created a Database project where I've written some simple CLR Stored
> Procedures. I can deploy and call the simple CLR Stored Procedures from my
> host WinForm application. This all works great.
> I'd now like to write a CLR Stored Procedure with a parameter of type
> System.Data.DataRow. Doing this compiles just fine, but when I attempt to
> deploy, I get the following error:
> Cannot find data type DataRow.
> Any suggestions on what I might do to get a CLR Stored Procedure with a
> DataRow parameter to compile AND deploy?
You can't do that. A CLR stored procedure can only take parameters
that maps to types used in SQL Server, and DataRow is not such a type.
Keep in mind that even if the stored procedure is implemented in the CLR,
there is still is a CREATE PROCEDURE statement which looks just like
the CREATE PROCEDURE statement for a T-SQL procedure as far as the
parameter list is concerned.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||You could serialize the DataRow and use a VarChar or VarBinary parameter.
Dino Esposito covers serializing/deserializing ADO.NET objects in depth in
"Applied XML Programming for Microsoft .NET" See chapter 9 "ADO.NET XML Dat
a
Serialization." You could use an XML representation, however there is a
particularly interesting example of serializing/deserializing a DataTable an
d
the DataRows it contains using an efficient custom binary representation on
pages 424-428. The sample code is in C# and there isn't much code needed.
Hope this helps.
Jack Whitney
"randy1200" wrote:

> I'm using Visual Studio 2005, C#, and SQL 2005. In Visual Studio, I've
> created a Database project where I've written some simple CLR Stored
> Procedures. I can deploy and call the simple CLR Stored Procedures from my
> host WinForm application. This all works great.
> I'd now like to write a CLR Stored Procedure with a parameter of type
> System.Data.DataRow. Doing this compiles just fine, but when I attempt to
> deploy, I get the following error:
> Cannot find data type DataRow.
> Any suggestions on what I might do to get a CLR Stored Procedure with a
> DataRow parameter to compile AND deploy?
> Thanks,
> --
> Randy|||I have been playing around with Dino Esposito's example of custom binary
serialization of a DataTable and the DataRows it contains. (This is the
example that I mentioned in my earlier posting on this thread.) It is very
nicely implemented. The code to serialize a DataTable to a file and
deserialize the file back to a DataTable, including a worker class definitio
n
and comments is 78 lines of code. Dino provides a sample Windows applicatio
n
that connects to a Northwind database. You enter SQL in a textbox like
"SELECT * FROM [Order Details]" and the application gets the data from the
database into a DataTable, serializes the DataTable to a file, deserializes
the file back to a DataTable, and renders the DataTable in a DataGrid. He
actually serializes the DataTable using two methods and compares the output
for size efficiency. The two methods are ordinary .NET framework binary
serialization and his custom binary serialization. The custom binary
serialization produces much smaller output. The code was written for .NET
1.1, but I just converted it to .NET 2.0 and accessed SQL Server 2005 with n
o
hitches. If you are interested in pursuing this, you should definitely take
a look at this sample.
Note that to serialize just a DataRow, you would need to serialize some
properties of the containing DataTable object, minimally the column names an
d
types, in addition to the values in the DataRow. Dino's example demonstrate
s
this.
Hope this helps.
Jack Whitney
"Jack Whitney" wrote:
> You could serialize the DataRow and use a VarChar or VarBinary parameter.
> Dino Esposito covers serializing/deserializing ADO.NET objects in depth in
> "Applied XML Programming for Microsoft .NET" See chapter 9 "ADO.NET XML D
ata
> Serialization." You could use an XML representation, however there is a
> particularly interesting example of serializing/deserializing a DataTable
and
> the DataRows it contains using an efficient custom binary representation o
n
> pages 424-428. The sample code is in C# and there isn't much code needed.
> --
> Hope this helps.
> Jack Whitney
>
> "randy1200" wrote:
>

DataGridView/SQL Express - Updating record using seperate form

Hi,

I am currently using VC++/Cli 2005. In a project, I'm using a DataGridView control to show records from a SQL Express 2005 table. Instead of updating a specific item directly within DataGridView control, I would like to open a new form and allow user to update selected record/item within that form. The reason to update this way is conditionned by the fact that I have 3 levels of detail as following:

Level 0 Level 1 Level 2

1:N 1:N
Furniture --> Component --> Component

You all understand that update form of Level1 will/must include, as Level0 do, another DataGridView control to display/detail all related items issued from next level. This explains why I can't allow user to update Level 1 directly from DataGridView control.

I've searched in MSDN and even bought a few books on subject but unfortunately I found nothing on how to do it this way. All articles on DataGridView control were only showing how to update record directly from control.

My approach, I think, would be to transmit to my level1 updating form, as a single parameter, the selected DataRow object (or a brand new one if currently adding) issued from DataGridView and let user update it's content. When User finally leaves level0 update form, then I presume that DataGridView corresponding table would be automatically updated according to DataGridView's content.

What would be the proper way to do it? I would certainly appreciate to hear you view on this.

Also, what can I do if I want to refresh DataGridView's content when coming back from update form. Is it done automatically? I would certainly be sure that it reflects the reality, not only when I update it myself but also especially when other users could concurrently update same records?

Thanks in advance,

Stphane

Hi Stephane,

It sounds like you're looking for a Master/Detail implementation. There are a number of topics covering this in MSDN, start with the first three hits on this search.

Mike

|||

Hi Mike,

I followed links supplied. For what I could see, they are only showing how to put 2 DataGridView controls, master and detail, on same windows form and have data displayed accordingly. I already had a look at this technique. Unfortunately, this is not exactly what I'm looking for.

I'm definitely looking to be able to get/highlight a record (a row...) from a DataGridView control and then be able to perform any insert/update/delete operation, update its content using a new windows form which will be my "updating" form where user will be able to supply any required infos for that specific record/row instead of doing it directly from within DataGridView control.

Any hint or useful links?

Thanks again for your help,

Stphane

|||

Hi Stephane,

First off, remember this is the SQL Express forum and you're asking about a VS component so you might get a better answer in one of the VS forums. I'd suggest the .NET Data Access forum as a place to start, but language specific forums or Windows Forms forums may also be usefull.

That said, if you start with this topic about using two DataGridViews but split the two DGs between two forms, it should just be a matter of writing some code to react to an action on the Master form. Check out the documentation for this control, there are a number of Properties that allow you to determin the selection as well as some events (i.e. OnClick) that should let you respond to user action in the control.

I'm guessing that if you ask around in the VS forums someone will have already written a framework to do this.

Mike

|||

Hi Mike,

Thanks for clarification. I must admit that I get quite lost when trying to find appropriate forum... Any document or article listing all forums and their purposes? Would certainly help!

I followed your link but anything I can find on MSDN is an example using 2 datagridviews on same form. For my case, I just try to find a way to, first, select a SQL table record from datagridview in form1, open a new form, form2, where I will enable user to update record's content. Scenario I illustrated could also include a second datagridview in form2.

Now browsing through the many forums available, it seems that there is one discussing specific issues regarding datagridview:

MSDN Forums Windows Forms Windows Forms Data Controls and Databinding

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=7&SiteID=1

Hope I will be able to get any hint from there!

Thanks for your help,

Stphane

|||

Hi Stephanie,

You might be confused because many of the VS examples and Walkthroughs, including this one, do not walk you through using the VS UI to create the example, they use a 100% code based solution. The sample that I linked to provides code that programatically creates a form with two DataGridViews on it, attaches those DGVs to BindingSources and then hooks them up to the Northwind Database.

VS doesn't have many help topics that walk through specific tasks using the UI tools. I looked around on http://windowsforms.com and didn't really find anything that discusses how to use the UI to do this either, but you're welcome to take a look yourself as you may find some interesting material there.

There isn't a document listing all the fourms, but this page has them all. Each has a short description which hopefully describes it's use.

Mike

|||

Hi again Mike,

Thanks for links. I will keep looking around. I got my hand on a book which seems to give some hints about DGVs. Hope I will find something...

Stphane

DataGridView/SQL Express - Updating record using seperate form

Hi,

I am currently using VC++/Cli 2005. In a project, I'm using a DataGridView control to show records from a SQL Express 2005 table. Instead of updating a specific item directly within DataGridView control, I would like to open a new form and allow user to update selected record/item within that form. The reason to update this way is conditionned by the fact that I have 3 levels of detail as following:

Level 0 Level 1 Level 2

1:N 1:N
Furniture --> Component --> Component

You all understand that update form of Level1 will/must include, as Level0 do, another DataGridView control to display/detail all related items issued from next level. This explains why I can't allow user to update Level 1 directly from DataGridView control.

I've searched in MSDN and even bought a few books on subject but unfortunately I found nothing on how to do it this way. All articles on DataGridView control were only showing how to update record directly from control.

My approach, I think, would be to transmit to my level1 updating form, as a single parameter, the selected DataRow object (or a brand new one if currently adding) issued from DataGridView and let user update it's content. When User finally leaves level0 update form, then I presume that DataGridView corresponding table would be automatically updated according to DataGridView's content.

What would be the proper way to do it? I would certainly appreciate to hear you view on this.

Also, what can I do if I want to refresh DataGridView's content when coming back from update form. Is it done automatically? I would certainly be sure that it reflects the reality, not only when I update it myself but also especially when other users could concurrently update same records?

Thanks in advance,

Stphane

Hi Stephane,

It sounds like you're looking for a Master/Detail implementation. There are a number of topics covering this in MSDN, start with the first three hits on this search.

Mike

|||

Hi Mike,

I followed links supplied. For what I could see, they are only showing how to put 2 DataGridView controls, master and detail, on same windows form and have data displayed accordingly. I already had a look at this technique. Unfortunately, this is not exactly what I'm looking for.

I'm definitely looking to be able to get/highlight a record (a row...) from a DataGridView control and then be able to perform any insert/update/delete operation, update its content using a new windows form which will be my "updating" form where user will be able to supply any required infos for that specific record/row instead of doing it directly from within DataGridView control.

Any hint or useful links?

Thanks again for your help,

Stphane

|||

Hi Stephane,

First off, remember this is the SQL Express forum and you're asking about a VS component so you might get a better answer in one of the VS forums. I'd suggest the .NET Data Access forum as a place to start, but language specific forums or Windows Forms forums may also be usefull.

That said, if you start with this topic about using two DataGridViews but split the two DGs between two forms, it should just be a matter of writing some code to react to an action on the Master form. Check out the documentation for this control, there are a number of Properties that allow you to determin the selection as well as some events (i.e. OnClick) that should let you respond to user action in the control.

I'm guessing that if you ask around in the VS forums someone will have already written a framework to do this.

Mike

|||

Hi Mike,

Thanks for clarification. I must admit that I get quite lost when trying to find appropriate forum... Any document or article listing all forums and their purposes? Would certainly help!

I followed your link but anything I can find on MSDN is an example using 2 datagridviews on same form. For my case, I just try to find a way to, first, select a SQL table record from datagridview in form1, open a new form, form2, where I will enable user to update record's content. Scenario I illustrated could also include a second datagridview in form2.

Now browsing through the many forums available, it seems that there is one discussing specific issues regarding datagridview:

MSDN Forums Windows Forms Windows Forms Data Controls and Databinding

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=7&SiteID=1

Hope I will be able to get any hint from there!

Thanks for your help,

Stphane

|||

Hi Stephanie,

You might be confused because many of the VS examples and Walkthroughs, including this one, do not walk you through using the VS UI to create the example, they use a 100% code based solution. The sample that I linked to provides code that programatically creates a form with two DataGridViews on it, attaches those DGVs to BindingSources and then hooks them up to the Northwind Database.

VS doesn't have many help topics that walk through specific tasks using the UI tools. I looked around on http://windowsforms.com and didn't really find anything that discusses how to use the UI to do this either, but you're welcome to take a look yourself as you may find some interesting material there.

There isn't a document listing all the fourms, but this page has them all. Each has a short description which hopefully describes it's use.

Mike

|||

Hi again Mike,

Thanks for links. I will keep looking around. I got my hand on a book which seems to give some hints about DGVs. Hope I will find something...

Stphane

database....help?

i got new project again similar to my previous project but i never encountered doing it b4.

hmmmm the scenario is:

i have a drobdownlist for a office_name and a text box for "date"(2007/11/03). after choosing a name from a drop down and writing a date in the textbox when i press the button (ok) a datagrid will appear with its data.

"office_name" field is in table (TM0011) and "year.month.date" is in table (TT0001).

In the gridview.i need to show the time_in,time_out and the name of the employee.

"time in" and "time_out" fields are in the table (TT0001) and syain_name field is in table(TM0001).the only connection they have is the "syain_id".coz both table have "syain_id" as key.

I have two option in my code.. which do u think will most likely work? the inner join or union?

here is my code:

Protected Sub Button_date_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button_date.Click

GridView_info.Visible = True

'////////// option 1 //////////////////
Dim MyConn As New SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("MS_PKG01ConnectionString").ConnectionString)
MyConn.Open()
Dim stringQuery As String = "select TT0001.*,TM0001.*,TM0011.* from TT0001 inner join TM0001 on TT0001.syain_id = TM0001.syain_id " '+ Request.requerystring("syain_id")
Dim sqldataadapter As New SqlClient.SqlDataAdapter(stringQuery, MyConn)

Dim ds As New DataSet()
sqldataadapter.Fill(ds, "TT0001")
GridView_info.DataSourceID = ""
GridView_info.DataSource = ds
GridView_info.DataBind()
MyConn.Close()

'/////////option 2 ///////////////////
'Dim MyConn As New SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("MS_PKG01ConnectionString").ConnectionString)
'MyConn.Open()

'Dim stringQuery As String = " select TT0001.syain_id,TT0001.year,TT0001.month,TT0001.day,TT0001.in_hh,TT0001in_mi,TT0001out_hh,TT0001out_mi From TT0001 Where TT0001.syain_id = TM0001.syain_id and TT0001.syain_id = " + Session("syain_id") + " Union Select TM0001.syain_id,TM0001.syain_name From TM0001 Where TM0001.syain_name = TTM0001.syain_name and TM0001.syain_id = " + Session("syain_id") + " "
'Dim stringQuery2 As String = "SELECT * from tempo_db"

'Dim SQLcommand1 As New SqlClient.SqlCommand("Drop table tempo_db", MyConn)
'SQLcommand1.ExecuteNonQuery()

'Dim SQLcommand2 As New SqlClient.SqlCommand("Create table tempo_db ( year char(4), month(2), day char(2), in_hh(2),in_mi(2), out_hh(2),out_mi(2) )", MyConn)
'SQLcommand2.ExecuteNonQuery()

'Dim sqldataadapter As New SqlClient.SqlDataAdapter(stringQuery, MyConn)
'Dim ds As New DataSet()
'Dim foundrow, temprow As DataRow

'Dim ds2 As New DataSet
'Dim temp_data_table As New DataTable
'sqldataadapter.Fill(ds, "TT0001")

'Dim sqldataadapter2 As New SqlClient.SqlDataAdapter(stringQuery2, MyConn)
'sqldataadapter2.Fill(ds2, "tempo_db")

'Dim date_ctr As Integer
'date_ctr = 1
'GridView_info.DataSourceID = ""
'GridView_info.DataSource = ds
'GridView_info.DataBind()
'MyConn.Close()

End Sub

thank you...

From your description I think JOIN is the proper solution. BTW, you may need Parameterized Query to prevent SQL Injection.

Pose again if you have any other problem on this.|||

hi thank you... the problem was just solved awhile ago... but thanks...

btw, are u familiar with hyperlink? coz i dont know how to put hyperlink ...

coz all the data's being shown in my gridview are generated automatically...

how can i put hyperlink on one of the columns if its not coded in html?

Database unususally large

I've been running Project Server 2003 for close to a year now. Currently the
database in SQL Server is at 16GB. I find it hard to believe that this is
correct, given that there are only around 6 projects stored in the database
and I know that much data has not been accumulated.
One thing I had to resort to early on was truncating the logs each night
because it would eventually grow so large (usually with a w or two) that
it would consume all disk space.
I'm not sure where to start here. I'm running this same App/DB in other
environments and this is not an issue
I did run dbcc checkdb and no errors or inconsistencies were reported.
Does anyone have a suggestion(s) on how to diagnose this issue and hopefully
fix the problem of this abnormal growth?You should be backing up your transaction logs on a regular basis. This
will also manage the growth of your log. Of course, you should also do full
backups of the DB itself. If you can take an outage from time to time, you
could re-index. All of these can be managed with a Database Maintenance
Plan, which you can create with Enterprise Manager.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Troy Jerkins" <tjerkins@.alltel.net> wrote in message
news:%23T4OVz4ZFHA.4040@.TK2MSFTNGP14.phx.gbl...
I've been running Project Server 2003 for close to a year now. Currently the
database in SQL Server is at 16GB. I find it hard to believe that this is
correct, given that there are only around 6 projects stored in the database
and I know that much data has not been accumulated.
One thing I had to resort to early on was truncating the logs each night
because it would eventually grow so large (usually with a w or two) that
it would consume all disk space.
I'm not sure where to start here. I'm running this same App/DB in other
environments and this is not an issue
I did run dbcc checkdb and no errors or inconsistencies were reported.
Does anyone have a suggestion(s) on how to diagnose this issue and hopefully
fix the problem of this abnormal growth?|||Perhaps there just is that much data. If you have documentation regarding
the database model, then identify any tables related to transaction history.
For example, perhaps it is retaining a copy of every record that is
modified.
"Troy Jerkins" <tjerkins@.alltel.net> wrote in message
news:%23T4OVz4ZFHA.4040@.TK2MSFTNGP14.phx.gbl...
> I've been running Project Server 2003 for close to a year now. Currently
the
> database in SQL Server is at 16GB. I find it hard to believe that this is
> correct, given that there are only around 6 projects stored in the
database
> and I know that much data has not been accumulated.
> One thing I had to resort to early on was truncating the logs each night
> because it would eventually grow so large (usually with a w or two)
that
> it would consume all disk space.
> I'm not sure where to start here. I'm running this same App/DB in other
> environments and this is not an issue
> I did run dbcc checkdb and no errors or inconsistencies were reported.
> Does anyone have a suggestion(s) on how to diagnose this issue and
hopefully
> fix the problem of this abnormal growth?
>|||Troy Jerkins wrote:
> I've been running Project Server 2003 for close to a year now.
> Currently the database in SQL Server is at 16GB. I find it hard to
> believe that this is correct, given that there are only around 6
> projects stored in the database and I know that much data has not
> been accumulated.
> One thing I had to resort to early on was truncating the logs each
> night because it would eventually grow so large (usually with a w
> or two) that it would consume all disk space.
> I'm not sure where to start here. I'm running this same App/DB in
> other environments and this is not an issue
> I did run dbcc checkdb and no errors or inconsistencies were reported.
> Does anyone have a suggestion(s) on how to diagnose this issue and
> hopefully fix the problem of this abnormal growth?
If you are not backing up your transaction logs on a regular basis, then
you should run the database in the Simple Recovery model to avoid log
file growth.
In your case, you were probably running in Full Recovery, which means
the log files continue to grow over time. It's possible most of that
space is taken up by the log files. Truncating the log file does not
recover disk space. You need to use DBCC SHRINKFILE for that
(off-hours).
Run sp_spaceused and sp_helpfile on the database and see how much space
is used and how much is unallocated space.
David Gugick
Quest Software
www.imceda.com
www.quest.com