2 Replies - 3693 Views - Last Post: 01 November 2009 - 02:46 PM

#1 vibha9999   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 18
  • Joined: 28-October 09

how to make a primary key autoincrement

Post icon  Posted 29 October 2009 - 11:26 AM

Hi guys,

I am working on project which is connected to access DB.I am just concerned with coding it in vb.net.The db is already created by someone else.

The problem i have is
1)there is a primarykey in db which is "PAGENO" and it is set to text now i need it to autoincrement how do i do it.

2)when i delete a record in delete button click considering the pageno how do i rearrange the pageno field so that all the other code doesn't mess up
here's what i did
   Private Sub Btndelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Btndelete.Click

		Dim cn As New OleDb.OleDbConnection
		cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\administrator\My Documents\Visual Studio 2005\Projects\sample.mdb;Persist Security Info=False"

		Dim cmd As New OleDb.OleDbCommand
		Dim a As Integer
		a = txtpage.Text
		
		cmd.CommandText = "delete ti.[ti001],ti.[pageno],ti.[ti018],ti.[ti020],ti.[ti021],ti.[ti031],ti.[ti043],ti.[ti055],ti.[ti067],ti.[ti079],ti.[ti091],ti.[ti103],ti.[ti115],ti.[ti127],ti.[ti139],ti.[ti151],ti.[ti163],ti.[ti175],ti.[ti187],ti.[ti199],ti.[ti211],ti.[ti223] from ti where ti.pageno = '" & txtpage.Text & "'"
		cmd.CommandType = CommandType.Text
		cmd.Connection = cn
		cn.Open()
		cmd.ExecuteNonQuery()
		Dim selectquery As String
		Dim ds As New DataSet

		Dim cm As New OleDb.OleDbCommand
			txtpage.Text = a - 1
		selectquery = "select TI.[ti021], ti.[ti001],ti.[ti031], ti.[ti043], ti.[ti055], ti.[ti067], ti.[ti079], ti.[ti091], ti.[ti103], ti.[ti115], ti.[ti127],ti.[ti139], ti.[ti151], ti.[ti163], ti.[ti175], ti.[ti187], ti.[ti199], ti.[ti211], ti.[ti223] from TI Where TI.[pageno]='" & txtpage.Text & "'"

		Dim da As New OleDb.OleDbDataAdapter(selectquery, cn)
		' cn.Open()
		da.Fill(ds, "TI")
		If ds.Tables(0).Rows.Count > 0 Then
			TxtilID.Text = ds.Tables("TI").Rows(0).Item(0).ToString()
			TextBox1.Text = ds.Tables("TI").Rows(0).Item(1).ToString()
			txtepa1.Text = ds.Tables("TI").Rows(0).Item(2).ToString()
			txtepa2.Text = ds.Tables("TI").Rows(0).Item(3).ToString()
			txtepa3.Text = ds.Tables("TI").Rows(0).Item(4).ToString()
			txtepa4.Text = ds.Tables("TI").Rows(0).Item(5).ToString()
			txtepa5.Text = ds.Tables("TI").Rows(0).Item(6).ToString()
			txtepa6.Text = ds.Tables("TI").Rows(0).Item(7).ToString()
			txtepa7.Text = ds.Tables("TI").Rows(0).Item(8).ToString()
			txtepa8.Text = ds.Tables("TI").Rows(0).Item(9).ToString()
			txtIL1.Text = ds.Tables("TI").Rows(0).Item(10).ToString()
			txtIL2.Text = ds.Tables("TI").Rows(0).Item(11).ToString()
			txtIL3.Text = ds.Tables("TI").Rows(0).Item(12).ToString()
			txtIL4.Text = ds.Tables("TI").Rows(0).Item(13).ToString()
			txtIL5.Text = ds.Tables("TI").Rows(0).Item(14).ToString()
			txtIL6.Text = ds.Tables("TI").Rows(0).Item(15).ToString()
			txtIL7.Text = ds.Tables("TI").Rows(0).Item(16).ToString()
			txtIL8.Text = ds.Tables("TI").Rows(0).Item(17).ToString()
			ComboBox10.SelectedItem = ds.Tables("TI").Rows(0).Item(18).ToString()
		Else

			MsgBox("no more records", MsgBoxStyle.Information, "No Records")
		End If
		cn.Close()
	End Sub



