10 Replies - 716 Views - Last Post: 04 February 2013 - 03:03 PM Rate Topic: -----

#1 dinver21  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 10
  • Joined: 19-January 13

Error on St_ID data

Posted 19 January 2013 - 10:36 AM

Private Sub Form_Load()
DTPicker1.Value = DateValue(Now)
connect

    SQL = "select St_ID from Student_Info"
    Set rs = conn.Execute(SQL, adExecuteNoRecords)
    If rs.EOF = False Then
    UserID.Caption = Format(rs!St_ID.Value, "0000")
    End If
    
connectclose
End Sub

Private Sub cmdSib_Click(Index As Integer)
If Text2.Text = "" Or Text3.Text = "" Or Text4.Text = "" Or Text5.Text = "" Or Text7.Text = "" Or Text8.Text = "" Or Combo1 = "" Or Combo2 = "" Or Combo3 = "" Or Combo4 = "" Then
MsgBox "Please complete the information need.", vbExclamation, "Warning"
Else
connect
    SQL = "INSERT INTO [Student_Info](St_ID,St_SName,St_FName,St_Mname,St_Gender,St_Year,St_Section,St_SY,St_Age,St_BDay,St_PName,St_PPhone,St_DateReg) VALUES ('" & UserID.Caption & "','" & StrConv(Text2.Text, vbUpperCase) & "','" & StrConv(Text3.Text, vbProperCase) & "','" & StrConv(Text4.Text, vbProperCase) & "','" & Combo1 & "','" & Combo2 & "','" & Combo4 & "','" & Text5.Text & "','" & Combo3 & "','" & DTPicker2 & "','" & StrConv(Text7.Text, vbProperCase) & "','" & Text8.Text & "','" & DTPicker1 & "')"
    Set rs = conn.Execute(SQL, adExecuteNoRecords)
    SQL = "update [Student_Info](St_ID) set St_ID=St_ID+1"
    Set rs = conn.Execute(SQL, adExecuteNoRecords)
    MsgBox "New information created.", vbInformation, "Success"
connectclose
Me.Hide
End If
End Sub


Public Sub connect()
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\Sonata\diNvEr!\Thesis\ThesisProgram\Final Thesis Program\Thesis.mdb;Persist Security Info=False"
conn.CursorLocation = adUseClient
conn.Mode = adModeReadWrite
conn.Open
End Sub

Public Sub connectclose()
Set rs = Nothing
conn.Close
End Sub



Need help here... am just workin with this in the past 3days... i cant get along the ID Code... i wanted to start it to 0000 until 9999.. during registration, ID code must start to 0000 and save it on my database access.. then next will have 0001,0002,0003....

any help will be appreciated! tns and God bless

This post has been edited by BobRodes: 04 February 2013 - 03:03 PM
Reason for edit:: Please use code tags in future.


Is This A Good Question/Topic? 0
  • +

Replies To: Error on St_ID data

#2 andrewsw  Icon User is offline

  • Fire giant boob nipple gun!
  • member icon

Reputation: 2890
  • View blog
  • Posts: 9,597
  • Joined: 12-December 12

Re: Error on St_ID data

Posted 19 January 2013 - 11:58 AM

PLEASE wrap your code in [code ] tags - the instructions are in the area where you are typing your post.

SQL = "update [Student_Info](St_ID) set St_ID=DMAX('St_ID','[Student_Info]')+1"


Does [Student_Info](St_ID) work? It's normally Student_Info!St_ID or Student_Info.St_ID.
Was This Post Helpful? 0
  • +
  • -

#3 dinver21  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 10
  • Joined: 19-January 13

Re: Error on St_ID data

Posted 20 January 2013 - 06:42 AM

#andrewsw

Thanks for the response Sir but nothings happen... my UserID.caption didn't display anything.. ive tried ....
SQL = "update [Student_Info](St_ID) set St_ID=DMAX('St_ID','[Student_Info]')+1"
and tried also to change the
[Student_Info](St_ID) to Student_Info!St_ID or Student_Info.St_ID
Format.. nothings happen...
Was This Post Helpful? 0
  • +
  • -

#4 andrewsw  Icon User is offline

  • Fire giant boob nipple gun!
  • member icon

Reputation: 2890
  • View blog
  • Posts: 9,597
  • Joined: 12-December 12

Re: Error on St_ID data

Posted 20 January 2013 - 07:14 AM

You've told it to execute a command that returns no records, but you are assigning the return result:

