Sql insert and update problem.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Sql insert and update problem.

Author

Topic

190Benz
Starting Member

3 Posts

Posted - 2007-04-25 : 05:40:43

I have a problem with my online word search engine.
I want to be able to insert and update words with one button.
I did this by first using a select to see if the word exists in the database. Here is my source code. I can only either always update or always insert.

Public Sub VertaalVanEngels(ByRef Engels As String, ByRef Nederlands As String, ByRef Grieks As String, ByRef Slovaaks As String, byref Zweeds as string)
SqlConnection1.Open()
cmdSelectEng.Parameters("@EngelsParam").Value = Engels
Dim dr As SqlClient.SqlDataReader
Dim woordbestaat As Boolean = True
dr = cmdSelectEng.ExecuteReader
dr.Read()
Try

dr.Close()
SqlConnection1.Close()
Catch ex As Exception
'woordbestaat = False
Throw New Exception("Er is geen engels woord gevonden")
End Try
Engels = dr("Engels")
Nederlands = dr("Nederlands")
Grieks = dr("Grieks")
Slovaaks = dr("Slovaaks")
Zweeds = dr("Zweeds")
dr.Close()
SqlConnection1.Close()

Public Sub InsertZweed(ByVal Engels As String, ByVal Nederlands As String, ByVal Grieks As String, ByVal Slovaaks As String, ByVal Zweeds As String)

SqlConnection1.Open()

SqlInsertZwe.Parameters.Item("@EngelsParam").Value = Engels
SqlInsertZwe.Parameters.Item("@NederlandsParam").Value = Nederlands
SqlInsertZwe.Parameters.Item("@GrieksParam").Value = Grieks
SqlInsertZwe.Parameters.Item("@SlovaaksParam").Value = Slovaaks
SqlInsertZwe.Parameters.Item("@ZweedsParam").Value = Zweeds
SqlInsertZwe.ExecuteNonQuery()

SqlConnection1.Close()

End Sub

Public Sub UpdateZweeds(ByVal Engels As String, ByVal Nederlands As String, ByVal Grieks As String, ByVal Slovaaks As String, ByVal Zweeds As String)

SqlConnection1.Open()

SqlUpdateZwe.Parameters.Item("@EngelsParam").Value = Engels
SqlUpdateZwe.Parameters.Item("@ZweedsParam").Value = Zweeds
SqlUpdateZwe.ExecuteNonQuery()

SqlConnection1.Close()

End Sub

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Dim woordbestaat As Boolean '= True
Dim woorden As New transacties
Dim Engels, Nederlands, Grieks, Slovaaks, Zweeds As String
Dim engelswoord, nederlandswoord, griekswoord, slovaakswoord, zweedswoord As String
engelswoord = txtEngels.Text
Zweeds = txtZweeds.Text
Nederlands = ""
Slovaaks = ""
Grieks = ""
Engels = engelswoord

Try
woorden.VertaalVanEngels(engelswoord, nederlandswoord, griekswoord, slovaakswoord, zweedswoord)
Catch ex As Exception
'woordbestaat = False
End Try

If woordbestaat = True Then
woorden.UpdateZweeds(Engels, Nederlands, Grieks, Slovaaks, Zweeds)
Else
woorden.InsertZweed(Engels, Nederlands, Grieks, Slovaaks, Zweeds)
End If
End Sub

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-04-25 : 08:03:24

quote:
I can only either always update or always insert.

Why is that? Can't you do this with a stored procedure? The code you have shown us looks like asp.net, not T_SQL. Are you getting an error somewhere?

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

190Benz
Starting Member

3 Posts

Posted - 2007-04-26 : 02:59:30

I made a mistake it's in asp.
I am not getting an error.

But i can insert or add by changing the woordbestaat to true or false.

But how can i check if the word excists or not and put this in a value in asp?

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-04-26 : 07:19:34

If the word exists in the database? Pass the word into a stored procedure that checks for its existance. Then return a 0 or 1 back to your calling application to let it know if the word exists or not. Then you can either update or insert as needed.
Another way is simply passing in all you need to a stored procedure, and letting the procedure decide to either update or insert.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

190Benz
Starting Member

3 Posts

Posted - 2007-04-26 : 17:01:52

What is the procedure to check if something excists or doesnt in a database?
Or how do you give this value to a defined variable?

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-04-27 : 09:59:26

[code] IF EXISTS (Select [Your criteria] FROM YourDatabase)
BEGIN
RETURN -1
END
[/code]

That is a very simple exists check. You may want to tune it a bit. Also, try following the links in my signature. They cover a LOT of the basic SQL tasks.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources