|
|
The problem is that SQL server CE (3.5) doesn't support the IF statement...
I'm busy updating some table date and I need to do a check if a row exists: if it does it needs to be updated, otherwise the data needs to be inserted.
Does anyone have ideas of how I can do this in SQL?
I could do it in code, but it seems inefficient as it would be a two step process.
Thx!
|
|
|
|
|
Jacquers wrote: I could do it in code, but it seems inefficient as it would be a two step process.
Forget worrying about this with regard to SQL "Server" CE - it's an in-process database so you're pretty much just calling into a DLL.
Something that few developers seem to be aware of is that "SQL" "Server" CE is particularly poor at using SQL. You are much, much better served by binding directly to a table using a SqlCeCommand with the CommandType property set to CommandType.TableDirect and the IndexName property set to the name of the index you want to search by, and calling ExecuteResultSet to get a SqlCeResultSet object. Specify ResultSetOptions.Updatable to directly edit the bound table.
You can then use the Seek method to move to a specific row. If it's there, you can use indexing operations or the SetValue/SetValues/Setdatatype methods to set the values of the row and call Update to commit the change. If not, you can create a new row by creating a SqlCeUpdatableRecord (by calling CreateRecord on the resultset), setting its values, and calling Insert on the resultset to insert it.
For more information and benchmarks, see SQL Compact Edition Insert Performance[^] from PocketPCDN.com.
DoEvents: Generating unexpected recursion since 1991
|
|
|
|
|
Thank you for the info! I'll check out the link as well 
|
|
|
|
|
When i run a crystal report file from vb one error like
' SERVER YET NOT OPEN'
comes how to solve it?
|
|
|
|
|
This means the crystal report is unable to connect with the database to genrate the report. The problem could be the file path, database password, etc.
Yogesh Agarwal
|
|
|
|
|
Hi guys, I am beginner in ASP.NET Programming. I want to know one thing, what is think really simple. I have a textbox and a button, and I would like the text what will be entered in the textbox to be added in the table in the database. If is possible, in C# language, cause I am using that language. Thanks ahead
|
|
|
|
|
laziale wrote: I am beginner
Now that you've said that, I recommend you to do some research using google.
Give a man a fish, he'll eat for a day. Teach a man how to fish, he'll eat for lifetime.
Pradeep Joe
|
|
|
|
|
I did it, but the results there are not really helping to me.
|
|
|
|
|
use the following : -
<br />
<br />
1. Import the SqlClient namespace<br />
<br />
2. Create the object of SQL Connect to bind with SQLServer<br />
<br />
SQLConnection con=new SQLConnection("Initial Catelog=test;Data Source=.;user id=sa;password=pwd");<br />
<br />
here <br />
<br />
Data Source is the computer name on which the database in created<br />
Initial Catelog is the database name available on the server<br />
user id is the user id for database as on password<br />
<br />
<br />
3. Now create the command object to give the sql command to the sever<br />
<br />
SQLCommand cmd=new SQLCommand(sqlstr,con);<br />
<br />
here con is the object for connection and sqlstr is the query to execute like in our case it is<br />
<br />
"insert into tbltest values ('" + txtname.text + "')"<br />
<br />
4. Now Execute the command using cmd object<br />
<br />
cmd.ExecuteNonQuery();<br />
<br />
<br />
Remember to open the connection using <br />
con.Open()<br />
before xecution of Query.<br />
<br />
<br />
If fell any trouble then plz reply
With Regards
Yogesh Agarwal
|
|
|
|
|
and what should I write for the button onclick action??
Btw thx for the previous reply, I appreciated, and it really help me.
and I get one error for connection con.open:
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
|
|
|
|
|
The code i gave could be written on button click event and if you receive this message then check you connection string is it correct??
Yogesh Agarwal
|
|
|
|
|
Thx man, it works now for me, I learn something new today. Have a nice time till the next chat and best wishes
|
|
|
|
|
Since comma is a delimiter in sql queries, are there any way that let me insert a float that has a comma as decimalsign
for example the table is:
testtable
col1 varchar(20);
col2 float;
insert into testtable ("blah", 2,2)
this of course wouldnt work since 2,2 acts as two values not as one
the european computers have comma set in the regional settings. so this could be problematic unless i change the decimal to a dot.
|
|
|
|
|
Deques wrote: the european computers have comma set in the regional settings. so this could be problematic unless i change the decimal to a dot.
The only way this is going to be a problem is if you are injecting values in to the SQL string. If you use parameters this is not an issue and nor does it blow a massive security hole in your application.
Please read this article on SQL Injection Attacks and how to prevent them[^]
|
|
|
|
|
hello,
i'm doing project 'check status bus express via SMS'. the connection is between handphone and GSM modem.right now,i didnt know how to search the database in MS Access using gsm modem(FARGO MAESTRO 100).
If i in wrong forum,can someone point out which forum should i entered..
with regard,
ieyra
|
|
|
|
|
Hello I have problem deleting rows
Below is the function I call when the user explicitely ask updade for it's changes
The dataset is the datasource of a datagridview
The first time the function is called it works fine
But if it is called a second time with the same datagrid view I got the error
{"Concurrency violation: the DeleteCommand affected 0 of the expected 1 records."}
But if I make another select between the two call : no error
I probabely miss something ?
private void do_update()<br />
{<br />
<br />
MySqlCommandBuilder cb = new MySqlCommandBuilder(adaAddr);<br />
<br />
DataSet ch = new DataSet();<br />
ch = dsAddr.GetChanges(DataRowState.Deleted);<br />
if (null != ch)<br />
{<br />
int del=adaAddr.Update(ch);
lbl_delcnt.Text = del.ToString();<br />
}<br />
<br />
ch = dsAddr.GetChanges(DataRowState.Modified);<br />
if (ch != null)<br />
{<br />
int upd=adaAddr.Update(ch);<br />
lbl_updcnt.Text = upd.ToString();<br />
}<br />
<br />
ch = dsAddr.GetChanges(DataRowState.Added);<br />
if (ch != null)<br />
{<br />
int add=adaAddr.Update(ch);<br />
lbl_addcnt.Text = add.ToString();<br />
}<br />
<br />
MySqlCommand cmd = new MySqlCommand("DELETE from tours where addrID not in ( select addrID from address );", conn);<br />
int tourdel=cmd.ExecuteNonQuery();<br />
lbl_deltourcnt.Text = tourdel.ToString();<br />
<br />
return;
|
|
|
|
|
Hi Guys,
Accedientally i delted a record in table when i was trying to do it on a temporary table.How do i recover the data in the table.Could you please let me know to ROll Back my data in table.
Your help is much appreciated.
Thank you
Kibrom
|
|
|
|
|
Did you take regular backups of your database? If so, restore from the last backup.
If not, then you've lost it forever. Maybe this will teach you the importance of taking regular backups.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
Hello there guys.
Im experiencing a problem with an update statement and this is the error I get when trying to log into my website.
Error Occured:
Error # -2147467259 Operation must use an updateable query.
SQL = UPDATE tbl_employees SET lastlogin = #28-Feb-2008# WHERE employeelogin = 'SIPHOSIBUYANE'
I tried to check the permission(They are correct), files(They are in the right places).Now I dont knw what could be the problem.
Here is the update statement.
Private function RunSQL(ByVal sql,ByRef myRS)
'Response.Write sql & "
"
On Error Resume Next
if lcase(left(trim(sql),6)) = "select" then
set myRS = server.CreateObject("adodb.recordset")
myRS.open sql,conn,3,3
else
select case lcase(left(trim(sql),6))
case "update", "delete"
if instr(lcase(sql),"where") = 0 then
response.write "Dork, you tried to run a "& lcase(left(trim(sql),6)) &" query without the where clause!"
boolDoNotRunQuery = true
response.end
end if
end select
if boolDoNotRunQuery <> true then
set myRS = conn.Execute(sql)
end if
end if
if err.number <> 0 then
Response.Clear
Response.Write "Error Occured:
"
Response.Write "Error # " & CStr(Err.Number) & " " & Err.Description & "
"
Response.Write "SQL = " & sql & "
"
Response.End
End if
On Error Goto 0
end Function
Private function DoSQL(ByVal sql)
'Response.write sql & "
"
On Error Resume Next
boolDoNotRunQuery = FALSE
actionWord = left(trim(sql),6)
select case lcase(actionWord)
case "update", "delete"
if instr(lcase(sql),"where") = 0 then
response.write "Dork, you tried to run a "& lcase(left(trim(sql),6)) &" query without the where clause!"
boolDoNotRunQuery = true
response.end
end if
end select
kagiso
|
|
|
|
|
This site is working fine from the server and on the computer(Local host), so I only copied the files across to the ftp folder of the web hosting company and the problem begun.
What should I do??
Thanx in advance.
kagiso
|
|
|
|
|
REMOTE is a Linked Server on HEADOFFICE
When I execute this T-SQL script on HEADOFFICE to test the connection :-
SET XACT_ABORT ON<br />
begin distributed tran<br />
select * from REMOTE.master.sys.sysprocesses<br />
commit tran<br />
SET XACT_ABORT OFF
I get the following error message :-
Msg 8501, Level 16, State 2, Line 1<br />
MSDTC on server 'REMOTE' is unavailable.
Both servers have DTC running and I checked that the firewalls have ports 1433 and 1434 open.
Any ideas would be appreicated.
Steve Jowett
-------------------------
It is offen dangerous to try and see someone else's point of view, without proper training. Douglas Adams (Mostly Harmless)
|
|
|
|
|
Hi,
can any one tell me what is the Difference between SQLSERVER 2000 and SQLSERVER 2005
With Regards,
Anu
|
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
