Input string not in correct format

  • Thread starter Thread starter nsj
  • Start date Start date
N

nsj

I am working with a web applicaction that accesses a SQL Server database. I
need the value of the 'id' column of the last inserted row in the table
'PERSON'. The SQL statement for that purpose is: "SELECT
IDENT_CURRENT('PERSON')"; . I need to insert this value in another table
called 'ADDRESS'. So I did the following:

System.Data.IDbConnection dbConnection = new
System.Data.SqlClient.SqlConnection(connectionString);
string queryString = "INSERT INTO [ADDRESS] ([id]) VALUES (@id)";
System.Data.IDbCommand dbCommand = new System.Data.SqlClient.SqlCommand();
dbCommand.CommandText = queryString;
dbCommand.Connection = dbConnection;

System.Data.IDataParameter dbParam_id = new
System.Data.SqlClient.SqlParameter();
dbParam_id.ParameterName = "@id";
dbParam_id.Value = "SELECT IDENT_CURRENT('PERSON')";
dbParam_id.DbType = System.Data.DbType.Int64;
dbCommand.Parameters.Add(dbParam_id);

I get the following error:
Exception Details: System.FormatException: Input string was not in a correct
format.

PERSON.id datatype is "bigint" in Sql server.
ADDRESS.id datatype is "bigint" in Sql server.
 
Hi,

I havent Test your code but I believe the error is here:

dbParam_id.ParameterName = "@id";
dbParam_id.Value = "SELECT IDENT_CURRENT('PERSON')";
dbParam_id.DbType = System.Data.DbType.Int64;

The value is not a int64 !!! , what make you think that it will execute the
query and store the value?

The best way of do this is using a SP !

or a better query :)

If you need the query let me know, I have to run now :(

Cheers,
 
I still dont get it right. Can you show me the query?

Thnaks
NJ

Ignacio Machin ( .NET/ C# MVP ) said:
Hi,

I havent Test your code but I believe the error is here:

dbParam_id.ParameterName = "@id";
dbParam_id.Value = "SELECT IDENT_CURRENT('PERSON')";
dbParam_id.DbType = System.Data.DbType.Int64;

The value is not a int64 !!! , what make you think that it will execute the
query and store the value?

The best way of do this is using a SP !

or a better query :)

If you need the query let me know, I have to run now :(

Cheers,

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation


nsj said:
I am working with a web applicaction that accesses a SQL Server
Click to expand...
database.
I
need the value of the 'id' column of the last inserted row in the table
'PERSON'. The SQL statement for that purpose is: "SELECT
IDENT_CURRENT('PERSON')"; . I need to insert this value in another table
called 'ADDRESS'. So I did the following:

System.Data.IDbConnection dbConnection = new
System.Data.SqlClient.SqlConnection(connectionString);
string queryString = "INSERT INTO [ADDRESS] ([id]) VALUES (@id)";
System.Data.IDbCommand dbCommand = new System.Data.SqlClient.SqlCommand();
dbCommand.CommandText = queryString;
dbCommand.Connection = dbConnection;

System.Data.IDataParameter dbParam_id = new
System.Data.SqlClient.SqlParameter();
dbParam_id.ParameterName = "@id";
dbParam_id.Value = "SELECT IDENT_CURRENT('PERSON')";
dbParam_id.DbType = System.Data.DbType.Int64;
dbCommand.Parameters.Add(dbParam_id);

I get the following error:
Exception Details: System.FormatException: Input string was not in a correct
format.

PERSON.id datatype is "bigint" in Sql server.
ADDRESS.id datatype is "bigint" in Sql server.
Click to expand...
Click to expand...
 
Hi ,

insert [ADDRESS] ( id]) select IDENT_CURRENT( 'PERSON')


That should work. Please take notice that in the Address table ALL other
fields should accept null or have default values

cheers,

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation


nsj said:
I still dont get it right. Can you show me the query?

Thnaks
NJ

"Ignacio Machin ( .NET/ C# MVP )" <ignacio.machin AT dot.state.fl.us> wrote
in message news:[email protected]...
Hi,

I havent Test your code but I believe the error is here:

dbParam_id.ParameterName = "@id";
dbParam_id.Value = "SELECT IDENT_CURRENT('PERSON')";
dbParam_id.DbType = System.Data.DbType.Int64;

The value is not a int64 !!! , what make you think that it will execute the
query and store the value?

The best way of do this is using a SP !

or a better query :)

