By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,812 Members | 1,324 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,812 IT Pros & Developers. It's quick & easy.

VB.Net WindowsApplication Save Access Database Record

P: 1
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).Database_Type
Case Database_Type.OracelDB
Select Case No
Case 0 : If Cn_Def.State = ConnectionState.Open Then Cn_Def.Close()
Cn_Def.ConnectionString = "Provider=MSDAORA.1;Password=" & DBInfo(No).Password & ";User ID=" & DBInfo(No).User_Name & ";Data Source=" & DBInfo(No).Server_Name & ";"
Cn_Def.Open()
Case 1 : If Cn1.State = ConnectionState.Open Then Cn1.Close()
Cn1.ConnectionString = "Provider=MSDAORA.1;Password=" & DBInfo(No).Password & ";User ID=" & DBInfo(No).User_Name & ";Data Source=" & DBInfo(No).Server_Name & ";"
Cn1.Open()
Case 2 : If Cn2.State = ConnectionState.Open Then Cn2.Close()
Cn2.ConnectionString = "Provider=MSDAORA.1;Password=" & DBInfo(No).Password & ";User ID=" & DBInfo(No).User_Name & ";Data Source=" & DBInfo(No).Server_Name & ";"
Cn2.Open()
End Select

Case Database_Type.SQLServer
Select Case No
Case 0 : If Cn_Def.State = ConnectionState.Open Then Cn_Def.Close()
Cn_Def.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=" & DBInfo(No).Database_Name & ";Data Source=" & DBInfo(No).Server_Name & ";Password=" & DBInfo(No).Password & ";User ID=" & DBInfo(No).User_Name & ";Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=" & DBInfo(No).Server_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.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=" & DBInfo(No).Database_Name & ";Data Source=" & DBInfo(No).Server_Name & ";Password=" & DBInfo(No).Password & ";User ID=" & DBInfo(No).User_Name & ";Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=" & DBInfo(No).Server_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.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=" & DBInfo(No).Database_Name & ";Data Source=" & DBInfo(No).Server_Name & ";Password=" & DBInfo(No).Password & ";User ID=" & DBInfo(No).User_Name & ";Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=" & DBInfo(No).Server_Name & ";Use Encryption for Data=False;Tag with column collation when possible=False"
Cn2.Open()
End Select

Case Database_Type.AccessDB
Select Case No
Case 0 : If Cn_Def.State = ConnectionState.Open Then Cn_Def.Close()
Cn_Def.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBInfo(No).Database_Name
Cn_Def.Open()
Case 1 : If Cn1.State = ConnectionState.Open Then Cn1.Close()
Cn1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBInfo(No).Database_Name
Cn1.Open()
Case 2 : If Cn2.State = ConnectionState.Open Then Cn2.Close()
Cn2.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBInfo(No).Database_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.Message)
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.Message)
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.Message, MsgBoxStyle.Critical, "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.OleDbConnection, 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.OleDbCommand(Statement, CN)

Dim i As Integer
For i = 0 To Param.Length - 1
Cmd.Parameters.Add("Param" & i, Param(i))
Next
Cmd.ExecuteNonQuery()
Catch e As Exception
Dim i As Integer
Dim x As String = ""
For i = 0 To Param.Length - 1
x &= "Parameter(" & i & "): " & Param(i).ToString & vbCrLf
Next
MsgBox(e.Message, MsgBoxStyle.Critical)
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.EventArgs) Handles Btn_Save.Click
Dim sql As String

If Status Then
If Trim(Me.Txt_FISH_SEQ.Text) <> Nothing AndAlso IsNumeric(Me.Txt_FISH_SEQ.Text) Then
If Not Found_Prim("FISHING_TRANSACTIONS", "FISH_SEQ", Me.Txt_FISH_SEQ.Text) Then

