table structure
srno bigint(20)
citycode bigint(20)
centercode bigint(20)
rollno bigint(20)
firstname varchar(100)
lastname varchar(100)
fathername varchar(100)
dob datetime
gender char(1)
examdate datetime
faceimagelocation varchar(256)
keydata varchar(256)
string StrQry=Insert into tblstudentDetails ( rollNo,formNo,citycode,firstName,lastName,fatherName,Gender,dob,centerCode,examDate,faceImageLocation,keydata )
values( ?rollNo,?formNo,?citycode,?firstName,?lastName,?fatherName,?Gender,?dob,?centerCode,?examDate,?faceImageLocation,?keydata)
SqlCmd = new OdbcCommand(StrQry, SqlCon);
SqlCmd.Parameters.Add(new OdbcParameter("?" + DT.Columns[IntIncVar].ToString(), SqlDbType.Int)).Value = DT.Rows[IntDTCOUNT][IntIncVar];
SqlCmd.Parameters.Add(new OdbcParameter("?" + DT.Columns[IntIncVar].ToString(), SqlDbType.VarChar)).Value = DT.Rows[IntDTCOUNT][IntIncVar];
SqlCmd.Parameters.Add(new OdbcParameter("?" + DT.Columns[IntIncVar].ToString(), SqlDbType.DateTime)).Value = DT.Rows[IntDTCOUNT][IntIncVar];
........... //as required
dt is datatable, passing parmeter field wise, i have not written all parameter here, getting right value for each column i.e., getting right value from DT.Rows[IntDTCOUNT][IntIncVar]
SqlCmd.ExecuteNonQuery();
// i am getting error as "ERROR [42000] [MySQL][ODBC 5.1 Driver][mysqld-5.1.28-rc-community]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'firstName,'SHREYA CHITNAVIS'lastName,'ABHAY CHITNAVIS'fatherName,'m'Gender,_lati' at line 1"
plz let me know where i am getting wrong
note:- database is mysql
Loading
Sunny SharmaPosted Aug 3, 2013, 3:58 AM
see this code:
-------------------------
nishant ranjanPosted Aug 3, 2013, 5:27 AM
Thanks
Cheers!!!
Sunny SharmaPosted Aug 3, 2013, 4:39 AM
I'm glad I could help, Happy Coding :)
Cheers!!
nishant ranjanPosted Aug 3, 2013, 4:25 AM
Done!!!
but y it was not working with sqldbtype???
anyway... thanks to all for the suggestions.
Sunny SharmaPosted Aug 3, 2013, 4:25 AM
any update on that?
nishant ranjanPosted Aug 3, 2013, 4:18 AM
its varchar
Iftikar HussainPosted Aug 3, 2013, 3:59 AM
Can you check again?
Regards,
Iftikar
nishant ranjanPosted Aug 3, 2013, 3:58 AM
SqlCmd.Parameters.Add("@keydata", OdbcType.VarChar, 256);
SqlCmd.Parameters["@keydata"].Value = "Test";
still null value is getting inserted
Iftikar HussainPosted Aug 3, 2013, 3:53 AM
Regards,
Iftikar
nishant ranjanPosted Aug 3, 2013, 3:52 AM
not working
nishant ranjanPosted Aug 3, 2013, 3:50 AM
OdbcType.VarChar, 256 is not working
Iftikar HussainPosted Aug 3, 2013, 3:46 AM
for key data m using below line
SqlCmd.Parameters.AddWithValue("keyData", dataToEncrypt.ToString()).;
Regards,
Iftikar
Iftikar HussainPosted Aug 3, 2013, 3:43 AM
............................
.............................
and also make your table column size enough to hold keydata value
Regards,
Iftikar
nishant ranjanPosted Aug 3, 2013, 3:41 AM
all other column value are inserted perfectly just this column has issue
for key data m using below line
SqlCmd.Parameters.Add(new OdbcParameter("@keyData", SqlDbType.VarChar)).Value = dataToEncrypt.ToString();
and for other field m using below line based on their datatype
SqlCmd.Parameters.Add(new OdbcParameter("@" + DT.Columns[IntIncVar].ToString(), SqlDbType.VarChar)).Value = DT.Rows[IntDTCOUNT][IntIncVar];
m i missing something above
datatoencrypt value is E5CAFC3CCF056BE4A319A1FD0FF8BE08
datatoencrypt variable is of string type
in database
keydata datatype is varchar(256)
Sunny SharmaPosted Aug 3, 2013, 3:36 AM
nishant ranjanPosted Aug 3, 2013, 3:29 AM
where datatoencrypt=E5CAFC3CCF056BE4A319A1FD0FF8BE08
insert command has 12 ? against 12 field
Insert into tblstudentDetails ( rollNo,formNo,citycode,firstName,lastName,fatherName,Gender,dob,centerCode,examDate,faceImageLocation,keydata ) values( ?,?,?,?,?,?,?,?,?,?,?,?)
still keydata is not getting inserted
Iftikar HussainPosted Aug 3, 2013, 3:26 AM
can you try like this
Regards,
Iftikar
nishant ranjanPosted Aug 3, 2013, 3:24 AM
tried and done except for one issue working fine but last column value i.e, keydata value is not getting inserted. all other value are getting inserted perfectly. just this issue remains. let me check the syntax.
Sunny SharmaPosted Aug 3, 2013, 3:21 AM
nishant ranjanPosted Aug 3, 2013, 3:19 AM
i tried but of no use
Sunny SharmaPosted Aug 3, 2013, 3:15 AM
modify your code like below:
------------------------
string StrQry=Insert into tblstudentDetails ( rollNo,formNo,citycode,firstName,lastName,fatherName,Gender,dob,centerCode,examDate,faceImageLocation,keydata )
values(?,?,?,?,?,?,?,?,?,?,?,?)
SqlCmd = new OdbcCommand(StrQry, SqlCon);
//replace these lines with I've menioned below it: (these codes are also fine but just to cut the lines)
SqlCmd.Parameters.Add(new OdbcParameter("?" + DT.Columns[IntIncVar].ToString(), SqlDbType.Int)).Value = DT.Rows[IntDTCOUNT][IntIncVar];
SqlCmd.Parameters.Add(new OdbcParameter("?" + DT.Columns[IntIncVar].ToString(), SqlDbType.VarChar)).Value = DT.Rows[IntDTCOUNT][IntIncVar];
use this line:
SqlCmd.Parameters.AddWithValue("rollno",DT.Rows[IntDTCOUNT][IntIncVar]);
SqlCmd.Parameters.AddWithValue("formno",DT.Rows[IntDTCOUNT][IntIncVar]);
// so on.
Feedback awaited.
Ravi ShekharPosted Aug 3, 2013, 3:11 AM
you tried or not????
this error occurs because of reserved keywords or inserting wrong datatype value
check below link
http://stackoverflow.com/questions/3843362/problem-with-odbc-net-connection-to-mysql-db
Sunny SharmaPosted Aug 3, 2013, 3:05 AM
http://dev.mysql.com/doc/mysqld-version-reference/en/mysqld-version-reference-reservedwords-5-5.html
Ravi ShekharPosted Aug 3, 2013, 3:03 AM
Insert into tblstudentDetails ( rollNo,formNo,citycode,firstName,lastName,fatherName,'Gender',dob,
centerCode,examDate,faceImageLocation,'keydata' )
values( ?rollNo,?formNo,?citycode,?firstName,?lastName,?fatherName,?Gender,?dob,?centerCode,?examDate,?faceImageLocation,?keydata)
here gender is also a keyword. so change. Gender as 'Gender'
nishant ranjanPosted Aug 3, 2013, 3:00 AM
m using OdbcConnection
Ravi ShekharPosted Aug 3, 2013, 3:00 AM
Insert into tblstudentDetails ( rollNo,formNo,citycode,firstName,lastName,fatherName,Gender,dob,
centerCode,examDate,faceImageLocation,'keydata' )
values( ?rollNo,?formNo,?citycode,?firstName,?lastName,?fatherName,?Gender,?dob,?centerCode,?examDate,?faceImageLocation,?keydata)
I think keydata is reserved keyword.
Sunny SharmaPosted Aug 3, 2013, 2:55 AM
I've few questions for you just to troubleshoot the issue:
1. Are you using a MySqlConnection or SqlConnection?
2. What is the point of adding OdbcParameter instead of MySqlParameter if it's a MySql database?
nishant ranjanPosted Aug 3, 2013, 2:48 AM
on debuging the value i get have written on earlier post. i am getting insert statement as posted, and value on debugging as posted. i dont see any missing or syntax error in that, if exists kindly point out
when i use ? in place of @ i get this error but on using @, executenonquery doesnot throw any error. instead it inserts row with null value for all column.
Sunny SharmaPosted Aug 3, 2013, 2:39 AM
See the error:
"'firstName,'SHREYA CHITNAVIS'lastName,'ABHAY CHITNAVIS'fatherName,'m'Gender,_lati' at line 1"
I don't see the statement in proper format. I mean they are not properly quoted, missing the commas somewhere. Check the logic carefully.
nishant ranjanPosted Aug 3, 2013, 2:37 AM
Insert into tblstudentDetails ( rollNo,formNo,citycode,firstName,lastName,fatherName,Gender,dob,centerCode,examDate,faceImageLocation,keydata ) values( ?rollNo,?formNo,?citycode,?firstName,?lastName,?fatherName,?Gender,?dob,?centerCode,?examDate,?faceImageLocation,?keydata)
at SqlCmd.ExecuteNonQuery();
on debugging i am getting each parameter value as i have mentioned in my last post.
@Ravi Shekhar
how i can put ' ' on this query?
Ravi ShekharPosted Aug 3, 2013, 2:33 AM
Sunny SharmaPosted Aug 3, 2013, 2:33 AM
put a breakpoint on this line:
SqlCmd.ExecuteNonQuery();
and check the final value of query in SqlCmd.
Share more code if still have issues.
nishant ranjanPosted Aug 3, 2013, 2:28 AM
@formNo 1
@citycode 9
@firstName SHREYA CHITNAVIS
@lastName SHREYA CHITNAVIS
@fatherName ABHAY CHITNAVIS
@Gender m
@dob 12/13/1995 12:00:00 AM
@centerCode 9003
@examDate 07/07/2013 12:00:00 AM
@faceImageLocation c:\VAYAPAMIMAGE\07-07-2013\9\p1.jpg
@keydata E5CAFC3CCF056BE4A319A1FD0FF8BE08
i am getting the above value for each parameter.
plz note:- if i am using @ in place of ?(see question) then null value is inserted into database and srno(as it is auto generated) goes to number of record inserted. i.e. if i insert 6 row using @ in place of ? then row will be inserted with srno value from 1-6 but all other column value are null
Ravi ShekharPosted Aug 3, 2013, 2:15 AM
or share your values here.