SQL Insert Error

[Cameron Frasnelly]

Any ideas on why the below doesn't work?

I keep getting this error message????? ---> "Object must implement
IConvertible".

Private Sub cmdSubmit_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles cmdSubmit.Click

'CONNECT TO SQL

Dim strConnection As String

strConnection = "Data Source=x.x.x.x,1433;Network Library=DBMSSOCN;Initial
Catalog=Ops1SQL;User ID=dbIN;Password=xxxyyyzzz;"

Dim cn As New SqlConnection(strConnection)

'------INSERT TO BASE Work Order Table

Dim strSQL As String



strSQL = "INSERT INTO tblWorkOrder (FKEYDepartment, Initiator, ReceiveNOW,
Problem)" & _

" VALUES (@Department, @Initiator, @ReceiveNOW, @Problem)"



Dim dbComm As New SqlCommand(strSQL, cn)

dbComm.Parameters.Add("@Department", SqlDbType.Int)

dbComm.Parameters.Add("@Initiator", SqlDbType.Int)

dbComm.Parameters.Add("@ReceiveNOW", SqlDbType.DateTime)

dbComm.Parameters.Add("@Problem", SqlDbType.NText, 16)

dbComm.Parameters("@Department").Value = 8

dbComm.Parameters("@Initiator").Value = CInt(Session("EmployeeKEY"))

dbComm.Parameters("@ReceiveNOW").Value = Now()

dbComm.Parameters("@Problem").Value = Me.txtComments



Dim woKEY As Integer

Try

cn.Open()

woKEY = dbComm.ExecuteScalar()

Catch ex As Exception

Me.txtAccountNumber.Text = ex.Message.ToString

Finally

If cn.State = ConnectionState.Open Then

cn.Close()

End If

Me.txtRespCode.Text = woKEY.ToString

End Try

'------INSERT the sub work order

End Sub

[Cameron Frasnelly]

Oh... it fails on this line...

woKEY = dbComm.ExecuteScalar()


"Cameron Frasnelly" <networkmanage@premierwestbank.com> wrote in message
news:%236RnE9fXDHA.652@TK2MSFTNGP10.phx.gbl...

> Any ideas on why the below doesn't work?
>
> I keep getting this error message????? ---> "Object must implement
> IConvertible".
>
> Private Sub cmdSubmit_Click(ByVal sender As System.Object, ByVal e As
> System.EventArgs) Handles cmdSubmit.Click
>
> 'CONNECT TO SQL
>
> Dim strConnection As String
>
> strConnection = "Data Source=x.x.x.x,1433;Network Library=DBMSSOCN;Initial
> Catalog=Ops1SQL;User ID=dbIN;Password=xxxyyyzzz;"
>
> Dim cn As New SqlConnection(strConnection)
>
> '------INSERT TO BASE Work Order Table
>
> Dim strSQL As String
>
>
>
> strSQL = "INSERT INTO tblWorkOrder (FKEYDepartment, Initiator, ReceiveNOW,
> Problem)" & _
>
> " VALUES (@Department, @Initiator, @ReceiveNOW, @Problem)"
>
>
>
> Dim dbComm As New SqlCommand(strSQL, cn)
>
> dbComm.Parameters.Add("@Department", SqlDbType.Int)
>
> dbComm.Parameters.Add("@Initiator", SqlDbType.Int)
>
> dbComm.Parameters.Add("@ReceiveNOW", SqlDbType.DateTime)
>
> dbComm.Parameters.Add("@Problem", SqlDbType.NText, 16)
>
> dbComm.Parameters("@Department").Value = 8
>
> dbComm.Parameters("@Initiator").Value = CInt(Session("EmployeeKEY"))
>
> dbComm.Parameters("@ReceiveNOW").Value = Now()
>
> dbComm.Parameters("@Problem").Value = Me.txtComments
>
>
>
> Dim woKEY As Integer
>
> Try
>
> cn.Open()
>
> woKEY = dbComm.ExecuteScalar()
>
> Catch ex As Exception
>
> Me.txtAccountNumber.Text = ex.Message.ToString
>
> Finally
>
> If cn.State = ConnectionState.Open Then
>
> cn.Close()
>
> End If
>
> Me.txtRespCode.Text = woKEY.ToString
>
> End Try
>
> '------INSERT the sub work order
>
> End Sub
>
>

[PJ]

> dbComm.Parameters("@Problem").Value = Me.txtComments

I think you want: Me.txtComments.Text