If you need the query let me know, I have to run now :(

Cheers,

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation


nsj said:
I am working with a web applicaction that accesses a SQL Server
Click to expand...
database.
I
need the value of the 'id' column of the last inserted row in the table
'PERSON'. The SQL statement for that purpose is: "SELECT
IDENT_CURRENT('PERSON')"; . I need to insert this value in another table
called 'ADDRESS'. So I did the following:

System.Data.IDbConnection dbConnection = new
System.Data.SqlClient.SqlConnection(connectionString);
string queryString = "INSERT INTO [ADDRESS] ([id]) VALUES (@id)";
System.Data.IDbCommand dbCommand = new System.Data.SqlClient.SqlCommand();
dbCommand.CommandText = queryString;
dbCommand.Connection = dbConnection;

System.Data.IDataParameter dbParam_id = new
System.Data.SqlClient.SqlParameter();
dbParam_id.ParameterName = "@id";
dbParam_id.Value = "SELECT IDENT_CURRENT('PERSON')";
dbParam_id.DbType = System.Data.DbType.Int64;
dbCommand.Parameters.Add(dbParam_id);

I get the following error:
Exception Details: System.FormatException: Input string was not in a correct
format.

PERSON.id datatype is "bigint" in Sql server.
ADDRESS.id datatype is "bigint" in Sql server.
Click to expand...
Click to expand...
Click to expand...
 
I still have a problem. I have fields in the table that does not accept
'NULL' nor have default values. I created a stored procedure called
'lastRowID_SP'. This stored procedure returns the value of the 'id' column
of the last inserted row. I want to use this stored procedure in my insert
statement. How can I do that? For example something like...

string queryString = "INSERT INTO [ADDRESS] ([id], [city], [state]) VALUES
(lastRowID_SP, @city, @state)";

Thanks,
NJ


Ignacio Machin ( .NET/ C# MVP ) said:
Hi ,

insert [ADDRESS] ( id]) select IDENT_CURRENT( 'PERSON')


That should work. Please take notice that in the Address table ALL other
fields should accept null or have default values

cheers,

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation


nsj said:
I still dont get it right. Can you show me the query?

Thnaks
NJ

"Ignacio Machin ( .NET/ C# MVP )" <ignacio.machin AT dot.state.fl.us> wrote
in message news:[email protected]...
Hi,

I havent Test your code but I believe the error is here:

dbParam_id.ParameterName = "@id";
dbParam_id.Value = "SELECT IDENT_CURRENT('PERSON')";
dbParam_id.DbType = System.Data.DbType.Int64;

The value is not a int64 !!! , what make you think that it will
Click to expand...
execute
the
query and store the value?

The best way of do this is using a SP !

or a better query :)

If you need the query let me know, I have to run now :(

Cheers,

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation


I am working with a web applicaction that accesses a SQL Server database.
I
need the value of the 'id' column of the last inserted row in the table
'PERSON'. The SQL statement for that purpose is: "SELECT
IDENT_CURRENT('PERSON')"; . I need to insert this value in another table
called 'ADDRESS'. So I did the following:

System.Data.IDbConnection dbConnection = new
System.Data.SqlClient.SqlConnection(connectionString);
string queryString = "INSERT INTO [ADDRESS] ([id]) VALUES (@id)";
System.Data.IDbCommand dbCommand = new System.Data.SqlClient.SqlCommand();
dbCommand.CommandText = queryString;
dbCommand.Connection = dbConnection;

System.Data.IDataParameter dbParam_id = new
System.Data.SqlClient.SqlParameter();
dbParam_id.ParameterName = "@id";
dbParam_id.Value = "SELECT IDENT_CURRENT('PERSON')";
dbParam_id.DbType = System.Data.DbType.Int64;
dbCommand.Parameters.Add(dbParam_id);

I get the following error:
Exception Details: System.FormatException: Input string was not in a
correct
format.

PERSON.id datatype is "bigint" in Sql server.
ADDRESS.id datatype is "bigint" in Sql server.
Click to expand...
Click to expand...
Click to expand...
 
Hi,

insert [ADDRESS] ( [id] , [othercolumn] ) select IDENT_CURRENT( 'PERSON')
, value_for_othercolumn


Cheers,

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation

nsj said:
I still have a problem. I have fields in the table that does not accept
'NULL' nor have default values. I created a stored procedure called
'lastRowID_SP'. This stored procedure returns the value of the 'id' column
of the last inserted row. I want to use this stored procedure in my insert
statement. How can I do that? For example something like...

string queryString = "INSERT INTO [ADDRESS] ([id], [city], [state]) VALUES
(lastRowID_SP, @city, @state)";

Thanks,
NJ


"Ignacio Machin ( .NET/ C# MVP )" <ignacio.machin AT dot.state.fl.us> wrote
in message news:[email protected]...
Hi ,

insert [ADDRESS] ( id]) select IDENT_CURRENT( 'PERSON')