if this works fine by setting the txtpage.text to a integer variable 'a' before deleting the record ,button previous and next doesn't work :(

  Private Sub Btnnext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Btnnext.Click
		Dim cn As New OleDb.OleDbConnection
		Dim selectquery As String
		cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\administrator\My Documents\Visual Studio 2005\Projects\sample.mdb;Persist Security Info=False"
		Dim ds As New DataSet

		Dim cm As New OleDb.OleDbCommand
	
		selectquery = "select TI.[ti021], ti.[ti001],ti.[ti031], ti.[ti043], ti.[ti055], ti.[ti067], ti.[ti079], ti.[ti091], ti.[ti103], ti.[ti115], ti.[ti127],ti.[ti139], ti.[ti151], ti.[ti163], ti.[ti175], ti.[ti187], ti.[ti199], ti.[ti211], ti.[ti223] from TI Where TI.[pageno]='" & (txtpage.Text) + 1 & "'"

		Dim da As New OleDb.OleDbDataAdapter(selectquery, cn)
		cn.Open()
		da.Fill(ds, "TI")
		If ds.Tables(0).Rows.Count > 0 Then
			txtpage.Text = txtpage.Text + 1
			TxtilID.Text = ds.Tables("TI").Rows(0).Item(0).ToString()
			TextBox1.Text = ds.Tables("TI").Rows(0).Item(1).ToString()
			txtepa1.Text = ds.Tables("TI").Rows(0).Item(2).ToString()
			txtepa2.Text = ds.Tables("TI").Rows(0).Item(3).ToString()
			txtepa3.Text = ds.Tables("TI").Rows(0).Item(4).ToString()
			txtepa4.Text = ds.Tables("TI").Rows(0).Item(5).ToString()
			txtepa5.Text = ds.Tables("TI").Rows(0).Item(6).ToString()
			txtepa6.Text = ds.Tables("TI").Rows(0).Item(7).ToString()
			txtepa7.Text = ds.Tables("TI").Rows(0).Item(8).ToString()
			txtepa8.Text = ds.Tables("TI").Rows(0).Item(9).ToString()
			txtIL1.Text = ds.Tables("TI").Rows(0).Item(10).ToString()
			txtIL2.Text = ds.Tables("TI").Rows(0).Item(11).ToString()
			txtIL3.Text = ds.Tables("TI").Rows(0).Item(12).ToString()
			txtIL4.Text = ds.Tables("TI").Rows(0).Item(13).ToString()
			txtIL5.Text = ds.Tables("TI").Rows(0).Item(14).ToString()
			txtIL6.Text = ds.Tables("TI").Rows(0).Item(15).ToString()
			txtIL7.Text = ds.Tables("TI").Rows(0).Item(16).ToString()
			txtIL8.Text = ds.Tables("TI").Rows(0).Item(17).ToString()
			ComboBox10.SelectedItem = ds.Tables("TI").Rows(0).Item(18).ToString()
		ElseIf ds.Tables(0).Rows.Count = 0 Then
			MsgBox("Your on last record", MsgBoxStyle.Information, "LAST RECORD")
		End If
		cn.Close()
	End Sub


