473,834 Members | 2,248 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

VB.Net WindowsApplicat ion Save Access Database Record

1 New Member
Friends,
I'm probably being more critical with VB.Net Windows application.
I have Developed VisualStudio 20005 VB.Net Windows application
how willl i be able to save a specific record into my database file throu GUI Save Record button?
As i write the comand as foloow but it is not inserting the new record in to the Access Database.

Public Function Open_Connection () As Boolean
Try
Select Case DBInfo(No).Data base_Type
Case Database_Type.O racelDB
Select Case No
Case 0 : If Cn_Def.State = ConnectionState .Open Then Cn_Def.Close()
Cn_Def.Connecti onString = "Provider=MSDAO RA.1;Password=" & DBInfo(No).Pass word & ";User ID=" & DBInfo(No).User _Name & ";Data Source=" & DBInfo(No).Serv er_Name & ";"
Cn_Def.Open()
Case 1 : If Cn1.State = ConnectionState .Open Then Cn1.Close()
Cn1.ConnectionS tring = "Provider=MSDAO RA.1;Password=" & DBInfo(No).Pass word & ";User ID=" & DBInfo(No).User _Name & ";Data Source=" & DBInfo(No).Serv er_Name & ";"
Cn1.Open()
Case 2 : If Cn2.State = ConnectionState .Open Then Cn2.Close()
Cn2.ConnectionS tring = "Provider=MSDAO RA.1;Password=" & DBInfo(No).Pass word & ";User ID=" & DBInfo(No).User _Name & ";Data Source=" & DBInfo(No).Serv er_Name & ";"
Cn2.Open()
End Select

Case Database_Type.S QLServer
Select Case No
Case 0 : If Cn_Def.State = ConnectionState .Open Then Cn_Def.Close()
Cn_Def.Connecti onString = "Provider=SQLOL EDB.1;Integrate d Security=SSPI;P ersist Security Info=True;Initi al Catalog=" & DBInfo(No).Data base_Name & ";Data Source=" & DBInfo(No).Serv er_Name & ";Password= " & DBInfo(No).Pass word & ";User ID=" & DBInfo(No).User _Name & ";Use Procedure for Prepare=1;Auto Translate=True; Packet Size=4096;Works tation ID=" & DBInfo(No).Serv er_Name & ";Use Encryption for Data=False;Tag with column collation when possible=False"
Cn_Def.Open()
Case 1 : If Cn1.State = ConnectionState .Open Then Cn1.Close()
Cn1.ConnectionS tring = "Provider=SQLOL EDB.1;Integrate d Security=SSPI;P ersist Security Info=True;Initi al Catalog=" & DBInfo(No).Data base_Name & ";Data Source=" & DBInfo(No).Serv er_Name & ";Password= " & DBInfo(No).Pass word & ";User ID=" & DBInfo(No).User _Name & ";Use Procedure for Prepare=1;Auto Translate=True; Packet Size=4096;Works tation ID=" & DBInfo(No).Serv er_Name & ";Use Encryption for Data=False;Tag with column collation when possible=False"
Cn1.Open()
Case 2 : If Cn2.State = ConnectionState .Open Then Cn2.Close()
Cn2.ConnectionS tring = "Provider=SQLOL EDB.1;Integrate d Security=SSPI;P ersist Security Info=True;Initi al Catalog=" & DBInfo(No).Data base_Name & ";Data Source=" & DBInfo(No).Serv er_Name & ";Password= " & DBInfo(No).Pass word & ";User ID=" & DBInfo(No).User _Name & ";Use Procedure for Prepare=1;Auto Translate=True; Packet Size=4096;Works tation ID=" & DBInfo(No).Serv er_Name & ";Use Encryption for Data=False;Tag with column collation when possible=False"
Cn2.Open()
End Select