That should work. Please take notice that in the Address table ALL other
fields should accept null or have default values

cheers,

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation


nsj said:
I still dont get it right. Can you show me the query?

Thnaks
NJ

"Ignacio Machin ( .NET/ C# MVP )" <ignacio.machin AT dot.state.fl.us> wrote
in message Hi,

I havent Test your code but I believe the error is here:

dbParam_id.ParameterName = "@id";
dbParam_id.Value = "SELECT IDENT_CURRENT('PERSON')";
dbParam_id.DbType = System.Data.DbType.Int64;

The value is not a int64 !!! , what make you think that it will execute
the
query and store the value?

The best way of do this is using a SP !

or a better query :)

If you need the query let me know, I have to run now :(

Cheers,

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation


I am working with a web applicaction that accesses a SQL Server
database.
I
need the value of the 'id' column of the last inserted row in the table
'PERSON'. The SQL statement for that purpose is: "SELECT
IDENT_CURRENT('PERSON')"; . I need to insert this value in another table
called 'ADDRESS'. So I did the following:

System.Data.IDbConnection dbConnection = new
System.Data.SqlClient.SqlConnection(connectionString);
string queryString = "INSERT INTO [ADDRESS] ([id]) VALUES (@id)";
System.Data.IDbCommand dbCommand = new
System.Data.SqlClient.SqlCommand();
dbCommand.CommandText = queryString;
dbCommand.Connection = dbConnection;

System.Data.IDataParameter dbParam_id = new
System.Data.SqlClient.SqlParameter();
dbParam_id.ParameterName = "@id";
dbParam_id.Value = "SELECT IDENT_CURRENT('PERSON')";
dbParam_id.DbType = System.Data.DbType.Int64;
dbCommand.Parameters.Add(dbParam_id);

I get the following error:
Exception Details: System.FormatException: Input string was not in a
correct
format.

PERSON.id datatype is "bigint" in Sql server.
ADDRESS.id datatype is "bigint" in Sql server.
Click to expand...
Click to expand...
Click to expand...
 
It still does not work. I get the following error: "Subqueries are not
allowed in this context. Only scalar expressions are allowed".

My SQL statement is as follows:

string queryString = "INSERT INTO [ADDRESS] ([id], [city]) VALUES ((select
IDENT_CURRENT('PERSON')), @city)";

I had tried this method earlier too. It did not work. Any idea how I can get
it to work?

Thanks and appreciate your help.
NJ


Ignacio Machin ( .NET/ C# MVP ) said:
Hi,

insert [ADDRESS] ( [id] , [othercolumn] ) select IDENT_CURRENT( 'PERSON')
, value_for_othercolumn


Cheers,

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation

nsj said:
I still have a problem. I have fields in the table that does not accept
'NULL' nor have default values. I created a stored procedure called
'lastRowID_SP'. This stored procedure returns the value of the 'id' column
of the last inserted row. I want to use this stored procedure in my insert
statement. How can I do that? For example something like...

string queryString = "INSERT INTO [ADDRESS] ([id], [city], [state]) VALUES
(lastRowID_SP, @city, @state)";

Thanks,
NJ


"Ignacio Machin ( .NET/ C# MVP )" <ignacio.machin AT dot.state.fl.us> wrote
in message news:[email protected]...
Hi ,

insert [ADDRESS] ( id]) select IDENT_CURRENT( 'PERSON')


That should work. Please take notice that in the Address table ALL other
fields should accept null or have default values

cheers,

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation


I still dont get it right. Can you show me the query?

Thnaks
NJ

"Ignacio Machin ( .NET/ C# MVP )" <ignacio.machin AT dot.state.fl.us>
wrote
in message Hi,

