473,322 Members | 1,188 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Reusing SQL statements

Hi,

I am new to C# and SQLite and I am having problems using
Paramaters.Add...

I prepare the "INSERT" statement and inside a loop I set the values for
each parameter and call ExecuteNonQuery();
Something similar to that:

checkCmd.CommandText = "SELECT userId FROM users WHERE
userId = @userId";

IDbDataParameter userIdPar =
checkCmd.CreateParameter();

userIdPar.ParameterName = "@userId";

userIdPar.DbType = DbType.String;
checkCmd.Parameters.Add(userIdPar);

updateCmd = conn.CreateCommand();
updateCmd.CommandText = "UPDATE users SET userName =
@userName, userPin = @userPin WHERE userId = @userId";
IDbDataParameter userIdUp =
updateCmd.CreateParameter();
userIdUp.ParameterName = "@userId";
userIdUp.DbType = DbType.Int32;
IDbDataParameter userNameUp =
updateCmd.CreateParameter();
userNameUp.ParameterName = "@userName";
userNameUp.DbType = DbType.String;
IDbDataParameter userPinUp =
updateCmd.CreateParameter();
userPinUp.ParameterName = "@userPin";
userPinUp.DbType = DbType.String;
updateCmd.Parameters.Add(userIdUp);
updateCmd.Parameters.Add(userNameUp);
updateCmd.Parameters.Add(userPinUp);
insertCmd = conn.CreateCommand();
insertCmd.CommandText = "INSERT INTO users (userId,
userName, userPin) VALUES (@userId, @userName, @userPin)";
IDbDataParameter userIdIn =
insertCmd.CreateParameter();
userIdIn.ParameterName = "@userId";
userIdIn.DbType = DbType.Int32;
IDbDataParameter userNameIn =
insertCmd.CreateParameter();
userNameIn.ParameterName = "@userName";
userNameIn.DbType = DbType.String;
IDbDataParameter userPinIn =
insertCmd.CreateParameter();
userPinIn.ParameterName = "@userPin";
userPinIn.DbType = DbType.String;
insertCmd.Parameters.Add(userIdIn);
insertCmd.Parameters.Add(userNameIn);
insertCmd.Parameters.Add(userPinIn);
while (objBinaryReaderRx.BaseStream.Position <
PacketSize)
{

int userId = objBinaryReaderRx.ReadInt32();
string userName = objBinaryReaderRx.ReadString();
string userPIN = objBinaryReaderRx.ReadString();

userIdPar.Value = userId;

readerCheck = checkCmd.ExecuteReader();

if (readerCheck.Read())
{
userIdUp.Value = userId;
userNameUp.Value = userName;
userPinUp.Value = userPIN;

updateCmd.ExecuteNonQuery();
}
else
{
userIdIn.Value = userId;
userNameIn.Value = userName;
userPinIn.Value = userPIN;

insertCmd.ExecuteNonQuery();

}

readerCheck.Close();
}
transaction.Commit();

return true;

catch{...}
finally{...}

The first "INSERT" works fine, but the second time I get: "Operation is
not valid due to the current state of the object."

I am using .NET 1.1, Finisar 1.1(as Database Driver) and SQLite 3

Thanks in advance

Dec 14 '06 #1
1 3981
This is the correct way to reuse you SQL code. It might not be the best one
but at least is a good one.

http://msdn.microsoft.com/library/de...wriportap2.asp

chanmm

"jfbaro" <jf*****@gmail.comwrote in message
news:11*********************@l12g2000cwl.googlegro ups.com...
Hi,

I am new to C# and SQLite and I am having problems using
Paramaters.Add...

I prepare the "INSERT" statement and inside a loop I set the values for
each parameter and call ExecuteNonQuery();
Something similar to that:

checkCmd.CommandText = "SELECT userId FROM users WHERE
userId = @userId";

IDbDataParameter userIdPar =
checkCmd.CreateParameter();

userIdPar.ParameterName = "@userId";

userIdPar.DbType = DbType.String;
checkCmd.Parameters.Add(userIdPar);