Case Database_Type.A ccessDB
Select Case No
Case 0 : If Cn_Def.State = ConnectionState .Open Then Cn_Def.Close()
Cn_Def.Connecti onString = "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=" & DBInfo(No).Data base_Name
Cn_Def.Open()
Case 1 : If Cn1.State = ConnectionState .Open Then Cn1.Close()
Cn1.ConnectionS tring = "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=" & DBInfo(No).Data base_Name
Cn1.Open()
Case 2 : If Cn2.State = ConnectionState .Open Then Cn2.Close()
Cn2.ConnectionS tring = "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=" & DBInfo(No).Data base_Name
Cn2.Open()
End Select
End Select
Return True
Catch ex As Exception
Select Case No
Case 0
If Cn_Def.State = ConnectionState .Open Then
Return True
Else : MsgBox(ex.Messa ge)
If Not Loded Then
Lb_Name1 = "Access Database"
Dim X As New Connection_Frm
X.ShowDialog()
End If
Return False
End If
Case 1
If Cn1.State = ConnectionState .Open Then
Return True
Else : MsgBox(ex.Messa ge)
If Not Loded Then
Lb_Name1 = "Access Database"
Dim X As New Connection_Frm

X.ShowDialog()
End If
Return False
End If
Case 2
If Cn2.State = ConnectionState .Open Then
Return True
Else : Msg = ex.Message
MsgBox(ex.Messa ge, MsgBoxStyle.Cri tical, "Farm Manager")
If Not Loded Then
Lb_Name1 = "Database"
Dim X As New Connection_Frm
X.ShowDialog()
End If
Return False
End If
End Select
'Finally
End Try
End Function
End Module


Public Function Exec(ByVal CN As OleDb.OleDbConn ection, ByVal Statement As String, _
ByVal ParamArray Param As Object()) As Boolean
Try
If CN.State = ConnectionState .Closed Then CN.Open()
Dim Cmd As New OleDb.OleDbComm and(Statement, CN)

Dim i As Integer
For i = 0 To Param.Length - 1
Cmd.Parameters. Add("Param" & i, Param(i))
Next
Cmd.ExecuteNonQ uery()
Catch e As Exception
Dim i As Integer
Dim x As String = ""
For i = 0 To Param.Length - 1
x &= "Parameter( " & i & "): " & Param(i).ToStri ng & vbCrLf
Next
MsgBox(e.Messag e, MsgBoxStyle.Cri tical)
Return False
Finally
End Try
Return True
End Function

SAVE BUTTON TRIGGER AS FOLLOWING


Private Sub Btn_Save_Click( ByVal sender As Object, ByVal e As System.EventArg s) Handles Btn_Save.Click
Dim sql As String

If Status Then
If Trim(Me.Txt_FIS H_SEQ.Text) <> Nothing AndAlso IsNumeric(Me.Tx t_FISH_SEQ.Text ) Then
If Not Found_Prim("FIS HING_TRANSACTIO NS", "FISH_SEQ", Me.Txt_FISH_SEQ .Text) Then

CMDS.Exec(Cn_De f, "Insert into FISHING_TRANSAC TIONS(Fish_Seq, Fish_Code,TRANS ACTION_DATE,MAR KET_CODE,QTY,WH OLSALE_PRICE,RE TAIL_PRICE,TOTA L_PRICE,NATURE, EXPORT,IMPORT) Values(?,?,?,?, ?,?,?,?,?,?,?)" , New Object() {Me.Txt_FISH_SE Q.Text, Me.Cmb_Fish_Cod e.Text, Me.DTP_FSHTRN_D AT.Text, Me.Txt_MKT_CODE .Text, Me.Txt_QTY.Text , Me.Txt_WHOLSALE _PRICE.Text, Me.Txt_RETAIL_P RICE.Text, Me.TXT_TOTAL_PR ICE.Text, Me.TXT_NATURE.T ext, Me.CHK_EXPORT.T ext, Me.CHK_IMPORTED .Text})
Else : MsgBox("Record Exist"MsgBoxSty le.Information, Me.Text)
Me.Txt_FISH_SEQ .Focus()

Me.DTP_FSHTRN_D AT.Focus()
End If

End If
Else


CMDS.Exec(Cn_De f, "Update FISHING_TRANSAC TIONS set FISH_SEQ=?,FISH _CODE=?,TRANSAC TION_DATE=?,MAR KET_CODE=?,QTY= ?,WHOLESALE_PRI CE=?,RETAIL_PRI CE=?,TOTAL_PRIC E=?,NATURE=?,EX PORT=?,IMPORT=? Where FISH_SEQ=" & Me.Txt_FISH_SEQ .Text, New Object() {Me.Txt_FISH_SE Q.Text, Me.Cmb_Fish_Cod e.Text, Me.DTP_FSHTRN_D AT.Text, Me.Txt_MKT_CODE .Text, Me.Txt_QTY.Text , Me.Txt_WHOLSALE _PRICE.Text, Me.Txt_RETAIL_P RICE.Text, Me.TXT_TOTAL_PR ICE.Text, Me.TXT_NATURE.T ext, Me.CHK_EXPORT.T ext, Me.CHK_IMPORTED .Text})