I havent Test your code but I believe the error is here:

dbParam_id.ParameterName = "@id";
dbParam_id.Value = "SELECT IDENT_CURRENT('PERSON')";
dbParam_id.DbType = System.Data.DbType.Int64;

The value is not a int64 !!! , what make you think that it will execute
the
query and store the value?

The best way of do this is using a SP !

or a better query :)

If you need the query let me know, I have to run now :(

Cheers,

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation


I am working with a web applicaction that accesses a SQL Server
database.
I
need the value of the 'id' column of the last inserted row in the
table
'PERSON'. The SQL statement for that purpose is: "SELECT
IDENT_CURRENT('PERSON')"; . I need to insert this value in another
table
called 'ADDRESS'. So I did the following:

System.Data.IDbConnection dbConnection = new
System.Data.SqlClient.SqlConnection(connectionString);
string queryString = "INSERT INTO [ADDRESS] ([id]) VALUES (@id)";
System.Data.IDbCommand dbCommand = new
System.Data.SqlClient.SqlCommand();
dbCommand.CommandText = queryString;
dbCommand.Connection = dbConnection;

System.Data.IDataParameter dbParam_id = new
System.Data.SqlClient.SqlParameter();
dbParam_id.ParameterName = "@id";
dbParam_id.Value = "SELECT IDENT_CURRENT('PERSON')";
dbParam_id.DbType = System.Data.DbType.Int64;
dbCommand.Parameters.Add(dbParam_id);

I get the following error:
Exception Details: System.FormatException: Input string was not
Click to expand...
Click to expand...
in
a
Click to expand...
Click to expand...
 
Hi,

Make sure that the value of the second column is a single value

post the query you are using

pd: you can try the query first in query analyser

cheers,

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation



nsj said:
It still does not work. I get the following error: "Subqueries are not
allowed in this context. Only scalar expressions are allowed".

My SQL statement is as follows:

string queryString = "INSERT INTO [ADDRESS] ([id], [city]) VALUES ((select
IDENT_CURRENT('PERSON')), @city)";

I had tried this method earlier too. It did not work. Any idea how I can get
it to work?

Thanks and appreciate your help.
NJ


"Ignacio Machin ( .NET/ C# MVP )" <ignacio.machin AT dot.state.fl.us> wrote
in message news:[email protected]...
Hi,

insert [ADDRESS] ( [id] , [othercolumn] ) select IDENT_CURRENT( 'PERSON')
, value_for_othercolumn


Cheers,

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation

nsj said:
I still have a problem. I have fields in the table that does not accept
'NULL' nor have default values. I created a stored procedure called
'lastRowID_SP'. This stored procedure returns the value of the 'id' column
of the last inserted row. I want to use this stored procedure in my insert
statement. How can I do that? For example something like...

string queryString = "INSERT INTO [ADDRESS] ([id], [city], [state]) VALUES
(lastRowID_SP, @city, @state)";

Thanks,
NJ


"Ignacio Machin ( .NET/ C# MVP )" <ignacio.machin AT dot.state.fl.us> wrote
in message Hi ,

insert [ADDRESS] ( id]) select IDENT_CURRENT( 'PERSON')


That should work. Please take notice that in the Address table ALL other
fields should accept null or have default values

cheers,

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation


I still dont get it right. Can you show me the query?

Thnaks
NJ

"Ignacio Machin ( .NET/ C# MVP )" <ignacio.machin AT dot.state.fl.us>
wrote
in message Hi,

I havent Test your code but I believe the error is here:

dbParam_id.ParameterName = "@id";
dbParam_id.Value = "SELECT IDENT_CURRENT('PERSON')";
dbParam_id.DbType = System.Data.DbType.Int64;

The value is not a int64 !!! , what make you think that it will
execute
the
query and store the value?

The best way of do this is using a SP !

or a better query :)