"Cameron Frasnelly" <networkmanage@premierwestbank.com> wrote in message
news:%236RnE9fXDHA.652@TK2MSFTNGP10.phx.gbl...

> Any ideas on why the below doesn't work?
>
> I keep getting this error message????? ---> "Object must implement
> IConvertible".
>
> Private Sub cmdSubmit_Click(ByVal sender As System.Object, ByVal e As
> System.EventArgs) Handles cmdSubmit.Click
>
> 'CONNECT TO SQL
>
> Dim strConnection As String
>
> strConnection = "Data Source=x.x.x.x,1433;Network Library=DBMSSOCN;Initial
> Catalog=Ops1SQL;User ID=dbIN;Password=xxxyyyzzz;"
>
> Dim cn As New SqlConnection(strConnection)
>
> '------INSERT TO BASE Work Order Table
>
> Dim strSQL As String
>
>
>
> strSQL = "INSERT INTO tblWorkOrder (FKEYDepartment, Initiator, ReceiveNOW,
> Problem)" & _
>
> " VALUES (@Department, @Initiator, @ReceiveNOW, @Problem)"
>
>
>
> Dim dbComm As New SqlCommand(strSQL, cn)
>
> dbComm.Parameters.Add("@Department", SqlDbType.Int)
>
> dbComm.Parameters.Add("@Initiator", SqlDbType.Int)
>
> dbComm.Parameters.Add("@ReceiveNOW", SqlDbType.DateTime)
>
> dbComm.Parameters.Add("@Problem", SqlDbType.NText, 16)
>
> dbComm.Parameters("@Department").Value = 8
>
> dbComm.Parameters("@Initiator").Value = CInt(Session("EmployeeKEY"))
>
> dbComm.Parameters("@ReceiveNOW").Value = Now()
>
> dbComm.Parameters("@Problem").Value = Me.txtComments
>
>
>
> Dim woKEY As Integer
>
> Try
>
> cn.Open()
>
> woKEY = dbComm.ExecuteScalar()
>
> Catch ex As Exception
>
> Me.txtAccountNumber.Text = ex.Message.ToString
>
> Finally
>
> If cn.State = ConnectionState.Open Then
>
> cn.Close()
>
> End If
>
> Me.txtRespCode.Text = woKEY.ToString
>
> End Try
>
> '------INSERT the sub work order
>
> End Sub
>
>

[Srinivas Kotipalli]

Use

ExecuteNonQuery instead of ExecuteScalar

"Cameron Frasnelly" <networkmanage@premierwestbank.com> wrote in message
news:%236RnE9fXDHA.652@TK2MSFTNGP10.phx.gbl...

> Any ideas on why the below doesn't work?
>
> I keep getting this error message????? ---> "Object must implement
> IConvertible".
>
> Private Sub cmdSubmit_Click(ByVal sender As System.Object, ByVal e As
> System.EventArgs) Handles cmdSubmit.Click
>
> 'CONNECT TO SQL
>
> Dim strConnection As String
>
> strConnection = "Data Source=x.x.x.x,1433;Network Library=DBMSSOCN;Initial
> Catalog=Ops1SQL;User ID=dbIN;Password=xxxyyyzzz;"
>
> Dim cn As New SqlConnection(strConnection)
>
> '------INSERT TO BASE Work Order Table
>
> Dim strSQL As String
>
>
>
> strSQL = "INSERT INTO tblWorkOrder (FKEYDepartment, Initiator, ReceiveNOW,
> Problem)" & _
>
> " VALUES (@Department, @Initiator, @ReceiveNOW, @Problem)"
>
>
>
> Dim dbComm As New SqlCommand(strSQL, cn)
>
> dbComm.Parameters.Add("@Department", SqlDbType.Int)
>
> dbComm.Parameters.Add("@Initiator", SqlDbType.Int)
>
> dbComm.Parameters.Add("@ReceiveNOW", SqlDbType.DateTime)
>
> dbComm.Parameters.Add("@Problem", SqlDbType.NText, 16)
>
> dbComm.Parameters("@Department").Value = 8
>
> dbComm.Parameters("@Initiator").Value = CInt(Session("EmployeeKEY"))
>
> dbComm.Parameters("@ReceiveNOW").Value = Now()
>
> dbComm.Parameters("@Problem").Value = Me.txtComments
>
>
>
> Dim woKEY As Integer
>
> Try
>
> cn.Open()
>
> woKEY = dbComm.ExecuteScalar()
>
> Catch ex As Exception
>
> Me.txtAccountNumber.Text = ex.Message.ToString
>
> Finally
>
> If cn.State = ConnectionState.Open Then
>
> cn.Close()
>
> End If
>
> Me.txtRespCode.Text = woKEY.ToString
>
> End Try
>
> '------INSERT the sub work order
>
> End Sub
>
>