Set rs = conn.Execute(SQL, adExecuteNoRecords)  'change to
conn.Execute SQL, adExecuteNoRecords

although adExecuteNoRecords is probably not required.

Your SQL statement has no WHERE clause so it will update ALL OF the records!!

This also assumes that St_ID is numerical data and has at least one value set.

This post has been edited by andrewsw: 20 January 2013 - 07:12 AM

Was This Post Helpful? 0
  • +
  • -

#5 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 571
  • View blog
  • Posts: 2,979
  • Joined: 19-May 09

Re: Error on St_ID data

Posted 31 January 2013 - 11:38 PM

View Postandrewsw, on 19 January 2013 - 12:58 PM, said:

PLEASE wrap your code in [code ] tags - the instructions are in the area where you are typing your post.

SQL = "update [Student_Info](St_ID) set St_ID=DMAX('St_ID','[Student_Info]')+1"


Does [Student_Info](St_ID) work? It's normally Student_Info!St_ID or Student_Info.St_ID.

Andrew, neither of those constructs will work in SQL, nor will the OP's. You're confusing SQL and ADO. dinver, just google "SQL update syntax".
Was This Post Helpful? 0
  • +
  • -

#6 kai_itz me  Icon User is offline

  • D.I.C Head

Reputation: 28
  • View blog
  • Posts: 159
  • Joined: 03-August 12

Re: Error on St_ID data

Posted 01 February 2013 - 12:28 AM

Quote

Andrew, neither of those constructs will work in SQL, nor will the OP's. You're confusing SQL and ADO. dinver, just google "SQL update syntax".


Mr.BOB is absolutely right. look at this (if u are using (sql server))

INSERT COMMAND

INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...)



UPDATE COMMAND
UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value

Was This Post Helpful? 0
  • +
  • -

#7 andrewsw  Icon User is offline

  • Fire giant boob nipple gun!
  • member icon

Reputation: 2890
  • View blog
  • Posts: 9,597
  • Joined: 12-December 12

Re: Error on St_ID data

Posted 01 February 2013 - 03:53 AM

Quote

look at this (if u are using (sql server))

The OP is not using SQL Server, he is using an Access (.mdb) file.

But it seems that we cannot use the Access DMAX function from an external application.

This post has been edited by andrewsw: 01 February 2013 - 03:59 AM

Was This Post Helpful? 0
  • +
  • -

#8 dinver21  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 10
  • Joined: 19-January 13

Re: Error on St_ID data

Posted 02 February 2013 - 04:52 AM

tnx Man!! i Got IT!! ^^,
#Fault on Syntaxes
Was This Post Helpful? 0
  • +
  • -

#9 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 571
  • View blog
  • Posts: 2,979
  • Joined: 19-May 09

Re: Error on St_ID data

Posted 03 February 2013 - 05:09 PM

View Postandrewsw, on 01 February 2013 - 04:53 AM, said:

Quote

look at this (if u are using (sql server))

The OP is not using SQL Server, he is using an Access (.mdb) file.

But it seems that we cannot use the Access DMAX function from an external application.

I didn't say he was using SQL Server, I said he was using SQL. Was I wrong? :) As I saw it, he had a syntax error in his update statement. Specifically, he was confusing the way parentheses are used with the way they are in the input statement. Dinver, well done on following through and finding your syntax error.

This post has been edited by BobRodes: 03 February 2013 - 05:10 PM

Was This Post Helpful? 0
  • +
  • -

#10 andrewsw  Icon User is offline

  • Fire giant boob nipple gun!
  • member icon

Reputation: 2890
  • View blog
  • Posts: 9,597
  • Joined: 12-December 12

Re: Error on St_ID data

Posted 03 February 2013 - 07:12 PM

BobRodes:

Quote

I didn't say he was using SQL Server, I said he was using SQL. Was I wrong?

No :). I just wanted to clarify that the OP wasn't using SQL Server, (referring to kai_itz me's post #6) for anyone else who might come across this thread, even though the two sample statements work with both Access and SQL Server.

[I had also hinted at the syntax error in my post #2, but I should have been more explicit!]

BTW Thanks @Bob for your support on another thread :flowers:
Was This Post Helpful? 0
  • +
  • -

#11 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 571
  • View blog
  • Posts: 2,979
  • Joined: 19-May 09

Re: Error on St_ID data

Posted 04 February 2013 - 03:03 PM

Oh, I see now. Good point.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1