CMDS.Exec(Cn_Def, "Insert into FISHING_TRANSACTIONS(Fish_Seq,Fish_Code,TRANSACTIO N_DATE,MARKET_CODE,QTY,WHOLSALE_PRICE,RETAIL_PRICE ,TOTAL_PRICE,NATURE,EXPORT,IMPORT) Values(?,?,?,?,?,?,?,?,?,?,?)", New Object() {Me.Txt_FISH_SEQ.Text, Me.Cmb_Fish_Code.Text, Me.DTP_FSHTRN_DAT.Text, Me.Txt_MKT_CODE.Text, Me.Txt_QTY.Text, Me.Txt_WHOLSALE_PRICE.Text, Me.Txt_RETAIL_PRICE.Text, Me.TXT_TOTAL_PRICE.Text, Me.TXT_NATURE.Text, Me.CHK_EXPORT.Text, Me.CHK_IMPORTED.Text})
Else : MsgBox("Record Exist"MsgBoxStyle.Information, Me.Text)
Me.Txt_FISH_SEQ.Focus()

Me.DTP_FSHTRN_DAT.Focus()
End If

End If
Else


CMDS.Exec(Cn_Def, "Update FISHING_TRANSACTIONS set FISH_SEQ=?,FISH_CODE=?,TRANSACTION_DATE=?,MARKET_C ODE=?,QTY=?,WHOLESALE_PRICE=?,RETAIL_PRICE=?,TOTAL _PRICE=?,NATURE=?,EXPORT=?,IMPORT=? Where FISH_SEQ=" & Me.Txt_FISH_SEQ.Text, New Object() {Me.Txt_FISH_SEQ.Text, Me.Cmb_Fish_Code.Text, Me.DTP_FSHTRN_DAT.Text, Me.Txt_MKT_CODE.Text, Me.Txt_QTY.Text, Me.Txt_WHOLSALE_PRICE.Text, Me.Txt_RETAIL_PRICE.Text, Me.TXT_TOTAL_PRICE.Text, Me.TXT_NATURE.Text, Me.CHK_EXPORT.Text, Me.CHK_IMPORTED.Text})

' Cmd_Cancel_Click(sender, e)
End If

End Sub
Mar 21 '07 #1
Share this Question
Share on Google+
1 Reply


vijaydiwakar
100+
P: 579
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).Database_Type
Case Database_Type.OracelDB
Select Case No
Case 0 : If Cn_Def.State = ConnectionState.Open Then Cn_Def.Close()
Cn_Def.ConnectionString = "Provider=MSDAORA.1;Password=" & DBInfo(No).Password & ";User ID=" & DBInfo(No).User_Name & ";Data Source=" & DBInfo(No).Server_Name & ";"
Cn_Def.Open()
Case 1 : If Cn1.State = ConnectionState.Open Then Cn1.Close()
Cn1.ConnectionString = "Provider=MSDAORA.1;Password=" & DBInfo(No).Password & ";User ID=" & DBInfo(No).User_Name & ";Data Source=" & DBInfo(No).Server_Name & ";"
Cn1.Open()
Case 2 : If Cn2.State = ConnectionState.Open Then Cn2.Close()
Cn2.ConnectionString = "Provider=MSDAORA.1;Password=" & DBInfo(No).Password & ";User ID=" & DBInfo(No).User_Name & ";Data Source=" & DBInfo(No).Server_Name & ";"
Cn2.Open()
End Select

Case Database_Type.SQLServer
Select Case No
Case 0 : If Cn_Def.State = ConnectionState.Open Then Cn_Def.Close()
Cn_Def.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=" & DBInfo(No).Database_Name & ";Data Source=" & DBInfo(No).Server_Name & ";Password=" & DBInfo(No).Password & ";User ID=" & DBInfo(No).User_Name & ";Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=" & DBInfo(No).Server_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.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=" & DBInfo(No).Database_Name & ";Data Source=" & DBInfo(No).Server_Name & ";Password=" & DBInfo(No).Password & ";User ID=" & DBInfo(No).User_Name & ";Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=" & DBInfo(No).Server_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.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=" & DBInfo(No).Database_Name & ";Data Source=" & DBInfo(No).Server_Name & ";Password=" & DBInfo(No).Password & ";User ID=" & DBInfo(No).User_Name & ";Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=" & DBInfo(No).Server_Name & ";Use Encryption for Data=False;Tag with column collation when possible=False"
Cn2.Open()
End Select

