hie, i'm using asp.net (using vb.net)
i developed a function to add some information into the database. it's working fine. but i need help on how to warn the user if the
time on that particular selected date has already been used. (example: 1pm on 4/8/2005 has a schedule in it already. so it can't be used to store something else)
this is my code.
hie,
thanks for the replies! i will try it out. but first i would like to ask about the connection string.
you see, currently i'm using data tools to connect the database. so i do not know how to use the connection strings and how to link it with the sql statement. (i'm bad in programming)
i'm using "Provider = Microsoft.Jet.OleDb.4.0;......"
can anyone teach me how to set up my connection string and sql statement if i don't use data tools? if i could do this then i can use sql statements. thanks.
thanks SonuKapoor for the link. i tried it out. but i got an error. [:'(] this is my code:
Dim db, rs, sSQL
Dim title, username As String
Dim data As Date
Dim schedule As String
Dim time As Integer
title = txttitle.Text
data = Calendar1.SelectedDate
schedule = txtschedule.Text
time = ddl1.SelectedValue
username = Session("UserName")
sSQL = "IF EXSIST(SELECT 'True' FROM Schedule where Date = '" & data & "', AND Time = '" & time & "')"
sSQL = sSQL & "BEGIN"
sSQL = sSQL & "SELECT 'This record already exists!'"
sSQL = sSQL & "END ELSE BEGIN"
sSQL = sSQL & "INSERT INTO Schedule(username, data, time, schedule) VALUES('" & username & "', '" & data & "', '" & time & "', '" & schedule & "')"
sSQL = sSQL & "END"
well, your problem is that you're trying to feed the VB.Net compiler vbscript. It just isnt going to work. On top of that, it has syntax errors for vbscript. Furthermore, even if you had your object creation right, Access does not to my knowledge support
anything similar to that form of sql.
Ok, the first step in fixing this code is to turn on Option Strict and Option explicit.
For embeded code, change your page declaration from
then how do i check for exsting records (such as same time and day), with my code here? then the function won't add the new row, if there are already the same time and day in the database. thanks
hot_choc
Member
400 Points
80 Posts
How to check if data already exist in database
Apr 08, 2005 09:55 PM|LINK
i developed a function to add some information into the database. it's working fine. but i need help on how to warn the user if the time on that particular selected date has already been used. (example: 1pm on 4/8/2005 has a schedule in it already. so it can't be used to store something else)
this is my code.
dsSchedule.Schedule.Clear()
daSchedule.Fill(dsSchedule)
Dim dr As DataRow = dsSchedule.Tables(0).NewRow()
dr("Title") = txttitle.Text
dr("Date") = Calendar1.SelectedDate
dr("Schedule") = txtschedule.Text
dr("Time") = ddl1.SelectedValue
dr("Username") = Session("UserName").ToString()
dsSchedule.Tables(0).Rows.Add(dr)
daSchedule.Update(dsSchedule)
txttitle.Text = ""
txtschedule.Text = ""
lbladd.Visible = True
lbladd.Text = "Appointment succesfully added!"
please help me out here. thanks! [:D]
SonuKapoor
Star
13757 Points
2655 Posts
MVP
Re: How to check if data already exist in database
Apr 09, 2005 12:54 AM|LINK
ASP.NET News and Articles For Lazy Developers |Ajax Data Controls Forum
azamsharp
All-Star
24614 Points
4612 Posts
Re: How to check if data already exist in database
Apr 09, 2005 03:39 AM|LINK
In this case I would suggest that you should have an active column in the database table which will denote that if the slot has been taken or not.
This will also makes it easier to find all the available slots since than you will only run a query like this:
SELECT * FROM Table WHERE Active = 1;
Which will return all the slots on which bookings have been done.
HighOnCoding
hot_choc
Member
400 Points
80 Posts
Re: How to check if data already exist in database
Apr 09, 2005 09:27 AM|LINK
thanks for the replies! i will try it out. but first i would like to ask about the connection string.
you see, currently i'm using data tools to connect the database. so i do not know how to use the connection strings and how to link it with the sql statement. (i'm bad in programming)
i'm using "Provider = Microsoft.Jet.OleDb.4.0;......"
can anyone teach me how to set up my connection string and sql statement if i don't use data tools? if i could do this then i can use sql statements. thanks.
hot_choc
Member
400 Points
80 Posts
Re: How to check if data already exist in database
Apr 09, 2005 09:34 PM|LINK
Dim db, rs, sSQL
Dim title, username As String
Dim data As Date
Dim schedule As String
Dim time As Integer
title = txttitle.Text
data = Calendar1.SelectedDate
schedule = txtschedule.Text
time = ddl1.SelectedValue
username = Session("UserName")
sSQL = "IF EXSIST(SELECT 'True' FROM Schedule where Date = '" & data & "', AND Time = '" & time & "')"
sSQL = sSQL & "BEGIN"
sSQL = sSQL & "SELECT 'This record already exists!'"
sSQL = sSQL & "END ELSE BEGIN"
sSQL = sSQL & "INSERT INTO Schedule(username, data, time, schedule) VALUES('" & username & "', '" & data & "', '" & time & "', '" & schedule & "')"
sSQL = sSQL & "END"
db = Server.CreateObject("Provider = Microsoft.Jet.OleDb.4.0;" & "data source = C:\Inetpub\wwwroot\AMFinal\Profile.mdb;")
db.Open()
rs = db.Execute(sSQL)
If rs(0) = "This record already exists!" Then
lbladd.Text = "Please select another time"
Else
lbladd.Text = "Your appointment has been added"
End If
i got this error:
Could not create an object of type 'Provider = Microsoft.Jet.OleDb.4.0;data source = C:\Inetpub\wwwroot\AMFinal\Profile.mdb;'.
Line 127: db = Server.CreateObject("Provider = Microsoft.Jet.OleDb.4.0;" & "data source = C:\Inetpub\wwwroot\AMFinal\Profile.mdb;")could you please tell me where i went wrong? thanks!
JeffreyABeck...
All-Star
16423 Points
3329 Posts
Re: How to check if data already exist in database
Apr 10, 2005 01:44 PM|LINK
if you turn on Option Strict & Option Explicit, you'll see all sorts of errors in your code.
Warning: Code is often uncompiled and possibly started life written on the back of a napkin. Beware typos.
hot_choc
Member
400 Points
80 Posts
Re: How to check if data already exist in database
Apr 10, 2005 02:19 PM|LINK
JeffreyABeck...
All-Star
16423 Points
3329 Posts
Re: How to check if data already exist in database
Apr 12, 2005 11:27 AM|LINK
well, your problem is that you're trying to feed the VB.Net compiler vbscript. It just isnt going to work. On top of that, it has syntax errors for vbscript. Furthermore, even if you had your object creation right, Access does not to my knowledge support anything similar to that form of sql.
Ok, the first step in fixing this code is to turn on Option Strict and Option explicit.
For embeded code, change your page declaration from
<%@ Page Language="VB" %>
to
<%@ Page Language="VB" Explicit="True" Strict="True" %>
If you're using a code behind, simply put
Option Explicit On
Option Strict On
as the first 2 lines in your code behind file.
This is going to cause a very very large number of errors. It will however shake out bugs that VB will introduce by trying to guess what you mean.
Warning: Code is often uncompiled and possibly started life written on the back of a napkin. Beware typos.
JeffreyABeck...
All-Star
16423 Points
3329 Posts
Re: How to check if data already exist in database
Apr 12, 2005 11:53 AM|LINK
Now we move on to Converting your old ADO code to ADO.Net.
To Start, in VB.Net, we no longer use Server.CreateObject to Create Database Objects.
Honestly, i'd suggest getting a good book on VB.Net, another on ADO.Net
Warning: Code is often uncompiled and possibly started life written on the back of a napkin. Beware typos.
hot_choc
Member
400 Points
80 Posts
Re: How to check if data already exist in database
Apr 13, 2005 01:46 PM|LINK
dsSchedule.Schedule.Clear()
daSchedule.Fill(dsSchedule)
Dim dr As DataRow = dsSchedule.Tables(0).NewRow()
dr("Title") = txttitle.Text
dr("Date") = Calendar1.SelectedDate
dr("Schedule") = txtschedule.Text
dr("Time") = ddl1.SelectedValue
dr("Username") = Session("UserName").ToString()
dsSchedule.Tables(0).Rows.Add(dr)
daSchedule.Update(dsSchedule)
txttitle.Text = ""
txtschedule.Text = ""
lbladd.Visible = True
lbladd.Text = "Appointment succesfully added!"
then how do i check for exsting records (such as same time and day), with my code here? then the function won't add the new row, if there are already the same time and day in the database. thanks