' Cmd_Cancel_Clic k(sender, e)
End If

End Sub
Mar 21 '07 #1
1 3735
vijaydiwakar
579 Contributor
Friends,
I'm probably being more critical with VB.Net Windows application.
I have Developed VisualStudio 20005 VB.Net Windows application
how willl i be able to save a specific record into my database file throu GUI Save Record button?
As i write the comand as foloow but it is not inserting the new record in to the Access Database.

Public Function Open_Connection () As Boolean
Try
Select Case DBInfo(No).Data base_Type
Case Database_Type.O racelDB
Select Case No
Case 0 : If Cn_Def.State = ConnectionState .Open Then Cn_Def.Close()
Cn_Def.Connecti onString = "Provider=MSDAO RA.1;Password=" & DBInfo(No).Pass word & ";User ID=" & DBInfo(No).User _Name & ";Data Source=" & DBInfo(No).Serv er_Name & ";"
Cn_Def.Open()
Case 1 : If Cn1.State = ConnectionState .Open Then Cn1.Close()
Cn1.ConnectionS tring = "Provider=MSDAO RA.1;Password=" & DBInfo(No).Pass word & ";User ID=" & DBInfo(No).User _Name & ";Data Source=" & DBInfo(No).Serv er_Name & ";"
Cn1.Open()
Case 2 : If Cn2.State = ConnectionState .Open Then Cn2.Close()
Cn2.ConnectionS tring = "Provider=MSDAO RA.1;Password=" & DBInfo(No).Pass word & ";User ID=" & DBInfo(No).User _Name & ";Data Source=" & DBInfo(No).Serv er_Name & ";"
Cn2.Open()
End Select

Case Database_Type.S QLServer
Select Case No
Case 0 : If Cn_Def.State = ConnectionState .Open Then Cn_Def.Close()
Cn_Def.Connecti onString = "Provider=SQLOL EDB.1;Integrate d Security=SSPI;P ersist Security Info=True;Initi al Catalog=" & DBInfo(No).Data base_Name & ";Data Source=" & DBInfo(No).Serv er_Name & ";Password= " & DBInfo(No).Pass word & ";User ID=" & DBInfo(No).User _Name & ";Use Procedure for Prepare=1;Auto Translate=True; Packet Size=4096;Works tation ID=" & DBInfo(No).Serv er_Name & ";Use Encryption for Data=False;Tag with column collation when possible=False"
Cn_Def.Open()
Case 1 : If Cn1.State = ConnectionState .Open Then Cn1.Close()
Cn1.ConnectionS tring = "Provider=SQLOL EDB.1;Integrate d Security=SSPI;P ersist Security Info=True;Initi al Catalog=" & DBInfo(No).Data base_Name & ";Data Source=" & DBInfo(No).Serv er_Name & ";Password= " & DBInfo(No).Pass word & ";User ID=" & DBInfo(No).User _Name & ";Use Procedure for Prepare=1;Auto Translate=True; Packet Size=4096;Works tation ID=" & DBInfo(No).Serv er_Name & ";Use Encryption for Data=False;Tag with column collation when possible=False"
Cn1.Open()
Case 2 : If Cn2.State = ConnectionState .Open Then Cn2.Close()
Cn2.ConnectionS tring = "Provider=SQLOL EDB.1;Integrate d Security=SSPI;P ersist Security Info=True;Initi al Catalog=" & DBInfo(No).Data base_Name & ";Data Source=" & DBInfo(No).Serv er_Name & ";Password= " & DBInfo(No).Pass word & ";User ID=" & DBInfo(No).User _Name & ";Use Procedure for Prepare=1;Auto Translate=True; Packet Size=4096;Works tation ID=" & DBInfo(No).Serv er_Name & ";Use Encryption for Data=False;Tag with column collation when possible=False"
Cn2.Open()
End Select