If you need the query let me know, I have to run now :(

Cheers,

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation


I am working with a web applicaction that accesses a SQL Server
database.
I
need the value of the 'id' column of the last inserted row in the
table
'PERSON'. The SQL statement for that purpose is: "SELECT
IDENT_CURRENT('PERSON')"; . I need to insert this value in another
table
called 'ADDRESS'. So I did the following:

System.Data.IDbConnection dbConnection = new
System.Data.SqlClient.SqlConnection(connectionString);
string queryString = "INSERT INTO [ADDRESS] ([id]) VALUES (@id)";
System.Data.IDbCommand dbCommand = new
System.Data.SqlClient.SqlCommand();
dbCommand.CommandText = queryString;
dbCommand.Connection = dbConnection;

System.Data.IDataParameter dbParam_id = new
System.Data.SqlClient.SqlParameter();
dbParam_id.ParameterName = "@id";
dbParam_id.Value = "SELECT IDENT_CURRENT('PERSON')";
dbParam_id.DbType = System.Data.DbType.Int64;
dbCommand.Parameters.Add(dbParam_id);

I get the following error:
Exception Details: System.FormatException: Input string was
Click to expand...
Click to expand...
not
in
Click to expand...
Click to expand...
 
Thanks
NJ

Ignacio Machin ( .NET/ C# MVP ) said:
Hi,

Make sure that the value of the second column is a single value

post the query you are using

pd: you can try the query first in query analyser

cheers,

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation



nsj said:
It still does not work. I get the following error: "Subqueries are not
allowed in this context. Only scalar expressions are allowed".

My SQL statement is as follows:

string queryString = "INSERT INTO [ADDRESS] ([id], [city]) VALUES ((select
IDENT_CURRENT('PERSON')), @city)";

I had tried this method earlier too. It did not work. Any idea how I can get
it to work?

Thanks and appreciate your help.
NJ


"Ignacio Machin ( .NET/ C# MVP )" <ignacio.machin AT dot.state.fl.us> wrote
in message news:[email protected]...
Hi,

insert [ADDRESS] ( [id] , [othercolumn] ) select IDENT_CURRENT( 'PERSON')
, value_for_othercolumn


Cheers,

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation

I still have a problem. I have fields in the table that does not accept
'NULL' nor have default values. I created a stored procedure called
'lastRowID_SP'. This stored procedure returns the value of the 'id' column
of the last inserted row. I want to use this stored procedure in my insert
statement. How can I do that? For example something like...

string queryString = "INSERT INTO [ADDRESS] ([id], [city], [state]) VALUES
(lastRowID_SP, @city, @state)";

Thanks,
NJ


"Ignacio Machin ( .NET/ C# MVP )" <ignacio.machin AT dot.state.fl.us>
wrote
in message Hi ,

insert [ADDRESS] ( id]) select IDENT_CURRENT( 'PERSON')


That should work. Please take notice that in the Address table ALL other
fields should accept null or have default values

cheers,

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation


I still dont get it right. Can you show me the query?

Thnaks
NJ

"Ignacio Machin ( .NET/ C# MVP )" <ignacio.machin AT dot.state.fl.us>
wrote
in message Hi,

I havent Test your code but I believe the error is here:

dbParam_id.ParameterName = "@id";
dbParam_id.Value = "SELECT IDENT_CURRENT('PERSON')";
dbParam_id.DbType = System.Data.DbType.Int64;

The value is not a int64 !!! , what make you think that it will
execute
the
query and store the value?

The best way of do this is using a SP !

or a better query :)

If you need the query let me know, I have to run now :(

Cheers,

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation


I am working with a web applicaction that accesses a SQL Server
database.
I
need the value of the 'id' column of the last inserted row
Click to expand...
in
the
table
'PERSON'. The SQL statement for that purpose is: "SELECT
IDENT_CURRENT('PERSON')"; . I need to insert this value in another
table
called 'ADDRESS'. So I did the following:

System.Data.IDbConnection dbConnection = new
System.Data.SqlClient.SqlConnection(connectionString);
string queryString = "INSERT INTO [ADDRESS] ([id]) VALUES (@id)";
System.Data.IDbCommand dbCommand = new
System.Data.SqlClient.SqlCommand();
dbCommand.CommandText = queryString;
dbCommand.Connection = dbConnection;

System.Data.IDataParameter dbParam_id = new
System.Data.SqlClient.SqlParameter();
dbParam_id.ParameterName = "@id";
dbParam_id.Value = "SELECT IDENT_CURRENT('PERSON')";
dbParam_id.DbType = System.Data.DbType.Int64;
dbCommand.Parameters.Add(dbParam_id);

I get the following error:
Exception Details: System.FormatException: Input string was
Click to expand...
not
in
a
correct
format.

PERSON.id datatype is "bigint" in Sql server.
ADDRESS.id datatype is "bigint" in Sql server.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top