Case Database_Type.AccessDB
Select Case No
Case 0 : If Cn_Def.State = ConnectionState.Open Then Cn_Def.Close()
Cn_Def.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBInfo(No).Database_Name
Cn_Def.Open()
Case 1 : If Cn1.State = ConnectionState.Open Then Cn1.Close()
Cn1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBInfo(No).Database_Name
Cn1.Open()
Case 2 : If Cn2.State = ConnectionState.Open Then Cn2.Close()
Cn2.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBInfo(No).Database_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.Message)
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.Message)
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.Message, MsgBoxStyle.Critical, "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.OleDbConnection, 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.OleDbCommand(Statement, CN)

Dim i As Integer
For i = 0 To Param.Length - 1
Cmd.Parameters.Add("Param" & i, Param(i))
Next
Cmd.ExecuteNonQuery()
Catch e As Exception
Dim i As Integer
Dim x As String = ""
For i = 0 To Param.Length - 1
x &= "Parameter(" & i & "): " & Param(i).ToString & vbCrLf
Next
MsgBox(e.Message, MsgBoxStyle.Critical)
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.EventArgs) Handles Btn_Save.Click
Dim sql As String

If Status Then
If Trim(Me.Txt_FISH_SEQ.Text) <> Nothing AndAlso IsNumeric(Me.Txt_FISH_SEQ.Text) Then
If Not Found_Prim("FISHING_TRANSACTIONS", "FISH_SEQ", Me.Txt_FISH_SEQ.Text) Then

CMDS.Exec(Cn_Def, "Insert into FISHING_TRANSACTIONS(Fish_Seq,Fish_Code,TRANSACTIO N_DATE,MARKET_CODE,QTY,WHOLSALE_PRICE,RETAIL_PRICE ,TOTAL_PRICE,NATURE,EXPORT,IMPORT) Values(?,?,?,?,?,?,?,?,?,?,?)", New Object() {Me.Txt_FISH_SEQ.Text, Me.Cmb_Fish_Code.Text, Me.DTP_FSHTRN_DAT.Text, Me.Txt_MKT_CODE.Text, Me.Txt_QTY.Text, Me.Txt_WHOLSALE_PRICE.Text, Me.Txt_RETAIL_PRICE.Text, Me.TXT_TOTAL_PRICE.Text, Me.TXT_NATURE.Text, Me.CHK_EXPORT.Text, Me.CHK_IMPORTED.Text})
Else : MsgBox("Record Exist"MsgBoxStyle.Information, Me.Text)
Me.Txt_FISH_SEQ.Focus()

Me.DTP_FSHTRN_DAT.Focus()
End If

End If
Else


CMDS.Exec(Cn_Def, "Update FISHING_TRANSACTIONS set FISH_SEQ=?,FISH_CODE=?,TRANSACTION_DATE=?,MARKET_C ODE=?,QTY=?,WHOLESALE_PRICE=?,RETAIL_PRICE=?,TOTAL _PRICE=?,NATURE=?,EXPORT=?,IMPORT=? Where FISH_SEQ=" & Me.Txt_FISH_SEQ.Text, New Object() {Me.Txt_FISH_SEQ.Text, Me.Cmb_Fish_Code.Text, Me.DTP_FSHTRN_DAT.Text, Me.Txt_MKT_CODE.Text, Me.Txt_QTY.Text, Me.Txt_WHOLSALE_PRICE.Text, Me.Txt_RETAIL_PRICE.Text, Me.TXT_TOTAL_PRICE.Text, Me.TXT_NATURE.Text, Me.CHK_EXPORT.Text, Me.CHK_IMPORTED.Text})

' Cmd_Cancel_Click(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

Post your reply

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