• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 125
  • Last Modified:

How do I know if a column in a datatable is a required field or cannot be null?

I have a datatable with so many fields. And most of them are required. I want to loop through all the fields and if the field is required, I will message the user. This will prevent me from hardcoding the field check, and less coding in my part.
0
stylelyf
Asked:
stylelyf
  • 5
  • 4
  • 2
1 Solution
 
Ronak PatelCommented:
Hi stylelyf,

You can check the database column is nullable or not by below SQL query.

SELECT name, is_nullable
FROM sys.columns
WHERE object_id = OBJECT_ID('tablename')

Open in new window


You have to replace "tablename" with your table name.
The "is_nullable" is a boolean column, so it will return 0 or 1 where 0 means the column is required / can't be null.

This query will return all the columns present in the table. You can loop through the columns by checking "is_nullable" value.

Hope this helps.

Regards,
Ronak
0
 
käµfm³d 👽Commented:
Check the AllowDbNull property of the column.
0
 
stylelyfAuthor Commented:
Thanks. But let me add more information.

Private Function GetData(ByVal spLoader As String)

		connection = New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("mainconnectionstring").ConnectionString)
		If connection.State = ConnectionState.Open Then
			connection.Close()
		End If

		connection.Open()

		command = New SqlCommand(spLoader, connection)
		command.CommandType = CommandType.StoredProcedure
		command.CommandTimeout = 0
		adapter = New SqlDataAdapter(command)
		Dim cb As New SqlCommandBuilder(adapter)
		adapter.UpdateCommand = cb.GetUpdateCommand(True)
		adapter.DeleteCommand = cb.GetDeleteCommand(True)
		adapter.InsertCommand = cb.GetInsertCommand(True)

		datatable = New DataTable()
		adapter.Fill(datatable)


		' Refresh grid.
		BindingSource1.DataSource = datatable
		DataGridView.DataSource = BindingSource1.DataSource
		Try
			DataGridView.Columns("recid").Visible = False
		Catch ex As Exception
			'
		End Try


		For Each column As DataColumn In DirectCast(DataGridView.DataSource, DataTable).Columns
			If Not column.AllowDBNull Then
				DataGridView.Columns(column.ColumnName).DefaultCellStyle.BackColor = Color.Red
			End If
		Next

	End Function

Open in new window


The parameter spLoader is a stored procedure, passed by the caller. So I will not know how many fields, or what are the fields.

The last loop

For Each column As DataColumn In DirectCast(DataGridView.DataSource, DataTable).Columns
			If Not column.AllowDBNull Then
				DataGridView.Columns(column.ColumnName).DefaultCellStyle.BackColor = Color.Red
			End If
		Next

Open in new window



is what I tried but I was wrong, allowdbnull was meant for something else.

Is there a way I can loop these unknown datatable fields and check if each is required?
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
käµfm³d 👽Commented:
What do you mean by "required"?
0
 
stylelyfAuthor Commented:
It means it cannot be null.

This means if the field cannot be null, I'm going to colorize that column to red to let the user know its a required column field. Because given a hundred fields, its easier to spot the field by the user even if the column is on the last ordinal, and the user wants to edit on the datagridview on a tabular manner, like excel.
0
 
Ronak PatelCommented:
Hi stylelyf,

Sorry I misunderstood your requirement.

You have to fill schema for getting schema information for the data table.
Added one line of code: adapter.FillSchema(datatable, SchemaType.Mapped)

Please refer below code:

Private Function GetData(ByVal spLoader As String)

		connection = New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("mainconnectionstring").ConnectionString)
		If connection.State = ConnectionState.Open Then
			connection.Close()
		End If

		connection.Open()

		command = New SqlCommand(spLoader, connection)
		command.CommandType = CommandType.StoredProcedure
		command.CommandTimeout = 0
		adapter = New SqlDataAdapter(command)
		Dim cb As New SqlCommandBuilder(adapter)
		adapter.UpdateCommand = cb.GetUpdateCommand(True)
		adapter.DeleteCommand = cb.GetDeleteCommand(True)
		adapter.InsertCommand = cb.GetInsertCommand(True)

		datatable = New DataTable()
		adapter.FillSchema(datatable, SchemaType.Mapped)
		adapter.Fill(datatable)

		' Refresh grid.
		BindingSource1.DataSource = datatable
		DataGridView.DataSource = BindingSource1.DataSource
		Try
			DataGridView.Columns("recid").Visible = False
		Catch ex As Exception
			'
		End Try


		For Each column As DataColumn In DirectCast(DataGridView.DataSource, DataTable).Columns
			If Not column.AllowDBNull Then
				DataGridView.Columns(column.ColumnName).DefaultCellStyle.BackColor = Color.Red
			End If
		Next

	End Function

Open in new window


Hope this helps.

Regards,
Ronak
0
 
stylelyfAuthor Commented:
Thanks, let me check and inform you when I get back next day.
0
 
stylelyfAuthor Commented:
I added the FillSchema() and tried a few code to dig the required property.

I added

Dim schema As DataTable = connection.GetSchema()
		For Each row As DataRow In schema.Rows
			For Each col As DataColumn In schema.Columns
				'col. where?
			Next
		Next

Open in new window



But I can't find it nowhere. How do I get it? (by using fillschema?)
0
 
Ronak PatelCommented:
Hi stylelyf,

Just refer the code block in my previous comment.

You just need to Fill Schema when you Fill the DataTable from DataAdapter, then the rest of your code will work fine.

To check the nullable column just check column.AllowDBNull, for columns which are not nullable it will be false.


Hope this helps.

Regards,
Ronak
0
 
stylelyfAuthor Commented:
Thanks! Saves me a lot of overtime hours which I could have spent more productively elsewhere.
0
 
Ronak PatelCommented:
You're welcome !
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 5
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now