Add a Record to a Database? Thanks.

microsoft.public.sqlserver.programming

30 Jun 2005 6:15 PM

Shapper

Hello,

I need to create a new record in an Access database.
The database has 3 fields:
[id] (autonumber)
[title] (string)
[text] (memo)
[publication_date] (DateTime)

When I create the record how should I create the [id] value?

Does the database insert it automatically?

Do I need to create a random number?
How can I do it to not repeat the ids values already in the table?

My code is not working but it seems fine to me:

Dim connectionString As String =
System.Configuration.ConfigurationSettings.AppSettings("connectionString")
Dim dbConnection As System.Data.IDbConnection = New
System.Data.OleDb.OleDbConnection(connectionString)

Dim queryString As String = "INSERT INTO [t_news] ([title], [text],
[publication_date]) VALUES (@title, @text, @publication_date)"
Dim dbCommand As System.Data.IDbCommand = New
System.Data.OleDb.OleDbCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection

Dim dbParam_title As System.Data.IDataParameter = New
System.Data.OleDb.OleDbParameter
dbParam_title.ParameterName = "@title"
dbParam_title.Value = title
dbParam_title.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_title)

Dim dbParam_text As System.Data.IDataParameter = New
System.Data.OleDb.OleDbParameter
dbParam_text.ParameterName = "@text"
dbParam_text.Value = text
dbParam_text.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_text)

Dim dbParam_publication_date As System.Data.IDataParameter = New
System.Data.OleDb.OleDbParameter
dbParam_publication_date.ParameterName = "@publication_date"
dbParam_publication_date.Value = DateTime.Now
dbParam_publication_date.DbType = System.Data.DbType.DateTime
dbCommand.Parameters.Add(dbParam_publication_date)

Dim rowsAffected As Integer = 0
dbConnection.Open
Try
rowsAffected = dbCommand.ExecuteNonQuery
Finally
dbConnection.Close
End Try

Thanks,
Miguel

30 Jun 2005 6:39 PM

Alejandro Mesa

> Does the database insert it automatically?

yes, do not referentiate it in the insert statement.

> Do I need to create a random number?

> How can I do it to not repeat the ids values already in the table?

set this column as the pk or create a unique constraint. you can also use a
datatype that accomadate a bigger range, like bigint or numeric.

AMB

Show quote

"Shapper" wrote:

> Hello,
>
> I need to create a new record in an Access database.
> The database has 3 fields:
> [id] (autonumber)
> [title] (string)
> [text] (memo)
> [publication_date] (DateTime)
>
> When I create the record how should I create the [id] value?
>
> Does the database insert it automatically?
>
> Do I need to create a random number?
> How can I do it to not repeat the ids values already in the table?
>
> My code is not working but it seems fine to me:
>
> Dim connectionString As String =
> System.Configuration.ConfigurationSettings.AppSettings("connectionString")
> Dim dbConnection As System.Data.IDbConnection = New
> System.Data.OleDb.OleDbConnection(connectionString)
>
> Dim queryString As String = "INSERT INTO [t_news] ([title], [text],
> [publication_date]) VALUES (@title, @text, @publication_date)"
> Dim dbCommand As System.Data.IDbCommand = New
> System.Data.OleDb.OleDbCommand
> dbCommand.CommandText = queryString
> dbCommand.Connection = dbConnection
>
> Dim dbParam_title As System.Data.IDataParameter = New
> System.Data.OleDb.OleDbParameter
> dbParam_title.ParameterName = "@title"
> dbParam_title.Value = title
> dbParam_title.DbType = System.Data.DbType.String
> dbCommand.Parameters.Add(dbParam_title)
>
> Dim dbParam_text As System.Data.IDataParameter = New
> System.Data.OleDb.OleDbParameter
> dbParam_text.ParameterName = "@text"
> dbParam_text.Value = text
> dbParam_text.DbType = System.Data.DbType.String
> dbCommand.Parameters.Add(dbParam_text)
>
> Dim dbParam_publication_date As System.Data.IDataParameter = New
> System.Data.OleDb.OleDbParameter
> dbParam_publication_date.ParameterName = "@publication_date"
> dbParam_publication_date.Value = DateTime.Now
> dbParam_publication_date.DbType = System.Data.DbType.DateTime
> dbCommand.Parameters.Add(dbParam_publication_date)
>
> Dim rowsAffected As Integer = 0
> dbConnection.Open
> Try
> rowsAffected = dbCommand.ExecuteNonQuery
> Finally
> dbConnection.Close
> End Try
>
> Thanks,
> Miguel
>
>

30 Jun 2005 6:42 PM

In MS Access, autonumber is an option for a numeric column.

Show quote

"Shapper" <mdmoura*NOSPAM*@gmail.*DELETE2SEND*com> wrote in message
news:uxGFp%23ZfFHA.3692@TK2MSFTNGP09.phx.gbl...
> Hello,
>
> I need to create a new record in an Access database.
> The database has 3 fields:
> [id] (autonumber)
> [title] (string)
> [text] (memo)
> [publication_date] (DateTime)
>
> When I create the record how should I create the [id] value?
>
> Does the database insert it automatically?
>
> Do I need to create a random number?
> How can I do it to not repeat the ids values already in the table?
>
> My code is not working but it seems fine to me:
>
> Dim connectionString As String =
> System.Configuration.ConfigurationSettings.AppSettings("connectionString")
> Dim dbConnection As System.Data.IDbConnection = New
> System.Data.OleDb.OleDbConnection(connectionString)
>
> Dim queryString As String = "INSERT INTO [t_news] ([title], [text],
> [publication_date]) VALUES (@title, @text, @publication_date)"
> Dim dbCommand As System.Data.IDbCommand = New
> System.Data.OleDb.OleDbCommand
> dbCommand.CommandText = queryString
> dbCommand.Connection = dbConnection
>
> Dim dbParam_title As System.Data.IDataParameter = New
> System.Data.OleDb.OleDbParameter
> dbParam_title.ParameterName = "@title"
> dbParam_title.Value = title
> dbParam_title.DbType = System.Data.DbType.String
> dbCommand.Parameters.Add(dbParam_title)
>
> Dim dbParam_text As System.Data.IDataParameter = New
> System.Data.OleDb.OleDbParameter
> dbParam_text.ParameterName = "@text"
> dbParam_text.Value = text
> dbParam_text.DbType = System.Data.DbType.String
> dbCommand.Parameters.Add(dbParam_text)
>
> Dim dbParam_publication_date As System.Data.IDataParameter = New
> System.Data.OleDb.OleDbParameter
> dbParam_publication_date.ParameterName = "@publication_date"
> dbParam_publication_date.Value = DateTime.Now
> dbParam_publication_date.DbType = System.Data.DbType.DateTime
> dbCommand.Parameters.Add(dbParam_publication_date)
>
> Dim rowsAffected As Integer = 0
> dbConnection.Open
> Try
> rowsAffected = dbCommand.ExecuteNonQuery
> Finally
> dbConnection.Close
> End Try
>
> Thanks,
> Miguel
>