Case Database_Type.A ccessDB
Select Case No
Case 0 : If Cn_Def.State = ConnectionState .Open Then Cn_Def.Close()
Cn_Def.Connecti onString = "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=" & DBInfo(No).Data base_Name
Cn_Def.Open()
Case 1 : If Cn1.State = ConnectionState .Open Then Cn1.Close()
Cn1.ConnectionS tring = "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=" & DBInfo(No).Data base_Name
Cn1.Open()
Case 2 : If Cn2.State = ConnectionState .Open Then Cn2.Close()
Cn2.ConnectionS tring = "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=" & DBInfo(No).Data base_Name
Cn2.Open()
End Select
End Select
Return True
Catch ex As Exception
Select Case No
Case 0
If Cn_Def.State = ConnectionState .Open Then
Return True
Else : MsgBox(ex.Messa ge)
If Not Loded Then
Lb_Name1 = "Access Database"
Dim X As New Connection_Frm
X.ShowDialog()
End If
Return False
End If
Case 1
If Cn1.State = ConnectionState .Open Then
Return True
Else : MsgBox(ex.Messa ge)
If Not Loded Then
Lb_Name1 = "Access Database"
Dim X As New Connection_Frm

X.ShowDialog()
End If
Return False
End If
Case 2
If Cn2.State = ConnectionState .Open Then
Return True
Else : Msg = ex.Message
MsgBox(ex.Messa ge, MsgBoxStyle.Cri tical, "Farm Manager")
If Not Loded Then
Lb_Name1 = "Database"
Dim X As New Connection_Frm
X.ShowDialog()
End If
Return False
End If
End Select
'Finally
End Try
End Function
End Module


Public Function Exec(ByVal CN As OleDb.OleDbConn ection, ByVal Statement As String, _
ByVal ParamArray Param As Object()) As Boolean
Try
If CN.State = ConnectionState .Closed Then CN.Open()
Dim Cmd As New OleDb.OleDbComm and(Statement, CN)

Dim i As Integer
For i = 0 To Param.Length - 1
Cmd.Parameters. Add("Param" & i, Param(i))
Next
Cmd.ExecuteNonQ uery()
Catch e As Exception
Dim i As Integer
Dim x As String = ""
For i = 0 To Param.Length - 1
x &= "Parameter( " & i & "): " & Param(i).ToStri ng & vbCrLf
Next
MsgBox(e.Messag e, MsgBoxStyle.Cri tical)
Return False
Finally
End Try
Return True
End Function

SAVE BUTTON TRIGGER AS FOLLOWING


Private Sub Btn_Save_Click( ByVal sender As Object, ByVal e As System.EventArg s) Handles Btn_Save.Click
Dim sql As String

If Status Then
If Trim(Me.Txt_FIS H_SEQ.Text) <> Nothing AndAlso IsNumeric(Me.Tx t_FISH_SEQ.Text ) Then
If Not Found_Prim("FIS HING_TRANSACTIO NS", "FISH_SEQ", Me.Txt_FISH_SEQ .Text) Then

CMDS.Exec(Cn_De f, "Insert into FISHING_TRANSAC TIONS(Fish_Seq, Fish_Code,TRANS ACTION_DATE,MAR KET_CODE,QTY,WH OLSALE_PRICE,RE TAIL_PRICE,TOTA L_PRICE,NATURE, EXPORT,IMPORT) Values(?,?,?,?, ?,?,?,?,?,?,?)" , New Object() {Me.Txt_FISH_SE Q.Text, Me.Cmb_Fish_Cod e.Text, Me.DTP_FSHTRN_D AT.Text, Me.Txt_MKT_CODE .Text, Me.Txt_QTY.Text , Me.Txt_WHOLSALE _PRICE.Text, Me.Txt_RETAIL_P RICE.Text, Me.TXT_TOTAL_PR ICE.Text, Me.TXT_NATURE.T ext, Me.CHK_EXPORT.T ext, Me.CHK_IMPORTED .Text})
Else : MsgBox("Record Exist"MsgBoxSty le.Information, Me.Text)
Me.Txt_FISH_SEQ .Focus()

Me.DTP_FSHTRN_D AT.Focus()
End If

End If
Else