Private Sub Btnprvs_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Btnprvs.Click
		Dim cn As New OleDb.OleDbConnection
		Dim selectquery As String
		cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\administrator\My Documents\Visual Studio 2005\Projects\sample.mdb;Persist Security Info=False"
		Dim ds As New DataSet

		Dim cm As New OleDb.OleDbCommand

		selectquery = "select TI.[ti021], ti.[ti001],ti.[ti031], ti.[ti043], ti.[ti055], ti.[ti067], ti.[ti079], ti.[ti091], ti.[ti103], ti.[ti115], ti.[ti127],ti.[ti139], ti.[ti151], ti.[ti163], ti.[ti175], ti.[ti187], ti.[ti199], ti.[ti211], ti.[ti223] from TI Where TI.[pageno]='" & (txtpage.Text) - 1 & "'"

		Dim da As New OleDb.OleDbDataAdapter(selectquery, cn)
		cn.Open()
		da.Fill(ds, "TI")
		If ds.Tables(0).Rows.Count > 0 Then
			txtpage.Text = txtpage.Text - 1
			TxtilID.Text = ds.Tables("TI").Rows(0).Item(0).ToString()
			TextBox1.Text = ds.Tables("TI").Rows(0).Item(1).ToString()
			txtepa1.Text = ds.Tables("TI").Rows(0).Item(2).ToString()
			txtepa2.Text = ds.Tables("TI").Rows(0).Item(3).ToString()
			txtepa3.Text = ds.Tables("TI").Rows(0).Item(4).ToString()
			txtepa4.Text = ds.Tables("TI").Rows(0).Item(5).ToString()
			txtepa5.Text = ds.Tables("TI").Rows(0).Item(6).ToString()
			txtepa6.Text = ds.Tables("TI").Rows(0).Item(7).ToString()
			txtepa7.Text = ds.Tables("TI").Rows(0).Item(8).ToString()
			txtepa8.Text = ds.Tables("TI").Rows(0).Item(9).ToString()
			txtIL1.Text = ds.Tables("TI").Rows(0).Item(10).ToString()
			txtIL2.Text = ds.Tables("TI").Rows(0).Item(11).ToString()
			txtIL3.Text = ds.Tables("TI").Rows(0).Item(12).ToString()
			txtIL4.Text = ds.Tables("TI").Rows(0).Item(13).ToString()
			txtIL5.Text = ds.Tables("TI").Rows(0).Item(14).ToString()
			txtIL6.Text = ds.Tables("TI").Rows(0).Item(15).ToString()
			txtIL7.Text = ds.Tables("TI").Rows(0).Item(16).ToString()
			txtIL8.Text = ds.Tables("TI").Rows(0).Item(17).ToString()
			ComboBox10.SelectedItem = ds.Tables("TI").Rows(0).Item(18).ToString()
		ElseIf ds.Tables(0).Rows.Count = 0 Then
			MsgBox("Your on first record", MsgBoxStyle.Information, "FIRST RECORD")
		End If
		cn.Close()
	End Sub



please help me with it.Thanks in advance.

This post has been edited by vibha9999: 29 October 2009 - 11:50 AM


Is This A Good Question/Topic? 0
  • +

Replies To: how to make a primary key autoincrement

#2 jwwicks   User is offline

  • D.I.C Head
  • member icon

Reputation: 24
  • View blog
  • Posts: 162
  • Joined: 31-July 08

Re: how to make a primary key autoincrement

Posted 29 October 2009 - 08:04 PM

Hello,

View Postvibha9999, on 29 Oct, 2009 - 10:26 AM, said:

Hi guys,

I am working on project which is connected to access DB.I am just concerned with coding it in vb.net.The db is already created by someone else.

The problem i have is
1)there is a primarykey in db which is "PAGENO" and it is set to text now i need it to autoincrement how do i do it.

2)when i delete a record in delete button click considering the pageno how do i rearrange the pageno field so that all the other code doesn't mess up
here's what i did

please help me with it.Thanks in advance.


You can't autoincrement a text field that I'm aware of...

So the values in PageNo convert to something that can be incremented ie CInt(ti.[PageNo]) yields a number??

if so then a select MAX(CInt(ti.[PageNo])) + 1 as nextVal

Jw
Jw
Was This Post Helpful? 0
  • +
  • -

#3 hcibrao   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 01-November 09

Re: how to make a primary key autoincrement

Posted 01 November 2009 - 02:46 PM

Hi vibha9999

You say that the database was created by someone else. What are the chances of modifying it?

If the database is set up properly, checking for referential integrity (relationships) and using identity columns (auto increment), all of that work you're having right now would be reduced to zero since the DBMS (I'm guessing it is MSSQL) would be in charge of doing that for you.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1