updateCmd = conn.CreateCommand();
updateCmd.CommandText = "UPDATE users SET userName =
@userName, userPin = @userPin WHERE userId = @userId";
IDbDataParameter userIdUp =
updateCmd.CreateParameter();
userIdUp.ParameterName = "@userId";
userIdUp.DbType = DbType.Int32;
IDbDataParameter userNameUp =
updateCmd.CreateParameter();
userNameUp.ParameterName = "@userName";
userNameUp.DbType = DbType.String;
IDbDataParameter userPinUp =
updateCmd.CreateParameter();
userPinUp.ParameterName = "@userPin";
userPinUp.DbType = DbType.String;
updateCmd.Parameters.Add(userIdUp);
updateCmd.Parameters.Add(userNameUp);
updateCmd.Parameters.Add(userPinUp);
insertCmd = conn.CreateCommand();
insertCmd.CommandText = "INSERT INTO users (userId,
userName, userPin) VALUES (@userId, @userName, @userPin)";
IDbDataParameter userIdIn =
insertCmd.CreateParameter();
userIdIn.ParameterName = "@userId";
userIdIn.DbType = DbType.Int32;
IDbDataParameter userNameIn =
insertCmd.CreateParameter();
userNameIn.ParameterName = "@userName";
userNameIn.DbType = DbType.String;
IDbDataParameter userPinIn =
insertCmd.CreateParameter();
userPinIn.ParameterName = "@userPin";
userPinIn.DbType = DbType.String;
insertCmd.Parameters.Add(userIdIn);
insertCmd.Parameters.Add(userNameIn);
insertCmd.Parameters.Add(userPinIn);
while (objBinaryReaderRx.BaseStream.Position <
PacketSize)
{

int userId = objBinaryReaderRx.ReadInt32();
string userName = objBinaryReaderRx.ReadString();
string userPIN = objBinaryReaderRx.ReadString();

userIdPar.Value = userId;

readerCheck = checkCmd.ExecuteReader();

if (readerCheck.Read())
{
userIdUp.Value = userId;
userNameUp.Value = userName;
userPinUp.Value = userPIN;

updateCmd.ExecuteNonQuery();
}
else
{
userIdIn.Value = userId;
userNameIn.Value = userName;
userPinIn.Value = userPIN;

insertCmd.ExecuteNonQuery();

}

readerCheck.Close();
}
transaction.Commit();

return true;

catch{...}
finally{...}

The first "INSERT" works fine, but the second time I get: "Operation is
not valid due to the current state of the object."

I am using .NET 1.1, Finisar 1.1(as Database Driver) and SQLite 3

Thanks in advance
Dec 14 '06 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Jo Voordeckers | last post by:
Hello all, I'm pretty new to the Java newsgroups so I apologize for dropping this into several maybe offtopic groups. I'm sorry! So on to my problem... I've come to a point in our RMI...
0
by: Oleg Paraschenko | last post by:
Hello, I'd like to introduce an article which might be of some interest: Reusing XML Processing Code in non-XML Applications HTML: http://uucode.com/texts/genxml/genxml.html PDF: ...
0
by: Mike | last post by:
Greetings, I have a COM+ component whose integrity I would like to maintain. In other words, I do not want to rebuild it in the .net environment. How do I go about reusing the DLL in a .net...
9
by: Alan | last post by:
Using VC++ (1998) compiler with PFE32 editor in Win2K Pro SP4. (DigitalMars CD on order. ) The program (below) instantiates a class and then deletes it. I would have thought that reusing the...
7
by: Klaus Johannes Rusch | last post by:
Is the following code valid and supported by current implementations? function somename() { this.show = function () { document.write("somename called") } } var somename = new somename();...
4
by: Old Wolf | last post by:
#include <stdio.h> #include <stdarg.h> Is this safe: void foo(const char *fmt, ...) { va_list ap; va_start(ap,fmt);
2
by: Andy Fish | last post by:
Hi, With languages like c# having namespaces, I was wondering what's the recommended practice for reusing source code. I'm not talking big stuff like log4net that's version controlled and...
3
by: Vik | last post by:
I use the same dataadapters and datasets on a few aspx pages. Currently I create these data controls on each page. Is it possible to create these controls only once and then use them on the...
13
by: GGawaran | last post by:
First off, Hi everyone new to .asp and am trying to self teach myself. Im trying to figure out what exactly im doing wrong, or maybe what I think I can do, I really cant. The Idea....
3
by: Simone | last post by:
Hello I am pretty new to asp.net :) I have a program that loops through a few sql statements and performs inserts. The problem is the sql statements are different for every upcoming new...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.