CMDS.Exec(Cn_De f, "Update FISHING_TRANSAC TIONS set FISH_SEQ=?,FISH _CODE=?,TRANSAC TION_DATE=?,MAR KET_CODE=?,QTY= ?,WHOLESALE_PRI CE=?,RETAIL_PRI CE=?,TOTAL_PRIC E=?,NATURE=?,EX PORT=?,IMPORT=? Where FISH_SEQ=" & Me.Txt_FISH_SEQ .Text, New Object() {Me.Txt_FISH_SE Q.Text, Me.Cmb_Fish_Cod e.Text, Me.DTP_FSHTRN_D AT.Text, Me.Txt_MKT_CODE .Text, Me.Txt_QTY.Text , Me.Txt_WHOLSALE _PRICE.Text, Me.Txt_RETAIL_P RICE.Text, Me.TXT_TOTAL_PR ICE.Text, Me.TXT_NATURE.T ext, Me.CHK_EXPORT.T ext, Me.CHK_IMPORTED .Text})

' Cmd_Cancel_Clic k(sender, e)
End If

End Sub
dear put ur Q in .net forum
insted of entire code simply put the code where u've the problem
Mar 21 '07 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

4
25034
by: Andras Gilicz | last post by:
Hi VB fans I'm working on a relatively large project in VB6 with about a dozen forms, including graphs, labels, text boxes, etc. The software itself is actually a flow simulator with more or less complex technical calculations, several input variables. I would like to equipp the starting panel with the usual New, Open, Save, Save As, Close etc. menus (like in Excel, or Word, etc.) What is the best way to accomplish Save, or Save As?...
49
14368
by: Yannick Turgeon | last post by:
Hello, We are in the process of examining our current main application. We have to do some major changes and, in the process, are questionning/validating the use of MS Access as front-end. The application is relatively big: around 200 tables, 200 forms and sub-forms, 150 queries and 150 repports, 5GB of data (SQL Server 2000), 40 users. I'm wondering what are the disadvantages of using Access as front-end? Other that it's not...
1
1925
by: waddley | last post by:
I have a client who wants to have Access ask to save changes anytime data is changed or added into the database. I'm not really an Access guy, I actually run their servers, but have been dropped into the roll because I have some understanding of it. Any help would be greatly appreciated, they have me on a serious time line and I've been pulling my hair out all day over this. Thanks in advance. -Walter
4
3450
by: WJA | last post by:
I'm probably missing something here but I can't understand the following. When 2 users try to save a record with the same primary key (a number field), the first record saves as expected, but the second does not produce an error. The "Save" button uses "Docmd.Save" to save the record. If I insert "Me.Dirty = False" before "Docmd.Save" an error is produced. Why doesn't "Docmd.Save" produce an error but instead silently fails to save the...
2
2876
by: josephm | last post by:
Hello Group: My first post on the group.Hope I get a response. I have a modest Fire Insurance ACCES Db.Thanks to this group - for the code. A "wanna be programmer"... "LEARNS" The code here-under (from the Archive) duplicates the EXPIRING records but affords the USER to give ONLY A NEW POLICY NO (NewKey),should a client opt to renew upon expiry of the YEARLY POLICY.The NEW POLICY NO is save as a NEW record along with the "old"static...
2
3200
by: rockdc1981 | last post by:
I dont it is possible to put this codes together.. I want to prompt the user to save the record and at the same time have a log of audit trail. the codes work out fine separately. Code for Audit Trail Option Compare Database Const cDQ As String = """" Sub AuditTrail(frm As Form, recordid As Control) 'Track changes to data. 'recordid identifies the pk field's corresponding
2
64519
by: Ian | last post by:
I am trying to save the current record on a form before opening a report, doesn’t sound to hard does it? The code on a buttons on click event goes like this: DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70 'First save record I used this for many years with problems using Access 97, when the database is upgraded to Access 2000 or later I occasionally get an error message saying “Save Command is not available now”.
2
2317
by: Karl | last post by:
Using A2000 When I click the save icon in form design, Access closes immediately. No warning messges, nothing. This happens on only one form. I deleted the form and recreated it. I could save the new form as Form1 but when I renamed it to the origianl forms name and click the save icon, Access closed. The form is based on a table that has several combo boxes. FWIW the table is
7
2178
by: Neil | last post by:
Was working in A2003 and noticed that the Save Record item on the Records menu was not available when the record was not dirty. In A2000, Save Record was always available. (This is a problem for me because I have Docmd.Runcmd acCmdSaveRecord code all over the place, which never gave an error before. But now it was giving an error when the Save Record menu command wasn't available.) So I went back to A2000 and confirmed that the Save...
0
10503
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10544
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10214
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9326
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
6951
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5624
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4425
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
3973
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3079
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.