School Assignment? Project Due Tomorrow? Chat LIVE With A Programming Expert!
You're Browsing As A Guest! Register Now...
Become an Expert!

Join 353,809 Programmers for FREE! Get instant access to thousands of experts, tutorials, code snippets, and more! There are 3,408 people online right now.Registration is fast and FREE... Join Now!



how to make a primary key autoincrement

Page 1 of 1

how to make a primary key autoincrement Rate Topic: -----

#1 vibha9999  Icon User is offline

  • New D.I.C Head
  • Pip

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


Dream Kudos: 0

Post icon  Posted 29 October 2009 - 10: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 - 10:50 AM

Was This Post Helpful? 0
  • +
  • -

#2 jwwicks  Icon User is offline

  • D.I.C Head
  • Icon

Reputation: 22
  • View blog
  • Posts: 151
  • Joined: 31-July 08


Dream Kudos: 250

Re: how to make a primary key autoincrement

Posted 29 October 2009 - 07: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  Icon User is offline

  • New D.I.C Head
  • Pip

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


Dream Kudos: 0

Re: how to make a primary key autoincrement

Posted 01 November 2009 - 01: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

Fast Reply

  

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users



Live Help!

Be Social

Dream.In.Code RSS Feed Dream.In.Code LinkedIn Group Follow Us On Twitter Fan Us On Facebook

Tutorials

Programming

Web Development

Reference Sheets

Code Snippets

DIC Chatroom

Bye Bye Ads

Monthly Drawing

Thumb Drive

Top Contributors

Top 10 Kudos This Month

Programming Help

Web Development



What's Here?

  • Members: 353,809
  • Replies: 956,662
  • Topics: 161,530
  • Snippets: 4,968
  • Tutorials: 1,307
  • Total Online: 3,408
  • Members: 104
  • Guests: 3,304