[Matthew Paterson]

Ok I'm having a problem with a SQL insert going to a Access database...here
is the connection code:

// PHP db connection
$db = 'C:\\Phpweb\\test.mdb';
$conn = new COM('ADODB.Connection');
$conn->Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=$db");
$query = $conn->Execute($sql);

Here is the print out of the SQL:

INSERT INTO
[Cards]([active],[division],[province],[filetitle],[air],[water],[sewage],[p
ttw],[partVIII],[part46]) VALUES ('1','ob','Ontario','0 0 0
0','1','1','1','1','1','1')

Here is the errors:

Warning: (null)(): Invoke() failed: Exception occurred. Source: Microsoft
JET Database Engine Description: Syntax error in INSERT INTO statement. in
c:\phpweb\admin.card.view.php on line 193

Warning: (null)(): Invoke() failed: Exception occurred. Source:
ADODB.Connection Description: Arguments are of the wrong type, are out of
acceptable range, or are in conflict with one another. in
c:\phpweb\admin.card.view.php on line 196

Warning: main(): PropGet() failed: Exception occurred. Source:
ADODB.Connection Description: Arguments are of the wrong type, are out of
acceptable range, or are in conflict with one another. in
c:\phpweb\admin.card.view.php on line 197

Any help would be greatly appricated!!

Thanks

[Matthew Paterson]

Ok I'm having a problem with a SQL insert going to a Access database...here
is the connection code:

// PHP db connection
$db = 'C:\\Phpweb\\test.mdb';
$conn = new COM('ADODB.Connection');
$conn->Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=$db");
$query = $conn->Execute($sql);

Here is the print out of the SQL:

INSERT INTO
[Cards]([active],[division],[province],[filetitle],[air],[water],[sewage],[p
ttw],[partVIII],[part46]) VALUES ('1','ob','Ontario','0 0 0
0','1','1','1','1','1','1')

Here is the errors:

Warning: (null)(): Invoke() failed: Exception occurred. Source: Microsoft
JET Database Engine Description: Syntax error in INSERT INTO statement. in
c:\phpweb\admin.card.view.php on line 193

Warning: (null)(): Invoke() failed: Exception occurred. Source:
ADODB.Connection Description: Arguments are of the wrong type, are out of
acceptable range, or are in conflict with one another. in
c:\phpweb\admin.card.view.php on line 196

Warning: main(): PropGet() failed: Exception occurred. Source:
ADODB.Connection Description: Arguments are of the wrong type, are out of
acceptable range, or are in conflict with one another. in
c:\phpweb\admin.card.view.php on line 197

Any help would be greatly appricated!!

Thanks

[Terence]

Matthew Paterson wrote:

> Ok I'm having a problem with a SQL insert going to a Access database...here
> is the connection code:
>
> // PHP db connection
> $db = 'C:\\Phpweb\\test.mdb';
> $conn = new COM('ADODB.Connection');

This is going to be expensive and less than reliable.

Do yourself a favour and use PEAR:DB
you can change to a real RDBMS later and your code will be none the
wiser. Here is a class I wrote as part of an MSAccess db app.

You need only look at the constructor if you don't want to use the rest.

Warning: the ODBC driver for PEAR:DB has a bug. It is designed to work
with unix style odbc driver. Go to c:/php/PEAR/DB/odbc.php and change

$conn = @$connect_function($dbhost, $user, $pw);
to
$conn = @$connect_function($dsninfo['database'], $user, $pw);

on line 134

I spoke to the maintainer of odbc.php and the fault as acknowledged but
left alone.

if you have no pear directory, then go the a dos command window, change
to your php directory, and type "go-pear" (make sure you are connected
to the net).


<?php

include_once "db.php";
include_once "class_Exception.php";

class sopsDb {

var $strUser ="";
var $strPass ="";
var $strDBname = "sops";
var $strHost = "localhost";
var $strDbType = "odbc";
var $dsn;
var $db;

function sopsDb() {
$this->dsn =
$this->strDbType."://"
// .$this->strUser.":"
// .$this->strPass."@"
.$this->strHost."/"
.$this->strDBname
;
if (DB::isError($this->db = DB::connect($this->dsn)))
new
Exception($this->db->getMessage().":\n".$this->db->getUserinfo(),true);
}

function &query($sql,$complain=true) {
$res =& $this->db->query($sql);
if($complain) $this->DBerrorCheck($res,$sql);
return $res;
}

function DBerrorCheck($obj,$sql) {
if (DB::isError($obj)) {
$err = $obj->getMessage().":\n".$obj->getUserinfo();

if(stristr($obj->getUserinfo(),"duplicate values"))
$err = "The name you specified already exists.";

new Exception($err,true);
}
}

function strMSEscapeSingleQuotes(&$arrPayload,$arrFields) {
foreach($arrFields AS $field) {
$arrPayload[$field] =
str_replace("'","''",$arrPayload[$field]);
}
}

function strOdbcDateTimeFormat($intTs=0) {
if(!$intTs) $intTs=time();
// { ts '1998-05-02 01:23:56.123' }
//
[url]http://msdn.microsoft.com/library/en-us/acdata/ac_8_con_03_04l0.asp[/url]
$strTs = "{ts'";
$strTs .= date("Y-m-d H:i:s",$intTs);
$strTs .= "'}";
return $strTs;
}

function MsSqlPrep(&$sql) {
$sql = str_replace(" '',"," NULL,",stripslashes($sql));
}

function escapeUri($uri) {
if(strpos($uri,"\\\\") === 0)
$uri = "file://".str_replace("\\","/",$uri);
return $uri;
}

function query2d($sql) {
$arr2d = array();
$objResTaskSystems = $this->query($sql);
while($arr2d[] = $objResTaskSystems->fetchRow(DB_FETCHMODE_ASSOC));
if(is_array($arr2d)) array_pop($arr2d);
return $arr2d;
}
}

?>

[eLVeNBYTe]

Do not use VALUES (), instead use SELECT.

[url]www.elvenbyte.es.kz[/url]

On Wed, 19 Nov 2003 03:33:57 GMT, "Matthew Paterson"
<mpaterso@telus.net> wrote:

>Ok I'm having a problem with a SQL insert going to a Access database...here
>is the connection code:
>
>// PHP db connection
>$db = 'C:\\Phpweb\\test.mdb';
>$conn = new COM('ADODB.Connection');
>$conn->Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=$db");
>$query = $conn->Execute($sql);
>
>Here is the print out of the SQL:
>
>INSERT INTO
>[Cards]([active],[division],[province],[filetitle],[air],[water],[sewage],[p
>ttw],[partVIII],[part46]) VALUES ('1','ob','Ontario','0 0 0
>0','1','1','1','1','1','1')
>
>Here is the errors:
>
>Warning: (null)(): Invoke() failed: Exception occurred. Source: Microsoft
>JET Database Engine Description: Syntax error in INSERT INTO statement. in
>c:\phpweb\admin.card.view.php on line 193
>
>Warning: (null)(): Invoke() failed: Exception occurred. Source:
>ADODB.Connection Description: Arguments are of the wrong type, are out of
>acceptable range, or are in conflict with one another. in
>c:\phpweb\admin.card.view.php on line 196
>
>Warning: main(): PropGet() failed: Exception occurred. Source:
>ADODB.Connection Description: Arguments are of the wrong type, are out of
>acceptable range, or are in conflict with one another. in
>c:\phpweb\admin.card.view.php on line 197
>
>Any help would be greatly appricated!!
>
>Thanks
>

eLVeNBYTe
[url]http://www.elvenbyte.es.kz[/url]

[kafooey]

On Wed, 19 Nov 2003 03:33:57 GMT, "Matthew Paterson"
<mpaterso@telus.net> wrote:

>INSERT INTO
>[Cards]([active],[division],[province],[filetitle],[air],[water],[sewage],[p
>ttw],[partVIII],[part46]) VALUES ('1','ob','Ontario','0 0 0
>0','1','1','1','1','1','1')

I thought square brackets meant "value of" in Access?

Have you tried...

INSERT INTO Cards
(active,division,province,filetitle,air,water,sewa ge,pttw,partVIII,part46)
VALUES
('1','ob','Ontario','0 0 0 0','1','1','1','1','1','1')

Also, are all the fields character datatypes?

kafooey
- [email]kafooey@nospam.yahoo.co.uk[/email]
- [url]http://www.pluggedout.com/blog[/url]